Connect to Microsoft SQL 2000 with the Perl Sybase Module
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: 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: Entering directory `/home/atrice/freetds-0.53/include' make: 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: Leaving directory `/home/atrice/freetds-0.53' make: 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:
Confirm the proper setting of the variable:
echo $SYBASE /usr/local/freetdsThen 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::SybaseNotice 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.
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.
|Dynamic DNS—an Object Lesson in Problem Solving||May 21, 2013|
|Using Salt Stack and Vagrant for Drupal Development||May 20, 2013|
|Making Linux and Android Get Along (It's Not as Hard as It Sounds)||May 16, 2013|
|Drupal Is a Framework: Why Everyone Needs to Understand This||May 15, 2013|
|Home, My Backup Data Center||May 13, 2013|
|Non-Linux FOSS: Seashore||May 10, 2013|
- Dynamic DNS—an Object Lesson in Problem Solving
- Making Linux and Android Get Along (It's Not as Hard as It Sounds)
- Using Salt Stack and Vagrant for Drupal Development
- New Products
- A Topic for Discussion - Open Source Feature-Richness?
- Drupal Is a Framework: Why Everyone Needs to Understand This
- Validate an E-Mail Address with PHP, the Right Way
- RSS Feeds
- Readers' Choice Awards
- Tech Tip: Really Simple HTTP Server with Python
2 hours 25 min ago
- Reply to comment | Linux Journal
2 hours 57 min ago
- All the articles you talked
5 hours 21 min ago
- All the articles you talked
5 hours 24 min ago
- All the articles you talked
5 hours 25 min ago
9 hours 50 min ago
- Keeping track of IP address
11 hours 41 min ago
- Roll your own dynamic dns
16 hours 54 min ago
- Please correct the URL for Salt Stack's web site
20 hours 6 min ago
- Android is Linux -- why no better inter-operation
22 hours 21 min ago
Enter to Win an Adafruit Pi Cobbler Breakout Kit for Raspberry Pi
It's Raspberry Pi month at Linux Journal. Each week in May, Adafruit will be giving away a Pi-related prize to a lucky, randomly drawn LJ reader. Winners will be announced weekly.
Fill out the fields below to enter to win this week's prize-- a Pi Cobbler Breakout Kit for Raspberry Pi.
Congratulations to our winners so far:
- 5-8-13, Pi Starter Pack: Jack Davis
- 5-15-13, Pi Model B 512MB RAM: Patrick Dunn
- 5-21-13, Prototyping Pi Plate Kit: Philip Kirby
- Next winner announced on 5-27-13!
Free Webinar: Hadoop
How to Build an Optimal Hadoop Cluster to Store and Maintain Unlimited Amounts of Data Using Microservers
Realizing the promise of Apache® Hadoop® requires the effective deployment of compute, memory, storage and networking to achieve optimal results. With its flexibility and multitude of options, it is easy to over or under provision the server infrastructure, resulting in poor performance and high TCO. Join us for an in depth, technical discussion with industry experts from leading Hadoop and server companies who will provide insights into the key considerations for designing and deploying an optimal Hadoop cluster.
Some of key questions to be discussed are:
- What is the “typical” Hadoop cluster and what should be installed on the different machine types?
- Why should you consider the typical workload patterns when making your hardware decisions?
- Are all microservers created equal for Hadoop deployments?
- How do I plan for expansion if I require more compute, memory, storage or networking?