Andrew Trice
Andrew shows how to build the DBD::Sybase module with the TDS libraries.
For many system administrators, myself
included, Perl is the scripting language of choice. Perl is nearly
ubiquitous, available for most operating systems in popular use
today. Thanks to the free availability of its source code, hundreds of
modules extending its capabilities have been developed. Quite noteworthy
among Perl modules is Tim Bunce's DBI (database independent). The
Perl DBI provides an API for interfacing with any database, at least
any database for which a corresponding DBD (yes, database dependent)
module is available.
A quick scan of Perl modules listed on cpan.org, Perl HQ,
shows DBD modules for Informix, Oracle, Sybase, IBM's DB2 and many
more. Glaringly absent from this list is a DBD module for connecting
to a Microsoft SQL server. A DBD::ODBC module has been developed, but
using this requires a separate driver manager and suitable drivers. To
the best of my knowledge, these are only available for Microsoft's
SQL 2000 from third-party vendors, a solution that of course brings
the benefits of professional development and support. As it turns out,
there is another way.
Thanks to an arrangement with Sybase, Microsoft's SQL server
products were developed with the same network communications protocol
that Sybase used, namely Tabular Data Stream, or TDS. Through the release
of SQL 7.0, Microsoft officially supported Sybase client software with a
caveat that such support was at its end. Thus the Perl DBD::Sybase module,
compiled with Sybase's freely downloadable client libraries, was
able to connect to any Microsoft SQL server until the release of SQL 2000.
With the introduction of TDS 8.0 and legacy support for TDS 7.0
in SQL 2000, compatibility with the Sybase client, using TDS 4.2,
is broken. However, the DBD::Sybase module can be built with the TDS
libraries from freetds.org, which does support TDS version 7.0,
and is used to connect to SQL 2000. Here's how.
I have tested this on Red Hat Linux 7.1 and 7.2. Because this project
requires working with source code, you'll need a compiler. GCC,
the GNU C compiler, is very conveniently supplied with Red Hat's
Linux distribution. Perl and the DBI module are likewise available in
RPM form on Red Hat CDs.
First, download DBD-Sybase-0.94.tar.gz to a convenient
location on your machine. This is available at
cpan.org and also from the author's download page at
www.mbay.net/~mpeppler. The FreeTDS source code is available
on www.freetds.org. Get freetds-0.53.tgz, the latest revision
as of this writing. I strongly recommend reading all the helpful
documentation available on these respective sites.
Next, gunzip and untar these downloaded files, then cd to the
newly created freetds-0.53 directory and run ./configure
--with-tdsver=7.0, which writes a Makefile suitable for compiling
FreeTDS on your machine and specifies tds 7.0 as the default protocol.
Now run make:
make
Making all in include
make[1]: Entering directory
`/home/atrice/freetds-0.53/include'
Making tds_configs.h
and so forth.
Then run make install to install your freshly compiled
FreeTDS. By default, it installs to /usr/local/freetds, though you can
change this when running configure with the -prefix=(PATH) switch. You
must be root to install:
make install
Making install in include
make[1]: Entering directory
`/home/atrice/freetds-0.53/include'
make[2]: Entering directory
`/home/atrice/freetds-0.53/include'
and so on. The final comments from make install will be
something like:
if [ -f /usr/local/freetds/etc/freetds.conf ];
then :;
else
/usr/bin/install -c -m 644 freetds.conf
/usr/local/freetds/etc/freetds.conf;
fi
make[2]: Leaving directory `/home/atrice/freetds-0.53'
make[1]: Leaving directory `/home/atrice/freetds-0.53'
You have now compiled and installed FreeTDS.
Next, we perform a very similar set of commands to configure and build
the DBD:Sybase module. First, however (very important), we must set up
an environment variable called SYBASE. This variable will be set to the
path of the FreeTDS installation. I am using the bash shell:
export SYBASE=/usr/local/freetds
Confirm the proper setting of the variable:
echo $SYBASE
/usr/local/freetds
Then cd into the DBD-Sybase directory and run Makefile.PL:
perl Makefile.PL
Sybase OpenClient found.
The DBD::Sybase module needs access to a Sybase server
to run the tests.
To clear an entry please enter 'undef'
Sybase server to use (default: undef):
User ID to log in to Sybase (default: sa):
Password (default: undef):
Note (probably harmless): No library found for -lcs
Note (probably harmless): No library found for -lsybtcl
Note (probably harmless): No library found for -lcomn
Note (probably harmless): No library found for -lintl
Using DBI 1.20 installed in
/usr/lib/perl5/site_perl/5.6.0/i386-linux/auto/DBI
Writing Makefile for DBD::Sybase
Notice that the Makefile.PL script asks you for information about your
Sybase server. It uses this information to write a file called PWD
that is used by the test utilities provided with the module. These were
designed to run against a Sybase server, not Microsoft, and I did not
have much success with the tests. The messages regarding the libraries
not found are due to compiling DBD::Sybase with the FreeTDS libraries
instead of Sybase's.
Run make, then make install. The last few messages
from make install should read as follows:
Installing /usr/share/man/man3/DBD::Sybase.3
Writing /usr/lib/perl5/site_perl/5.6.0/i386-linux/
auto/DBD/Sybase/.packlist
Appending installation info to /usr/lib/perl5/5.6.0/
i386-linux/perllocal.pod
You are done installing DBD::Sybase. Next we'll configure
FreeTDS to talk to your SQL 2000 database.
FreeTDS has a configuration file quite logically called
freetds.conf. It resides in the freetds installation directory under /etc,
so in my case the full path is /usr/local/freetds/etc/freetds.conf. There
is a sample Microsoft server configuration already present in this
file, and you only need modify it to reflect your server's
information. Mine looks like this:
# A typical Microsoft SQL Server 7.0 configuration
[file1]
host = file1
port = 1433
tds version = 7.0
My SQL server is called file1; it runs its database service on the
default port of 1433, and I have specified the tds version 7.0. There is
a global configuration section in the beginning of this file where you
also can set the tds version. Make sure your client machine can resolve
the hostname of your database server, or simply use its IP address.
Now we're ready to attempt a connection. Listing 1 is a Perl script
written by my colleague Trevor Price that queries the sample database
called Northwind. This database is included with a typical SQL 2000
installation. It makes use of a stored procedure called sp_help, which
returns information about all the tables in that database. Copy this
code into a file called something like testsql.pl, then edit $user and
$password to reflect a database account with access to your server.
Listing 1. Trevor Price's Perl Script that Queries the Sample Database Called Northwind
Run the script and you should get an output looking like this:
perl testsql.pl
rows is -1
Alphabetical list of products dbo view
Category Sales for 1997 dbo view
Current Product List dbo view
Customer and Suppliers by City dbo view
Invoices dbo view
Order Details Extended dbo view
If you've gotten this far, congratulations! I hope you find this
as helpful as we have.
Andrew Trice is a systems administrator with Vital Link
Business Systems. He holds a BA in English Literature from Cornell
University and is chief mastering engineer with Iron Robot Records,
an independent label based in San Francisco.
NOTE (Jim Turner): If you get "Undefined Symbol ct_ctx_global", you must
edit DBD:Sybase's "dbdimp.c" file and add a DEFINE line to define
it to "cs_ctx_alloc"!!!!! Also, placeholders are not currently supported!
Another useful link is: http://tlowery.hypermart.net/perl_dbi_dbd_faq.html#how!