|
Using SQLite data access provider with UniDAC in Delphi |
|
|
|
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.
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.
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.
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.
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.
| Option | Description |
|---|---|
| ASCIIDataBase |
Enables or disables ASCII support. The default value is False.
Note: Set the |
| 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:
|
| 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. |
| RealAsDouble |
Is used to specify whether REAL columns are mapped either to TFloatField fields or to TSingleField fields.
True
REAL columns are mapped to TFloatField fields.
False
REAL columns are mapped to TSingleField fields.
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. |
The TUniSQL component has no SQLite-specific options.
| 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. |
The TUniScript component has no SQLite-specific options.
| 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. |
The TUniDump component has no SQLite-specific options.
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. |