I think you are missing the point, or of course, I could be <G>.
I believed that array binding would be for a case to batch multiple updates,
so instead of:
@ary = (1,2,3)
$sql = qq{insert into foo (bar1) values (?)};
$sth = $dbh->prepare($sql);
foreach $el @ary {
$sth->bind_param(1, $el);
$sth->execute;
}
you would have:
@ary = (1,2,3)
$sql = qq{insert into foo (bar1) values (?)};
$sth = $dbh->prepare($sql);
$sth->bind_param(1, @ary); # insert 3 values...
$sth->execute;
>
> 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.
>
>
