[Solved] Debugging SQlite
lazarus / 16-Databases

----------------------------------------------------------------------
SymbolicFrank · 2022-01-18 13:27

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.

----------------------------------------------------------------------
Zvoni · 2022-01-18 13:38

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.h
tml#msg402674

Show your INSERT-Statement that fails

btw: You should avoid using AUTOINCREMENT -->
https://www.sqlite.org/autoinc.html

----------------------------------------------------------------------
SymbolicFrank · 2022-01-18 14:04

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 · 2022-01-18 14:26

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

----------------------------------------------------------------------
SymbolicFrank · 2022-01-18 15:03

Quote from: Zvoni on January 18, 2022, 03:26:51 pm1) I think you
misunderstand the way AUTOINC works in SQLite. Read through my Link
aboveI don't understand it. Can you explain?

Quote2) 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i const 
UniqueFields: string = 'kenteken=kenteken'; function Empty(const s:
string): Boolean;begin  if Length(Trim(s)) = 0 then Result := True 
else Result := False;end; function ExecuteCommand(ThisCommand: string;
ThisConnection: TSQLConnection): Boolean;begin  Result := False;   if
(not Assigned(ThisConnection)) or Empty(ThisCommand) then Exit;  
ThisConnection.ExecuteDirect(ThisCommand); 
ThisConnection.Transaction.Commit;  Result := True;end; function
FieldToStr(ThisField: TField): string;begin  Result := '';   if not
Assigned(ThisField) then Exit;   Result := ThisField.FieldName;   case
ThisField.DataType of    ftFixedChar, ftFixedWideChar:      Result :=
'char[' + IntToStr(ThisField.Size) + ']';    ftString, ftWideString: 
   begin        if ThisField.Size <= 50 then          Result := Result
