sqlite3_prepare_v2 character limit?

Question

I have data stored in a sqlite3 database and have several places that I read and write data to and from the database. The problem I am running into is that if the SQL statement gets to be very long the sqlite3_prepare_v2 method returns an error.

This code works:

NSString *strSQL = @"UPDATE commandList SET displayName=?, type=?, formula=?, controlButton=?, sort=? WHERE pk=?;";
const char *sql = [strSQL UTF8String];
if (sqlite3_prepare_v2(database, sql, -1, &dehydrate_statment, NULL) != SQLITE_OK) {
    NSLog(@"Error: failed to create update statement with message '%@'.", sqlite3_errmsg(database));
}

But this code errors out:

NSString *strSQL = @"UPDATE commandList SET displayName=?, type=?, formula=?, onFormula=?, offFormula=?, controlButton=?, sort=? WHERE pk=?;";
const char *sql = [strSQL UTF8String];
if (sqlite3_prepare_v2(database, sql, -1, &dehydrate_statment, NULL) != SQLITE_OK) {
    NSLog(@"Error: failed to create update statement with message '%@'.", sqlite3_errmsg(database));
}

note the only difference is the 1st line.

Answer 1

When you say "the code errors out", you really ought to post the error. This saves us from speculating, or having to write a sample to reproduce the error you are getting.

See: http://www.sqlite.org/limits.html

SQLite does limit the maximum size of an SQL statement. (This prevents undesirable behavior when run in an embedded environment, but doesn't affect the size of values bound to the statement.)

You should't be running into this size limit, based on the code above, but it is hard to say what specifically you are running into because the sample + question doesn't stand alone.

Answer 2

The problem doesn't exist in the iPhone simulator so it must be something else.