PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • Overview
  • Main remarks
  • Required versions
  • Type and content of items
  • Declared type
  • NUMERIC or DECIMAL type
  • Automatic identifier
  • Unicode
  • Sensitivity to the case
  • 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/iPadApple WatchUniversal Windows 10 AppWindows Mobile
Others
Stored procedures
Native SQLite Connector: Specific features and remarks
Native AccessesAvailable only with this kind of connection
Overview
The following paragraphs present the main remarks and the specific features of Native SQLite Connector (also called "Native SQLite Access").
Main remarks

Required versions

Caution: This version of Native SQLite Connector operates with WINDEV 23 and/or WEBDEV 23.
Type and content of items

Declared type

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:
  • NULL.
  • INTEGER.
  • REAL.
  • TEXT.
  • BLOB.
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.

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 the performances.
Notes:
  • 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".

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.

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.
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 performances 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 have the ability to start a transaction, to run/browse all the queries one by one and to 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.
  • a browse is in progress on another file of 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 the HFSQL functions are not supported in this version.
Note: Only the locks performed by the SQL functions are supported. The lock is managed for the data file only by SQLite.

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