Recent

Author Topic: [Solved] Debugging SQlite  (Read 6022 times)

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 917
[Solved] Debugging SQlite
« on: January 18, 2022, 02:27:54 pm »
I create a long SQL statement, set all the parameters, execute it, and ... nothing happens. Or, when I insert a record that has a "Integer Primary Key Autoincrement", which should be an alias for RowID and should get a number automatically, when you don't insert it or insert "NULL", the value stays "NULL", in both cases.

So, I made a small testapp, to see if it all works as expected. It does. I double-check and triple-check my SQL statements and parameters. They are fine. I execute the statements, and ... noting happens. The tables are still mostly empty or contain many records that are mostly empty. Or the Primary Key is NULL for all records. No errors, though.

I change my insert and update queries and break them up into smaller ones that cannot go wrong. Or so I thought. There is little difference. I start first inserting records that only include the unique values other than the AutoInc primary key. Afterwards I update them and insert all the values. That results in tables filled with records without a primary key and only those other, unique values. No errors.

I log every action and see lots of rollbacks. I start committing each and every individual action immediately. More data enters the database, but most tables still have only NULL-values as AutoInc primary keys. Still no errors.

What more can I do?

I think I'll scrap the whole setup and only use the TSQLQuery datasets. See if that works. I'm not happy with the performance of databases in FPC, while I thought that was what it did best.
« Last Edit: January 19, 2022, 12:42:28 pm by SymbolicFrank »

Zvoni

  • Hero Member
  • *****
  • Posts: 1185
Re: Debugging SQlite
« Reply #1 on: January 18, 2022, 02:38:54 pm »
You do know, that SQLite has a function which returns the SQL-Statement AFTER Parameters are set (To check if the Statement makes "sense")?
https://forum.lazarus.freepascal.org/index.php/topic,54225.msg402674.html#msg402674

Show your INSERT-Statement that fails

btw: You should avoid using AUTOINCREMENT --> https://www.sqlite.org/autoinc.html
« Last Edit: January 18, 2022, 02:53:07 pm by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 917
Re: Debugging SQlite
« Reply #2 on: January 18, 2022, 03:04:09 pm »
Well, I reduced the insert statements to this:

Code: [Select]
Commit:   Committing transaction
Prepare:  TQuerySQLStatement : insert or ignore into kenteken (ConversionKey)
values  (:ConversionKey);
Execute:  TQuerySQLStatement : insert or ignore into kenteken (ConversionKey)
values  (:ConversionKey);
Commit:   Committing transaction

That works, except that all my AutoInc primary keys are NULL.

It is followed by an update statement of 5429 chars, which is just:

Code: [Select]
update kenteken set fieldname = :fieldname, ... where ConversionKey = :ConversionKey;
The log says:

Code: [Select]
Commit:   Committing transaction
And it is, except that my primary key is NULL for all records.

At first, I was using:

Code: [Select]
insert into kenteken (<very many fields>)
values (<all the parameters>)
on conflict (ConversionKey) do
update set (Field1 = excluded.Field1, ...)
where ConversionKey = : ConversionKey;

But that resulted in empty tables. I have tried a few other variants, but this is the one that works best (for a certain value of 'works').

That "ConversionKey" is the primary key from the external data source. It is far easier and faster to add that to the table, but they don't have to be Integers (in this case, they're GUIDs), and I need those integer keys for the rest of the database.

And yes, I do understand that I don't have to use primary keys and autoincrement, or any other constraints and that I could add those values myself, but I would want to have foreign keys and a functional, relational model. It's far easier to have the database enforce integrity and do the lookups (joins), than having to do that all by myself.

Zvoni

  • Hero Member
  • *****
  • Posts: 1185
Re: Debugging SQlite
« Reply #3 on: January 18, 2022, 03:26:51 pm »
1) I think you misunderstand the way AUTOINC works in SQLite. Read through my Link above
2) What are the properties of your PK-Field? I'm specifically asking for NOT NULL (if you get my drift.....) --> Could you post your CREATE TABLE-Statement?

EDIT: Since you mentioned GUID's: --> https://stackoverflow.com/questions/18954130/can-we-use-guid-as-a-primary-key-in-sqlite-database/23531060
« Last Edit: January 18, 2022, 03:32:42 pm by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 917
Re: Debugging SQlite
« Reply #4 on: January 18, 2022, 04:03:56 pm »
1) I think you misunderstand the way AUTOINC works in SQLite. Read through my Link above
I don't understand it. Can you explain?

Quote
2) What are the properties of your PK-Field? I'm specifically asking for NOT NULL (if you get my drift.....) --> Could you post your CREATE TABLE-Statement?

