iDevGames Forums

Full Version: sqlite3 multi-line query
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
I have sqlite running fine in my iOS app, but am trying to increase performance and have run into a problem.

I thought putting multiple "INSERT" commands into a single string, preparing that statement, and performing sqlite3_step on that would be faster than performing many prepares for each "INSERT" command.

I programmatically construct a long string with a bunch of lines such as:

Code:
INSERT INTO publications ( name, someColumn, anotherColumn, yetAnotherColumn ) VALUES ( 'Some Name','3t995','','BBA0' ) ;
INSERT INTO publications ( name, someColumn, anotherColumn, yetAnotherColumn ) VALUES ( 'Journal of Math','1326','','BBAA' ) ;
INSERT INTO publications ( name, someColumn, anotherColumn, yetAnotherColumn ) VALUES ( 'Journal of Jumping','thisValue','','BBAB' ) ;
INSERT INTO publications ( name, someColumn, anotherColumn, yetAnotherColumn ) VALUES ( 'Journal of Medicine','1529','','BBAC' ) ;

This is in an NSMutableString named sqlString, which I do the following to:

Code:
sqlite3_stmt *statement;
            if (sqlite3_prepare_v2(appDelegate.database, [sqlString UTF8String], -1, &statement, NULL) == SQLITE_OK)
            {
                int resultCode = sqlite3_step(statement);
                if( resultCode != SQLITE_ERROR )
                {
                    //we're good!
                }
                else
                {
                    NSLog(@"sqlite ERROR for committing [%@] that: %i", sqlString, resultCode);
                }
            }
            sqlite3_finalize(statement);

Both sqlite3_prepare_v2 and sqlite3_step are returning SQLITE_OK (which is code 101), but when I exit out of the app and completely reload it, none of the data has truly been written to disk.

I have escaped all the single quote ' characters in the string to be double single quotes ''... any ideas what might be going wrong?

Thanks!
Fantastic link, thanks so much! After some tweaking, took my queries from 55 seconds to 5! Embarrassed I never took the time to optimize this before... But glad it's there now!
Reference URL's