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.
The main features of the SQLite data access provider are:
The full list of SQLite provider features can be found at the UniDAC product page.
Both Professional and Standard Editions of UniDAC include the SQLite provider. Express Edition of UniDAC does not include the SQLite provider.
To learn about SQLite compatibility, refer to the Compatibility section.
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.
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.
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.
Enables or disables ASCII support. The default value is False.
Note: For this option usage set the UseUnicode option to false.
|BusyTimeout||Use the ClientLibrary option to set or get 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 Direct mode.|
Specifies which user privileges to use when accessing a SQLite database.
Connect with default permissions.
Connect with read/write permissions.
Connect with read-only permissions.
|ClientLibrary||Use the ClientLibrary option to set or get the client library location.|
|DateFormat||Defines the format for storing dates in the database. If it is not specified, the default yyyy-mm-dd format will be used.|
|DefaultCollations||Enables or disables automatic default collations registration on connection establishing.
List of available default collations:
|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.|
Enables loading and using an SQLite extension:
|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 hh24:mi:ss format will be 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.|
The TUniSQL component has no SQLite-specific options.
|AdvancedTypeDetection||If the option is set to False, standard metadata retrieval is performed when detecting the field type in a database. If set to 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||If True, all records of a query are requested from database server when the dataset is being opened. 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.|
The TUniScript component has no SQLite-specific options.
|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.|
The TUniDump component has no SQLite-specific options.