>-----Original Message-----
>From: Tim Bunce [mailto:[EMAIL PROTECTED]]
>Sent: Thursday, August 09, 2001 8:07 AM
>To: Jeff Urlwin
>Cc: Dean Arnold; Tim Bunce; [EMAIL PROTECTED]
>Subject: Re: array binding (was: RE: Call for testers: Array
>binding via
>DBD::ODBC)
I agree with Tim that if it's going to be in it should be a driver specific
function. While I know that there have been a number of people who want to
bind a "list" to a single paramter I'm not convinced it's the right thing to
do.
Consider the following sql:
insert into foo (bar1, bar2, bar3) values (1,2,3)
while it may be considered conveinient to write:
@ary = (1,2,3);
$sql = qq{insert into foo (bar1, bar2, bar3) values (?)};
$sth = $dbh->prepare($sql);
$sth->bind_param_array(1, \@ary)
$sth->execute;
Surely this reads better:
@ary = (1,2,3);
$sql = qq{insert into foo (bar1, bar2,bar3) values (?,?,?)};
$sth = $dbh->prepare($sql);
$x = 1;
foreach $el (@ary) {
$sth->bind_param($x,$el);
$x++;
}
$sth->execute;
The number of placeholder paramters match the number of fields named for the
insert and the sql fragment in the code looks pretty logical. Changing this
to a dynamic generation independant of the number of elements makes sense
too:
@fields = qw( bar1 bar2 bar3);
@values = qw(1 2 3);
$sql = qq{insert into foo (} . join(@fields,",") . qq{) values (} . join(
map { "?" } @fields, ",") . qq{)};
$sth = $dbh->prepare($sql);
$x = 1;
foreach $el (@values) {
$sth->bind_param($x,$el);
$x++;
}
$sth->execute;
The main point is that the number of placeholders should always match the
values that are substituted for each execution.
$sth->execute($scalar,@array,$scalar) really doesn't look right to me.
--Neil
>On Wed, Aug 08, 2001 at 03:43:05PM -0400, Jeff Urlwin wrote:
>> Now I remember why I shelved it for a bit.
>>
>> As I recall, it required changes to DBI. Those need to be
>coordinated with
>> Tim, as I can't really update DBD::ODBC without the
>corresponding changes to
>> the DBI.
>
>Actually you don't - just add the functions and call them via
>$h->func(...,'funcname').
>
>I'd rather not change the official spec till we've got a couple of
>working implementations we can play with. I'd then also want
>to try implementing
>fallback behaviour in the DBI for driver that don't support
>the new methods.
>
>Tim.
>
>> Can we discuss the changes to the DBI spec and how the array
>binding is
>> implemented? I believe Dean is proposing three new functions:
>>
>> $rv = $sth->bind_param_array($p_num, \@bind_value_ary);
>> $rv = $sth->bind_param_array($p_num, \@bind_value_ary, $bind_type);
>> $rv = $sth->bind_param_array($p_num, \@bind_value_ary, \%attr);
>>
>> As I, honestly, haven't been keeping up with the DBI spec, I
>can't tell
>> where DBI was going with this.
>>
>> Jeff
>>
>>
>> > -----Original Message-----
>> > From: Dean Arnold [mailto:[EMAIL PROTECTED]]
>> > Sent: Wednesday, August 08, 2001 11:00 AM
>> > To: Jeff Urlwin
>> > Subject: Fw: Call for testers: Array binding via DBD::ODBC
>> >
>> >
>> > Here's the original msg w/ attachment...
>> >
>> > Glad to see you're still "on the bus" with this...
>> >
>> >
>> > Regards,
>> > Dean Arnold
>> >
>> > ----- Original Message -----
>> > From: "Dean Arnold" <[EMAIL PROTECTED]>
>> > To: "Jeff Urlwin" <[EMAIL PROTECTED]>
>> > Sent: Monday, January 29, 2001 4:54 PM
>> > Subject: Re: Call for testers: Array binding via DBD::ODBC
>> >
>> >
>> > > I've attached a ZIP file with several source files:
>> > >
>> > > The DBI.pm includes the changes for the DBI layer, and
>has some POD
>> > > updates describing the interfaces.
>> > >
>> > > The ODBC.xsi file has the DBI-to-DBD::ODBC translation layer
>> > > stuff in it.
>> > >
>> > > The dbdimp.c file has the actual implementation code in it, and
>> > > dbdimp.h has some updates to the common data structures.
>> > >
>> > > bulkapitst.pl is a little test script to compare
>row-at-a-time vs.
>> > > array binding.
>> > >
>> > > BTW I've had a chance to test this with Oracle's ODBC
>> > > driver, it seems to get about a 10x thruput improvement
>> > > for INSERTs, and about 2x for SELECT.
>> > >
>> > > Also be advised that I'm not an XSUB guru, so some of
>> > > my implementation may make you cringe; any comments
>> > > or questions are welcome.
>> > >
>> > > Regards,
>> > > Dean Arnold
>> > >
>> > >
>> > > ----- Original Message -----
>> > > From: "Jeff Urlwin" <[EMAIL PROTECTED]>
>> > > To: "Dean Arnold" <[EMAIL PROTECTED]>; "DBI-users"
>> > <[EMAIL PROTECTED]>
>> > > Sent: Monday, January 29, 2001 3:21 PM
>> > > Subject: RE: Call for testers: Array binding via DBD::ODBC
>> > >
>> > >
>> > > > Dean,
>> > > >
>> > > > Sorry, I've been out of the loop and on vacation the last week.
>> > > >
>> > > > Please send proposed patches to me. I anticipate having more
>> > time to work
>> > > > on DBD::ODBC shortly as I am leaving my current job to "go
>> > out on my own".
>> > > > My current job has been a timesink, so this should be
>> > interesting. I have
>> > > > an upcoming release for DBD::ODBC WRT bind_param_inout, but I
>> > haven't been
>> > > > able to get it to work on Oracle yet, so I haven't released it.
>> > > >
>> > > > Jeff
>> >
>> >
>
>
__________________________________________________________________________
Please Note :
Only the intended recipient is authorised to access or use this e-mail.
If you are not the intended recipient, please delete this e-mail and notify
the sender immediately. The contents of this e-mail are the writer's
opinion and are not necessarily endorsed by the Gunz Companies
unless expressly stated.
We use virus scanning software but exclude all liability for viruses or
similar in any attachment.
