1SQLite ODBC Driver
2------------------
3
4This is an open source ODBC driver for the wonderful
5SQLite 2.8.* and SQLite 3.* Database Engine/Library.
6The driver is usable but may contain lots of memory
7leaks and all other kinds of bugs. Use it on your own
8risk.
9
10The current source can be downloaded from
11
12 http://www.ch-werner.de/sqliteodbc/sqliteodbc-*.tar.gz
13
14WIN32 binaries (the ODBC driver DLL, install/uninstall programs) are in
15
16 http://www.ch-werner.de/sqliteodbc/sqliteodbc.exe
17
18The binaries were made with SQLite 2.8.17, SQLite 3.7.8, MingW
19cross compiler and tested on Windows NT 4.0 with the query tool
20of MS Excel 97, with StarOffice 5.2 and OpenOffice 1.1 and 2.x.
21Execute the sqliteodbc.exe NSIS installer to unpack the necessary
22files. This installs the SQLite ODBC driver and creates a System DSN.
23To remove the driver use the start menu entries or the UNINST.EXE
24program. To create a SQLite data source use the ODBC control panel
25applet and provide the name of the SQLite database file to be worked
26on as an absolute pathname including the drive letter, eg as
27"C:\TEMP\SQLite.DB". The busy (or lock) timeout for the database
28can be specified in the respective field. If empty a default value
29of 100000 milliseconds is used.
30
31The Win64 installer (sqliteodbc_w64.exe) was made with SQLite 3.7.8,
32MingW cross compiler and only rudimentary tested on Windows Vista 64.
33
34Other tests were made on Linux with the "isql" command line tool
35and the "DataManager" GUI tool of unixODBC 2.1.0.
36
37
38Since October 14th, 2001, the driver supports the data types SQL_INTEGER,
39SQL_TINYINT, SQL_SMALLINT, SQL_FLOAT, SQL_DOUBLE, SQL_DATE, SQL_TIME,
40SQL_TIMESTAMP, and SQL_VARCHAR.
41
42Since May 25th, 2002, SQL_LONGVARCHAR is available but rather
43experimental. That type is used for SQLite schema containing text
44or varchar with a size specifier larger than 255.
45
46The data type mapping obtains per-column meta information from the
47"PRAGMA table_info(...)" SQLite statement. If SELECTs are used which
48contain columns for which the table qualifier cannot be determined,
49no meta information for data type mapping is available and therefore
50the database source data type will be SQL_VARCHAR or SQL_LONGVARCHAR
51which usually maps to SQL_C_CHAR.
52
53Restrictions of data type mapping:
54
55- Integer and floating point columns in the database are reported
56 as NULLs when no digit seen in the column, otherwise all digits
57 up to end of string or non-digit are interpreted as the value,
58 i.e. '10blurk' is ten, '0blurk' is zero, but 'blurk' is NULL.
59- Format for SQL_DATE is YYYY-MM-DD or YYYYMMDD
60- Format for SQL_TIME is hh:mm:ss or hhmmss
61- Format for SQL_TIMESTAMP is
62 YYYYMMDDhhmmss[fraction]
63 or YYYY-MM-DD hh:mm:ss[.fraction]
64 or hh:mm:ss[.fraction] YYYY-MM-DD
65 The fractional part is expressed as 1E-09 seconds
66- The driver puts the ODBC string representations for date/time,
67 (eg for "{ts '2001-10-10 12:58:00'}" the substring within the
68 single quotes) directly into the SQLite column
69
70
71Since November 17th, 2001, configure/libtool is used for the Un*x
72version which should automatically find the SQLite and unixODBC
73(or iODBC) header files and libraries. Do the usual
74
75 $ ./configure && make
76
77followed by
78
79 # make install
80
81in order to get /usr/local/lib/libsqliteodbc.so.
82Of course, you should have installed the unixODBC (or iODBC)
83development RPMs since the ODBC header files are required for
84the build of the SQLite ODBC driver.
85
86Since May 15th, 2003, (version 0.51), there are two variants
87of the SQLite 2.x driver for Win32 platforms: the first (sqliteodbc.dll)
88linked against ISO8859-1 SQLite library exporting ODBC/SQL ANSI
89functions, and the second (sqliteodbcu.dll) linked against UTF-8
90SQLite library exporting ODBC/SQL UNICODE functions.
91
92The UNICODE version is experimental and allows to turn off
93wide character SQL data types by its configuration dialog
94(checkmark labelled "No WCHAR"). It is known to work on Win32.
95It may work on UN*X too using newer version of unixODBC.
96
97To setup a SQLite data source using unixODBC (www.unixodbc.org):
98
99 1. Add the driver to /etc/odbcinst.ini:
100
101 [SQLite]
102 Description=SQLite ODBC Driver
103 Driver=/usr/local/lib/libsqliteodbc.so
104 Setup=/usr/local/lib/libsqliteodbc.so
105 Threading=2
106
107 2. Add a DSN to your private ~/.odbc.ini:
108
109 [mysqlitedb]
110 Description=My SQLite test database
111 Driver=SQLite
112 Database=/home/johndoe/databases/mytest.db
113 # optional lock timeout in milliseconds
114 Timeout=2000
115
116For iODBC (www.iodbc.org, only versions 3.0.[56] tested) do the
117following steps:
118
119 1. Add the driver to /etc/odbcinst.ini:
120
121 [ODBC Drivers]
122 ...
123 SQLite=Installed
124 ...
125
126 [SQLite]
127 Driver=/usr/local/lib/libsqliteodbc.so
128
129 2. Add a DSN to your private ~/.odbc.ini:
130
131 [ODBC Data Sources]
132 ...
133 mysqlitedb=SQLite
134 ...
135
136 [mysqlitedb]
137 Driver=/usr/local/lib/libsqliteodbc.so
138 Description=My SQLite test database
139 Database=/home/johndoe/databases/mytest.db
140 # optional lock timeout in milliseconds
141 Timeout=2000
142
143
144Python sample usage with eGenix mx-Extension
145(see http://www.lemburg.com/files/python/mxODBC.html)
146
147 $ python
148 >>> import mx.ODBC.unixODBC
149 >>> dbc=mx.ODBC.unixODBC.connect("mysqlitedb")
150 >>> cur=dbc.cursor()
151 >>> cur.execute("create table foo (id int, name string)")
152 1
153 >>> cur.execute("insert into foo values(1, 'Me')")
154 1
155 >>> cur.execute("insert into foo values(2, 'You')")
156 1
157 >>> dbc.commit()
158 >>> cur.execute("select * from foo")
159 >>> print cur.fetchall()
160 [(1, 'Me'), (2, 'You')]
161 >>> print cur.fetchall()
162 []
163 >>> cur.execute("drop table foo")
164 1
165 >>> dbc.commit()
166
167
168Build instructions for MS Visual C++ 6.0:
169
170... for SQLite 2.x.x
171
1721. Extract the source tarball sqliteodbc.tar.gz
1732. Extract the official SQLite 2.x.x sources in the sqliteodbc
174 directory which resulted from step 1. Optionally, apply the
175 sqlite-locale-patch-28* which matches your SQLite version
1763. Setup your MSVC++ environment, ie PATH/INCLUDE/LIB, then
177 open a command window, cd to the sqliteodbc directory and enter:
178
179 nmake -f sqliteodbc.mak
180
181 This compiles the SQLite sources first, creates a link library
182 of the necessary object files, then compiles and links the ODBC
183 driver and the (un)install program.
1844. If you'd like to create the UNICODE version of the driver, enter:
185
186 nmake -f sqliteodbc.mak clean
187 nmake -f sqliteodbc.mak ENCODING=UTF8
188
189... for SQLite 3.x.x
190
1911. Extract the source tarball sqliteodbc.tar.gz
1922. Extract the amalgamation SQLite 3.x.x. sources in the sqliteodbc
193 directory which resulted from step 1.
1943. Setup your MSVC++ environment, ie PATH/INCLUDE/LIB, then
195 open a command window, cd to the sqliteodbc directory and enter:
196
197 nmake -f sqlite3odbc.mak
198
199 This compiles the amalgamation SQLite3 source and the ODBC driver
200 first, then and links the ODBC driver and the (un)install program.
201
202
203Names of Win32 Driver DLLs:
204
205 sqliteodbc.dll Driver with ISO8859-1 SQLite2 engine
206 sqliteodbcu.dll Driver with UTF-8/UNICODE SQLite2 engine
207 sqlite3odbc.dll Driver with SQLite3 engine
208
209
210Build instructions for MingW cross compiler for Win32 targets:
211
212 A script named mingw-cross-build.sh is provided which contains
213 all necessary information. It downloads the required SQLite
214 source tarballs and builds SQLite and the ODBC drivers. The
215 final step is creating an NSIS installer.
216
217
218Build instructions for MingW cross compiler for Win64 targets:
219
220 A script named mingw64-cross-build.sh is provided which contains
221 all necessary information. It downloads the required SQLite
222 source tarballs and builds SQLite 3 and the ODBC driver. The
223 final step is creating an NSIS installer.
224
225
226Win32 install/remove/shell by RUNDLL32
227
228 Each driver DLL provides entry points for ODBC driver installation
229 and removal which can be invoked from RUNDLL32.EXE, eg
230
231 ### install sqliteodbc.dll
232 C:\> rundll32 [path]sqliteodbc.dll,install [quiet]
233
234 ### remove sqlite3odbc.dll
235 C:\> rundll32 [path]sqlite3odbc.dll,uninstall [quiet]
236
237 If [path] is not provided newer Windows OSes tend to favor the
238 sqlite*odbc*dll in system directories over the current directory,
239 thus better provide an absolute path to the DLL of interest.
240 If the word "quiet" appears anywhere after the DLL/function
241 name, no info message boxes pop up (but errors are shown).
242
243 An (interactive or batch) SQLite shell can be invoked, too, eg
244
245 ### run SQLite shell on database C:\bla\my.db
246 C:\> rundll32 [path]sqliteodbc.dll,shell C:\bla\my.db ...
247
248 ### batch run with given SQL
249 C:\> rundll32 [path]sqliteodbc.dll,shell -batch C:\bla\my.db
250 "select * from table" <NUL: 2>NUL: >out.txt
251
252
253Win64 notes
254
255 On Win64 (64 bit versions of Vista, Windows 7) both 32 bit and 64 bit
256 drivers can be installed in parallel. The 32 bit drivers are required
257 when using 32 bit applications. In order to manage 32 bit data
258 sources, the 32 bit ODBC admin tool C:\Windows\SysWOW64\odbcad32.exe
259 must be used.
260
261
262DSN-less connection to the driver
263
264 Using the SQLDriverConnect() API it should be possible to connect to
265 a SQLite database with these strings (Win32 and UN*X)
266
267 DSN=SQLite Datasource;Database=full-path-to-db;...
268 DSN=SQLite3 Datasource;Database=full-path-to-db;...
269
270 alternatively
271
272 Win32:
273
274 Driver=SQLite ODBC Driver;Database=full-path-to-db;...
275 Driver=SQLite3 ODBC Driver;Database=full-path-to-db;...
276
277 UN*X (Linux RPM):
278
279 Driver=SQLITE;Database=full-path-to-db;...
280 Driver=SQLITE3;Database=full-path-to-db;...
281
282
283Build Instructions for Alpha/Tru64 (OSF1 V5.1) and HP/UX (B.11.23 U ia64)
284
285 Nikola Radovanovic had success with these commands to build
286 all required components:
287
288 sqlite (3.6.7):
289 ./configure --prefix=$HOME/development --disable-tcl \
290 CC='cc -pthread' CFLAGS='-DSQLITE_ENABLE_COLUMN_METADATA=1'
291 gmake && gmake install
292
293 unixODBC (2.2.12):
294 ./configure --prefix=${HOME}/development --disable-gui \
295 --without-x --enable-iconv=no
296 gmake && gmake install
297
298 sqliteodbc (>0.79):
299 ./configure --with-sqlite3=${HOME}/development \
300 --with-odbc=${HOME}/development --prefix=${HOME}/development \
301 --enable-winterface=no
302 OSF1: gmake && gmake install
303 HP/UX: gmake CFLAGS="+DD64" && gmake install
304
305
306Build Instructions for RPM based systems
307
308 rpmbuild -tb sqliteodbc-*.tar.gz
309
310
311Build Instructions for Debian based systems
312
313 tar xzf sqliteodbc-*.tar.gz
314 cd sqliteodbc-*
315 ./configure && make deb
316
317
318Special build to use System.Data.SQLite on Win32/Win64
319
320 A variant of the SQLite3 ODBC driver can be build which uses
321 internal dynamic linking to System.Data.SQLite.dll or sqlite3.dll.
322 This feature is turned on when running the mingw*-cross-build.sh
323 scripts with SQLITE_DLLS=2.
324
325
326
327TODO:
328
329- improve documentation
330
331
3322011-11-15
333Christian Werner
334mailto:chw@ch-werner.de
335
336