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

On Tue, 2004-07-06 at 20:57, Stephen.Sprague@MorganStanley.com wrote:
> 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?

You'd need something like

	$dbh = DBI->connect('dbi:Sybase:server=foo;bcpLogin=true', ...);

to enable the CS_BLK_LOGIN connection property that is required for
blk_xxx() calls to work.

I'd rather not open secondary connections for this feature to work.

Michael


> 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 :>>               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 :
-- 
Michael Peppler                              Data Migrations, Inc.
mpeppler@peppler.org                       http://www.peppler.org/
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short
or long term contract positions - http://www.peppler.org/resume.html