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

To deploy Win32 applications built with run-time packages, it is required to deploy the liteproviderXX.bpl file.

For more information about deployment of UniDAC-based applications, please, refer to the common Deployment topic.

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 or gets the timeout of waiting for locked resource (database or table). If resource is not unlocked during the time specified in BusyTimeout, then SQlite returns the SQLITE_BUSY error. Default value of this option is 0.
CipherLicense To use the SQLCipher Commercial Edition library for encrypting SQLite database files, insert your SQLCipher license key into the field. Note that the option is not available in the Direct mode.
ConnectMode Specifies which user privileges to use when accessing an SQLite database.
cmDefault
The default value. Connect with default permissions.
cmReadWrite
Connect with read/write permissions.
cmReadOnly
Connect with read-only permissions.
ClientLibrary Use the ClientLibrary option to set or get the location of the client library.
DateFormat Defines the format for storing dates in the database. If it is not specified, the default format yyyy-mm-dd is used.
DefaultCollations Enables or disables automatic default collations registration on connection establishing.List of available default collations:

  • UniNoCase - allows to compare unicode strings case-insensitively.
Direct If the Direct option is set to True, UniDAC connects to the database directly using embedded SQLite3 engine and does not use SQLite3 client library.
EnableLoadExtension Enables loading and using an SQLite extension:
UniConnection.ExecSQL('SELECT load_extension(''C:\ext.dll'', ''sqlite3_ext_init'');');
EnableSharedCache Enables or disables the Shared-Cache mode for SQLite database. Default value of this option 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.
ForeignKeys Enables or disables the enforcement of foreign key constraints. Foreign key constraints are disabled by default in SQLite, so this option can be used to force enabling or disabling them by the application.

Default value of this option is True.
ForceCreateDatabase Used to force TLiteConnection to create a new database before opening a connection, if the database does not exist.
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 Read-Uncommitted isolation mode. A database connection in read-uncommitted mode does not attempt to obtain read-locks before reading from database tables as described above. This can lead to inconsistent query results if another database connection modifies a table while it is being read, but it also means that a read-transaction opened by a connection in read-uncommitted mode can neither block nor be blocked by any other connection. Default value of this option is False.
TimeFormat Defines the format for storing time in the database. If it is not specified, the default format hh24:mi:ss is used.
UseUnicode Enables or disables Unicode support. Affects character data fetched from the server. When set to True, all character data is stored as WideStrings, and TStringField is replaced by TWideStringFiled.

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 TLiteLoader.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.

© 1997-2020 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback