Connecting to SQL Server 2008 from Linux using Perl DBI

[oracle@racnode1 ~]$ cd /tmp
[oracle@racnode1 tmp]$ gunzip DBI-1.614.tar.gz
[oracle@racnode1 tmp]$ tar xvf DBI-1.614.tar
[oracle@racnode1 tmp]$ cd DBI-1.614


[oracle@racnode1 DBI-1.614]$ perl Makefile.PL

*** Your LANG environment variable is set to 'en_US.UTF-8'
*** This may cause problems for some perl installations.
*** If you get test failures, please try again with LANG unset.
*** If that then works, please email dbi-dev@perl.org with details
*** including the output of 'perl -V'
... <snip>...
Writing Makefile for DBI


[oracle@racnode1 DBI-1.614]$ make
/usr/bin/perl "-MExtUtils::Command" -e mkpath blib/lib/DBI
rm -f blib/lib/DBI/Changes.pm
cp Changes blib/lib/DBI/Changes.pm
cp Driver_xst.h blib/arch/auto/DBI/Driver_xst.h
cp lib/DBD/Proxy.pm blib/lib/DBD/Proxy.pm
cp lib/DBI/Gofer/Response.pm blib/lib/DBI/Gofer/Response.pm
cp lib/DBI/Gofer/Transport/Base.pm blib/lib/DBI/Gofer/Transport/Base.pm
cp lib/DBI/Util/_accessor.pm blib/lib/DBI/Util/_accessor.pm
cp lib/DBD/DBM.pm blib/lib/DBD/DBM.pm
... <snip>...


[oracle@racnode1 DBI-1.614]$ su
Password: xxxxxx


[root@racnode1 DBI-1.614]# make install
Manifying blib/man1/dbiprof.1
Manifying blib/man1/dbiproxy.1
Manifying blib/man1/dbilogstrip.1
Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBI/dbixs_rev.h
... <snip>...
Installing /usr/bin/dbilogstrip
Writing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBI/.packlist
Appending installation info to /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/perllocal.pod


[root@racnode1 DBI-1.614]# exit
exit

[oracle@racnode1 ~]$ cd /tmp
[oracle@racnode1 tmp]$ tar zxvf freetds-stable.tgz
[oracle@racnode1 tmp]$ cd freetds-0.82


[oracle@racnode1 freetds-0.82]$ ./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... x86_64-unknown-linux-gnu
checking host system type... x86_64-unknown-linux-gnu
checking for style of include used by make... GNU
... <snip>...
config.status: creating win32/version.rc
config.status: creating win32/freetds.nsh
config.status: creating include/config.h
config.status: executing depfiles commands


[oracle@racnode1 freetds-0.82]$ make
Making all in include
make[1]: Entering directory `/tmp/freetds-0.82/include'
make  all-am
make[2]: Entering directory `/tmp/freetds-0.82/include'
echo '#define FREETDS_SYSCONFDIR "/usr/local/freetds/etc"' >freetds_sysconfdir.h
... <snip>...
make[1]: Entering directory `/tmp/freetds-0.82'
make[1]: Nothing to be done for `all-am'.
make[1]: Leaving directory `/tmp/freetds-0.82'
if test ! -f PWD; then cp ./PWD.in PWD; fi


[oracle@racnode1 freetds-0.82]$ su
Password: xxxxxx


[root@racnode1 freetds-0.82]# make install
Making install in include
make[1]: Entering directory `/tmp/freetds-0.82/include'
make[2]: Entering directory `/tmp/freetds-0.82/include'
make[2]: Nothing to be done for `install-exec-am'.
... <snip>...
make[2]: Leaving directory `/tmp/freetds-0.82'
make[1]: Leaving directory `/tmp/freetds-0.82'


[root@racnode1 freetds-0.82]# exit
exit
 

Install DBD-Sybase

The final component to install is the Perl DBD::Sybase driver module. The commands used to install the DBD::Sybase driver module are exactly the same as building the Perl DBI module. There is on extremely important exception and that is that the environment variable SYBASE MUST be set to the path of the FreeTDS installation prior to installing DBD::Sybase.

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:

[oracle@racnode1 ~]$ 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:

