Purpose: To discuss [ODBC] bindings for Tcl. TclODBC (developed by Roy Nurmi) is currently at version 2.3.1 for Windows and 2.2.1 for Unix 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. ''Note: 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. ---- [LV] tclodbc is a binding of an ODBC library to Tcl. What I've not found is where to locate the original ODBC library. http://www.unixodbc.org/ appears to be at least one source for the library. ---- ''11 Sep 2006'' Spotted on Freshmeat [http://freshmeat.net/projects/tclodbc/]: TclODBC Release "0.0", author listed as Robert Heller. Quoth the release notes: "TclODBC is a Tcl interface to the ODBC API. This is a complete rewrite of the old tclodbc package." What: TclODBC Where: http://freshmeat.net/projects/tclodbc/ http://freshmeat.net/redir/tclodbc/66305/url_tgz/TclODBC-0.0.tar.gz ftp://ftp.deepsoft.com/pub/deepwoods/Other/TclODBC-0.0.tar.gz Description: A UNIX/Linux based re-write of the tclodbc package. It is in plain C (not C++) and uses SWIG to generate an OO interface layer for Tcl. Updated: 09/10/2006 Contact: mailto:heller@deepsoft.com (Robert Heller) http://www.deepsoft.com/ ---- 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. ---- '''Issues with the Windows Version:''' [MG] Jan 19th 2006 - 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? 1.-'''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 <
> <
> 2.-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" <
> <
> ---- '''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" ---- 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. -- [CLN] 2005-02-20 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] Oct-29-2005 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: "pkg'''I'''ndex.tcl" Thanks for being so kind to answer. ---- 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} ---- 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 ---- If you want to compile tclodbc for unix, you should get the CSV version from sourceforge: cvs -d:pserver:anonymous@tclodbc.cvs.sourceforge.net:/cvsroot/tclodbc login cvs -z3 -d:pserver:anonymous@tclodbc.cvs.sourceforge.net:/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. [[16Jun2006]] PS. ---- 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 [http://sourceforge.net/projects/unixodbc] On top of the tclodbc2.2tar.gz and the tclodbc2.2-config.tar.gz you need to add two files to the config subdirectory: * config.guess * config.sub Luckily these files are available 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) [http://sourceforge.net/projects/daft], which is written in tcl/tk and distributed as a starkit. Jelco. ---- 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... JL ---- '''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] 10-May-2007: 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-'''; * Rename the '''pkgIndex.tcl-hand''' rule to '''pkgIndex.tcl'''. Then proceed with '''make''' as usual. ---- 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 [http://www.sf.net/projects/tclodbc] * get the config subdir from [http://pascal.scheffers.net/software/tclodbc2.2-config.tar.gz] 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] ---- [Jussi Kuosa] is working to document ... Linux TclODBC ... ---- A correspondent writes, 'I see this sample usage example used a lot: % database connect db "DRIVER=SQL Server;SERVER=dbs1;DBQ=mydb" db % set ids [db "select id from employees where salary < 1000"] {222 333 444} If this could be expanded a little further, like with an example of an SQL table (or any table) with simple columns and rows then a sample of how to query the info in the rows and columns it would make it a little clearer. I have a book on PHP and it gives these types of examples of how to script the PHP and insert SQL queries, etc.' ---- Are the packages from [COMPANY: Belgian Graphic Interface] and http://www.solagem.fi/~rnurmi/tclodbc.html different, as appears to be the case? Yes, they are. I got the name first ;-) [Roy Nurmi] ---- [phk] Note that the statement db columns $tablename can give different results on different databases. (Due to different ODBC version or specific features). As [Roy Nurmi] showed me, there is a way to get a description: db statement s columns s $tablename s columns ---- [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... ---- [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. ---- see also: [a form for Access Database] ---- 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. ---- '''Alternative ODBC Tcl Extensions:''' [Tclsql], [snichols]: Here's another Windows C++ based Tcl extension to the ODBC API, the source code and nifty starkit on how to use tktable with SQL Server are included at the sourceforge project website. The starkit show's a working example of how to update a SQL table using the tktable widget: http://sourceforge.net/projects/tclsql ---- [SnODBC] is an ODBC binding, mostly compatible with [TclODBC], distributed as [starkit]. It supports unicode data types; it is able invoke windows ODBC GUI for selecting data source. Tested on Windows-x86 and Linux-x86. 2008-11-18: [davidw] has recently modified SnODBC to improve performance, with the addition of some C coded files. See http://github.com/davidw/snodbc/tree/master ---- [Odbcisql], [TP]: A nice companion to TclODBC is [http://www.nyx.net/~tpoindex/tcl.html#Odbcisql]. Odbcisql is a Tk-based SQL processor for ODBC databases. ---- [odbctcl] ---- What: TclDBI Where: From the contact Description: Based on the MODDBC project, contact has put the code into a namespace, updated the drivers, added a driver for ODBC. It also supports Postgres, Sybase, and msql. Updated: 09/2000 Contact: mailto:jscottb@gosiggy.com ---- ---- '''Related to TclODBC:''' [TimpleSQL] was written around this interface and is quite handy for interfacing applications to databases and also for cgi work. ---- See also: the page on [ODBC] and its connection to Tcl. ---- [Miscellaneous Tcl procs (Dillinger)] ---- What: tclodbc (nurmi) Where: http://www.solagem.fi/%7Ernurmi/tclodbc.html (???) http://www.solagem.fi/%7Ernurmi/tclodbc.zip http://www.ccp.uchicago.edu/%7Esteva/mactclodbc http://www.tcl.tk/software/tcltk/netcvs.html http://www.google.com/search?q=site%3Atcl.tk+tclodbc http://www.unixodbc.org/ Description: Object oriented ODBC database interface for Tcl. Supports multiple simultaneous connections, transaction handling, precompiled SQL statements and SQL argument handling. As of 2.1, supports Tcl 7.6, 8.0 and 8.1. When using Tcl 8.1, Unicode support is available. The version 8.1 DLL also supports Tcl 8.2. The uchicago site is a port of the extension to MacTcl. Currently at v2.1. Updated: 09/2001 Contact: mailto:Roy.Nurmi@iki.fi (Roy Nurmi) mailto:steva@dura.spc.uchicago.edu (Steve Aronson) ---- What: Windows NT Extensions Set Where: http://zazu.maxwell.syr.edu/nt-tcl/ ftp://ftp.maxwell.syr.edu/tcl/tcl82/nt-tcl82.zip ftp://ftp.maxwell.syr.edu/pub/tcl/stubs/nt-tcl82stubs.zip http://www-personal.umich.edu/%7Espencer/guraldi/gdtcl.html ftp://ftp.maxwell.syr.edu/pub/tcl/tcl76/ntext-prelim.zip Description: Extensions specifically for managing x86 Windows NT. The ntsys extension handles adding, enumerating and deleting users, local and global groups, retrieving user account info, retrieving, modifying and setting file and directory permissions. The ODBC extension provides basic support. The registry extension is a port of Gordon Chaffee's work in TkNT with some enhancments. The gdtcl is a port of gdtcl and gd for doing GIF creation and manipulation. The gdtcl extension includes a safe entry point and thus can be used with the Tcl plugin. tclping provides access to the icmp echo protocol. shortcut is an extension to create and maintain Windows 95/NT shortcuts. The entire package has been built with MSVC++ 5.0. The author mentions that none of these have been tested with Windows 95. Some may work - the NT specific ones obviously won't. Updated: 09/1999 Contact: mailto:cmsedore@maxwell.syr.edu (Christopher M. Sedore) ---- '''[Suri] - 2011-05-31 01:33:53''' When I'm trying to install tclodbc am getting this error on my windows xp machine, in which active tcl has been installed already... Please help me out. ---- http://wiki.tcl.tk/9557#pagetoc56444d7c%|%Using tclODBC and tclhttpd%|% ---- !!!!!! %| [Category Database] | [Category Example] | [Category Package] |% !!!!!!