GM - Free SQLite ODBC Driver

Access SQLite Databases via ODBC.

Features

Table of contents

Connection strings

Opening a SQLite database in readonly mode

Datatype handling

  String and Text datatypes

  Date and time datatypes

  Number datatypes

Changing the SQLite version installed with the ODBC driver

Silent and embedded install/uninstall of the ODBC driver

Manually extract files from the installer *.exe file

 

Connection strings

A connection string is a list of name=value pairs, separated by semicolons, for example:

Driver=GM-Software SQLite3 ODBC Driver;DBQ=C:\Path\to\SomeSQLiteDB.sqlite3;NoMutex=1;

If the values contain a semicolon themself, the values can be surrounded by curly braces, for example:

Driver={Some Text Driver (*.txt; *.csv)};Filename=C:\Path\to\DBTextFile.txt;

The SQLDriverConnect ODBC API function takes a connection string to establish a database connection.

Connection string values recognized by the ODBC driver manager of the system:

Name Datatype Range Default Description
Driver String * '' The name of an ODBC driver.
DriverID Integer * <None> The ID of an ODBC driver. Only some Microsoft drivers actually have an ID assigned to them.
DSN String * '' The name of a data source that has been created using the ODBC driver management of the system. A data source already specifies all values described below, therefore a connection string like DSN=SomeDataSource; already has all values set. If additional values are given in the connection string they will supersede the values from the data source.

If you specify either Driver=Xxxxx; or DriverID=nn; in the connection string you don't need to create a data source entry in the ODBC administration of your system, because the driver manager can select the correct driver using this value. If the driver name is specified in the connection string, it must exactly match the name of the driver as shown by the ODBC administration in the Drivers page tab.

For example the following connection string works without creating a DSN entry in the ODBC administration of your system:

Driver=GM-Software SQLite3 ODBC Driver;DBQ=C:\Path\to\SomeSQLiteDB.sqlite3;

Connection string values recognized by the GM - ODBC Driver for SQLite:

Bold names are mandatory, they may cause an error when omitted from the connection string.

Name Datatype Range Default Description
DSN String * '' The name of a data source that has been created using the ODBC driver management of the system. A data source already specifies all values described below, therefore a connection string like DSN=SomeDataSource; already has all values set. If additional values are given in the connection string they will supersede the values from the data source.
FileName | Database | DBQ String * '' Path and filename of the database file to be opened. Any of the three names can be used to set the filename.
ReadOnly Boolean 0 | 1, False | True False Open database in read only mode. True passes the SQLITE_OPEN_READONLY flag to sqlite3_open_v2(...). False passes the SQLITE_OPEN_READWRITE flag. If ReadOnly is True then AllowCreate is ignored. Supersedes what might have been set via SQLSetConnectAttr(SQL_ATTR_ACCESS_MODE) ODBC API.
Exclusive Boolean 0 | 1, False | True False Open database in exclusive mode. True passes the SQLITE_OPEN_EXCLUSIVE flag to sqlite3_open_v2(...). NOTE: This is a no-op and might become an error in future versions of SQLite.
AllowCreate Boolean 0 | 1, False | True True Allow the database file to be created if it doesn't exists. True passes the SQLITE_OPEN_CREATE flag to sqlite3_open_v2(...)
AllowUri Boolean 0 | 1, False | True False Allow the database filename to be an URI. True passes the SQLITE_OPEN_URI flag to sqlite3_open_v2(...)
InMemory Boolean 0 | 1, False | True False The database will be opened as an in-memory database. The database is named by the "filename" argument for the purposes of cache-sharing, if shared cache mode is enabled, but the "filename" is otherwise ignored. AllowCreate is ignored, InMemory will always create a new database inside the memory. True passes the SQLITE_OPEN_MEMORY flag to sqlite3_open_v2(...)
NoMutex Boolean 0 | 1, False | True True The new database connection will use the "multi-thread" threading mode. True passes the SQLITE_OPEN_NOMUTEX flag to sqlite3_open_v2(...)
FullMutex Boolean 0 | 1, False | True False The new database connection will use the "serialized" threading mode. True passes the SQLITE_OPEN_FULLMUTEX flag to sqlite3_open_v2(...)
SharedCache Boolean 0 | 1, False | True False The database is opened with shared cache enabled. True passes the SQLITE_OPEN_SHAREDCACHE flag to sqlite3_open_v2(...)
PrivateCache Boolean 0 | 1, False | True False The database is opened with shared cache disabled. True passes the SQLITE_OPEN_PRIVATECACHE flag to sqlite3_open_v2(...)
NoFollow Boolean 0 | 1, False | True False The database filename is not allowed to contain a symbolic link. True passes the SQLITE_OPEN_NOFOLLOW flag to sqlite3_open_v2(...)
ExtendedCodes Boolean 0 | 1, False | True True The database connection uses extended result code mode. True passes the SQLITE_OPEN_EXRESCODE flag to sqlite3_open_v2(...)
BusyTimeout Integer >= 0 3000 The maximum time (in [milliseconds]) to wait for statements of other connections to finish before the exceution of statements on this connection is aborted. This will call sqlite3_busy_timeout(...) after the creation of the connection. This value can always be changed using the ODBC SQLSetConnectAttr(SQL_ATTR_CONNECTION_TIMEOUT) API (here you have to specify [seconds]).
FKeyConstraints Boolean 0 | 1, False | True True Enforce foreign key constraints on the database connection". True uses the SQLITE_DBCONFIG_ENABLE_FKEY flag with sqlite3_db_config(...) after the connection has been opened.
UseTriggers Boolean 0 | 1, False | True True Enable triggers on the database connection". True uses the SQLITE_DBCONFIG_ENABLE_TRIGGER flag with sqlite3_db_config(...) after the connection has been opened.
EnableViews Boolean 0 | 1, False | True True Enable views on the database connection". True uses the SQLITE_DBCONFIG_ENABLE_VIEW flag with sqlite3_db_config(...) after the connection has been opened.

