GM - Free SQLite ODBC Driver

Access SQLite Databases via ODBC. Release Notes Download Windows x86 32-Bit & 64-Bit

Features

• Fast, stable, optimized native code (build with freePascal).
• Thread safe. All objects inside the driver are internally secured by critical sections, so they can safely accessed by multiple threads.
• Conforms to ODBC standard 3.5.
• Unicode: The internal string type of the driver is Unicode (Utf-16). The driver DLL exports only the SQLXxxxxW functions. But it converts string data to ANSI if an application wants character data as SQL_C_CHAR (instead of SQL_C_WCHAR).
• Support for Date, Time and Datetime datatypes.
• All important SQLite flags can be specified in the Connection strings.
• Easy to use dialog to create or change ODBC data source entries in the ODBC management of the system.
• Supports SQLSetPos with operations SQL_ADD, SQL_UPDATE, SQL_DELETE and SQL_REFRESH for tables with primary keys, when all primary key columns are in the select list of the statement.
• Automatic data type conversions for almost all possible type combinations: application buffer datatype <=> database column datatype.
• SQLite DLL's included in the installer.
• Runs on all Windows systems NT | 2000 | XP | Vista | 7 | 8 | 10 | 11 or Windows Server 2000 | 2003 | 2008 | 2012 | 2016 | 2019 | 2022 32-Bit or 64-Bit (No dependencies to other software like .Net, Gdi+, C++ runtimes, java, nodeJS etc.).

Table of contents

 Connection strings

 Opening a SQLite database in readonly mode

 ODBC escape sequences

 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:

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.

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 (using SQLSetConnectAttr(.. , SQL_ATTR_ACCESS_MODE, ..) ODBC API).

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.

ODBC escape sequences

The ODBC standard introduces some additional syntax elements to SQL. Such ODBC escape sequences are always enclosed by curly brackets { .. }.

The driver searches for ODBC escape sequences in all SQL statements that are executed. It replaces those ODBC escape sequences by the corresponding SQLite syntax elements.

Currently the driver recognizes and replaces only the ODBC escape sequences for date {d 'YYYY-MM-DD'}, time {t 'HH:NN:SS.FFF'} and datetime {dt 'YYYY-MM-DD HH:NN:SS.FFF'} values. More details here: Date and time datatypes.

The search and replacement for ODBC escape sequences is turned on by default. This setting can be changed the following ways:

1. Include ..;IgnoreOdbcESC=True|False;.. in the connection string used to connect to the SQLite database. This will turn off|on the search and replacement of ODBC escape sequences for all statements executed on that connection.
2. Set the value of SQL_ATTR_NOSCAN via ODBC API function SQLSetStmtAttr to SQL_NOSCAN_ON or SQL_NOSCAN_OFF after a statement has been created and before it is executed. This setting will always supersede whatever value has been specified in the connection string.

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
 );

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
 );

DATE, TIME and DATETIME data types are all stored as Double values inside the SQLite database, because SQLite has no internal Datetime data type.

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 value 1.0 is 01.01.1900 00:00:00 (DD.MM.YYYY HH:MM:SS).
The value 45292.25 is 01.01.2024 06:00:00 (DD.MM.YYYY HH:MM:SS).

The conversion to julian day is: Julian day = DateTimeDoubleValue + 2415018.5.

This interpretation of Double values as datetime values is known as Excel epoch.

If the application uses SQL_C_CHAR or SQL_C_WCHAR as value buffer data type, then the string representation of the underlying Double value is returned e.g. 45292.25 for 01.01.2024 06:00:00.

The SQLite datetime function takes a julian day and can be used to convert such double values to a formatted string for display:

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

Time values are fractions of 1:

One day = 1.0

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.

This Excel epoch interpretation of Double values as datetime values is used by MS-Excel and some programming languages like Pascal and Visual Basic. It offers 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 as Excel epoch, 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'});

 {d 'YYYY-MM-DD'}
 {t 'HH:NN:SS.FFF'}
 {dt 'YYYY-MM-DD HH:NN:SS.FFF'}

 YYYY: Four digit year
 MM: Two digit month
 DD: Two digit day
 HH: Two digit hour
 NN: Two digit minute
 SS: Two digit second
 FFF: Up to three digit milliseconds, can be omitted, if omitted the dot should be omitted too.

Note: The format of ODBC Date, Time and Datetime literals is always as given above, it does not depend on regional settings!

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.

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:
• Rename the 32-bit DLL to sqlite3_32.dll.
• Rename the 64-bit DLL to sqlite3_64.dll.
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 in 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 environment 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:

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.2.1.exe /S

Silent install and specify the installation directory:

 C:\InstallGMSQLite3Odbc_v1.2.1.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.