Apache-LoggedAuthDBI
view release on metacpan or search on metacpan
# $Id: DBI.pm,v 11.43 2004/02/01 11:16:16 timbo Exp $
# vim: ts=8:sw=4
#
# Copyright (c) 1994-2004 Tim Bunce Ireland
#
# See COPYRIGHT section in pod text below for usage and distribution rights.
#
require 5.006_00;
BEGIN {
$DBI::VERSION = "1.48"; # ==> ALSO update the version in the pod text below!
}
=head1 NAME
DBI - Database independent interface for Perl
=head1 SYNOPSIS
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 );
$rv = $sth->rows;
$rc = $dbh->begin_work;
$rc = $dbh->commit;
$rc = $dbh->rollback;
$quoted_string = $dbh->quote($string);
$rc = $h->err;
$str = $h->errstr;
$rv = $h->state;
$rc = $dbh->disconnect;
I<The synopsis above only lists the major methods and parameters.>
=head2 GETTING HELP
If you have questions about DBI, or DBD driver modules, you can get
help from the I<dbi-users@perl.org> mailing list. You can get help
on subscribing and using the list by emailing I<dbi-users-help@perl.org>.
(To help you make the best use of the dbi-users mailing list,
and any other lists or forums you may use, I I<strongly>
recommend that you read "How To Ask Questions The Smart Way"
by Eric Raymond: L<http://www.catb.org/~esr/faqs/smart-questions.html>)
The DBI home page at L<http://dbi.perl.org/> is always worth a visit
and includes an FAQ and links to other resources.
Before asking any questions, reread this document, consult the
archives and read the DBI FAQ. The archives are listed
at the end of this document and on the DBI home page.
An FAQ is installed as a L<DBI::FAQ> module so
you can read it by executing C<perldoc DBI::FAQ>.
However the DBI::FAQ module is currently (2004) outdated relative
to the online FAQ on the DBI home page.
This document often uses terms like I<references>, I<objects>,
I<methods>. If you're not familar with those terms then it would
be a good idea to read at least the following perl manuals first:
L<perlreftut>, L<perldsc>, L<perllol>, and L<perlboot>.
Please note that Tim Bunce does not maintain the mailing lists or the
web page (generous volunteers do that). So please don't send mail
directly to him; he just doesn't have the time to answer questions
personally. The I<dbi-users> mailing list has lots of experienced
people who should be able to help you if you need it. If you do email
Tim he's very likely to just forward it to the mailing list.
tmplss_ => { class => 'DBD::TemplateSS', },
tuber_ => { class => 'DBD::Tuber', },
uni_ => { class => 'DBD::Unify', },
xbase_ => { class => 'DBD::XBase', },
xl_ => { class => 'DBD::Excel', },
yaswi_ => { class => 'DBD::Yaswi', },
};
sub dump_dbd_registry {
require Data::Dumper;
local $Data::Dumper::Sortkeys=1;
local $Data::Dumper::Indent=1;
print Data::Dumper->Dump([$dbd_prefix_registry], [qw($dbd_prefix_registry)]);
}
# --- Dynamically create the DBI Standard Interface
my $keeperr = { O=>0x0004 };
%DBI::DBI_methods = ( # Define the DBI interface methods per class:
common => { # Interface methods common to all DBI handle classes
'DESTROY' => $keeperr,
'CLEAR' => $keeperr,
'EXISTS' => $keeperr,
'FETCH' => { O=>0x0404 },
'FIRSTKEY' => $keeperr,
'NEXTKEY' => $keeperr,
'STORE' => { O=>0x0418 | 0x4 },
_not_impl => undef,
can => { O=>0x0100 }, # special case, see dispatch
debug => { U =>[1,2,'[$debug_level]'], O=>0x0004 }, # old name for trace
dump_handle => { U =>[1,3,'[$message [, $level]]'], O=>0x0004 },
err => $keeperr,
errstr => $keeperr,
state => $keeperr,
func => { O=>0x0006 },
parse_trace_flag => { U =>[2,2,'$name'], O=>0x0404, T=>8 },
parse_trace_flags => { U =>[2,2,'$flags'], O=>0x0404, T=>8 },
private_data => { U =>[1,1], O=>0x0004 },
set_err => { U =>[3,6,'$err, $errmsg [, $state, $method, $rv]'], O=>0x0010 },
trace => { U =>[1,3,'[$trace_level, [$filename]]'], O=>0x0004 },
trace_msg => { U =>[2,3,'$message_text [, $min_level ]' ], O=>0x0004, T=>8 },
swap_inner_handle => { U =>[2,3,'$h [, $allow_reparent ]'] },
},
dr => { # Database Driver Interface
'connect' => { U =>[1,5,'[$db [,$user [,$passwd [,\%attr]]]]'], H=>3 },
'connect_cached'=>{U=>[1,5,'[$db [,$user [,$passwd [,\%attr]]]]'], H=>3 },
'disconnect_all'=>{ U =>[1,1], O=>0x0800 },
data_sources => { U =>[1,2,'[\%attr]' ], O=>0x0800 },
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]]'] },
fetchall_hashref => { U =>[2,2,'$key_field'] },
blob_read => { U =>[4,5,'$field, $offset, $len [, \\$buf [, $bufoffset]]'] },
blob_copy_to_file => { U =>[3,3,'$field, $filename_or_handleref'] },
dump_results => { U =>[1,5,'$maxfieldlen, $linesep, $fieldsep, $filehandle'] },
more_results => { U =>[1,1] },
finish => { U =>[1,1] },
cancel => { U =>[1,1], O=>0x0800 },
rows => $keeperr,
_get_fbav => undef,
_set_fbav => { T=>6 },
},
);
while ( my ($class, $meths) = each %DBI::DBI_methods ) {
while ( my ($method, $info) = each %$meths ) {
my $fullmeth = "DBI::${class}::$method";
DBI->_install_method($fullmeth, 'DBI.pm', $info);
}
}
{
package DBI::common;
@DBI::dr::ISA = ('DBI::common');
@DBI::db::ISA = ('DBI::common');
@DBI::st::ISA = ('DBI::common');
}
# End of init code
END {
return unless defined &DBI::trace_msg; # return unless bootstrap'd ok
local ($!,$?);
DBI->trace_msg(" -- DBI::END\n", 2);
# Let drivers know why we are calling disconnect_all:
$DBI::PERL_ENDING = $DBI::PERL_ENDING = 1; # avoid typo warning
DBI->disconnect_all() if %DBI::installed_drh;
}
sub CLONE {
my $olddbis = $DBI::_dbistate;
_clone_dbis() unless $DBI::PurePerl; # clone the DBIS structure
DBI->trace_msg(sprintf "CLONE DBI for new thread %s\n",
$DBI::PurePerl ? "" : sprintf("(dbis %x -> %x)",$olddbis, $DBI::_dbistate));
while ( my ($driver, $drh) = each %DBI::installed_drh) {
no strict 'refs';
next if defined &{"DBD::${driver}::CLONE"};
next unless defined;
s/$quote/$quote$quote/g; # escape embedded quotes
$_ = qq{$quote$_$quote};
}
# strip out catalog if present for special handling
my $catalog = (@id >= 3) ? shift @id : undef;
# join the dots, ignoring any null/undef elements (ie schema)
my $quoted_id = join '.', grep { defined } @id;
if ($catalog) { # add catalog correctly
$quoted_id = ($info->[2] == 2) # SQL_CL_END
? $quoted_id . $info->[1] . $catalog
: $catalog . $info->[1] . $quoted_id;
}
return $quoted_id;
}
sub quote {
my ($dbh, $str, $data_type) = @_;
return "NULL" unless defined $str;
unless ($data_type) {
$str =~ s/'/''/g; # ISO SQL2
return "'$str'";
}
my $dbi_literal_quote_cache = $dbh->{'dbi_literal_quote_cache'} ||= [ {} , {} ];
my ($prefixes, $suffixes) = @$dbi_literal_quote_cache;
my $lp = $prefixes->{$data_type};
my $ls = $suffixes->{$data_type};
if ( ! defined $lp || ! defined $ls ) {
my $ti = $dbh->type_info($data_type);
$lp = $prefixes->{$data_type} = $ti ? $ti->{LITERAL_PREFIX} || "" : "'";
$ls = $suffixes->{$data_type} = $ti ? $ti->{LITERAL_SUFFIX} || "" : "'";
}
return $str unless $lp || $ls; # no quoting required
# XXX don't know what the standard says about escaping
# in the 'general case' (where $lp != "'").
# So we just do this and hope:
$str =~ s/$lp/$lp$lp/g
if $lp && $lp eq $ls && ($lp eq "'" || $lp eq '"');
return "$lp$str$ls";
}
sub rows { -1 } # here so $DBI::rows 'works' after using $dbh
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;
}
sub prepare_cached {
my ($dbh, $statement, $attr, $if_active) = @_;
# Needs support at dbh level to clear cache before complaining about
# active children. The XS template code does this. Drivers not using
# the template must handle clearing the cache themselves.
my $cache = $dbh->FETCH('CachedKids');
$dbh->STORE('CachedKids', $cache = {}) unless $cache;
my @attr_keys = ($attr) ? sort keys %$attr : ();
my $key = ($attr) ? join("~~", $statement, @attr_keys, @{$attr}{@attr_keys}) : $statement;
my $sth = $cache->{$key};
if ($sth) {
return $sth unless $sth->FETCH('Active');
Carp::carp("prepare_cached($statement) statement handle $sth still Active")
unless ($if_active ||= 0);
$sth->finish if $if_active <= 1;
return $sth if $if_active <= 2;
}
$sth = $dbh->prepare($statement, $attr);
$cache->{$key} = $sth if $sth;
return $sth;
}
sub ping {
shift->_not_impl('ping');
"0 but true"; # special kind of true 0
}
sub begin_work {
my $dbh = shift;
return $dbh->set_err(1, "Already in a transaction")
unless $dbh->FETCH('AutoCommit');
$dbh->STORE('AutoCommit', 0); # will croak if driver doesn't support it
$dbh->STORE('BegunWork', 1); # trigger post commit/rollback action
return 1;
}
sub primary_key {
my ($dbh, @args) = @_;
my $sth = $dbh->primary_key_info(@args) or return;
my ($row, @col);
push @col, $row->[3] while ($row = $sth->fetch);
Carp::croak("primary_key method not called in list context")
unless wantarray; # leave us some elbow room
return @col;
}
sub tables {
my ($dbh, @args) = @_;
my $sth = $dbh->table_info(@args[0,1,2,3,4]) or return;
sub type_info { # this should be sufficient for all drivers
my ($dbh, $data_type) = @_;
my $idx_hash;
my $tia = $dbh->{dbi_type_info_row_cache};
if ($tia) {
$idx_hash = $dbh->{dbi_type_info_idx_cache};
}
else {
my $temp = $dbh->type_info_all;
return unless $temp && @$temp;
# we cache here because type_info_all may be expensive to call
$tia = $dbh->{dbi_type_info_row_cache} = $temp;
$idx_hash = $dbh->{dbi_type_info_idx_cache} = shift @$tia;
}
my $dt_idx = $idx_hash->{DATA_TYPE} || $idx_hash->{data_type};
Carp::croak("type_info_all returned non-standard DATA_TYPE index value ($dt_idx != 1)")
if $dt_idx && $dt_idx != 1;
# --- simple DATA_TYPE match filter
my @ti;
my @data_type_list = (ref $data_type) ? @$data_type : ($data_type);
foreach $data_type (@data_type_list) {
if (defined($data_type) && $data_type != DBI::SQL_ALL_TYPES()) {
push @ti, grep { $_->[$dt_idx] == $data_type } @$tia;
}
else { # SQL_ALL_TYPES
push @ti, @$tia;
}
last if @ti; # found at least one match
}
# --- format results into list of hash refs
my $idx_fields = keys %$idx_hash;
my @idx_names = map { uc($_) } keys %$idx_hash;
my @idx_values = values %$idx_hash;
Carp::croak "type_info_all result has $idx_fields keys but ".(@{$ti[0]})." fields"
if @ti && @{$ti[0]} != $idx_fields;
my @out = map {
my %h; @h{@idx_names} = @{$_}[ @idx_values ]; \%h;
} @ti;
return $out[0] unless wantarray;
return @out;
}
sub data_sources {
my ($dbh, @other) = @_;
my $drh = $dbh->{Driver}; # XXX proxy issues?
return $drh->data_sources(@other);
}
}
{ 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")
if defined($NUM_OF_FIELDS) && defined($NUM_OF_PARAMS)
&& $NUM_OF_FIELDS != $NUM_OF_PARAMS;
$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) = @_;
# start with empty status array
($tuple_status) ? @$tuple_status = () : $tuple_status = [];
my ($err_count, %errstr_cache);
while ( my $tuple = &$fetch_tuple_sub() ) {
if ( my $rc = $sth->execute(@$tuple) ) {
push @$tuple_status, $rc;
}
else {
$err_count++;
my $err = $sth->err;
push @$tuple_status, [ $err, $errstr_cache{$err} ||= $sth->errstr, $sth->state ];
}
}
return ($err_count) ? undef : scalar(@$tuple_status)||"0E0";
}
sub fetchall_arrayref { # ALSO IN Driver.xst
my ($sth, $slice, $max_rows) = @_;
$max_rows = -1 unless defined $max_rows;
my $mode = ref($slice) || 'ARRAY';
my @rows;
my $row;
if ($mode eq 'ARRAY') {
# we copy the array here because fetch (currently) always
# returns the same array ref. XXX
if ($slice && @$slice) {
$max_rows = -1 unless defined $max_rows;
push @rows, [ @{$row}[ @$slice] ]
while($max_rows-- and $row = $sth->fetch);
}
elsif (defined $max_rows) {
$max_rows = -1 unless defined $max_rows;
push @rows, [ @$row ]
while($max_rows-- and $row = $sth->fetch);
}
else {
push @rows, [ @$row ] while($row = $sth->fetch);
}
}
elsif ($mode eq 'HASH') {
$max_rows = -1 unless defined $max_rows;
if (keys %$slice) {
my @o_keys = keys %$slice;
my @i_keys = map { lc } keys %$slice;
while ($max_rows-- and $row = $sth->fetchrow_hashref('NAME_lc')) {
my %hash;
@hash{@o_keys} = @{$row}{@i_keys};
push @rows, \%hash;
}
}
else {
# XXX assumes new ref each fetchhash
push @rows, $row
while ($max_rows-- and $row = $sth->fetchrow_hashref());
}
}
else { Carp::croak("fetchall_arrayref($mode) invalid") }
return \@rows;
}
sub fetchall_hashref {
my ($sth, $key_field) = @_;
my $hash_key_name = $sth->{FetchHashKeyName} || 'NAME';
my $names_hash = $sth->FETCH("${hash_key_name}_hash");
my @key_fields = (ref $key_field) ? @$key_field : ($key_field);
my @key_indexes;
my $num_of_fields = $sth->FETCH('NUM_OF_FIELDS');
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;
sub blob_copy_to_file { # returns length or undef on error
my($self, $field, $filename_or_handleref, $blocksize) = @_;
my $fh = $filename_or_handleref;
my($len, $buf) = (0, "");
$blocksize ||= 512; # not too ambitious
local(*FH);
unless(ref $fh) {
open(FH, ">$fh") || return undef;
$fh = \*FH;
}
while(defined($self->blob_read($field, $len, $blocksize, \$buf))) {
print $fh $buf;
$len += length $buf;
}
close(FH);
$len;
}
sub more_results {
shift->{syb_more_results}; # handy grandfathering
}
}
unless ($DBI::PurePerl) { # See install_driver
{ @DBD::_mem::dr::ISA = qw(DBD::_mem::common); }
{ @DBD::_mem::db::ISA = qw(DBD::_mem::common); }
{ @DBD::_mem::st::ISA = qw(DBD::_mem::common); }
# DBD::_mem::common::DESTROY is implemented in DBI.xs
}
1;
__END__
=head1 DESCRIPTION
The DBI is a database access module for the Perl programming language. It defines
a set of methods, variables, and conventions that provide a consistent
database interface, independent of the actual database being used.
It is important to remember that the DBI is just an interface.
The DBI is a layer
of "glue" between an application and one or more database I<driver>
modules. It is the driver modules which do most of the real work. The DBI
provides a standard interface and framework for the drivers to operate
within.
=head2 Architecture of a DBI Application
Private driver functions can be invoked using the DBI C<func()> method.
Private driver attributes are accessed just like standard attributes.
Many methods have an optional C<\%attr> parameter which can be used to
pass information to the driver implementing the method. Except where
specifically documented, the C<\%attr> parameter can only be used to pass
driver specific hints. In general, you can ignore C<\%attr> parameters
or pass it as C<undef>.
=head2 Naming Conventions and Name Space
The DBI package and all packages below it (C<DBI::*>) are reserved for
use by the DBI. Extensions and related modules use the C<DBIx::>
namespace (see L<http://www.perl.com/CPAN/modules/by-module/DBIx/>).
Package names beginning with C<DBD::> are reserved for use
by DBI database drivers. All environment variables used by the DBI
or by individual DBDs begin with "C<DBI_>" or "C<DBD_>".
The letter case used for attribute names is significant and plays an
important part in the portability of DBI scripts. The case of the
attribute name is used to signify who defined the meaning of that name
and its values.
Case of name Has a meaning defined by
------------ ------------------------
UPPER_CASE Standards, e.g., X/Open, ISO SQL92 etc (portable)
MixedCase DBI API (portable), underscores are not used.
lower_case Driver or database engine specific (non-portable)
It is of the utmost importance that Driver developers only use
lowercase attribute names when defining private attributes. Private
attribute names must be prefixed with the driver name or suitable
abbreviation (e.g., "C<ora_>" for Oracle, "C<ing_>" for Ingres, etc).
=head2 SQL - A Query Language
Most DBI drivers require applications to use a dialect of SQL
(Structured Query Language) to interact with the database engine.
The L</"Standards Reference Information"> section provides links
to useful information about SQL.
The DBI itself does not mandate or require any particular language to
be used; it is language independent. In ODBC terms, the DBI is in
"pass-thru" mode, although individual drivers might not be. The only requirement
is that queries and other statements must be expressed as a single
string of characters passed as the first argument to the L</prepare> or
L</do> methods.
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:
"SELECT name, age FROM ?" # wrong (will probably fail)
"SELECT name, ? FROM people" # wrong (but may not 'fail')
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("Joe Bloggs", undef);
$sth = $dbh->prepare(qq{
UPDATE people SET age = ? WHERE fullname = ?
});
$sth->execute(undef, "Joe Bloggs");
However, care must be taken when trying to use NULL values in a
C<WHERE> clause. Consider:
SELECT fullname FROM people WHERE age = ?
Binding an C<undef> (NULL) to the placeholder will I<not> select rows
which have a NULL C<age>! At least for database engines that
conform to the SQL standard. Refer to the SQL manual for your database
engine or any SQL book for the reasons for this. To explicitly select
NULLs you have to say "C<WHERE age IS NULL>".
A common issue is to have a code fragment handle a value that could be
either C<defined> or C<undef> (non-NULL or NULL) at runtime.
A simple technique is to prepare the appropriate statement as needed,
and substitute the placeholder for non-NULL cases:
$sql_clause = defined $age? "age = ?" : "age IS NULL";
$sth = $dbh->prepare(qq{
SELECT fullname FROM people WHERE $sql_clause
});
$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
below. But each example lacks portability, robustness, or simplicity.
Whether an example is supported on your database engine depends on
what SQL extensions it provides, and where it supports the C<?>
placeholder in a statement.
0) age = ?
1) NVL(age, xx) = NVL(?, xx)
2) ISNULL(age, xx) = ISNULL(?, xx)
3) DECODE(age, ?, 1, 0) = 1
4) age = ? OR (age IS NULL AND ? IS NULL)
5) age = ? OR (age IS NULL AND SP_ISNULL(?) = 1)
6) age = ? OR (age IS NULL AND ? = 1)
Statements formed with the above C<WHERE> clauses require execute
statements as follows. The arguments are required, whether their
values are C<defined> or C<undef>.
0,1,2,3) $sth->execute($age);
4,5) $sth->execute($age, $age);
6) $sth->execute($age, defined($age) ? 0 : 1);
Example 0 should not work (as mentioned earlier), but may work on
a few database engines anyway (e.g. Sybase). Example 0 is part
of examples 4, 5, and 6, so if example 0 works, these other
examples may work, even if the engine does not properly support
the right hand side of the C<OR> expression.
Examples 1 and 2 are not robust: they require that you provide a
valid column value xx (e.g. '~') which is not present in any row.
That means you must have some notion of what data won't be stored
in the column, and expect clients to adhere to that.
Example 5 requires that you provide a stored procedure (SP_ISNULL
in this example) that acts as a function: it checks whether a value
is null, and returns 1 if it is, or 0 if not.
Example 6, the least simple, is probably the most portable, i.e., it
should work with with most, if not all, database engines.
Here is a table that indicates which examples above are known to
work on various database engines:
-----Examples------
0 1 2 3 4 5 6
- - - - - - -
Oracle 9 N Y N Y Y ? Y
Informix IDS 9 N N N Y N Y Y
MS SQL N N Y N Y ? Y
Sybase Y N N N N N Y
AnyData,DBM,CSV Y N N N Y Y* Y
* Works only because Example 0 works.
DBI provides a sample perl script that will test the examples above
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
imported individually by name, or all together by importing the
special C<:sql_types> tag.
The names and values of all the defined 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:
=over 4
=item C<parse_dsn>
($scheme, $driver, $attr_string, $attr_hash, $driver_dsn) = DBI->parse_dsn($dsn)
or die "Can't parse DBI DSN '$dsn'";
Breaks apart a DBI Data Source Name (DSN) and returns the individual
parts. If $dsn doesn't contain a valid DSN then parse_dsn() returns
an empty list.
$scheme is the first part of the DSN and is currently always 'dbi'.
$driver is the driver name, possibly defaulted to $ENV{DBI_DRIVER},
and may be undefined. $attr_string is the optional attribute string,
which may be undefined. If $attr_string is true then $attr_hash
is a reference to a hash containing the parsed attribute names and
values. $driver_dsn is the last part of the DBI DSN string.
The parse_dsn() method was added in DBI 1.43.
=item C<connect>
$dbh = DBI->connect($data_source, $username, $password)
or die $DBI::errstr;
$dbh = DBI->connect($data_source, $username, $password, \%attr)
or die $DBI::errstr;
Establishes a database connection, or session, to the requested C<$data_source>.
Returns a database handle object if the connection succeeds. Use
C<$dbh-E<gt>disconnect> to terminate the connection.
If the connect fails (see below), it returns C<undef> and sets both C<$DBI::err>
and C<$DBI::errstr>. (It does I<not> explicitly set C<$!>.) You should generally
test the return status of C<connect> and C<print $DBI::errstr> if it has failed.
Multiple simultaneous connections to multiple databases through multiple
drivers can be made via the DBI. Simply make one C<connect> call for each
database and keep a copy of each returned database handle.
The C<$data_source> value must begin with "C<dbi:>I<driver_name>C<:>".
The I<driver_name> specifies the driver that will be used to make the
connection. (Letter case is significant.)
As a convenience, if the C<$data_source> parameter is undefined or empty,
the DBI will substitute the value of the environment variable C<DBI_DSN>.
If just the I<driver_name> part is empty (i.e., the C<$data_source>
prefix is "C<dbi::>"), the environment variable C<DBI_DRIVER> is
used. If neither variable is set, then C<connect> dies.
Examples of C<$data_source> values are:
=head1 DBI DATABASE HANDLE OBJECTS
This section covers the methods and attributes associated with
database handles.
=head2 Database Handle Methods
The following methods are specified for DBI database handles:
=over 4
=item C<clone>
$new_dbh = $dbh->clone();
$new_dbh = $dbh->clone(\%attr);
The C<clone> method duplicates the $dbh connection by connecting
with the same parameters ($dsn, $user, $password) as originally used.
The attributes for the cloned connect are the same as those used
for the original connect, with some other attribute merged over
them depending on the \%attr parameter.
If \%attr is given then the attributes it contains are merged into
the original attributes and override any with the same names.
Effectively the same as doing:
%attribues_used = ( %original_attributes, %attr );
If \%attr is not given then it defaults to a hash containing all
the attributes in the attribute cache of $dbh excluding any non-code
references, plus the main boolean attributes (RaiseError, PrintError,
AutoCommit, etc.). This behaviour is subject to change.
The clone method can be used even if the database handle is disconnected.
The C<clone> method was added in DBI 1.33. It is very new and likely
to change.
=item C<data_sources>
@ary = $dbh->data_sources();
@ary = $dbh->data_sources(\%attr);
Returns a list of data sources (databases) available via the $dbh
driver's data_sources() method, plus any extra data sources that
the driver can discover via the connected $dbh. Typically the extra
data sources are other databases managed by the same server process
that the $dbh is connected to.
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.
=item C<last_insert_id>
$rv = $dbh->last_insert_id($catalog, $schema, $table, $field);
$rv = $dbh->last_insert_id($catalog, $schema, $table, $field, \%attr);
Returns a value 'identifying' the row just inserted, if possible.
Typically this would be a value assigned by the database server
to a column with an I<auto_increment> or I<serial> type.
Returns undef if the driver does not support the method or can't
determine the value.
The $catalog, $schema, $table, and $field parameters may be required
for some drivers (see below). If you don't know the parameter values
and your driver does not need them, then use C<undef> for each.
There are several caveats to be aware of with this method if you want
to use it for portable applications:
B<*> For some drivers the value may only available immediately after
the insert statement has executed (e.g., mysql, Informix).
B<*> For some drivers the $catalog, $schema, $table, and $field parameters
are required (e.g., Pg), for others they are ignored (e.g., mysql).
B<*> Drivers may return an indeterminate value if no insert has
been performed yet.
B<*> For some drivers the value may only be available if placeholders
have I<not> been used (e.g., Sybase, MS SQL). In this case the value
returned would be from the last non-placeholder insert statement.
B<*> Some drivers may need driver-specific hints about how to get
the value. For example, being told the name of the database 'sequence'
object that holds the value. Any such hints are passed as driver-specific
attributes in the \%attr parameter.
B<*> If the underlying database offers nothing better, then some
drivers may attempt to implement this method by executing
"C<select max($field) from $table>". Drivers using any approach
like this should issue a warning if C<AutoCommit> is true because
it is generally unsafe - another process may have modified the table
between your insert and the select. For situations where you know
it is safe, such as when you have locked the table, you can silence
the warning by passing C<Warn> => 0 in \%attr.
B<*> If no insert has been performed yet, or the last insert failed,
then the value is implementation defined.
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.
If called in a scalar context for a statement handle that has more
than one column, it is undefined whether the driver will return
the value of the first column or the last. So don't do that.
Also, in a scalar context, an C<undef> is returned if there are no
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>
fails then C<selectall_arrayref> will return C<undef>; if
C<fetchall_arrayref> fails then it will return with whatever data
has been fetched thus far. You should check C<$sth-E<gt>err>
afterwards (or use the C<RaiseError> attribute) to discover if the data is
complete or was truncated due to an error.
The L</fetchall_arrayref> method called by C<selectall_arrayref>
supports a $max_rows parameter. You can specify a value for $max_rows
by including a 'C<MaxRows>' attribute in \%attr. In which case finish()
is called for you after fetchall_arrayref() returns.
The L</fetchall_arrayref> method called by C<selectall_arrayref>
also supports a $slice parameter. You can specify a value for $slice by
including a 'C<Slice>' or 'C<Columns>' attribute in \%attr. The only
difference between the two is that if C<Slice> is not defined and
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.
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<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,
C<selectcol_arrayref> will return C<undef>. If C<fetch> 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.
The C<selectcol_arrayref> method defaults to pushing a single column
value (the first) from each row into the result array. However, it can
also push another column, or even multiple columns per row, into the
result array. This behaviour can be specified via a 'C<Columns>'
attribute which must be a ref to an array containing the column number
or numbers to use. For example:
# get array of id and name pairs:
my $ary_ref = $dbh->selectcol_arrayref("select id, name from table", { Columns=>[1,2] });
my %hash = @$ary_ref; # build hash from key-value pairs so $hash{$id} => name
You can specify a maximum number of rows to fetch by including a
'C<MaxRows>' attribute in \%attr.
=item C<prepare>
$sth = $dbh->prepare($statement) or die $dbh->errstr;
$sth = $dbh->prepare($statement, \%attr) or die $dbh->errstr;
Prepares a statement for later execution by the database
engine and returns a reference to a statement handle object.
The returned statement handle can be used to get attributes of the
statement and invoke the L</execute> method. See L</Statement Handle Methods>.
Drivers for engines without the concept of preparing a
statement will typically just store the statement in the returned
handle and process it when C<$sth-E<gt>execute> is called. Such drivers are
unlikely to give much useful information about the
statement, such as C<$sth-E<gt>{NUM_OF_FIELDS}>, until after C<$sth-E<gt>execute>
has been called. Portable applications should take this into account.
In general, DBI drivers do not parse the contents of the statement
(other than simply counting any L</Placeholders>). The statement is
passed directly to the database engine, sometimes known as pass-thru
mode. This has advantages and disadvantages. On the plus side, you can
access all the functionality of the engine being used. On the downside,
you're limited if you're using a simple engine, and you need to take extra care if
writing applications intended to be portable between engines.
Portable applications should not assume that a new statement can be
prepared and/or executed while still fetching results from a previous
statement.
Some command-line SQL tools use statement terminators, like a semicolon,
to indicate the end of a statement. Such terminators should not normally
is to find the name of the driver using:
$dbh->{Driver}->{Name}
=item C<Name> (string)
Holds the "name" of the database. Usually (and recommended to be) the
same as the "C<dbi:DriverName:...>" string used to connect to the database,
but with the leading "C<dbi:DriverName:>" removed.
=item C<Statement> (string, read-only)
Returns the statement string passed to the most recent L</prepare> method
called in this database handle, even if that method failed. This is especially
useful where C<RaiseError> is enabled and the exception handler checks $@
and sees that a 'prepare' method call failed.
=item C<RowCacheSize> (integer)
A hint to the driver indicating the size of the local row cache that the
application would like the driver to use for future C<SELECT> statements.
If a row cache is not implemented, then setting C<RowCacheSize> is ignored
and getting the value returns C<undef>.
Some C<RowCacheSize> values have special meaning, as follows:
0 - Automatically determine a reasonable cache size for each C<SELECT>
1 - Disable the local row cache
>1 - Cache this many rows
<0 - Cache as many rows that will fit into this much memory for each C<SELECT>.
Note that large cache sizes may require a very large amount of memory
(I<cached rows * maximum size of row>). Also, a large cache will cause
a longer delay not only for the first fetch, but also whenever the
cache needs refilling.
See also the L</RowsInCache> statement handle attribute.
=item C<Username> (string)
Returns the username used to connect to the database.
=back
=head1 DBI STATEMENT HANDLE OBJECTS
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
"C<0E0>", which Perl will treat as 0 but will regard as true. Note that it
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.
If B<any> tuple execution returns an error, C<execute_array> will
return C<undef>. In that case, the application should inspect the
status array to determine which parameter tuples failed.
Some databases may not continue executing tuples beyond the first
failure. In this case the status array will either hold fewer
elements, or the elements beyond the failure will be undef.
If all parameter tuples are successfully executed, C<execute_array>
returns the number tuples executed. If no tuples were executed,
then execute_array() returns "C<0E0>", just like execute() does,
which Perl will treat as 0 but will regard as true.
For example:
$sth = $dbh->prepare("INSERT INTO staff (first_name, last_name) VALUES (?, ?)");
my $tuples = $sth->execute_array(
{ ArrayTupleStatus => \my @tuple_status },
\@first_names,
\@last_names,
);
if ($tuples) {
print "Successfully inserted $tuples records\n";
}
else {
for my $tuple (0..@last_names-1) {
my $status = $tuple_status[$tuple];
$status = [0, "Skipped"] unless defined $status;
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
tuples. Be sure to check your driver and database for its specific
behaviour.
Note that, in general, performance will usually be better with
C<AutoCommit> turned off, and using explicit C<commit> after each
C<execute_array> call.
The C<execute_array> method was added in DBI 1.22, and ArrayTupleFetch
was added in 1.36.
=item C<execute_for_fetch>
$rc = $sth->execute_for_fetch($fetch_tuple_sub);
$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
the element holds the return value from execute(), which is typically
a row count. If the execute() did fail then the element holds a
reference to an array containing ($sth->err, $sth->errstr, $sth->state).
Although each tuple returned by $fetch_tuple_sub is effectively used
to call $sth->execute(@$tuple_array_ref) the exact timing may vary.
Drivers are free to accumulate sets of tuples to pass to the
database server in bulk group operations for more efficient execution.
However, the $fetch_tuple_sub is specifically allowed to return
the same array reference each time (which is what fetchrow_arrayref()
usually does).
For example:
my $sel = $dbh1->prepare("select foo, bar from table1");
$sel->execute;
my $ins = $dbh2->prepare("insert into table2 (foo, bar) values (?,?)");
my $fetch_tuple_sub = sub { $sel->fetchrow_arrayref };
my @tuple_status;
$rc = $ins->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
my @errors = grep { ref $_ } @tuple_status;
Similarly, if you already have an array containing the data rows
to be processed you'd use a subroutine to shift off and return
each array ref in turn:
$ins->execute_for_fetch( sub { shift @array_of_arrays }, \@tuple_status);
The C<execute_for_fetch> method was added in DBI 1.38.
=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>
returns an empty list. You should check C<$sth-E<gt>err> afterwards (or use
the C<RaiseError> attribute) to discover if the empty list returned was
due to an error.
If called in a scalar context for a statement handle that has more
than one column, it is undefined whether the driver will return
the value of the first column or the last. So don't do that.
Also, in a scalar context, an C<undef> is returned if there are no
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<fetchrow_array> in a scalar context.
=item C<fetchrow_hashref>
$hash_ref = $sth->fetchrow_hashref;
$hash_ref = $sth->fetchrow_hashref($name);
An alternative to C<fetchrow_arrayref>. Fetches the next row of data
and returns it as a reference to a hash containing field name and field
value pairs. Null fields are returned as C<undef> values in the hash.
If there are no more rows or if an error occurs, then C<fetchrow_hashref>
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.
The optional C<$name> parameter specifies the name of the statement handle
attribute. For historical reasons it defaults to "C<NAME>", however using either
"C<NAME_lc>" or "C<NAME_uc>" is recomended for portability.
The keys of the hash are the same names returned by C<$sth-E<gt>{$name}>. If
more than one field has the same name, there will only be one entry in
the returned hash for those fields.
Because of the extra work C<fetchrow_hashref> and Perl have to perform, it
is not as efficient as C<fetchrow_arrayref> or C<fetchrow_array>.
By default a reference to a new hash is returned for each row.
It is likely that a future version of the DBI will support an
attribute which will enable the same hash to be reused for each
row. This will give a significant performance boost, but it won't
be enabled by default because of the risk of breaking old code.
=item C<fetchall_arrayref>
$tbl_ary_ref = $sth->fetchall_arrayref;
$tbl_ary_ref = $sth->fetchall_arrayref( $slice );
$tbl_ary_ref = $sth->fetchall_arrayref( $slice, $max_rows );
data fetched thus far, which may be none. You should check C<$sth-E<gt>err>
afterwards (or use the C<RaiseError> attribute) to discover if the data is
complete or was truncated due to an error.
If $slice is an array reference, C<fetchall_arrayref> uses L</fetchrow_arrayref>
to fetch each row as an array ref. If the $slice array is not empty
then it is used as a slice to select individual columns by perl array
index number (starting at 0, unlike column and parameter numbers which
start at 1).
With no parameters, or if $slice is undefined, C<fetchall_arrayref>
acts as if passed an empty array ref.
If $slice is a hash reference, C<fetchall_arrayref> uses L</fetchrow_hashref>
to fetch each row as a hash reference. If the $slice hash is empty then
fetchrow_hashref() is simply called in a tight loop and the keys in the hashes
have whatever name lettercase is returned by default from fetchrow_hashref.
(See L</FetchHashKeyName> attribute.) If the $slice hash is not
empty, then it is used as a slice to select individual columns by
name. The values of the hash should be set to 1. The key names
of the returned hashes match the letter case of the names in the
parameter hash, regardless of the L</FetchHashKeyName> attribute.
For example, to fetch just the first column of every row:
$tbl_ary_ref = $sth->fetchall_arrayref([0]);
To fetch the second to last and last column of every row:
$tbl_ary_ref = $sth->fetchall_arrayref([-2,-1]);
To fetch all fields of every row as a hash ref:
$tbl_ary_ref = $sth->fetchall_arrayref({});
To fetch only the fields called "foo" and "bar" of every row as a hash ref
(with keys named "foo" and "BAR"):
$tbl_ary_ref = $sth->fetchall_arrayref({ foo=>1, BAR=>1 });
The first two examples return a reference to an array of array refs.
The third and forth return a reference to an array of hash refs.
If $max_rows is defined and greater than or equal to zero then it
is used to limit the number of rows fetched before returning.
fetchall_arrayref() can then be called again to fetch more rows.
This is especially useful when you need the better performance of
fetchall_arrayref() but don't have enough memory to fetch and return
all the rows in one go. Here's an example:
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);
The C<fetchall_hashref> method can be used to fetch all the data to be
returned from a prepared and executed statement handle. It returns a reference
to a hash containing a key for each distinct value of the $key_field column
that was fetched For each key the corresponding value is a reference to a hash
containing all the selected columns and their values, as returned by fetchrow_hashref().
If there are no rows to return, C<fetchall_hashref> returns a reference
to an empty hash. If an error occurs, C<fetchall_hashref> returns the
data fetched thus far, which may be none. You should check
C<$sth-E<gt>err> afterwards (or use the C<RaiseError> attribute) to
discover if the data is complete or was truncated due to an error.
The $key_field parameter provides the name of the field that holds the
value to be used for the key for the returned hash. For example:
$dbh->{FetchHashKeyName} = 'NAME_lc';
$sth = $dbh->prepare("SELECT FOO, BAR, ID, NAME, BAZ FROM TABLE");
$sth->execute;
$hash_ref = $sth->fetchall_hashref('id');
print "Name for id 42 is $hash_ref->{42}->{name}\n";
The $key_field parameter can also be specified as an integer column
number (counting from 1). If $key_field doesn't match any column in
the statement, as a name first then as a number, then an error is
returned.
For queries returing more than one 'key' column, you can specify
multiple column names by passing $key_field as a reference to an
array containing one or more key column names (or index numbers).
For example:
$sth = $dbh->prepare("SELECT foo, bar, baz FROM table");
$sth->execute;
$hash_ref = $sth->fetchall_hashref( [ qw(foo bar) ] );
print "For foo 42 and bar 38, baz is $hash_ref->{42}->{38}->{baz}\n";
The fetchall_hashref() method is normally used only where the key
fields values for each row are unique. If multiple rows are returned
with the same values for the key fields then later rows overwrite
earlier ones.
=item C<finish>
$rc = $sth->finish;
Indicate that no more data will be fetched from this statement handle
before it is either executed again or destroyed. The C<finish> method
is rarely needed, and frequently overused, but can sometimes be
helpful in a few very specific situations to allow the server to free
up resources (such as sort buffers).
When all the data has been fetched from a C<SELECT> statement, the
driver should automatically call C<finish> for you. So you should
I<not> normally need to call it explicitly I<except> when you know
that you've not fetched all the data from a statement handle.
The most common example is when you only want to fetch one row,
but in that case the C<selectrow_*> methods are usually better anyway.
Adding calls to C<finish> after each fetch loop is a common mistake,
don't do it, it can mask genuine problems like uncaught fetch errors.
Consider a query like:
SELECT foo FROM table WHERE bar=? ORDER BY foo
where you want to select just the first (smallest) "foo" value from a
very large table. When executed, the database server will have to use
temporary buffer space to store the sorted rows. If, after executing
the handle and selecting one row, the handle won't be re-executed for
some time and won't be destroyed, the C<finish> method can be used to tell
the server that the buffer space can be freed.
Calling C<finish> resets the L</Active> attribute for the statement. It
may also make some statement handle attributes (such as C<NAME> and C<TYPE>)
unavailable if they have not already been accessed (and thus cached).
The C<finish> method does not affect the transaction status of the
database connection. It has nothing to do with transactions. It's mostly an
internal "housekeeping" method that is rarely needed.
See also L</disconnect> and the L</Active> attribute.
The C<finish> method should have been called C<discard_pending_rows>.
=item C<rows>
$rv = $sth->rows;
Returns the number of rows affected by the last row affecting command,
or -1 if the number of rows is not known or not available.
Generally, you can only rely on a row count after a I<non>-C<SELECT>
C<execute> (for some specific operations like C<UPDATE> and C<DELETE>), or
after fetching all the rows of a C<SELECT> statement.
For C<SELECT> statements, it is generally not possible to know how many
rows will be returned except by fetching them all. Some drivers will
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
prints the results to C<$fh> (defaults to C<STDOUT>) separated by C<$lsep>
(default C<"\n">). C<$fsep> defaults to C<", "> and C<$maxlen> defaults to 35.
This method is designed as a handy utility for prototyping and
testing queries. Since it uses L</neat_list> to
format and edit the string for reading by humans, it is not recomended
for data transfer applications.
=back
=head2 Statement Handle Attributes
This section describes attributes specific to statement handles. Most
of these attributes are read-only.
Changes to these statement handle attributes do not affect any other
existing or future statement handles.
Attempting to set or get the value of an unknown attribute generates a warning,
except for private driver specific attributes (which all have names
starting with a lowercase letter).
Example:
... = $h->{NUM_OF_FIELDS}; # get/read
Some drivers cannot provide valid values for some or all of these
attributes until after C<$sth-E<gt>execute> has been successfully
called. Typically the attribute will be C<undef> in these situations.
Some attributes, like NAME, are not appropriate to some types of
statement, like SELECT. Typically the attribute will be C<undef>
in these situations.
See also L</finish> to learn more about the effect it
may have on some attributes.
=over 4
=item C<NUM_OF_FIELDS> (integer, read-only)
Number of fields (columns) in the data the prepared statement may return.
Statements that don't return rows of data, like C<DELETE> and C<CREATE>
set C<NUM_OF_FIELDS> to 0.
=item C<NUM_OF_PARAMS> (integer, read-only)
The number of parameters (placeholders) in the prepared statement.
All possible values for C<TYPE> should have at least one entry in the
output of the C<type_info_all> method (see L</type_info_all>).
=item C<PRECISION> (array-ref, read-only)
Returns a reference to an array of integer values for each column.
For numeric columns, the value is the maximum number of digits
(without considering a sign character or decimal point). Note that
the "display size" for floating point types (REAL, FLOAT, DOUBLE)
can be up to 7 characters greater than the precision (for the
sign + decimal point + the letter E + a sign + 2 or 3 digits).
For any character type column the value is the OCTET_LENGTH,
in other words the number of bytes, not characters.
(More recent standards refer to this as COLUMN_SIZE but we stick
with PRECISION for backwards compatibility.)
=item C<SCALE> (array-ref, read-only)
Returns a reference to an array of integer values for each column.
NULL (C<undef>) values indicate columns where scale is not applicable.
=item C<NULLABLE> (array-ref, read-only)
Returns a reference to an array indicating the possibility of each
column returning a null. Possible values are C<0>
(or an empty string) = no, C<1> = yes, C<2> = unknown.
print "First column may return NULL\n" if $sth->{NULLABLE}->[0];
=item C<CursorName> (string, read-only)
Returns the name of the cursor associated with the statement handle, if
available. If not available or if the database driver does not support the
C<"where current of ..."> SQL syntax, then it returns C<undef>.
=item C<Database> (dbh, read-only)
Returns the parent $dbh of the statement handle.
=item C<ParamValues> (hash ref, read-only)
Returns a reference to a hash containing the values currently bound
to placeholders. The keys of the hash are the 'names' of the
placeholders, typically integers starting at 1. Returns undef if
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.
=item C<Statement> (string, read-only)
Returns the statement string passed to the L</prepare> method.
=item C<RowsInCache> (integer, read-only)
If the driver supports a local row cache for C<SELECT> statements, then
this attribute holds the number of un-fetched rows in the cache. If the
driver doesn't, then it returns C<undef>. Note that some drivers pre-fetch
rows on execute, whereas others wait till the first fetch.
See also the L</RowCacheSize> database handle attribute.
=back
=head1 OTHER METHODS
=over 4
=item C<install_method>
DBD::Foo::db->install_method($method_name, \%attr);
Installs the driver-private method named by $method_name into the
DBI method dispatcher so it can be called directly, avoiding the
need to use the func() method.
It is called as a static method on the driver class to which the
method belongs. The method name must begin with the corresponding
registered driver-private prefix. For example, for DBD::Oracle
$method_name must being with 'C<ora_>', and for DBD::AnyData it
must begin with 'C<ad_>'.
The attributes can be used to provide fine control over how the DBI
dispatcher handles the dispatching of the method. However, at this
point, it's undocumented and very liable to change. (Volunteers to
polish up and document the interface are very welcome to get in
touch via dbi-dev@perl.org)
Methods installed using install_method default to the standard error
handling behaviour for DBI methods: clearing err and errstr before
calling the method, and checking for errors to trigger RaiseError
etc. on return. This differs from the default behaviour of func().
Note for driver authors: The DBD::Foo::xx->install_method call won't
work until the class-hierarchy has been setup. Normally the DBI
looks after that just after the driver is loaded. This means
install_method() can't be called at the time the driver is loaded
unless the class-hierarchy is set up first. The way to do that is
( run in 0.718 second using v1.01-cache-2.11-cpan-2398b32b56e )