Perl on Linux and SQL Server 2000 on Windows

Colleagues,

There was a time when I did a lot of searching on ways to use Perl
sitting on Linux to connect SQL Server 2000 sitting on Windows. Here is
a piece of documentation that might help other users who do not want to
leave Linux environment just because the DB server is a Windows based
SQL Server 2000.

---starting point---

Fedora Core 4 Linux
Linux ae206-06.jacks.local 2.6.14-1.1656_FC4 #1 Thu Jan 5 22:13:22 EST
2006 i686 i686 i386 GNU/Linux
Fedora Core release 4 (Stentz)
LSB_VERSION="1.3"

gcc version 4.0.2 20051125 (Red Hat 4.0.2-8)
Using built-in specs.
Target: i386-redhat-linux
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man
--infodir=/usr/share/info --enable-shared --enable-threads=posix
--enable-checking=release --with-system-zlib --enable-__cxa_atexit
--disable-libunwind-exceptions --enable-libgcj-multifile
--enable-languages=c,c++,objc,java,f95,ada --enable-java-awt=gtk
--with-java-home=/usr/lib/jvm/java-1.4.2-gcj-1.4.2.0/jre
--host=i386-redhat-linux
Thread model: posix
gcc version 4.0.2 20051125 (Red Hat 4.0.2-8)

Perl v5.8.6 built for i386-linux-thread-multi



--- needed ---

DBI-1.48
http://www.cpan.org/authors/id/T/TI/TIMB/DBI-1.48.tar.gz

DBD-Sybase-1.07
http://www.cpan.org/authors/id/M/ME/MEWP/DBD-Sybase-1.07.tar.gz

FreeTDS - (freetds-0.63)
ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz




--- start compilation etc ---

Install DBI
Two methods:
1) First method
#> cpan
cpan> install DBI

2) Second method
$ cd /tmp
$ gunzip DBI-1.48.tar.gz
$ tar xvf DBI-1.48.tar
$ cd DBI-1.48
$ perl Makefile.PL
$ make
$ su
# make install
# exit
$

Install FreeTDS
$ cd /tmp
$ tar zxvf freetds-stable.tgz
$ cd freetds-0.63
$ ./configure --with-tdsver=7.0 --prefix=/usr/local/freetds
$ make
$ su
# make install
# exit
$

Install DBD-Sybase
Two methods but the first one might fail:
1) First method
#> cpan
cpan> install DBD::Sybase

2) Second method
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, add to your .bash_profile:
export SYBASE=/usr/local/freetds
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/usr/local/freetds/lib

Source the .bash_profile file by:
$> . .bash_profile

If your shell is CSH or TCSH, modify .cshrc file by adding:
setenv SYBASE /usr/local/freetds
setenv LD_LIBRARY_PATH /usr/local/freetds/lib

Source the .cshrc file by:
$> source .cshrc

$ cd /tmp
$ gunzip DBD-Sybase-1.07.tar.gz
$ tar xvf DBD-Sybase-1.07.tar
$ cd DBD-Sybase-1.07
$ perl Makefile.PL
Use 'CHAINED' mode by default (Y/N) [Y]: Y
Sybase server to use (default: SYBASE): GOBLIN
User ID to log in to Sybase (default: sa): <your username>
Password (default: undef): <leave empty>
Sybase database to use on GOBLIN (default: undef): climate
$ make
$ su
# make install
# exit
$

Configure FreeTDS
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.

Here is an example entry I made:
[goblin]
host = goblin.sdstate.edu
port = 1433
tds version = 8.0

A Simple Example

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)
Pea***, 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


Hope this helps. Information was collected from different websites for
this documentation. I have been using freetds + Perl (on Linux)
successfully for half a year now, and performance is just fine. One
thing to note is that if/when you dump a CSV or delimited file after
processing into SQL Server, USE TRANSACTIONS!! Without transactions,
insertion is extremely slow - depending on how much data one is
inserting.


Chirag Shukla

.

Loading