PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: "Kiriakos Georgiou (Contractor)" <kiriakos dot georgiou at nrl dot navy dot mil>
Subject: Re: Bulk insert/load API access from DBI
Date: Jul 6 2004 6:06PM

No question, Tim Bunce's solution is optimal.  Keep the existing DBI 
calls, and introduce flags that activate new (bulk) functionality.

K

Michael Peppler wrote:

> On Tue, 2004-07-06 at 18:55, Matthew.Persico@Lazard.com 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.)
> 
> Comments?
> 
> Michael
> 
> 
> 
> 
> 
>>--
>>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                                                    
>>              >              il>                                                                                                                                        
>>              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
>>
>>
>>
>>
>>
>>
>>
>>
>>