|Pathologically Eclectic Rubbish Lister|
Perl with Sybase: sybperl or DBI/DBD::Sybase?by mpeppler (Vicar)
|on Oct 27, 2001 at 01:39 UTC||Need Help??|
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 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:
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.
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.