Tclodbc

TclODBC , a Tcl extension by Roy Nurmi, provides an interface to ODBC. Supports multiple simultaneous connections, transaction handling, precompiled SQL statements and SQL argument handling. Unicode support is available with later versions of Tcl

Documentation

Jussi Kuosa is working to document ... Linux TclODBC ...

Attributes

website
TclODBC
website (old)
http://www.solagem.fi/%7Ernurmi/tclodbc.html
latest version
2.5.1

See Also

a form for Access Database
TimpleSQL
written around Tclodbc and is quite handy for interfacing applications to databases and also for cgi work.

Downloading

PS 2006-06-16:

The sources in CSV are much more current and can actually be built on most systems. The 2.2.1 source release is hard to build, or not at all on modern systems.

In Feb, 2007, The Changelog for the SF.net tclodbc extension shows a date of February, 2006. That change appears to be related to an update to TEA 3.5.

Steve Aronson provided the following downloads for Power Macintosh

Tmactclodbc21.sit.hqx
Tclodbc2.1 for the Power Macintosh
mactclodbc21s.sit.hqx
Tclodbc2.1 for the Power Macintosh (source code) and CodeWarrior project

Building

If you want to compile tclodbc for unix, you should get the CSV version from sourceforge:

cvs -d:pserver:[email protected]:/cvsroot/tclodbc login
cvs -z3 -d:pserver:[email protected]:/cvsroot/tclodbc co -P

This includes a proper configure script.

Please note the suse compile hints below. Those will probably be needed for any recent OS.


Jelco:

Just an addition for those who want to compile the tclodbc library (2.2) for Suse Linux 9.1:

You need to have unixODBC 2.2.8 or higher installed.

On top of the tclodbc2.2tar.gz and the tclodbc2.2-config.tar.gz you need to add two files to the config subdirectory:

Dowlnload these files or find them in /usr/lib/lbltdl or /usr/lib/rpm. Copy them to the tclodbc/config directory.

Do an autoconf, execute ./configure and a Makefile will be created. What happened in my installation was that the compiled libtclodbc2.2.so was not recognized as a valid library by ranlib (or ar). The problem was created by gcc in the makefile. So I replaced the line:

SHLIB_LD = gcc -pipe -shared

by:

SHLIB_LD = g++ -pipe -shared

This Makefile created a valid tclodbc library for Suse Linux 9.1.

I enclosed this library in the Database Fishing Tool (daFT) , which is written in tcl/tk and distributed as a starkit.


JL:

A further addition to get it compiled with SuSE 9.3:

I found, that I had to link libtclodbc2.2 not only against libodbc.so but also against libodbcinst.so, because the Symbol "SQLConfigDataSource" (and "SQLConfigDrivers") migrated from libodbc.so (in unixODBC-2.2.6 which came with SuSE 9.0) to libodbcinst.so (in unixODBC-2.2.10 from SuSE 9.3).

So (quick and dirty) I changed

SHLIB_LDFLAGS =  -L/usr/lib -lodbc

to

SHLIB_LDFLAGS =  -L/usr/lib -lodbc -lodbcinst

in the Makefile.

If you compile the lib this way on SuSE 9.0 (with -lodbcinst), you can use it on SuSE 9.0 and 9.3...


Compiling on Suse 10.0 on x86-64

I needed to change two things in the generated Makefile:

 SHLIB_LD = g++ -pipe -shared

and

 LIBS = -L/usr/lib64 -lodbc -lodbcinst

--[2006Jun16] PS

kostix 10-May-2007: Hit the same problem, I think, so let me clarify the solution: for some reason gcc is used for both compiling and linking instead of g++ when building from CVS on Debian Sarge. This results in not linking against libstdc++.so.N which, in turn, results in unresolved symbols at the attempt to [load] it:

 $ make
 ...
 $ tclsh
 % load ./libtclodbc2.5.so
 couldn't load file "./libtclodbc2.5.so": ./libtclodbc2.5.so: undefined symbol: _ZTVN10__cxxabiv117__class_type_infoE
 % ^D
 $ ldd libtclodbc2.5.so|grep ++
 $

In fact, g++ must be chosen, since it compiles C++ code, but the configure only provides you with the --enable-gxx option which runs some tests for g++, but the relevant variables in the generatd Makefile end up containing references to gcc.

The above fix for SHLIB_LD fixed this (also I have changed CC from gcc -pipe to g++ -pipe but this seems reundant):

 $ ldd libtclodbc2.5.so|grep ++
     libstdc++.so.5 => /usr/lib/libstdc++.so.5 (0x00176000)

kostix 2005-05-10: Generation of pkgIndex.tcl also has problems: by default it's created empty with some extensive comment about working auto-loading.

If you want the [package require] support, you are seemingly expected to provide the --disable-load option to ./configure, but this generates broken Makefile which cannot link the target library.

The workaround to this is to omit --disable-load then fix the generated Makefile:

  • Rename the pkgIndex.tcl rule to pkgIndex.tcl-<whatever-you-want>;
  • Rename the pkgIndex.tcl-hand rule to pkgIndex.tcl.