Code: Pascal  [Select][+][-]
  1. const
  2.   UniqueFields: string = 'kenteken=kenteken';
  3.  
  4. function Empty(const s: string): Boolean;
  5. begin
  6.   if Length(Trim(s)) = 0 then Result := True
  7.   else Result := False;
  8. end;
  9.  
  10. function ExecuteCommand(ThisCommand: string; ThisConnection: TSQLConnection): Boolean;
  11. begin
  12.   Result := False;
  13.  
  14.   if (not Assigned(ThisConnection)) or Empty(ThisCommand) then Exit;
  15.  
  16.   ThisConnection.ExecuteDirect(ThisCommand);
  17.   ThisConnection.Transaction.Commit;
  18.   Result := True;
  19. end;
  20.  
  21. function FieldToStr(ThisField: TField): string;
  22. begin
  23.   Result := '';
  24.  
  25.   if not Assigned(ThisField) then Exit;
  26.  
  27.   Result := ThisField.FieldName;
  28.  
  29.   case ThisField.DataType of
  30.     ftFixedChar, ftFixedWideChar:
  31.       Result := 'char[' + IntToStr(ThisField.Size) + ']';
  32.     ftString, ftWideString:
  33.       begin
  34.         if ThisField.Size <= 50 then
  35.           Result := Result + ' char[' + IntToStr(ThisField.Size) + ']'
  36.         else
  37.           Result := Result + ' varchar[' + IntToStr(ThisField.Size) + ']';
  38.       end;
  39.     else
  40.       Result := Result + ' ' + FieldTypeNames[ThisField.DataType];
  41.   end;
  42.  
  43.  
  44.   if (ThisField.DataType = ftAutoInc) or (ThisField is TAutoIncField) then
  45.     Result := Result + ' Primary Key';
  46. end;
  47.  
  48. function CreateSQLTable(TableName: string; SourceTable: TDataSet;
  49.   ThisConnection: TSQLConnection; AddConversionField: Boolean = False): Boolean;
  50. var
  51.   q: string;
  52.   i: Integer;
  53.   f: TField;
  54.   PK, UF: TStringList;
  55.   s: string;
  56. begin
  57.   Result := False;
  58.  
  59.   if Empty(TableName) then Exit;
  60.   if not Assigned(SourceTable) then Exit;
  61.   if SourceTable.Fields.Count = 0 then Exit;
  62.  
  63.   TableName := ChangeFileExt(TableName, '');
  64.  
  65.   if SQLTableExists(TableName, ThisConnection) then
  66.     if not ExecuteCommand('drop table ' + TableName + ';', ThisConnection) then
  67.       Exit;
  68.  
  69.   q := 'create table ' + TableName + ' (';
  70.  
  71.   for i := 0 to SourceTable.Fields.Count - 1 do
  72.   begin
  73.     f := SourceTable.Fields[i];
  74.     q := q + FieldToStr(f);
  75.     if i < SourceTable.Fields.Count - 1 then q := q + ', ';
  76.   end;
  77.  
  78.   // Field to keep the key from the exernal datasource
  79.   if AddConversionField then q := q + ', ' + ConversionFieldName + ' ' +
  80.     ConversionFieldType;
  81.  
  82.   // No AutoInc, use lookup table
  83.   if Pos('primary key', LowerCase(q)) < 1 then
  84.   begin
  85.     PK := TStringList.Create;
  86.     PK.Text := PrimaryKeys;
  87.     PK.CaseSensitive := False;
  88.     s := PK.Values[TableName];
  89.     if Length(s) > 0 then q := q + ', Primary Key (' + s + ')';
  90.     PK.Free;
  91.   end;
  92.  
  93.   // Another lookup table, just a single entry here
  94.   UF := TStringList.Create;
  95.   UF.Text := UniqueFields;
  96.   UF.CaseSensitive := False;
  97.   s := UF.Values[TableName];
  98.   if not Empty(s) then s := s + ', ';
  99.   s := s + ConversionFieldName;
  100.   q := q + ', Unique (' + s + ')';
  101.   UF.Free;
  102.  
  103.   q := q + ');';
  104.  
  105.   Result := ExecuteCommand(q, ThisConnection);
  106. end;

I import other (DBF) tables into the SQL database. The structure should be the same, except with an extra, unique field (that external Key, the GUID). Although the structure depends on the SQlite version, as the older versions only had 4 field types and the newer versions (since a month or so) also have all the other field types you would expect an SQL database to have. Although you can only see that in the release notes and with a tool like SQliteStudio, the documentation hasn't been updated yet.

