Connect to Microsoft SQL 2000 with the Perl Sybase Module

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.

email: atrice@vitallink.com

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.

______________________

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Freetds latest version + dbd::sybase + aix 5.3

Dinesh's picture

Hi,

I am not able to install freetds latest statble version 0.82 on my aix 5.3 box. I want to use the dbd::sybase perl module but freetds is not able to install after I did the following:

./configure --with-tdsver=7.0 --disable-libiconv --disable-odbc --prefix=/usr/local/freetds/

make

It throws lots of error

fisql.c:31:31: error: readline/readline.h: No such file or directory
fisql.c:32:30: error: readline/history.h: No such file or directory
fisql.c: In function 'main':
fisql.c:400: error: 'rl_outstream' undeclared (first use in this function)
fisql.c:400: error: (Each undeclared identifier is reported only once
fisql.c:400: error: for each function it appears in.)
fisql.c:402: error: 'rl_readline_name' undeclared (first use in this function)
fisql.c:403: warning: implicit declaration of function 'rl_bind_key'
fisql.c:403: error: 'rl_insert' undeclared (first use in this function)
fisql.c:504: warning: implicit declaration of function 'rl_on_new_line'
fisql.c:505: warning: implicit declaration of function 'rl_reset_line_state'
fisql.c:517: warning: implicit declaration of function 'readline'
fisql.c:517: warning: assignment makes pointer from integer without a cast
fisql.c:523: warning: implicit declaration of function 'add_history'
fisql.c:540: error: 'rl_instream' undeclared (first use in this function)
fisql.c:544: warning: assignment makes pointer from integer without a cast
fisql.c:618: warning: assignment makes pointer from integer without a cast

Any help is appreciable

Regards,
Dinesh

Great job!

Larry E. Ives's picture

I came in today with the task in mind to begin to write some scripts to query some data in our MSSQL database which would then create html based reporting on our intranet. I planned on taking at least a day to make the initial connection and query but did it in 30 minutes. Thank you very much for your concise and straight forward howto article.

Incredibly helpful!

Anonymous's picture

Thank you SO much for your guidance on this! 1 hour of research to find the artical and I'm up and running!

Works but throws an ERROR

ThomasFiema's picture

any one get this at the start of the script runing:

cs_config(CS_LOC_PROP) failed at /usr/lib/perl5/5.8.0/i386-linux-thread-multi/DynaLoader.pm line 249.
DBD::Sybase::db STORE failed: Server message number=3902 severity=16 state=1 line=2 server=THOC105A text=The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. at /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi/DBI.pm line 555.

This is due to AutoCommit

Anonymous's picture

This is due to AutoCommit being on and no Begin transaction having occurred.

Add the AutoCommit => 0 to the connect statement as shown below.

my $dbh = DBI->connect($dsn, "username", 'password', {AutoCommit => 0});

BTW...

Anonymous's picture

BTW, this gets the data no problem, but throws this Error. Any input would be appreciated.

Thanks

TF

Great tip, but might be worth

Anonymous's picture

Great tip, but might be worth pointing out that this only applies to *nix. If your Perl script will be running on a Windows box you can just use the DBD::ADO driver instead.

Re: Connect to Microsoft SQL 2000 with the Perl Sybase Module

Anonymous's picture

Cool workaround!
However, can this client solution interoperate with SQL server
password encryption funtions? I do not like sending
passwords over the wire in clear text.

Re: Connect to Microsoft SQL 2000 with the Perl Sybase Module

Anonymous's picture

Hey, Baby. I think you're cute. Are you straight and single?

Re: Connect to Microsoft SQL 2000 with the Perl Sybase Module

Anonymous's picture

Cool workaround!
However, can this client solution interoperate with SQL server
password encryption funtions? I do not like sending
passwords over the wire in clear text.

Re: Connect to Microsoft SQL 2000 with the Perl Sybase Module

Anonymous's picture

I'm running Red Hat Linux 8.0 with perl, v5.8.0 built for i386-linux-thread-multi.
I installed FreeTDS v 0.60, DBI-1.32 and DBD:Sybase-0.95.
When I try to run the sample script, I get

perl: relocation error: /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/auto/DBD/Sybase/Sybase.so: undefined symbol: cs_ctx_global

Has anyone gotten this to work with the latest version of the software?
Thanks in advance for the replies.

Re: Connect to Microsoft SQL 2000 with the Perl Sybase Module

Anonymous's picture

I'm running on RH 8.0, but I always install my own perl and such (I don't use the RH crap - too old) DBD-Sybase-1.02, freetds-0.62.1. Everything works fine...

Maybe you need a newer perl, freetds, or dbd-sybase - you are not at the current versions.

Re: Connect to Microsoft SQL 2000 with the Perl Sybase Module

Anonymous's picture

Hi,

I got the same error and found an explanation here :

http://www.rosat.mpe-garching.mpg.de/mailing-lists/dbi/2002-11/msg00065....

I'll try the nightly snapshot approach. Although It'd be great to get a working non-ALPHA version.

Pieter..

Re: Connect to Microsoft SQL 2000 with the Perl Sybase Module

rawcane's picture

This is brilliant. I wish I'd found this page before I tried to install everything...

One question: My set up is exactly as above but I get the message...

Failed to prepare SQL - Server message number=10000 severity=7 state=0 line=1 server=OpenClient text=Dynamic placeholders only supported under TDS 5.0 at CSV.pl line 49.

...when I try to prepare an insert statement with ?-style placeholders. I could put the variables into the sql string directly but that goes against the DBI guide instructions.

Is there a way of getting this to work without breaking the rules?

Re: Connect to Microsoft SQL 2000 with the Perl Sybase Module

Anonymous's picture

We spent $500 with a third party solution using ODBC and iodbc. What a pain in the arse too! This solution installed and returned a successful query in 10 minutes. Thank you for a most excellent article!

Re: Connect to Microsoft SQL 2000 with the Perl Sybase Module

Anonymous's picture

Worked for me too, yipeee. I think I had 8 hours into trying to get this to work before this article. Actually, I spent most of my time trying to debug the make test of DBD::Sybase. It does return a lot of errors so don't even bother running it.

Re: Connect to Microsoft SQL 2000 with the Perl Sybase Module

Anonymous's picture

Tried it today on a RH7.2 system, Perl 5.6.1, to a SQL Server 2000 system on

the same LAN - it worked as described. Thanks to all the writers of freetds for

that very fine effort!

Re: Connect to Microsoft SQL 2000 with the Perl Sybase Module

Anonymous's picture

If you'r on FreeBSD, it's even easier..

cd /usr/ports/databases/freetds && make install clean

cd ../p5-DBD-Sybase

vi /usr/local/etc/freetds.conf

..and your ready to go.

Re: Connect to Microsoft SQL 2000 with the Perl Sybase Module

Anonymous's picture

oopsie..

cd /usr/ports/databases/freetds && make install clean

cd ../p5-DBD-Sybase && make install clean

vi /usr/local/etc/freetds.conf

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState