This is an open source ODBC driver for the wonderful SQLite 2.8. and SQLite 3. Database Engine/Library. The driver is usable but may contain bugs. Use it on your own risk.
The current source can be downloaded from
http://www.ch-werner.de/sqliteodbc/sqliteodbc-*.tar.gzWIN32 binaries (the ODBC driver DLL, install/uninstall programs) are in
http://www.ch-werner.de/sqliteodbc/sqliteodbc.exeThe binaries were made with SQLite 2.8.17, SQLite 3.43.2, MingW cross compiler and tested on Windows NT 4.0 with the query tool of MS Excel 97, with StarOffice 5.2 and OpenOffice 1.1 and 2.x. Execute the sqliteodbc.exe NSIS installer to unpack the necessary files. This installs the SQLite ODBC driver and creates a System DSN. To remove the driver use the start menu entries or the UNINST.EXE program. To create a SQLite data source use the ODBC control panel applet and provide the name of the SQLite database file to be worked on as an absolute pathname including the drive letter, eg as "C:\TEMP\SQLite.DB". The busy (or lock) timeout for the database can be specified in the respective field. If empty a default value of 100000 milliseconds is used.
The Win64 installer (sqliteodbc_w64.exe) was made with SQLite 3.43.2, MingW cross compiler and only rudimentary tested on Windows Vista 64.
Other tests were made on Linux with the "isql" command line tool and the "DataManager" GUI tool of unixODBC 2.1.0.
Since October 14th, 2001, the driver supports the data types SQL_INTEGER, SQL_TINYINT, SQL_SMALLINT, SQL_FLOAT, SQL_DOUBLE, SQL_DATE, SQL_TIME, SQL_TIMESTAMP, and SQL_VARCHAR.
Since May 25th, 2002, SQL_LONGVARCHAR is available but rather experimental. That type is used for SQLite schema containing text or varchar with a size specifier larger than 255.
The data type mapping obtains per-column meta information from the "PRAGMA table_info(...)" SQLite statement. If SELECTs are used which contain columns for which the table qualifier cannot be determined, no meta information for data type mapping is available and therefore the database source data type will be SQL_VARCHAR or SQL_LONGVARCHAR which usually maps to SQL_C_CHAR.
Restrictions of data type mapping:
Since November 17th, 2001, configure/libtool is used for the Un*x version which should automatically find the SQLite and unixODBC (or iODBC) header files and libraries. Do the usual
$ ./configure && makefollowed by
# make installin order to get /usr/local/lib/libsqliteodbc.so. Of course, you should have installed the unixODBC (or iODBC) development RPMs since the ODBC header files are required for the build of the SQLite ODBC driver.
Since May 15th, 2003, (version 0.51), there are two variants of the SQLite 2.x driver for Win32 platforms: the first (sqliteodbc.dll) linked against ISO8859-1 SQLite library exporting ODBC/SQL ANSI functions, and the second (sqliteodbcu.dll) linked against UTF-8 SQLite library exporting ODBC/SQL UNICODE functions.
The UNICODE version is experimental and allows to turn off wide character SQL data types by its configuration dialog (checkmark labelled "No WCHAR"). It is known to work on Win32. It may work on UN*X too using newer version of unixODBC.
To setup a SQLite data source using unixODBC (www.unixodbc.org):
Add the driver to /etc/odbcinst.ini:
[SQLite] Description=SQLite ODBC Driver Driver=/usr/local/lib/libsqliteodbc.so Setup=/usr/local/lib/libsqliteodbc.so Threading=2
Add a DSN to your private ~/.odbc.ini:
[mysqlitedb] Description=My SQLite test database Driver=SQLite Database=/home/johndoe/databases/mytest.db
Timeout=2000
For iODBC (www.iodbc.org, only versions 3.0.[56] tested) do the following steps:
Add the driver to /etc/odbcinst.ini:
[ODBC Drivers] ... SQLite=Installed ...
[SQLite] Driver=/usr/local/lib/libsqliteodbc.so
Add a DSN to your private ~/.odbc.ini:
[ODBC Data Sources] ... mysqlitedb=SQLite ...
[mysqlitedb] Driver=/usr/local/lib/libsqliteodbc.so Description=My SQLite test database Database=/home/johndoe/databases/mytest.db
Timeout=2000
DSN-less connection to the driver
Using the SQLDriverConnect() API it is possible to connect to a SQLite database with these strings (Win32 and UN*X)
DSN={SQLite Datasource};Database=full-path-to-db;...
DSN={SQLite3 Datasource};Database=full-path-to-db;...alternatively
Win32:
Driver={SQLite ODBC Driver};Database=full-path-to-db;...
Driver={SQLite3 ODBC Driver};Database=full-path-to-db;...UN*X (Linux RPM):
Driver=SQLITE;Database=full-path-to-db;...
Driver=SQLITE3;Database=full-path-to-db;...Connect string parameters for DSN-less connects
Database (string) name of SQLite2/3 database file; default empty PWD (string) password when built with SEE support; default empty Timeout (integer) lock time out in milliseconds; default 100000 StepAPI (boolean) if true, use sqlite[3]_step et.al.; default false NoTXN (boolean) if true, only pretend transactions; default false NoWCHAR (boolean) if true, don't support WCHAR types for character data; default false NoCreat (boolean) if true and database file doesn't exist, don't create it automatically; default false, unsupported for SQLite2 LongNames (boolean) if true, don't shorten column names; default false ShortNames (boolean) if true, enforce short column names; default false SyncPragma (string) value for PRAGMA SYNCHRONOUS; default empty FKSupport (boolean) if true, support SQLite3 foreign key constraints; default false JournalMode (string) value for PRAGMA JOURNAL_MODE; default empty OEMCP (boolean) Win32 only: if true, translate strings from/to UTF8 to current code page; default false BigInt (boolean) if true, force integer columns to SQL_BIGINT; default false JDConv (boolean) if true, use SQLite3 julian day representation for SQL_TIME, SQL_TIMESTAMP, SQL_DATE types; default false TraceFile (string) name of file to write SQLite traces to; default empty AttachAs (string) SQLite3 only: comma separated names to be the attach names to the current database file; default empty ILike (boolean) SQLite3 only: if true, treat ILIKE as LIKE in SQL to mimic PostgreSQL syntax; default false
Python sample usage with eGenix mx-Extension (see http://www.lemburg.com/files/python/mxODBC.html)
$ python
>>> import mx.ODBC.unixODBC
>>> dbc=mx.ODBC.unixODBC.connect("mysqlitedb")
>>> cur=dbc.cursor()
>>> cur.execute("create table foo (id int, name string)")
1
>>> cur.execute("insert into foo values(1, 'Me')")
1
>>> cur.execute("insert into foo values(2, 'You')")
1
>>> dbc.commit()
>>> cur.execute("select * from foo")
>>> print cur.fetchall()
[(1, 'Me'), (2, 'You')]
>>> print cur.fetchall()
[]
>>> cur.execute("drop table foo")
1
>>> dbc.commit()Build instructions for MS Visual C++ 6.0: (unsupported, needs manual fiddling makefiles depending on SQLite version)
... for SQLite 2.x.x
Setup your MSVC++ environment, ie PATH/INCLUDE/LIB, then open a command window, cd to the sqliteodbc directory and enter:
nmake -f sqliteodbc.makThis compiles the SQLite sources first, creates a link library of the necessary object files, then compiles and links the ODBC driver and the (un)install program.
If you'd like to create the UNICODE version of the driver, enter:
nmake -f sqliteodbc.mak clean nmake -f sqliteodbc.mak ENCODING=UTF8
... for SQLite 3.x.x
Setup your MSVC++ environment, ie PATH/INCLUDE/LIB, then open a command window, cd to the sqliteodbc directory and enter:
nmake -f sqlite3odbc.makThis compiles the amalgamation SQLite3 source and the ODBC driver first, then and links the ODBC driver and the (un)install program.
Names of Win32 Driver DLLs:
sqliteodbc.dll Driver with ISO8859-1 SQLite2 engine sqliteodbcu.dll Driver with UTF-8/UNICODE SQLite2 engine sqlite3odbc.dll Driver with SQLite3 engine
Build instructions for MingW cross compiler for Win32 targets:
A script named mingw-cross-build.sh is provided which contains all necessary information. It downloads the required SQLite source tarballs and builds SQLite and the ODBC drivers. The final step is creating an NSIS installer.
Build instructions for MingW cross compiler for Win64 targets:
A script named mingw64-cross-build.sh is provided which contains all necessary information. It downloads the required SQLite source tarballs and builds SQLite 3 and the ODBC driver. The final step is creating an NSIS installer.
Special build to use System.Data.SQLite on Win32/Win64
A variant of the SQLite3 ODBC driver can be build which uses internal dynamic linking to System.Data.SQLite.dll or sqlite3.dll. This feature is turned on when running the mingw*-cross-build.sh scripts with SQLITE_DLLS=2.
Build Instructions for Alpha/Tru64 (OSF1 V5.1) and HP/UX (B.11.23 U ia64)
Nikola Radovanovic had success with these commands to build all required components:
sqlite (3.6.7): ./configure --prefix=$HOME/development --disable-tcl \ CC='cc -pthread' CFLAGS='-DSQLITE_ENABLE_COLUMN_METADATA=1' gmake && gmake install
unixODBC (2.2.12): ./configure --prefix=${HOME}/development --disable-gui \ --without-x --enable-iconv=no gmake && gmake install
sqliteodbc (>0.79): ./configure --with-sqlite3=${HOME}/development \ --with-odbc=${HOME}/development --prefix=${HOME}/development \ --enable-winterface=no OSF1: gmake && gmake install HP/UX: gmake CFLAGS="+DD64" && gmake install
Build Instructions for RPM based systems
rpmbuild -tb sqliteodbc-*.tar.gz
Build Instructions for Debian based systems
tar xzf sqliteodbc-.tar.gz cd sqliteodbc- ./configure && make deb
Win32 install/remove using RUNDLL32
Each driver DLL provides entry points for ODBC driver installation and removal which can be invoked from RUNDLL32.EXE, eg
C:> RUNDLL32 [path]sqliteodbc.dll,install [quiet]
C:> RUNDLL32 [path]sqlite3odbc.dll,uninstall [quiet]
If [path] is not provided newer Windows OSes tend to favor the sqliteodbcdll in system directories over the current directory, thus better provide an absolute path to the DLL of interest. If the word "quiet" appears anywhere after the DLL/function name, no info message boxes pop up (but errors are shown).
Win64 notes
On Win64 (64 bit versions of Vista, Windows 7 ...) both 32 bit and 64 bit drivers can be installed in parallel. The 32 bit drivers are required when using 32 bit applications. In order to manage 32 bit data sources, the 32 bit ODBC admin tool C:\Windows\SysWOW64\odbcad32.exe must be used.
MacOSX notes (thanks Steve Palm)
The driver requires that you have ODBC installed and set up on your Mac. Some GUI tools are here:
http://www.iodbc.org/dataspace/iodbc/wiki/iODBC/Downloads
http://www.odbcmanager.netThe ODBC configuration files can be edited manually as on Linux. The files are at:
/Library/ODBC/odbc.ini
/Library/ODBC/odbcinst.iniExample for odbc.ini:
[ODBC Data Sources]
Mail = SQLite3 Driver
[Mail]
Driver = /usr/local/lib/libsqlite3odbc.dylib
Description = OSX Mail Database
database = /Users/n9yty/Library/Mail/V3/MailData/Envelope IndexExample for odbcinst.ini
[ODBC Drivers]
SQLite3 Driver = Installed
[SQLite3 Driver]
Driver = /usr/local/lib/libsqlite3odbc.dylib
Setup = /usr/local/lib/libsqlite3odbc.dylibThe iODBC driver manages provides the utility programs iodbctest and iodbctestw (UNICODE) which can be run in Terminal to verify the installed data sources.
TODO:
2023-10-23 Christian Werner mailto:chw@ch-werner.de