[oracle@racnode1 ~]$ 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 the FreeTDS online user guide 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 2008. 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.

[oracle@racnode1 ~]$ cd /tmp
[oracle@racnode1 tmp]$ gunzip DBD-Sybase-1.10.tar.gz
[oracle@racnode1 tmp]$ tar xvf DBD-Sybase-1.10.tar
[oracle@racnode1 tmp]$ cd DBD-Sybase-1.10


[oracle@racnode1 DBD-Sybase-1.10]$ echo $SYBASE
/usr/local/freetds

[oracle@racnode1 DBD-Sybase-1.10]$ echo $LD_LIBRARY_PATH
/u01/app/oracle/product/10.2.0/db_1/lib:/lib:/usr/lib:/usr/local/lib:/usr/local/freetds/lib


[oracle@racnode1 DBD-Sybase-1.10]$ 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...

***NOTE***
There is an incompatibility between perl (5.8.x) built in threaded mode and
Sybase's threaded libraries, which means that signals delivered to the perl
process result in a segment violation.

I suggest building DBD::Sybase with the normal libraries in this case to get
reasonable behavior for signal handling.

Use the threaded (lib..._r) libraries [N]: N

OK - I'll use the normal libs

Running in 64bit mode - looking for '64' libraries...
BLK api NOT available.
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): VMWINDOWS1
User ID to log in to Sybase (default: sa): sa
Password (default: undef): myPassword

Sybase database to use on VMWINDOWS1 (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


[oracle@racnode1 DBD-Sybase-1.10]$ 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


[oracle@racnode1 DBD-Sybase-1.10]$ su
Password: xxxxxx


[root@racnode1 DBD-Sybase-1.10]# make install
Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/Sybase/Sybase.bs
Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-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/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD/dbd-sybase.pod
Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD/Sybase.pm
Installing /usr/share/man/man3/DBD::Sybase.3
Writing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/Sybase/.packlist
Appending installation info to /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/perllocal.pod


[root@racnode1 DBD-Sybase-1.10]# exit
exit

Configure FreeTDS

At this point, all software components and modules are installed. The final step is to configure FreeTDS to talk to your SQL Server 2008 database!

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). It is possible to modify one of them to reflect your server's information.

Here are several example entries I made:

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

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

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

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

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

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

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


When configuring the entry, note that the dataserver name ([AlexDB] and the [AdventureWorks] sample databases in this example) must be a valid database name! Otherwise, the server will refuse your connection.

The name of my SQL Server in the above example is vmwindows1.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.

Example Perl Scripts

Now let's take a look at our new configuration in action by running a couple of example Perl scripts.

Query the Server, Connection Properties and Available Databases

#!/usr/bin/perl

use DBI;

my $user   = "ahunter";
my $passwd = "myPassword";
my $server = "AlexDB";

my $dbh = DBI->connect("DBI:Sybase:server=$server", $user, $passwd, {PrintError => 0});
my $sth;

unless ($dbh) {
    die "ERROR: Failed to connect to server ($server).\nERROR MESSAGE: $DBI::errstr";
} else {
    print "\n";
    print "Successful Connection.";
    print "\n\n";
}


print "Current Connection Properties\n";
print "---------------------------------------------------------------------------------------\n";
my $sqlStatement = "select \@\@servername, \@\@version, db_name(), system_user, host_name()";
unless ($sth = $dbh->prepare($sqlStatement)) {
    $dbh->disconnect;
    die "ERROR: Failed to prepare SQL statement.\nSQL: $sqlStatement\nERROR MESSAGE: $DBI::errstr";
}
unless ($sth->execute) {
    $dbh->disconnect;
    die "ERROR: Failed to execute query.\nSQL: $sqlStatement\nERROR MESSAGE: $DBI::errstr";
}
while ((    $serverName
          , $sqlServerVersion
          , $currentDatabase
          , $currentUser
          , $clientMachine) = $sth->fetchrow) {
    ($sqlServerVersion, @dummy) = split(/\n/, $sqlServerVersion);

    print "    SQL Server Instance : $serverName\n";
    print "    SQL Server Version  : $sqlServerVersion\n";
    print "    Current Database    : $currentDatabase\n";
    print "    Current User        : $currentUser\n";
    print "    Client Machine      : $clientMachine\n";
}
print "\n";
$sth->finish;


