|
|
|
|
|
|
|
|
|
|
- Overview
- Main remarks
- Required versions
- Type and content of items
- Declared type
- NUMERIC or DECIMAL type
- Automatic identifier
- Unicode
- Case sensitivity
- Sensitivity to the accented and special characters
- Remarks and specific features of Native SQLite Connector
- Optimizing the multiple write operations
- Batches of queries
- HCreation function
- Locking records
- "Database is locked" error
- Unsupported SQL commands
SQLite Native Connector: Features and notes
 Disponible solo con este tipo de conexión
The following paragraphs present the main aspects and specific features of the Native SQLite Connector. Required versions Attention: This version of the SQLite Native Connector only works with WINDEV and/or WEBDEV 2025. Type and content of items Declared type In SQLite databases, items have a "declared" type. However, the type of each value can differ from this "declared" type. It is therefore possible to store any value, of any length, in any type of field, theoretically without loss (e.g.: 'toto' can be stored in an integer-type field). 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 It is advisable to force the field type 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: - NULL.
- INTEGER.
- REAL.
- TEXT.
- BLOB.
For more details, see the documentation about SQLite. 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. Automatic identifier In SQLite, each record has a unique 64-bit integer identifier: 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: - "ROWID",
- "_ROWID_",
- "OID".
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 performance. Remarks: - 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 name "ROWID" is already used by another item in the file, the Native Connector uses one of the other unique identifier names: "_ROWID_" or "OID".
Unicode 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. Case sensitivity 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 an SQLite database. Remarks and specific features of Native SQLite Connector Optimizing the multiple write operations If no transaction is started, a transaction is automatically started before the operation and automatically ended after the operation. Conseil: The performance of a write loop will be better if all operations are carried out in the same transaction..
To do so, all you have to do is call SQLTransaction before the loop and SQLTransaction after the loop. Example: SQLTransaction(sqlStart, <Connection>)
// Write loop
// ...
SQLTransaction(sqlCommit, <Connection>) Batches of queries The Native Connector does not support the batches of queries (which means several queries separated by ';' run at once). A single query must be run at a time. Tip: It's possible to start a transaction, execute/scan all requests one by one, then end the transaction. HCreation function HCreation may fail ("database table is locked" error) if the following conditions are fulfilled: - the file to create already exists.
- an iteration is in progress on another file in the same database.
This is caused by a limitation of SQLite database. Locking records The lock options (hLockWrite and hLockReadWrite constants) that can be used with HFSQL functions are not supported in this version. Note: Only SQL function blocks are managed. SQLite supports locks set on data files only. "Database is locked" error In Windows, a random "database is locked" error from the SQLite database may appear when trying to perform concurrent read operations (multiple threads or processes).. This error is not caused by the Native SQLite Connector. Unsupported SQL commands The Native SQLite Connector does not support: - The following statements:
- INSTR
- PATINDEX
- TOP
- BOTTOM
- LOG
- LN
- EXP
- POWER
- SIGN
- SQRT
- COS
- SIN
- TAN
- MOD
- MID
- RIGHT and FULL OUTER JOIN
- the style concatenations (for example SELECT CUSTOMER.CITY + '////' + CUSTOMER.ZIPCODE AS CONCAT1 FROM CUSTOMER WHERE CUSTOMER.CITY + '////' + CUSTOMER.ZIPCODE LIKE 'O%')
Esta página también está disponible para…
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|