sqlite3 multi-line query

Moderator
Posts: 387
Joined: 2002.08
Post: #1
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!

KB Productions, Car Care for iPhone/iPod Touch
@karlbecker_com
All too often, art is simply the loss of practicality.
Quote this message in a reply
Luminary
Posts: 5,143
Joined: 2002.04
Moderator
Posts: 387
Joined: 2002.08
Post: #3
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!

KB Productions, Car Care for iPhone/iPod Touch
@karlbecker_com
All too often, art is simply the loss of practicality.
Quote this message in a reply
Post Reply