the REPLACE INTO statement in SQLite3 shows a replaced row but actually it isn't

Question

Just want to ask a basic question about SQLite3's REPLACE INTO usage. All my other database code is working, but the REPLACE INTO statement seems to work, but isn't there. I EXPECT to the REPLACE INTO to REPLACE the data in the specified row ID, id, held in variable dbRow.

insertSQL = [NSString stringWithFormat: @"REPLACE INTO script ( id, nrec, url ) VALUES ( \"%d\", \"%d\", \"%@\" )", dbRow, recN, fileLoc ];
insert_stmt = [insertSQL UTF8String];
if( sqlite3_exec(scriptDB, [insertSQL UTF8String], NULL, NULL, &errorSQL ) == SQLITE_OK) {
   NSLog( @"dB updated at row=%d with nrec=%d and URL=%@.", dbRow, recN, fileLoc );
} else {
   NSLog(@"Line Not replaced.  Error: %s", errorSQL );
}

The above works: The OK condition shows exactly what I want to see. No errors. Later, when I need to use this data, it did NOT replace the original. So, just to test things, I do a SELECT right away, in the same method:

querySQL = [NSString stringWithFormat: @"SELECT id, nrec, url FROM script WHERE title=\"%@\" AND nline=\"%d\"", myTitle, myLineN ];
query_stmt = [querySQL UTF8String];
if( sqlite3_prepare_v2(scriptDB, query_stmt, -1, &statement, NULL) == SQLITE_OK) {
   NSLog(@"Just Replaced Query prepared");
   while( sqlite3_step(statement) == SQLITE_ROW ) {
      myID = sqlite3_column_int(statement, 0);
      myNRec = sqlite3_column_int(statement, 1);
      myURL = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 4)];
   }
   sqlite3_finalize(statement);
   NSLog( @"Line %d of %@ has: recN=%d in row=%d at url: %@", myLineN, myTitle, myNRec, myID, myURL );
} else {
   NSLog( @"Error: %s", errMsg );
}

I expect to see the same thing that I REPLACE INTO'd the database. I don't. I see the original data, before the REPLACE. Why does it say it's there when it isn't?

Because you're trying to update a file in the app bundle? Sooooo many dupes of this exact same question on SO... — , Oct 11 '12 at 09:10
for example, http://stackoverflow.com/questions/717108/where-would-you-place-your-sqlite-database-file-in-an-iphone-app — Rick_CBR929RR, Oct 11 '12 at 14:47

score 0 · Answer 1 · answered Oct 11 '12 at 09:13

0

It seems you're trying to update a database file located in your app bundle. This is impossible because you can't write to files in your app bundle (for obvious security reasons). You have to copy the database to a writable location, e. g. into the Documents or Library directory before altering it.

(Curiuos why the sqlite3 library still reports a success? Well, because the kernel in iOS and the sandboxing service is tricky. sqlite3 gets informed about successfully having written the file in question by the kernel, however the actual write is silently skipped according to the file's/directory's sandboxing profile.)

answered Oct 11 '12 at 09:13

That's more helpful. Thank you. Now it's in: /Users/apple/Library/Application Support/iPhone Simulator/6.0/Applications/8AF97457-7F0E-4994-9C45-106DBF79C930/Documents/script2.db – Rick_CBR929RR Oct 11 '12 at 09:17
I've got a few view controllers in my app, and I'm using INSERT and SELECT successfully between controllers. It's just the REPLACE INTO that is acting weird. – Rick_CBR929RR Oct 11 '12 at 09:24
Update: I externally made my databases and copied them into my project under supporting files. Then I followed the link in my comment above to createEditableCopyOfScriptsDatabase in my AppDelegate.m with NSLog notes to make sure all was well. It was. Then I used (NSString *) getWritableScriptsDBPath to reference the database. Guess what? The REPLACE INTO still didn't work. So, I separated it into DELETE FROM and INSERT INTO. Still can't change the database. Not on the simulator and not on an actual phone. Anybody able to point me to something that WILL work? – Rick_CBR929RR Oct 12 '12 at 02:44

score 0 · Accepted Answer · answered Oct 12 '12 at 23:50

After trying a lot of seemingly unrelated things, and almost giving up on using SQLite3, I finally decided to put ALL of the table values into the REPLACE INTO line:

replaceSQL = [NSString stringWithFormat: @"REPLACE INTO script ( id, title, nline, nrec, role, speak, url ) VALUES (\"%d\", \"%@\", \"%d\", \"%d\", \"%@\", \"%@\", \"%@\")", myID, myTitle, myLineN, recN, myRole, mySpeak, fileLoc ];

Now it's working fine, as designed.

the REPLACE INTO statement in SQLite3 shows a replaced row but actually it isn't

2 Answers2