SQLITE Multiple Statements at once

[BostonMerlin]

I'm trying to execute multiple sqlite statements at the same time back to back but am having a problem. Using MS SQL daily at my full time gig its fairly easy to wrap begin/end statements around inserts, deletes and updates.

I read up on the sqllite site that they support BEGIN and END transactions but whenever i pass more than one statement into sqlite3_prepare_v2 it returns an error.

example: "BEGIN Delete col1 From table1 END BEGIN Update Table2 Set col1=3 END"

I'm processing several thousand rows at a time and am trying to speed things up as much as possible.

Any thoughts?

Thanks John

[BostonMerlin]

I think adding semicolons after each statement will work.. still need to test.

John

[Stitch]

Hi,

Yes, you need begin and commit statements but you implement them like this:

int success;
if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
  // BEGIN STATEMENT
  const char *sql1 = "BEGIN EXCLUSIVE TRANSACTION";
  sqlite3_stmt *begin_statement;
  if (sqlite3_prepare_v2(database, sql1, -1, &begin_statement, NULL) != SQLITE_OK) {
    NSAssert1(0, @"Error Message: '%s'.", sqlite3_errmsg(database));
  }
  success = sqlite3_step(begin_statement);
  if (success != SQLITE_DONE) {
    NSAssert1(0, @"Error Message: '%s'.", sqlite3_errmsg(database));
  }


  // DO YOUR NORMAL INSERT STATEMENTS HERE


  // COMMIT
  const char *sql2 = "COMMIT TRANSACTION";
  sqlite3_stmt *commit_statement;
  if (sqlite3_prepare_v2(database, sql2, -1, &commit_statement, NULL) != SQLITE_OK) {
    NSAssert1(0, @"Error Message: '%s'.", sqlite3_errmsg(database));
  }
  success = sqlite3_step(commit_statement);
  if (success != SQLITE_DONE) {
    NSAssert1(0, @"Error Message: '%s'.", sqlite3_errmsg(database));
  }
}

Thanks,
Richard.

[Knertified]

I found this thread when searching for transaction help. I think I've found an easier way as it's working for me.

Before your first prepare statement add:

	sqlite3_exec(database, "BEGIN", 0, 0, 0); // Begin Transaction

After your last SQL reset statement but before your finalize statement add:

	sqlite3_exec(database, "COMMIT", 0, 0, 0); // Commit Transaction

[lbendlin]

There is no need for any Prepare statements if you do simple transactions (without BLOBs). You can use exclusively sqlite3_exec, and construct the inner loop statement on the fly with sprintf and a buffer variable that is large enough to hold the statement.

			sqlite3_exec(database, "PRAGMA synchronous = 0;", NULL, NULL, NULL);
			sqlite3_exec(database, "PRAGMA temp_store = 2;", NULL, NULL, NULL);
			// truncate table
			sqlite3_exec(database, "DELETE FROM table", NULL, NULL, NULL);
			// import data
			char buffer [200];
			//NSLog(@"Beginning transaction");
			sqlite3_exec(database, "BEGIN TRANSACTION;", NULL, NULL, NULL);
			for (int i = 0; i < [chunks count]-2; i++) {

				sprintf(buffer,	"INSERT INTO table VALUES (%s);",[[chunks objectAtIndex:i] UTF8String]); 
				sqlite3_exec(database, buffer, NULL, NULL, NULL);
			}
			//NSLog(@"Committing transaction");
			if(sqlite3_exec(database, "COMMIT TRANSACTION;", NULL, NULL, NULL)== SQLITE_OK) {...

[skayser]

Hi - I'm a beginner programmer and was hoping someone would be able to tell me where I need to put the 'begin' and 'commit' statements. I have a process which takes an sql statement and puts it into this method as the 'sqlString'. Running the method succesfully runs the sql scripts as per my logging but I don't think the data is being committed to the sqlite db as the view isn't updated.

Could someone tell me where in this code I should put the begin and commit statements? THANKS!

	sqlite3 *database;
	sqlite3_stmt *selectStatement; 
	
	NSString *sqltemp= sqlString;
	const char *sqlStatement = [sqltemp UTF8String];

	if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK)

	{
		if(sqlite3_prepare_v2(database, sqlStatement, -1, &selectStatement, NULL) != SQLITE_OK)
		{
			NSAssert1(0,@"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
		}
	}

[lbendlin]

Begin/commit has limited use for a single statement (unless you want to explitly lock the db against other use at that time)

you use begin/commit with loops of SQL for example when you want to insert 1000 rows one by one.

[junglemason]

Originally Posted by lbendlin

sqlite3_exec(database, "PRAGMA synchronous = 0;", NULL, NULL, NULL);
sqlite3_exec(database, "PRAGMA temp_store = 2;", NULL, NULL, NULL);
// truncate table
sqlite3_exec(database, "DELETE FROM table", NULL, NULL, NULL);
// import data
char buffer [200];
//NSLog(@"Beginning transaction");
sqlite3_exec(database, "BEGIN TRANSACTION;", NULL, NULL, NULL);
for (int i = 0; i < [chunks count]-2; i++) {
	sprintf(buffer, "INSERT INTO table VALUES (%s);",[[chunks objectAtIndex:i] UTF8String]);
	sqlite3_exec(database, buffer, NULL, NULL, NULL);
}
//NSLog(@"Committing transaction");
if(sqlite3_exec(database, "COMMIT TRANSACTION;", NULL, NULL, NULL)== SQLITE_OK) {...

Thanks, lbendlin!

A few questions, if you don't mind:

- Can I put the delete statement inside the transaction?

- Anything wrong with using pragma synchronous=2 (FULL) instead of 0? I want to be extra safe in case a crash happens mid-transaction.

- char buffer [200]; - I am going to have a thousand rows to insert - will this hold them? Do I just change the number from 200 to 2bajillion?

Thanks again
-Rich

[lbendlin]

Originally Posted by junglemason

- Can I put the delete statement inside the transaction?

- Anything wrong with using pragma synchronous=2 (FULL) instead of 0? I want to be extra safe in case a crash happens mid-transaction.

- char buffer [200]; - I am going to have a thousand rows to insert - will this hold them? Do I just change the number from 200 to 2bajillion?

Thanks again
-Rich

You can put any statement in there, but there will be no speed gain. Only the inserts benefit from that, speed wise.

The buffer is refilled every time. It only needs to be as long as your longest anticipated statement

I have no opinion on the Pragma stuff. I don't understand it entirely, and it also doesn't seem to make any (speed) difference.