Insert dictionary in an SQLite db won't work

Question

I'm trying to updating the SQLite db on the iPhone with a dictionary returned by my server. sqlite3_step return sqlite_ok but the database still remain empty. Anyway inserted a log for a possible error after sqlite3_step and this return "database locked" so I think something is wrong with the sqlite function order in my code. I've also verified that the dictionary contains data with a log inside the iteration and it gives me all I expect to find.

What's wrong here? Can someone help me? Sorry for my bad english and thanks Marco

 //----- database update -----------------------------------------------------------------------------------------------------
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *dbPath = [documentsDirectory stringByAppendingPathComponent:@"DB.sqlite"];

if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {
    hasError= false;         
     const char *update_stmt="REPLACE INTO table VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
        sqlite3_stmt *compiledstatement;
        if(sqlite3_prepare_v2(database,update_stmt , -1, &compiledstatement, NULL)==SQLITE_OK) {
            for (NSDictionary *item in [update objectForKey:@"table1"]) {
                sqlite3_bind_int(compiledstatement, 1,[[item objectForKey:@"a"]integerValue]);
                sqlite3_bind_text(compiledstatement,2,[[item objectForKey:@"b"] UTF8String], -1, SQLITE_TRANSIENT);
                sqlite3_bind_text(compiledstatement,3,[[item objectForKey:@"c"] UTF8String], -1, SQLITE_TRANSIENT);
                sqlite3_bind_text(compiledstatement,4,[[item objectForKey:@"d"] UTF8String], -1, SQLITE_TRANSIENT);
                sqlite3_bind_int(compiledstatement,5,[[item objectForKey:@"e"] integerValue]);
                sqlite3_bind_double(compiledstatement,6,[[item objectForKey:@"f"] doubleValue]);
                sqlite3_bind_text(compiledstatement,7,[[item objectForKey:@"g"] UTF8String], -1, SQLITE_TRANSIENT);
                sqlite3_bind_text(compiledstatement,8,[[item objectForKey:@"s"] UTF8String], -1, SQLITE_TRANSIENT);
                sqlite3_bind_text(compiledstatement,9,[[item objectForKey:@"g"] UTF8String], -1, SQLITE_TRANSIENT);
                sqlite3_bind_text(compiledstatement,10,[[item objectForKey:@"u"] UTF8String], -1, SQLITE_TRANSIENT);
                sqlite3_bind_text(compiledstatement,11,[[item objectForKey:@"y"] UTF8String], -1, SQLITE_TRANSIENT);
                sqlite3_bind_int(compiledstatement,12,[[item objectForKey:@"n"] integerValue]);
                sqlite3_bind_int(compiledstatement,13,[[item objectForKey:@"k"] integerValue]);
                //sqlite3_bind_text(compiledstatement,2,[@"" UTF8String], -1, SQLITE_TRANSIENT);
                if(!sqlite3_step(compiledstatement)==SQLITE_DONE) {hasError= true; NSLog(@"error (%s)", sqlite3_errmsg(database));} //error
                NSLog(@"error (%s)", sqlite3_errmsg(database));
                sqlite3_reset(compiledstatement);
            }
            sqlite3_finalize(compiledstatement);
        }else { NSLog(@"prepare FAILED (%s)", sqlite3_errmsg(database));}        
} else {NSLog(@"opening error");}
sqlite3_close(database);

EDIT:

I'm not able to understand why this code won't work!!! I've not find the final solution.

I always get "library routine called out of sequence" at COMMIT, why? Isn't this sequence correct?

-open -begin -prepare -cycle the dictionary(bind, step and reset; for every item in dictionary I must insert or replace a row) -finalize -commit -close

If I remove the begin and commit exec I have the error "database is locked" on "step", when the code try to insert the first row... it's a nightmare...

Thank you again for your help!!

    //----- database update -----------------------------------------------------------------------------------------------------

const char *updateTags_stmt="REPLACE INTO tags VALUES(?,?,?)";

if (sqlite3_open_v2([dbPath UTF8String], &database,SQLITE_OPEN_READWRITE,NULL) != SQLITE_OK) {
    sqlite3_close(database);return;
}

if (sqlite3_exec(database, "BEGIN", 0, 0, 0)!=SQLITE_OK) {sqlite3_close(database);return;}

sqlite3_stmt *compiledstatement;


    if(sqlite3_prepare_v2(database,updateTags_stmt , -1, &compiledstatement, NULL)!=SQLITE_OK) {sqlite3_close(database);return;}

        for (NSDictionary *item in [update objectForKey:@"tags"]) {
            sqlite3_bind_int(compiledstatement,1,[[item objectForKey:@"tid"] integerValue]);
            sqlite3_bind_text(compiledstatement,2,[[item objectForKey:@"categoria"] UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(compiledstatement,3,[[item objectForKey:@"tag"] UTF8String], -1, SQLITE_TRANSIENT);
            if(sqlite3_step(compiledstatement)!=SQLITE_DONE) {
                sqlite3_finalize(compiledstatement);
                sqlite3_close(database);
                return;
            } else {if (sqlite3_reset(compiledstatement)!=SQLITE_OK){sqlite3_close(database);return;}}
        }
        if (sqlite3_finalize(compiledstatement)!=SQLITE_OK){sqlite3_close(database);return;}



    if (sqlite3_exec(database, "COMMIT", NULL, NULL, 0)!=SQLITE_OK) {sqlite3_close(database);return;}


if (sqlite3_close(database)!=SQLITE_DONE){sqlite3_close(database);return;}

Answer 1

This code is wrong; it first inverts the return value of sqlite3_step, and then compares that value to SQLITE_DONE (which never succeeds because ! returns 0 or 1):

if(!sqlite3_step(compiledstatement)==SQLITE_DONE)

Do it correctly:

if (sqlite3_step(compiledstatement) != SQLITE_DONE)

Answer 2

Finally I've found the problem: there was another query on a different class in which then statement was not finalized so the DB still remains always open... two weeks for this lesson!! heheh sorry!