Geek Answers Handbook

Delphi support for SQLite character set

SQLite has an interesting "feature" that allows you to store something in any field, regardless of its data type.

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

I need to read some created SQLite files created (ab) with this "function". They have a field defined as VARCHAR (30), but use it to store strings of up to 100 characters or more. SQLite happily does this without any clipping if you make direct calls to the SQLite DLL to store your data.

I am currently using DevArt UniDAC 3.70.0.19, which has SQLite support for reading these files, however it fairly reasonably respects the size of the field as it is defined and therefore creates a 30-character TStringField object. All characters stored outside this 30 character limit are not accessible to me.

I am aware of all the available Delphi SQLite solutions, but can anyone tell me which one if someone can handle this "function"?

+4
source share
3 answers

You can do this with any level of direct access to the sqlite server without any level of TDataset.

For example, our open source shell is http://blog.synopse.info/post/2011/07/22/SynDBSQLite3%3A-SQLite3-direct-access

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

+3
source

Since I wanted a very thin shell around sqlite3.dll, I wrote my own, heavily based on OleVariants:

https://github.com/stijnsanders/TSQLite

It should also “ignore” the type of column that you are describing, since I have not added any type checking, other than what the query results provide.

0
source

Your solution may lie in sqlite. The default schema is not writable (you cannot UPDATE the sqlite_master table), but with reasonable knowledge and a little help PRAGMA writable_schema = ON; can you do this. Thus, some changes are safe, for example, changing VARCHAR (N) to VARCHAR (M), sqlite does not care about the number in parentheses.

Step 1, your circuit is limited to 30 characters

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

The line below takes into account sqlite_table changes

 PRAGMA writable_schema=ON;

And the next 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 know what you are doing , because some changes are not welcome, because sqlite expects some storage format based on the information in the schema. Converting Varchar to varchar does not change the storage format

-1
source

More articles:


All Articles