SQLite ODBC connection string parameters

The following table describes the key connection string parameters for connecting to SQLite via the ODBC driver.

Server configuration

Parameter Description
ClientLibrary The path to the SQLite3 client library.
Database The path to the SQLite database file.
Direct Enables direct connections to a database, which doesn’t require the SQLite3 client library.
EncryptionKey The encryption key for connecting to an encrypted database.
EncryptAlgorithm The encryption algorithm for connecting to an encrypted database. Available values:
  • Triple DES.
  • Blowfish.
  • AES 128.
  • AES 192.
  • AES 256.
  • Cast 128.
  • RC4.
  • Default.

Advanced settings

Parameter Description
Allow Null Strings Allows NULL values to be passed to metadata retrieval functions, ensuring compatibility with third-party tools that send NULL where the ODBC specification requires a value.
Busy Timeout Sets the busy timeout duration in milliseconds or disables busy handlers. A busy handler will sleep for the specified amount of time when a table is locked. After this time has elapsed, an exception is raised. Setting the timeout to 0 disables all busy handlers. The default value is 15.
ConnectMode Specifies the connection mode. Available values:
  • Default – (Default) Opens a database in default mode.
  • ReadWrite – Opens a database for reading and writing.
  • ReadOnly – Opens a database in read-only mode.
Empty Strings as NULL Treats empty strings as NULL values when passed to metadata retrieval functions, ensuring compatibility with third-party tools that pass empty strings instead of NULL.
Enable Shared Cache Enables multiple connections to the same database to share a single data and schema cache. Shared-cache mode is intended for use in embedded servers. The default value is True.
ForceCreateDatabase Creates a new database if it does not exist before opening a connection.
Integer As BigInt If True, maps INTEGER fields to SQL_BIGINT. The default value is False.
Journal Mode Specifies how SQLite manages transactions and the rollback journal. Available values:
  • Delete – Deletes the rollback journal at the end of each transaction.
  • Truncate – Stores the rollback journal in volatile RAM. This 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 corrupted.
  • Persist – Keeps the rollback journal file after the transaction is committed, but overwrites its header with zeroes to prevent other connections from rolling back using that journal. This mode improves performance on platforms where deleting or truncating a file is more expensive than overwriting its first block.
  • Memory – Stores the rollback journal in volatile RAM. This reduces 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 corrupted.
  • WAl – Uses a write-ahead log instead of a rollback journal to implement transactions. When the database is updated, the original content is preserved in the database file, and the changes are appended to a separate WAL file. All transactions appended to the WAL file are eventually transferred back into the original database.
  • Off – Disables the rollback journal completely. No rollback journal is created, so there is no 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 – (Default) If the database was previously opened in WAL mode, opens it in WAL mode; otherwise, opens the database in Delete mode.
Locking Mode Specifies how the driver locks the database file. Available values:
  • Exclusive – (Default) Keeps the database file locked for the lifetime of the connection. 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 shared memory.
  • Normal – Unlocks the database file at the end of each read or write transaction.
ODBC Behavior Specifies the ODBC specification version the driver must conform to, based on the expectations of a third-party tool.
Typically, the driver’s behavior is controlled by setting the SQL_ATTR_ODBC_VERSION attribute via the SQLSetEnvAttr function. However, some third-party tools expect the driver to behave according to ODBC 2.x standards, but either fail to set this attribute or pass an incorrect value.
In such cases, you can explicitly define the required behavior in the connection string using the ODBC Behavior parameter. Available values:
  • Default – Uses the default behavior as determined by the third-party tool.
  • Ver 2.x – Forces the driver to use ODBC 2.x behavior.
  • Ver 3.x – Forces the driver to use ODBC 3.x behavior.
Real As Double Determines how REAL (floating-point) values are handled or stored—specifically, whether they should be treated as DOUBLE PRECISION instead of the standard REAL type.
RegionalDateTimeSettings Enables the use of local regional settings when converting dates and times to strings.
RegionalNumberSettings Enables local regional settings for converting numbers to strings. The default value is False.
String Types Specifies the type of string data returned. Available values:
  • Default – The driver determines the appropriate string types.
  • Ansi – Returns string data as SQL_CHAR, SQL_VARCHAR, and SQL_LONGVARCHAR.
  • Unicode – Returns string data as SQL_WCHAR, SQL_WVARCHAR, and SQL_WLONGVARCHAR.
Synchronous Specifies how the driver syncs database writes to disk. Available values:
  • Off – (Default) Skips syncing after handing data off to the operating system. The database engine continues without syncing after handing data off to the operating system. If the application that runs SQLite crashes, the data will be safe—unless the operating system crashes or the computer loses power before the data has been written to disk, in which case the database might become corrupted. This is the fastest mode.
  • Normal – Syncs only at the most critical points, less often than in Full mode. Normal mode is faster than Full mode. When you use WAL mode (and probably Delete mode) with synchronous=NORMAL, data is safe from corruption. The synchronous=NORMAL setting is a reasonable choice for most applications running in WAL mode.
  • Full – Ensures all content is safely written to disk before continuing. This preserves database integrity even in the event of an operating system failure or power outage. This mode is safe but slower and is typically used when WAL mode is not enabled.
  • Extra – Compared to Full mode, adds an extra sync in Delete mode by syncing the directory that contains the rollback journal after the journal is unlinked to commit a transaction. This provides additional durability if a power outage occurs immediately after the commit.
UnknownAsString If False, maps fields of unknown data types (for example, the result of the ifnull function) to SQL_LONGVARCHAR, because their maximum length is unknown. If True, maps fields of unknown data types to SQL_VARCHAR with a size of 8192 and truncates values that exceed this size. The default value is False.

Sample connection string

Connection in Direct mode

DRIVER={Devart ODBC Driver for SQLite};Database=myDatabase

Connection using the client library

DRIVER={Devart ODBC Driver for SQLite};Direct=False;Database=myDatabase;Client Library=myClientLibrary