Tclodbc

Purpose: To discuss ODBC bindings for Tcl. TclODBC (developed by Roy Nurmi) is currently at version 2.3 and resides in SourceForge ( http://www.sf.net/projects/tclodbc ). It is based on the ODBC V2 API and has support for Tcl 7.6, 8.0. and 8.1+ on Win32-Intel systems. A UNIX version is also available, but appears to not be in the mainstream of development.

TclODBC currently recognizes the following SQL standard datatypes: CHAR, NUMERIC, DECIMAL, INTEGER, SMALLINT, FLOAT, REAL, DOUBLE, VARCHAR; and the extended types: DATE, TIME, TIMESTAMP, LONGVARCHAR, BINARY, VARBINARY, LONGVARBINARY, BIGINT, TINYINT, BIT.

TclODBC V2.x is based on ODBC V2 and therefore does not handle Unicode datatypes (e.g., NCHAR, NVARCHAR for MS SQL Server). It does have the ability to specify which 8-bit character set encoding the target database is operating in and translate to and from Tcl's UTF-8 encoding.

Access to schema-related data is also available.

TclODBC V2.x has been used or tested with: SQLBase, MS SQL Server, Oracle, Progress, Sybase SQLAnywhere, Informix Online Dynamic Server, PostgresSQL, MySQL, Microsoft Access, Paradox, Unify DataServer.

Sample usage:

   % database connect db "DRIVER=SQL Server;SERVER=dbs1;DBQ=mydb"
   db
   % set ids [db "select id from employees where salary < 1000"]
   {222 333 444}

The windows version of tclodbc comes with a precompiled tclodbc.dll, the unix version does not.

Unfortunately, the packaged unix version does not compile out of the box, but the remedy is very simple:

• get tclodbc2.2.tar.gz from [1]
• get the config subdir from [2] or swipe it (like I did) from oratcl 3.3

 $ cd /tmp
 $ tar xzf tclodbc2.2.tar.gz
 $ cd tclodbc
 $ tar xzf ../tclodbc2.2-config.tar.gz
 $ autoconf

Now you have a working ./configure and you can:

 $ ./configure
 $ make
 $ su
 Password:
 # make install

You should have a working tclodbc.so, for maximum enjoyment, I suggest using it with nstcl.

- PS

#sample session with csv-file:

% package require tclodbc
2.3
(bin) 4 % database connect db DSNcsv
db
(bin) 6 % db tables
{{U:\CSV} {} sample.csv TABLE {}}
(bin) 7 % db columns sample.csv
{{U:\CSV} {} sample.csv n 4 INTEGER 10 4 0 10 1 {} {} 4 {} {} 1 YES 1} {{U:\CSV} {} sample.csv descr 12 CHAR 255 510 {} {} 1 {} {} 12 {} 510 2 YES 2}
(bin) 9 % puts [lindex [db columns sample.csv] 1]
{U:\CSV} {} sample.csv descr 12 CHAR 255 510 {} {} 1 {} {} 12 {} 510 2 YES 2
(bin) 11 % puts [lindex [lindex [db columns sample.csv] 1] 2]
sample.csv
(bin) 12 % puts [lindex [lindex [db columns sample.csv] 1] 3]
descr
(bin) 13 % puts [lindex [lindex [db columns sample.csv] 1] 4]
12
(bin) 14 % db statement s "select * from sample.csv"
s
(bin) 15 % s
{1 {this is a descr}} {2 test}
dev) 11 % db statement i "insert into sample.csv values (12, 'new description')"
i
(dev) 12 % i
1
(dev) 13 % db statement s "select * from sample.csv"
s
(dev) 14 % s
{1 {this is a descr}} {2 test} {12 {new description}}