Sqlite3 odbc driver for windows

Devart ODBC Driver for SQLite provides a high-performance and feature-rich connectivity solution for ODBC-compliant applications to access SQLite databases from Windows, macOS, and Linux, both 32-bit and 64-bit. Apple Silicon M1 is supported. Full support for standard ODBC API functions and data types implemented in our driver makes interaction of your application with SQLite fast, easy and extremely handy.
Available in both installer formats, MSI and EXE.

Key Features:

Direct Connection

With our connectivity solution, various ODBC-aware applications are able to establish a connection to an SQLite database not only by means of the native SQLite client library, but also in the Direct mode, without involving any external libraries. This improves performance of your applications and streamlines the deployment process, since there is no need to distribute any additional client software with the driver.

SQLite Database Encryption

To protect your data from unauthorized access, our ODBC driver for SQLite provides a powerful and customizable SQLite Database Encryption engine. You no longer have to spend money to buy expensive SQLite client libraries with support for encryption. With our driver the following algorithms will be able to protect your data: AES, Blowfish, TripleDES, Cast128, RC4.

Cost-Effective Deployment

Our ODBC driver is a standalone installation file that doesn’t require the user to deploy and configure any additional software such as a database client or a vendor library. Deployment costs are reduced drastically, especially when using the silent install method with an OEM license in large organizations that have hundreds of machines.

ODBC Conformance

The driver provides full support for common ODBC interface:

• ODBC API Functions support
• ODBC Data Types support
  In addition, we provide support for Advanced Connection String parameters, thus allowing any desktop and web applications to connect to SQLite from various environments and platforms that support ODBC.

Advanced Data Conversion

We have implemented advanced Data Conversion mechanisms that provide bi-directional mapping between any SQLite and ODBC data types.

Bulk Updates

Moreover, with our driver you can perform bulk updates to SQLite database by combining SQL statements into batches, thus simplifying and speeding up large data modification to SQLite.

SQLite Compatibility

Our driver is compatible with SQLite version 3.0 or higher.

Integration

The driver is compatible with 3rd-party tools for data analysis, such as Microsoft Excel, and integrates with various IDEs and systems like Visual Studio, etc.

For the complete list of compatible tools and environments visit the Compatibility page.

Platforms Variety

ODBC Driver for SQLite can be used with 32-bit and 64-bit applications on both x32 and x64 platforms, so there is no need to additionally configure the driver, applications or environment.

Fully Unicode-Compliant Driver

With our fully Unicode-compliant driver, you can properly retrieve and modify any data in multilingual SQLite databases, regardless of their character set: Latin, Cyrillic, Hebrew, Chinese, etc., and in any language environment.

SQLite ODBC Driver
------------------

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.gz

WIN32 binaries (the ODBC driver DLL, install/uninstall programs) are in

    http://www.ch-werner.de/sqliteodbc/sqliteodbc.exe

The binaries were made with SQLite 2.8.17, SQLite 3.32.3, 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.32.3,
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:

- Integer and floating point columns in the database are reported
  as NULLs when no digit seen in the column, otherwise all digits
  up to end of string or non-digit are interpreted as the value,
  i.e. '10blurk' is ten, '0blurk' is zero, but 'blurk' is NULL.
- Format for SQL_DATE is YYYY-MM-DD or YYYYMMDD
- Format for SQL_TIME is hh:mm:ss or hhmmss
- Format for SQL_TIMESTAMP is
      YYYYMMDDhhmmss[fraction]
  or  YYYY-MM-DD hh:mm:ss[.fraction]
  or  hh:mm:ss[.fraction] YYYY-MM-DD
  The fractional part is expressed as 1E-09 seconds
- The driver puts the ODBC string representations for date/time,
  (eg for "{ts '2001-10-10 12:58:00'}" the substring within the
  single quotes) directly into the SQLite column
- When the DSN Option "JDConv" (Julian Day conversion) is enabled
  the SQLite 3 driver translates floating point column data
  interpreted as Julian Day to/from SQL_DATE, SQL_TIME, and
  SQL_TIMESTAMP data types (supported since May 2013)

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 && make

followed by

    # make install

in 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):

  1. 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

  2. Add a DSN to your private ~/.odbc.ini:

    [mysqlitedb]
    Description=My SQLite test database
    Driver=SQLite
    Database=/home/johndoe/databases/mytest.db
    # optional lock timeout in milliseconds
    Timeout=2000

For iODBC (www.iodbc.org, only versions 3.0.[56] tested) do the
following steps:

  1. Add the driver to /etc/odbcinst.ini:

    [ODBC Drivers]
    ...
    SQLite=Installed
    ...

    [SQLite]
    Driver=/usr/local/lib/libsqliteodbc.so

  2. 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
    # optional lock timeout in milliseconds
    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


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