Note: Name matching is done case insensitive by the driver. If you type the names in a connection string incorrectly, or put entries with names not recognized by the driver, no error occurs. Such entries are simply ignored. If a mandatory entry is missing an error may occur during connect, or the driver manager asks for the missing values.

Entries omitted from the connection string are assumed to have the values given in the Default column.

Opening a SQLite database in readonly mode

Most likely you will be using some ODBC library. Almost certainly your ODBC library will offer a ReadOnly property/argument on the connection level.

In addition you can specify ReadOnly=True in the connection string. This will supersede any ReadOnly setting done by your library via the ODBC call SQLSetConnectAttr(.. , SQL_ATTR_ACCESS_MODE, ..). When omitting ReadOnly from the connection string or specifying ReadOnly=False the ReadOnly setting of your library will be used.

Datatype handling

If an application calls SQLBindCol or SQLGetData with a C-type different to the native data type of the database column (reported by SQLDescribeCol) the driver converts the data to the type wanted by the application. Almost all data type conversions are implemented, but some conversions may imply data loss (e.g. rounding from Double to Integer) or cause errors (e.g. converting string "Hello" to an integer etc.).

String and Text datatypes

 CREATE TABLE Persons (
   FirstName Varchar(80),
   LastName Varchar(80),
   Gender Char(1),
   Comment Text
 );
CREATE TABLE type SQL type reported by SQLDescribeCol Possible C types for application data value
CHAR(n) | VARCHAR(n) | NVARCHAR(n) | VARCHAR2(n) SQL_WVARCHAR SQL_C_CHAR | SQL_C_WCHAR | SQL_C_TINYINT | SQL_C_STINYINT | SQL_C_UTINYINT | SQL_C_SHORT | SQL_C_SSHORT | SQL_C_USHORT | SQL_C_LONG | SQL_C_SLONG | SQL_C_ULONG | SQL_C_BIGINT | SQL_C_DOUBLE | SQL_C_FLOAT | SQL_C_TIMESTAMP | SQL_C_TYPE_TIMESTAMP | SQL_C_DATE | SQL_C_TYPE_DATE | SQL_C_TIME | SQL_C_TYPE_TIME | SQL_C_SS_TIME2
TEXT | MEMO | LONGVARCHAR SQL_WLONGVARCHAR SQL_C_CHAR | SQL_C_WCHAR | SQL_C_TINYINT | SQL_C_STINYINT | SQL_C_UTINYINT | SQL_C_SHORT | SQL_C_SSHORT | SQL_C_USHORT | SQL_C_LONG | SQL_C_SLONG | SQL_C_ULONG | SQL_C_BIGINT | SQL_C_DOUBLE | SQL_C_FLOAT | SQL_C_TIMESTAMP | SQL_C_TYPE_TIMESTAMP | SQL_C_DATE | SQL_C_TYPE_DATE | SQL_C_TIME | SQL_C_TYPE_TIME | SQL_C_SS_TIME2

