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: Michael Peppler <mpeppler at peppler dot org>
Subject: Re: Bulk insert/load API access from DBI
Date: Jul 6 2004 5:29PM

On Tue, 2004-07-06 at 18:55, wrote:
> 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
>       bcp_prepare
>       bcp_execute
>       bcp_commit
> 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.

For now the only reason to implement the BLK api is to allow its use in
pipes - typically fetching data from one datasource and inserting it to
another with the BLK api instead of doing normal inserts (possibly doing
data munging/conversion on the way.)

Using the single $dbh->bcp(...) call wouldn't really be useful in that
case - though something similar exists in the Sybase::BCP/BLK modules,
and could certainly be added on top of whatever calls I end up defining.

One item to remember, though, is that as these calls aren't part of the
DBI they will be private DBD::Sybase extensions, and will therefore be
called syb_blk_something_or_other (or syb_bcp_...).

As a C programmer I like the 'blk' name, as that's what is used in the
Sybase libs (CT-lib), whereas the 'bcp' name refers to the old DBlib
implementation. However, perl users aren't likely to know about this, so
maybe using 'syb_bcp_xxx' would be a better idea.

Tim Bunce (over in dbi-dev) suggested doing something like this:

$sth = $dbh->prepare("insert into sometable values(?,?,?...)", {
syb_bcp_attribs => { ... }});

where DBD::Sybase would pick up the syb_bcp_attribs flag and flip on the
BLK functionality for that statement handle (the execute and commit
calls would stay the same as the normal behavior.)

The alternative would be to create a special syb_bcp_prepare() call that
works like blk_init() in Sybase::CTlib but returns a statement handle
(albeit one with special functionality turned on so that the blk calls
are used to send the data to the server.)



> --
> Matthew Persico
> Vice President
> Lazard
> 30 Rockefeller Plaza
> New York, NY 10020, USA
> Phone Number: 212 632 8255
> Fax Number: 212 332 5904
> Email:
>                                                     To:      "Michael Peppler"                                                     
>               "Kiriakos Georgiou                    cc:      "Sybperl-L Mailing List"                                             
>               (Contractor)"                         Subject: Re: Bulk insert/load API access from DBI                                                    
>                              il>                                                                                                                                        
>               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.
> K
> 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
Michael Peppler                              Data Migrations, Inc.             
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short
or long term contract positions -