Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: Matthew dot Persico at Lazard dot com
Subject: Re: Bulk insert/load API access from DBI
Date: Jul 6 2004 4:55PM

So far there is one vote for functions and one vote for SQL extentions. My guess
is that it will be a lot easier to write/maintain as calls. SQL would have to be
parsed into calls anyway. Save yourself the parsing step.

By the way, I don't even want to write a loop. I want this:

$dbh->bcp(table => 'foo',
            [attrib => value, ...],  ## for some universe of attributes
            source => [an open file handle or a string interpreted as a file
name or an array ref of array refs]);

$dbh->bcp(table => 'foo',
             [attrib => value, ...],  ## for some universe of attributes
            target => [an open file handle or a string interpreted as a file
name or an array ref to hold array refs]);

The direction (in or out) is inferred from whether 'source' or 'target' is
supplied. If you implement this as

and exposed those calls to us also, then if one needs to filter out rows, one
can write their own loop and filter/massage as needed. Otherwise, let the bcp()
call run the loop.
Matthew Persico
Vice President
30 Rockefeller Plaza
New York, NY 10020, USA
Phone Number: 212 632 8255
Fax Number: 212 332 5904

                                                    To:      "Michael Peppler"                                                     
              "Kiriakos Georgiou                    cc:      "Sybperl-L Mailing List"                                             
              (Contractor)"                         Subject: Re: Bulk insert/load API access from DBI                                                    
              Sent by:                                                                                                                                   
              06 Jul 2004 12:25 PM                                                                                                                       

Yes, it makes sense.  I find the BULK INSERT as you described it a
little sexier than implementing new calls.


Michael Peppler wrote:

> [ also posted to dbi-dev for comments there ]
> I've been asked to investigate the feasibility of adding access to
> Sybase's bulk-load API in DBD::Sybase. This is an API that allows you to
> load table rows in a minimally logged manner, and is of course much
> faster than normal INSERT statements.
> I have this API available in Sybase::CTlib, and there are a number of users
> who mix DBI and Sybase::CTlib to get the necessary functionality.
> I can implement this as a bunch of private DBD::Sybase calls,
> but after thinking about this a little I thought that it might be
> possible to integrate this to the prepare()/execute()/commit() sequence.
> Maybe something like:
> $sth = $dbh->prepare("BULK INSERT  -- attributes to be
> defined");
> while(<>) {
>    @row = split('\|');   # or whatever...
>    $sth->execute(@row);
> }
> $sth->commit;
> Obviously the driver (or DBI) would have to recognize the BULK INSERT
> statement and switch to the bulk load API. That's similar to what I do
> now with the "EXEC ..." statement that generates RPC calls instead of
> SQL language commands to the server.
> Does this make sense?
> Comments?
> Michael