CHAR, VARCHAR, NVARCHAR and VARCHAR2 datatypes will be treated as string data having a maximum length. Internally they will be stored as TEXT inside the SQLite database. Applications can bind a buffer via SQLBindCol to such columns. Such Columns will be reported as SQL_WVARCHAR by SQLDescribeCol with a size according to the length given by the CREATE TABLE statement. If an application uses larger buffers or creates SQL INSERT statements with larger values for such columns, all data will be stored inside the SQLite database, since SQLite has no length limitations for TEXT data. When an application fetches such larger values with correctly sized buffers, all values that do not fit into the buffer size will be truncated and the return value will be SQL_SUCCESS_WITH_INFO with code 01004 (String data right truncated).

TEXT, MEMO and LONGVARCHAR datatypes will be treated as string data with unlimited length. Internally they will be stored as TEXT inside the SQLite database. Applications should only bind a length indicator but no data buffer when calling SQLBindCol for such columns. If a length indicator is bound then the driver will set it's value to the length of the current string data after each call of SQLFetch or SQLFetchScroll. Such Columns will be reported as SQL_WLONGVARCHAR by SQLDescribeCol with a size of 0 . The data of such columns should be retrieved using SQLGetData.

Date and time datatypes

 CREATE TABLE WorkTimes (
   PersonID Integer,  -- <= reference to some person
   Dateonly Date,
   StartDT Datetime,
   EndDT Datetime,
   TaskID Integer -- <= reference to some task
 );
CREATE TABLE type SQL type reported by SQLDescribeCol Possible C types for application data value
DATE SQL_DATE SQL_C_CHAR | SQL_C_WCHAR | SQL_C_TINYINT | SQL_C_STINYINT | SQL_C_UTINYINT | SQL_C_SHORT | SQL_C_SSHORT | SQL_C_USHORT | SQL_C_LONG | SQL_C_SLONG | SQL_C_ULONG | SQL_C_BIGINT | SQL_C_DOUBLE | SQL_C_FLOAT | SQL_C_TIMESTAMP | SQL_C_TYPE_TIMESTAMP | SQL_C_DATE | SQL_C_TYPE_DATE | SQL_C_TIME | SQL_C_TYPE_TIME | SQL_C_SS_TIME2
TIME SQL_TIME SQL_C_CHAR | SQL_C_WCHAR | SQL_C_TINYINT | SQL_C_STINYINT | SQL_C_UTINYINT | SQL_C_SHORT | SQL_C_SSHORT | SQL_C_USHORT | SQL_C_LONG | SQL_C_SLONG | SQL_C_ULONG | SQL_C_BIGINT | SQL_C_DOUBLE | SQL_C_FLOAT | SQL_C_TIMESTAMP | SQL_C_TYPE_TIMESTAMP | SQL_C_DATE | SQL_C_TYPE_DATE | SQL_C_TIME | SQL_C_TYPE_TIME | SQL_C_SS_TIME2
DATETIME SQL_TYPE_TIMESTAMP SQL_C_CHAR | SQL_C_WCHAR | SQL_C_TINYINT | SQL_C_STINYINT | SQL_C_UTINYINT | SQL_C_SHORT | SQL_C_SSHORT | SQL_C_USHORT | SQL_C_LONG | SQL_C_SLONG | SQL_C_ULONG | SQL_C_BIGINT | SQL_C_DOUBLE | SQL_C_FLOAT | SQL_C_TIMESTAMP | SQL_C_TYPE_TIMESTAMP | SQL_C_DATE | SQL_C_TYPE_DATE | SQL_C_TIME | SQL_C_TYPE_TIME | SQL_C_SS_TIME2

DATE, TIME and DATETIME are all stored as Double values inside the SQLite database.

