Microsoft SQL Server is an RDBMS.
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.
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)
GJW 2019-06-05: I was able to make a connection to an MS SQL database from Debian GNU/Linux 10 using the following steps:
package require tdbc::odbc set host ... set db ... set user ... set pass ... set drv /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so set conn "Driver=$drv;Server=$host;Port=1433;Database=$db;UID=$user;PWD=$pass" tdbc::odbc::connection create db $conn
TP TclODBC is not only an option, but a working solution. The other pieces that are required are:
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
Install tdbc and tdbc::odbc packages ( teapot or via download/compile) Install unixODBC odbc connection software and freeTDS driver package
yum -y install unixODBC freetds
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
Freetds comes with a utility , tsql that will help you diagnose your connection after you have edited you 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.0Configuring 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>
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 closeschlenk - 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
MattAdams - 2020-01-09 15:15:51
As per this whitepaper for TDBC , it looks like all statements must be prepared prior to being executed. I have a few database instructions for MS-SQL that I want to execute that can only be run in ad-hoc mode (MS-SQL will reject these EXEC statements if I try to prepare them before executing). In these cases is there any way for me to work around TDBC's requirement to prepare all statements prior to execution?