Then proceed with make as usual.


PS:

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:

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

Description

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


LV: To which version of tclodbc does the above apply - 2.x or 0.0?


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, PostgreSQL, MySQL, Microsoft Access, Paradox, Unify DataServer, DB2, SQLite

NB: ODBC is way slower than using native database accessors. If at all possible you should almost certainly avoid it if you can.

At one point

Connection strings

SQLite3

database connect db {Driver=SQLite3 ODBC Driver;Database=C:\stuff\sqlite3data.db}

SQL Server

database connect db {DRIVER=SQL Server;SERVER=dbs1;DATABASE=mydb;Username=user}

MySQL

database connect db {Driver=MySQL ODBC 3.51 Driver;DATABASE=mydb;SERVER=myserver;PORT=3306;UID=jose;PWD=password}

PostgreSQL

database connect db {Driver=PostgreSQL ANSI;Database=mydb;Servername=myserver;Port=5432;Username=jose;Password=password}

MS Access

set driver "Microsoft Access Driver (*.mdb)"
set dbfile "C:\\Program Files\\Microsoft Office\\office\\samples\\Northwind.mdb"
database connect db "DRIVER=$driver;DBQ=$dbfile"

Usage

phk: Due to different ODBC version or specific features, the statement

db columns $tablename

can give different results on different databases

As Roy Nurmi showed me, there is a way to get a description:

db statement s columns
s $tablename
s columns

Basic Examples

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

Another generic example:

package require tclodbc

database connect mydb "name-of-datasource" "username" "password"

set rows [mydb {SELECT a,c,b FROM tablename WHERE condition}]
foreach row $rows {
    foreach {a b c} $row {break}
    do something with the column values $a $b $c
}

mydb disconnect

Example: Basic Operations

JDM 2006-08-02: Are there any examples anywhere of an UPDATE, DELETE and/or INSERT using tclodbc? I have searched around, but have not found any.

DPE 2006-08-03: This just uses SQL.

For example: I have a very simple table with 2 columns: DBID and Name (DBID is an identity column so is not specified on insert)

# To Get a list of Microsoft SQL Servers for the connection string use the following
# which will return something like "SQLEXPRESS SQL2005".  It does not return any blank
# instance name which on my machine is the default SQL Server which is SQL Server 2000
package require registry
registry values "HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL"

# Connect to database called "myDatabaseName" on the local machine
# (replace [info hostname] which the desired hostname or leave for the local host)

# This is used for Microsoft SQL Server 2000 on my machine (it is the default)
database connect db "DRIVER=SQL Server;SERVER=localhost;DATABASE=myDatabaseName"

# This is used for Microsoft SQL Server 2005 Express on my machine
#database connect db "DRIVER=SQL Server;SERVER=[info hostname]\\SQLEXPRESS;DATABASE=myDatabaseName"

# This is used for Microsoft SQL Server 2005 on my machine
#database connect db "DRIVER=SQL Server;SERVER=[info hostname]\\SQL2005;DATABASE=myDatabaseName"

# Insert a row into MyTable
db "insert into MyTable (Name) values ('MyName')"

# Select all rows from MyTable
db "select * from MyTable"

# Update row in MyTable with DBID = 1
db "update MyTable set Name = 'MyNewName' where DBID = '1'"

# Delete rows in MyTable with Name = MyNewName
db "delete from MyTable where Name = 'MyNewName'"

JDM 2006-10-09: Thank you for the examples! One question: When I do a select of a column from an MSSQL database, the column comes back with curly brackets prepended and appended (2 each), i.e. the database value is "This is the varchar variable" and the value returned by tclodbc is "{{This is the varchar variable}}". Any ideas why this is happening?

JDM, it's a feature. Do not think that "there are extra curly brackets", but rather that the interface simply supplies you with lists of data. You'll presumably dererence with lindex or foreach or some other way to isolate individual data.

Example: Excel Files

David Bigelow contributes the following example of how to use tclodbc for talking directly with MS-Excel Files. The idea behind it was to examine the viability of allowing users/customers to continue to use MS-Excel (due to its inherent portability), and then use Tcl/Tk and TclODBC to basicly glue together a bigger picture of what may be coming from multiple sources.

CAUTION - this is a slow interface, you should consider using tcom first or BETTER YET - tap into a real database. However, this does allow you to execute a SQL Query directly into a MS-Excel Worksheet - which is more than cool given the difficulties of working with MS-Excel.

NOTE: Worksheet Naming Conventions: "[SheetName$]" = Table Name in Database World.

Below is an actual example I did as a test to compare the performance of using TclODBC verses some advanced formulas within the MS-Excel Sheet. I did not time the actual execution, but it feels to be about 10x slower than a direct database connection like MS-Access.

To keep things simple, each Worksheet was laid out just like a Database Table, Column Names at the Top and the Data underneath. To minimize debugging, I also made sure that the Columns were on "Row 1".

