ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / Native Connectors/Native Accesses / Native SQLite Connector
  • 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
  • Unsupported SQL commands
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Others
Stored procedures
Native SQLite Connector: Specific features and remarks
Native Connectors (Native Accesses)Available only with this kind of connection
Overview
The following paragraphs present the main aspects and specific features of the Native SQLite Connector.
Main remarks

Required versions

Caution: This version of the Native SQLite Connector is only supported in WINDEV and/or WEBDEV 2024.
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.
You can 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:
  • 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

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:
  • "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 "ROWID" name is already used by another file item, the Native Connector will use one of the other names of the unique identifier: "_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

Each write operation (performed by HAdd, HModify, HDelete) is performed in a transaction.
If no transaction is started, a transaction is automatically started before the operation and automatically ended after the operation.
Tip: The performance of a write loop will be improved if all the operations are performed 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: You can start a transaction, execute/read all the queries one by one and 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.
Remark: Only the locks set by SQL functions are supported. SQLite supports locks set on data files only.

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%')
Minimum version required
  • Version 16
This page is also available for…
Comments
Click [Add] to post a comment

Last update: 09/05/2023

Send a report | Local help