SQLITE (C/C++interface) - How to commit a transaction

Question

I am using sqlite c/c++ interface.

Now here is my scenario -

I have 3 tables (related tables) say A, B, C.

Now, there is a function called Set, which get some inputs and based on the inputs inserts rows into these three tables. (sometimes it can be an update in one of the tables)

Now I need two things.

One, i dont want autocommit feature. Basically I would like to commit after every 1000 calls to Set function

Secondly, within the set function itself, if i find that after inserting into two tables, the third insert fails, then i have to revert, those particular changes in that Set function call.

Now i dont see any sqlite3_commit function exposed. I only see a function called sqlite3_commit_hook() which is slightly diff in documentation. Are there any function exposed for this purpose? or What is the way to achieve this behaviour?

Can you help me with the best approach of doing this.

Regards, Arjun

Answer 1

can you use BEGIN, COMMIT and ROLLBACK sql statements inside the C/C++ code?

Answer 2

You use sqlite3_exec and pass "BEGIN TRANSACTION" and "END TRANSACTION" respectively.

// 'db' is the pointer you got from sqlite3_open*
sqlite3_exec(db, "BEGIN TRANSACTION;", NULL, NULL, NULL);
// Any (modifying) SQL commands executed here are not committed until at the you call:
sqlite3_exec(db, "END TRANSACTION;", NULL, NULL, NULL);

There are synonyms for these SQL commands (like COMMIT instead of END TRANSACTION). For reference, here is the SQLite documentation for transactions.