MS SQL Server

Microsoft SQL Server is an RDBMS.

Tcl Bindings edit

tdbc: As of Tcl-8.6, is part of the core distribution, and includes an ODBC driver
SQL Relay
TclODBC: might be an option.
TclSQL: is an option. This C++-based extension currently only works with Windows-based platforms.

TP 2006-08-02: For MSSQL interface, Sybtcl should work, and I would recommend using the FreeTDS

interface library, as it can compile options to include various MSSQL protocol versions. FreeTDS can also build an ODBC driver, so Tclodbc works too. For most of my DB interface needs lately, I've been using Tclodbc on both Unix/Linux and Windows.

Using tdbc edit

superlinux 2012-03-31T09:26:

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.

Programming in Tcl/Tk Lesson 35:Connecting to MS SQL Server 2008 Using TDBC & ODBC.(English Version)

Programming in Tcl/Tk Lesson 35:Connecting to MS SQL Server 2008 Using TDBC & ODBC.(Arabic Version)

Using TclODBC edit

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/<USER>/.odbc.ini assuming /home/<USER> 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 <dbserver hostname/ip> -p <port usually 1433> -U <user usually schema as well> -P <password> -D <database name>

  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 <TableName>
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
[<one_word_description aka Servername>]
      host = <ip|hostname>
      port = <port>
      tds version = 8.0

Configuring odbc.ini

a. As root edit the /etc/odbc.ini and insert the following :

[<my_DSN_Name>]
Description        = FreeTDS
Servername      = <one_word_description a.k.a. Servername> from freetds.conf
Driver                = <shared library name for driver>
Database        = <db name>

Where <shared library name for driver> 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 <my_DSN_Name> <db_username> <db user password>
+---------------------------------------+
| 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

[<my_DSN_Name>64]
Description        = FreeTDS64
Servername      = <name>64
Driver                = /usr/lib64/libtdsodbc.so.0
Database        = <db name>

[<my_DSN_Name>]
Description        = FreeTDS
Servername      = <name>
Driver                = /usr/lib/libtdsodbc.so.0
Database        = <db name>

Trying it out : edit

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={<user>};PWD=<password>"
puts "[join [tdbc::odbc::datasources ] \n ]"

set stmt [db prepare "select * from <tablename>" ]
$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

Category Database

SQL