ODBC Driver for SQLite

Connection String Options - ODBC Driver for SQLite

SQLite ODBC Connection String Options

The following table lists the connection string options for SQLite.

Option Description

ClientLibrary

The path to the SQLite3 client library.

Database

The path to the SQLite database file.

Direct

Enables direct connections to the database, which doesn't require the SQLite client library.

EncryptionAlgorithm

The encryption algorithm to be used when connecting to an encrypted database. The value can be one of the following:

  • Triple DES
  • Blowfish
  • AES128
  • AES192
  • AES256
  • Cast128
  • RC4

EncryptionKey

The encryption key to be used when connecting to an encrypted database.

Advanced Settings

Option Description

Allow NULL strings

To retrieve metadata, not all parameters according to MSDN can accept a null value. If NULL, the driver should return an error. But some 3rd-party tools pass NULL to the parameters. These options should be enabled for compatibility with such tools.

Empty strings as NULL

Busy Timeout

Sets the busy timeout duration in milliseconds, or disables busy handlers. A busy handler will sleep for a specified amount of time when a table is locked. After the specified time has elapsed, an exception is raised. Setting the timeout to 0 will disable all busy handlers. The default value is 15.

Cipher License

The SQLCipher license key for the SQLCipher Commercial Edition library for encrypting the SQLite database files. Note that this option is not available in the Direct mode.

ConnectMode

The connection mode.
Default
The default value. Opens the database in the default mode.
ReadWrite
Opens the database for reading and writing.
ReadOnly
Opens the database in read-only mode.

Enable Shared Cache

Enables multiple connections to the same database to share a single data and schema cache. The shared-cache mode is intended for use in embedded servers. The default value is True.

Integer As BigInt

If True, INTEGER fields are mapped to SQL_BIGINT. The default value is False.

ForceCreateDatabase

If the database does not exist, a new database will be created before opening a connection.

Journal Mode

The journal mode.
Delete
The rollback journal is deleted at the conclusion of each transaction.
Truncate
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.
Persist
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.
Memory
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.
WAL
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.
Off
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.
Default
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.

Locking Mode

The database locking mode.
Exclusive
The default value. 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.
Normal
The database connection unlocks the database file at the conclusion of each read or write transaction.

ODBC Behavior

Used to set the behavior corresponding to the ODBC specification version that a third-party tool expects. The behavior of ODBC driver can be changed by setting a value for the SQL_ATTR_ODBC_VERSION attribute by calling the SQLSetEnvAttr function. But some third-party tools expect the driver to exhibit ODBC 2.x behavior, but forget to call SQLSetEnvAttr with the specified version or pass an incorrect value there. In this case, the required behavior can be explicitly specified in the Connection String by setting the ODBC Behavior parameter. The possible values are:

  • Default - default ODBC behavior determined by a third-party tool.
  • Ver 2.x - ODBC 2.x behavior is explicitly set.
  • Ver 3.x - ODBC 3.x behavior is explicitly set.

RegionalDateTimeSettings

Enables the use of local regional settings when converting dates and times to strings.

RegionalNumberSettings

Enables the use of local regional settings when converting numbers to strings.

String Types

Sets the string value types returned by the driver as Default, Ansi or Unicode.

  • Default - the driver defines the string types.
  • Ansi - all string types will be returned as SQL_CHAR, SQL_VARCHAR and SQL_LONGVARCHAR.
  • Unicode - all string types will be returned as SQL_WCHAR, SQL_WVARCHAR and SQL_WLONGVARCHAR.

The option value should be changed if any third-party tool supports only Ansi string types or Unicode ones.

Synchronous

The database synchronization mode when writing to disk.
Off
The default mode. 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.
Normal
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.
Full
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.
Extra
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.

UnknownAsString

If False, fields of unknown data types (for example, the ifnull function result) are mapped to SQL_LONGVARCHAR. SQL_LONGVARCHAR is used because maximum length of values from such fields is unknown. If True, fields of unknown data types are mapped to SQL_VARCHAR. Size of fields is set to 8192. Values larger than this size are truncated. The default value is False.

Sample SQLite ODBC Connection String in Direct Mode

DRIVER={Devart ODBC Driver for SQLite};Database=D:\test.db3

Sample SQLite ODBC Connection String Using Client Library

DRIVER={Devart ODBC Driver for SQLite}; Direct=False; Database=D:\test.db3; Client Library=D:\SQLite3.dll

© 2015-2022 Devart. All Rights Reserved. Request Support ODBC Forum Provide Feedback