Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling

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

by mpeppler (Vicar)
on Oct 27, 2001 at 01:39 UTC ( #121700=perlmeditation: 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, 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.


Replies are listed 'Best First'.
Re: Perl with Sybase: sybperl or DBI/DBD::Sybase?
by davorg (Chancellor) on Oct 29, 2001 at 14:42 UTC


    When I first started using Perl, the vast majority of the work I was doing was interacting with Sybase databases (I was using Sybase long before I was using Perl). Sybperl made my live much easier than it would have otherwise been.

    Since then, my emphasis has changed. I now use Perl with many different database systems. For that, DBI and the various DBD modules has been a lifesaver as it means that I can change database systems easily and still use very similar Perl code.

    I guess what I'm trying to say, is that without Sybperl I probably wouldn't have started using Perl - so my Perl career is largely your fault.

    So thank you :)


    "The first rule of Perl club is you don't talk about Perl club."

The Pros and Cons of Using Sybperl in 2001
by dave_aiello (Pilgrim) on Nov 07, 2001 at 23:43 UTC
    From a historical perspective, the Perl community owes a huge debt to Michael Peppler and his sybperl modules. I would argue that the original sybperl module almost single-handedly made Perl a viable development tool in the securities industry in the United States.

    On the other hand, I am currently working on a consulting project in the securities industry where sybperl was used by default. Many developers are using sybperl because they heard about it by word of mouth. This would be fine, were it not for the fact that there is a lot of poorly-written sybperl code out there being used as templates for new development.

    DBI may not support all of the DBlib and CTlib functionality in the most optimal way, but it is well documented. The fact that O'Reilly has produced an entire book on DBI is in itself a reason to choose DBI as a preferred interface.

    Having said all of this, I suggest that the community invest heavily in further documentation, sample code, and tips on how to use sybperl. This would increase the likelyhood of successful use of sybperl as a basis for projects within corporations. When sybperl-based projects fail, they reflect badly on Perl as a development platform. The result is often that code is rewritten using Java, C/C++, or VB. This is bad for all of us, in the long run, and a luxury we cannot afford in this economy.

    Dave Aiello
    Chatham Township Data Corporation

      *blush* ...Thanks...

      I agree that writing a good script that uses the lower level library isn't as easy as it could be, and that there are quite a few pitfalls.
      However, I have a feeling that most of these issues are related to the SQL side of things, and to an improper handling of result sets (which I think is the single most common problem) and those problems will be the same whether you use DBI or Sybase::CTlib.

      You know the old saw - you can write bad code in any language :-)

      I would be interested in hearing more (off-line) about any failed sybperl related projects that you know about (if you can share that information, of course). If I can improve on the documentation to avoid those sort of problems then I'd like to do that...


Re: Perl with Sybase: sybperl or DBI/DBD::Sybase?
by busunsl (Vicar) on Oct 29, 2001 at 12:51 UTC
    When I use Perl and Sybase products together, DBI/DBD::Sybase is always the choice.

    I don't need that finer level of control Sybperl is providing and it seems most need even less than DBD::Sybase is giving.
    Consider the DBIx modules or even EZDBI, they offer the simplest way to SQL and I think that's the way most people go.

    Perhaps it would be possible to incorporate some of the more sophisticated things into DBD::Sybase.
    The DBD::Pg module offers a method called func which executes PostgreSQL specific functions in a consistent way.
    That could be a way for DBD::Sybase to provide some specialties.

    Asynchronous requests, this is great! I'd love to see that in DBD::Sybase.

    You did a great job with Sybperl and DBD::Sybase, Michael!
    Thanks a lot!

      There are a few Sybase specific calls that you can execute via $dbh->func(...) or $sth->func(...). In particular you can do native read/write of IMAGE/TEXT data that way.


Re: Perl with Sybase: sybperl or DBI/DBD::Sybase?
by herveus (Parson) on Nov 02, 2001 at 02:12 UTC

    I've been using Sybase::CTlib for something like five or six years now in a Solaris/Sybase shop. I did a bit of C programming with Sybase. The difference was night and day. Sybperl took so much of the ooky-but-necessary fiddly bits and just hid them. A lot of my Sybase/Perl programs look like:

    $db->ct_sql(<<GO, sub { # process the rows }); SQL SQL GO
    repeated as needed...

    Right now, I don't have the luxury of what amounts to a pure Sybase shop to support. Thank you Michael for making this possible!

    Now, I am looking into DBI just because it seems to be a good idea to be familiar with it...


Re: Perl with Sybase: sybperl or DBI/DBD::Sybase?
by Gyro (Monk) on Nov 20, 2001 at 22:29 UTC
    Sybperl has been a lifesaver. My former employer would not buy the tools needed, so I made them using Sybperl. Now I have code snippits for all kinds of tasks.

    Would like to know if Sybase::DBlib will ever be removed from distribution. It would be a shame if Sybase killed DBlib completely. I find it much faster that CTlib and with only limited use of BCP in CTlib...what's the point. Much of my heavier hitting code deals with bulk-loads. Do you know if CTlib's role with BCP will expand?

    Will DBI/DBD::Sybase eventually handle the intricacies in Sybperl or is this something yet to be seen? I like the idea of one API handling connectivity to multiple platforms. My current project is to connect DB2 with Sybase and it looks like it will be a combination of DBI and Sybperl. Going with straight DBI won't give me the functionality needed. I look forward to DBI as it matures.

    We do appreciate what you are doing for the community.

      I very much doubt that Sybase will discontinue DBlibrary anytime soon, although you need to be aware that the DB-Library API does have some limitations and can't handle some of the newer capabilities (such as wider varchars, for example.)

      What additional BCP functionality do you need in Sybase::CTlib?

      What functionality in sybperl do you use that is not present in the DBI API?




        As I mentioned, Sybase::BCP is one I rely on, coupled with DBlib I use it to do all my data transfers. The two are well suited since BCP is based on DBlib. My concern lies with the changes in the Sybase client i.e., how does BCP match up with the changes in datatypes? This is something we havenít had to deal with yet. It would seem there would be some issues there. Would feel more comfortable to see CTlib have full access to the BCP API. But that seems more of a Sybase issue.
        As far as DBI is concerned, if in the future it also could have access to the BCP API. I move a lot of data. With the ability to pluggin to different DBMSí it would be an added bonus to move data in bulk from one platform to another, using the appropriate bulkload APIís.


Re: Perl with Sybase: sybperl or DBI/DBD::Sybase?
by metaperl (Curate) on Oct 20, 2009 at 15:28 UTC
    It seems you have to manually quote things with sybperl --- there appear to be no placeholders? Even in the newer Sybase::Simple.
      Sybase::Simple is just a fairly simple wrapper which is there to simplify the API, but doesn't really provide additional functionality.

      Sybase::CTlib does provide support for placeholders - see ct_dyn_prepare() and friends.

      That being said - I think today I would use DBD::Sybase for any new project, unless there really was a need to access the low-level CTlib API for some reason...


      PS - I just noticed that I wrote the original message in this thread almost 8 years ago... yikes!

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlmeditation [id://121700]
Approved by root
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (3)
As of 2021-10-18 18:15 GMT
Find Nodes?
    Voting Booth?
    My first memorable Perl project was:

    Results (74 votes). Check out past polls.