UniDAC

Using SQLite data access provider with UniDAC in Delphi

This article provides a brief overview of the SQLite data access provider for UniDAC used to establish a connection to SQLite databases from Delphi and Lazarus. You will find the description of some useful features and how to get started quickly.

Overview

The main features of the SQLite data access provider are:

The full list of SQLite provider features can be found on the UniDAC features page.

Both Professional and Standard Editions of UniDAC include the SQLite provider. Express Edition of UniDAC does not include the SQLite provider.

Compatibility

To learn about SQLite compatibility, refer to the Compatibility section.

Requirements

Applications that use the SQLite provider require SQLite client library (sqlite3.dll). The SQLite provider dynamically loads SQLite client DLL available on user systems. To locate DLL you can set the ClientLibrary specific option of TUniConnection with the path to the client library. By default the SQLite provider searches a client library in directories specified in the PATH environment variable.

Deployment

When an application was built without runtime packages (Link with runtime packages set to False in Project Options), you do not need to deploy any BPL files with it. For more information, see Deployment.
Note that UniDAC Trial requires deployment of additional BPL files regardless of Link with runtime packages.

SQLite-specific options

Though UniDAC is components that provide unified interface to work with different database servers, it also lets you tune behaviour for each server individually. For thin setup of a certain database server, UniDAC provides server-specific options. These options can be applied to such components as TUniConnection, TUniQuery, TUniTable, TUniStoredProc, TUniSQL, TUniScript via their SpecificOptions property. SpecificOptions is a sting list. Therefore you can use the following syntax to assign an option value:

  UniConnection.SpecificOptions.Values['CharLength'] := '1';

Below you will find the description of allowed options grouped by components.

TUniConnection

Option Description
ASCIIDataBase Enables or disables ASCII support. The default value is False.

Note: Set the UseUnicode option to False before enabling ASCII support.

BusyTimeout Sets the timeout in milliseconds for a locked resource (database or table). If the resource is not unlocked during this time, SQLite returns the SQLITE_BUSY error. The default value is 0.
CipherLicense Holds a license key for SQLCipher Commercial Edition. Note that SQLCipher is not supported in the Direct mode.
ConnectMode The connection mode.
cmDefault
The default value. The database is opened for reading and writing. Corresponds to the SQLite default behavior.
cmReadWrite
The database is opened for reading and writing.
cmReadOnly
The database is opened in read-only mode.
Note that the ForceCreateDatabase option can be used to enable the automatic creation of the database when it doesn't already exist.
ClientLibrary Use the ClientLibrary option to set or get the location of the client library.
DateFormat The format for storing dates in the database. If no format is specified, the default format yyyy-mm-dd is used. The default value is an empty string.
DefaultCollations Enables or disables automatic default collations registration on connection establishing.
Direct When True, UniDAC connects to the database directly using the embedded SQLite3 engine, without the SQLite3 client library. The Direct mode also enables you to work with an encrypted database using the EncryptionAlgorithm and EncryptionKey options and the EncryptDatabase method.
EnableLoadExtension Enables loading and using an SQLite extension:
UniConnection.ExecSQL('SELECT load_extension(''C:\ext.dll'', ''sqlite3_ext_init'');');
EnableSharedCache Enables or disables the SQLite shared-cache mode. The default value is False.
EncryptionAlgorithm Used to specify the encryption algorithm for an encrypted database.
EncryptionKey This property is used for password input and for working with encrypted database. Password can be set or changed using EncryptDatabase method.
ForceCreateDatabase Forces TLiteConnection to create a new database before opening a connection, if the database doesn't already exist.
ForeignKeys Enables or disables foreign keys constraints without explicitly executing the "PRAGMA foreign_keys = ON;" and "PRAGMA foreign_keys = OFF;" statements. The default value if True.
IntegerAsLargeInt Maps INT (INTEGER) columns to TLargeIntField fields. If True, INT (INTEGER) columns are mapped to TLargeIntField fields. If False, INT (INTEGER) columns are mapped to TIntegerField fields. The default value is False.
JournalMode The journal mode.
jmDelete
The rollback journal is deleted at the conclusion of each transaction.
jmTruncate
The rollback journal is stored in volatile RAM. It reduces disk I/O, but decreases database safety and integrity. If the application using SQLite crashes in the middle of a transaction, the database file may become corrupt.
jmPersist
The rollback journal file is not deleted when the transaction is commited. The header of the journal is filled with zeroes to prevent other connections rolling back from the journal. This mode optimizes performance on platforms where deleting or truncating a file is much more expensive than overwriting the first block of a file with zeros.
jmMemory
The rollback journal is stored in volatile RAM. This reduces the disk I/O, but decreases database safety and integrity. If the application using SQLite crashes in the middle of a transaction in this mode, the database file is likely to become corrupt.
jmWAL
A write-ahead log is used instead of a rollback journal to implement transactions. When data database is updated, the original content is preserved in the database file and the changes are appended in a separate WAL file. All the transactions that are appended in the WAL file are eventually transferred back into the original database.
jmOff
The rollback journal is completely disabled. No rollback journal is created, thus there is no rollback journal to delete. The ROLLBACK command does not work -- it behaves in an undefined way. Avoid using the ROLLBACK command when the journal mode is disabled.
jmDefault
The default value. If the database was previously opened in the WAL mode, then Default will open the database in the WAL mode; otherwise, the database will be opened in the Delete mode.
LockingMode The database locking mode.
lmExclusive
The database connection never releases file locks. The first time the database is read or written in this mode, a shared lock is obtained and held. Use this mode if you want to prevent other processes from accessing the database file, reduce the number of filesystem operations, or access WAL databases without using the shared memory.
lmNormal
The database connection unlocks the database file at the conclusion of each read or write transaction.
Note: Keep the default LockingMode=lmExclusive and Synchronous=smOff for the best perfomance.
NativeDate If the option is set to True, the date and time values will be stored in the database in the native SQLite format, and when retrieved, they will be converted to the TDateTime type. If set to False, no conversion to the TDateTime type will be made. The default value is True.
ReadUncommitted Enables or disables the Read Uncommitted isolation mode. A database connection in this mode doesn't attempt to acquire a read lock on the table before reading from it. This can lead to inconsistent query results if another database connection modifies data in the table while it is being read, but it also means that a read transaction opened by a connection in the Read Uncommitted mode can neither block nor be blocked by another connection. The default value is False.
Synchronous The database synchronization mode when writing to disk.
smOff
The database engine continues without syncing after handing data off to the operating system. If the application running SQLite crashes, the data will safe, unless the operating system crashes or the computer loses power before data has been written to disk, in which case the database might become corrupted. This is the fastest mode.
smNormal
The database engine still syncs at the most critical moments, but less often than in the FULL mode. The Normal mode is faster than the Full mode. When using the WAL mode (and probably the DELETE mode) with synchronous=NORMAL, data is safe from corruption. The synchronous=NORMAL setting is a reasonable choice for most applications running in the WAL mode.
smFull
The database engine ensures that all content is safely written to disk before continuing. This preserves database integrity even in case of an operating system failure or power outage. It is a safe, but slower mode, and is most commonly used when not in the WAL mode.
smExtra
This mode is similar to the FULL mode, but in the DELETE mode, the directory containing the rollback journal is synced after that journal is unlinked to commit a transaction. This provides additional durability if a power outage occurs right after the commit.
Note: Keep the default Synchronous=smOff and LockingMode=lmExclusive for the best perfomance.
TimeFormat The format for storing time in the database. If no format is specified, the default format hh24:mi:ss will be used. The default value is an empty string.
UseUnicode Enables or disables Unicode support. When set to True, all character data is stored as WideString, and TStringField is used instead of TWideStringField. The default value is False.

