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"?

Answer 1

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.