Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

Comment on

( #3333=superdoc: print w/replies, xml ) 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 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


In reply to Perl with Sybase: sybperl or DBI/DBD::Sybase? by mpeppler

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and all is quiet...

    How do I use this? | Other CB clients
    Other Users?
    Others studying the Monastery: (3)
    As of 2017-12-18 04:08 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?
      What programming language do you hate the most?




















      Results (466 votes). Check out past polls.

      Notices?