TUniSQL

The TUniSQL component has no SQLite-specific options.

TUniQuery, TUniTable, TUniStoredProc

Option Description
AdvancedTypeDetection When False, standard metadata retrieval is performed when detecting the field type in a database. When True, a number of trecords will be prefetched from a table, and the field type will be detected based on the type of data stored in the corresponding column in the table. The default value is False.
FetchAll When True, all records of a query are requested from the database server when opening the dataset. If False, records are retrieved when a data-aware component or a program requests it. The default value is False.
ExtendedFieldsInfo If True, the driver performs additional queries to the database when opening a dataset. These queries return information about which fields of the dataset are required or autoincrement. Set this option to True, if you need the Required property of fields be set automatically.
UnknownAsString If set to True, all SQLite data types that are fetched as text and don't have the size limit, are mapped to TStringField with the default size 8192 bytes. If False (default value), such types are mapped to TMemoField. The TEXT data type is always mapped to TMemoField regardless of the value of this option.

TUniScript

The TUniScript component has no SQLite-specific options.

TUniLoader

Option Description
AutoCommit Used to automatically perform a commit after loading a certain amount of records. When the property is set to True, a transaction implicitly starts before loading the block of records and commits automatically after records were loaded. The default value is True.
AutoCommitRowCount Use the AutoCommitRowCount property to specify the number of records, after which the transaction will be commited automatically when the TUni.AutoCommit property is set to True. The default value is 1000.
QuoteNames Use the QuoteNames option to quote all database object names in automatically generated SQL statements, such as UPDATE statements. The default value is False.

TUniDump

The TUniDump component has no SQLite-specific options.

Data Type Mapping

The following table lists the constants for mapping SQLite data types to Delphi data types. See Data Type Mapping for more information.

Constant Description
liteInteger Maps INTEGER to Delphi data types.
liteReal Maps REAL to Delphi data types.
liteText Maps TEXT to Delphi data types.
liteBlob Maps BLOB to Delphi data types.
liteNull Maps NULL to Delphi data types.
liteBit Maps BIT to Delphi data types.
liteTinyInt Maps TINYINT to Delphi data types.
liteSmallInt Maps SMALLINT to Delphi data types.
liteInt2 Maps INT2 to Delphi data types.
liteInt Maps INT to Delphi data types.
liteMediumInt Maps MEDIUMINT to Delphi data types.
liteBigInt Maps BIGINT to Delphi data types.
liteUBigInt Maps UNSIGNED BIG INT to Delphi data types.
liteInt8 Maps INT8 to Delphi data types.
liteInt64 Maps INT64 to Delphi data types.
liteChar Maps CHAR to Delphi data types.
liteVarChar Maps VARCHAR to Delphi data types.
liteClob Maps CLOB to Delphi data types.
liteFloat Maps FLOAT to Delphi data types.
liteDouble Maps DOUBLE to Delphi data types.
liteNumeric Maps NUMERIC to Delphi data types.
liteDecimal Maps DECIMAL to Delphi data types.
liteNumber Maps NUMBER to Delphi data types.
liteMoney Maps MONEY to Delphi data types.
liteBool Maps BOOLEAN to Delphi data types.
liteBinary Maps BINARY to Delphi data types.
liteVarBinary Maps VARBINARY to Delphi data types.
liteDate Maps DATE to Delphi data types.
liteTime Maps TIME to Delphi data types.
liteDateTime Maps DATETIME to Delphi data types.
liteTimestamp Maps TIMESTAMP to Delphi data types.
liteTimestampTZ Maps TIMESTAMP WITH TIME ZONE to Delphi data types.
© 1997-2024 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback