Recent

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

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1313
Re: Debugging SQlite
« Reply #15 on: January 19, 2022, 10:50:58 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.

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

  • Hero Member
  • *****
  • Posts: 1313
Re: Debugging SQlite
« Reply #16 on: January 19, 2022, 10:52:50 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

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

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: Debugging SQlite
« Reply #17 on: January 19, 2022, 10:56:57 am »
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.
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:
Quote
if 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.
« Last Edit: January 19, 2022, 11:09:41 am 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: 1313
Re: Debugging SQlite
« Reply #18 on: January 19, 2022, 11:21:51 am »
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.
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:
Quote
if 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.
« Last Edit: January 19, 2022, 11:31:30 am by SymbolicFrank »

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1313
Re: Debugging SQlite
« Reply #19 on: January 19, 2022, 11:58:19 am »
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

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

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: Debugging SQlite
« Reply #20 on: January 19, 2022, 12:23:51 pm »
So, resolved?
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: 1313
Re: Debugging SQlite
« Reply #21 on: January 19, 2022, 12:49:52 pm »
So, 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

  • Hero Member
  • *****
  • Posts: 2319
Re: Debugging SQlite
« Reply #22 on: January 19, 2022, 03:23:42 pm »
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.
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
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: 1313
Re: [Solved] Debugging SQlite
« Reply #23 on: January 24, 2022, 06:26:48 pm »
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

  • Hero Member
  • *****
  • Posts: 2319
Re: [Solved] Debugging SQlite
« Reply #24 on: January 25, 2022, 10:53:00 am »
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.
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: 1313
Re: [Solved] Debugging SQlite
« Reply #25 on: January 25, 2022, 01:56:58 pm »
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

  • Hero Member
  • *****
  • Posts: 2319
Re: [Solved] Debugging SQlite
« Reply #26 on: January 25, 2022, 03:43:12 pm »
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.
Probably exactly because they use Versions prior to 3.37.
DB Browser for SQLite (what i use) reports Version 3.35 for the engine
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