Sqlite doesn't seem to like my delete statement

Question

I've got the following iphone code, which seems to be failing:

sqlite3_stmt *dbps;
NSString *sql = @"delete from days where day=?1;insert into days(disabled,recipe_id,day) values(?2,?3,?1)";
int rc = sqlite3_prepare_v2(db, sql.UTF8String, -1, &dbps, NULL);
...

The 'rc' return code is 1, meaning SQLITE_ERROR (SQL error or missing database, according to the sqlite site). Not sure what i've done wrong? The database 'db' is indeed open, and other queries seem to work fine.

Thanks a lot guys

Answer 1

Are you sure you have copied the database in Documents directory before opening it? iPhone OS only allow write permissions in documents directory. Here is the code for copying database to Documents directory -

//function to copy database in Documents dir.

    -(void) checkAndCreateDatabase{
        // Check if the SQL database has already been saved to the users phone, if not then copy it over
        BOOL success;

    // Create a FileManager object, we will use this to check the status
    // of the database and to copy it over if required
    NSFileManager *fileManager = [NSFileManager defaultManager];

    // Check if the database has already been created in the users filesystem
    success = [fileManager fileExistsAtPath:databasePath];

    // If the database already exists then return without doing anything
    if(success) return;

    // If not then proceed to copy the database from the application to the users filesystem

    // Get the path to the database in the application package
    NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];

    // Copy the database from the package to the users filesystem
    [fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:nil];

    [fileManager release];


  }


// open the database and fire the delete query...


    sqlite3 *database;
    NSString *sqlStatement = @"";



    NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDir = [documentPaths objectAtIndex:0];
    databasePath = [documentsDir stringByAppendingPathComponent:databaseName];
            NSLog(@"%@",databasePath);
    [serlf checkAndCreateDatabase];



    if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) 
    {

// here you can fire the delete query...
    }

Answer 2

Remove the insert statement from your string. It is not compiled anyway since sqlite3_prepare_v2 will "only compile the first statement in zSql."

Perhaps you should use a trigger to do your (optional) delete, or use insert or replace.

Answer 3

Silly me, i just had an old copy of the schema in my Documents folder, which didn't have the 'days' table in it. So i followed the instructions here: http://stackoverflow.com/questions/692064/cleaning-up-the-iphone-simulator, and then it copied the new schema over, and it started working again.

Thanks for the help guys.