The resulting primary keys are "Integer Primary Key Autoincrement" in the created table.
« Last Edit: January 18, 2022, 04:05:39 pm by SymbolicFrank »

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 917
Re: Debugging SQlite
« Reply #5 on: January 18, 2022, 05:35:11 pm »
Ok, after some more debugging and looking at the code in the SQlite-specific units, my conclusion is that the current FPC (trunk) is not compatible with the new versions of SQlite.

Back to the drawing board.

dseligo

  • Hero Member
  • *****
  • Posts: 580
Re: Debugging SQlite
« Reply #6 on: January 18, 2022, 09:12:10 pm »
I think I'll scrap the whole setup and only use the TSQLQuery datasets.

What do you use to access SQlite (if not TSQLQuery)?

This code:
Code: Pascal  [Select][+][-]
  1.   SQLiteDefaultLibrary := 'sqlite3.37.2.dll';
  2.  
  3.   SQLite3Connection1.DatabaseName := 'autoinc.db3';
  4.   SQLite3Connection1.Connected := True;
  5.  
  6.   SQLQuery1.SQL.Text := 'create table autoinc (id integer, data integer, primary key (id)) ';
  7.   SQLQuery1.ExecSQL;
  8.  
  9.   SQLQuery1.SQL.Text := 'insert into autoinc (data) values (100)';
  10.   SQLQuery1.ExecSQL;
  11.   SQLQuery1.SQL.Text := 'insert into autoinc (data) values (200)';
  12.   SQLQuery1.ExecSQL;
  13.   SQLQuery1.SQL.Text := 'insert into autoinc (data) values (300)';
  14.   SQLQuery1.ExecSQL;
  15.  
  16.   SQLQuery1.SQL.Text := 'update autoinc set data = 250 where id = 2';
  17.   SQLQuery1.ExecSQL;
  18.  
  19.   SQLQuery1.SQL.Text := 'select * from autoinc';
  20.   SQLQuery1.Open;

Produces:
Code: Text  [Select][+][-]
  1.   id | data
  2. -----+------
  3.    1 |  100
  4.    2 |  250
  5.    3 |  300

This is with latest SQlite3 library (3.37.2), Lazarus 2.2.0 (rev lazarus_2_2_0) FPC 3.2.2 x86_64-win64-win32/win64, Windows 11.

Zvoni

  • Hero Member
  • *****
  • Posts: 1185
Re: Debugging SQlite
« Reply #7 on: January 18, 2022, 09:25:15 pm »
Frank, I‘m following a hunch: what‘s the value of „result“ in your lines 40 and 45?
Resp. the value of „q“ in line 103?
« Last Edit: January 18, 2022, 09:26:52 pm by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

dseligo

  • Hero Member
  • *****
  • Posts: 580
Re: Debugging SQlite
« Reply #8 on: January 18, 2022, 09:49:46 pm »
Same results with ZEOS components:
Code: Pascal  [Select][+][-]
  1.   ZConnection1.Database := 'autoincz.db3';
  2.   ZConnection1.Protocol := 'sqlite-3';
  3.   ZConnection1.LibraryLocation := 'sqlite3.37.2.dll';
  4.  
  5.   ZQuery1.Connection := ZConnection1;
  6.  
  7.   ZQuery1.SQL.Text := 'create table autoinc (id integer, data integer, primary key (id)) ';
  8.   ZQuery1.ExecSQL;
  9.  
  10.   ZQuery1.SQL.Text := 'insert into autoinc (data) values (100)';
  11.   ZQuery1.ExecSQL;
  12.   ZQuery1.SQL.Text := 'insert into autoinc (data) values (200)';
  13.   ZQuery1.ExecSQL;
  14.   ZQuery1.SQL.Text := 'insert into autoinc (data) values (300)';
  15.   ZQuery1.ExecSQL;
  16.  
  17.   ZQuery1.SQL.Text := 'update autoinc set data = 250 where id = 2';
  18.   ZQuery1.ExecSQL;
  19.  
  20.   ZQuery1.SQL.Text := 'select * from autoinc';
  21.   ZQuery1.Open;

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 917
Re: Debugging SQlite
« Reply #9 on: January 19, 2022, 10:18:38 am »
I think I'll scrap the whole setup and only use the TSQLQuery datasets.

What do you use to access SQlite (if not TSQLQuery)?

