One of my clients called up asking to have an old data loader script (written in TCL) updated to post to MS SQL Server as well as to the legacy Postgres server. I was kind of surprised how easy it was.

I started off with information from the MS SQL Server page on the TCL/Tk Wiki.

Originally I was planning on using the TDBC package for connecting (which is the current standard), but the target server only has TCL8.4 available, so instead we had to go with the TCLODBC package.

Also, I was hoping to use the free binary ODBC drivers from Microsoft, however they required a 64bit OS, and that target server is still running a 32bit distribution. Luckily there was a pre-packaged version of the FreeTDS library available.

The installation process was pretty simple, we just had to install the following packages:

• unixodbc
• freetds
• tdsodbc
• tclodbc

After the install we just needed to tweak /etc/freetds/freetds.conf and /etc/odbc.ini with the connection information.

The unixodbc package includes the isql command which allows you to execute sql queries from the command line, which is helpful for testing the connection outside of your application.

One quirk I found was that the tclodbc driver wouldn’t connect using the connection information in the ini files along with a username and password. It turns out you just need to use the second form of the connection command which uses a connection string, so instead of:

database db ConnectionListedInIni UserName Password

we used:

database db "DSN=ConnectionListedInIni;UID=UserName;PWD=Password"

which did the trick.

One last thing - be sure to set the ’encryption = required’ option in the /etc/freetds/freetds.conf file for your connection if you want to encrypt the connection between your app and the server. The default is ‘off’.

Now, we get to move forward with the coding.