The DBI is a database access module for the Perl programming language. It defines a set of methods, variables, and conventions that provide a consistent database interface, independent of the actual database being used.

It is important to remember that the DBI is just an interface. The DBI is a layer of "glue" between an application (written in Perl) and one or more database driver modules (i.e. Oracle, MySQL, Sybase). It is the driver modules which do most of the real work. The DBI provides a standard interface and framework for the drivers to operate within.

Using the Perl DBI interface, you must first download and install the mandatory DBI Perl module. Downloading and installing all Perl modules mentioned in this article will be discussed in later sections.

At the time of this writing, the most current release of DBI available from CPAN.org is version 1.48. DBI-1.48.tar.gz

Note that a protocol is not an API. The two, however, are related. A protocol is when two computers have the ability to speak the same language. A server, for example, will be able to recognize and respond to a client request that has the right combination of bits in the correct order. This is all the responsibility of a software library. Today, many software libraries exist and they all have their own API. With regards to our current discussion, they are responsible for moving SQL data through a TDS pipe. Although ODBC, db-lib, ct-lib and JDBC have very dissimilar APIs, they are all considered the same to the server because on the wire, they speak TDS. For example, ODBC, db-lib, ct-lib and JDBC differ dramatically in their programming style and convention but they all use netlib to communicate to the server. The language they all use is TDS.

Netlib was responsible for the transport of data between two computers and can run on IPX/SPX, DECnet, NetBEUI and the ubiquitous TCP/IP. Db-lib, on the other hand, provided the API to the client program and did all of its communication with the server via netlib. Whatever db-lib sent to the server took the form of a stream of bytes (actually, a structured stream of bytes meant for tables) and was called a Tabular Data Stream.

Thanks to a technology sharing agreement with Sybase in 1990, Microsoft starting marketing its own SQL Server. All Microsoft SQL Server products then made use of the same network communications protocol used by Sybase - TDS.

Microsoft kept the db-lib API and also added ODBC plus several others. At around this same time, Sybase introduced a more robust successor to db-lib called ct-lib and renamed the pair "Open Client".

Within the technology sharing agreement with Sybase, Microsoft's SQL server products were being developed with the same network communications protocol that Sybase used, namely TDS. Through the release of SQL Server 7.0, Microsoft officially supported Sybase client software with a caveat that such support would come to an end. Until SQL Server 2000, is was then possible to use the Perl DBD::Sybase module (compiled with Sybase's freely downloadable client libraries) to access any Microsoft SQL server!

With the introduction of TDS 8.0 and now legacy support for TDS 7.0 in SQL Server 2000, compatibility with the Sybase client, using TDS 4.2, is broken. There is a work around however to accessing SQL Server 2000 and this article describes how!

Use the following commands to unpack, build and install the Perl DBI module. Please keep in mind that the all of the following commands can be performed as any UNIX user, however, the make install must be performed as root.

$ cd /tmp $ gunzip DBI-1.48.tar.gz $ tar xvf DBI-1.48.tar $ cd DBI-1.48 $ perl Makefile.PL *** You are using a perl configured with threading enabled. *** You should be aware that using multiple threads is *** not recommended for production environments. ... <snip>... Writing Makefile for DBI $ make cp Changes blib/lib/DBI/Changes.pm cp Roadmap.pod blib/lib/DBI/Roadmap.pm cp dbd_xsh.h blib/arch/auto/DBI/dbd_xsh.h cp dbivport.h blib/arch/auto/DBI/dbivport.h ... <snip>... Manifying blib/man3/Win32::DBIODBC.3pm Manifying blib/man3/DBI::PurePerl.3pm Manifying blib/man3/DBI::ProfileData.3pm $ su # make install Manifying blib/man1/dbiprof.1 Manifying blib/man1/dbiproxy.1 Installing /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/auto/DBI/dbipport.h ... <snip>... Installing /usr/bin/dbiproxy Writing /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/auto/DBI/.packlist Appending installation info to /usr/lib/perl5/5.8.5/i386-linux-thread-multi/perllocal.pod # exit $

Note that when running configure, we need to use the --with-tdsver=7.0 in order to create a Makefile suitable for compiling FreeTDS that specifies TDS 7.0 as the default protocol. Also note that I choose to install FreeTDS to /usr/local/freetds using the --prefix=(PATH) option.

