sqlite3 multi-line query
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:
This is in an NSMutableString named sqlString, which I do the following to:
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!
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.
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.