>From the pod I added to DBI.pm in the ZIP file (just as a starting point
for discussion):
<snip>
$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);
$rv = $sth->bind_param_status(\@status_ary);
$rv = $sth->bind_param_status(\%status_hash);
$rc = $sth->bind_col_array($col_num, \@col_array, $max_rows);
$rv = $sth->bind_row_status(\@status_ary);
$rv = $sth->bind_row_status(\%status_hash);
<snip>
=item C<bind_param_array>
$rc = $sth->bind_param_array($p_num, \@bind_value) || die $sth->errstr;
$rv = $sth->bind_param_array($p_num, \@bind_value, \%attr) || ...
$rv = $sth->bind_param_array($p_num, \@bind_value, $bind_type) || ...
The C<bind_param_array> method can be used to bind an array of values
with a placeholder embedded in the prepared statement. This can
provide significant performance benefits for some drivers.
Some drivers do not support placeholders; some do not support
parameter arrays.
See also L</bind_param> and L</"Placeholders and Bind Values"> for more information.
=item C<bind_param_status>
$rc = $sth->bind_param_status($p_num, \@status_ary) || die $sth->errstr;
$rc = $sth->bind_param_status($p_num, \%status_hash) || ...
The C<bind_param_status> method is used to bind an array or hash
to receive the result status of each tuple of parameters bound
to a statement via L</bind_param_array>. If an array is used,
then status for *all* parameters is returned; if a hash is specified,
then only non-successful parameter tuples are reported, using the
corresponding index of the parameter tuple as the hash key.
See also L</bind_param> and L</"Placeholders and Bind Values"> for more
information.
<snip>
=item C<bind_col_array>
$rc = $sth->bind_col_array($column_number, \@array_to_bind, $max_rows);
Binds an output column (field) of a C<SELECT> statement to a Perl array.
See C<bind_columns> below for an example. Note that column numbers count
up from 1.
Whenever a fetch is executed against a data-returning statement,
the corresponding Perl array variable is automatically updated, returning
up to $max_rows rows of data. There is no need to fetch and assign
the values manually.
Binding arrays to columns can often provide significant performance benefits
when moving large amounts of data, especially when moving data between database
systems. If the arrays bound to the columns of the source database are also
bound as parameter arrays (using L</bind_param_array>) to the target database,
data movement can be handled with a very simple loop:
$src = $srcdbh->prepare('SELECT * FROM source');
$tgt = $tgtdbh->prepare('INSERT INTO target VALUES(?, ?, ?, ?)');
@col1 = ();
@col2 = ();
@col3 = ();
@col4 = ();
$src->bind_col_array(1, \@col1, 500);
$src->bind_col_array(2, \@col2, 500);
$src->bind_col_array(3, \@col3, 500);
$src->bind_col_array(4, \@col4, 500);
$tgt->bind_param_array(1, \@col1);
$tgt->bind_param_array(2, \@col2);
$tgt->bind_param_array(3, \@col3);
$tgt->bind_param_array(4, \@col4);
$src->execute;
while ($src->fetch) {
$tgt->execute;
}
Note that if L</fetchrow_array>, L</fetchrow_arrayref>, or L</fetchrow_hashref>
(rather than L</fetch>) is called for a statement which has array-bound
columns, the individual items in the returned row array are arrayrefs to either
the bound column arrays, or new arrays generated internally by the driver
module (for columns which were note explicitly bound to an array).
=item C<bind_row_status>
$rc = $sth->bind_row_status(\@array_to_bind);
$rc = $sth->bind_row_status(\%array_to_bind);
Binds a Perl array or hash to receive row status information for each row
returned as a result of using L</bind_col_array>. If an array is used, then
status for *all* returned rows will be provided; if a hash is specified, then
only rows reporting a non-successful status are returned, using their
respective index number in the array(s) bound by L</bind_col_array> as the
hash key. Status codes are integers from the ODBC set (e.g., SQL_SUCCESS,
SQL_SUCCESS_WITH_INFO, SQL_ERROR, etc.).
Regards,
Dean Arnold
----- Original Message -----
From: "Jeff Urlwin" <[EMAIL PROTECTED]>
To: "Dean Arnold" <[EMAIL PROTECTED]>
Cc: "Tim Bunce" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, August 08, 2001 12:43 PM
Subject: array binding (was: RE: Call for testers: Array binding via DBD::ODBC)
> 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.
>
> 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
> >
