Recent

Author Topic: How can I commit multiple SQL satements as a single transaction? (SQLite)  (Read 8002 times)

alemartini

  • Newbie
  • Posts: 4
Hi,

I need to execute several SQL statements as a single transaction, but so far I haven't been able to do it.

First I thought about handling the whole thing by myself (by using a  BEGIN TRANSACTION .. COMMIT block), which is the way I'd prefer to do it. However, unless I'm missing something, that seems to be impossible. Any attempt to call BEGIN TRANSACTION throws an exception (EDatabaseError : cannot start a transaction within a transaction). By reading the code in sqldb.pp, it seems that ExecutDirect will call StartTransaction whether you want it or not, so I guess that's the reason for the exception.

After realizing that I wouldn't be able to handle the transation myself, I decided to try by using the TSQLTransaction class. Here's a minimum working example showing some basic tests I've made:

Code: [Select]
program sqltest;

{$mode objfpc}{$H+}

uses
  {$IFDEF UNIX}{$IFDEF UseCThreads}
  cthreads,
  {$ENDIF}{$ENDIF}
  Classes,
  { you can add units after this }
  sqlite3conn, sqldb, sysutils;

{$R *.res}

var
  conn : TSQLite3Connection;
  tran : TSQLTransaction;
  qry  : TSQLQuery;
  sql  : TStringList;

begin
  DeleteFile ('/tmp/abc.db');
  conn := TSQLite3Connection.Create (nil);
  conn.DatabaseName := '/tmp/abc.db';

  tran := TSQLTransaction.Create (nil);
  conn.Transaction  := tran;

  conn.Connected := True;

  qry := TSQLQuery.Create (nil);
  qry.DataBase := conn;

  sql := TStringList.Create;

  sql.Add ('CREATE TABLE T (a INTEGER, b INTEGER);');
  sql.Add ('CREATE TABLE Z (a INTEGER, b INTEGER);');
  sql.Add ('INSERT INTO T VALUES (1, 2);');

  Writeln ('SQL statements:');
  Writeln (SQL.Text);

  Writeln ('Running SQL statements ...');
  conn.ExecuteDirect (SQL.Text);
  tran.Commit;

  Writeln ('Showing table T contents:');
  sql.Clear;
  sql.Add ('SELECT * FROM T');
  qry.SQL.Text := sql.Text;
  qry.Open;
  if (qry.BOF and qry.EOF) then
     Writeln ('Table T is empty')
  else begin
    while not qry.EOF do
    begin
      Writeln ( IntToStr (qry.FieldByName ('a').AsInteger) + ' | '
              + IntToStr (qry.FieldByName('b').AsInteger) );
      qry.Next;
    end;
  end;
  Writeln ('Done!');
end.

As you can see, the code is quite trivial: it just creates a couple of tables (T and Z) and then inserts a record on table T.

The problem is that after calling ExecuteDirect, only the first SQL statement has been executed. Table Z is never created, and no data is inserted into table T.

I guess I could call ExecuteDirect for every single statement, instead of treating them as a block, but that would defeat the purpose of transactions (specially considering that every call to ExecuteDirect ends up starting a new transaction on its own).

So my question is this: how do you commit a block of multiple SQL statements as single transaction?

Regards,
Alex


Additional info:

- fpc: 2.6.0
- lazarus: 0.9.30
- sqlite: 3.7.13
- OS: Debian Wheezy
« Last Edit: April 02, 2014, 06:44:25 am by alemartini »

alemartini

  • Newbie
  • Posts: 4
Ok, I think I got it.

ExecuteDirect will not actually start a new transaction if you already have an open one. So you can call ExecDirect as many times as needed (one per SQL statement), and perform the commit when you're done. I've made some more tests, and it seems to be working as expected.

However... I'm still curious: isn't there a simpler way to do this? For small transactions, making a call to ExecuteDirect for every SQL statement may not be a big deal. However, for larger transactions it gets tiresome real quick.

Is there some sort of limitation on the SQLite side that prevents sending multiple SQL statements in a single step? (perhaps sqlite3_prepare can only handle one statement at a time?)

alemartini

  • Newbie
  • Posts: 4
In case it might be useful for someone else, there's actually a way to perform multiple statements in a single step: have a look at the TSQLite3conn.execsql procedure.

I've modified my example program to use execsql instead of ExecuteDirect, and it worked flawlessly.

And BTW, I haven't tested it thorougly yet, but it seems that by using execsql you can even handle transactions on your own.

So far I have only one doubt left: why is execsql hidden as a protected procedure? Wouldn't it make things easier to leave it as public?
« Last Edit: April 02, 2014, 08:29:43 am by alemartini »

karaba

  • New Member
  • *
  • Posts: 49
I quess it is to be used from a TSQLQuery instead of diercly calling on it. for future reference all SQLDB suite is created for sql server access, to use it you need a connection object a transaction object and a Query object, the connection is linked directly to the server and communicates with it transferring data between the server and your applicationm the transaction manages an sql server transaction it is an easy way to group commands under one go, query is the component that manages the data transferred from the server and the sql commands that are required to edit them (select, update, insert etc). It is not needed to access the ExecSQL method directly and you shouldn't not unless you need to do, on the other hand the executeDirect is there to bypass the transaction concept something to be avoided at all costs and only in the case of "there is no other way to do it" is to be used.

karaba

  • New Member
  • *
  • Posts: 49