If the application uses SQL_C_CHAR or SQL_C_WCHAR as data value then you get the string representation of the underlying Double value e.g. 45292.25 (01.01.2024 06:00:00).

The date is represented by the integral part of the Double value, and the time is represented by the fractional part of the Double value.

The value 0.0 is 30.12.1899 00:00:00 (DD.MM.YYYY HH:MM:SS). The conversion to julian day is: Julian day = DateTimeDoubleValue + 2415018.5

The SQLite datetime function can be used to convert such double values to a string for display:

 SELECT datetime(StartDT + 2415018.5) as WorkStart FROM WorkTimes WHERE ... ;

Time values are fractions of 1:

One day = 1

One hour = 1 / 24 = 0.0416666666666667

Six hours = 6 / 24 = 1 / 4 = 0.25

One minute = 1 / (24 * 60) = 1 / 1440 = 0.0006944444444444444

15 minutes = 15 / (24 * 60) = 1 / 96 = 0.0104166666666667

For calculations the normal arithmetic operators can be used:

 SELECT EndDT - StartDT as WorkDurationDays FROM WorkTimes WHERE ... ;
 SELECT (EndDT - StartDT) * 24 as WorkDurationHours FROM WorkTimes WHERE ... ;
 SELECT (EndDT - StartDT) * 1440 as WorkDurationMinutes FROM WorkTimes WHERE ... ;
 SELECT (EndDT - StartDT) * 86400 as WorkDurationSeconds FROM WorkTimes WHERE ... ;
 SELECT Sum(EndDT - StartDT) as SumOfWorkDurationsDays FROM WorkTimes WHERE ... ;
 SELECT Sum(EndDT - StartDT) * 24 as SumOfWorkDurationsHours FROM WorkTimes WHERE ... ;
 SELECT Sum(EndDT - StartDT) * 1440 as SumOfWorkDurationsMinutes FROM WorkTimes WHERE ... ;
 SELECT Sum(EndDT - StartDT) * 86400 as SumOfWorkDurationsSeconds FROM WorkTimes WHERE ... ;

There is no need for a separate "timespan" datatype.

These date and time interpretations of Double values are known as "Microsoft Excel epoch" and are used by MS-Excel and some programming languages like Pascal and Visual Basic. They offer sufficient precision in a reasonable date range along with small storage size. Calculations are done directly by standard floating point operations that perform well on common CPU's/FPU's.

Applications can use the ODBC syntax for Date, Time and Datetime literals inside their SQL statements. The driver will convert such literals to the corresponding Double values that represent the given date and time, for example:

 SELECT * FROM WorkTimes WHERE StartDT >= {d '2023-05-21'};
 UPDATE WorkTimes SET StartDT = {dt '2023-08-17 10:00:00'} WHERE ... ;
 DELETE FROM WorkTimes WHERE StartDT < {d '2023-01-01'};
 INSERT INTO WorkTimes (StartDT) VALUES ({dt '2023-05-27 09:00:00'});

Number datatypes

 CREATE TABLE WorkTimes (
   numericval Numeric(5, 5),
   decimalval Decimal(8, 3),
   dblval Double,
   realval Real,
   intval Integer
 );

SQLite databases only support two number datatypes Int64 and Double.

CREATE TABLE type SQL type reported by SQLDescribeCol Possible C types for application data value
INT | LONG | INTEGER | SMALLINT | BIGINT | TINYINT SQL_BIGINT SQL_C_CHAR | SQL_C_WCHAR | SQL_C_TINYINT | SQL_C_STINYINT | SQL_C_UTINYINT | SQL_C_SHORT | SQL_C_SSHORT | SQL_C_USHORT | SQL_C_LONG | SQL_C_SLONG | SQL_C_ULONG | SQL_C_BIGINT | SQL_C_DOUBLE | SQL_C_FLOAT | SQL_C_TIMESTAMP | SQL_C_TYPE_TIMESTAMP | SQL_C_DATE | SQL_C_TYPE_DATE | SQL_C_TIME | SQL_C_TYPE_TIME | SQL_C_SS_TIME2
NUMERIC(n, n) | DECIMAL(n, n) | DOUBLE | REAL SQL_DOUBLE SQL_C_CHAR | SQL_C_WCHAR | SQL_C_TINYINT | SQL_C_STINYINT | SQL_C_UTINYINT | SQL_C_SHORT | SQL_C_SSHORT | SQL_C_USHORT | SQL_C_LONG | SQL_C_SLONG | SQL_C_ULONG | SQL_C_BIGINT | SQL_C_DOUBLE | SQL_C_FLOAT | SQL_C_TIMESTAMP | SQL_C_TYPE_TIMESTAMP | SQL_C_DATE | SQL_C_TYPE_DATE | SQL_C_TIME | SQL_C_TYPE_TIME | SQL_C_SS_TIME2