1. Extract the source tarball sqliteodbc.tar.gz
2. Extract the official SQLite 2.x.x sources in the sqliteodbc
   directory which resulted from step 1. Optionally, apply the
   sqlite-locale-patch-28* which matches your SQLite version
3. Setup your MSVC++ environment, ie PATH/INCLUDE/LIB, then
   open a command window, cd to the sqliteodbc directory and enter:

        nmake -f sqliteodbc.mak

   This 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.
4. 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

1. Extract the source tarball sqliteodbc.tar.gz
2. Extract the amalgamation SQLite 3.x.x. sources in the sqliteodbc
   directory which resulted from step 1.
3. Setup your MSVC++ environment, ie PATH/INCLUDE/LIB, then
   open a command window, cd to the sqliteodbc directory and enter:

        nmake -f sqlite3odbc.mak

   This 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/shell using RUNDLL32

  Each driver DLL provides entry points for ODBC driver installation
  and removal which can be invoked from RUNDLL32.EXE, eg

  ### install sqliteodbc.dll
  C:\> RUNDLL32 [path]sqliteodbc.dll,install [quiet]

  ### remove sqlite3odbc.dll
  C:\> RUNDLL32 [path]sqlite3odbc.dll,uninstall [quiet]

  If [path] is not provided newer Windows OSes tend to favor the
  sqlite*odbc*dll 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).

  An (interactive or batch) SQLite shell can be invoked, too, eg

  ### run SQLite shell on database C:\bla\my.db
  C:\> RUNDLL32 [path]sqliteodbc.dll,shell C:\bla\my.db ...

  ### batch run with given SQL
  C:\> RUNDLL32 [path]sqliteodbc.dll,shell -batch C:\bla\my.db
       "select * from table" <NUL: 2>NUL: >out.txt


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.net

  The ODBC configuration files can be edited manually as on Linux.
  The files are at:

    /Library/ODBC/odbc.ini
    /Library/ODBC/odbcinst.ini

  Example 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 Index

  Example for odbcinst.ini

    [ODBC Drivers]
    SQLite3 Driver        = Installed

    [SQLite3 Driver]
    Driver = /usr/local/lib/libsqlite3odbc.dylib
    Setup  = /usr/local/lib/libsqlite3odbc.dylib

  The iODBC driver manages provides the utility programs iodbctest
  and iodbctestw (UNICODE) which can be run in Terminal to verify
  the installed data sources.


TODO:

- improve documentation


2020-06-20
Christian Werner
mailto:chw@ch-werner.de

SQLite ODBC Driver

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.gz

WIN32 binaries (the ODBC driver DLL, install/uninstall programs) are in

http://www.ch-werner.de/sqliteodbc/sqliteodbc.exe

The binaries were made with SQLite 2.8.17, SQLite 3.32.3, 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.32.3,
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:

• Integer and floating point columns in the database are reported
  as NULLs when no digit seen in the column, otherwise all digits
  up to end of string or non-digit are interpreted as the value,
  i.e. ’10blurk’ is ten, ‘0blurk’ is zero, but ‘blurk’ is NULL.
• Format for SQL_DATE is YYYY-MM-DD or YYYYMMDD
• Format for SQL_TIME is hh:mm:ss or hhmmss
• Format for SQL_TIMESTAMP is
  YYYYMMDDhhmmss[fraction]
  or YYYY-MM-DD hh:mm:ss[.fraction]
  or hh:mm:ss[.fraction] YYYY-MM-DD
  The fractional part is expressed as 1E-09 seconds
• The driver puts the ODBC string representations for date/time,
  (eg for «{ts ‘2001-10-10 12:58:00’}» the substring within the
  single quotes) directly into the SQLite column
• When the DSN Option «JDConv» (Julian Day conversion) is enabled
  the SQLite 3 driver translates floating point column data
  interpreted as Julian Day to/from SQL_DATE, SQL_TIME, and
  SQL_TIMESTAMP data types (supported since May 2013)

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 && make

followed by

# make install

in 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):

1. 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

1. Add a DSN to your private ~/.odbc.ini:

[mysqlitedb]
Description=My SQLite test database
Driver=SQLite
Database=/home/johndoe/databases/mytest.db
# optional lock timeout in milliseconds
Timeout=2000

For iODBC (www.iodbc.org, only versions 3.0.[56] tested) do the
following steps:

1. Add the driver to /etc/odbcinst.ini:

[ODBC Drivers]
...
SQLite=Installed
...

[SQLite]
Driver=/usr/local/lib/libsqliteodbc.so

1. 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
# optional lock timeout in milliseconds
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

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

1. Extract the source tarball sqliteodbc.tar.gz

2. Extract the official SQLite 2.x.x sources in the sqliteodbc
   directory which resulted from step 1. Optionally, apply the
   sqlite-locale-patch-28* which matches your SQLite version