+ ' char[' + IntToStr(ThisField.Size) + ']'        else         
Result := Result + ' varchar[' + IntToStr(ThisField.Size) + ']';     
end;    else      Result := Result + ' ' +
FieldTypeNames[ThisField.DataType];  end;    if (ThisField.DataType =
ftAutoInc) or (ThisField is TAutoIncField) then    Result := Result +
' Primary Key';end; function CreateSQLTable(TableName: string;
SourceTable: TDataSet;  ThisConnection: TSQLConnection;
AddConversionField: Boolean = False): Boolean;var  q: string;  i:
Integer;  f: TField;  PK, UF: TStringList;  s: string;begin  Result :=
False;   if Empty(TableName) then Exit;  if not Assigned(SourceTable)
then Exit;  if SourceTable.Fields.Count = 0 then Exit;   TableName :=
ChangeFileExt(TableName, '');   if SQLTableExists(TableName,
ThisConnection) then    if not ExecuteCommand('drop table ' +
TableName + ';', ThisConnection) then      Exit;   q := 'create table
' + TableName + ' (';   for i := 0 to SourceTable.Fields.Count - 1 do 
begin    f := SourceTable.Fields[i];    q := q + FieldToStr(f);    if
i < SourceTable.Fields.Count - 1 then q := q + ', ';  end;   // Field
to keep the key from the exernal datasource  if AddConversionField
then q := q + ', ' + ConversionFieldName + ' ' +   
ConversionFieldType;   // No AutoInc, use lookup table  if
Pos('primary key', LowerCase(q)) < 1 then  begin    PK :=
TStringList.Create;    PK.Text := PrimaryKeys;    PK.CaseSensitive :=
False;    s := PK.Values[TableName];    if Length(s) > 0 then q := q +
', Primary Key (' + s + ')';    PK.Free;  end;   // Another lookup
table, just a single entry here  UF := TStringList.Create;  UF.Text :=
UniqueFields;  UF.CaseSensitive := False;  s := UF.Values[TableName]; 
if not Empty(s) then s := s + ', ';  s := s + ConversionFieldName;  q
:= q + ', Unique (' + s + ')';  UF.Free;   q := q + ');';   Result :=
ExecuteCommand(q, ThisConnection);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.

----------------------------------------------------------------------
SymbolicFrank · 2022-01-18 16:35

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 · 2022-01-18 20:12

Quote from: SymbolicFrank on January 18, 2022, 02:27:54 pmI 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
SQLiteDefaultLibrary := 'sqlite3.37.2.dll';  
SQLite3Connection1.DatabaseName := 'autoinc.db3'; 
SQLite3Connection1.Connected := True;   SQLQuery1.SQL.Text := 'create
table autoinc (id integer, data integer, primary key (id)) '; 
SQLQuery1.ExecSQL;   SQLQuery1.SQL.Text := 'insert into autoinc (data)
values (100)';  SQLQuery1.ExecSQL;  SQLQuery1.SQL.Text := 'insert into
autoinc (data) values (200)';  SQLQuery1.ExecSQL;  SQLQuery1.SQL.Text
:= 'insert into autoinc (data) values (300)';  SQLQuery1.ExecSQL;  
SQLQuery1.SQL.Text := 'update autoinc set data = 250 where id = 2'; 
SQLQuery1.ExecSQL;   SQLQuery1.SQL.Text := 'select * from autoinc'; 
SQLQuery1.Open;
Produces:
Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   id |
data-----+------   1 |  100   2 |  250   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 · 2022-01-18 20:25

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?

----------------------------------------------------------------------
dseligo · 2022-01-18 20:49

Same results with ZEOS components:
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  
ZConnection1.Database := 'autoincz.db3';  ZConnection1.Protocol :=
'sqlite-3';  ZConnection1.LibraryLocation := 'sqlite3.37.2.dll';  
ZQuery1.Connection := ZConnection1;   ZQuery1.SQL.Text := 'create
table autoinc (id integer, data integer, primary key (id)) '; 
ZQuery1.ExecSQL;   ZQuery1.SQL.Text := 'insert into autoinc (data)
values (100)';  ZQuery1.ExecSQL;  ZQuery1.SQL.Text := 'insert into
autoinc (data) values (200)';  ZQuery1.ExecSQL;  ZQuery1.SQL.Text :=
'insert into autoinc (data) values (300)';  ZQuery1.ExecSQL;  
ZQuery1.SQL.Text := 'update autoinc set data = 250 where id = 2'; 
ZQuery1.ExecSQL;   ZQuery1.SQL.Text := 'select * from autoinc'; 
ZQuery1.Open;

----------------------------------------------------------------------
SymbolicFrank · 2022-01-19 09:18

Quote from: dseligo on January 18, 2022, 09:12:10 pmQuote from:
SymbolicFrank on January 18, 2022, 02:27:54 pmI 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
SQLiteDefaultLibrary := 'sqlite3.37.2.dll';  
SQLite3Connection1.DatabaseName := 'autoinc.db3'; 
SQLite3Connection1.Connected := True;   SQLQuery1.SQL.Text := 'create
table autoinc (id integer, data integer, primary key (id)) '; 
SQLQuery1.ExecSQL;   SQLQuery1.SQL.Text := 'insert into autoinc (data)
values (100)';  SQLQuery1.ExecSQL;  SQLQuery1.SQL.Text := 'insert into
autoinc (data) values (200)';  SQLQuery1.ExecSQL;  SQLQuery1.SQL.Text
:= 'insert into autoinc (data) values (300)';  SQLQuery1.ExecSQL;  
SQLQuery1.SQL.Text := 'update autoinc set data = 250 where id = 2'; 
SQLQuery1.ExecSQL;   SQLQuery1.SQL.Text := 'select * from autoinc'; 
SQLQuery1.Open;
Produces:
Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   id |
data-----+------   1 |  100   2 |  250   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 · 2022-01-19 09:20

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"

----------------------------------------------------------------------
SymbolicFrank · 2022-01-19 09:26

Quote from: Zvoni on January 18, 2022, 09:25:15 pmFrank, 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 · 2022-01-19 09:40

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.

----------------------------------------------------------------------
Zvoni · 2022-01-19 09:47

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.

----------------------------------------------------------------------
Zvoni · 2022-01-19 09:48

Quote from: SymbolicFrank on January 19, 2022, 10:40:15 amQuestion:
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

----------------------------------------------------------------------
SymbolicFrank · 2022-01-19 09:50

Quote from: Zvoni on January 19, 2022, 10:47:16 amOK,
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.

I tried specifying 'BigInt Primary Key Autoincrement', but that gives
an error message. If you specify 'AutoInc Primary Key' as the field
type, that works as well and results in that same Integer Primary Key
Autoincrement'. SQliteStudio calls them all AutoInc as well.

----------------------------------------------------------------------
SymbolicFrank · 2022-01-19 09:52

Quote from: Zvoni on January 19, 2022, 10:48:55 amQuote from:
SymbolicFrank on January 19, 2022, 10:40:15 amQuestion: 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

Yes, I put 'Commit' everywhere now. Simply for if I need to do things
like: 'select count(*) from tablename;'.

----------------------------------------------------------------------
Zvoni · 2022-01-19 09:56

Quote from: SymbolicFrank on January 19, 2022, 10:50:58 amIf you
specify 'AutoInc Primary Key' as the field type, that works as well
and results in that same Integer Primary Key Autoincrement'.
SQliteStudio calls them all AutoInc as well.
And that might be the Problem. Never used SQLiteStudio, so i don't
know if SQLiteStuido interprets something differentyl.
It's the reason why i asked for your Create Table-Statement with just
the Primary Key column, after your String-Concatenation has run
through

Basically what you send to create the table

CREATE TABLE MyTable (ID SomeType PRIMARY KEY AUTOINCREMENT,
SomeOtherFields.....)

For PrimaryKey AutoInc to work (basically as an Alias for ROWID), the
Table (resp. the Column) MUST be created using the "INTEGER"-keyword.
Any other keywords for Datatypes (INT, AUTOINC, whatever) might be
accepted, but it won't work

From SQLite-Docs:
Quoteif a rowid table has a primary key that consists of a single
column and the declared type of that column is "INTEGER" in any
mixture of upper and lower case, then the column becomes an alias for
the rowid. Such a column is usually referred to as an "integer primary
key". A PRIMARY KEY column only becomes an integer primary key if the
declared type name is exactly "INTEGER". Other integer type names like
"INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the
primary key column to behave as an ordinary table column with integer
affinity and a unique index, not as an alias for the rowid.

----------------------------------------------------------------------
SymbolicFrank · 2022-01-19 10:21

Quote from: Zvoni on January 19, 2022, 10:56:57 amQuote from:
SymbolicFrank on January 19, 2022, 10:50:58 amIf you specify 'AutoInc
Primary Key' as the field type, that works as well and results in that
same Integer Primary Key Autoincrement'. SQliteStudio calls them all
AutoInc as well.
And that might be the Problem. Never used SQLiteStudio, so i don't
know if SQLiteStuido interprets something differentyl.
It's the reason why i asked for your Create Table-Statement with just
the Primary Key column, after your String-Concatenation has run
through

Basically what you send to create the table

CREATE TABLE MyTable (ID SomeType PRIMARY KEY AUTOINCREMENT,
SomeOtherFields.....)

For PrimaryKey AutoInc to work (basically as an Alias for ROWID), the
Table (resp. the Column) MUST be created using the "INTEGER"-keyword.
Any other keywords for Datatypes (INT, AUTOINC, whatever) might be
accepted, but it won't work

From SQLite-Docs:
Quoteif a rowid table has a primary key that consists of a single
column and the declared type of that column is "INTEGER" in any
mixture of upper and lower case, then the column becomes an alias for
the rowid. Such a column is usually referred to as an "integer primary
key". A PRIMARY KEY column only becomes an integer primary key if the
declared type name is exactly "INTEGER". Other integer type names like
"INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the
primary key column to behave as an ordinary table column with integer
affinity and a unique index, not as an alias for the rowid.

I understand what you mean, but it is its own datatype. Look at the
picture. You cannot have both Integer and AutoInc.

And I don't know how up-to-date this is, but there is a table here as
to what results in which field.

Using the old DLL doesn't seem to change much, so I'm pretty sure I'm
doing something somewhere which the designer of the component or
database engine doesn't want me to do. And of which I'm pretty sure
most other SQL databases wouldn't mind.

Anyway, I'll keep on simplifying it until it works. Wish me luck,
because it has to be ready in two days and I still have to make some
complex queries to insert all kinds of totals. I spend most of my time
on this project so far debugging things not written by me.

Edit: added a pic of the empty fields.

----------------------------------------------------------------------
SymbolicFrank · 2022-01-19 10:58

Quote from: Zvoni on January 19, 2022, 10:56:57 amBasically what you
send to create the table

CREATE TABLE MyTable (ID SomeType PRIMARY KEY AUTOINCREMENT,
SomeOtherFields.....)

For PrimaryKey AutoInc to work (basically as an Alias for ROWID), the
Table (resp. the Column) MUST be created using the "INTEGER"-keyword.
Any other keywords for Datatypes (INT, AUTOINC, whatever) might be
accepted, but it won't work

I changed it and you were totally right! The datatype changed from
'AutoInc' to 'Integer', but the ROWIDs are inserted.

----------------------------------------------------------------------
Zvoni · 2022-01-19 11:23

So, resolved?

----------------------------------------------------------------------
SymbolicFrank · 2022-01-19 11:49

Quote from: Zvoni on January 19, 2022, 12:23:51 pmSo, resolved?

Yes, I think so. I changed my 'Post' statement back to the complex one
('on conflict .. do update') and that works well now. Now I can start
debugging my application in earnest.

Still, it is strange that all other data types than 'AutoInc' (and
'Integer' of course) generate an error, so it's probably on their
translation table but is not replaced by the right field type (it ends
up in the SQL database as a 'String' field with the 'AutoInc' field
type). I would call that a bug. At least you have to know not to do
that.

And it was not a bug in any FPC / Laz component.

----------------------------------------------------------------------
Zvoni · 2022-01-19 14:23

Quote from: SymbolicFrank on January 19, 2022, 12:49:52 pmStill, it is
strange that all other data types than 'AutoInc' (and 'Integer' of
course) generate an error, so it's probably on their translation table
but is not replaced by the right field type (it ends up in the SQL
database as a 'String' field with the 'AutoInc' field type). I would
call that a bug.
Not really.
In a way, you can look at SQLite being a "typeless" Database, or in
Programming-speak: a weakly typed DB
SQLite allows to store alphanumeric Strings in a Integer column
without complaining.
And SQLite doesn't have DataTypes for columns per se, but something
called "Storage Class" (of which there are 5).
Using DataTypes you're used to from other DB-Systems (MySQL, PostGres,
etc.) is accepted by SQLite and assigned an "Affinity" to a Storage-
Class
like your example with ".... CHAR(255), .... VARCHAR(1000)", but
internally such columns would have an Affinity for Storage Class TEXT
in this example