print "Available Databases\n";
print "-----------------------------------------------------------------\n";
my $sqlStatement = "select
                        isnull(db_name(dbid), 'MSSQL SYSTEM RESOURCE') as Name
                      , str(convert(dec(15),sum(size))* 8192/ 1048576,10,2)+ N' MB' as Size
                    from sys.sysaltfiles
                    group by dbid
                    order by 2 desc";
unless ($sth = $dbh->prepare($sqlStatement)) {
    $dbh->disconnect;
    die "ERROR: Failed to prepare SQL statement.\nSQL: $sqlStatement\nERROR MESSAGE: $DBI::errstr";
}
unless ($sth->execute) {
    $dbh->disconnect;
    die "ERROR: Failed to execute query.\nSQL: $sqlStatement\nERROR MESSAGE: $DBI::errstr";
}
while (($databaseName, $databaseSize) = $sth->fetchrow) {
    printf("    %-40s %20s\n", $databaseName, $databaseSize);  
}
print "\n";
$sth->finish;

print "Disconnecting from SQL Server.\n\n";
$dbh->disconnect;
exit(0);

[oracle@racnode1 bin]$ perl queryServer.pl

Successful Connection.

Current Connection Properties
---------------------------------------------------------------------------------------
    SQL Server Instance : VMWINDOWS1
    SQL Server Version  : Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)
    Current Database    : AlexDB
    Current User        : ahunter
    Client Machine      : racnode1

Available Databases
-----------------------------------------------------------------
    AdventureWorks2008R2                                180.69 MB
    AdventureWorks                                      172.00 MB
    AdventureWorksDW2008R2                               75.50 MB
    AdventureWorksDW                                     68.81 MB
    MSSQL SYSTEM RESOURCE                                61.06 MB
    msdb                                                 12.63 MB
    ReportServer                                          9.38 MB
    tempdb                                                8.50 MB
    AdventureWorksLT2008R2                                7.19 MB
    AdventureWorksLT                                      7.19 MB
    master                                                5.00 MB
    ReportServerTempDB                                    3.00 MB
    AlexDB                                                3.00 MB
    model                                                 1.75 MB

Disconnecting from SQL Server.

Query AdventureWorks Database Customer Names

#!/usr/bin/perl

use DBI;

my $user   = "ahunter";
my $passwd = "myPassword";
my $server = "AdventureWorks";

my $dbh = DBI->connect("DBI:Sybase:server=$server", $user, $passwd, {PrintError => 0});
my $sth;

unless ($dbh) {
    die "ERROR: Failed to connect to server ($server).\nERROR MESSAGE: $DBI::errstr";
} else {
    print "\n";
    print "Successful Connection.";
    print "\n\n";
}


print "Individual Customers (Last Name = 'Beck')\n";
print "--------------------------------------------\n";
my $sqlStatement = "SELECT FirstName, LastName
                    FROM [AdventureWorks].Person.Contact AS C
                    JOIN [AdventureWorks].Sales.Individual AS I
                        ON C.ContactID = I.ContactID
                    JOIN [AdventureWorks].Sales.Customer AS Cu
                        ON I.CustomerID = Cu.CustomerID
                    WHERE Cu.CustomerType = 'I'
                        AND LastName = 'Beck'
                    ORDER BY LastName, FirstName";
unless ($sth = $dbh->prepare($sqlStatement)) {
    $dbh->disconnect;
    die "ERROR: Failed to prepare SQL statement.\nSQL: $sqlStatement\nERROR MESSAGE: $DBI::errstr";
}
unless ($sth->execute) {
    $dbh->disconnect;
    die "ERROR: Failed to execute query.\nSQL: $sqlStatement\nERROR MESSAGE: $DBI::errstr";
}
while (($firstName, $lastName) = $sth->fetchrow) {
    printf("    %s, %s\n", $lastName, $firstName);  
}

my $rows = $sth->rows;
print "\n";
print "Rows returned: $rows\n";
print "\n";
$sth->finish;

print "Disconnecting from SQL Server.\n\n";
$dbh->disconnect;
exit(0);