Weird problem with a sqlite3-database (Can only write once)

Question

Hey,

i have a huge problem with my sqlite3 database on the iphone sdk 4. I can only write/read the database once! With the iphone sdk 3.2 everything had just worked fine.

After the first try i get the errormessage "SQLITE_BUSY".

Here's a snippet of my code i use:

-(void) addFavoriteOdv:(Odv*)odv name:(NSString*)name;
{
    @synchronized(self)
    {
        if(name == nil)
            name = odv.name;

        NSString* rowId = [self addOdv:odv];

        int existing = [self numberOfFavoriteOdvs:rowId];
        if(existing == 0)
        {
            sqlite3 *database = [self getOpenDatabase];

            const char *insertStatement = [@"insert into favoriteodvs(odv, name) values (?, ?)" UTF8String];

            int retCode = 0;
            sqlite3_stmt *statement;
            retCode = sqlite3_prepare_v2(database, insertStatement, -1, &statement, NULL);

            if(retCode == SQLITE_OK)
            {
                sqlite3_bind_text(statement, 1, [rowId UTF8String], -1, SQLITE_TRANSIENT);
                sqlite3_bind_text(statement, 2, [name UTF8String], -1, SQLITE_TRANSIENT);

                if(sqlite3_step(statement)!= SQLITE_DONE)
                {
                    NSLog(@"SQL Error: %s", sqlite3_errmsg(database));
                    NSLog(@"Inserting FavoriteOdv failed");
                }
            }

            sqlite3_finalize(statement);
            sqlite3_close(database);
        }
    }
}

Has anybody an idea? I use the libsqlite3.dylib.

Thanks, Sebastian

Answer 1

to reuse your prepared statement - you must call

sqlite3_reset(statement);

right after you're received "SQLITE_DONE" from sqlite3_step. After that you can bind another data and run your query again.

p.s. wrong:

const char *insertStatement = [@"insert into favoriteodvs(odv, name) values (?, ?)" UTF8String];

correct:

const char *insertStatement = "insert into favoriteodvs(odv, name) values (?, ?)";