maybe that is the thing having confused you

----------------------------------------------------------------------
SymbolicFrank · 2022-01-24 17:26

Ok, I read the documentation. It was different than I thought, from
the bits and pieces I read before. Essentially, field types are just
for show, and you can insert a string into an Int field. That's going
to be a pain when I convert it back to DBF files. I'll turn them all
into STRICT tables.

----------------------------------------------------------------------
Zvoni · 2022-01-25 09:53

As an advice:
the STRICT-Modifier is a pretty recent feature (Late November 2021 -
Version 3.37)
Since i don't know if your program is just for you, or you're going to
distribute it, i'd probably check the SQLite-Version before doing
anything with the connection.
If the Version is smaller/earlier than 3.37 fire a "PRAGMA
writable_schema=ON" against the then opened connection as the first
thing (or add it to the Params of the connection before opening it).

In any case: Do not rely on the "error-handling" of the Database, but
sanitize in your code.
It's one of my own tenets for years to validate User-Input regardless
how my backend is set up.

----------------------------------------------------------------------
SymbolicFrank · 2022-01-25 12:56

This is stand-alone, so I can distribute what is needed. And yes, by
now most of my code consists of type and value checks / conversions

It does work, I got a lot of illegal conversion errors, mostly
DateTime, of course. By now, most should be gone.

But there is another problem: the most popular SQlite editors I could
find all give an error that the schema / table definitions are
corrupt, "near 'strict'". So I have to make my own tool to check the
status and execute SQL commands.

----------------------------------------------------------------------
Zvoni · 2022-01-25 14:43

Quote from: SymbolicFrank on January 25, 2022, 01:56:58 pmBut there is
another problem: the most popular SQlite editors I could find all give
an error that the schema / table definitions are corrupt, "near
'strict'". So I have to make my own tool to check the status and
execute SQL commands.
Probably exactly because they use Versions prior to 3.37.
DB Browser for SQLite (what i use) reports Version 3.35 for the engine