Re: SQL Server freeTDS walkthrough?

[joe++]

Hi,

I had a solution working two years ago, with Perl on Solaris 2.6 (Sun SPARC) and SQL-Server 7 on WinNT 4.

Back then I wrote the following documentation:

MS SQL-Server (NT) to Perl (Solaris)

Setup:

• Perl interface for MS-SQL-Server
• Perl is running on the local Sun (Solaris) machine
• SQL Server is hosted on a remote Windows NT box

Implemention:

The connection between the database and the Perl client is implemented over Sybase/MS native TDS format (Tabular Data Stream). This is different from a ODBC connection because no abstraction layer is put between the client and server; they talk in the native, proprietary TDS format. This approach is somewhat limited as the TDS specs are not published and the FreeTDS implementation is based on reverse engineering.

The advantages are big, however, as no separate driver modules are needed and no TCP ports other than the default SQL-Server port (1433) need to be opened up.

At the Perl side of the chain the DBI interface is used. This interface uses specific database abstraction layers for specific database engines. However, for MS-SQL-Server no DBD driver exists. The Sybase driver can be used instead, as the common TDS implementation appears to be broad enough. See the reference section for more details on DBI and DBD::Database interfaces.

Important: as Sybase is rather different from MySQL you might want to set some database dependent features when calling DBI's connect() method.

Notably, the syb_flush_finish flag is needed to flush any possibly remaining rows when calling finish() on the statement handle. See below for an example program.

Also, take care of the AutoCommit feature, which by default is OFF. All changes on the data must be committed by calling the commit() of the statement handle. MySQL users may want to set AutoCommit to a true value (1).

Text fields are truncated to 4096 bytes by default. As TEXT fields are not handled like BLOB data, setting LongReadLenght has no effect. You should execute the following SQL query instead:

$dbh->do('SET textsize 32000');

Installation instructions:

These instructions are based on

• FreeTDS (web) version 0.50
• DBD::Sybase (CPAN) version 0.21

First, make and install the FreeTDS libraries. For MS SQL-Server 7 the TDS protocol version needs to be either 4.2 or 7.0, with 4.2 being much more mature. The documentation and FAQ are quite helpful with this and other topics.

making the FreeTDS package

cd /usr/freetds

./configure --prefix=/usr/freetds --with-tdsver=4.2 \
            --enable-msdblib --enable-dbmfix 
  ...
make
  ...
make install

NOTE: you should use GNU make as the default Solaris make appears pretty brain dead.

NOTE 2: don't expect the regression tests with make check and/or make test to run; you'd better skip them. The build process should run without errors and a few warnings, though.

making and installing DBD::Sybase

From the FreeTSD FAQ:

" DBD::Sybase is the recommended option, and yes it does work with MSSQL.
You will need to install the perl DBI module and the FreeTDS package, particularly the CTLib portion.
Set your SYBASE environment variable to /usr/local/freetds and install DBD::Sybase.
Don't worry too much if some of the tests fail.
Do worry if the module doesn't compile.
Make sure you have the most recent version of FreeTDS installed.
You can check the TDS mailing list archives or ask the mailing list for help. "

Important: you should set the SYBASE environment variable before trying to build the interface, as shown on the first line below!

export SYBASE=/usr/freetds

cd /usr/src/DBD-Sybase-0.21

perl Makefile.PL
  ...
make
  ...
make install

NOTE: here again make test is likely to fail. Writing your own simple test program is highly recommended!

#!/usr/bin/perl -w
use DBI;

my $SERV = '123.123.123.123';
my $USER = 'me_myself';
my $PASS = 'mypassword';

my $dbh = DBI->connect("dbi:Sybase:server=$SERV", $USER, $PASS,
                           {PrintError => 0, syb_flush_finish => 1});
$dbh->do('SET textsize 32000');

my $sth = $dbh->prepare("SELECT TOP 5 id, testcol FROM test ORDER BY id");
$sth->execute();

while (my $hr = $sth->fetchrow_hashref) {
  print map("$_: $$hr{$_}, ", sort keys %$hr), "\n";
}

$sth->finish();

Reference

• Perl Module DBI (distribution on CPAN), the universal Database Interface.
• The accompanying DBI::FAQ (distribution on CPAN), Frequently Asked Questions, you better read it ;-).
• Perl Module DBD::Sybase (distribution on CPAN), the DBI driver for Sybase.
• homepage of the FreeTDS library implementation.

--
Cheers, Joe

[Anonymous Monk]

Thanks for the steps. I pretty much followed through with those. And then tried to use the test.pl (saved as CGI and run from my solaris website.

Here's the test.cgi code:


#!/usr/local/bin/perl
#
use DBI;

my $dbh = DBI->connect("dbi:Sybase:server=JDBC", 'guest', 'sybase', {P
+rintError => 0});

die "Unable for connect to server $DBI::errstr"
    unless $dbh;

my $rc;
my $sth;

$sth = $dbh->prepare("select \@\@servername");
if($sth->execute) {
    while(@dat = $sth->fetchrow) {
        print "@dat\n";
    }
}

And here's the error I got:


install_driver(Sybase) failed: Can't continue after import errors at /
+usr/local/lib/perl5/site_perl/5.6.1/sun4-solaris/DBD/Sybase.pm line 9
+2
BEGIN failed--compilation aborted at /usr/local/lib/perl5/site_perl/5.
+6.1/sun4-solaris/DBD/Sybase.pm line 92.
Compilation failed in require at (eval 626) line 3.