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: Stephen dot Sprague at MorganStanley dot com
Subject: Re: Bulk insert/load API access from DBI
Date: Jul 6 2004 6:57PM

so, if the 'bulk' attribute is on then:

   $dbh->prepare  does a blk_init
   $sth->execute  does a blk_rowxfer
   $sth->commit   does a blk_done

any additions/changes to the DBI->connect() call?  maybe the most
important one - if it's a 'bulk' connection or not.  or do you envision
bulk and non-bulk queries on the same handle? if so, would I see two
connections from the sybase server perspective?






On Tue, 6 Jul 2004 @ 2:06pm, an entity claiming to be Kiriakos Georgiou...:

kiriak :No question, Tim Bunce's solution is optimal.  Keep the existing DBI
kiriak :calls, and introduce flags that activate new (bulk) functionality.
kiriak :
kiriak :K
kiriak :
kiriak :Michael Peppler wrote:
kiriak :
kiriak :> On Tue, 2004-07-06 at 18:55, Matthew.Persico@Lazard.com wrote:
kiriak :>
kiriak :>>So far there is one vote for functions and one vote for SQL extentions. My guess
kiriak :>>is that it will be a lot easier to write/maintain as calls. SQL would have to be
kiriak :>>parsed into calls anyway. Save yourself the parsing step.
kiriak :>>
kiriak :>>By the way, I don't even want to write a loop. I want this:
kiriak :>>
kiriak :>>$dbh->bcp(table => 'foo',
kiriak :>>            [attrib => value, ...],  ## for some universe of attributes
kiriak :>>            source => [an open file handle or a string interpreted as a file
kiriak :>>name or an array ref of array refs]);
kiriak :>>
kiriak :>>$dbh->bcp(table => 'foo',
kiriak :>>             [attrib => value, ...],  ## for some universe of attributes
kiriak :>>            target => [an open file handle or a string interpreted as a file
kiriak :>>name or an array ref to hold array refs]);
kiriak :>>
kiriak :>>The direction (in or out) is inferred from whether 'source' or 'target' is
kiriak :>>supplied. If you implement this as
kiriak :>>      bcp_prepare
kiriak :>>      bcp_execute
kiriak :>>      bcp_commit
kiriak :>>
kiriak :>>and exposed those calls to us also, then if one needs to filter out rows, one
kiriak :>>can write their own loop and filter/massage as needed. Otherwise, let the bcp()
kiriak :>>call run the loop.
kiriak :>
kiriak :>
kiriak :> For now the only reason to implement the BLK api is to allow its use in
kiriak :> pipes - typically fetching data from one datasource and inserting it to
kiriak :> another with the BLK api instead of doing normal inserts (possibly doing
kiriak :> data munging/conversion on the way.)
kiriak :>
kiriak :> Using the single $dbh->bcp(...) call wouldn't really be useful in that
kiriak :> case - though something similar exists in the Sybase::BCP/BLK modules,
kiriak :> and could certainly be added on top of whatever calls I end up defining.
kiriak :>
kiriak :> One item to remember, though, is that as these calls aren't part of the
kiriak :> DBI they will be private DBD::Sybase extensions, and will therefore be
kiriak :> called syb_blk_something_or_other (or syb_bcp_...).
kiriak :>
kiriak :> As a C programmer I like the 'blk' name, as that's what is used in the
kiriak :> Sybase libs (CT-lib), whereas the 'bcp' name refers to the old DBlib
kiriak :> implementation. However, perl users aren't likely to know about this, so
kiriak :> maybe using 'syb_bcp_xxx' would be a better idea.
kiriak :>
kiriak :> Tim Bunce (over in dbi-dev) suggested doing something like this:
kiriak :>
kiriak :> $sth = $dbh->prepare("insert into sometable values(?,?,?...)", {
kiriak :> syb_bcp_attribs => { ... }});
kiriak :>
kiriak :> where DBD::Sybase would pick up the syb_bcp_attribs flag and flip on the
kiriak :> BLK functionality for that statement handle (the execute and commit
kiriak :> calls would stay the same as the normal behavior.)
kiriak :>
kiriak :> The alternative would be to create a special syb_bcp_prepare() call that
kiriak :> works like blk_init() in Sybase::CTlib but returns a statement handle
kiriak :> (albeit one with special functionality turned on so that the blk calls
kiriak :> are used to send the data to the server.)
kiriak :>
kiriak :> Comments?
kiriak :>
kiriak :> Michael
kiriak :>
kiriak :>
kiriak :>
kiriak :>
kiriak :>
kiriak :>>--
kiriak :>>Matthew Persico
kiriak :>>Vice President
kiriak :>>Lazard
kiriak :>>30 Rockefeller Plaza
kiriak :>>New York, NY 10020, USA
kiriak :>>Phone Number: 212 632 8255
kiriak :>>Fax Number: 212 332 5904
kiriak :>>Email: matthew.persico@lazard.com
kiriak :>>
kiriak :>>
kiriak :>>
kiriak :>>
kiriak :>>                                                    To:      "Michael Peppler" 
kiriak :>>              "Kiriakos Georgiou                    cc:      "Sybperl-L Mailing List" 
kiriak :>>              (Contractor)"                         Subject: Re: Bulk insert/load API access from DBI
kiriak :>>              >              il>
kiriak :>>              Sent by:
kiriak :>>              owner-sybperl-l@peppler.org
kiriak :>>
kiriak :>>              06 Jul 2004 12:25 PM
kiriak :>>
kiriak :>>
kiriak :>>
kiriak :>>
kiriak :>>Yes, it makes sense.  I find the BULK INSERT as you described it a
kiriak :>>little sexier than implementing new calls.
kiriak :>>
kiriak :>>K
kiriak :>>
kiriak :>>
kiriak :>>Michael Peppler wrote:
kiriak :>>
kiriak :>>
kiriak :>>>[ also posted to dbi-dev for comments there ]
kiriak :>>>
kiriak :>>>I've been asked to investigate the feasibility of adding access to
kiriak :>>>Sybase's bulk-load API in DBD::Sybase. This is an API that allows you to
kiriak :>>>load table rows in a minimally logged manner, and is of course much
kiriak :>>>faster than normal INSERT statements.
kiriak :>>>
kiriak :>>>I have this API available in Sybase::CTlib, and there are a number of users
kiriak :>>>who mix DBI and Sybase::CTlib to get the necessary functionality.
kiriak :>>>
kiriak :>>>I can implement this as a bunch of private DBD::Sybase calls,
kiriak :>>>but after thinking about this a little I thought that it might be
kiriak :>>>possible to integrate this to the prepare()/execute()/commit() sequence.
kiriak :>>>
kiriak :>>>Maybe something like:
kiriak :>>>
kiriak :>>>$sth = $dbh->prepare("BULK INSERT  -- attributes to be
kiriak :>>>defined");
kiriak :>>>while(<>) {
kiriak :>>>   @row = split('\|');   # or whatever...
kiriak :>>>   $sth->execute(@row);
kiriak :>>>}
kiriak :>>>$sth->commit;
kiriak :>>>
kiriak :>>>Obviously the driver (or DBI) would have to recognize the BULK INSERT
kiriak :>>>statement and switch to the bulk load API. That's similar to what I do
kiriak :>>>now with the "EXEC ..." statement that generates RPC calls instead of
kiriak :>>>SQL language commands to the server.
kiriak :>>>
kiriak :>>>Does this make sense?
kiriak :>>>
kiriak :>>>Comments?
kiriak :>>>
kiriak :>>>Michael
kiriak :>>
kiriak :>>
kiriak :>>
kiriak :>>
kiriak :>>
kiriak :>>
kiriak :>>
kiriak :>>
kiriak :>>
kiriak :
kiriak :
kiriak :
kiriak :