3. Setup your MSVC++ environment, ie PATH/INCLUDE/LIB, then
   open a command window, cd to the sqliteodbc directory and enter:

    nmake -f sqliteodbc.mak
   

   This 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.

4. 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

1. Extract the source tarball sqliteodbc.tar.gz

2. Extract the amalgamation SQLite 3.x.x. sources in the sqliteodbc
   directory which resulted from step 1.

3. Setup your MSVC++ environment, ie PATH/INCLUDE/LIB, then
   open a command window, cd to the sqliteodbc directory and enter:

    nmake -f sqlite3odbc.mak
   

   This 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/shell using RUNDLL32

Each driver DLL provides entry points for ODBC driver installation
and removal which can be invoked from RUNDLL32.EXE, eg

install sqliteodbc.dll

C:> RUNDLL32 [path]sqliteodbc.dll,install [quiet]

remove sqlite3odbc.dll

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

An (interactive or batch) SQLite shell can be invoked, too, eg

run SQLite shell on database C:\bla\my.db

C:> RUNDLL32 [path]sqliteodbc.dll,shell C:\bla\my.db …

batch run with given SQL

C:> RUNDLL32 [path]sqliteodbc.dll,shell -batch C:\bla\my.db
«select * from table» <NUL: 2>NUL: >out.txt

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.net

The ODBC configuration files can be edited manually as on Linux.
The files are at:

/Library/ODBC/odbc.ini
/Library/ODBC/odbcinst.ini

Example 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 Index

Example for odbcinst.ini

[ODBC Drivers]
SQLite3 Driver        = Installed

[SQLite3 Driver]
Driver = /usr/local/lib/libsqlite3odbc.dylib
Setup  = /usr/local/lib/libsqlite3odbc.dylib

The iODBC driver manages provides the utility programs iodbctest
and iodbctestw (UNICODE) which can be run in Terminal to verify
the installed data sources.

TODO:

• improve documentation

2020-06-20
Christian Werner
mailto:[email protected]

Using WinSQL with SQLite

SQLite is a popular embedded database that is used in many off-the-shelf applications. Once such popular application is Mozilla Firefox, which stores user preferences and browser history in a relational database.

Following white paper demonstrate how to download SQLite’s ODBC and using it with WinSQL. For demonstration purposes we will take an existing database that is used by Mozilla Firefox and view its data.

Download and installation

The first step is to download an ODBC driver for SQLite, which is available at http://www.ch-werner.de/sqliteodbc/. Download sqliteodbc.exe file from this website.  IMPORTANT: Download this file even if you are running a 64-bit version of Windows. Since WinSQL is a 32-bit application, it will only work with a 32-bit driver.

After downloading the file, double click it to run the installer. This opens the following screen.

Click Next all the way to accept default values.

Confirming the installation

Aside from installing the necessary files, this will also register 3 ODBC drivers on your machine. The following steps confirm the driver is installed and is correctly registered.

• Start WinSQL on your machine
• Click Open ODBC Manager under the File menu. Refer to the image below.

  

• This opens the ODBC manager on your machine. Click the Drivers tab and ensure you see the 3 drivers for SQLite.

  

Preparing Firefox’s database

Since Firefox is a production application, it is recommended you make a backup of the database file and then view its contents. We strongly discourage using the database in its default location.

Skip this step if you are not planning to use Firefox’s database for this exercise.

• Using Windows Explorer navigate to the following location on your hard disk
• XP, 2002 & 2003 — C:\Documents and Settings\YourUserName\Application Data\Mozilla\Firefox\Profiles\XXXXX.default
• Vists, 2008 and Windows 7 — C:\Users\YourUserName\AppData\Roaming\\Mozilla\Firefox\Profiles\XXXXX.default

  Replace XXXXX with the actual values that appear on your machine.

• You will see a handful of files with extension .sqlite . I recommend you copy places.sqlite to another folder, for example c:\temp\places.sqlite
• Rename places.sqlite to places.db in this temp folder. This is because .db extension is the default extension for SQLite and is a bit easier to work with.

Creating an ODBC DSN

The next step is to create an ODBC DSN referring to c:\temp\places.db file.

• Start WinSQL. Click Open ODBC Manager under the File menu to bring up the ODBC manager.
• Click the Add button. This brings up the «Create New Data Source» window
• Select SQLite3 ODBC Driver from the list and click Finish

  

• The next screen allows you to specify a name for this DNS and the path for your database.

  

• To create a new database file, specify a file name that does not exist on your hard drive. If the database file is not found, the driver will create a new blank database.

Connecting from WinSQL

Finally, create a new connecting in WinSQL. Select the newly created DNS in the previous step and click OK. SQLite does not require any user id/password. Therefore, you can either leave these values blank or use some arbitrary string.

Once connected you can write SQL queries or view existing catalog.