Recent

Author Topic: How to use stoUseImplicit with SQLite?  (Read 3001 times)

regs

  • Jr. Member
  • **
  • Posts: 53
How to use stoUseImplicit with SQLite?
« on: August 17, 2021, 04:08:00 am »
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  [Select][+][-]
  1.  
  2. .....
  3. var
  4.   sSQL: String;
  5. begin
  6.  
  7.   SQLTransaction1.DataBase := SQLite3Connection1;
  8.   SQLTransaction1.Action := caCommitRetaining;
  9.   SQLite3Connection1.DatabaseName := 'db.sqlite';
  10.   SQLite3Connection1.Open;
  11.  
  12.   SQLQuery1.DataBase := SQLite3Connection1;
  13.   SQLQuery1.Transaction := SQLTransaction1;
  14.  
  15.   SQLTransaction1.Options := [stoUseImplicit];
  16.  
  17.   sSQL :=
  18.     'BEGIN;'#10 +
  19.     '  DELETE FROM table1;'#10 +
  20.     '  DELETE FROM table2;'#10 +
  21.     '  DELETE FROM table3;'#10 +
  22.     '  DELETE FROM table4;'#10 +
  23.     'COMMIT;'
  24.   ;
  25.  
  26.   SQLQuery1.SQL.Text := sSQL;
  27.   SQLQuery1.ExecSQL; //not commited
  28.   //SQLite3Connection1.CloseTransactions;
  29.  
  30.   SQLTransaction1.Options := [];
  31.  
  32.   sSQL := 'INSERT INTO table1 (column1) VALUES (1)';
  33.  
  34.   SQLQuery1.SQL.Text := sSQL;
  35.  
  36.   SQLTransaction1.StartTransaction; //error transaction already started
  37. .....
  38.  
  39.  



devEric69

  • Hero Member
  • *****
  • Posts: 648
Re: How to use stoUseImplicit with SQLite?
« Reply #1 on: August 17, 2021, 09:06:47 am »
Hello,

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).

use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

regs

  • Jr. Member
  • **
  • Posts: 53
Re: How to use stoUseImplicit with SQLite?
« Reply #2 on: August 17, 2021, 11:44:37 am »
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.

regs

  • Jr. Member
  • **
  • Posts: 53
Re: How to use stoUseImplicit with SQLite?
« Reply #3 on: August 17, 2021, 11:58:22 pm »
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.
« Last Edit: August 18, 2021, 12:00:08 am by regs »

Hartmut

  • Hero Member
  • *****
  • Posts: 742
Re: How to use stoUseImplicit with SQLite?
« Reply #4 on: August 26, 2021, 07:47:53 am »
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 https://forum.lazarus.freepascal.org/index.php/board,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.

HTH

 

TinyPortal © 2005-2018