Type and content of items
In the SQLite databases, the items have a "declared" type. However, the type of each value can differ from this "declared" type.
Therefore, you have the ability to store any value, of any length, in any type of item, theoretically without loss (example: 'toto' can be stored in an integer item).
This operating mode is not available in HFSQL. Therefore, the Native Connector will try to convert the value read on the database into the "declared" type of the item.
Workaround: We advise you to force the type of the item in the analysis to "Text". In this case, the Native Access will convert all the values into the text type, allowing you to read and display any type of value.
SQLite stores the values in one of the following types:
See the documentation about SQLite for more details.
NUMERIC or DECIMAL type
The internal operating mode of SQLite limits the precision of the NUMERIC items to 15 digits because the value is stored as real.
On SQLite, each record has a unique identifier whose type is 64-bit integer: the ROWID.
Although this item is "hidden" by default by the SQLite engine, it is always possible to access it by using one of the following names:
In most cases, the searches performed on the "ROWID" identifier are twice faster than the searches performed on any other primary key.
Therefore, the Native SQLite Connector systematically imports the "ROWID" column in order to improve the performances.
- If a file has a "INTEGER PRIMARY KEY" item, this item is an alias of the "ROWID" item. In this case, the ROWID item will be imported under this name.
- If the "ROWID" name is already used by another file item, the Native Connector is using one of the other names of the unique identifier: "_ROWID_" or "OID".
All the character strings are in UNICODE format.
All the exchanges with the SQLite engine are performed in UTF8.
If a character string is in ANSI format in the analysis, the Native Connector automatically performs all the necessary conversions.
Sensitivity to the case
The Native Connector manages the sensitivity to the case ("collate binary" or "collate nocase") when creating or importing an existing file.
The insensitivity to the case is managed for the Latin 1 characters only.
Sensitivity to the accented and special characters
All the items are always sensitive to the accented and special characters on a SQLite database.