Forum > Databases

How to use stoUseImplicit with SQLite?


I'm trying a block of code with Commit. Everything goes without error, but nothing happens in database.

And then if I try to switch it off and send another quert with tranaction i'm geting error that transation within transaction cannot be started. CloseTransactions doesn't help.

Am I misunderstanding something in a concept?

It's FPC and Lazarus trunk.

--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} --- .....var  sSQL: String;begin   SQLTransaction1.DataBase := SQLite3Connection1;  SQLTransaction1.Action := caCommitRetaining;  SQLite3Connection1.DatabaseName := 'db.sqlite';  SQLite3Connection1.Open;   SQLQuery1.DataBase := SQLite3Connection1;  SQLQuery1.Transaction := SQLTransaction1;   SQLTransaction1.Options := [stoUseImplicit];   sSQL :=    'BEGIN;'#10 +    '  DELETE FROM table1;'#10 +    '  DELETE FROM table2;'#10 +    '  DELETE FROM table3;'#10 +    '  DELETE FROM table4;'#10 +    'COMMIT;'  ;   SQLQuery1.SQL.Text := sSQL;  SQLQuery1.ExecSQL; //not commited  //SQLite3Connection1.CloseTransactions;   SQLTransaction1.Options := [];   sSQL := 'INSERT INTO table1 (column1) VALUES (1)';   SQLQuery1.SQL.Text := sSQL;   SQLTransaction1.StartTransaction; //error transaction already started.....  


To be fair, I don't know "implicit starts". Maybe (hypothesis), "implicit starts" ensures that the transaction is always active (never need to code to start it). So (hypothesis), because "explicit starts" (oTrans.Options := [stoUseExplicitStart];) implies having to code if oTrans.InTransation then oTrans.StartTransaction;, you can't explicitly start a transaction having called oTrans.Options := [stoUseImplicit]; one time (with one\some embedded SQL calls to Open or ExecSQL inside).

Implicit shouldn't ensure. It relies everything to database, so BEGIN, COMMIT and ROLLBACK should work. But COMMIT doesn't happen without any error and when switching Implicit off it throws error that transaction is already started.

It looks like sqlite3_prepare and sqlite3_prepare_v2 only support 1 statement at a time. Which in this case is BEGIN TRANSACTION.

TSQLQuery.ExecSQL and TSQLConnection.ExecuteDirect points to TSQLite3Connection.Prepare and TSQLite3Connection.Execute which are using sqlite3_prepare and sqlite3_step. sqlite3_prepare should be done in a cycle.

SQLite has also function sqlite3_exec. sqlite3_exec does internal sqlite3_prepare cycling. TSQLite3Connection has protected function execsql, which is using sqlite3_exec. Which that function everything goes as expected.

Now TSQLQuery.ExecSQL is supposed to work as a component and FPC API, like TParams etc, so understandably it could only support one statement. But ExecuteDirect doesn't have it all. So may be TSQLite3Connection should have overriden version of ExecuteDirect which would be pointing to execsql.

Don't understand your problem really and what you write, because I'm to much a beginner to databases. But if you want an example, where 'stoUseImplicit' in SQLite really works, you can look at my small demo in,16.0.html where 'stoUseImplicit' was added later in reply #1 (see there). It works without creating any errors (if the disk is not full).

Whether my commands 'SQLTransactionX.Active:=True' and call of sql_commit() are neccessary or not, I don't know (I came from the other side than you).

But my call to sql_close() after each 'commit' is not neccessary, I do it only to improve access to the DB for others.



[0] Message Index

Go to full version