This code:
Code: Pascal  [Select][+][-]
  1.   SQLiteDefaultLibrary := 'sqlite3.37.2.dll';
  2.  
  3.   SQLite3Connection1.DatabaseName := 'autoinc.db3';
  4.   SQLite3Connection1.Connected := True;
  5.  
  6.   SQLQuery1.SQL.Text := 'create table autoinc (id integer, data integer, primary key (id)) ';
  7.   SQLQuery1.ExecSQL;
  8.  
  9.   SQLQuery1.SQL.Text := 'insert into autoinc (data) values (100)';
  10.   SQLQuery1.ExecSQL;
  11.   SQLQuery1.SQL.Text := 'insert into autoinc (data) values (200)';
  12.   SQLQuery1.ExecSQL;
  13.   SQLQuery1.SQL.Text := 'insert into autoinc (data) values (300)';
  14.   SQLQuery1.ExecSQL;
  15.  
  16.   SQLQuery1.SQL.Text := 'update autoinc set data = 250 where id = 2';
  17.   SQLQuery1.ExecSQL;
  18.  
  19.   SQLQuery1.SQL.Text := 'select * from autoinc';
  20.   SQLQuery1.Open;

Produces:
Code: Text  [Select][+][-]
  1.   id | data
  2. -----+------
  3.    1 |  100
  4.    2 |  250
  5.    3 |  300

This is with latest SQlite3 library (3.37.2), Lazarus 2.2.0 (rev lazarus_2_2_0) FPC 3.2.2 x86_64-win64-win32/win64, Windows 11.

I made a test (attached). It is roughly what you did as well. It works with the newest version of the SQlite DLL. If I scale it up and use complex statements with lots of parameters (the average table has more than 100 fields), it stops working. I expect that fields don't work anymore. Like, when debugging, the code thinks that my Integer Primary Key Autoincrement field is actually a String. Which would be logical, as that is the big change from a few months ago.

I'm now testing with an old version of the SQLite DLL (I think the one from OpenOffice, as you cannot download an old binary). It's the one in the 7z.

Zvoni

  • Hero Member
  • *****
  • Posts: 1185
Re: Debugging SQlite
« Reply #10 on: January 19, 2022, 10:20:41 am »
Frank, use the current SQLite-Lib, and post the Value of "q" from Line 103 from your sample-code above.
I have a hunch what might be the "Problem"
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 917
Re: Debugging SQlite
« Reply #11 on: January 19, 2022, 10:26:33 am »
Frank, I‘m following a hunch: what‘s the value of „result“ in your lines 40 and 45?
Resp. the value of „q“ in line 103?

FieldTypeNames returns  the field type as string.

'q' is a string of a few kilobytes (depending on the table) with the 'create table' statement. That works, as it's a plain SQL statement without parameters. The tables are created as expected. I check them out with SQliteStudio.

It's a database from a commercial product, so I cannot show it in detail.

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 917
Re: Debugging SQlite
« Reply #12 on: January 19, 2022, 10:40:15 am »
Question: should I use a single TSQLTransaction for everything, with Commit or CommitRetaining? Or give each action its own transaction?

To answer my own question: more than one transactions in the same connection gives an error message: 'cannot start another transaction inside a transaction' or something like that.
« Last Edit: January 19, 2022, 10:47:27 am by SymbolicFrank »

Zvoni

  • Hero Member
  • *****
  • Posts: 1185
Re: Debugging SQlite
« Reply #13 on: January 19, 2022, 10:47:16 am »
OK,
returning to the Link to the SQLite-Docs:
An integer Primary Key (with/without AUTOINCREMENT Keyword) will only work if the DataType of that COLUMN is INTEGER (Not Int, SmallInt or whatever else)
Any other DataType used (and that's my hunch since you use FieldTypeNames) ALLOWS NULL in the Primary Key.
It would be enough to get just the part of your CREATE TABLE statement where you define the Primary key

Try:
In your code-sample starting in Line 29 you have a Case-Block checking DataTypes, followed by a separate If-Check for ftAutoInc (outside the Case-Block)
Move the If-Check for ftAutoInc into the Case-Block, and set the Datatype explicitly (and delete the If-Check outside the case-Block)
Result:=Result + ' INTEGER PRIMARY KEY AUTOINCREMENT';

And run your test again.
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Zvoni

  • Hero Member
  • *****
  • Posts: 1185
Re: Debugging SQlite
« Reply #14 on: January 19, 2022, 10:48:55 am »
Question: should I use a single TSQLTransaction for everything, with Commit or CommitRetaining? Or give each action its own transaction?

To answer my own question: more than one transactions in the same connection gives an error message: 'cannot start another transaction inside a transaction' or something like that.
If it's not Multi-user/thread, then a single Transaction is enough. Just run the "Actions" sequentially
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

 

TinyPortal © 2005-2018