Recent

Author Topic: [SOLVED] SQLite - multiple commands in ExecSQL  (Read 15789 times)

Dibo

  • Hero Member
  • *****
  • Posts: 1048
[SOLVED] SQLite - multiple commands in ExecSQL
« on: December 18, 2012, 04:29:39 pm »
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
« Last Edit: December 18, 2012, 05:44:29 pm by Dibo »

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: SQLite - multiple commands in ExecSQL
« Reply #1 on: December 18, 2012, 04:33:44 pm »
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
« Last Edit: December 18, 2012, 04:36:45 pm by BigChimp »
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: SQLite - multiple commands in ExecSQL
« Reply #2 on: December 18, 2012, 04:34:06 pm »
So you decided on Sqlite for your indexing program then ;)
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

Dibo

  • Hero Member
  • *****
  • Posts: 1048
Re: SQLite - multiple commands in ExecSQL
« Reply #3 on: December 18, 2012, 05:05:52 pm »
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 :P . 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

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: SQLite - multiple commands in ExecSQL
« Reply #4 on: December 18, 2012, 05:16:44 pm »
Nice speed increase ;)

I'm still testing :P . 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.
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

Dibo

  • Hero Member
  • *****
  • Posts: 1048
Re: SQLite - multiple commands in ExecSQL
« Reply #5 on: December 18, 2012, 05:25:12 pm »
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
Quote
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

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: SQLite - multiple commands in ExecSQL
« Reply #6 on: December 18, 2012, 05:28:34 pm »
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 ;)

Quote
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
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

Dibo

  • Hero Member
  • *****
  • Posts: 1048
Re: SQLite - multiple commands in ExecSQL
« Reply #7 on: December 18, 2012, 05:34:59 pm »
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

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: SQLite - multiple commands in ExecSQL
« Reply #8 on: December 18, 2012, 05:39:21 pm »
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
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

Dibo

  • Hero Member
  • *****
  • Posts: 1048
Re: SQLite - multiple commands in ExecSQL
« Reply #9 on: December 18, 2012, 05:42:11 pm »
Yes yes I know ;)

Cuniculophile

  • Newbie
  • Posts: 1
Re: [SOLVED] SQLite - multiple commands in ExecSQL
« Reply #10 on: January 01, 2013, 04:19:43 pm »
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...

 

TinyPortal © 2005-2018