sqlite3 Transaction

Question

in order to speed up table inserts, I've concatenated insert statements (all semicol terminated) into one single string (sqlCode), which is PREPAREd then STEPed.

There are 10000 records in my test file, but only 1000 inserted records in the table.

What am I doing wrong?

I know i should bind parameters to the insert statements, but am new to this and learning: bound parameters will come next. Also, the return value (0) resets a counter used elsewhere.

Thanks.

Frank.

int loadBloc( sqlite3 *db, char *sqlCode )
{
    int   i;
    int   rc;
    sqlite3_stmt *stmt = NULL;

    /* TRANSACT !! */
    sqlite3_exec(db, "BEGIN", 0, 0, 0);
    //printf("%d\n",strlen(sqlCode));
    rc = sqlite3_prepare_v2( db, sqlCode, -1 , &stmt, NULL );
    checkRC( db, rc,SQLITE_OK  , "Error: Failed to prepare.\n" );
    rc = sqlite3_step(stmt);
    checkRC( db, rc,SQLITE_DONE, "Error: Failed to load.\n" );
    sqlite3_exec(db, "COMMIT", 0, 0, 0);
    sqlite3_finalize(stmt);
    sqlCode[0] = '\0';
    return 0;
}

score 0 · Answer 1 · edited May 23 '17 at 11:55

I would check the return value of sqlite3_finalize() - it returns SQLITE_OK (which is equal 0) or an error code if any. So if there is any error, you will catch it and find its explanation in the official documentation.

The second thing I want to mention here - sqlite3_exec() is a wrapper around sqlite3_prepare_v2(), sqlite3_step() and sqlite3_finalize(). You call it inside another sqlite3_prepare_v2() \ sqlite3_finalize() block and even if it's a legal operation, you commit transaction before all queries have been finished. I would try to move transaction's commit:

...
int sqlRet = sqlite3_finalize(stmt); // sqlRet will contain error code
sqlite3_exec(db, "COMMIT", 0, 0, 0);
...

Notice that sqlite3_exec() also returns error code if any error occur. You can replace your calls to sqlite3_prepare_v2(), sqlite3_step() and sqlite3_finalize() by single call to sqlite3_exec() and providing a callback function if it's more convenient. In your case (executing inserts) callback isn't needed. The 5'th parameter of sqlite3_exec() might be useful - it's error message in text format.

Some useful links:

sqlite3 Transaction

1 Answers1