Changing the SQLite version installed with the ODBC driver

The ODBC driver uses the precompiled Windows DLL's of SQLite. To find out which version of these DLL's is installed with the driver execute the following SQL statement on any connection:

 SELECT sqlite_version();

SQLite frequently releases new versions. There won't be a new release of this ODBC driver for every SQLite release just to include the most recent SQLite DLL's.

The SQLite DLL's are located in the same folder where the ODBC driver has been installed to. You can simply replace these SQLite DLL's with another version:

  1. Download the precompiled Windows 32-bit and 64-bit DLL's from the SQLite website.
  2. Close all SQLite ODBC database connections (otherwise the DLL's will be in use and cannot be overwritten).
  3. Extract the SQLite DLL's from the downloaded ZIP archives and:
  4. Copy those two DLL's to the folder where the ODBC driver is installed, replacing the existing DLL's with same names.

Note: The setup will remove those two DLL's on uninstall, regardless of their version. If they have been deleted or moved elsewhere no error will occur during uninstall. The uninstaller will never delete a file not installed by itself. If the original SQLite DLL's in this folder have been renamed (to be able to restore them) the renamed DLL's will not be deleted on uninstall. Because those files being left the folder will not be deleted. The deletion of the folder will be scheduled for the next reboot and the finish page of the uninstaller will tell you that a reboot is needed.

The ODBC driver calls LoadLibrary("sqlite3_32.dll") or LoadLibrary("sqlite3_64.dll") to load the SQLite DLL. Because there is no path given to the LoadLibrary calls the system will search for those DLL files the usual way. So you may move the SQLite DLL's away from the folder of the ODBC driver to another folder and include that folder in the system Path variable. No error will occur in the uninstaller when the SQLite DLL's don't exist anymore in the folder of the ODBC driver during uninstall.

Silent and embedded install/uninstall of the ODBC driver

The installer has been build using NSIS. The generated installer and uninstaller *.exe files support the following command line arguments:

Parameter Install Uninstall Description
/NCRC Install & Uninstall Disables the CRC check, unless CRCCheck force was used in the script.
/S Install & Uninstall Runs the installer or uninstaller silently.
/D Install Sets the installation directory ($INSTDIR), overriding the internal default C:\Program Files (x86)\GM-Software SQLite3 ODBC Driver. It must be the last parameter used in the command line and must not contain any quotes, even if the path contains spaces. Only absolute paths are supported.
_?= Uninstall Sets $INSTDIR. It also stops the uninstaller from copying itself to the temporary directory and running from there. It can be used along with ExecWait to wait for the uninstaller to finish. It must be the last parameter used in the command line and must not contain any quotes, even if the path contains spaces..

The original documentation of the command line arguments can be found in section 3.2 of the NSIS documentation.

You may include the installer of the GM-SQLite ODBC driver in the installer of your own software, and use the Silent command line argument to install and uninstall the driver silently from within your software's installation.

Silent install:

 C:\InstallGMSQLite3Odbc_v1.1.0.exe /S

Silent install and specify the installation directory:

 C:\InstallGMSQLite3Odbc_v1.1.0.exe /S /D=C:\Path\To\Custom dir

Silent uninstall:

 C:\Program Files (x86)\GM-Software SQLite3 ODBC Driver\UninstallGMSQLite3Odbc_v1.0.0.exe /S

or

 UninstallGMSQLite3Odbc_v1.0.0.exe /S _?=C:\Program Files (x86)\GM-Software SQLite3 ODBC Driver

Manually extract files from the installer *.exe file

The installer *.exe file can be opened like a normal archive file (*.zip etc.) using 7-Zip to extract files from it. This way you can always restore the original SQLite DLL's if you replaced them by another version, without the need to reinstall the driver.