DBD::Sybase

iDog

Installation

It seems that it's still not a good idea to install DBD::Sybase using cpan as of now (April 2009). In fact, it can be installed easily in a Linux box in following way.

• download DBD::Sybase source code at http://search.cpan.org/~mewp/DBD-Sybase-1.09/Sybase.pm
• extract it to a dir
• export Sybase environment:

. /path/to/sybase/sybase.sh

• cd to the DBD::Sybase source code dir, do

perl Makefile.PL

• edit the generated Makefile to replace 'cc' to 'gcc' (two places)
• build and install it

make
make install

Usage

Typical usage of the DBD::Perl is as follows:

#!/usr/bin/per l -w

use strict;
use DBI;

# supposing following variables are defined here...
#   $server
#   $user
#   $pwd
#   $databaseName
#   $sql

my $dbh = DBI->connect("dbi:Sybase:server=$server", $user, $pwd)
          or die "Cannot connect to Sybase: $DBI::errstr";

$dbh->do("use $databaseName") or die "Cannot use database: $dbh->errstr";

my $sth = $dbh->prepare("$sql") or die "Cannot prepare SQL: $dbh->errstr";
$sth->execute or die "Cannot exec SQL: $sth->errstr";

while(my $row = $sth->fetchrow_arrayref()) {
    print ">>> $row->[0]\n";
}

$dbh->disconnect;

We can also use it in following way in order to save some effort:

my $dbh = DBI->connect("dbi:Sybase:server=$server", $user, $pwd,
                       { RaiseError   => 1,    # print err and die on error
                         PrintError   => 0,    # since already print error
                         AutoCommit   => 0,    # support transaction
                         syb_date_fmt => 'ISO'}); # print datetime in a better format

$dbh->do("use $databaseName");

my $sth = $dbh->prepare('update xxx set ...'); # transaction is started here automatically
$sth->execute;

$sth = $dbh->prepare('delete from xxx where ...');
$sth->execute;

$dbh->commit;

#...

$sth = $dbh->prepare('delete from yyy where ...');
$sth->execute;

$dbh->commit;

$dbh->disconnect;

Typical error:

If you connect to Sybase in following way:

my $dbh = new DBI->connect(...);

then you will get an error saying "can't locate auto/DBI/new.al". There is no 'new' in DBI.

DBD::Sybase vs Sybase::Sybperl

Both DBI + DBD::Sybase and Sybase::Sybperl can be used to access Sybase with Perl code, and both of them were wrote by Michael Peppler. So what's the differences between them?

Most of the points in following table are based on what Michael Peppler said.

Notes:

(*1) If a stored proc returns several result sets with different number of cols, then it should be processed in following way:

$sth = $dbh->prepare("exec my_proc $arg1, $arg2");
$sth->execute;
do {
    my $row;
    while($row = $sth->fetch) {
        # ...
    }
} while($sth->{syb_more_results});

(*2) when AutoCommit is off, if it's in chained transaction mode, then you cannot issue commands containing 'begin trans'; if it's not in chained transaction mode, then you cannot issue commands containing DDL statements (including 'select into').