I quess it is to be used from a TSQLQuery instead of diercly calling on it. for future reference all SQLDB suite is created for sql server access, to use it you need a connection object a transaction object and a Query object, the connection is linked directly to the server and communicates with it transferring data between the server and your applicationm the transaction manages an sql server transaction it is an easy way to group commands under one go, query is the component that manages the data transferred from the server and the sql commands that are required to edit them (select, update, insert etc). It is not needed to access the ExecSQL method directly and you shouldn't not unless you need to do, on the other hand the executeDirect is there to bypass the transaction concept something to be avoided at all costs and only in the case of "there is no other way to do it" is to be used for mainstream data access application.

alemartini

  • Newbie
  • Posts: 4
I quess it is to be used from a TSQLQuery instead of diercly calling on it. for future reference all SQLDB suite is created for sql server access, to use it you need a connection object a transaction object and a Query object, the connection is linked directly to the server and communicates with it transferring data between the server and your applicationm the transaction manages an sql server transaction it is an easy way to group commands under one go, query is the component that manages the data transferred from the server and the sql commands that are required to edit them (select, update, insert etc). It is not needed to access the ExecSQL method directly and you shouldn't not unless you need to do, on the other hand the executeDirect is there to bypass the transaction concept something to be avoided at all costs and only in the case of "there is no other way to do it" is to be used.

You're talking about ExecSQL while I'm talking about execsql, and that's a completely different thing. I think I have a good understanding of the SQLdb package, its different components and how they interact with each other. And as you can see in my first post, I was asking a very specific thing about a particular use case.

Unless I'm missing the obvious, there's no way to bypass automatic "transactions" by using ExecuteDirect (see my first post and also the code in sqldb.pp which seems pretty clear in that regard).

And I put transactions between quotes, because I'm afraid that the whole concept of transactions is seriously broken (I'm talking about the TSQLTransaction class).

According to the semi-official documentation (Wiki) :

Quote
A SQL statement that is executed by Sqldb must always be executed within a transaction, even if the database system does not support transactions. Also, there are database systems that do support transaction for which TSQLConnection does not (yet) support transaction

So IMHO, what should be avoided at all costs is forcing programmers to use a class that:

a) Is useless for every case where the underlying engine doesn't support transactions
b) Is useless for every case where the class can't handle transactions (even if the chosen database engine supports them)

It's not only pointless, but it's also potentially dangerous with regard to data integrity. It's giving a false sense of safety ("automatic transaction handling") when in several cases it actually does nothing at all.

eny

  • Hero Member
  • *****
  • Posts: 1613
It's not only pointless, but it's also potentially dangerous with regard to data integrity. It's giving a false sense of safety ("automatic transaction handling") when in several cases it actually does nothing at all.
The idea is to have an abstraction layer that makes it easy(-ier) to use to a different RDBMS if desired.
Although I wonder in practice how often that actually happens.
I agree though that it should be crystal clear if and when transactions are actually used 'under the hood', so that you can be sure your dml is ACID compliant.
All posts based on: Win10 (Win64); Lazarus 2.0.10 'stable' (x64) unless specified otherwise...

karaba

  • New Member
  • *
  • Posts: 49
You're talking about ExecSQL while I'm talking about execsql, and that's a completely different thing. I think I have a good understanding of the SQLdb package, its different components and how they interact with each other. And as you can see in my first post, I was asking a very specific thing about a particular use case.

OK I haven't looked closely at the code but my understanding of pascal is that its not case sensitive so ExecSQL and execsql should be the same method. I say should be with regard of overloading and having two methods with the same and different parameters would work so I'll come bakc to it after I take an actual look.

Unless I'm missing the obvious, there's no way to bypass automatic "transactions" by using ExecuteDirect (see my first post and also the code in sqldb.pp which seems pretty clear in that regard).

And I put transactions between quotes, because I'm afraid that the whole concept of transactions is seriously broken (I'm talking about the TSQLTransaction class).


There was a thread a few months back where the users where trying to execute commands outside a transaction in an SQLite database (something about clean or pack the database etc) and it was either changed or a new method introduced (yet again), being the ever optimist I would expect to change the executedirect to support both in and out of transaction commands. Haven't checked it any farther than what it was posted on the forums so I do not know the current status it might not even be released with FPC 2.6.2/4 yet.

According to the semi-official documentation (Wiki) :

Quote
A SQL statement that is executed by Sqldb must always be executed within a transaction, even if the database system does not support transactions. Also, there are database systems that do support transaction for which TSQLConnection does not (yet) support transaction

So IMHO, what should be avoided at all costs is forcing programmers to use a class that:

a) Is useless for every case where the underlying engine doesn't support transactions
b) Is useless for every case where the class can't handle transactions (even if the chosen database engine supports them)

Agreed there is no point on forcing specific work habits to the user base that might not needed or finds it a pain in the behind and opts to not do it or  flat out just because. It is not the place of a library to enforce those things it should be helpful for the proper use but it should not deny to work any other way.

It's not only pointless, but it's also potentially dangerous with regard to data integrity. It's giving a false sense of safety ("automatic transaction handling") when in several cases it actually does nothing at all.

Actually the need for TSQLTransaction arises from the fact that there is no "automatic transaction handling" it forces you to use the object so that you can control the transaction manually, the only automatic thing about it is that it starts the transaction when needed every thing else is the users responsibility. if there was any auto handling then they could have added an auto generator on top of the handling and ignore the missing component.

 

 

TinyPortal © 2005-2018