发信人: tanshuai()
整理人: workingnow(2002-10-30 13:59:15), 站内信件
|
有不少人需要DBI.pm可是找不到,小弟就将他放在这里,供大家使用!
# $Id: DBI.pm,v 10.4 1998/09/03 21:56:42 timbo Exp $
#
# Copyright (c) 1994,1995,1996,1997,1998 Tim Bunce England
#
# See COPYRIGHT section in pod text below for usage and distribution r
ights.
#
require 5.003;
BEGIN {
$DBI::VERSION = '1.02'; # ==> 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);
$dbh = DBI->connect($data_source, $username, $auth);
$dbh = DBI->connect($data_source, $username, $auth, \%attr);
$rc = $dbh->disconnect;
$rv = $dbh->do($statement);
$rv = $dbh->do($statement, \%attr);
$rv = $dbh->do($statement, \%attr, @bind_values);
@row_ary = $dbh->selectrow_array($statement);
$ary_ref = $dbh->selectall_arrayref($statement);
$sth = $dbh->prepare($statement);
$sth = $dbh->prepare_cached($statement);
$rv = $sth->bind_param($p_num, $bind_value);
$rv = $sth->bind_param($p_num, $bind_value, $bind_type);
$rv = $sth->bind_param($p_num, $bind_value, \%attr);
$rv = $sth->execute;
$rv = $sth->execute(@bind_values);
$rc = $sth->bind_col($col_num, \$col_variable);
$rc = $sth->bind_columns(\%attr, @list_of_refs_to_vars_to_bind);
@row_ary = $sth->fetchrow_array;
$ary_ref = $sth->fetchrow_arrayref;
$hash_ref = $sth->fetchrow_hashref;
$rc = $sth->finish;
$rv = $sth->rows;
$rc = $dbh->commit;
$rc = $dbh->rollback;
$sql = $dbh->quote($string);
$rc = $h->err;
$str = $h->errstr;
$rv = $h->state;
=head2 NOTE
This is the DBI specification that corresponds to the DBI version 1.02
($Date: 1998/09/03 21:56:42 $).
The DBI specification is currently evolving quite quickly so it is
important to check that you have the latest copy. The RECENT CHANGES
section below has a summary of user-visible changes and the F<Changes>
file supplied with the DBI holds more detailed change information.
Note also that whenever the DBI changes the drivers take some time to
catch up. Recent versions of the DBI have added many new features
(marked *NEW* in the text) that may not yet be supported by the driver
s
you use. Talk to the authors of those drivers if you need the features
.
Please also read the DBI FAQ which is installed as a DBI::FAQ module s
o
you can use perldoc to read it by executing the C<perldoc DBI::FAQ> co
mmand.
=head2 RECENT CHANGES
A brief summary of significant user-visible changes in recent versions
(if a recent version isn't mentioned it simply means that there were n
o
significant user-visible changes in that version).
=over 4
=item DBI 1.00 - 14th August 1998
Added $dbh->table_info.
=item DBI 0.96 - 10th August 1998
Added $sth->{PRECISION} and $sth->{SCALE}.
Added DBD::Shell and dbish interactive DBI shell.
Any database attribs can be set via DBI->connect(,,, \%attr).
Added _get_fbav and _set_fbav methods for Perl driver developers.
DBI trace now shows appends " at yourfile.pl line nnn".
PrintError and RaiseError now prepend driver and method name.
Added $dbh->{Name}.
Added $dbh->quote($value, $data_type).
Added DBD::Proxy and DBI::ProxyServer (from Jochen Wiedmann).
Added $dbh->selectall_arrayref and $dbh->selectrow_array methods.
Added $dbh->table_info.
Added $dbh->type_info and $dbh->type_info_all.
Added $h->trace_msg($msg) to write to trace log.
Added @bool = DBI::looks_like_number(@ary).
=item DBI 0.92 - 4th February 1998
Added $dbh->prepare_cached() caching variant of $dbh->prepare.
Added new attributes: Active, Kids, ActiveKids, CachedKids.
Added support for general-purpose 'private_' attributes.
=back
=cut
# The POD text continues at the end of the file.
# DBI file-private variables
my %installed_rootclass;
{
package DBI;
my $Revision = substr(q$Revision: 10.4 $, 10);
use Carp;
use DynaLoader ();
use Exporter ();
BEGIN {
@ISA = qw(Exporter DynaLoader);
# Make some utility functions available if asked for
@EXPORT = (); # we export nothing by default
@EXPORT_OK = ('%DBI'); # populated by export_ok_tags:
%EXPORT_TAGS = (
sql_types => [ qw(
SQL_ALL_TYPES
SQL_CHAR SQL_NUMERIC SQL_DECIMAL SQL_INTEGER SQL_SMALLINT
SQL_FLOAT SQL_REAL SQL_DOUBLE SQL_VARCHAR
SQL_DATE SQL_TIME SQL_TIMESTAMP
SQL_LONGVARCHAR SQL_BINARY SQL_VARBINARY SQL_LONGVARBINARY
SQL_BIGINT SQL_TINYINT
) ],
utils => [ qw(
neat neat_list dump_results looks_like_number
) ],
);
Exporter::export_ok_tags('sql_types', 'utils');
$DBI::dbi_debug = $ENV{DBI_TRACE} || $ENV{PERL_DBI_DEBUG} || 0;
bootstrap DBI;
}
use strict;
my $connect_via = "connect";
# check if user wants a persistent database connection ( Apache + mod_
perl )
my $GATEWAY_INTERFACE = $ENV{GATEWAY_INTERFACE} || '';
if (substr($GATEWAY_INTERFACE,0,8) eq 'CGI-Perl' and $INC{'Apache/DBI.
pm'}) {
$connect_via = "Apache::DBI::connect";
DBI->trace_msg("DBI connect via $INC{'Apache/DBI.pm'}\n") if $DBI:
:dbi_debug;
}
if ($DBI::dbi_debug) {
# this is a bit of a handy hack for "DBI_TRACE=/tmp/dbi.log"
if ($DBI::dbi_debug =~ m/^\d/) {
# dbi_debug is number so debug to stderr at that level
DBI->trace($DBI::dbi_debug);
}
else {
# dbi_debug is a file name to debug to file at level 2
# the function will reset $dbi_debug to the value 2.
DBI->trace(2, $DBI::dbi_debug);
}
}
%DBI::installed_drh = (); # maps driver names to installed driver han
dles
# Setup special DBI dynamic variables. See DBI::var::FETCH for details
.
# These are dynamically associated with the last handle used.
tie $DBI::err, 'DBI::var', '*err'; # special case: referenced vi
a IHA list
tie $DBI::state, 'DBI::var', '"state'; # special case: referenced vi
a IHA list
tie $DBI::lasth, 'DBI::var', '!lasth'; # special case: return boolea
n
tie $DBI::errstr, 'DBI::var', '&errstr'; # call &errstr in last used p
kg
tie $DBI::rows, 'DBI::var', '&rows'; # call &rows in last used p
kg
sub DBI::var::TIESCALAR{ my $var = $_[1]; bless \$var, 'DBI::var'; }
sub DBI::var::STORE { Carp::croak("Can't modify \$DBI::${$_[0]} spe
cial variable") }
sub DBI::var::DESTROY { }
{ package DBI::DBI_tie; # used to catch DBI->{Attrib} mistake
sub TIEHASH { bless {} }
sub STORE { Carp::carp("DBI->{$_[1]} is invalid syntax (you prob
ably want \$h->{$_[1]})");}
*FETCH = \&STORE;
}
tie %DBI::DBI => 'DBI::DBI_tie';
# --- Dynamically create the DBI Standard Interface
my $std = undef;
my $keeperr = { O=>0x04 };
my @TieHash_IF = ( # Generic Tied Hash Interface
'STORE' => $std,
'FETCH' => $keeperr,
'FIRSTKEY'=> $keeperr,
'NEXTKEY' => $keeperr,
'EXISTS' => $keeperr,
'CLEAR' => $keeperr,
'DESTROY' => undef, # hardwired internally
);
my @Common_IF = ( # Interface functions common to all DBI classes
func => { O=>0x06 },
event => { U =>[2,0,'$type, @args'], O=>0x04 },
'trace' => { U =>[1,3,'[$trace_level, [$filename]]'], O=>0x04 },
trace_msg => { U =>[2,2,'$message_text'], O=>0x04 },
debug => { U =>[1,2,'[$debug_level]'], O=>0x04 }, # old name for tr
ace
private_data => { U =>[1,1], O=>0x04 },
err => $keeperr,
errstr => $keeperr,
state => { U =>[1,1], O=>0x04 },
_not_impl => $std,
);
my %DBI_IF = ( # Define the DBI Interface:
dr => { # Database Driver Interface
@Common_IF,
@TieHash_IF,
'connect' => { U =>[1,5,'[$db [,$user [,$passwd [,\%attr]]]]'] },
'disconnect_all'=>{ U =>[1,1] },
data_sources => { U =>[1,2,'[\%attr]' ] },
},
db => { # Database Session Class Interface
@Common_IF,
@TieHash_IF,
commit => { U =>[1,1] },
rollback => { U =>[1,1] },
'do' => { U =>[2,0,'$statement [, \%attr [, @bind_params ] ]']
},
prepare => { U =>[2,3,'$statement [, \%attr]'] },
prepare_cached => { U =>[2,3,'$statement [, \%attr]'] },
selectrow_array => { U =>[2,0,'$statement [, \%attr [, @bind_params ]
]'] },
selectall_arrayref=>{U =>[2,0,'$statement [, \%attr [, @bind_params ]
]'] },
handler => { U =>[2,2,'\&handler'] },
ping => { U =>[1,1] },
disconnect => { U =>[1,1] },
quote => { U =>[2,2, '$str'] },
rows => $keeperr,
tables => { U =>[1,1] },
table_info => { U =>[1,1] },
type_info_all => { U =>[1,1] },
type_info => { U =>[1,2] },
get_info => { U =>[2,2] },
},
st => { # Statement Class Interface
@Common_IF,
@TieHash_IF,
bind_col => { U =>[3,4,'$column, \\$var [, \%attr]'] },
bind_columns => { U =>[3,0,'\%attr, \\$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]'] },
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,2] },
blob_read => { U =>[4,5,'$field, $offset, $len [, \\$buf [, $bufoffs
et]]'] },
blob_copy_to_file => { U =>[3,3,'$field, $filename_or_handleref'] },
dump_results => { U =>[1,5,'$maxfieldlen, $linesep, $fieldsep, $fileh
andle'] },
finish => { U =>[1,1] },
cancel => { U =>[1,1] },
rows => $keeperr,
_get_fbav => undef,
_set_fbav => undef,
},
);
my($class, $method);
foreach $class (keys %DBI_IF){
my %pkgif = %{$DBI_IF{$class}};
foreach $method (keys %pkgif){
DBI->_install_method("DBI::${class}::$method", 'DBI.pm',
$pkgif{$method});
}
}
# End of init code
END {
DBI->trace_msg(" -> DBI::END\n") if $DBI::dbi_debug >= 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;
DBI->trace_msg(" <- DBI::END complete\n") if $DBI::dbi_debug >=
2;
}
# --- The DBI->connect Front Door function
sub connect {
my $class = shift;
my($dsn, $user, $pass, $attr, $old_driver) = @_;
my $driver;
my $dbh;
# switch $old_driver<->$attr if called in old style
($old_driver, $attr) = ($attr, $old_driver) if $attr and !ref($att
r);
$dsn ||= $ENV{DBI_DSN} || $ENV{DBI_DBNAME} || '' unless $old_drive
r;
$user = $ENV{DBI_USER} unless defined $user;
$pass = $ENV{DBI_PASS} unless defined $pass;
if ($DBI::dbi_debug) {
local $^W = 0; # prevent 'Use of uninitialized value' warnings
DBI->trace_msg(" -> $class->connect(".join(", ",@_).")\n");
}
Carp::croak('Usage: $class->connect([$dsn [,$user [,$passwd [,\%at
tr]]]])')
if (ref $old_driver or ($attr and not ref $attr) or ref $pass);
# extract dbi:driver prefix from $dsn into $1
$dsn =~ s/^dbi:(.*?)://i;
# Set $driver. Old style driver, if specified, overrides new dsn s
tyle.
$driver = $old_driver || $1
or Carp::croak("Can't connect, no database driver specified");
if ($ENV{DBI_AUTOPROXY} && $driver ne 'Proxy' && $driver ne 'Switc
h') {
$dsn = "$ENV{DBI_AUTOPROXY};dsn=dbi:$driver:$dsn";
$driver = 'Proxy';
DBI->trace_msg(" DBI_AUTOPROXY: dbi:$driver:$dsn\n");
}
unless ($old_driver) { # new-style connect so new default semantic
s
$attr = { PrintError=>1, AutoCommit=>1, ref $attr ? %$attr : () };
}
my $drh = $class->install_driver($driver) || die 'panic: install_d
river';
unless ($dbh = $drh->$connect_via($dsn, $user, $pass, $attr)) {
Carp::croak($drh->errstr) if ref $attr and $attr->{RaiseError};
Carp::carp($drh->errstr) if ref $attr and $attr->{PrintError};
DBI->trace_msg(" $class->connect failed: ".($drh->errstr)."\n")
;
$! = 0; # for the daft people who do DBI->connect(...) || die "$!";
return undef;
}
# XXX this is inelegant but practical in the short term, sigh.
if ($installed_rootclass{$class}) {
$dbh->{RootClass} = $class;
bless $dbh => $class.'::db';
my $inner = DBI::_inner($dbh);
bless $inner => $class.'::db';
}
if (ref $attr) {
my %a = %$attr;
my $a;
# handle these attributes first
foreach $a (qw(RaiseError PrintError AutoCommit)) {
next unless exists $a{$a};
$dbh->{$a} = $a{$a};
delete $a{$a};
}
foreach $a (keys %a) {
$dbh->{$a} = $a{$a};
}
}
DBI->trace_msg(" <- connect= $dbh\n");
$dbh;
}
sub disconnect_all {
foreach(keys %DBI::installed_drh){
my $drh = $DBI::installed_drh{$_};
next unless ref $drh; # avoid problems on premature death
$drh->disconnect_all();
}
}
sub install_driver { # croaks on failure
my $class = shift;
my($driver, $attr) = @_;
my $drh;
$driver ||= $ENV{DBI_DRIVER} || '';
# allow driver to be specified as a 'dbi:driver:' string
$driver = $1 if $driver =~ s/^DBI:(.*?)://i;
Carp::croak("usage: $class->install_driver(\$driver [, \%attr])")
unless ($driver and @_<=3);
# already installed
return $drh if $drh = $DBI::installed_drh{$driver};
DBI->trace_msg(" -> $class->install_driver($driver"
.") for perl=$] pid=$$ ruid=$< euid=$>\n")
if $DBI::dbi_debug;
# --- load the code
eval "package DBI::_firesafe; require DBD::$driver";
if ($@) {
my $advice = "";
if ($@ =~ /Can't find loadable object/) {
$advice = "Perhaps DBD::$driver was statically linked into a new
perl binary."
."\nIn which case you need to use that new perl binary."
."\nOr perhaps only the .pm file was installed but not the shared o
bject file."
}
elsif ($@ =~ /Can't locate.*?DBD\/$driver\.pm/) {
my @drv = DBI->available_drivers(1);
$advice = "Perhaps the DBD::$driver perl module hasn't been insta
lled,\n"
."or perhaps the capitalisation of '$driver' isn't right.\n"
."Available drivers: ".join(", ", sort @drv).".";
}
Carp::croak("install_driver($driver) failed: $@$advice\n");
}
DBI->trace_msg(" install_driver: driver $driver loaded\n")
if $DBI::dbi_debug;
# --- do some behind-the-scenes checks and setups on the driver
_setup_driver($driver);
# --- run the driver function
my $driver_class = "DBD::$driver";
$drh = eval { $driver_class->driver($attr || {}) };
unless ($drh && ref $drh && !$@) {
my $advice = "";
# catch people on case in-sensitive systems using the wrong case
$advice = "\nPerhaps the capitalisation of DBD '$driver' isn't right.
"
if $@ =~ /locate object method/;
croak("$driver_class initialisation failed: $@$advice");
}
$DBI::installed_drh{$driver} = $drh;
DBI->trace_msg(" <- install_driver= $drh\n") if $DBI::dbi_debug
;
$drh;
}
*driver = \&install_driver; # currently an alias, may change
sub _setup_driver {
my $driver = shift;
my $type;
foreach $type (qw(dr db st)){
my $class = "DBD::${driver}::$type";
no strict 'refs';
push @{"${class}::ISA"}, "DBD::_::$type";
push @{"${class}_mem::ISA"}, "DBD::_mem::$type";
}
}
sub init_rootclass {
my $rootclass = shift;
no strict 'refs';
croak("Can't init '$rootclass' without '$rootclass\::db' class.")
unless defined ${"$rootclass\::db::"}{ISA};
$installed_rootclass{$rootclass} = 1;
# may do checks on ::db and ::st classes later
return 1;
}
*internal = \&DBD::Switch::dr::driver;
#sub internal { return DBD::Switch::dr::driver(@_); }
sub available_drivers {
my($quiet) = @_;
my(@drivers, $d, $f);
local(*DBI::DIR);
my(%seen_dir, %seen_dbd);
foreach $d (@INC){
chomp($d); # perl 5 beta 3 bug in #!./perl -Ilib from Test::Harness
next unless -d "$d/DBD";
next if $seen_dir{$d};
$seen_dir{$d} = 1;
# XXX we have a problem here with case insensitive file systems
# XXX since we can't tell what case must be used when loading.
opendir(DBI::DIR,"$d/DBD") || Carp::carp "opendir $d/DBD: $!\n";
foreach $f (sort readdir(DBI::DIR)){
next unless $f =~ s/\.pm$//;
next if $f eq 'NullP' || $f eq 'Sponge';
if ($seen_dbd{$f}){
Carp::carp "DBD::$f in $d is hidden by DBD::$f in $seen_dbd{$f}\n"
unless $quiet;
} else {
push(@drivers, $f);
}
$seen_dbd{$f} = $d;
}
closedir(DBI::DIR);
}
@drivers;
}
sub data_sources {
my ($class, $driver, @attr) = @_;
my $drh = $class->install_driver($driver);
my @ds = $drh->data_sources(@attr);
return @ds;
}
sub neat_list {
my ($listref, $maxlen, $sep) = @_;
$maxlen = 0 unless defined $maxlen; # 0 == use internal default
$sep = ", " unless defined $sep;
join($sep, map { neat($_,$maxlen) } @$listref);
}
sub dump_results { # also aliased as a method in DBD::_::st
my ($sth, $maxlen, $lsep, $fsep, $fh) = @_;
return 0 unless $sth;
$maxlen ||= 35;
$lsep ||= "\n";
$fh ||= \*STDOUT;
my $rows = 0;
my $ref;
while($ref = $sth->fetch) {
print $fh $lsep if $rows++ and $lsep;
my $str = neat_list($ref,$maxlen,$fsep);
print $fh $str; # done on two lines to avoid 5.003 errors
}
print $fh "\n$rows rows".($DBI::err ? " ($DBI::err: $DBI::errstr)"
: "")."\n";
$rows;
}
sub connect_test_perf {
my($class, $dsn,$dbuser,$dbpass, $attr) = @_;
croak("connect_test_perf needs hash ref as fourth arg") unless ref $a
ttr;
# these are non standard attributes just for this special method
my $loops ||= $attr->{dbi_loops} || 5;
my $par ||= $attr->{dbi_par} || 1; # parallelism
my $verb ||= $attr->{dbi_verb} || 1;
print "$dsn: testing $loops sets of $par connections:\n";
require Benchmark;
require FileHandle;
$| = 1;
my $t0 = new Benchmark; # not currently used
my $drh = $class->install_driver($dsn) or Carp::croak("Can't insta
ll $dsn driver\n");
my $t1 = new Benchmark;
my $loop;
for $loop (1..$loops) {
my @cons;
print "Connecting... " if $verb;
for (1..$par) {
print "$_ ";
push @cons, ($drh->connect($dsn,$dbuser,$dbpass)
or Carp::croak("Can't connect # $_: $DBI::errstr\n"));
}
print "\nDisconnecting...\n" if $verb;
for (@cons) {
$_->disconnect or warn "bad disconnect $DBI::errstr"
}
}
my $t2 = new Benchmark;
my $td = Benchmark::timediff($t2, $t1);
printf "Made %2d connections in %s\n", $loops*$par, Benchmark::tim
estr($td);
print "\n";
return $td;
}
*trace_msg = \&DBD::_::common::trace_msg;
# --- Private Internal Function for Creating New DBI Handles
sub _new_handle {
my($class, $parent, $attr, $imp_data) = @_;
Carp::croak('Usage: DBI::_new_handle'
.'($class_name, parent_handle, \%attr, $imp_data)'."\n"
.'got: ('.join(", ",$class, $parent, $attr, $imp_data).")\n")
unless(@_ == 4 and (!$parent or ref $parent)
and ref $attr eq 'HASH');
my $imp_class = $attr->{ImplementorClass} or
Carp::croak("_new_handle($class): 'ImplementorClass' attribute not gi
ven");
DBI->trace_msg(" New $class (for $imp_class, parent=$parent, id
=".($imp_data||'').")\n")
if $DBI::dbi_debug >= 3;
# This is how we create a DBI style Object:
my(%hash, $i, $h);
$i = tie %hash, $class, $attr; # ref to inner hash (for driver
)
$h = bless \%hash, $class; # ref to outer hash (for applic
ation)
# The above tie and bless may migrate down into _setup_handle()...
# Now add magic so DBI method dispatch works
DBI::_setup_handle($h, $imp_class, $parent, $imp_data);
return $h unless wantarray;
($h, $i);
}
# minimum constructors for the tie's (alias to XS version)
sub DBI::st::TIEHASH { bless $_[1] => $_[0] };
*DBI::dr::TIEHASH = \&DBI::st::TIEHASH;
*DBI::db::TIEHASH = \&DBI::st::TIEHASH;
# These three special constructors are called by the drivers
# The way they are called is likey to change.
sub _new_drh { # called by DBD::<drivername>::driver()
my ($class, $initial_attr, $imp_data) = @_;
# Provide default storage for State,Err and Errstr.
# Note that these are shared by all child handles by default! XXX
# State must be undef to get automatic faking in DBI::var::FETCH
my ($h_state_store, $h_err_store, $h_errstr_store) = (undef, 0, ''
);
my $attr = {
'ImplementorClass' => $class,
# these attributes get copied down to child handles by default
'Handlers' => [],
'State' => \$h_state_store, # Holder for DBI::state
'Err' => \$h_err_store, # Holder for DBI::err
'Errstr' => \$h_errstr_store, # Holder for DBI::errstr
'Debug' => 0,
%$initial_attr,
'Type'=>'dr',
};
_new_handle('DBI::dr', '', $attr, $imp_data);
}
sub _new_dbh { # called by DBD::<drivername>::dr::connect()
my ($drh, $initial_attr, $imp_data) = @_;
my $imp_class = $drh->{ImplementorClass}
|| Carp::croak("DBI _new_dbh: $drh has no ImplementorClass");
substr($imp_class,-4,4) = '::db';
my $app_class = ref $drh;
substr($app_class,-4,4) = '::db';
my $attr = {
'ImplementorClass' => $imp_class,
%$initial_attr,
'Type' => 'db',
'Driver' => $drh,
};
_new_handle($app_class, $drh, $attr, $imp_data);
}
sub _new_sth { # called by DBD::<drivername>::db::prepare)
my ($dbh, $initial_attr, $imp_data) = @_;
my $imp_class = $dbh->{ImplementorClass}
|| Carp::croak("DBI _new_sth: $dbh has no ImplementorClass");
substr($imp_class,-4,4) = '::st';
my $app_class = ref $dbh;
substr($app_class,-4,4) = '::st';
my $attr = {
'ImplementorClass' => $imp_class,
%$initial_attr,
'Type' => 'st',
'Database' => $dbh,
};
_new_handle($app_class, $dbh, $attr, $imp_data);
}
} # end of DBI package scope
# --------------------------------------------------------------------
# === The internal DBI Switch pseudo 'driver' class ===
{ package DBD::Switch::dr;
DBI::_setup_driver('Switch'); # sets up @ISA
require Carp;
$imp_data_size = 0;
$imp_data_size = 0; # avoid typo warning
$err = 0;
sub driver {
return $drh if $drh; # a package global
my $inner;
($drh, $inner) = DBI::_new_drh('DBD::Switch::dr', {
'Name' => 'Switch',
'Version' => $DBI::VERSION,
# the Attribution is defined as a sub as an example
'Attribution' => sub { "DBI-$DBI::VERSION Switch by Tim Bunce" },
}, \$err);
Carp::croak("DBD::Switch init failed!") unless ($drh && $inner);
return $drh;
}
sub FETCH {
my($drh, $key) = @_;
return DBI->trace if $key eq 'DebugDispatch';
return undef if $key eq 'DebugLog'; # not worth fetching, sorry
return $drh->DBD::_::dr::FETCH($key);
undef;
}
sub STORE {
my($drh, $key, $value) = @_;
if ($key eq 'DebugDispatch') {
DBI->trace($value);
} elsif ($key eq 'DebugLog') {
DBI->trace(-1, $value);
} else {
$drh->DBD::_::dr::STORE($key, $value);
}
}
}
# --------------------------------------------------------------------
# === OPTIONAL MINIMAL BASE CLASSES FOR DBI SUBCLASSES ===
# We only define default methods for harmless functions.
# We don't, for example, define a DBD::_::st::prepare()
{ package DBD::_::common; # ====== Common base class methods ======
use strict;
# methods common to all handle types:
sub _not_impl {
my ($h, $method) = @_;
my $msg = "Driver does not implement the %s method.\n";
$h->trace_msg(sprintf($msg, $method));
return; # empty list / undef
}
# generic TIEHASH default methods:
sub FIRSTKEY { }
sub NEXTKEY { }
sub EXISTS { defined($_[0]->FETCH($_[1])) } # XXX undef?
sub CLEAR { Carp::carp "Can't CLEAR $_[0] (DBI)" }
}
{ package DBD::_::dr; # ====== DRIVER ======
@ISA = qw(DBD::_::common);
use strict;
sub connect { # normally overridden, but a handy default
my($drh, $dsn, $user, $auth)= @_;
my($this) = DBI::_new_dbh($drh, {
'Name' => $dsn,
'User' => $user,
});
$this;
}
sub disconnect_all { # Driver must take responsibility for this
# XXX Umm, may change later.
Carp::croak("Driver has not implemented the disconnect_all method.");
}
sub data_sources {
shift->_not_impl('data_sources');
}
}
{ package DBD::_::db; # ====== DATABASE ======
@ISA = qw(DBD::_::common);
use strict;
sub disconnect {
shift->_not_impl('disconnect');
}
# Drivers are required to implement *::db::DESTROY to encourage ti
dy-up
sub DESTROY { Carp::croak("Driver has not implemented DESTROY for
@_") }
sub quote {
my ($dbh, $str, $data_type) = @_;
return "NULL" unless defined $str;
unless ($data_type) {
$str =~ s/'/''/g; # ISO SQL2
return "'$str'";
}
# Optimise for standard numerics which need no quotes
return $str if $data_type == DBI::SQL_INTEGER
|| $data_type == DBI::SQL_SMALLINT
|| $data_type == DBI::SQL_DECIMAL
|| $data_type == DBI::SQL_FLOAT
|| $data_type == DBI::SQL_REAL
|| $data_type == DBI::SQL_DOUBLE
|| $data_type == DBI::SQL_NUMERIC;
my $ti = $dbh->type_info($data_type);
# XXX needs checking
my $lp = $ti ? $ti->{LITERAL_PREFIX} || "" : "'";
my $ls = $ti ? $ti->{LITERAL_SUFFIX} || "" : "'";
# XXX don't know what the standard says about escaping
# in the 'general case' (where $lp != "'"). So we do this:
$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 selectrow_array {
my ($dbh, $stmt, $attr, @bind) = @_;
my $sth = (ref $stmt) ? $stmt
: $dbh->prepare($stmt, $attr);
return unless $sth;
$sth->execute(@bind) || return;
my @row = $sth->fetchrow_array;
$sth->finish;
return @row;
}
sub selectall_arrayref {
my ($dbh, $stmt, $attr, @bind) = @_;
my $sth = (ref $stmt) ? $stmt
: $dbh->prepare($stmt, $attr);
return unless $sth;
$sth->execute(@bind) || return;
return $sth->fetchall_arrayref;
}
sub prepare_cached {
# 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 ($dbh, $statement, $attr, @params) = @_;
my $cache = $dbh->FETCH('CachedKids');
$dbh->STORE('CachedKids', $cache = {}) unless $cache;
my $key = join " | ", $statement, $attr ? %$attr : ();
my $sth = $cache->{$key};
return $sth if $sth;
$sth = $cache->{$key} = $dbh->prepare($statement, $attr);
Croak::carp("prepare_cached($statement) statement handle $sth still a
ctive")
if $sth && $sth->FETCH('Active');
return $sth;
}
sub ping {
shift->_not_impl('ping');
1;
}
sub commit {
shift->_not_impl('commit');
}
sub rollback {
shift->_not_impl('rollback');
}
sub get_info {
shift->_not_impl("get_info @_");
return undef;
}
sub table_info {
shift->_not_impl('table_info');
return undef;
}
sub tables {
my ($dbh, @args) = @_;
my $sth = $dbh->table_info(@args);
return () unless $sth;
my ($row, @tables);
while($row = $sth->fetch) {
my $name = $row->[2];
$name = "$row->[1].$name" if $row->[1];
push @tables, $name;
}
return @tables;
}
sub type_info_all {
my ($dbh) = @_;
$dbh->_not_impl('type_info_all');
my $ti = [ {} ];
return $ti;
}
sub type_info {
my ($dbh, $data_type) = @_;
my $tia = $dbh->type_info_all;
return unless @$tia;
Carp::croak "Invalid result structure from $dbh->type_info_all"
unless ref($tia) eq 'ARRAY' && ref($tia->[0]) eq 'HASH';
my $idx_hash = shift @$tia;
my @ti;
# --- simple DATA_TYPE match filter
if (defined($data_type) && $data_type != DBI::SQL_ALL_TYPES()) {
my $dt_idx = $idx_hash->{DATA_TYPE};
unless (defined $dt_idx) {
Carp::croak "No DATA_TYPE field in type_info_all result";
# XXX search for a "suitable" type (e.g. DECIMAL)
}
@ti = grep { $_->[$dt_idx] == $data_type } @$tia;
}
else {
@ti = @$tia;
}
# --- format results into list of hash refs
my $idx_fields = keys %$idx_hash;
my @idx_names = keys %$idx_hash;
my @idx_values = values %$idx_hash;
my @out = map {
Carp::croak
"type_info_all result has $idx_fields keys but ".(@$_)." fields"
if @$_ != $idx_fields;
my %h; @h{@idx_names} = @{$_}[ @idx_values ]; \%h;
} @ti;
return $out[0] unless wantarray;
return @out;
}
}
{ package DBD::_::st; # ====== STATEMENT ======
@ISA = qw(DBD::_::common);
use strict;
sub finish { 1 }
sub cancel { undef }
sub bind_param { Carp::croak("Can't bind_param, not implement by d
river") }
sub fetchrow_hashref {
my $sth = shift;
# This may be recoded in XS. It could work with fb_av and bind_col.
# Probably best to add an AV*fields_hvav to dbih_stc_t and set it up
# on the first call to fetchhash which alternate name/value pairs.
# This implementation is just rather simple and not very optimised.
# Notes for XS implementation: since apps may add entries to the hash
# before the next fetch we need to check the key count and, if change
d,
# scan the hash and delete inappropriate keys.
my $row = $sth->fetch or return undef;
my %hash;
@hash{ @{ $sth->FETCH('NAME') } } = @$row;
return \%hash;
}
sub fetchall_arrayref {
my $sth = shift;
my $slice= shift || []; # XXX not documented: may change
my $mode = ref $slice;
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) {
push @rows, [ @{$row}[ @$slice] ] while($row = $sth->fetch);
}
else {
push @rows, [ @$row ] while($row = $sth->fetch);
}
}
elsif ($mode eq 'HASH') {
my @o_keys = keys %$slice;
if (@o_keys) {
my %i_names = map { (lc($_)=>$_) } @{ $sth->{NAME} };
my @i_keys = map { $i_names{lc($_)} } @o_keys;
while ($row = $sth->fetchrow_hashref) {
my %hash;
@hash{@o_keys} = @{$row}{@i_keys};
push @rows, \%hash;
}
}
else {
# XXX assumes new ref each fetchhash
push @rows, $row while ($row = $sth->fetchrow_hashref);
}
}
else { Carp::croak("fetchall_arrayref($mode) invalid") }
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;
}
# Drivers are required to implement *::st::DESTROY to encourage ti
dy-up
sub DESTROY { Carp::croak("Driver has not implemented DESTROY for
@_") }
}
{ # See install_driver
{ package DBD::_mem::dr; @ISA = qw(DBD::_mem::common); }
{ package DBD::_mem::db; @ISA = qw(DBD::_mem::common); }
{ package DBD::_mem::st; @ISA = qw(DBD::_mem::common); }
# DBD::_mem::common::DESTROY is implemented in DBI.xs
}
1;
__END__
=head1 DESCRIPTION
The Perl DBI is a database access Application Programming Interface
(API) for the Perl Language. The DBI defines a set of functions,
variables and conventions that provide a consistent database interface
independant of the actual database being used.
It is important to remember that the DBI is just an interface. A thin
layer of 'glue' between an application and one or more Database Driver
s.
It is the drivers which do the real work. The DBI provides a standard
interface and framework for the drivers to operate within.
This document is a I<work-in-progress>. Although it is incomplete it
should be useful in getting started with the DBI.
=head2 Architecture of a DBI Application
|<- Scope of DBI ->|
.-. .--------------. .-------------.
.-------. | |---| XYZ Driver |---| XYZ Engine |
| Perl | |S| `--------------' `-------------'
| script| |A| |w| .--------------. .-------------.
| using |--|P|--|i|---|Oracle Driver |---|Oracle Engine|
| DBI | |I| |t| `--------------' `-------------'
| API | |c|...
|methods| |h|... Other drivers
`-------' | |...
`-'
The API is the Application Perl-script (or Programming) Interface. Th
e
call interface and variables provided by DBI to perl scripts. The API
is implemented by the DBI Perl extension.
The 'Switch' is the code that 'dispatches' the DBI method calls to the
appropriate Driver for actual execution. The Switch is also
responsible for the dynamic loading of Drivers, error checking/handlin
g
and other duties. The DBI and Switch are generally synonymous.
The Drivers implement support for a given type of Engine (database).
Drivers contain implementations of the DBI methods written using the
private interface functions of the corresponding Engine. Only authors
of sophisticated/multi-database applications or generic library
functions need be concerned with Drivers.
=head2 Notation and Conventions
DBI static 'top-level' class name
$dbh Database handle object
$sth Statement handle object
$drh Driver handle object (rarely seen or used in applications)
$h Any of the $??h handle types above
$rc General Return Code (boolean: true=ok, false=error)
$rv General Return Value (typically an integer)
@ary List of values returned from the database, typically a row of
data
$rows Number of rows processed (if available, else -1)
$fh A filehandle
undef NULL values are represented by undefined values in perl
Note that Perl will automatically destroy database and statement objec
ts
if all references to them are deleted.
Handle object attributes are shown as:
C< $h-E<gt>{attribute_name}> (I<type>)
where I<type> indicates the type of the value of the attribute (if it'
s
not a simple scalar):
\$ reference to a scalar: $h->{attr} or $a = ${$h->{attr}}
\@ reference to a list: $h->{attr}->[0] or @a = @{$h->{attr}}
\% reference to a hash: $h->{attr}->{a} or %a = %{$h->{attr}}
=head2 General Interface Rules & Caveats
The DBI does not have a concept of a `current session'. Every session
has a handle object (i.e., a $dbh) returned from the connect method an
d
that handle object is used to invoke database related methods.
Most data is returned to the perl script as strings (null values are
returned as undef). This allows arbitrary precision numeric data to b
e
handled without loss of accuracy. Be aware that perl may not preserve
the same accuracy when the string is used as a number.
Dates and times are returned as character strings in the native format
of the corresponding Engine. Time Zone effects are Engine/Driver
dependent.
Perl supports binary data in perl strings and the DBI will pass binary
data to and from the Driver without change. It is up to the Driver
implementors to decide how they wish to handle such binary data.
Multiple SQL statements may not be combined in a single statement
handle, e.g., a single $sth.
Non-sequential record reads are not supported in this version of the
DBI. E.g., records can only be fetched in the order that the database
returned them and once fetched they are forgotten.
Positioned updates and deletes are not directly supported by the DBI.
See the description of the CursorName attribute for an alternative.
Individual Driver implementors are free to provide any private
functions and/or handle attributes that they feel are useful.
Private driver functions can be invoked using the DBI C<func> method.
Private driver attributes are accessed just like standard attributes.
Character sets: Most databases which understand character sets have a
default global charset and text stored in the database is, or should
be, stored in that charset (if it's not then that's the fault of eithe
r
the database or the application that inserted the data). When text is
fetched it should be (automatically) converted to the charset of the
client (presumably based on the locale). If a driver needs to set a
flag to get that behaviour then it should do so. It should not require
the application to do that.
=head2 Naming Conventions and Name Space
The DBI package and all packages below it (DBI::*) are reserved for
use by the DBI. Package names beginning with DBD:: are reserved for us
e
by DBI database drivers. All environment variables used by the DBI
or DBD's begin with 'DBI_' or '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, SQL92 etc (portable)
MixedCase DBI API (portable), underscores are not used.
lower_case Driver or 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., ora_ for Oracle, ing_ for Ingres etc).
Driver Specific Prefix Registry:
ora_ DBD::Oracle
ing_ DBD::Ingres
odbc_ DBD::ODBC
syb_ DBD::Sybase
db2_ DBD::DB2
ix_ DBD::Informix
csv_ DBD::CSV
file_ DBD::TextFile
xbase_ DBD::XBase
solid_ DBD::Solid
proxy_ DBD::Proxy
=head2 Data Query Methods
The DBI allows an application to `prepare' a statement for later execu
tion.
A prepared statement is identified by a statement handle object, e.g.,
$sth.
Typical method call sequence for a select statement:
connect,
prepare,
execute, fetch, fetch, ... finish,
execute, fetch, fetch, ... finish,
execute, fetch, fetch, ... finish.
Typical method call sequence for a non-select statement:
connect,
prepare,
execute,
execute,
execute.
=head2 Placeholders and Bind Values
Some drivers support Placeholders and Bind Values. These drivers allow
a database statement to contain placeholders, sometimes called
parameter markers, that indicate values that will be supplied later,
before the prepared statement is executed. For example, an applicatio
n
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 binding and the values are
referred to as bind values.
When using placeholders with the SQL C<LIKE> qualifier you must
remember that the placeholder substitutes for the whole string.
So you should use "... LIKE ? ..." and include any wildcard
characters in the value that you bind to the placeholder.
B<Null Values>
Undefined values or C<undef> can be used to indicate null values.
However, care must be taken in the particular case of trying to use
null values to qualify a select statement. Consider:
select description from products where product_code = ?
Binding an undef (NULL) to the placeholder will I<not> select rows
which have a NULL product_code! Refer to the SQL manual for your datab
ase
engine or any SQL book for the reasons for this. To explicitly select
NULLs you have to say "where product_code is NULL" and to make that
general you have to say:
... where product_code = ? or (? is null and product_code is null)
and bind the same value to both placeholders.
B<Performance>
Without using placeholders, the insert statement above would have to
contain the literal values to be inserted and it would have to be
re-prepared and re-executed for each row. With placeholders, the inser
t
statement only needs to be prepared once. The bind values for each row
can be given to the execute method each time it's called. By avoiding
the need to re-prepare the statement for each row the application
typically many times faster! Here's an example:
my $sth = $dbh->prepare(q{
insert into sales (product_code, qty, price) values (?, ?, ?)
}) || die $dbh->errstr;
while (<>) {
chop;
my ($product_code, $qty, $price) = split /,/;
$sth->execute($product_code, $qty, $price) || die $dbh->errstr;
}
$dbh->commit || 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 lik
e
C<qq{...}> operator if you want to interpolate variables into the stri
ng.
See L<perlop/"Quote and Quote-like Operators"> for more details.
See L</bind_column> for a related method used to associate perl
variables with the I<output> columns of a select statement.
=head2 SQL - A Query Language
Most DBI drivers require applications to use a dialect of SQL (the
Structured Query Language) to interact with the database engine. Thes
e
links may provide some useful information about SQL:
http://www.jcc.com/sql_stnd.html
http://w3.one.net/~jhoffman/sqltut.htm
http://skpc10.rdg.ac.uk/misc/sqltut.htm
http://epoch.CS.Berkeley.EDU:8000/sequoia/dba/montage/FAQ/SQL_TOC.ht
ml
http://www.bf.rmit.edu.au/Oracle/sql.html
The DBI itself does not mandate or require any particular language to
be used. It is language independant. In ODBC terms the DBI is in
'pass-thru' mode (individual drivers might not be). The only requireme
nt
is that queries and other statements must be expressed as a single
string of letters passed as the first argument to the L</prepare> meth
od.
=head1 THE DBI CLASS
=head2 DBI Class Methods
=over 4
=item B<connect>
$dbh = DBI->connect($data_source, $username, $password) || die $DBI:
:errstr;
$dbh = DBI->connect($data_source, $username, $password, \%attr) || .
..
Establishes a database connection (session) to the requested data_sour
ce.
Returns a database handle object if the connect succeeds. If the conne
ct
fails (see below) it returns undef and sets $DBI::err and $DBI::errstr
(it does I<not> set $! or $? etc).
Multiple simultaneous connections to multiple databases through multip
le
drivers can be made via the DBI. Simply make one connect call for each
and keep a copy of each returned database handle.
The $data_source value should begin with 'dbi:driver_name:'. That
prefix will be stripped off and the driver_name part is used to specif
y
the driver (letter case is significant).
As a convenience, if the $data_source field is undefined or empty the
DBI will substitute the value of the environment variable DBI_DSN.
If the driver_name part is empty (i.e., data_source prefix is 'dbi::')
the environment variable DBI_DRIVER is used. If that variable is not
set then the connect dies.
Examples of $data_source values:
dbi:DriverName:database_name
dbi:DriverName:database_name@hostname:port
dbi:DriverName:database=database_name;host=hostname;port=port
There is I<no standard> for the text following the driver name. Each
driver is free to use whatever syntax it wants. The only requirement t
he
DBI makes is that all the information is supplied in a single string.
You must consult the documentation for the drivers you are using for a
description of the syntax they require. (Where a driver author needs
to define a syntax for the data_source it is recommended that
they follow the ODBC style, the last example above.)
If the environment variable DBI_AUTOPROXY is defined (and the driver i
n
$data_source is not 'Proxy') then the connect request will
automatically be changed to:
dbi:Proxy:$ENV{DBI_AUTOPROXY};dsn=$data_source
and passed to the DBD::Proxy module. DBI_AUTOPROXY would typically be
"hostname=...;port=...". See L<DBD::Proxy> for more details.
If $username or $password are I<undefined> (rather than empty) then th
e
DBI will substitute the values of the DBI_USER and DBI_PASS environmen
t
variables respectively. The use of the environment for these values i
s
not recommended for security reasons. The mechanism is only intended t
o
simplify testing.
DBI->connect automatically installs the driver if it has not been
installed yet. Driver installation I<always> returns a valid driver
handle or it I<dies> with an error message which includes the string
'install_driver' and the underlying problem. So, DBI->connect will die
on a driver installation failure and will only return undef on a
connect failure, for which $DBI::errstr will hold the error.
The $data_source argument (with the 'dbi:...:' prefix removed) and the
$username and $password arguments are then passed to the driver for
processing. The DBI does not define I<any> interpretation for the
contents of these fields. The driver is free to interpret the
data_source, username and password fields in any way and supply
whatever defaults are appropriate for the engine being accessed
(Oracle, for example, uses the ORACLE_SID and TWO_TASK env vars if no
data_source is specified).
The AutoCommit and PrintError attributes for each connection default t
o
default to I<on> (see L</AutoCommit> and L</PrintError> for more infor
mation).
The \%attr parameter can be used to alter the default settings of the
PrintError, RaiseError and AutoCommit attributes. For example:
$dbh = DBI->connect($data_source, $user, $pass, {
PrintError => 0,
AutoCommit => 0
});
These are currently the I<only> defined uses for the DBI->connect \%at
tr.
Portable applications should not assume that a single driver will be
able to support multiple simultaneous sessions.
Where possible each session ($dbh) is independent from the transaction
s
in other sessions. This is useful where you need to hold cursors open
across transactions, e.g., use one session for your long lifespan
cursors (typically read-only) and another for your short update
transactions.
For compatibility with old DBI scripts the driver can be specified by
passing its name as the fourth argument to connect (instead of \%attr)
:
$dbh = DBI->connect($data_source, $user, $pass, $driver);
In this 'old-style' form of connect the $data_source should not start
with 'dbi:driver_name:' and, even if it does, the embedded driver_name
will be ignored. The $dbh->{AutoCommit} attribute is I<undefined>. The
$dbh->{PrintError} attribute is off. And the old DBI_DBNAME env var is
checked if DBI_DSN is not defined. This 'old-style' connect will be
withdrawn in a future version.
=item B<available_drivers>
@ary = DBI->available_drivers;
@ary = DBI->available_drivers($quiet);
Returns a list of all available drivers by searching for DBD::* module
s
through the directories in @INC. By default a warning will be given if
some drivers are hidden by others of the same name in earlier
directories. Passing a true value for $quiet will inhibit the warning.
=item B<data_sources>
@ary = DBI->data_sources($driver);
@ary = DBI->data_sources($driver, \%attr);
Returns a list of all data sources (databases) available via the named
driver. The driver will be loaded if not already. If $driver is empty
or undef then the value of the DBI_DRIVER environment variable will be
used.
Data sources will be returned in a form suitable for passing to the
L</connect> method, i.e., they will include the "dbi:$driver:" prefix.
Note that many drivers have no way of knowing what data sources might
be available for it and thus, typically, return an empty or incomplete
list.
=item B<trace>
DBI->trace($trace_level)
DBI->trace($trace_level, $trace_file)
DBI trace information can be enabled for all handles using this DBI
class method. To enable trace information for a specific handle use
the similar $h->trace method described elsewhere.
Use $trace_level 2 to see detailed call trace information including
parameters and return values. The trace output is detailed and
typically I<very> useful. Much of the trace output is formatted using
the L</neat> function.
Use $trace_level 0 to disable the trace.
If $trace_filename is specified then the file is opened in append
mode and I<all> trace output (including that from other handles)
is redirected to that file.
See also the $h->trace() method and L</DEBUGGING> for information
about the DBI_TRACE environment variable.
=back
=head2 DBI Utility Functions
=over 4
=item B<neat>
$str = DBI::neat($value, $maxlen);
Return a string containing a neat (and tidy) representation of the
supplied value.
Strings will be quoted (but internal quotes will not be escaped).
Values I<known> to be numeric will be unquoted. Undefined (NULL) value
s
will be shown as C<undef> (without quotes). Unprintable characters wil
l
be replaced by dot (.).
For result strings longer than $maxlen the result string will be
truncated to $maxlen-4 and C<...'> will be appended. If $maxlen is 0
or undef it defaults to $DBI::neat_maxlen which, in turn, defaults to
400.
This function is designed to format values for human consumption.
It is used internally by the DBI for L</trace> output. It should
typically I<not> be used for formating values for database use
(see also L</quote>).
=item B<neat_list>
$str = DBI::neat_list(\@listref, $maxlen, $field_sep);
Calls DBI::neat on each element of the list and returns a string
containing the results joined with $field_sep. $field_sep defaults
to C<", ">.
=item B<looks_like_number>
@bool = DBI::looks_like_number(@array);
Returns true for each element that looks like a number.
Returns false for each element that does not look like a number.
Returns undef for each element that is undefined or empty.
=back
=head2 DBI Dynamic Attributes
These attributes are always associated with the last handle used.
Where an attribute is equivalent to a method call, then refer to
the method call for all related documentation.
B<Warning:> these attributes are provided as a convenience but they
do have limitations. Specifically, because they are associated with
the last handle used, they should only be used I<immediately> after
calling the method which 'sets' them. They have a 'short lifespan'.
There may also be problems with the multi-threading in 5.005.
If in any doubt, use the corresponding method call.
=over 4
=item B<$DBI::err>
Equivalent to $h->err.
=item B<$DBI::errstr>
Equivalent to $h->errstr.
=item B<$DBI::state>
Equivalent to $h->state.
=item B<$DBI::rows>
Equivalent to $h->rows.
=back
=head1 METHODS COMMON TO ALL HANDLES
=over 4
=item B<err>
$rv = $h->err;
Returns the native database engine error code from the last driver
function called.
=item B<errstr>
$str = $h->errstr;
Returns the native database engine error message from the last driver
function called.
=item B<state>
$str = $h->state;
Returns an error code in the standard SQLSTATE five character format.
Note that the specific success code C<00000> is translated to C<0>
(false). If the driver does not support SQLSTATE then state will
return C<S1000> (General Error) for all errors.
=item B<trace>
$h->trace($trace_level);
$h->trace($trace_level, $trace_filename);
DBI trace information can be enabled for a specific handle (and any
future children of that handle) by setting the trace level using the
trace method.
Use $trace_level 2 to see detailed call trace information including
parameters and return values. The trace output is detailed and
typically I<very> useful.
Use $trace_level 0 to disable the trace.
If $trace_filename is specified then the file is opened in append
mode and I<all> trace output (including that from other handles)
is redirected to that file.
See also the DBI->trace() method and L</DEBUGGING> for information
about the DBI_TRACE environment variable.
=item B<trace_msg>
$h->trace_msg($message_text);
Writes $message_text to trace file if trace is enabled for $h or
for the DBI as a whole. Can also be called as DBI->trace_msg($msg).
See L</trace>.
=item B<func>
$h->func(@func_arguments, $func_name);
The func method can be used to call private non-standard and
non-portable methods implemented by the driver. Note that the function
name is given as the I<last> argument.
This method is not directly related to calling stored procedures.
Calling stored procedures is currently not defined by the DBI.
Some drivers, such as DBD::Oracle, support it in non-portable ways.
See driver documentation for more details.
=back
=head1 ATTRIBUTES COMMON TO ALL HANDLES
These attributes are common to all types of DBI handles.
Some attributes are inherited by I<child> handles. That is, the value
of an inherited attribute in a newly created statement handle is the
same as the value in the parent database handle. Changes to attributes
in the new statement handle do not affect the parent database handle
and changes to the database handle do not affect I<existing> statement
handles, only future ones.
Attempting to set or get the value of an unknown attribute is fatal,
except for private driver specific attributes (which all have names
starting with a lowercase letter).
Example:
$h->{AttributeName} = ...; # set/write
... = $h->{AttributeName}; # get/read
=over 4
=item B<Warn> (boolean, inherited)
Enables useful warnings for certain bad practices. Enabled by default.
Some
emulation layers, especially those for perl4 interfaces, disable warni
ngs.
=item B<Active> (boolean, read-only)
True if the handle object is 'active'. This is rarely used in
applications. The exact meaning of active is somewhat vague at the
moment. For a database handle it typically means that the handle is
connected to a database ($dbh->disconnect should set Active off). For
a statement handle it I<typically> means that the handle is a select
that may have more data to fetch ($dbh->finish or fetching all the dat
a
should set Active off).
=item B<Kids> (integer, read-only)
For a driver handle, Kids is the number of currently existing database
handles that were created from that driver handle. For a database
handle, Kids is the number of currently existing statement handles tha
t
were created from that database handle.
=item B<ActiveKids> (integer, read-only)
Like Kids (above), but only counting those that are Active (as above).
=item B<CachedKids> (hash ref)
For a database handle, returns a reference to the cache (hash) of
statement handles created by the L</prepare_cached> method. For a
driver handle, it would return a reference to the cache (hash) of
statement handles created by the (not yet implemented) connect_cached
method.
=item B<CompatMode> (boolean, inherited)
Used by emulation layers (such as Oraperl) to enable compatible behavi
our
in the underlying driver (e.g., DBD::Oracle) for this handle. Not norm
ally
set by application code.
=item B<InactiveDestroy> (boolean)
This attribute can be used to disable the database related effect of
DESTROY'ing a handle (which would normally close a prepared statement
or disconnect from the database etc). It is specifically designed for
use in UNIX applications which 'fork' child processes. Either the
parent or the child process, but not both, should set InactiveDestroy
on all their handles. For a database handle, this attribute does not
disable an I<explicit> call to the disconnect method, only the implici
t
call from DESTROY.
=item B<PrintError> (boolean, inherited)
This attribute can be used to force errors to generate warnings (using
warn) in addition to returning error codes in the normal way. When se
t
on, any method which results in an error occuring will cause the DBI t
o
effectively do a warn("$class $method failed $DBI::errstr") where $cla
ss
is the driver class and $method is the name of the method which failed
. E.g.,
DBD::Oracle::db prepare failed: ... error text here ...
By default DBI->connect sets PrintError on (except for old-style conne
ct
usage, see connect for more details).
If desired, the warnings can be caught and processed using a $SIG{__WA
RN__}
handler or modules like CGI::ErrorWrap.
=item B<RaiseError> (boolean, inherited)
This attribute can be used to force errors to raise exceptions rather
than simply return error codes in the normal way. It defaults to off.
When set on, any method which results in an error occuring will cause
the DBI to effectively do a croak("$class $method failed $DBI::errstr"
)
where $class is the driver class and $method is the name of the method
which failed. E.g.,
DBD::Oracle::db prepare failed: ... error text here ...
If PrintError is also on then the PrintError is done before the
RaiseError unless no __DIE__ handler has been defined, in which case
PrintError is skipped since the croak will print the message.
If you want to temporarily turn RaiseError off (inside a library funct
ion
that may fail for example), the recommended way is like this:
{
local $h->{RaiseError} = 0 if $h->{RaiseError};
...
}
The original value will automatically and reliably be restored by perl
regardless of how the block is exited. The C<... if $h->{RaiseError}>
is
optional but makes the code slightly faster in the common case.
B<Sadly this doesn't work> for perl versions upto and including 5.004_
04.
For backwards compatibility could just use C<eval { ... }> instead.
=item B<ChopBlanks> (boolean, inherited)
This attribute can be used to control the trimming of trailing space
characters from I<fixed width> character (CHAR) fields. No other field
types are affected, even where field values have trailing spaces.
The default is false (it is possible that that may change).
Applications that need specific behaviour should set the attribute as
needed. Emulation interfaces should set the attribute to match the
behaviour of the interface they are emulating.
Drivers are not required to support this attribute but any driver whic
h
does not must arrange to return undef as the attribute value.
=item B<LongReadLen> (unsigned integer, inherited)
This attribute may be used to control the maximum length of 'long'
('blob', 'memo' etc.) fields which the driver will I<read> from the
database automatically when it fetches each row of data.
A value of 0 means don't automatically fetch any long data (fetch
should return undef for long fields when LongReadLen is 0).
The default is typically 0 (zero) bytes but may vary between drivers.
Most applications fetching long fields will set this value to slightly
larger than the longest long field value which will be fetched.
Changing the value of LongReadLen for a statement handle I<after> it's
been prepare()'d I<will typically have no effect> so it's usual to
set LongReadLen on the $dbh before calling prepare.
The LongReadLen attribute only relates to fetching/reading long values
it is I<not> involved in inserting/updating them.
See L</LongTruncOk> about truncation behaviour.
=item B<LongTruncOk> (boolean, inherited)
This attribute may be used to control the effect of fetching a long
field value which has been truncated (typically because it's longer
than the value of the LongReadLen attribute).
By default LongTruncOk is false and fetching a truncated long value
will cause the fetch to fail. (Applications should always take care to
check for errors after a fetch loop in case an error, such as a divide
by zero or long field truncation, caused the fetch to terminate
prematurely.)
If a fetch fails due to a long field truncation when LongTruncOk is
false, many drivers will allow you to continue fetching further rows.
See also L</LongReadLen>.
=item B<private_*>
The DBI provides a way to store extra information in a DBI handle as
'private' attributes. The DBI will allow you to store and retreive any
attribute which has a name starting with 'private_'. It is I<strongly>
recommended that you use just I<one> private attribute (e.g., use a
hash ref) and give it a long and unambiguous name that includes the
module or application that the attribute relates to (e.g.,
'private_YourModule_thingy').
=back
=head1 DBI DATABASE HANDLE OBJECTS
=head2 Database Handle Methods
=over 4
=item B<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. The $statement parameter can be a previously prepa
red
statement handle in which case the prepare is skipped.
In any method fails, and L</RaiseError> is not set, selectrow_array
will return an empty list.
=item B<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. The $statement parameter can be a previously prepa
red
statement handle in which case the prepare is skipped.
In any method fails, and L</RaiseError> is not set, selectall_arrayref
will return undef.
=item B<prepare>
$sth = $dbh->prepare($statement) || die $dbh->errstr;
$sth = $dbh->prepare($statement, \%attr) || die $dbh->errstr;
Prepare a I<single> statement for execution by the database engine and
return a reference to a statement handle object which can be used to
get attributes of the statement and invoke the L</execute> method.
Note that prepare should never execute a statement, even if it is not
a
select statement, it only prepares it for execution. (Having said that
,
some drivers, notably Oracle, will execute data definition statements
such as create/drop table when they are prepared. In practice this is
rarely a problem.)
Drivers for engines which don't have the concept of preparing a
statement will typically just store the statement in the returned
handle and process it when $sth->execute is called. Such drivers are
likely to be unable to give much useful information about the
statement, such as $sth->{NUM_OF_FIELDS}, until after $sth->execute
has been called. Portable applications should take this into account.
In general DBI drivers do I<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 ca
n
access all the functionality of the engine being used. On the downside
,
you're limited if using a simple engine and need to take extra care if
attempting to write applications to be portable between engines.
Some command-line SQL tools use statement terminators, like a semicolo
n,
to indicate the end of a statement. Such terminators should not be
used with the DBI.
=item B<prepare_cached>
$sth = $dbh->prepare_cached($statement) || die $dbh->errstr
;
$sth = $dbh->prepare_cached($statement, \%attr) || die $dbh->errstr
;
Like L</prepare> except that the statement handled returned will be st
ored
in a hash associated with the $dbh. If another call is made to prepare
_cached
with the I<same parameter values> then the corresponding cached $sth
will be returned (and the database server will not be contacted).
This cacheing can be useful in some applications but it can also cause
problems and should be used with care. Currently a warning will be
generated if the cached $sth being returned is active (L</finish> has
not been called on it).
The cache can be accessed ( |
|