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
Jussi Kuosa is working to document ... Linux TclODBC ...
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
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:
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.
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
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"
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
% 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 disconnectJDM 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.
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 disconnectIf 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...
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.
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?
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:
from
package require tclodbc 2.2
to:
package require tclodbc 2.3
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"
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:
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.