|
Talking to Microsoft SQL Server from Unix |
|
 CSE Home |
About Us |
Search |
Contact Info |
|
|
Background
We wish to be able to access data hosted on any platform from any other platform. Perhaps he most stressful example of data that we can't easily share are MS SQL data, which are hosted exclusively on Windows NT and share easily only to Windows clients. (We also wring our hands over Access data, but somewhat less energetically.) We have some of the software discussed in this (aging) document installed on www.cs.washington.edu. For details, see below. Sybase ASEKey to many of the strategies that we explore in this document is this simple historical fact: MS SQL is a fork of a database from Sybase. It turns out that the protocol that's used to talk to the two of them is known as "TDS" - tabular data streams. Sybase has a product called Adaptive Server Enterprise (ASE) that includes, besides an entire database server, libraries that speak TDS. They have made various versions available for free for various uses for Linux - an early version is free for development and deployment, and a later version is free for development only. See http://www.sybase.com/adaptiveserver/. Part of that package is a pair of libraries - dblib and ctlib - the "OpenClient Client Library." For reasons I don't know, ctlib is preferred over dblib. dblib and ctlib are distributed as binary static libraries - and not at all in source form. ASE is notable for us mostly because both FreeTDS (see below) and the Sybase DBD (low-level Perl driver, see below) are based upon these APIs - notably upon the ctlib API. FreeTDS
There is an open source project - FreeTDS, http://www.freetds.org/ - to create an interface to TDS-compliant database servers such as Sybase and, still, MS SQL. MS SQL 7.0 speaks TDS version 7.0, but will also speak TDS 4.2 (SQL Server 2000 speaks TDS version 8.0, but also falls back on demand). The key distinction twixt 4.2 and 7.0 seems to be support for unicode and "long varchars." The TDS 8.0 support in FreeTDS is described as incomplete. Thus, by building the OpenTDS code for the 4.2 protocol level, you get libraries that can talk to MS SQL of any version - presumably, so long as the databases aren't unicode and don't use long varchars. (I build for 7.0.) FreeTDS includes a simple command line client called tsql, useful for testing if not much else. Documentation for FreeTDS has improved. There is an archive of the mailing list at http://franklin.oit.unc.edu/cgi-bin/lyris.pl?visit=freetds. SQSH
Also notable: a handy open source tool called sqsh, now in version 2.1. See http://www.sqsh.org/ . It's just a command line tool that, like isql, allows you to formulate queries. It builds on both Unix and, with Cygwin, NT, links against either ASE or FreeTDS ctlib, and is more featureful than isql. Its key utility, though, is to provide an isql-like functionality on Unix hosts. I've built that and tested it (lightly) on Linux 2.2.13/glibc 2.1. It's an exceptionally well-crafted piece of code. PerlFreeTDS provides C-language APIs, but virtually all of our CGIs are implemented in Perl. So, we want a way to talk TDS from Perl. There are at least two currently-available ways to get from access to MS SQL data from perl: sybperl and DBD::Sybase. sybperl is a version of perl that is staticly linked to ctlib so as to provide access to TDS-compliant database servers. We don't favor sybperl because it requires a special, hefty version of perl. On the upside, it's been around for many years and is actively maintained. DBD::Sybase is a driver for TDS-compliant databases that works, like all DBDs, with DBI. It's designed for use with Sybase ctlib but it works, according to the fine folks at FreeTDS, with FreeTDS ctlib. Here is an example script that works with the pubs example database that is installed with MS SQL. This script was tested with Linux 2.0.13 (Redhat 5.2) running a vanilla perl 5.005_03, data served from MS SQL 7.0 on Windows NTS 4.0SP6, but it should work with any perl 5.004 or better and any version of MS SQL.
#!/usr/bin/perl
use DBI;
$ENV{'SYBASE'} = '/usr/local/freetds';
$dbh = DBI->connect('dbi:Sybase:server=cobalt', 'emil', 'mrevil')
or die 'connect';
$dbh->do("use pubs");
$sth = $dbh->prepare('select * from authors') or die 'prepare';
$sth->execute or die 'execute';
while (@data = $sth->fetchrow_array) {
print "$data[0]\n"; # the authors' SSNs
}
$sth->finish;
$dbh->disconnect;
ODBCThere is an active open source project to build ODBC for Unix at http://genix.net/unixODBC/, and there is already DBD::ODBC to privide an interface for perl, be it running on Unix or Windows. We are aware of four current ODBC solutionss. Three solutions involve some type of proxying, and two are commercial.
Installed SoftwareWe've installed and tested some of this stuff on www.cs.washington.edu, and it's relatively easy to make it available elsewhere.
Last modified 19 July 2000. |
||||||||||||||||||||
 |
Computer Science & Engineering University of Washington, Box 352350 Seattle, WA 98195-2350 (206) 543-1695 [comments to rose@cs.washington.edu] Privacy policy and terms of use |
