Delphi support for SQLite manifest typing

Question

SQLite has the interesting "feature" of letting you store anything in any field regardless of its data type.

http://www.sqlite.org/different.html#typing

I'm having to read some externally created SQLite files that were created by (ab)using this "feature". They have a field defined as VARCHAR(30) but are using it to store strings up to 100 characters or more. SQLite happily does this without any trimming taking place if you make direct calls to the SQLite DLL to store your data.

I'm currently using DevArt UniDAC 3.70.0.19 which has SQLite support to read these files, however it quite reasonably respects the size of the field as it is defined and so creates a TStringField object with a length of 30 characters. All characters stored beyond this 30 character limit are inaccessible to me.

I'm aware of all the available Delphi solutions for SQLite however can someone tell me which of them if any can handle this "feature"?

score 3 · Accepted Answer · edited Nov 13 '14 at 11:49

You can do this with any direct access layer to sqlite engine, without any TDataset layer.

For instance our open source wrapper http://blog.synopse.info/post/2011/07/22/SynDBSQLite3%3A-SQLite3-direct-access

In fact, unlike most SQL databases, SQLite does not restrict the type of data that may be inserted into a column based on the column's declared type. Instead, SQLite uses dynamic typing. The declared type of a column is used to determine the affinity of the column only. Direct access without the Db.pas layer allows use of this unique feature.

score 0 · Answer 2 · answered Dec 18 '12 at 21:03

0

Your solution can lay in sqlite itself. The schema is by default not writable (you can not UPDATE sqlite_master table) but with sane knowledge and a little help of PRAGMA writable_schema=ON; you can do this. So some changes are safe, for example changing VARCHAR(N) to VARCHAR(M), sqlite doesn't care about the number in the brackets.

Step 1, your schema is limited to 30 chars

CREATE TABLE [TestTable] (
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Txt] VARCHAR(30)
)

The line below allows sqlite_table changes

PRAGMA writable_schema=ON;

And the following statement will change the limits to 100

Update sqlite_master set sql='CREATE TABLE [TestTable] (
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Txt] VARCHAR(100)
)' where tbl_name='TestTable' and type='table'

But you should be aware of what you're doing since some changes are no welcome because sqlite expects some storage format based on the information in the schema. Varchar to varchar conversion does not change the storage format

answered Dec 18 '12 at 21:03

Maksee

2,311
2
24
34

This does not make any sense with sqlite. Unlike most SQL databases, SQLite does not restrict the type of data that may be inserted into a column based on the columns declared type. Instead, SQLite uses dynamic typing. The declared type of a column is used to determine the affinity of the column only. Such tricks to fake the table definition won't work with sql requests, for instance. – Arnaud Bouchez Dec 18 '12 at 21:21
@Arnaud_Bouchez, OP quote: "All characters stored beyond this 30 character limit are inaccessible to me". This trick solved exactly this problem since the code that parses sqlite schema (the only way to do size-limited typing) now will be more than happy to show all the characters below 100 – Maksee Dec 19 '12 at 02:56
Question title is explicitly about "Delphi support for SQLite manifest typing", and the truncation is just a small part of the problem. `TDataSet` based SQLite3 units (like UniDac or even the latest DBExpress driver supplied with XE3) will simply be unable to use this feature. OP question is about using this feature in Delphi, not only fix the text field truncation. The problem is not in SQLite3, but on the Delphi side, how the SQlite3 APIs are called and returned data is processed. That's why your answer is true, but sadly out of scope. – Arnaud Bouchez Dec 19 '12 at 14:19

score 0 · Answer 3 · answered Dec 19 '12 at 11:58

0

Because I wanted a really thin wrapper around the sqlite3.dll, I wrote my own, heavily based on OleVariants:

https://github.com/stijnsanders/TSQLite

It should also 'disregard' column type like you discribe, since I've added no other type checking than what the query results provide.

answered Dec 19 '12 at 11:58

Stijn Sanders

35,982
11
45
67

Why use olevariants and not variants? BSTR storage for text is pretty slow. But nice wrapper. – Arnaud Bouchez Dec 20 '12 at 13:37
Good question, I've always been using OleVariants because I got to know them via COM/ActiveX. – Stijn Sanders Dec 21 '12 at 11:51

score 0 · Answer 4 · edited Apr 06 '21 at 16:23

0

Need change the output type to TEXT, use CAST

SELECT cast(column as TEXT) FROM `tablename`

edited Apr 06 '21 at 16:23

David Buck

3,752
35
31
35

answered Apr 06 '21 at 15:04

Kroligoff

31
1

Delphi support for SQLite manifest typing

4 Answers4