'''[http://sourceforge.net/projects/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: http://sourceforge.net/projects/tclodbc/%|%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 [http://web.archive.org/web/20080405080800/http://www.ccp.uchicago.edu/~steva/mactclodbc/mactclodbc21.sit.hqx%|%Tmactclodbc21.sit.hqx]: Tclodbc2.1 for the Power Macintosh [http://web.archive.org/web/20080405080800/http://www.ccp.uchicago.edu/~steva/mactclodbc/mactclodbc21s.sit.hqx%|%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: ======none 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. ---- Jelco: Just an addition for those who want to compile the tclodbc library (2.2) for Suse Linux 9.1: You need to have [http://sourceforge.net/projects/unixodbc%|%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: * [http://git.savannah.gnu.org/gitweb/?p=config.git;a=blob_plain;f=config.guess;hb=HEAD%|%config.guess] * [http://git.savannah.gnu.org/gitweb/?p=config.git;a=blob_plain;f=config.sub;hb=HEAD%|%config.sub] 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: ======none SHLIB_LD = gcc -pipe -shared ====== by: ======none SHLIB_LD = g++ -pipe -shared ====== This Makefile created a valid tclodbc library for Suse Linux 9.1. I enclosed this library in the [http://sourceforge.net/projects/daft%|%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-'''; * 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: * get tclodbc2.2.tar.gz from [http://www.sf.net/projects/tclodbc%|%sf.net] * get the config subdir from * [http://pascal.scheffers.net/software/tclodbc2.2-config.tar.gz%|%tclodbc2.2-config.tar.gz] * or swipe it (like I did) from [oratcl] 3.3 ======none $ 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'' ======none 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 ** ======none % 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 ======none package require tclodbc 2.2 ====== to: ======none package require tclodbc 2.3 ====== *** TKTABLE.TCL *** When you run this sample, the following error shows up: ======none 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: "pkg'''I'''ndex.tcl" Thanks for being so kind to answer. <> Database | Example | Package