sybperl-l Archive

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
      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.
--
Matthew Persico
Vice President
Lazard
30 Rockefeller Plaza
New York, NY 10020, USA
Phone Number: 212 632 8255
Fax Number: 212 332 5904
Email: matthew.persico@lazard.com


                                                                                                                                                         
                                                                                                                                                         
                                                    To:      "Michael Peppler"                                                     
              "Kiriakos Georgiou                    cc:      "Sybperl-L Mailing List"                                             
              (Contractor)"                         Subject: Re: Bulk insert/load API access from DBI                                                    
                                                                                                                                                      
              Sent by:                                                                                                                                   
              owner-sybperl-l@peppler.org                                                                                                                
                                                                                                                                                         
              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