Apache-LoggedAuthDBI
view release on metacpan or search on metacpan
use DBI;
@driver_names = DBI->available_drivers;
@data_sources = DBI->data_sources($driver_name, \%attr);
$dbh = DBI->connect($data_source, $username, $auth, \%attr);
$rv = $dbh->do($statement);
$rv = $dbh->do($statement, \%attr);
$rv = $dbh->do($statement, \%attr, @bind_values);
$ary_ref = $dbh->selectall_arrayref($statement);
$hash_ref = $dbh->selectall_hashref($statement, $key_field);
$ary_ref = $dbh->selectcol_arrayref($statement);
$ary_ref = $dbh->selectcol_arrayref($statement, \%attr);
@row_ary = $dbh->selectrow_array($statement);
$ary_ref = $dbh->selectrow_arrayref($statement);
$hash_ref = $dbh->selectrow_hashref($statement);
$sth = $dbh->prepare($statement);
$sth = $dbh->prepare_cached($statement);
$rc = $sth->bind_param($p_num, $bind_value);
$rc = $sth->bind_param($p_num, $bind_value, $bind_type);
$rc = $sth->bind_param($p_num, $bind_value, \%attr);
$rv = $sth->execute;
$rv = $sth->execute(@bind_values);
$rv = $sth->execute_array(\%attr, ...);
$rc = $sth->bind_col($col_num, \$col_variable);
$rc = $sth->bind_columns(@list_of_refs_to_vars_to_bind);
@row_ary = $sth->fetchrow_array;
$ary_ref = $sth->fetchrow_arrayref;
$hash_ref = $sth->fetchrow_hashref;
$ary_ref = $sth->fetchall_arrayref;
$ary_ref = $sth->fetchall_arrayref( $slice, $max_rows );
$hash_ref = $sth->fetchall_hashref( $key_field );
default_user => { U =>[3,4,'$user, $pass [, \%attr]' ] },
},
db => { # Database Session Class Interface
data_sources => { U =>[1,2,'[\%attr]' ], O=>0x0200 },
take_imp_data => { U =>[1,1], },
clone => { U =>[1,1,''] },
connected => { O=>0x0100 },
begin_work => { U =>[1,2,'[ \%attr ]'], O=>0x0400 },
commit => { U =>[1,1], O=>0x0480|0x0800 },
rollback => { U =>[1,1], O=>0x0480|0x0800 },
'do' => { U =>[2,0,'$statement [, \%attr [, @bind_params ] ]'], O=>0x3200 },
last_insert_id => { U =>[5,6,'$catalog, $schema, $table_name, $field_name [, \%attr ]'], O=>0x2800 },
preparse => { }, # XXX
prepare => { U =>[2,3,'$statement [, \%attr]'], O=>0x2200 },
prepare_cached => { U =>[2,4,'$statement [, \%attr [, $if_active ] ]'], O=>0x2200 },
selectrow_array => { U =>[2,0,'$statement [, \%attr [, @bind_params ] ]'], O=>0x2000 },
selectrow_arrayref=>{U =>[2,0,'$statement [, \%attr [, @bind_params ] ]'], O=>0x2000 },
selectrow_hashref=>{ U =>[2,0,'$statement [, \%attr [, @bind_params ] ]'], O=>0x2000 },
selectall_arrayref=>{U =>[2,0,'$statement [, \%attr [, @bind_params ] ]'], O=>0x2000 },
selectall_hashref=>{ U =>[3,0,'$statement, $keyfield [, \%attr [, @bind_params ] ]'], O=>0x2000 },
selectcol_arrayref=>{U =>[2,0,'$statement [, \%attr [, @bind_params ] ]'], O=>0x2000 },
ping => { U =>[1,1], O=>0x0404 },
disconnect => { U =>[1,1], O=>0x0400|0x0800 },
quote => { U =>[2,3, '$string [, $data_type ]' ], O=>0x0430 },
quote_identifier=> { U =>[2,6, '$name [, ...] [, \%attr ]' ], O=>0x0430 },
rows => $keeperr,
tables => { U =>[1,6,'$catalog, $schema, $table, $type [, \%attr ]' ], O=>0x2200 },
table_info => { U =>[1,6,'$catalog, $schema, $table, $type [, \%attr ]' ], O=>0x2200|0x0800 },
column_info => { U =>[5,6,'$catalog, $schema, $table, $column [, \%attr ]'],O=>0x2200|0x0800 },
primary_key_info=> { U =>[4,5,'$catalog, $schema, $table [, \%attr ]' ], O=>0x2200|0x0800 },
primary_key => { U =>[4,5,'$catalog, $schema, $table [, \%attr ]' ], O=>0x2200 },
foreign_key_info=> { U =>[7,8,'$pk_catalog, $pk_schema, $pk_table, $fk_catalog, $fk_schema, $fk_table [, \%attr ]' ], O=>0x2200|0x0800 },
type_info_all => { U =>[1,1], O=>0x2200|0x0800 },
type_info => { U =>[1,2,'$data_type'], O=>0x2200 },
get_info => { U =>[2,2,'$info_type'], O=>0x2200|0x0800 },
},
st => { # Statement Class Interface
bind_col => { U =>[3,4,'$column, \\$var [, \%attr]'] },
bind_columns => { U =>[2,0,'\\$var1 [, \\$var2, ...]'] },
bind_param => { U =>[3,4,'$parameter, $var [, \%attr]'] },
bind_param_inout=> { U =>[4,5,'$parameter, \\$var, $maxlen, [, \%attr]'] },
execute => { U =>[1,0,'[@args]'], O=>0x1040 },
bind_param_array => { U =>[3,4,'$parameter, $var [, \%attr]'] },
bind_param_inout_array => { U =>[4,5,'$parameter, \\@var, $maxlen, [, \%attr]'] },
execute_array => { U =>[2,0,'\\%attribs [, @args]'], O=>0x1040 },
execute_for_fetch => { U =>[2,3,'$fetch_sub [, $tuple_status]'], O=>0x1040 },
fetch => undef, # alias for fetchrow_arrayref
fetchrow_arrayref => undef,
fetchrow_hashref => undef,
fetchrow_array => undef,
fetchrow => undef, # old alias for fetchrow_array
fetchall_arrayref => { U =>[1,3, '[ $slice [, $max_rows]]'] },
sub do {
my($dbh, $statement, $attr, @params) = @_;
my $sth = $dbh->prepare($statement, $attr) or return undef;
$sth->execute(@params) or return undef;
my $rows = $sth->rows;
($rows == 0) ? "0E0" : $rows;
}
sub _do_selectrow {
my ($method, $dbh, $stmt, $attr, @bind) = @_;
my $sth = ((ref $stmt) ? $stmt : $dbh->prepare($stmt, $attr))
or return;
$sth->execute(@bind)
or return;
my $row = $sth->$method()
and $sth->finish;
return $row;
}
sub selectrow_hashref { return _do_selectrow('fetchrow_hashref', @_); }
# XXX selectrow_array/ref also have C implementations in Driver.xst
sub selectrow_arrayref { return _do_selectrow('fetchrow_arrayref', @_); }
sub selectrow_array {
my $row = _do_selectrow('fetchrow_arrayref', @_) or return;
return $row->[0] unless wantarray;
return @$row;
}
# XXX selectall_arrayref also has C implementation in Driver.xst
# which fallsback to this if a slice is given
sub selectall_arrayref {
my ($dbh, $stmt, $attr, @bind) = @_;
my $sth = (ref $stmt) ? $stmt : $dbh->prepare($stmt, $attr)
or return;
$sth->execute(@bind) || return;
my $slice = $attr->{Slice}; # typically undef, else hash or array ref
if (!$slice and $slice=$attr->{Columns}) {
if (ref $slice eq 'ARRAY') { # map col idx to perl array idx
$slice = [ @{$attr->{Columns}} ]; # take a copy
for (@$slice) { $_-- }
}
}
my $rows = $sth->fetchall_arrayref($slice, my $MaxRows = $attr->{MaxRows});
$sth->finish if defined $MaxRows;
return $rows;
}
sub selectall_hashref {
my ($dbh, $stmt, $key_field, $attr, @bind) = @_;
my $sth = (ref $stmt) ? $stmt : $dbh->prepare($stmt, $attr);
return unless $sth;
$sth->execute(@bind) || return;
return $sth->fetchall_hashref($key_field);
}
sub selectcol_arrayref {
my ($dbh, $stmt, $attr, @bind) = @_;
my $sth = (ref $stmt) ? $stmt : $dbh->prepare($stmt, $attr);
return unless $sth;
$sth->execute(@bind) || return;
my @columns = ($attr->{Columns}) ? @{$attr->{Columns}} : (1);
my @values = (undef) x @columns;
my $idx = 0;
for (@columns) {
$sth->bind_col($_, \$values[$idx++]) || return;
}
my @col;
if (my $max = $attr->{MaxRows}) {
push @col, @values while @col<$max && $sth->fetch;
}
else {
push @col, @values while $sth->fetch;
}
return \@col;
}
}
}
{ package # hide from PAUSE
DBD::_::st; # ====== STATEMENT ======
@DBD::_::st::ISA = qw(DBD::_::common);
use strict;
sub bind_param { Carp::croak("Can't bind_param, not implement by driver") }
#
# ********************************************************
#
# BEGIN ARRAY BINDING
#
# Array binding support for drivers which don't support
# array binding, but have sufficient interfaces to fake it.
# NOTE: mixing scalars and arrayrefs requires using bind_param_array
# for *all* params...unless we modify bind_param for the default
# case...
#
# 2002-Apr-10 D. Arnold
sub bind_param_array {
my $sth = shift;
my ($p_id, $value_array, $attr) = @_;
return $sth->set_err(1, "Value for parameter $p_id must be a scalar or an arrayref, not a ".ref($value_array))
if defined $value_array and ref $value_array and ref $value_array ne 'ARRAY';
return $sth->set_err(1, "Can't use named placeholder '$p_id' for non-driver supported bind_param_array")
unless DBI::looks_like_number($p_id); # because we rely on execute(@ary) here
return $sth->set_err(1, "Placeholder '$p_id' is out of range")
if $p_id <= 0; # can't easily/reliably test for too big
# get/create arrayref to hold params
my $hash_of_arrays = $sth->{ParamArrays} ||= { };
# If the bind has attribs then we rely on the driver conforming to
# the DBI spec in that a single bind_param() call with those attribs
# makes them 'sticky' and apply to all later execute(@values) calls.
# Since we only call bind_param() if we're given attribs then
# applications using drivers that don't support bind_param can still
# use bind_param_array() so long as they don't pass any attribs.
$$hash_of_arrays{$p_id} = $value_array;
return $sth->bind_param($p_id, undef, $attr)
if $attr;
1;
}
sub bind_param_inout_array {
my $sth = shift;
# XXX not supported so we just call bind_param_array instead
# and then return an error
my ($p_num, $value_array, $attr) = @_;
$sth->bind_param_array($p_num, $value_array, $attr);
return $sth->set_err(1, "bind_param_inout_array not supported");
}
sub bind_columns {
my $sth = shift;
my $fields = $sth->FETCH('NUM_OF_FIELDS') || 0;
if ($fields <= 0 && !$sth->{Active}) {
return $sth->set_err(1, "Statement has no result columns to bind"
." (perhaps you need to successfully call execute first)");
}
# Backwards compatibility for old-style call with attribute hash
# ref as first arg. Skip arg if undef or a hash ref.
my $attr;
$attr = shift if !defined $_[0] or ref($_[0]) eq 'HASH';
die "bind_columns called with ".@_." refs when $fields needed."
if @_ != $fields;
my $idx = 0;
$sth->bind_col(++$idx, shift, $attr) or return
while (@_);
return 1;
}
sub execute_array {
my $sth = shift;
my ($attr, @array_of_arrays) = @_;
my $NUM_OF_PARAMS = $sth->FETCH('NUM_OF_PARAMS'); # may be undef at this point
# get tuple status array or hash attribute
my $tuple_sts = $attr->{ArrayTupleStatus};
return $sth->set_err(1, "ArrayTupleStatus attribute must be an arrayref")
if $tuple_sts and ref $tuple_sts ne 'ARRAY';
# bind all supplied arrays
if (@array_of_arrays) {
$sth->{ParamArrays} = { }; # clear out old params
return $sth->set_err(1,
@array_of_arrays." bind values supplied but $NUM_OF_PARAMS expected")
if defined ($NUM_OF_PARAMS) && @array_of_arrays != $NUM_OF_PARAMS;
$sth->bind_param_array($_, $array_of_arrays[$_-1]) or return
foreach (1..@array_of_arrays);
}
my $fetch_tuple_sub;
if ($fetch_tuple_sub = $attr->{ArrayTupleFetch}) { # fetch on demand
return $sth->set_err(1,
"Can't use both ArrayTupleFetch and explicit bind values")
if @array_of_arrays; # previous bind_param_array calls will simply be ignored
if (UNIVERSAL::isa($fetch_tuple_sub,'DBI::st')) {
my $fetch_sth = $fetch_tuple_sub;
return $sth->set_err(1,
"ArrayTupleFetch sth is not Active, need to execute() it first")
unless $fetch_sth->{Active};
# check column count match to give more friendly message
my $NUM_OF_FIELDS = $fetch_sth->{NUM_OF_FIELDS};
return $sth->set_err(1,
"$NUM_OF_FIELDS columns from ArrayTupleFetch sth but $NUM_OF_PARAMS expected")
$fetch_tuple_sub = sub { $fetch_sth->fetchrow_arrayref };
}
elsif (!UNIVERSAL::isa($fetch_tuple_sub,'CODE')) {
return $sth->set_err(1, "ArrayTupleFetch '$fetch_tuple_sub' is not a code ref or statement handle");
}
}
else {
my $NUM_OF_PARAMS_given = keys %{ $sth->{ParamArrays} || {} };
return $sth->set_err(1,
"$NUM_OF_PARAMS_given bind values supplied but $NUM_OF_PARAMS expected")
if defined($NUM_OF_PARAMS) && $NUM_OF_PARAMS != $NUM_OF_PARAMS_given;
# get the length of a bound array
my $maxlen;
my %hash_of_arrays = %{$sth->{ParamArrays}};
foreach (keys(%hash_of_arrays)) {
my $ary = $hash_of_arrays{$_};
next unless ref $ary eq 'ARRAY';
$maxlen = @$ary if !$maxlen || @$ary > $maxlen;
}
# if there are no arrays then execute scalars once
$maxlen = 1 unless defined $maxlen;
my @bind_ids = 1..keys(%hash_of_arrays);
my $tuple_idx = 0;
$fetch_tuple_sub = sub {
return if $tuple_idx >= $maxlen;
my @tuple = map {
my $a = $hash_of_arrays{$_};
ref($a) ? $a->[$tuple_idx] : $a
} @bind_ids;
++$tuple_idx;
return \@tuple;
};
}
return $sth->execute_for_fetch($fetch_tuple_sub, $tuple_sts);
}
sub execute_for_fetch {
my ($sth, $fetch_tuple_sub, $tuple_status) = @_;
foreach (@key_fields) {
my $index = $names_hash->{$_}; # perl index not column
$index = $_ - 1 if !defined $index && DBI::looks_like_number($_) && $_>=1 && $_ <= $num_of_fields;
return $sth->set_err(1, "Field '$_' does not exist (not one of @{[keys %$names_hash]})")
unless defined $index;
push @key_indexes, $index;
}
my $rows = {};
my $NAME = $sth->FETCH($hash_key_name);
my @row = (undef) x $num_of_fields;
$sth->bind_columns(\(@row));
while ($sth->fetch) {
my $ref = $rows;
$ref = $ref->{$row[$_]} ||= {} for @key_indexes;
@{$ref}{@$NAME} = @row;
}
return $rows;
}
*dump_results = \&DBI::dump_results;
For an interesting diversion on the I<real> history of RDBMS and SQL,
from the people who made it happen, see:
http://ftp.digital.com/pub/DEC/SRC/technical-notes/SRC-1997-018-html/sqlr95.html
Follow the "Full Contents" then "Intergalactic dataspeak" links for the
SQL history.
=head2 Placeholders and Bind Values
Some drivers support placeholders and bind values.
I<Placeholders>, also called parameter markers, are used to indicate
values in a database statement that will be supplied later,
before the prepared statement is executed. For example, an application
might use the following to insert a row of data into the SALES table:
INSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?)
or the following, to select the description for a product:
SELECT description FROM products WHERE product_code = ?
The C<?> characters are the placeholders. The association of actual
values with placeholders is known as I<binding>, and the values are
referred to as I<bind values>.
Note that the C<?> is not enclosed in quotation marks, even when the
placeholder represents a string. Some drivers also allow placeholders
like C<:>I<name> and C<:>I<n> (e.g., C<:1>, C<:2>, and so on)
in addition to C<?>, but their use is not portable.
With most drivers, placeholders can't be used for any element of a
statement that would prevent the database server from validating the
statement and creating a query execution plan for it. For example:
Also, placeholders can only represent single scalar values.
For example, the following
statement won't work as expected for more than one value:
"SELECT name, age FROM people WHERE name IN (?)" # wrong
"SELECT name, age FROM people WHERE name IN (?,?)" # two names
When using placeholders with the SQL C<LIKE> qualifier, you must
remember that the placeholder substitutes for the whole string.
So you should use "C<... LIKE ? ...>" and include any wildcard
characters in the value that you bind to the placeholder.
B<NULL Values>
Undefined values, or C<undef>, are used to indicate NULL values.
You can insert and update columns with a NULL value as you would a
non-NULL value. These examples insert and update the column
C<age> with a NULL value:
$sth = $dbh->prepare(qq{
INSERT INTO people (fullname, age) VALUES (?, ?)
});
$sth->execute(defined $age ? $age : ());
The following technique illustrates qualifying a C<WHERE> clause with
several columns, whose associated values (C<defined> or C<undef>) are
in a hash %h:
for my $col ("age", "phone", "email") {
if (defined $h{$col}) {
push @sql_qual, "$col = ?";
push @sql_bind, $h{$col};
}
else {
push @sql_qual, "$col IS NULL";
}
}
$sql_clause = join(" AND ", @sql_qual);
$sth = $dbh->prepare(qq{
SELECT fullname FROM people WHERE $sql_clause
});
$sth->execute(@sql_bind);
The techniques above call prepare for the SQL statement with each call to
execute. Because calls to prepare() can be expensive, performance
can suffer when an application iterates many times over statements
like the above.
A better solution is a single C<WHERE> clause that supports both
NULL and non-NULL comparisons. Its SQL statement would need to be
prepared only once for all cases, thus improving performance.
Several examples of C<WHERE> clauses that support this are presented
on your database engine and tell you which ones work. It is located
in the F<ex/> subdirectory of the DBI source distribution, or here:
L<http://svn.perl.org/modules/dbi/trunk/ex/perl_dbi_nulls_test.pl>
Please use the script to help us fill-in and maintain this table.
B<Performance>
Without using placeholders, the insert statement shown previously would have to
contain the literal values to be inserted and would have to be
re-prepared and re-executed for each row. With placeholders, the insert
statement only needs to be prepared once. The bind values for each row
can be given to the C<execute> method each time it's called. By avoiding
the need to re-prepare the statement for each row, the application
typically runs many times faster. Here's an example:
my $sth = $dbh->prepare(q{
INSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?)
}) or die $dbh->errstr;
while (<>) {
chomp;
my ($product_code, $qty, $price) = split /,/;
$sth->execute($product_code, $qty, $price) or die $dbh->errstr;
}
$dbh->commit or die $dbh->errstr;
See L</execute> and L</bind_param> for more details.
The C<q{...}> style quoting used in this example avoids clashing with
quotes that may be used in the SQL statement. Use the double-quote like
C<qq{...}> operator if you want to interpolate variables into the string.
See L<perlop/"Quote and Quote-like Operators"> for more details.
See also the L</bind_columns> method, which is used to associate Perl
variables with the output columns of a C<SELECT> statement.
=head1 THE DBI PACKAGE AND CLASS
In this section, we cover the DBI class methods, utility functions,
and the dynamic attributes associated with generic DBI handles.
=head2 DBI Constants
Constants representing the values of the SQL standard types can be
produced like this:
foreach (@{ $DBI::EXPORT_TAGS{sql_types} }) {
printf "%s=%d\n", $_, &{"DBI::$_"};
}
These constants are defined by SQL/CLI, ODBC or both.
C<SQL_BIGINT> is (currently) omitted, because SQL/CLI and ODBC provide
conflicting codes.
See the L</type_info>, L</type_info_all>, and L</bind_param> methods
for possible uses.
Note that just because the DBI defines a named constant for a given
data type doesn't mean that drivers will support that data type.
=head2 DBI Class Methods
The following methods are provided by the DBI class:
Data sources are returned in a form suitable for passing to the
L</connect> method (that is, they will include the "C<dbi:$driver:>" prefix).
The data_sources() method, for a $dbh, was added in DBI 1.38.
=item C<do>
$rows = $dbh->do($statement) or die $dbh->errstr;
$rows = $dbh->do($statement, \%attr) or die $dbh->errstr;
$rows = $dbh->do($statement, \%attr, @bind_values) or die ...
Prepare and execute a single statement. Returns the number of rows
affected or C<undef> on error. A return value of C<-1> means the
number of rows is not known, not applicable, or not available.
This method is typically most useful for I<non>-C<SELECT> statements that
either cannot be prepared in advance (due to a limitation of the
driver) or do not need to be executed repeatedly. It should not
be used for C<SELECT> statements because it does not return a statement
handle (so you can't fetch any data).
The default C<do> method is logically similar to:
sub do {
my($dbh, $statement, $attr, @bind_values) = @_;
my $sth = $dbh->prepare($statement, $attr) or return undef;
$sth->execute(@bind_values) or return undef;
my $rows = $sth->rows;
($rows == 0) ? "0E0" : $rows; # always return true if no error
}
For example:
my $rows_deleted = $dbh->do(q{
DELETE FROM table
WHERE status = ?
}, undef, 'DONE') or die $dbh->errstr;
Using placeholders and C<@bind_values> with the C<do> method can be
useful because it avoids the need to correctly quote any variables
in the C<$statement>. But if you'll be executing the statement many
times then it's more efficient to C<prepare> it once and call
C<execute> many times instead.
The C<q{...}> style quoting used in this example avoids clashing with
quotes that may be used in the SQL statement. Use the double-quote-like
C<qq{...}> operator if you want to interpolate variables into the string.
See L<perlop/"Quote and Quote-like Operators"> for more details.
Given all the caveats above, it's clear that this method must be
used with care.
The C<last_insert_id> method was added in DBI 1.38.
=item C<selectrow_array>
@row_ary = $dbh->selectrow_array($statement);
@row_ary = $dbh->selectrow_array($statement, \%attr);
@row_ary = $dbh->selectrow_array($statement, \%attr, @bind_values);
This utility method combines L</prepare>, L</execute> and
L</fetchrow_array> into a single call. If called in a list context, it
returns the first row of data from the statement. The C<$statement>
parameter can be a previously prepared statement handle, in which case
the C<prepare> is skipped.
If any method fails, and L</RaiseError> is not set, C<selectrow_array>
will return an empty list.
more rows or if an error occurred. That C<undef> can't be distinguished
from an C<undef> returned because the first field value was NULL.
For these reasons you should exercise some caution if you use
C<selectrow_array> in a scalar context.
=item C<selectrow_arrayref>
$ary_ref = $dbh->selectrow_arrayref($statement);
$ary_ref = $dbh->selectrow_arrayref($statement, \%attr);
$ary_ref = $dbh->selectrow_arrayref($statement, \%attr, @bind_values);
This utility method combines L</prepare>, L</execute> and
L</fetchrow_arrayref> into a single call. It returns the first row of
data from the statement. The C<$statement> parameter can be a previously
prepared statement handle, in which case the C<prepare> is skipped.
If any method fails, and L</RaiseError> is not set, C<selectrow_array>
will return undef.
=item C<selectrow_hashref>
$hash_ref = $dbh->selectrow_hashref($statement);
$hash_ref = $dbh->selectrow_hashref($statement, \%attr);
$hash_ref = $dbh->selectrow_hashref($statement, \%attr, @bind_values);
This utility method combines L</prepare>, L</execute> and
L</fetchrow_hashref> into a single call. It returns the first row of
data from the statement. The C<$statement> parameter can be a previously
prepared statement handle, in which case the C<prepare> is skipped.
If any method fails, and L</RaiseError> is not set, C<selectrow_hashref>
will return undef.
=item C<selectall_arrayref>
$ary_ref = $dbh->selectall_arrayref($statement);
$ary_ref = $dbh->selectall_arrayref($statement, \%attr);
$ary_ref = $dbh->selectall_arrayref($statement, \%attr, @bind_values);
This utility method combines L</prepare>, L</execute> and
L</fetchall_arrayref> into a single call. It returns a reference to an
array containing a reference to an array for each row of data fetched.
The C<$statement> parameter can be a previously prepared statement handle,
in which case the C<prepare> is skipped. This is recommended if the
statement is going to be executed many times.
If L</RaiseError> is not set and any method except C<fetchall_arrayref>
C<Columns> is an array ref, then the array is assumed to contain column
index values (which count from 1), rather than perl array index values.
In which case the array is copied and each value decremented before
passing to C</fetchall_arrayref>.
=item C<selectall_hashref>
$hash_ref = $dbh->selectall_hashref($statement, $key_field);
$hash_ref = $dbh->selectall_hashref($statement, $key_field, \%attr);
$hash_ref = $dbh->selectall_hashref($statement, $key_field, \%attr, @bind_values);
This utility method combines L</prepare>, L</execute> and
L</fetchall_hashref> into a single call. It returns a reference to a
hash containing one entry, at most, for each row, as returned by fetchall_hashref().
The C<$key_field> parameter defines which column, or columns, are used as keys
in the returned hash. It can either be the name of a single field, or a
reference to an array containing multiple field names. See fetchall_hashref()
for more details.
If any method except C<fetchrow_hashref> fails, and L</RaiseError> is not set,
C<selectall_hashref> will return C<undef>. If C<fetchrow_hashref> fails and
L</RaiseError> is not set, then it will return with whatever data it
has fetched thus far. $DBI::err should be checked to catch that.
=item C<selectcol_arrayref>
$ary_ref = $dbh->selectcol_arrayref($statement);
$ary_ref = $dbh->selectcol_arrayref($statement, \%attr);
$ary_ref = $dbh->selectcol_arrayref($statement, \%attr, @bind_values);
This utility method combines L</prepare>, L</execute>, and fetching one
column from all the rows, into a single call. It returns a reference to
an array containing the values of the first column from each row.
The C<$statement> parameter can be a previously prepared statement handle,
in which case the C<prepare> is skipped. This is recommended if the
statement is going to be executed many times.
If any method except C<fetch> fails, and L</RaiseError> is not set,
This section lists the methods and attributes associated with DBI
statement handles.
=head2 Statement Handle Methods
The DBI defines the following methods for use on DBI statement handles:
=over 4
=item C<bind_param>
$sth->bind_param($p_num, $bind_value)
$sth->bind_param($p_num, $bind_value, \%attr)
$sth->bind_param($p_num, $bind_value, $bind_type)
The C<bind_param> method takes a copy of $bind_value and associates it
(binds it) with a placeholder, identified by $p_num, embedded in
the prepared statement. Placeholders are indicated with question
mark character (C<?>). For example:
$dbh->{RaiseError} = 1; # save having to check each method call
$sth = $dbh->prepare("SELECT name, age FROM people WHERE name LIKE ?");
$sth->bind_param(1, "John%"); # placeholders are numbered from 1
$sth->execute;
DBI::dump_results($sth);
See L</"Placeholders and Bind Values"> for more information.
B<Data Types for Placeholders>
The C<\%attr> parameter can be used to hint at the data type the
placeholder should have. Typically, the driver is only interested in
knowing if the placeholder should be bound as a number or a string.
$sth->bind_param(1, $value, { TYPE => SQL_INTEGER });
As a short-cut for the common case, the data type can be passed
directly, in place of the C<\%attr> hash reference. This example is
equivalent to the one above:
$sth->bind_param(1, $value, SQL_INTEGER);
The C<TYPE> value indicates the standard (non-driver-specific) type for
this parameter. To specify the driver-specific type, the driver may
support a driver-specific attribute, such as C<{ ora_type =E<gt> 97 }>.
The SQL_INTEGER and other related constants can be imported using
use DBI qw(:sql_types);
See L</"DBI Constants"> for more information.
The data type for a placeholder cannot be changed after the first
C<bind_param> call. In fact the whole \%attr parameter is 'sticky'
in the sense that a driver only needs to consider the \%attr parameter
for the first call, for a given $sth and parameter. After that the driver
may ignore the \%attr parameter for that placeholder.
Perl only has string and number scalar data types. All database types
that aren't numbers are bound as strings and must be in a format the
database will understand except where the bind_param() TYPE attribute
specifies a type that implies a particular format. For example, given:
$sth->bind_param(1, $value, SQL_DATETIME);
the driver should expect $value to be in the ODBC standard SQL_DATETIME
format, which is 'YYYY-MM-DD HH:MM:SS'. Similarly for SQL_DATE, SQL_TIME etc.
As an alternative to specifying the data type in the C<bind_param> call,
you can let the driver pass the value as the default type (C<VARCHAR>).
You can then use an SQL function to convert the type within the statement.
For example:
INSERT INTO price(code, price) VALUES (?, CONVERT(MONEY,?))
The C<CONVERT> function used here is just an example. The actual function
and syntax will vary between different databases and is non-portable.
See also L</"Placeholders and Bind Values"> for more information.
=item C<bind_param_inout>
$rc = $sth->bind_param_inout($p_num, \$bind_value, $max_len) or die $sth->errstr;
$rv = $sth->bind_param_inout($p_num, \$bind_value, $max_len, \%attr) or ...
$rv = $sth->bind_param_inout($p_num, \$bind_value, $max_len, $bind_type) or ...
This method acts like L</bind_param>, but also enables values to be
updated by the statement. The statement is typically
a call to a stored procedure. The C<$bind_value> must be passed as a
reference to the actual value to be used.
Note that unlike L</bind_param>, the C<$bind_value> variable is not
copied when C<bind_param_inout> is called. Instead, the value in the
variable is read at the time L</execute> is called.
The additional C<$max_len> parameter specifies the minimum amount of
memory to allocate to C<$bind_value> for the new value. If the value
returned from the database is too
big to fit, then the execution should fail. If unsure what value to use,
pick a generous length, i.e., a length larger than the longest value that would ever be
returned. The only cost of using a larger value than needed is wasted memory.
Undefined values or C<undef> are used to indicate null values.
See also L</"Placeholders and Bind Values"> for more information.
=item C<bind_param_array>
$rc = $sth->bind_param_array($p_num, $array_ref_or_value)
$rc = $sth->bind_param_array($p_num, $array_ref_or_value, \%attr)
$rc = $sth->bind_param_array($p_num, $array_ref_or_value, $bind_type)
The C<bind_param_array> method is used to bind an array of values
to a placeholder embedded in the prepared statement which is to be executed
with L</execute_array>. For example:
$dbh->{RaiseError} = 1; # save having to check each method call
$sth = $dbh->prepare("INSERT INTO staff (first_name, last_name, dept) VALUES(?, ?, ?)");
$sth->bind_param_array(1, [ 'John', 'Mary', 'Tim' ]);
$sth->bind_param_array(2, [ 'Booth', 'Todd', 'Robinson' ]);
$sth->bind_param_array(3, "SALES"); # scalar will be reused for each row
$sth->execute_array( { ArrayTupleStatus => \my @tuple_status } );
The C<%attr> ($bind_type) argument is the same as defined for L</bind_param>.
Refer to L</bind_param> for general details on using placeholders.
(Note that bind_param_array() can I<not> be used to expand a
placeholder into a list of values for a statement like "SELECT foo
WHERE bar IN (?)". A placeholder can only ever represent one value
per execution.)
Scalar values, including C<undef>, may also be bound by
C<bind_param_array>. In which case the same value will be used for each
L</execute> call. Driver-specific implementations may behave
differently, e.g., when binding to a stored procedure call, some
databases may permit mixing scalars and arrays as arguments.
The default implementation provided by DBI (for drivers that have
not implemented array binding) is to iteratively call L</execute> for
each parameter tuple provided in the bound arrays. Drivers may
provide more optimized implementations using whatever bulk operation
support the database API provides. The default driver behaviour should
match the default DBI behaviour, but always consult your driver
documentation as there may be driver specific issues to consider.
Note that the default implementation currently only supports non-data
returning statements (INSERT, UPDATE, but not SELECT). Also,
C<bind_param_array> and L</bind_param> cannot be mixed in the same
statement execution, and C<bind_param_array> must be used with
L</execute_array>; using C<bind_param_array> will have no effect
for L</execute>.
The C<bind_param_array> method was added in DBI 1.22.
=item C<execute>
$rv = $sth->execute or die $sth->errstr;
$rv = $sth->execute(@bind_values) or die $sth->errstr;
Perform whatever processing is necessary to execute the prepared
statement. An C<undef> is returned if an error occurs. A successful
C<execute> always returns true regardless of the number of rows affected,
even if it's zero (see below). It is always important to check the
return status of C<execute> (and most other DBI methods) for errors
if you're not using L</RaiseError>.
For a I<non>-C<SELECT> statement, C<execute> returns the number of rows
affected, if known. If no rows were affected, then C<execute> returns
is I<not> an error for no rows to be affected by a statement. If the
number of rows affected is not known, then C<execute> returns -1.
For C<SELECT> statements, execute simply "starts" the query within the
database engine. Use one of the fetch methods to retrieve the data after
calling C<execute>. The C<execute> method does I<not> return the number of
rows that will be returned by the query (because most databases can't
tell in advance), it simply returns a true value.
If any arguments are given, then C<execute> will effectively call
L</bind_param> for each value before executing the statement. Values
bound in this way are usually treated as C<SQL_VARCHAR> types unless
the driver can determine the correct type (which is rare), or unless
C<bind_param> (or C<bind_param_inout>) has already been used to
specify the type.
If execute() is called on a statement handle that's still active
($sth->{Active} is true) then it should effectively call finish()
to tidy up the previous execution results before starting this new
execution.
=item C<execute_array>
$rv = $sth->execute_array(\%attr) or die $sth->errstr;
$rv = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
Execute the prepared statement once for each parameter tuple
(group of values) provided either in the @bind_values, or by prior
calls to L</bind_param_array>, or via a reference passed in \%attr.
The execute_array() method returns the number of tuples executed,
or C<undef> if an error occured. Like execute(), a successful
execute_array() always returns true regardless of the number of
tuples executed, even if it's zero. See the C<ArrayTupleStatus>
attribute below for how to determine the execution status for each
tuple.
Bind values for the tuples to be executed may be supplied row-wise
by an C<ArrayTupleFetch> attribute, or else column-wise in the
C<@bind_values> argument, or else column-wise by prior calls to
L</bind_param_array>.
Where column-wise binding is used (via the C<@bind_values> argument
or calls to bind_param_array()) the maximum number of elements in
any one of the bound value arrays determines the number of tuples
executed. Placeholders with fewer values in their parameter arrays
are treated as if padded with undef (NULL) values.
If a scalar value is bound, instead of an array reference, it is
treated as a I<variable> length array with all elements having the
same value. It's does not influence the number of tuples executed,
so if all bound arrays have zero elements then zero tuples will
be executed. If I<all> bound values are scalars then one tuple
will be executed, making execute_array() act just like execute().
The C<ArrayTupleFetch> attribute can be used to specify a reference
to a subroutine that will be called to provide the bind values for
each tuple execution. The subroutine should return an reference to
an array which contains the appropriate number of bind values, or
return an undef if there is no more data to execute.
As a convienience, the C<ArrayTupleFetch> attribute can also be
used to specify a statement handle. In which case the fetchrow_arrayref()
method will be called on the given statement handle in order to
provide the bind values for each tuple execution.
The values specified via bind_param_array() or the @bind_values
parameter may be either scalars, or arrayrefs. If any C<@bind_values>
are given, then C<execute_array> will effectively call L</bind_param_array>
for each value before executing the statement. Values bound in
this way are usually treated as C<SQL_VARCHAR> types unless the
driver can determine the correct type (which is rare), or unless
C<bind_param>, C<bind_param_inout>, C<bind_param_array>, or
C<bind_param_inout_array> has already been used to specify the type.
See L</bind_param_array> for details.
The mandatory C<ArrayTupleStatus> attribute is used to specify a
reference to an array which will receive the execute status of each
executed parameter tuple.
For tuples which are successfully executed, the element at the same
ordinal position in the status array is the resulting rowcount.
If the execution of a tuple causes an error, then the corresponding
status array element will be set to a reference to an array containing
the error code and error string set by the failed execution.
next unless ref $status;
printf "Failed to insert (%s, %s): %s\n",
$first_names[$tuple], $last_names[$tuple], $status->[1];
}
}
Support for data returning statements such as SELECT is driver-specific
and subject to change. At present, the default implementation
provided by DBI only supports non-data returning statements.
Transaction semantics when using array binding are driver and
database specific. If C<AutoCommit> is on, the default DBI
implementation will cause each parameter tuple to be inidividually
committed (or rolled back in the event of an error). If C<AutoCommit>
is off, the application is responsible for explicitly committing
the entire set of bound parameter tuples. Note that different
drivers and databases may have different behaviours when some
parameter tuples cause failures. In some cases, the driver or
database may automatically rollback the effect of all prior parameter
tuples that succeeded in the transaction; other drivers or databases
may retain the effect of prior successfully executed parameter
$rc = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
The execute_for_fetch() method is used to perform bulk operations
and is most often used via the execute_array() method, not directly.
The fetch subroutine, referenced by $fetch_tuple_sub, is expected
to return a reference to an array (known as a 'tuple') or undef.
The execute_for_fetch() method calls $fetch_tuple_sub, without any
parameters, until it returns a false value. Each tuple returned is
used to provide bind values for an $sth->execute(@$tuple) call.
If there were any errors then C<undef> is returned and the @tuple_status
array can be used to discover which tuples failed and with what errors.
If there were no errors then execute_for_fetch() returns the number
of tuples executed. Like execute() and execute_array() a zero is
returned as "0E0" so execute_for_fetch() is only false on error.
If \@tuple_status is passed then the execute_for_fetch method uses
it to return status information. The tuple_status array holds one
element per tuple. If the corresponding execute() did not fail then
=item C<fetchrow_arrayref>
$ary_ref = $sth->fetchrow_arrayref;
$ary_ref = $sth->fetch; # alias
Fetches the next row of data and returns a reference to an array
holding the field values. Null fields are returned as C<undef>
values in the array.
This is the fastest way to fetch data, particularly if used with
C<$sth-E<gt>bind_columns>.
If there are no more rows or if an error occurs, then C<fetchrow_arrayref>
returns an C<undef>. You should check C<$sth-E<gt>err> afterwards (or use the
C<RaiseError> attribute) to discover if the C<undef> returned was due to an
error.
Note that the same array reference is returned for each fetch, so don't
store the reference and then use it after a later fetch. Also, the
elements of the array are also reused for each row, so take care if you
want to take a reference to an element. See also L</bind_columns>.
=item C<fetchrow_array>
@ary = $sth->fetchrow_array;
An alternative to C<fetchrow_arrayref>. Fetches the next row of data
and returns it as a list containing the field values. Null fields
are returned as C<undef> values in the list.
If there are no more rows or if an error occurs, then C<fetchrow_array>
my $rows = []; # cache for batches of rows
while( my $row = ( shift(@$rows) || # get row from cache, or reload cache:
shift(@{$rows=$sth->fetchall_arrayref(undef,10_000)||[]}) )
) {
...
}
That can be the fastest way to fetch and process lots of rows using the DBI,
but it depends on the relative cost of method calls vs memory allocation.
A standard C<while> loop with column binding is often faster because
the cost of allocating memory for the batch of rows is greater than
the saving by reducing method calls. It's possible that the DBI may
provide a way to reuse the memory of a previous batch in future, which
would then shift the balance back towards fetchall_arrayref().
=item C<fetchall_hashref>
$hash_ref = $sth->fetchall_hashref($key_field);
return the number of rows the application has fetched so far, but
others may return -1 until all rows have been fetched. So use of the
C<rows> method or C<$DBI::rows> with C<SELECT> statements is not
recommended.
One alternative method to get a row count for a C<SELECT> is to execute a
"SELECT COUNT(*) FROM ..." SQL statement with the same "..." as your
query and then fetch the row count from that.
=item C<bind_col>
$rc = $sth->bind_col($column_number, \$var_to_bind);
$rc = $sth->bind_col($column_number, \$var_to_bind, \%attr );
$rc = $sth->bind_col($column_number, \$var_to_bind, $bind_type );
Binds a Perl variable and/or some attributes to an output column
(field) of a C<SELECT> statement. Column numbers count up from 1.
You do not need to bind output columns in order to fetch data.
For maximum portability between drivers, bind_col() should be called
after execute() and not before.
See also C<bind_columns> for an example.
The binding is performed at a low level using Perl aliasing.
Whenever a row is fetched from the database $var_to_bind appears
to be automatically updated simply because it refers to the same
memory location as the corresponding column value. This makes using
bound variables very efficient. Multiple variables can be bound
to a single column, but there's rarely any point. Binding a tied
variable doesn't work, currently.
The L</bind_param> method
performs a similar, but opposite, function for input variables.
B<Data Types for Column Binding>
The C<\%attr> parameter can be used to hint at the data type
formatting the column should have. For example, you can use:
$sth->bind_col(1, undef, { TYPE => SQL_DATETIME });
to specify that you'd like the column (which presumably is some
kind of datetime type) to be returned in the standard format for
SQL_DATETIME, which is 'YYYY-MM-DD HH:MM:SS', rather than the
native formatting the database would normally use.
There's no $var_to_bind in that example to emphasize the point
that bind_col() works on the underlying column value and not just
a particular bound variable.
As a short-cut for the common case, the data type can be passed
directly, in place of the C<\%attr> hash reference. This example is
equivalent to the one above:
$sth->bind_col(1, undef, SQL_DATETIME);
The C<TYPE> value indicates the standard (non-driver-specific) type for
this parameter. To specify the driver-specific type, the driver may
support a driver-specific attribute, such as C<{ ora_type =E<gt> 97 }>.
The SQL_DATETIME and other related constants can be imported using
use DBI qw(:sql_types);
See L</"DBI Constants"> for more information.
The data type for a bind variable cannot be changed after the first
C<bind_col> call. In fact the whole \%attr parameter is 'sticky'
in the sense that a driver only needs to consider the \%attr parameter
for the first call for a given $sth and column.
The TYPE attribute for bind_col() was first specified in DBI 1.41.
=item C<bind_columns>
$rc = $sth->bind_columns(@list_of_refs_to_vars_to_bind);
Calls L</bind_col> for each column of the C<SELECT> statement.
The C<bind_columns> method will die if the number of references does not
match the number of fields.
For maximum portability between drivers, bind_columns() should be called
after execute() and not before.
For example:
$dbh->{RaiseError} = 1; # do this, or check every call for errors
$sth = $dbh->prepare(q{ SELECT region, sales FROM sales_by_region });
$sth->execute;
my ($region, $sales);
# Bind Perl variables to columns:
$rv = $sth->bind_columns(\$region, \$sales);
# you can also use Perl's \(...) syntax (see perlref docs):
# $sth->bind_columns(\($region, $sales));
# Column binding is the most efficient way to fetch data
while ($sth->fetch) {
print "$region: $sales\n";
}
For compatibility with old scripts, the first parameter will be
ignored if it is C<undef> or a hash reference.
Here's a more fancy example that binds columns to the values I<inside>
a hash (thanks to H.Merijn Brand):
$sth->execute;
my %row;
$sth->bind_columns( \( @row{ @{$sth->{NAME_lc} } } ));
while ($sth->fetch) {
print "$row{region}: $row{sales}\n";
}
=item C<dump_results>
$rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);
Fetches all the rows from C<$sth>, calls C<DBI::neat_list> for each row, and
not supported by the driver.
See L</ShowErrorStatement> for an example of how this is used.
If the driver supports C<ParamValues> but no values have been bound
yet then the driver should return a hash with placeholders names
in the keys but all the values undef, but some drivers may return
a ref to an empty hash.
It is possible that the values in the hash returned by C<ParamValues>
are not I<exactly> the same as those passed to bind_param() or execute().
The driver may have slightly modified values in some way based on the
TYPE the value was bound with. For example a floating point value
bound as an SQL_INTEGER type may be returned as an integer.
The values returned by C<ParamValues> can be passed to another
bind_param() method with the same TYPE and will be seen by the
database as the same value.
It is also possible that the keys in the hash returned by C<ParamValues>
are not exactly the same as those implied by the prepared statement.
For example, DBD::Oracle translates 'C<?>' placeholders into 'C<:pN>'
where N is a sequence number starting at 1.
The C<ParamValues> attribute was added in DBI 1.28.
( run in 3.705 seconds using v1.01-cache-2.11-cpan-2398b32b56e )