SQLite table always as WIDEMEMO

[dinko]

I am trying to connect UniQuery with SQLIte provider to a SQLite database. I just place a uniquery SELECT * FROM USERS and what I get always is WIDEMEMO instead of string data

http://take.ms/zH1UX

How can I get values as string without the need to cast every single value in the SQL query?

[MaximG]

Fields, whose type can't be defined by UniDAC, will be casted as WideMemo (e.g. the GUID type) . In case of using standard string data types (CHAR, VARCHAR, TEXT, etc.) when creating SQLite tables, they will be casted in code as TStringField. To cast a specific data type, you can use DataTypeMapping : https://www.devart.com/unidac/docs/?dat ... apping.htm . So, for example, if you want to cast all Text fields cast to String, use the following rule:

Code: Select all

uses  LiteDataTypeMapUni;
...
UniQuery.DataTypeMap.AddDBTypeRule(liteText, ftString);

[dinko]

SQLite has only "TEXT" field type which is the same for Memos (long texts) and Strings. UniDac is able to see those as memos.

I see your code is based per UniQuery but in my case I would like a solution on UniConnection level because I am using other components (TMS Business) that use UniConnection without UniQuery. So my problem still exists.

Maybe one option must be in SpecificOptions like "Map all text fields to string" to make developers life easier!

Do you have anything else to suggest about this?

[MaximG]

SQLite provides a dynamic typing system. It allows to declare a column of an arbitrary type. TEXT is only the type affinity. So, if you declare a column of type VARCHAR(100), for example, SQLite will store it internally as a TEXT. More information about the type affinity you can get from the official SQLite documentation: https://www.sqlite.org/datatype3.html .
Considering such a feature of SQLite, UniDAC provides some kind of type affinity too. So, the VARCHAR(100) column will be automatically mapped as a TStringField.
You can also use data type mapping, as we wrote earlier. Data type mapping is implemented both at the connection and at the dataset levels. So, you can add the following line

Code: Select all

UniConnection.DataTypeMap.AddDBTypeRule(liteText, ftString);

to cast all TEXT table columns to TStringField.
You can read more about data type mapping in the UniDAC documentation: https://www.devart.com/unidac/docs/?dat ... apping.htm .

[dinko]

Ok that DataTypeMap on Connection level works good and solves my problem, but I still cant understand why you say that:

Considering such a feature of SQLite, UniDAC provides some kind of type affinity too. So, the VARCHAR(100) column will be automatically mapped as a TStringField.

How can Unidac map SQLITE VARCHAR(100) where there is no VARCHAR? As you said and the link you gave me VARCHAR and TEXT are both declared as TEXT (affinity 2) and UniDac always see affinity 2 as MEMO. Without the DataTypeMap "hacking" its impossible to have a SQLite db that will be mapped as string instead of memo.

Am I wrong?

[AlexP]

We retrieve the type name of each field from the database, and the database returns the name that was specified in DDL. Then we create a corresponding Delphi field type by name. For TEXT it is ftMemo, for Varchar - ftString.