What package can I use to connect to MS SQL Server through [Tcl]? * [TclODBC] might be an option. * [TclSQL] is an option. This [C++]-based [extension] currently only works with [Windows]-based platforms. * [tdbc] works and now comes with 8.6 [TP] TclODBC is not only an option, but a working solution. The other pieces that are required are: * unixODBC http://www.unixodbc.org * FreeTDS http://freetds.org Many main-stream [Linux] distributions already ship unixODBC (RedHat, for one). Compiling unixODBC is fairly easy, just ./configure ; make ; make install FreeTDS started as a project to provide open source drivers for [Sybase]. Since MS SQL Server is a re-write of Sybase (MS SQL 7.0 and later; 6.5 and early versions were a direct port of Sybase 4.2), the FreeTDS drivers include protocol specific variances to handle MS SQL Server. The trick is to configure FreeTDS as ./configure --with-tdsver=7.0 ; make ; make install Use --with-tdsver=8.0 for newest MS SQL Servers. Compile and install TclODBC. You'll need to have both a FreeTDS freetds.conf file entry, and an unixODBC odbc.ini file entry to define your MS SQL Server. See the docs for each product. These are Unix/Linux instructions; if you are already on a Windows box, it should be as simple as just compiling (or installing a pre built-) TclODBC. ---- The SQL Server Express edition is available at no charge [http://msdn.microsoft.com/vstudio/express/sql/). ---- [Bezoar] [tdbc] is now at a point where it can be used but I found it not ready out of box on Linux systems. So I did the hard work to figure out how to set up unixODBC and freeTDS to work on Linux. Connection to a SQL Server from Linux using tdbc ***Pre-requisites :*** Install tdbc and tdbc::odbc packages ( teapot or via download/compile) Install unixODBC odbc connection software and freeTDS driver package * Build as specified above or * Using your distros package manager install the packages, On Fedora ( as root or use sudo) : ====== yum -y install unixODBC freetds ====== ***Configuration*** If utilizing the package route the critical configuration files are If compiling the options given to the configure scripts will determine their locations. Latest version of freetds will by default use /usr/local/etc rather than /etc. The following files define the system available connections and should be defined by the root user. If you want your own private connections then create the odbc.ini and odbcinst.ini files in your home directory as hidden files ( e.g odbc.ini -> /home//.odbc.ini assuming /home/ is your home directory). /etc/odbc.ini /etc/freetds.conf /etc/odbcinst.ini ****Configuring FreeTDS**** Freetds comes with a utility , tsql that will help you diagnose your connection after you have edited you file. * Try to connect directly to your db server. I you get this to work then you can update the freetds.conf file. ====== tsql -H -p -U -P -D ====== if you succeed you will get a 1> prompt and you can execute some sql but you must use the MS convention of using "GO" to run the sql. Type quit, exit or ^D to exit ====== 1> select * from 2> GO .... ====== b. Open up the /etc/freetds.conf file insert the following; you will likely already have global options defined and some examples in the file already. if you have a recent version of SqlServer use tds version 8.0 otherwise use an earlier version ( look up on web what to use) : ====== # A typical Microsoft server [] host = port = tds version = 8.0 ====== Configuring odbc.ini a. As root edit the /etc/odbc.ini and insert the following : ====== [] Description = FreeTDS Servername = from freetds.conf Driver = Database = ====== Where for me was: /usr/lib/libtdsodbc.so.0 Configuring odbcinst.ini a. As root edit the /etc/odbcinst.ini ====== [FreeTDS] Description = FreeTDS driver for sql server Driver = /usr/lib/libtdsodbc.so.0 Setup = /usr/lib/libtdsS.so.2 FileUsage = 1 UsageCount = 2 ====== Not sure what FileUsage and UsageCount are for but it did not hurt. There are a number of other options such as Thread to enable threaded connections (not discussed here). Test the configuration with the isql utility that comes with unixODBC. ====== >isql +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> ====== Note if you are on a 64 bit OS and your tcl is compiled 32 bit you will need to make separate configurations for each in odbcinst.ini. Isql may be 64bit and so require the alternate configuration. odbcinst.ini ====== [FreeTDS64] Description = FreeTDS driver for sql server Driver = /usr/lib64/libtdsodbc.so.0 Setup = /usr/lib64/libtdsS.so.2 FileUsage = 1 UsageCount = 2 [FreeTDS] Description = FreeTDS driver for sql server Driver = /usr/lib/libtdsodbc.so.0 Setup = /usr/lib/libtdsS.so.2 FileUsage = 1 UsageCount = 2 ====== and odbc.ini ====== [64] Description = FreeTDS64 Servername = 64 Driver = /usr/lib64/libtdsodbc.so.0 Database = [] Description = FreeTDS Servername = Driver = /usr/lib/libtdsodbc.so.0 Database = ====== **Trying it out : ** Most of the problem as getting the connect string correct oddly with my version of tdbc::odbc v 1.0.16 I needed to unbrace the password in order for it to connect. ====== #!/bin/sh # the next line restarts using wish \ exec /opt/usr8.6b.2/bin/tclsh8.6 "$0" ${1+"$@"} if { [ catch {package require tdbc } err ] != 0 } { puts stderr "Unable to find package tdbc ... adjust your auto_path!"; } if { [ catch {package require tdbc::odbc } err ] != 0 } { puts stderr "Unable to find package tdbc::unixodbc ... adjust your auto_path!"; } tdbc::odbc::connection create db "DRIVER={FreeTDS};DSN={};UID={};PWD=" puts "[join [tdbc::odbc::datasources ] \n ]" set stmt [db prepare "select * from " ] $stmt foreach -as lists -columnvar x row { puts $x puts $row } $stmt close db close ====== ---- '''[schlenk] - 2011-11-23 19:05:32''' Would be interesting to see if it worked with the new official Microsoft ODBC driver for Linux too: http://www.microsoft.com/download/en/details.aspx?id=28160 ---- '''[superlinux] - Saturday, March 31 2012 09:26 AM Please watch this YouTube video tutorial about using TDBC with ODBC using activestate's TCL running on Windows Xp Sevice Pack 3. The Windows Xp has also MS SQL Database Server 2008 installed on it. This tutorial will teach you how to connect to the database using tdbc::odbc package. http://www.youtube.com/watch?v=b4J-QNIwX18%|%Programming in Tcl/Tk Lesson 35:Connecting to MS SQL Server 2008 Using TDBC & ODBC.(English Version)%|% http://www.youtube.com/watch?v=XjTrg2RtegY%|%Programming in Tcl/Tk Lesson 35:Connecting to MS SQL Server 2008 Using TDBC & ODBC.(Arabic Version)%|% ---- <> Database