package require tclodbc
### LITTLE GUI - ONLY FOR DISPLAYING RESULTS

pack [text .tb] -expand y -fill both

### DATABASE CONNECTION DEFINITION

# Define ODBC Driver & File for MS-Excel
set fname "WBS_030616.xls"
set drvr "Microsoft Excel Driver (*.xls)"

# Connect to MS-Excel File (using ODBC Database Connection)
database db "DRIVER=$drvr;DBQ=$fname"

### QUERY DEFINITION & EXECUTION

# Setup Special Excel Vairables to Addres the Worksheet Names
set ENG_BOM             \[ENG_BOM\$\]
set Sherpa_BOM          \[Sherpa_BOM_DHB\$\]
set Order_Status        \[Order_Status\$\]
set Order_Status        \[Order_Status\$\]
# QUERY ALL "3" MS-Excel Worksheets at the SAME TIME!
set res [db "select $ENG_BOM.INDENT,
    $ENG_BOM.COMPONENT_NAME,
    $ENG_BOM.PART_NUMBER,
    $Sherpa_BOM.SIGNOFF_STATUS,
    $Order_Status.STATUS,
    $ENG_BOM.Description,
    $ENG_BOM.Detail,
    $ENG_BOM.Quantity
    from (($ENG_BOM
    LEFT OUTER JOIN $Sherpa_BOM
    ON $ENG_BOM.PART_NUMBER = $Sherpa_BOM.PART_NUMBER
    )
    LEFT OUTER JOIN $Order_Status
    ON $ENG_BOM.PART_NUMBER = $Order_Status.PART_NUMBER);"]

    # Write the Results to a Text Box.
    .tb insert end $res

### DATABASE DISCONNECT
db disconnect

If you pull the detailed code out of this (specifically the query), you will see that the only main difference is how you address the Worksheet Names in the Spreadsheet; which is akin to a Table in a database.

I found this to be a useful example, I hope others find benefit in this also.

Dave...

Issue: "memo" Field

If you are having issues with the MS Access "memo" field, see if the post from etdxc in the page: Microsoft Access is what you are facing.

Issues with the Windows Version *

MG 2006-01-19: I just downloaded TclODBC 2.3 for Windows from the above sf.net link, and found a problem: although the pkgIndex.tcl says package ifneeded tclodbc 2.3, all the other .tcl files there do package provide tclodb 2.2. Is it just that those other .tcl files weren't updated, or is the code not actually 2.3 at all?

Utility scripts not updated to the latest tclodbc version

Probably related to what is mentioned above...When you try to run the samples, located (after installation) at:
your_Tcl_path\lib\tclodbc2.3\samples, you will get the following error:
conflicting versions provided for package "tclodbc": 2.3, then 2.2 while executing... ... GENUTIL.TCL

fix: modify the first line of the following files:

  • DATAUTIL.TCL
  • GENUTIL.TCL
  • SQLUTIL.TCL
  • TKUTIL.TCL

from

package require tclodbc 2.2

to:

package require tclodbc 2.3

TKTABLE.TCL

When you run this sample, the following error shows up:

window name "t" already exists in parent
while executing
"table .t -variable t..."

fix:

change the name of this script to something that does not conflict with the package itself named also tktable !

for example, JM changed this sample script name to be "TABLETK.TCL"

Issue: File Name Case Sensitivity

CLN 2005-02-20: Has anyone figured out how to distribute TclODBC in a TclKit? I got [MySQLTcl to work by copying the DLLs out to $env(TEMP) at startup but I can't get the same thing to work for TclODBC. I keep getting an error that the package can't be found.

I fixed the problem with finding the package itself by renaming the files. They were distributed as uppercase filenames (PKGINDEX.TCL etc.), certainly in the version I downloaded. This doesn't matter to Windows, but it does matter in Tclkit's VFS. Make sure the filenames are in the right case and the package require line should work. I just need to figure out how to move the dlls out to a temporary folder now... -- AJS 2005-04-08

JM 2005-10-29: This a question that I think is still unanswered, in fact,I am facing the same problem...

MG: An answer was posted there regarding case-sensitivity of file names. But if that doesn't solve your problem...

Make sure you're loading it properly. For instance:

  • If you're only copying the DLL out, make sure you use [load [file join $env(TMP) $dll_name]]] to load it
  • If you want to use package require, make sure you're copying the pkgIndex.tcl file and the dll (and any other files needed - read the pkgIndex.tcl to see what they may be), then [lappend auto_path $env(TMP)] and [package require $packagename]. (Beware overwriting pkgIndex.tcl's from other things, though - you may want to copy it into a new directory inside TMP instead.)

Does that help with your problem at all?

JM: Thanks, I thought that posted solution in Tclodbc was not the complete solution, as I tried it and it did not work to me. Problem was that I was having a mistake renaming "PKGINDEX.TCL" to "pkgindex.tcl" when I should: "pkgIndex.tcl"

Thanks for being so kind to answer.