'''[http://www.microsoft.com/en-us/sqlserver/default.aspx%|%Microsoft SQL Server]''' is an [RDBMS].
** Tcl Bindings **
[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 [http://freetds.org%|%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 **
[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.
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)%|%
[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:
* Use a version of Tcl with the tdbc::odbc driver installed.
* Install the following Debian packages: freetds-bin, unixodbc-dev, tdsodbc.
* Test the connection using tsql, as described below, to ensure that the hostname, port, user name, and password are functioning, and to find a simple SQL query that will work for testing purposes.
* Write Tcl code of the following form:
======
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
======
** Using [TclODBC] **
[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
======none
./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) :
======none
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 : **
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
----
'''[MattAdams] - 2020-01-09 15:15:51'''
As per this https://www.tclcommunityassociation.org/wub/proceedings/Proceedings-2008/proceedings/tdbc/tcl2k8-kenny-withfonts.pdf%|%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?
<<categories>> Database | SQL