$ cd /tmp $ tar zxvf freetds-stable.tgz $ cd freetds-0.63 $ ./configure --with-tdsver=7.0 --prefix=/usr/local/freetds checking for a BSD-compatible install... /usr/bin/install -c checking whether build environment is sane... yes checking for gawk... gawk checking whether make sets $(MAKE)... yes checking build system type... i686-pc-linux-gnu ... <snip>... config.status: creating win32/freetds.nsh config.status: creating include/config.h config.status: executing depfiles commands $ make Making all in include make[1]: Entering directory `/tmp/freetds-0.63/include' make all-am make[2]: Entering directory `/tmp/freetds-0.63/include' ... <snip>... make[1]: Entering directory `/tmp/freetds-0.63' make[1]: Nothing to be done for `all-am'. make[1]: Leaving directory `/tmp/freetds-0.63' if test ! -f PWD; then cp ./PWD.in PWD; fi $ su # make install Making install in include make[1]: Entering directory `/tmp/freetds-0.63/include' make[2]: Entering directory `/tmp/freetds-0.63/include' make[2]: Nothing to be done for `install-exec-am'. /bin/sh ../mkinstalldirs /usr/local/freetds/include ... <snip>... if test ! -f /usr/local/freetds/etc/locales.conf; then \ /usr/bin/install -c -m 644 ./locales.conf /usr/local/freetds/etc/locales.conf; \ fi make[2]: Leaving directory `/tmp/freetds-0.63' make[1]: Leaving directory `/tmp/freetds-0.63' # exit $

Before configuring and installing the DBD::Sybase driver module, ensure to set the environment variable SYBASE to the path of the FreeTDS installation. In this article, we used /usr/local/freetds. If your shell is BASH or KSH, use: $ export SYBASE=/usr/local/freetds Although, not important for the installation phase, you should also set your LD_LIBRARY_PATH environment variable to the /lib directory as in: $ export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/usr/local/freetds/lib Both of these environment variables should be set your login profile (i.e. .bash_profile) See http://www.freetds.org/userguide/envvar.htm for further information on setting environment variables for FreeTDS.

Use the following commands to unpack, build and install the Perl DBD::Sybase driver module. Please keep in mind that the all of the following commands can be performed as any UNIX user, however, the make install must be performed as root.

Also note that the Makefile.PL script asks you for information about your Sybase server. It uses this information to build and write a file called PWD which is used by the test utilities provided with the module. These tests were designed to run against a Sybase server - not Microsoft SQL Server 2000. Keep in mind that any error messages indicating No library found are due to compiling DBD::Sybase with the FreeTDS libraries instead of Sybase's and can be safely ignored.

$ cd /tmp $ gunzip DBD-Sybase-1.07.tar.gz $ tar xvf DBD-Sybase-1.07.tar $ cd DBD-Sybase-1.07 $ echo $SYBASE /usr/local/freetds $ echo $LD_LIBRARY_PATH /lib:/usr/lib:/usr/local/lib:/usr/local/freetds/lib $ perl Makefile.PL Unknown Client Library version - assuming FreeTDS. By default DBD::Sybase 1.05 and later use the 'CHAINED' mode (where available) when 'AutoCommit' is turned off. Versions 1.04 and older instead managed the transactions explicitly with a 'BEGIN TRAN' before the first DML statement. Using the 'CHAINED' mode is preferable as it is the way that Sybase implements AutoCommit handling for both its ODBC and JDBC drivers. Use 'CHAINED' mode by default (Y/N) [Y]: Y Running in threaded mode - looking for _r libraries... No thread-safe Sybase libraries found The DBD::Sybase module need access to a Sybase server to run the tests. To clear an entry please enter 'undef' Sybase server to use (default: SYBASE): MELODY User ID to log in to Sybase (default: sa): sa Password (default: undef): mypassword Sybase database to use on MELODY (default: undef): alexdb * Writing login information, including password, to file PWD. Checking if your kit is complete... Looks good 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 Note (probably harmless): No library found for -lblk ... <snip>... Writing Makefile for DBD::Sybase $ make cp dbd-sybase.pod blib/lib/DBD/dbd-sybase.pod cp Sybase.pm blib/lib/DBD/Sybase.pm ... <snip>... chmod 755 blib/arch/auto/DBD/Sybase/Sybase.so cp Sybase.bs blib/arch/auto/DBD/Sybase/Sybase.bs chmod 644 blib/arch/auto/DBD/Sybase/Sybase.bs Manifying blib/man3/DBD::Sybase.3 $ su # make install Installing /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/auto/DBD/Sybase/Sybase.bs Installing /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/auto/DBD/Sybase/Sybase.so Files found in blib/arch: installing files in blib/lib into architecture dependent library tree Installing /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBD/Sybase.pm Installing /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBD/dbd-sybase.pod Installing /usr/share/man/man3/DBD::Sybase.3 Writing /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/auto/DBD/Sybase/.packlist Appending installation info to /usr/lib/perl5/5.8.5/i386-linux-thread-multi/perllocal.pod # exit $

FreeTDS uses a configuration file called freetds.conf. This file can be found in the FreeDS installation directory under /etc. In this article, the file would be located at /usr/local/freetds/etc/freetds.conf. This file contains several configuration examples (some commented out) and it is very possible to modify one of them to reflect your server's information.

Here is an example entry I made:

[melody] host = melody.idevelopment.info port = 1433 tds version = 8.0

The name of my SQL Server in the above example is melody.idevelopment.info and is running its database service on the default port of 1433. Notice that I have also specified the tds version of 8.0.

Query the Server Name

#!/usr/local/bin/perl # use DBI; my $user = "sa"; my $passwd = "mypassword"; my $dbh = DBI->connect("DBI:Sybase:server=melody", $user, $passwd, {PrintError => 0}); unless ($dbh) { die "Unable for connect to server $DBI::errstr"; } my $sth; $sth = $dbh->prepare("select \@\@servername"); if ($sth->execute) { while(@dat = $sth->fetchrow) { print "SERVER IS: @dat\n"; } } exit(0); $ perl queryServer.pl SERVER IS: MELODY

Query the Northwind Database Employees

#!/usr/local/bin/perl # use DBI; my $user = "sa"; my $passwd = "mypassword"; my $dbh = DBI->connect("DBI:Sybase:server=melody", $user, $passwd, {PrintError => 0}); $dbh->do("use Northwind"); unless ($dbh) { die "Unable for connect to server $DBI::errstr"; } my $sqlStatement = " SELECT lastname , firstname , title FROM employees ORDER BY lastname"; $sth = $dbh->prepare($sqlStatement); $sth->execute; $rows = $sth->rows; print "Rows returned: $rows\n"; while ( ( $lastname , $firstname , $title) = $sth->fetchrow_array ) { printf ("%s, %s (%s)\n", $lastname, $firstname, $title); } exit(0); $ perl queryNorthwindEmployees.pl Rows returned: -1 Buchanan, Steven (Sales Manager) Callahan, Laura (Inside Sales Coordinator) Davolio, Nancy (Sales Representative) Dodsworth, Anne (Sales Representative) Fuller, Andrew (Vice President, Sales) King, Robert (Sales Representative) Leverling, Janet (Sales Representative) Peacock, Margaret (Sales Representative) Suyama, Michael (Sales Representative)

Query Northwind Database Objects

#!/usr/local/bin/perl # use DBI; my $user = "sa"; my $passwd = "mypassword"; my $dbh = DBI->connect("DBI:Sybase:server=melody", $user, $passwd, {PrintError => 0}); $dbh->do("use Northwind"); unless ($dbh) { die "Unable for connect to server $DBI::errstr"; } $sth = $dbh->prepare("sp_help"); $sth->execute ; $rows = $sth->rows ; print "Rows returned: $rows\n"; while ( @first = $sth->fetchrow_array ) { foreach $field ( @first ) { printf ("%-37s", $field); } print "\n"; } exit(0); $ perl queryNorthwind.pl Rows returned: -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 Order Subtotals dbo view Orders Qry dbo view Product Sales for 1997 dbo view Products Above Average Price dbo view Products by Category dbo view Quarterly Orders dbo view Sales by Category dbo view Sales Totals by Amount dbo view Summary of Sales by Quarter dbo view Summary of Sales by Year dbo view sysconstraints dbo view syssegments dbo view Categories dbo user table CustomerCustomerDemo dbo user table CustomerDemographics dbo user table Customers dbo user table ... <snip>... DF_Products_UnitsOnOrder dbo default (maybe cns) CK_Birthdate dbo check cns CK_Discount dbo check cns CK_Products_UnitPrice dbo check cns CK_Quantity dbo check cns CK_ReorderLevel dbo check cns CK_UnitPrice dbo check cns CK_UnitsInStock dbo check cns CK_UnitsOnOrder dbo check cns