Salesforce

SQLite Databases (Magic xpa 4.x)

« Go Back

Information

 
Created BySalesforce Service User
Approval Process StatusPublished
Objective
Description

SQLite Databases (Magic xpa 4.x)

The SQLite database is a single user, self-contained transactional SQL database engine that does not require a server or any installation or configuration.

The SQLite database is always installed so you do not need to select it in the Magic xpa installation wizard.

The following is information about working with the SQLite database:

Table Position

SQLite supports ROWID as the table position. The default position is ROWID.

Get Definition

For SQLite, if the primary key is a single column that auto-increments, the Get Definition utility will not bring the key into the table structure. This is because there is not a separate index using the column. The index internally refers to ROWID.

Hints

Hints are not supported and will be disregarded.

Isolation level

Isolation level cannot be changed. Any changes in the DBMS will be disregarded.

Joins

Inner and Outer joins should be implemented using the same syntax as in the SQL Server.

Transactions

Physical /deferred transactions are supported.

Array fetching

SQLite does not support array fetching. Therefore, the array size setting will be disregarded and the records will be fetched one record at a time.

Parallel execution

A single Magic xpa engine, or multiple engines on the same machine, may access SQLite via parallel programs. In such a case, only one thread/EXE can open a physical transaction, since the SQLite file is exclusively locked.

APG

Using the Automatic Program Generator (APG) in DSQL SELECT statements will result in the Null Allowed column property being set to Yes.

Identity

Identity should be defined as Type INTEGER PRIMARY KEY.

SQLite does not allow more than one INTEGER PRIMARY KEY fields.

Identity fields are stored as INT64 data types in SQLite; therefore, you should create a Numeric field to hold 8 bytes of data.

Encryption

The SQLite database supports encryption (Since version: 3.0). The encryption password is defined in the User Password database property.

It is not possible to migrate an existing non-encrypted SQLite database to an encrypted database. If you have an existing application with an SQLite database and you want to use encryption, you should define a different database with encryption and synchronize your data to this database.

Temporary SQLite Database

SQLite database also can be used as a Temporary database in the Environment setting 'Database for Sort/Temporary' under the Preferences tab. A database information flag named 'TEMP_DB' is introduced in order to define a Temporary SQLite database. For more details refer to Database Information - SQLite. Since version: 4.6

Since version: 1.9e

Related Topics

Reference
Attachment 
Attachment