[SOLVED] SQLite - multiple commands in ExecSQL
lazarus / 16-Databases

----------------------------------------------------------------------
Dibo · 2012-12-18 15:29

Hi,

I'm testing SQLite components. I have TSQLite3Connection and TSQLQuery
and this code:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   sl :=
TStringList.Create;  try    for i:=0 to 100 do      sl.Add('insert
into test_table(test_field)
values('+QuotedStr('some_value_'+IntToStr(i))+');');   
SQLite3Connection1.ExecuteDirect(sl.Text);  finally    sl.Free;  end;
 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i for i:=0 to
100 do   SQLQuery1.Add('insert into test_table(test_field) values('+Qu
otedStr('some_value_'+IntToStr(i))+');');SQLQuery1.ExecSQL; 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 · 2012-12-18 15:33

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#msg1
07801
and the wiki; edit, e.g.
http://wiki.lazarus.freepascal.org/Working_With_TSQLQuery#Parameters_i
n_TSQLQuery.SQL
http://wiki.lazarus.freepascal.org/SQLdb_Tutorial3#Parameterized_queri
es

----------------------------------------------------------------------
BigChimp · 2012-12-18 15:34

So you decided on Sqlite for your indexing program then

----------------------------------------------------------------------
Dibo · 2012-12-18 16:05

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.
Quote from: BigChimp on December 18, 2012, 04:34:06 pmSo 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 · 2012-12-18 16:16

Nice speed increase

Quote from: Dibo on December 18, 2012, 05:05:52 pmI'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 · 2012-12-18 16:25

Quote from: BigChimp on December 18, 2012, 05:16:44 pmNice 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
QuoteNope, 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 · 2012-12-18 16:28

Quote from: Dibo on December 18, 2012, 05:25:12 pmQuote from: BigChimp
on December 18, 2012, 05:16:44 pmNice 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

Quote from: Dibo on December 18, 2012, 05:25:12 pmQuoteNope, 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 · 2012-12-18 16:34

Quote from: BigChimp on December 18, 2012, 05:28:34 pmDoes 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 · 2012-12-18 16:39

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 · 2012-12-18 16:42

Yes yes I know

----------------------------------------------------------------------
Cuniculophile · 2013-01-01 15:19

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