LiteSQL and Threads

Discussion of open issues, suggestions and bugs regarding LiteDAC (SQLite Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
LHSoft
Posts: 130
Joined: Sat 18 Aug 2012 08:33

LiteSQL and Threads

Post by LHSoft » Sun 04 Aug 2019 12:03

Hello,
in your History you write that Thread behavior is improved.
So I used Verson 3.04 til yesterday, now I have installed v4.01.

In a Thread I create a LiteSQL:

Code: Select all

ProtInsertSQL := TLiteSQL.Create(nil);
    ProtInsertSQL.Connection := DBM.SQLKasse;
    ProtInsertSQL.SQL.Clear;
    ProtInsertSQL.SQL.Add('INSERT INTO KaArtikelProtokoll');
    ProtInsertSQL.SQL.Add('(Zeit,Barcode,PreisVKAlt,PreisVKNeu,Kennzeichen)');
    ProtInsertSQL.SQL.Add('VALUES');
    ProtInsertSQL.SQL.Add('(:Zeit,:Barcode,:PreisVKAlt,:PreisVKNeu,:Kennzeichen)');
    ProtInsertSQL.Params[0].DataType := ftDateTime;
    ProtInsertSQL.Params[1].DataType := ftString;
    ProtInsertSQL.Params[2].DataType := ftCurrency;
    ProtInsertSQL.Params[3].DataType := ftCurrency;
    ProtInsertSQL.Params[4].DataType := ftString;
    ProtInsertSQL.Params.ValueCount := 1;
The Connection DBM.SQLKasse is not created in this thread but on a Datamodul.

I Fill the Values of the LiteSQL:

Code: Select all

i := ProtInsertSQL.Params.ValueCount - 1;
  ProtInsertSQL.Params[0][i].AsDateTime := Zeit;
  ProtInsertSQL.Params[1][i].AsString := Barcode;
  ProtInsertSQL.Params[2][i].AsCurrency := oldPreis;
  ProtInsertSQL.Params[3][i].AsCurrency := newPreis;
  ProtInsertSQL.Params[4][i].AsString := Kennzeichen;
  ProtInsertSQL.Params.ValueCount := ProtInsertSQL.Params.ValueCount + 1;
If running the line

Code: Select all

ProtInsertSQL.Execute(ProtInsertSQL.Params.ValueCount - 1);
in version 4.01 the program crashes, not so in version 3.04

Because you changed the bahavior in threads can you tell me what I have to Change now to get this work again.
Because the Connection is in exclusive mode (see below), I think I can not create a seperate Connection within the thread.
Also I thought about a synchronize for execute.

The Connection is set like this:

Code: Select all

procedure TDBM.SQLKasseBeforeConnect(Sender: TObject);
begin
  SQLKasse.LoginPrompt := False;
  SQLKasse.Database :=  'xy.db3';
  SQLKasse.EncryptionKey := '***';
  SQLKasse.Options.EncryptionAlgorithm := TLiteEncryptionAlgorithm.leBlowfish;
  SQLKasse.Options.Direct := True;
  SQLKasse.Options.UseUnicode := True;
end;

Code: Select all

procedure TDBM.SQLKasseAfterConnect(Sender: TObject);
begin
  SQLKasse.ExecSQL('PRAGMA page_size=4096');
  SQLKasse.ExecSQL('vacuum');
  SQLKasse.ExecSQL('PRAGMA journal_mode=WAL');
  SQLKasse.ExecSQL('PRAGMA locking_mode = EXCLUSIVE');
  SQLKasse.ExecSQL('BEGIN EXCLUSIVE');
  SQLKasse.ExecSQL('COMMIT');
  SQLKasse.ExecSQL('PRAGMA cell_size_check = TRUE');

LHSoft
Posts: 130
Joined: Sat 18 Aug 2012 08:33

Re: LiteSQL and Threads

Post by LHSoft » Sun 04 Aug 2019 13:55

the crash occurs in liteStatic, Line 1575:

Code: Select all

function localtime(OOQ00QCOQ0:pointer):ptm;
begin
Result:=malloc(SizeOf(stm));
OO00CQCOQ0(integer(OOQ00QCOQ0^),Result);
end;
So reason may not be Thread but a Trigger in the table.

This is the Table:

Code: Select all

CREATE TABLE [KaArtikelProtokoll](
  [Zeit] DATETIME, 
  [Barcode] NVARCHAR(13), 
  [PreisVKAlt] FLOAT(10, 2), 
  [PreisVKNeu] FLOAT(10, 2), 
  [Kennzeichen] NVARCHAR(1), 
  UNIQUE([Zeit], [Barcode]) ON CONFLICT IGNORE);

CREATE TRIGGER prot_insert AFTER INSERT
ON KaArtikelProtokoll
BEGIN
   UPDATE lastChange SET ChangeTime=datetime('now', 'localtime');
END;
The Trigger works til v3.04.10, now it causes crash.
Removing the trigger, there is no crash in 4.00.01.

Whats wrong?
Last edited by LHSoft on Sun 04 Aug 2019 17:37, edited 1 time in total.

LHSoft
Posts: 130
Joined: Sat 18 Aug 2012 08:33

Re: LiteSQL and Threads

Post by LHSoft » Sun 04 Aug 2019 17:17

deleted

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: LiteSQL and Threads

Post by MaximG » Fri 09 Aug 2019 13:10

Thank you for the information. We will investigate the described issue and let you know the results shortly.

LHSoft
Posts: 130
Joined: Sat 18 Aug 2012 08:33

Re: LiteSQL and Threads

Post by LHSoft » Mon 07 Oct 2019 22:12

Hello,
any results why triggers do not work?
I still can not use actual Version because triggers do not work

LHSoft
Posts: 130
Joined: Sat 18 Aug 2012 08:33

Re: LiteSQL and Threads

Post by LHSoft » Thu 31 Oct 2019 02:11

MaximG wrote: Fri 09 Aug 2019 13:10 Thank you for the information. We will investigate the described issue and let you know the results shortly.
This was August.
I'm waiting for a reply since then

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: LiteSQL and Threads

Post by MaximG » Mon 04 Nov 2019 12:08

We've reproduced the issue and fixed it. The fix will be included in the next build of our product. As a workaround, we can send you a night LiteDAC build including the required changes. For this provide us with your license number and IDE version you are interested in For your convenience, please use the e-support form https://www.devart.com/company/contactform.html

LHSoft
Posts: 130
Joined: Sat 18 Aug 2012 08:33

Re: LiteSQL and Threads

Post by LHSoft » Mon 04 Nov 2019 15:03

Thanks, ist OK to know that the problem is fixed, so I wait for next release, hope comming soon
best regards
hans

Post Reply