|
|
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
>>
>>
>>
>>
>>
>>
>>
>>
>>
|
