[SOLVED] SQLite - multiple commands in ExecSQL

[Dibo]

Hi,

I'm testing SQLite components. I have TSQLite3Connection and TSQLQuery and this code:

Code: Pascal  [Select][+][-]

1.   sl := TStringList.Create;
2.   try
3.     for i:=0 to 100 do
4.       sl.Add('insert into test_table(test_field) values('+QuotedStr('some_value_'+IntToStr(i))+');');
5.     SQLite3Connection1.ExecuteDirect(sl.Text);
6.   finally
7.     sl.Free;
8.   end;
9.  

But only first command is executed (I see only one record). I'm using ";" as separator, if I don't use it then I get syntax error. Same thing if I use SQLQuery:

Code: Pascal  [Select][+][-]

1. for i:=0 to 100 do
2.    SQLQuery1.Add('insert into test_table(test_field) values('+QuotedStr('some_value_'+IntToStr(i))+');');
3. SQLQuery1.ExecSQL;
4.  

But if I call ExecSQL on each loop, then it work. Note that with TSQLite3Dataset this "bulk insert" worked fine, but I had to move to SQLiteConnection

Regards

[BigChimp]

You're right; AFAIK, sqldb does not support executing multiple statements at once.

You can use parameterized queries to improve performance.
See e.g here:
http://www.lazarus.freepascal.org/index.php?topic=19007.msg107801#msg107801
and the wiki; edit, e.g.
http://wiki.lazarus.freepascal.org/Working_With_TSQLQuery#Parameters_in_TSQLQuery.SQL
http://wiki.lazarus.freepascal.org/SQLdb_Tutorial3#Parameterized_queries

[BigChimp]

So you decided on Sqlite for your indexing program then

[Dibo]

Wow, I know about Params and used it often in my old work on delphi 7 but I didn't know that it speed up execution so much. Insert 25000 records takes 650 ms when I called ExecSQL on each loop which was a bit longer than using TSQLite3Dataset (with multiple commands it took 340 ms). But now with params it takes 260 ms . This is enough for me.

So you decided on Sqlite for your indexing program then

I'm still testing . I'm thinking about Firebird which also can work as "embedded". But I don't need such complex database. The only one thing would convince me now to Firebird. Long time ago I saw on this forum source of fbclient.dll/.so ported to free pascal. But can't find it

[BigChimp]

Nice speed increase

I'm still testing . I'm thinking about Firebird which also can work as "embedded". But I don't need such complex database. The only one thing would convince me now to Firebird. Long time ago I saw on this forum source of fbclient.dll/.so ported to free pascal. But can't find it

Nope, AFAIK the sources were never ported to Pascal. You may be thinking of some construction where the dlls were compressed into a resource that was loaded with your executable.

[Dibo]

Nice speed increase

Yep . This is interesting that params are faster than ready composed string. Though params must be parsed etc. and finaly replaced in SQL. I always thought that params are for programmers - less code, clarity

Nope, AFAIK the sources were never ported to Pascal. You may be thinking of some construction where the dlls were compressed into a resource that was loaded with your executable.

Hmm, maybe. It was very long time ago

[BigChimp]

Nice speed increase

Yep . This is interesting that params are faster than ready composed string. Though params must be parsed etc. and finaly replaced in SQL. I always thought that params are for programmers - less code, clarity

Now you know why I rant at everybody telling them they should use parameterized queries

Nope, AFAIK the sources were never ported to Pascal. You may be thinking of some construction where the dlls were compressed into a resource that was loaded with your executable.

Hmm, maybe. It was very long time ago

Does this page look familiar??
http://wiki.freepascal.org/fb_embedded2_Laz

[Dibo]

Does this page look familiar??
http://wiki.freepascal.org/fb_embedded2_Laz

Yes! This is it. But only for windows. So I stay with SQLight. I'm even glad of this, after all day testing of SQLite it finally meets my expectations. Thanks

[BigChimp]

Just to confirm/clarify: Firebird embedded can also be used on Linux etc. See http://wiki.lazarus.freepascal.org/Firebird_embedded

The page I linked to describes a procedure to add the Firebird embedded DLLs as a resource added to the executable so you don't need to distribute dlls.

I updated that wiki page with the same warning...

Edit: clarified

[Dibo]

Yes yes I know

[Cuniculophile]

I am working with Lazarus and SQlite3 for little time but I just had a good experience on updating each line of big table this way :

var
    cnx : TZConnection (or whatever)
    query : TQuery (or what's U like best)


code

query.connection := cnx; // blabla...

query.sql.text := 'BEGIN TRANSACTION';
Query.ExecSQL;

while (it's necessary) do
begin
    Query.SQL.text := 'SQL QUERY FOR whatever you need to change in the base';
    Query.ExecSQL;                                 
end;
 
Query.SQL.Text := 'COMMIT';
Query.ExecSQL;

// Done... Just housekeeping left.


The commands you want to apply are temporalily stored the queries in a file next to the database. Then, SQlite3 considers all this SQL code at once. I suppose there is some sort of optimization process cause the execution is rather performant (considering the kind of database structure)

Note : You must make sure there is at leat one command between 'BEGIN TRANSACTION' and 'COMMIT', otherwise you may get an error in return. I use to insert the 'BEGIN TRANSACTION' when the program meets the first change that really needs to be done.

I'm not sure this is useful nor if someone else have given a better answer since I did'nt read all the messages.

Enjoy coding...