Perl with Sybase: sybperl or DBI/DBD::Sybase?

[mpeppler]

OK - so most of you don't use Sybase... but for those who do this might be of interest:

This is a question I've been getting a lot lately, and I figure that it might be a good idea to try clarify the situation a little bit.

sybperl is the collective name for a collection of modules in the Sybase:: namespace, much like libwww or libnet. The name might be a little misleading, as other similar packages (oraperl, ingperl) were not ported to perl 5, or were only ported as emulation packages over the DBI api.

sybperl includes four modules: Sybase::DBlib, Sybase::CTlib, Sybase::BCP and Sybase::Sybperl. The first two implement a thin wrapper around the Sybase DB-Library and Client Library APIs, respectively. Sybase::BCP is a specialty module aimed at doing Bulk-Copy operations, and Sybase::Sybperl is a compatibility module with sybperl 1.xx (i.e. with the perl 4.x version).

The sybperl modules are actively maintained and expanded. In particular the Sybase::CTlib module has received considerable attention in recent months, with the addition of such features as asynchronous database requests, limited access to the bulk-copy API, etc.
(The reason Sybase::CTlib has received most attention is that Sybase has not upgraded the DB-Library API to handle recent additions to the TDS protocol, and so Sybase::DBlib should probably not be used for new projects.)

The sybperl modules are thin wrappers around the Sybase APIs. This is both good and bad. It's good because you have greater control, and because the API is (obviously) close to the way the server and the protocol work. It's bad in that it's a proprietary API, and that it is somewhat verbose.

The DBI API is great, and Tim's work on the DBI code is outstanding. I've tried to make DBD::Sybase conform to that API as well as possible, and in general I think it does a pretty good job. There are however certain areas where the DBI API and Sybase don't quite match. In particular Sybase (and MS-SQL) can return several result sets of varying width (number of columns) from a single stored procedure call. Getting this to work requires the perl code to include an additional loop to make sure that all the result sets have been processed:

    $sth = $dbh->prepare("exec some_proc $foo, $bar");
    $sth->execute;
    do {
        while($d = $sth->fetch) {
           ....
        }
    } while($sth->{syb_more_results});

Another slightly annoying issue is AutoCommit. DBD::Sybase handles AutoCommit off in two ways: it can use the "chained transaction" mode, or it can silently issue a "begin transaction" when the first request is issued, but both have down sides. In the "chained transaction" mode you will get an error if you happen to execute code that issues an explicit "begin tran", and in the other mode you get an error if you execute code that has any DDL statements (including a "select into") because of system table locking issues.

So what is my point?

DBI/DBD::Sybase offers perl-level portability, and an API that will be familiar to a lot of perl programmers, at the expense of some level of control, and with a slightly higher chance of bugs, as the code is both newer and somewhat more complex.
Sybase::CTlib offers better control, and a somewhat more mature codebase, at the expense of perl-level portability, and a somewhat more complex API (but you can easily get around that particular problem by using the Sybase::Simple module.)

Portability can also be achieved differently. For example I did a lot of the coding for eCircles.com, and while we had a rather large amount of perl code, we ended up with only about 400 lines of Sybase-related code. Porting the perl code to another database would not have been difficult (however, porting the SQL stored procedures are a different story altogether!)

I guess the bottom line is - both modules have their strengths and weaknesses, and the one you choose will depend on the expertise you already have, and whether your shop is purely Sybase or is multi-vendor.

Michael