# $Id: DBI.pm,v 11.23 2002/12/01 22:34:29 timbo Exp $ # # Copyright (c) 1994-2002 Tim Bunce Ireland # # See COPYRIGHT section in pod text below for usage and distribution rights. # require 5.005_03; BEGIN { $DBI::VERSION = "1.32"; # ==> 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); $rc = $sth->bind_param_array($p_num, $bind_values, \%attr); $rv = $sth->execute_array(\%attr); $rv = $sth->execute_array(\%attr, @bind_values); $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 =head2 GETTING HELP If you have questions about DBI, you can get help from the I mailing list. You can get help on subscribing and using the list by emailing: dbi-users-help@perl.org Also worth a visit is the DBI home page at: http://dbi.perl.org/ 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. The FAQ is installed as a L module so you can read it by executing C. To help you make the best use of the dbi-users mailing list, and any other lists or forums you may use, I strongly recommend that you read "How To Ask Questions The Smart Way" by Eric Raymond: http://www.tuxedo.org/~esr/faqs/smart-questions.html This document often uses terms like I, I, I. 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, L, L, and L. 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 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. =head2 NOTES This is the DBI specification that corresponds to the DBI version 1.32 (C<$Date: 2002/12/01 22:34:29 $>). The DBI is evolving at a steady pace, so it's good to check that you have the latest copy. The significant user-visible changes in each release are documented in the L module so you can read them by executing C. Some DBI changes require changes in the drivers, but the drivers can take some time to catch up. Recent versions of the DBI have added new features (generally marked I in the text) that may not yet be supported by the drivers you use. Talk to the authors of those drivers if you need the new features. Extensions to the DBI API often use the C namespace. See L and: http://search.cpan.org/search?mode=module&query=DBIx%3A%3A =cut # The POD text continues at the end of the file. { package DBI; my $Revision = substr(q$Revision: 11.23 $, 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 = qw(%DBI %DBI_methods hash); # also populated by export_ok_tags: %EXPORT_TAGS = ( sql_types => [ qw( SQL_GUID SQL_WLONGVARCHAR SQL_WVARCHAR SQL_WCHAR SQL_BIT SQL_TINYINT SQL_LONGVARBINARY SQL_VARBINARY SQL_BINARY SQL_LONGVARCHAR SQL_UNKNOWN_TYPE SQL_ALL_TYPES SQL_CHAR SQL_NUMERIC SQL_DECIMAL SQL_INTEGER SQL_SMALLINT SQL_FLOAT SQL_REAL SQL_DOUBLE SQL_DATETIME SQL_DATE SQL_INTERVAL SQL_TIME SQL_TIMESTAMP SQL_VARCHAR SQL_BOOLEAN SQL_UDT SQL_UDT_LOCATOR SQL_ROW SQL_REF SQL_BLOB SQL_BLOB_LOCATOR SQL_CLOB SQL_CLOB_LOCATOR SQL_ARRAY SQL_ARRAY_LOCATOR SQL_MULTISET SQL_MULTISET_LOCATOR SQL_TYPE_DATE SQL_TYPE_TIME SQL_TYPE_TIMESTAMP SQL_TYPE_TIME_WITH_TIMEZONE SQL_TYPE_TIMESTAMP_WITH_TIMEZONE SQL_INTERVAL_YEAR SQL_INTERVAL_MONTH SQL_INTERVAL_DAY SQL_INTERVAL_HOUR SQL_INTERVAL_MINUTE SQL_INTERVAL_SECOND SQL_INTERVAL_YEAR_TO_MONTH SQL_INTERVAL_DAY_TO_HOUR SQL_INTERVAL_DAY_TO_MINUTE SQL_INTERVAL_DAY_TO_SECOND SQL_INTERVAL_HOUR_TO_MINUTE SQL_INTERVAL_HOUR_TO_SECOND SQL_INTERVAL_MINUTE_TO_SECOND ) ], utils => [ qw( neat neat_list dump_results looks_like_number ) ], profile => [ qw( dbi_profile dbi_profile_merge dbi_time ) ], ); $DBI::dbi_debug = $ENV{DBI_TRACE} || $ENV{PERL_DBI_DEBUG} || 0; # If you get an error here like "Can't find loadable object ..." # then you haven't installed the DBI correctly. Read the README # then install it again. if ( $ENV{DBI_PUREPERL} ) { eval { bootstrap DBI } if $ENV{DBI_PUREPERL} == 1; require DBI::PurePerl if $@ or $ENV{DBI_PUREPERL} >= 2; $DBI::PurePerl ||= 0; # just to silence "only used once" warnings } else { bootstrap DBI; } $EXPORT_TAGS{preparse_flags} = [ grep { /^DBIpp_\w\w_/ } keys %{__PACKAGE__."::"} ]; Exporter::export_ok_tags(keys %EXPORT_TAGS); } *trace_msg = \&DBD::_::common::trace_msg; *set_err = \&DBD::_::common::set_err; use strict; my $connect_via = "connect"; # check if user wants a persistent database connection ( Apache + mod_perl ) if ($INC{'Apache/DBI.pm'} && substr($ENV{GATEWAY_INTERFACE}||'',0,8) eq 'CGI-Perl') { $connect_via = "Apache::DBI::connect"; DBI->trace_msg("DBI connect via $INC{'Apache/DBI.pm'}\n"); } if ($DBI::dbi_debug) { @DBI::dbi_debug = ($DBI::dbi_debug); if ($DBI::dbi_debug !~ m/^\d$/) { # dbi_debug is a file name to write trace log to. # Default level is 2 but if file starts with "digits=" then the # digits (and equals) are stripped off and used as the level unshift @DBI::dbi_debug, 2; @DBI::dbi_debug = ($1,$2) if $DBI::dbi_debug =~ m/^(\d+)=(.*)/; $DBI::dbi_debug = $DBI::dbi_debug[0]; } DBI->trace(@DBI::dbi_debug); } %DBI::installed_drh = (); # maps driver names to installed driver handles # 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 via IHA list tie $DBI::state, 'DBI::var', '"state'; # special case: referenced via IHA list tie $DBI::lasth, 'DBI::var', '!lasth'; # special case: return boolean tie $DBI::errstr, 'DBI::var', '&errstr'; # call &errstr in last used pkg tie $DBI::rows, 'DBI::var', '&rows'; # call &rows in last used pkg sub DBI::var::TIESCALAR{ my $var = $_[1]; bless \$var, 'DBI::var'; } sub DBI::var::STORE { Carp::croak("Can't modify \$DBI::${$_[0]} special variable") } { package DBI::DBI_tie; # used to catch DBI->{Attrib} mistake sub TIEHASH { bless {} } sub STORE { Carp::carp("DBI->{$_[1]} is invalid syntax (you probably want \$h->{$_[1]})");} *FETCH = \&STORE; } tie %DBI::DBI => 'DBI::DBI_tie'; # --- Dynamically create the DBI Standard Interface my $keeperr = { O=>0x0004 }; my @TieHash_IF = ( # Generic Tied Hash Interface 'STORE' => { O=>0x0410 }, 'FETCH' => { O=>0x0404 }, 'FIRSTKEY'=> $keeperr, 'NEXTKEY' => $keeperr, 'EXISTS' => $keeperr, 'CLEAR' => $keeperr, 'DESTROY' => undef, # hardwired internally ); my @Common_IF = ( # Interface functions common to all DBI classes func => { O=>0x0006 }, 'trace' => { U =>[1,3,'[$trace_level, [$filename]]'], O=>0x0004 }, trace_msg => { U =>[2,3,'$message_text [, $min_level ]' ], O=>0x0004, T=>8 }, debug => { U =>[1,2,'[$debug_level]'], O=>0x0004 }, # old name for trace private_data => { U =>[1,1], O=>0x0004 }, err => $keeperr, errstr => $keeperr, state => { U =>[1,1], O=>0x0004 }, set_err => { O=>0x0010 }, _not_impl => undef, ); %DBI::DBI_methods = ( # Define the DBI interface methods per class: dr => { # Database Driver Interface @Common_IF, @TieHash_IF, '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] }, data_sources => { U =>[1,2,'[\%attr]' ] }, default_user => { U =>[3,4,'$user, $pass [, \%attr]' ] }, }, db => { # Database Session Class Interface @Common_IF, @TieHash_IF, connected => { O=>0x0100 }, begin_work => { U =>[1,2,'[ \%attr ]'], O=>0x0400 }, commit => { U =>[1,1], O=>0x0480 }, rollback => { U =>[1,1], O=>0x0480 }, 'do' => { U =>[2,0,'$statement [, \%attr [, @bind_params ] ]'] }, preparse => { }, # XXX prepare => { U =>[2,3,'$statement [, \%attr]'] }, prepare_cached => { U =>[2,4,'$statement [, \%attr [, $allow_active ] ]'] }, selectrow_array => { U =>[2,0,'$statement [, \%attr [, @bind_params ] ]'] }, selectrow_arrayref=>{U =>[2,0,'$statement [, \%attr [, @bind_params ] ]'] }, selectrow_hashref=>{ U =>[2,0,'$statement [, \%attr [, @bind_params ] ]'] }, selectall_arrayref=>{U =>[2,0,'$statement [, \%attr [, @bind_params ] ]'] }, selectall_hashref=>{ U =>[3,0,'$statement, $keyfield [, \%attr [, @bind_params ] ]'] }, selectcol_arrayref=>{U =>[2,0,'$statement [, \%attr [, @bind_params ] ]'] }, ping => { U =>[1,1], O=>0x0404 }, disconnect => { U =>[1,1], O=>0x0400 }, quote => { U =>[2,3, '$string [, $data_type ]' ], O=>0x0430 }, quote_identifier=> { U =>[2,5, '$name [, ...]' ], O=>0x0430 }, rows => $keeperr, tables => { U =>[1,6,'$catalog, $schema, $table, $type [, \%attr ]' ], O=>0x0200 }, table_info => { U =>[1,6,'$catalog, $schema, $table, $type [, \%attr ]' ], O=>0x0200 }, column_info => { U =>[1,6,'$catalog, $schema, $table, $column [, \%attr ]' ], O=>0x0200 }, primary_key_info=> { U =>[4,5,'$catalog, $schema, $table [, \%attr ]' ], O=>0x0200 }, primary_key => { U =>[4,5,'$catalog, $schema, $table [, \%attr ]' ], O=>0x0200 }, foreign_key_info=> { U =>[1,7,'$pk_catalog, $pk_schema, $pk_table, $fk_catalog, $fk_schema, $fk_table' ], O=>0x0200 }, type_info_all => { U =>[1,1], O=>0x0200 }, type_info => { U =>[1,2,'$data_type'], O=>0x0200 }, get_info => { U =>[2,2,'$info_type'], O=>0x0200 }, }, st => { # Statement Class Interface @Common_IF, @TieHash_IF, 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=>0x40 }, 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]'] }, 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] }, rows => $keeperr, _get_fbav => undef, _set_fbav => { T=>6 }, }, ); my($class, $method); foreach $class (keys %DBI::DBI_methods){ my %pkgif = %{ $DBI::DBI_methods{$class} }; foreach $method (keys %pkgif){ DBI->_install_method("DBI::${class}::$method", 'DBI.pm', $pkgif{$method}); } } # 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::installed_drh = (); # clear loaded drivers so they have a chance to reinitialize DBI->trace_msg(sprintf "CONE DBI for new thread %s\n", $DBI::PurePerl ? "" : sprintf("(dbis %x -> %x)",$olddbis, $DBI::_dbistate)); } # --- The DBI->connect Front Door methods sub connect_cached { # XXX we expect Apache::DBI users to still call connect() my ($class, $dsn, $user, $pass, $attr) = @_; ($attr ||= {})->{dbi_connect_method} = 'connect_cached'; return $class->connect($dsn, $user, $pass, $attr); } 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($attr); my $connect_meth = (ref $attr) ? $attr->{dbi_connect_method} : undef; $connect_meth ||= $connect_via; # fallback to default $dsn ||= $ENV{DBI_DSN} || $ENV{DBI_DBNAME} || '' unless $old_driver; if ($DBI::dbi_debug) { local $^W = 0; pop @_ if $connect_meth ne 'connect'; my @args = @_; $args[2] = '****'; # hide password DBI->trace_msg(" -> $class->$connect_meth(".join(", ",@args).")\n"); } Carp::croak('Usage: $class->connect([$dsn [,$user [,$passwd [,\%attr]]]])') if (ref $old_driver or ($attr and not ref $attr) or ref $pass); # extract dbi:driver prefix from $dsn into $1 $dsn =~ s/^dbi:(\w*?)(?:\((.*?)\))?://i or '' =~ /()/; # ensure $1 etc are empty if match fails my $driver_attrib_spec = $2; # Set $driver. Old style driver, if specified, overrides new dsn style. $driver = $old_driver || $1 || $ENV{DBI_DRIVER} or Carp::croak("Can't connect(@_), no database driver specified " ."and DBI_DSN env var not set"); if ($ENV{DBI_AUTOPROXY} && $driver ne 'Proxy' && $driver ne 'Sponge' && $driver ne 'Switch') { my $proxy = 'Proxy'; if ($ENV{DBI_AUTOPROXY} =~ s/^dbi:(\w*?)(?:\((.*?)\))?://i) { $proxy = $1; $driver_attrib_spec = ($driver_attrib_spec) ? "$driver_attrib_spec,$2" : $2; } $dsn = "$ENV{DBI_AUTOPROXY};dsn=dbi:$driver:$dsn"; $driver = $proxy; DBI->trace_msg(" DBI_AUTOPROXY: dbi:$driver($driver_attrib_spec):$dsn\n"); } my %attr; # take a copy we can delete from if ($old_driver) { %attr = %$attr if $attr; } else { # new-style connect so new default semantics %attr = ( PrintError => 1, AutoCommit => 1, ref $attr ? %$attr : (), $driver_attrib_spec ? (split /\s*=>?\s*|\s*,\s*/, $driver_attrib_spec) : (), ); # XXX to be enabled for DBI v2.0? #Carp::carp("AutoCommit attribute not specified in $class->connect") # if $^W && !defined($attr->{AutoCommit}); } $attr = \%attr; # now set $attr at our local copy my $drh = $DBI::installed_drh{$driver} || $class->install_driver($driver) or die "panic: $class->install_driver($driver) failed"; ($user, $pass) = $drh->default_user($user, $pass, $attr) if !(defined $user && defined $pass); unless ($dbh = $drh->$connect_meth($dsn, $user, $pass, $attr)) { $user = '' if !defined $user; my $msg = "$class connect('$dsn','$user',...) failed: ".$drh->errstr; DBI->trace_msg(" $msg\n"); unless ($attr->{HandleError} && $attr->{HandleError}->($msg, $drh, $dbh)) { Carp::croak($msg) if $attr->{RaiseError}; Carp::carp ($msg) if $attr->{PrintError}; } $! = 0; # for the daft people who do DBI->connect(...) || die "$!"; return $dbh; # normally undef, but HandleError could change it } # handle basic RootClass subclassing: my $rebless_class = $attr->{RootClass} || ($class ne 'DBI' ? $class : ''); if ($rebless_class) { if ($attr->{RootClass}) { # explicit attribute (rather than static call) delete $attr->{RootClass}; DBI::_load_module($rebless_class, 0) unless @{"$rebless_class\::ISA"}; } no strict 'refs'; unless (@{"$rebless_class\::db::ISA"} && @{"$rebless_class\::st::ISA"}) { Carp::carp("DBI subclasses '$rebless_class\::db' and ::st are not setup, RootClass ignored"); $rebless_class = undef; $class = 'DBI'; } else { $dbh->{RootClass} = $rebless_class; # $dbh->STORE called via plain DBI::db DBI::_set_isa([$rebless_class], 'DBI'); # sets up both '::db' and '::st' DBI::_rebless($dbh, $rebless_class); # appends '::db' } } if (%$attr) { DBI::_rebless_dbtype_subclass($dbh, $rebless_class||$class, delete $attr->{DbTypeSubclass}, $attr) if $attr->{DbTypeSubclass}; my $a; foreach $a (qw(RaiseError PrintError AutoCommit)) { # do these first next unless exists $attr->{$a}; $dbh->{$a} = delete $attr->{$a}; } foreach $a (keys %$attr) { $dbh->{$a} = $attr->{$a}; } } # if we've been subclassed then let the subclass know that we're connected $dbh->connected($dsn, $user, $pass, \%attr) if ref $dbh ne 'DBI::db'; DBI->trace_msg(" <- connect= $dbh\n") if $DBI::dbi_debug; return $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 disconnect { # a regular beginners bug Carp::croak("DBI->disconnect is not a DBI method. Read the DBI manual."); } 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 $^O perl=$] pid=$$ ruid=$< euid=$>\n") if $DBI::dbi_debug; # --- load the code my $driver_class = "DBD::$driver"; eval "package DBI::_firesafe; require $driver_class"; if ($@) { my $err = $@; my $advice = ""; if ($err =~ /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 object file." } elsif ($err =~ /Can't locate.*?DBD\/$driver\.pm in \@INC/) { my @drv = DBI->available_drivers(1); $advice = "Perhaps the DBD::$driver perl module hasn't been fully installed,\n" ."or perhaps the capitalisation of '$driver' isn't right.\n" ."Available drivers: ".join(", ", @drv)."."; } elsif ($err =~ /Can't load .*? for module DBD::/) { $advice = "Perhaps a required shared library or dll isn't installed where expected"; } elsif ($err =~ /Can't locate .*? in \@INC/) { $advice = "Perhaps a module that DBD::$driver requires hasn't been fully installed"; } Carp::croak("install_driver($driver) failed: $err$advice\n"); } if ($DBI::dbi_debug) { no strict 'refs'; (my $driver_file = $driver_class) =~ s/::/\//g; my $dbd_ver = ${"$driver_class\::VERSION"} || "undef"; DBI->trace_msg(" install_driver: $driver_class version $dbd_ver" ." loaded from $INC{qq($driver_file.pm)}\n"); } # --- do some behind-the-scenes checks and setups on the driver _setup_driver($driver_class); # --- run the driver function $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_class = shift; my $type; foreach $type (qw(dr db st)){ my $class = $driver_class."::$type"; no strict 'refs'; push @{"${class}::ISA"}, "DBD::_::$type"; push @{"${class}_mem::ISA"}, "DBD::_mem::$type" unless $DBI::PurePerl; } } sub _rebless { my $dbh = shift; my ($outer, $inner) = DBI::_handles($dbh); my $class = shift(@_).'::db'; bless $inner => $class; bless $outer => $class; # outer last for return } sub _set_isa { my ($classes, $topclass) = @_; my $trace = DBI->trace_msg(" _set_isa([@$classes])\n"); foreach my $suffix ('::db','::st') { my $previous = $topclass || 'DBI'; # trees are rooted here foreach my $class (@$classes) { my $base_class = $previous.$suffix; my $sub_class = $class.$suffix; my $sub_class_isa = "${sub_class}::ISA"; no strict 'refs'; if (@$sub_class_isa) { DBI->trace_msg(" $sub_class_isa skipped (already set to @$sub_class_isa)\n") if $trace; } else { @$sub_class_isa = ($base_class) unless @$sub_class_isa; DBI->trace_msg(" $sub_class_isa = $base_class\n") if $trace; } $previous = $class; } } } sub _rebless_dbtype_subclass { my ($dbh, $rootclass, $DbTypeSubclass, $attr) = @_; # determine the db type names for class hierarchy my @hierarchy = DBI::_dbtype_names($dbh, $DbTypeSubclass, $attr); # add the rootclass prefix to each ('DBI::' or 'MyDBI::' etc) $_ = $rootclass.'::'.$_ foreach (@hierarchy); # load the modules from the 'top down' DBI::_load_module($_, 1) foreach (reverse @hierarchy); # setup class hierarchy if needed, does both '::db' and '::st' DBI::_set_isa(\@hierarchy, $rootclass); # finally bless the handle into the subclass DBI::_rebless($dbh, $hierarchy[0]); } sub _dbtype_names { # list dbtypes for hierarchy, ie Informix=>ADO=>ODBC my ($dbh, $DbTypeSubclass, $attr) = @_; if ($DbTypeSubclass && $DbTypeSubclass ne '1' && ref $DbTypeSubclass ne 'CODE') { # treat $DbTypeSubclass as a comma separated list of names my @dbtypes = split /\s*,\s*/, $DbTypeSubclass; $dbh->trace_msg(" DbTypeSubclass($DbTypeSubclass)=@dbtypes (explicit)\n"); return @dbtypes; } # XXX will call $dbh->get_info(17) (=SQL_DBMS_NAME) in future? my $driver = $dbh->{Driver}->{Name}; if ( $driver eq 'Proxy' ) { # XXX Looking into the internals of DBD::Proxy is questionable! ($driver) = $dbh->{proxy_client}->{application} =~ /^DBI:(.+?):/i or die "Can't determine driver name from proxy"; } my @dbtypes = (ucfirst($driver)); if ($driver eq 'ODBC' || $driver eq 'ADO') { # XXX will move these out and make extensible later: my $_dbtype_name_regexp = 'Oracle'; # eg 'Oracle|Foo|Bar' my %_dbtype_name_map = ( 'Microsoft SQL Server' => 'MSSQL', 'SQL Server' => 'Sybase', 'Adaptive Server Anywhere' => 'ASAny', 'ADABAS D' => 'AdabasD', ); my $name; $name = $dbh->func(17, 'GetInfo') # SQL_DBMS_NAME if $driver eq 'ODBC'; $name = $dbh->{ado_conn}->Properties->Item('DBMS Name')->Value if $driver eq 'ADO'; die "Can't determine driver name! ($DBI::errstr)\n" unless $name; my $dbtype; if ($_dbtype_name_map{$name}) { $dbtype = $_dbtype_name_map{$name}; } else { if ($name =~ /($_dbtype_name_regexp)/) { $dbtype = lc($1); } else { # generic mangling for other names: $dbtype = lc($name); } $dbtype =~ s/\b(\w)/\U$1/g; $dbtype =~ s/\W+/_/g; } # add ODBC 'behind' ADO push @dbtypes, 'ODBC' if $driver eq 'ADO'; # add discovered dbtype in front of ADO/ODBC unshift @dbtypes, $dbtype; } @dbtypes = &$DbTypeSubclass($dbh, \@dbtypes) if (ref $DbTypeSubclass eq 'CODE'); $dbh->trace_msg(" DbTypeSubclass($DbTypeSubclass)=@dbtypes\n"); return @dbtypes; } sub _load_module { (my $module = shift) =~ s!::!/!g; my $missing_ok = shift; eval { require $module.'.pm'; }; return 1 unless $@; return 0 if $missing_ok && $@ =~ /^\@INC\b/; die; # propagate $@; } sub init_rootclass { # deprecated return 1; } *internal = \&DBD::Switch::dr::driver; sub available_drivers { my($quiet) = @_; my(@drivers, $d, $f); local(*DBI::DIR, $@); my(%seen_dir, %seen_dbd); my $haveFileSpec = eval { require File::Spec }; foreach $d (@INC){ chomp($d); # Perl 5 beta 3 bug in #!./perl -Ilib from Test::Harness my $dbd_dir = ($haveFileSpec ? File::Spec->catdir($d, 'DBD') : "$d/DBD"); next unless -d $dbd_dir; 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, $dbd_dir) || Carp::carp "opendir $dbd_dir: $!\n"; foreach $f (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); } # "return sort @drivers" will not DWIM in scalar context. return wantarray ? sort @drivers : @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 $attr; # 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.pm"; # don't let toke.c create empty FileHandle package $| = 1; my $t0 = new Benchmark; # not currently used my $drh = $class->install_driver($dsn) or Carp::croak("Can't install $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::timestr($td); print "\n"; return $td; } # Help people doing DBI->errstr, might even document it one day # XXX probably best moved to cheaper XS code sub err { $DBI::err } sub errstr { $DBI::errstr } # --- Private Internal Function for Creating New DBI Handles sub _new_handle { my ($class, $parent, $attr, $imp_data, $imp_class) = @_; Carp::croak('Usage: DBI::_new_handle' .'($class_name, parent_handle, \%attr, $imp_data)'."\n" .'got: ('.join(", ",$class, $parent, $attr, $imp_data).")\n") unless (@_ == 5 and (!$parent or ref $parent) and ref $attr eq 'HASH' and $imp_class); $attr->{ImplementorClass} = $imp_class or Carp::croak("_new_handle($class): 'ImplementorClass' attribute not given"); 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 application) # 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); } # XXX 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 likely to change. my $profile; sub _new_drh { # called by DBD::::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 = { # 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, FetchHashKeyName=> 'NAME', %$initial_attr, }; my ($h, $i) = _new_handle('DBI::dr', '', $attr, $imp_data, $class); # XXX DBI_PROFILE unless DBI::PurePerl because for some reason # it kills the t/zz_*_pp.t tests (they silently exit early) if ($ENV{DBI_PROFILE} && !$DBI::PurePerl) { # The profile object created here when the first driver is loaded # is shared by all drivers so we end up with just one set of profile # data and thus the 'total time in DBI' is really the true total. if (!$profile) { # first time $h->{Profile} = $ENV{DBI_PROFILE}; $profile = $h->{Profile}; } else { $h->{Profile} = $profile; } } return $h unless wantarray; ($h, $i); } sub _new_dbh { # called by DBD::::dr::connect() my ($drh, $attr, $imp_data) = @_; my $imp_class = $drh->{ImplementorClass} or 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'; _new_handle($app_class, $drh, $attr||{}, $imp_data, $imp_class); } sub _new_sth { # called by DBD::::db::prepare) my ($dbh, $attr, $imp_data) = @_; my $imp_class = $dbh->{ImplementorClass} or 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'; _new_handle($app_class, $dbh, $attr, $imp_data, $imp_class); } } # end of DBI package scope # -------------------------------------------------------------------- # === The internal DBI Switch pseudo 'driver' class === { package DBD::Switch::dr; DBI::_setup_driver('DBD::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, 'Attribution' => "DBI $DBI::VERSION 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) = @_; $h->trace_msg("Driver does not implement the $method method.\n"); 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)" } *dump_handle = \&DBI::dump_handle; } { package DBD::_::dr; # ====== DRIVER ====== @ISA = qw(DBD::_::common); use strict; sub default_user { my ($drh, $user, $pass) = @_; unless (defined $user) { $user = $ENV{DBI_USER}; carp("DBI connect: user not defined and DBI_USER env var not set") if 0 && !defined $user && $drh->{Warn}; # XXX enable later } unless (defined $pass) { $pass = $ENV{DBI_PASS}; carp("DBI connect: password not defined and DBI_PASS env var not set") if 0 && !defined $pass && $drh->{Warn}; # XXX enable later } return ($user, $pass); } 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 connect_cached { my $drh = shift; my ($dsn, $user, $auth, $attr)= @_; # 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 = $drh->FETCH('CachedKids'); $drh->STORE('CachedKids', $cache = {}) unless $cache; my @attr_keys = $attr ? sort keys %$attr : (); my $key = join "~~", $dsn, $user||'', $auth||'', $attr ? (@attr_keys,@{$attr}{@attr_keys}) : (); my $dbh = $cache->{$key}; return $dbh if $dbh && $dbh->FETCH('Active') && eval { $dbh->ping }; $dbh = $drh->connect(@_); $cache->{$key} = $dbh; # replace prev entry, even if connect failed return $dbh; } 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'); } sub quote_identifier { my ($dbh, @id) = @_; my $attr = (@id > 3) ? pop @id : undef; my $info = $dbh->{dbi_quote_identifier_cache} ||= [ $dbh->get_info(29) || '"', # SQL_IDENTIFIER_QUOTE_CHAR $dbh->get_info(41) || '.', # SQL_CATALOG_NAME_SEPARATOR $dbh->get_info(114) || 1, # SQL_CATALOG_LOCATION ]; my $quote = $info->[0]; foreach (@id) { # quote the elements 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) { $_-- } } } return $sth->fetchall_arrayref($slice, $attr->{MaxRows}); } 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; push @col, @values while $sth->fetch; return \@col; } sub prepare_cached { my ($dbh, $statement, $attr, $allow_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) { if ($sth->FETCH('Active') && ($allow_active||0) != 2) { Carp::carp("prepare_cached($statement) statement handle $sth was still active") if !$allow_active; $sth->finish; } return $sth; } $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->DBI::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 } sub commit { shift->_not_impl('commit'); } sub rollback { shift->_not_impl('rollback'); } sub get_info { shift->_not_impl("get_info @_"); } sub table_info { shift->_not_impl('table_info'); } sub column_info { shift->_not_impl('column_info'); } sub primary_key_info { shift->_not_impl('primary_key_info'); } 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); croak("primary_key method not called in list context") unless wantarray; # leave us some elbow room return @col; } sub foreign_key_info { shift->_not_impl('foreign_key_info'); } sub tables { my ($dbh, @args) = @_; my $sth = $dbh->table_info(@args) or return; my $tables = $sth->fetchall_arrayref or return; my @tables; if ($dbh->get_info(29)) { # SQL_IDENTIFIER_QUOTE_CHAR @tables = map { $dbh->quote_identifier( @{$_}[0,1,2] ) } @$tables; } else { # temporary old style hack (yeach) @tables = map { my $name = $_->[2]; if ($_->[1]) { my $schema = $_->[1]; # a sad hack (mostly for Informix I recall) my $quote = ($schema eq uc($schema)) ? '' : '"'; $name = "$quote$schema$quote.$name" } $name; } @$tables; } 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 $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; $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; } } { package DBD::_::st; # ====== STATEMENT ====== @ISA = qw(DBD::_::common); use strict; sub cancel { undef } 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->DBI::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->DBI::set_err(1, "Can't use named placeholders for non-driver supported bind_param_array") unless DBI::looks_like_number($p_id); # because we rely on execute(@ary) here # get/create arrayref to hold params my $hash_of_arrays = $sth->{ParamArrays} ||= { }; if (ref $value_array eq 'ARRAY') { # check that input has same length as existing # find first arrayref entry (if any) foreach (keys %$hash_of_arrays) { my $v = $$hash_of_arrays{$_}; next unless ref $v eq 'ARRAY'; return $sth->DBI::set_err(1, "Arrayref for parameter $p_id has ".@$value_array." elements" ." but parameter $_ has ".@$v) if @$value_array != @$v; } } # 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->DBI::set_err(1, "bind_param_inout_array not supported"); } sub execute_array { my $sth = shift; my ($attribs, @array_of_arrays) = @_; # get tuple status array or hash attribute (if any) my $tuple_sts = $attribs->{ArrayTupleStatus}; return $sth->DBI::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 my $NUM_OF_PARAMS = $sth->FETCH('NUM_OF_PARAMS'); return $sth->DBI::set_err(1, @array_of_arrays." bind values supplied but $NUM_OF_PARAMS expected") if @array_of_arrays != $NUM_OF_PARAMS; $sth->bind_param_array($_, $array_of_arrays[$_-1]) or return foreach (1..@array_of_arrays); } # no binds, no args, why did they call us ? just toss it to execute() return $sth->execute unless $sth->{ParamArrays}; # get the length of a bound array my $len = 1; # in case all are scalars my %hash_of_arrays = %{$sth->{ParamArrays}}; foreach (keys(%hash_of_arrays)) { my $ary = $hash_of_arrays{$_}; $len = @$ary if ref $ary eq 'ARRAY'; } my @bind_ids = 1..keys(%hash_of_arrays); my ($errcount, $rowcount); my %errstr_cache; @$tuple_sts = () if $tuple_sts; # reset the status array $tuple_sts->[$len-1] = undef; # presize array for (my $i=0; $i < $len; ++$i) { # for each tuple my @tuple = map { my $a = $hash_of_arrays{$_}; ref($a) ? $a->[$i] : $a } @bind_ids; my $rc = $sth->execute(@tuple); if ($rc) { $rowcount += $tuple_sts->[$i] = $rc; next; } return unless $tuple_sts; # return error if no status provided $errcount++; my $err = $sth->err; $tuple_sts->[$i] = [ $err, $errstr_cache{$err} ||= $sth->errstr ]; } return ($errcount) ? undef : $rowcount; } 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 { # XXX may be better to fetchall_arrayref then convert to hashes my ($sth, $key_field) = @_; my $hash_key_name = $sth->{FetchHashKeyName}; my $names_hash = $sth->FETCH("${hash_key_name}_hash"); my $index = $names_hash->{$key_field}; # perl index not column number ++$index if defined $index; # convert to column number $index ||= $key_field if DBI::looks_like_number($key_field) && $key_field>=1; return $sth->DBI::set_err(1, "Field '$key_field' does not exist (not one of @{[keys %$names_hash]})") unless defined $index; my $key_value; $sth->bind_col($index, \$key_value) or return; my %rows; while (my $row = $sth->fetchrow_hashref($hash_key_name)) { $rows{ $key_value } = $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 { 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 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 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 |<- Scope of DBI ->| .-. .--------------. .-------------. .-------. | |---| XYZ Driver |---| XYZ Engine | | Perl | | | `--------------' `-------------' | script| |A| |D| .--------------. .-------------. | using |--|P|--|B|---|Oracle Driver |---|Oracle Engine| | DBI | |I| |I| `--------------' `-------------' | API | | |... |methods| | |... Other drivers `-------' | |... `-' The API, or Application Programming Interface, defines the call interface and variables for Perl scripts to use. The API is implemented by the Perl DBI extension. The DBI "dispatches" the method calls to the appropriate driver for actual execution. The DBI is also responsible for the dynamic loading of drivers, error checking and handling, providing default implementations for methods, and many other non-database specific duties. Each driver contains implementations of the DBI methods using the private interface functions of the corresponding database engine. Only authors of sophisticated/multi-database applications or generic library functions need be concerned with drivers. =head2 Notation and Conventions The following conventions are used in this document: $dbh Database handle object $sth Statement handle object $drh Driver handle object (rarely seen or used in applications) $h Any of the handle types above ($dbh, $sth, or $drh) $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 \%attr Reference to a hash of attribute values passed to methods Note that Perl will automatically destroy database and statement handle objects if all references to them are deleted. =head2 Outline Usage To use DBI, first you need to load the DBI module: use DBI; use strict; (The C isn't required but is strongly recommended.) Then you need to L to your data source and get a I for that connection: $dbh = DBI->connect($dsn, $user, $password, { RaiseError => 1, AutoCommit => 0 }); Since connecting can be expensive, you generally just connect at the start of your program and disconnect at the end. Explicitly defining the required C behavior is strongly recommended and may become mandatory in a later version. This determines whether changes are automatically committed to the database when executed, or need to be explicitly committed later. The DBI allows an application to "prepare" statements for later execution. A prepared statement is identified by a statement handle held in a Perl variable. We'll call the Perl variable C<$sth> in our examples. The typical method call sequence for a C statement is: prepare, execute, execute, execute. for example: $sth = $dbh->prepare("INSERT INTO table(foo,bar,baz) VALUES (?,?,?)"); while() { chomp; my ($foo,$bar,$baz) = split /,/; $sth->execute( $foo, $bar, $baz ); } The C method can be used for non repeated I-C statement. Consider: SELECT description FROM products WHERE product_code = ? Binding an C (NULL) to the placeholder will I select rows which have a NULL C! 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" 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. Sadly, that more general syntax doesn't work for Sybase and MS SQL Server. However on those two servers the original "C" syntax works for binding nulls. B 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 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 and L for more details. The C style quoting used in this example avoids clashing with quotes that may be used in the SQL statement. Use the double-quote like C operator if you want to interpolate variables into the string. See L for more details. See also the L method, which is used to associate Perl variables with the output columns of a C that may have more data to fetch. (Fetching all the data or calling C<$sth->EC sets C off.) =item C (integer, read-only) For a driver handle, C is the number of currently existing database handles that were created from that driver handle. For a database handle, C is the number of currently existing statement handles that were created from that database handle. =item C (integer, read-only) Like C, but only counting those that are C (as above). =item C (hash ref) For a database handle, returns a reference to the cache (hash) of statement handles created by the L method. For a driver handle, returns a reference to the cache (hash) of database handles created by the L method. =item C (boolean, inherited) Used by emulation layers (such as Oraperl) to enable compatible behavior in the underlying driver (e.g., DBD::Oracle) for this handle. Not normally set by application code. =item C (boolean) This attribute can be used to disable the I related effect of DESTROYing a handle (which would normally close a prepared statement or disconnect from the database etc). For a database handle, this attribute does not disable an I call to the disconnect method, only the implicit call from DESTROY. The default value, false, means that a handle will be automatically destroyed when it passes out of scope. A true value disables automatic destruction. (Think of the name as meaning 'inactive the DESTROY method'.) This attribute is specifically designed for use in Unix applications that "fork" child processes. Either the parent or the child process, but not both, should set C on all their shared handles. Note that some databases, including Oracle, don't support passing a database connection across a fork. =item C (boolean, inherited) This attribute can be used to force errors to generate warnings (using C) in addition to returning error codes in the normal way. When set "on", any method which results in an error occuring will cause the DBI to effectively do a C where C<$class> is the driver class and C<$method> is the name of the method which failed. E.g., DBD::Oracle::db prepare failed: ... error text here ... By default, CEC sets C "on". If desired, the warnings can be caught and processed using a C<$SIG{__WARN__}> handler or modules like CGI::Carp and CGI::ErrorWrap. =item C (boolean, inherited) This attribute can be used to force errors to raise exceptions rather than simply return error codes in the normal way. It is "off" by default. When set "on", any method which results in an error will cause the DBI to effectively do a C, where C<$class> is the driver class and C<$method> is the name of the method that failed. E.g., DBD::Oracle::db prepare failed: ... error text here ... If you turn C on then you'd normally turn C off. If C is also on, then the C is done first (naturally). Typically C is used in conjunction with C to catch the exception that's been thrown and followed by an C block to handle the caught exception. In that eval block the $DBI::lasth variable can be useful for diagnosis and reporting. For example, $DBI::lasth->{Type} and $DBI::lasth->{Statement}. If you want to temporarily turn C off (inside a library function that is likely to fail, for example), the recommended way is like this: { local $h->{RaiseError}; # localize and turn off for this block ... } The original value will automatically and reliably be restored by Perl, regardless of how the block is exited. The same logic applies to other attributes, including C. Sadly, this doesn't work for Perl versions up to and including 5.004_04. Even more sadly, for Perl 5.5 and 5.6.0 it does work but leaks memory! For backwards compatibility, you could just use C instead. =item C (code ref, inherited) This attribute can be used to provide your own alternative behaviour in case of errors. If set to a reference to a subroutine then that subroutine is called when an error is detected (at the same point that C and C are handled). The subroutine is called with three parameters: the error message string that C and C would use, the DBI handle being used, and the first value being returned by the method that failed (typically undef). If the subroutine returns a false value then the C and/or C attributes are checked and acted upon as normal. For example, to C with a full stack trace for any error: use Carp; $h->{HandleError} = sub { confess(shift) }; Or to turn errors into exceptions: use Exception; # or your own favourite exception module $h->{HandleError} = sub { Exception->new('DBI')->raise($_[0]) }; It is possible to 'stack' multiple HandleError handlers by using closures: sub your_subroutine { my $previous_handler = $h->{HandleError}; $h->{HandleError} = sub { return 1 if $previous_handler and &$previous_handler(@_); ... your code here ... }; } Using a C inside a subroutine to store the previous C value is important. See L and L for more information about I. It is possible for C to alter the error message that will be used by C and C if it returns false. It can do that by altering the value of $_[0]. This example appends a stack trace to all errors and, unlike the previous example using Carp::confess, this will work C as well as C: $h->{HandleError} = sub { $_[0]=Carp::longmess($_[0]); 0; }; It is also possible for C to hide an error, to a limited degree, by using L to reset $DBI::err and $DBI::errstr, and altering the return value of the failed method. For example: $h->{HandleError} = sub { return 0 unless $_[0] =~ /^\S+ fetchrow_arrayref failed:/; return 0 unless $_[1]->err == 1234; # the error to 'hide' $h->set_err(0,""); # turn off the error $_[2] = [ ... ]; # supply alternative return value return 1; }; This only works for methods which return a single value and is hard to make reliable (avoiding infinite loops, for example) and so isn't recommended for general use! If you find a I use for it then please let me know. =item C (boolean, inherited) I This attribute can be used to cause the relevant Statement text to be appended to the error messages generated by the C and C attributes. Only applies to errors on statement handles plus the prepare(), do(), and the various C database handle methods. (The exact format of the appended text is subject to change.) If C<$h-E{ParamValues}> returns a hash reference of parameter (placeholder) values then those are formatted and appened to the end of the Statement text in the error message. =item C (integer, inherited) I This attribute can be used as an alternative to the L method to set the DBI trace level for a specific handle. =item C (string, inherited) This attribute is used to specify whether the fetchrow_hashref() method should perform case conversion on the field names used for the hash keys. For historical reasons it defaults to 'C' but it is recommended to set it to 'C' (convert to lower case) or 'C' (convert to upper case) according to your preference. It can only be set for driver and database handles. For statement handles the value is frozen when prepare() is called. =item C (boolean, inherited) This attribute can be used to control the trimming of trailing space characters from fixed width character (CHAR) fields. No other field types are affected, even where field values have trailing spaces. The default is false (although it is possible that the default may change). Applications that need specific behavior should set the attribute as needed. Emulation interfaces should set the attribute to match the behavior of the interface they are emulating. Drivers are not required to support this attribute, but any driver which does not support it must arrange to return C as the attribute value. =item C (unsigned integer, inherited) This attribute may be used to control the maximum length of long fields ("blob", "memo", etc.) which the driver will read from the database automatically when it fetches each row of data. The C attribute only relates to fetching and reading long values; it is not involved in inserting or updating them. A value of 0 means not to automatically fetch any long data. (C should return C for long fields when C is 0.) The default is typically 0 (zero) bytes but may vary between drivers. Applications fetching long fields should set this value to slightly larger than the longest long field value to be fetched. Some databases return some long types encoded as pairs of hex digits. For these types, C relates to the underlying data length and not the doubled-up length of the encoded string. Changing the value of C for a statement handle after it has been C'd will typically have no effect, so it's common to set C on the C<$dbh> before calling C. Note that the value used here has a direct effect on the memory used by the application, so don't be too generous. See L for more information on truncation behavior. =item C (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 C attribute). By default, C is false and so fetching a long value that needs to be truncated will cause the fetch to fail. (Applications should always be sure 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 C is false, many drivers will allow you to continue fetching further rows. See also L. =item C (boolean, inherited) If this attribute is set to a true value I Perl is running in taint mode (e.g., started with the C<-T> option), then all the arguments to most DBI method calls are checked for being tainted. I The attribute defaults to off, even if Perl is in taint mode. See L for more about taint mode. If Perl is not running in taint mode, this attribute has no effect. When fetching data that you trust you can turn off the TaintIn attribute, for that statement handle, for the duration of the fetch loop. =item C (boolean, inherited) If this attribute is set to a true value I Perl is running in taint mode (e.g., started with the C<-T> option), then most data fetched from the database is considered tainted. I The attribute defaults to off, even if Perl is in taint mode. See L for more about taint mode. If Perl is not running in taint mode, this attribute has no effect. When fetching data that you trust you can turn off the TaintOut attribute, for that statement handle, for the duration of the fetch loop. Currently only fetched data is tainted. It is possible that the results of other DBI method calls, and the value of fetched attributes, may also be tainted in future versions. That change may well break your applications unless you take great care now. If you use DBI Taint mode, please report your experience and any suggestions for changes. =item C (boolean, inherited) This value is shortcut for L and L (it is also present for backwards compatability). Setting this attribute sets both L and L, and retrieving it returns a true value if and only if L and L are both set to true values. =item C (inherited) Enable collection and reporting of method call timing statistics. See the L module documentation for I more detail. =item C 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 "C". It is I recommended that you use just I private attribute (e.g., use a hash ref) I give it a long and unambiguous name that includes the module or application name that the attribute relates to (e.g., "C"). Because of the way the Perl tie mechanism works you cannot reliably use the C<||=> operator directly to initialise the attribute, like this: my $foo = $dbh->{private_yourmodname_foo} ||= { ... }; # WRONG you should use a two step approach like this: my $foo = $dbh->{private_yourmodname_foo}; $foo ||= $dbh->{private_yourmodname_foo} = { ... }; =back =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 $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 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-C statements because it does not return a statement handle (so you can't fetch any data). The default C 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 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 it once and call C many times instead. The C style quoting used in this example avoids clashing with quotes that may be used in the SQL statement. Use the double-quote-like C operator if you want to interpolate variables into the string. See L for more details. =item C @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, L and L 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 is skipped. If any method fails, and L is not set, C 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 is returned if there are no more rows or if an error occurred. That C can't be distinguished from an C returned because the first field value was NULL. For these reasons you should exercise some caution if you use C in a scalar context. =item C $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, L and L 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 is skipped. If any method fails, and L is not set, C will return undef. =item C $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, L and L 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 is skipped. If any method fails, and L is not set, C will return undef. =item C $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, L and L 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 is skipped. This is recommended if the statement is going to be executed many times. If L is not set and any method except C fails then C will return C; if C fails then it will return with whatever data has been fetched thus far. You should check C<$sth->EC afterwards (or use the C attribute) to discover if the data is complete or was truncated due to an error. The L method called by C supports a $max_rows parameter. You can specify a value for $max_rows by including a 'C' attribute in \%attr. The L method called by C also supports a $slice parameter. You can specify a value for $slice by including a 'C' or 'C' attribute in \%attr. The only difference between the two is that if C is not defined and C 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. =item C $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, L and L into a single call. It returns a reference to a hash containing one entry for each row. The key for each row entry is specified by $key_field. The value is a reference to a hash returned by C. The C<$statement> parameter can be a previously prepared statement handle, in which case the C is skipped. This is recommended if the statement is going to be executed many times. If any method except C fails, and L is not set, C will return C. If C fails and L 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 $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, L, 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 is skipped. This is recommended if the statement is going to be executed many times. If any method except C fails, and L is not set, C will return C. If C fails and L 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 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' 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 =item C $sth = $dbh->prepare($statement) or die $dbh->errstr; $sth = $dbh->prepare($statement, \%attr) or die $dbh->errstr; Prepares a single 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 method. See L. 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->EC is called. Such drivers are unlikely to give much useful information about the statement, such as C<$sth->EC<{NUM_OF_FIELDS}>, until after C<$sth->EC 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). 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 be used with the DBI. =item C $sth = $dbh->prepare_cached($statement) $sth = $dbh->prepare_cached($statement, \%attr) $sth = $dbh->prepare_cached($statement, \%attr, $allow_active) Like L except that the statement handle returned will be stored in a hash associated with the C<$dbh>. If another call is made to C with the same C<$statement> and C<%attr> values, then the corresponding cached C<$sth> will be returned without contacting the database server. Here are some examples of C: sub insert_hash { my ($table, $field_values) = @_; my @fields = sort keys %$field_values; # sort required my @values = @{$field_values}{@fields}; my $sql = sprintf "insert into %s (%s) values (%s)", $table, join(",", @fields), join(",", ("?")x@fields); my $sth = $dbh->prepare_cached($sql); return $sth->execute(@values); } sub search_hash { my ($table, $field_values) = @_; my @fields = sort keys %$field_values; # sort required my @values = @{$field_values}{@fields}; my $qualifier = ""; $qualifier = "where ".join(" and ", map { "$_=?" } @fields) if @fields; $sth = $dbh->prepare_cached("SELECT * FROM $table $qualifier"); return $dbh->selectall_arrayref($sth, {}, @values); } I This caching can be useful in some applications, but it can also cause problems and should be used with care. Here is a contrived case where caching would cause a significant problem: my $sth = $dbh->prepare_cached('SELECT * FROM foo WHERE bar=?'); $sth->execute($bar); while (my $data = $sth->fetchrow_hashref) { my $sth2 = $dbh->prepare_cached('SELECT * FROM foo WHERE bar=?'); $sth2->execute($data->{bar}); while (my $data2 = $sth2->fetchrow_arrayref) { do_stuff(...); } } In this example, since both handles are preparing the exact same statement, C<$sth2> will not be its own statement handle, but a duplicate of C<$sth> returned from the cache. The results will certainly not be what you expect. Typically the the inner fetch loop will work normally, fetching all the records and terminating when there are no more, but now $sth is the same as $sth2 the outer fetch loop will also terminate. The C<$allow_active> parameter lets you adjust DBI's behavior when prepare_cached is returning a statement handle that is still active. There are three settings: =over 4 B<0>: A warning will be generated, and C will be called on the statement handle before it is returned. This is the default behavior if C<$allow_active> is not passed. B<1>: C will be called on the statement handle, but the warning is suppressed. B<2>: DBI will not touch the statement handle before returning it. You will need to check C<$sth->EC<{Active}> on the returned statement handle and deal with it in your own code. =back Because the cache used by prepare_cached() is keyed by all the parameters, including any attributes passed, you can also avoid this issue by doing something like: my $sth = $dbh->prepare_cached("...", { dbi_dummy => __FILE__.__LINE__ }); which will ensure that prepare_cached only returns statements cached by that line of code in that source file. =item C $rc = $dbh->commit or die $dbh->errstr; Commit (make permanent) the most recent series of database changes if the database supports transactions and AutoCommit is off. If C is on, then calling C will issue a "commit ineffective with AutoCommit" warning. See also L in the L section below. =item C $rc = $dbh->rollback or die $dbh->errstr; Rollback (undo) the most recent series of uncommitted database changes if the database supports transactions and AutoCommit is off. If C is on, then calling C will issue a "rollback ineffective with AutoCommit" warning. See also L in the L section below. =item C $rc = $dbh->begin_work or die $dbh->errstr; Enable transactions (by turning C off) until the next call to C or C. After the next C or C, C will automatically be turned on again. If C is already off when C is called then it does nothing except return an error. If the driver does not support transactions then when C attempts to set C off the driver will trigger a fatal error. See also L in the L section below. =item C $rc = $dbh->disconnect or warn $dbh->errstr; Disconnects the database from the database handle. C is typically only used before exiting the program. The handle is of little use after disconnecting. The transaction behavior of the C method is, sadly, undefined. Some database systems (such as Oracle and Ingres) will automatically commit any outstanding changes, but others (such as Informix) will rollback any outstanding changes. Applications not using C should explicitly call C or C before calling C. The database is automatically disconnected by the C method if still connected when there are no longer any references to the handle. The C method for each driver should implicitly call C to undo any uncommitted changes. This is vital behavior to ensure that incomplete transactions don't get committed simply because Perl calls C on every object before exiting. Also, do not rely on the order of object destruction during "global destruction", as it is undefined. Generally, if you want your changes to be commited or rolled back when you disconnect, then you should explicitly call L or L before disconnecting. If you disconnect from a database while you still have active statement handles (e.g., SELECT statement handles that may have more data to fetch), you will get a warning. The warning may indicate that a fetch loop terminated early, perhaps due to an uncaught error. To avoid the warning call the C method on the active handles. =item C $rc = $dbh->ping; Attempts to determine, in a reasonably efficient way, if the database server is still running and the connection to it is still working. Individual drivers should implement this function in the most suitable manner for their database engine. The current I implementation always returns true without actually doing anything. Actually, it returns "C<0 but true>" which is true but zero. That way you can tell if the return value is genuine or just the default. Drivers should override this method with one that does the right thing for their type of database. Few applications would have direct use for this method. See the specialized Apache::DBI module for one example usage. =item C I B This method is experimental and may change. $value = $dbh->get_info( $info_type ); Returns information about the implementation, i.e. driver and data source capabilities, restrictions etc. It returns C for unknown or unimplemented information types. For example: $database_version = $dbh->get_info( 18 ); # SQL_DBMS_VER $max_select_tables = $dbh->get_info( 106 ); # SQL_MAXIMUM_TABLES_IN_SELECT See L for more detailed information about the information types and their meanings and possible return values. The DBI curently doesn't provide a name to number mapping for the information type codes or the results. Applications are expected to use the integer values directly, with the name in a comment, or define their own named values using something like the L pragma. Because some DBI methods make use of get_info(), drivers are strongly encouraged to support I the following very minimal set of information types to ensure the DBI itself works properly: Type Name Example A Example B ---- -------------------------- ------------ ------------ 17 SQL_DBMS_NAME 'ACCESS' 'Oracle' 18 SQL_DBMS_VER '03.50.0000' '08.01.0721' 29 SQL_IDENTIFIER_QUOTE_CHAR '`' '"' 41 SQL_CATALOG_NAME_SEPARATOR '.' '@' 114 SQL_CATALOG_LOCATION 1 2 =item C I B This method is experimental and may change. $sth = $dbh->table_info( $catalog, $schema, $table, $type ); $sth = $dbh->table_info( $catalog, $schema, $table, $type, \%attr ); $sth = $dbh->table_info( \%attr ); # old style Returns an active statement handle that can be used to fetch information about tables and views that exist in the database. The old style interface passes all the parameters as a reference to an attribute hash with some or all of the following attributes: %attr = ( TABLE_CAT => $catalog # String value of the catalog name , TABLE_SCHEM => $schema # String value of the schema name , TABLE_NAME => $table # String value of the table name , TABLE_TYPE => $type # String value of the table type(s) ); The old style interface is deprecated and will be removed in a future version. The support for the selection criteria is driver specific. If the driver doesn't support one or more of them then you may get back more than you asked for and can do the filtering yourself. The arguments $catalog, $schema and $table may accept search patterns according to the database/driver, for example: $table = '%FOO%'; Remember that the underscore character ('C<_>') is a search pattern that means match any character, so 'FOO_%' is the same as 'FOO%' and 'FOO_BAR%' will match names like 'FOO1BAR'. The value of $type is a comma-separated list of one or more types of tables to be returned in the result set. Each value may optionally be quoted, e.g.: $type = "TABLE"; $type = "'TABLE','VIEW'"; In addition the following special cases may also be supported by some drivers: =over 4 =item * If the value of $catalog is '%' and $schema and $table name are empty strings, the result set contains a list of catalog names. For example: $sth = $dbh->table_info('%', '', ''); =item * If the value of $schema is '%' and $catalog and $table are empty strings, the result set contains a list of schema names. =item * If the value of $type is '%' and $catalog, $schema, and $table are all empty strings, the result set contains a list of table types. =back The statement handle returned has at least the following fields in the order show below. Other fields, after these, may also be present. B: Table catalog identifier. This field is NULL (C) if not applicable to the data source, which is usually the case. This field is empty if not applicable to the table. B: The name of the schema containing the TABLE_NAME value. This field is NULL (C) if not applicable to data source, and empty if not applicable to the table. B: Name of the table (or view, synonym, etc). B: One of the following: "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM" or a type identifier that is specific to the data source. B: A description of the table. May be NULL (C). Note that C might not return records for all tables. Applications can use any valid table regardless of whether it's returned by C. See also L, L and L. =item C I B This method is experimental and may change. $sth = $dbh->column_info( $catalog, $schema, $table, $column ); Returns an active statement handle that can be used to fetch information about columns in specified tables. The arguments $schema, $table and $column may accept search patterns according to the database/driver, for example: $table = '%FOO%'; Note: The support for the selection criteria is driver specific. If the driver doesn't support one or more of them then you may get back more than you asked for and can do the filtering yourself. The statement handle returned has at least the following fields in the order shown below. Other fields, after these, may also be present. B: The catalog identifier. This field is NULL (C) if not applicable to the data source, which is often the case. This field is empty if not applicable to the table. B: The schema identifier. This field is NULL (C) if not applicable to the data source, and empty if not applicable to the table. B: The table identifier. Note: A driver may provide column metadata not only for base tables, but also for derived objects like SYNONYMS etc. B: The column identifier. B: The concise data type code. B: A data source dependent data type name. B: The column size. This is the maximum length in characters for character data types, the number of digits or bits for numeric data types or the length in the representation of temporal types. See the relevant specifications for detailed information. B: The length in bytes of transferred data. B: The total number of significant digits to the right of the decimal point. B: The radix for numeric precision. The value is 10 or 2 for numeric data types and NULL (C) if not applicable. B: Indicates if a column can accept NULLs. The following values are defined: SQL_NO_NULLS 0 SQL_NULLABLE 1 SQL_NULLABLE_UNKNOWN 2 B: A description of the column. B: The default value of the column. B: The SQL data type. B: The subtype code for datetime and interval data types. B: The maximum length in bytes of a character or binary data type column. B: The column sequence number (starting with 1). B: Indicates if the column can accept NULLs. Possible values are: 'NO', 'YES' and ''. SQL/CLI defines the following additional columns: CHAR_SET_CAT CHAR_SET_SCHEM CHAR_SET_NAME COLLATION_CAT COLLATION_SCHEM COLLATION_NAME UDT_CAT UDT_SCHEM UDT_NAME DOMAIN_CAT DOMAIN_SCHEM DOMAIN_NAME SCOPE_CAT SCOPE_SCHEM SCOPE_NAME MAX_CARDINALITY DTD_IDENTIFIER IS_SELF_REF Drivers capable of supplying any of those values should do so in the corresponding column and supply undef values for the others. Drivers wishing to provide extra database/driver specific information should do so in extra columns beyond all those listed above, and use lowercase field names with the driver-specific prefix (i.e., 'ora_...'). Applications accessing such fields should do so by name and not by column number. The result set is ordered by TABLE_CAT, TABLE_SCHEM, TABLE_NAME and ORDINAL_POSITION. Note: There is some overlap with statement attributes (in perl) and SQLDescribeCol (in ODBC). However, SQLColumns provides more metadata. See also L and L. =item C I B This method is experimental and may change. $sth = $dbh->primary_key_info( $catalog, $schema, $table ); Returns an active statement handle that can be used to fetch information about columns that make up the primary key for a table. The arguments don't accept search patterns (unlike table_info()). For example: $sth = $dbh->primary_key_info( undef, $user, 'foo' ); $data = $sth->fetchall_arrayref; Note: The support for the selection criteria, such as $catalog, is driver specific. If the driver doesn't support catalogs and/or schemas, it may ignore these criteria. The statement handle returned has at least the following fields in the order shown below. Other fields, after these, may also be present. B: The catalog identifier. This field is NULL (C) if not applicable to the data source, which is often the case. This field is empty if not applicable to the table. B: The schema identifier. This field is NULL (C) if not applicable to the data source, and empty if not applicable to the table. B: The table identifier. B: The column identifier. B: The column sequence number (starting with 1). Note: This field is named B in SQL/CLI. B: The primary key constraint identifier. This field is NULL (C) if not applicable to the data source. See also L and L. =item C I B This method is experimental and may change. @key_column_names = $dbh->primary_key( $catalog, $schema, $table ); Simple interface to the primary_key_info() method. Returns a list of the column names that comprise the primary key of the specified table. The list is in primary key column sequence order. =item C I B This method is experimental and may change. $sth = $dbh->foreign_key_info( $pk_catalog, $pk_schema, $pk_table , $fk_catalog, $fk_schema, $fk_table ); Returns an active statement handle that can be used to fetch information about foreign keys in and/or referencing the specified table(s). The arguments don't accept search patterns (unlike table_info()). C<$pk_catalog>, C<$pk_schema>, C<$pk_table> identify the primary (unique) key table (B). C<$fk_catalog>, C<$fk_schema>, C<$fk_table> identify the foreign key table (B). If both B and B are given, the function returns the foreign key, if any, in table B that refers to the primary (unique) key of table B. (Note: In SQL/CLI, the result is implementation-defined.) If only B is given, then the result set contains the primary key of that table and all foreign keys that refer to it. If only B is given, then the result set contains all foreign keys in that table and the primary keys to which they refer. (Note: In SQL/CLI, the result includes unique keys too.) For example: $sth = $dbh->foreign_key_info( undef, $user, 'master'); $sth = $dbh->foreign_key_info( undef, undef, undef , undef, $user, 'detail'); $sth = $dbh->foreign_key_info( undef, $user, 'master', undef, $user, 'detail'); Note: The support for the selection criteria, such as C<$catalog>, is driver specific. If the driver doesn't support catalogs and/or schemas, it may ignore these criteria. The statement handle returned has the following fields in the order shown below. Because ODBC never includes unique keys, they define different columns in the result set than SQL/CLI. SQL/CLI column names are shown in parentheses. B: The primary (unique) key table catalog identifier. This field is NULL (C) if not applicable to the data source, which is often the case. This field is empty if not applicable to the table. B: The primary (unique) key table schema identifier. This field is NULL (C) if not applicable to the data source, and empty if not applicable to the table. B: The primary (unique) key table identifier. B: The primary (unique) key column identifier. B: The foreign key table catalog identifier. This field is NULL (C) if not applicable to the data source, which is often the case. This field is empty if not applicable to the table. B: The foreign key table schema identifier. This field is NULL (C) if not applicable to the data source, and empty if not applicable to the table. B: The foreign key table identifier. B: The foreign key column identifier. B: The column sequence number (starting with 1). B: The referential action for the UPDATE rule. The following codes are defined: CASCADE 0 RESTRICT 1 SET NULL 2 NO ACTION 3 SET DEFAULT 4 B: The referential action for the DELETE rule. The codes are the same as for UPDATE_RULE. B: The foreign key name. B: The primary (unique) key name. B: The deferrability of the foreign key constraint. The following codes are defined: INITIALLY DEFERRED 5 INITIALLY IMMEDIATE 6 NOT DEFERRABLE 7 B< ( UNIQUE_OR_PRIMARY )>: This column is necessary if a driver includes all candidate (i.e. primary and alternate) keys in the result set (as specified by SQL/CLI). The value of this column is UNIQUE if the foreign key references an alternate key and PRIMARY if the foreign key references a primary key, or it may be undefined if the driver doesn't have access to the information. See also L and L. =item C I B This method is experimental and may change. @names = $dbh->tables( $catalog, $schema, $table, $type ); @names = $dbh->tables; # deprecated Simple interface to table_info(). Returns a list of matching table names, possibly including a catalog/schema prefix. See L for a description of the parameters. If C<$dbh->EC returns true (29 is SQL_IDENTIFIER_QUOTE_CHAR) then the table names are constructed and quoted by L to ensure they are usable even if they contain whitespace or reserved words etc. =item C B This method is experimental and may change. $type_info_all = $dbh->type_info_all; Returns a reference to an array which holds information about each data type variant supported by the database and driver. The array and its contents should be treated as read-only. The first item is a reference to an 'index' hash of CE C pairs. The items following that are references to arrays, one per supported data type variant. The leading index hash defines the names and order of the fields within the arrays that follow it. For example: $type_info_all = [ { TYPE_NAME => 0, DATA_TYPE => 1, COLUMN_SIZE => 2, # was PRECISION originally LITERAL_PREFIX => 3, LITERAL_SUFFIX => 4, CREATE_PARAMS => 5, NULLABLE => 6, CASE_SENSITIVE => 7, SEARCHABLE => 8, UNSIGNED_ATTRIBUTE=> 9, FIXED_PREC_SCALE => 10, # was MONEY originally AUTO_UNIQUE_VALUE => 11, # was AUTO_INCREMENT originally LOCAL_TYPE_NAME => 12, MINIMUM_SCALE => 13, MAXIMUM_SCALE => 14, NUM_PREC_RADIX => 15, }, [ 'VARCHAR', SQL_VARCHAR, undef, "'","'", undef,0, 1,1,0,0,0,undef,1,255, undef ], [ 'INTEGER', SQL_INTEGER, undef, "", "", undef,0, 0,1,0,0,0,undef,0, 0, 10 ], ]; Note that more than one row may have the same value in the C field if there are different ways to spell the type name and/or there are variants of the type with different attributes (e.g., with and without C set, with and without C, etc). The rows are ordered by C first and then by how closely each type maps to the corresponding ODBC SQL data type, closest first. The meaning of the fields is described in the documentation for the L method. The index values shown above (e.g., CE C<6>) are for illustration only. Drivers may define the fields with a different order. This method is not normally used directly. The L method provides a more useful interface to the data. Even though an 'index' hash is provided, all the field names in the index hash defined above will always have the index values defined above. This is defined behaviour so that you don't need to rely on the index hash, which is handy because the lettercase of the keys is not defined. It is usually uppercase, as show here, but drivers are free to return names with any lettercase. Drivers are also free to return extra driver-specific columns of information - though it's recommended that they start at column index 50 to leave room for expansion of the DBI/ODBC specification. =item C B This method is experimental and may change. @type_info = $dbh->type_info($data_type); Returns a list of hash references holding information about one or more variants of $data_type. The list is ordered by C first and then by how closely each type maps to the corresponding ODBC SQL data type, closest first. If called in a scalar context then only the first (best) element is returned. If $data_type is undefined or C, then the list will contain hashes for all data type variants supported by the database and driver. If $data_type is an array reference then C returns the information for the I type in the array that has any matches. The keys of the hash follow the same letter case conventions as the rest of the DBI (see L). The following items should exist: =over 4 =item TYPE_NAME (string) Data type name for use in CREATE TABLE statements etc. =item DATA_TYPE (integer) SQL data type number. =item COLUMN_SIZE (integer) For numeric types, this is either the total number of digits (if the NUM_PREC_RADIX value is 10) or the total number of bits allowed in the column (if NUM_PREC_RADIX is 2). For string types, this is the maximum size of the string in bytes. For date and interval types, this is the maximum number of characters needed to display the value. =item LITERAL_PREFIX (string) Characters used to prefix a literal. A typical prefix is "C<'>" for characters, or possibly "C<0x>" for binary values passed as hexadecimal. NULL (C) is returned for data types for which this is not applicable. =item LITERAL_SUFFIX (string) Characters used to suffix a literal. Typically "C<'>" for characters. NULL (C) is returned for data types where this is not applicable. =item CREATE_PARAMS (string) Parameter names for data type definition. For example, C for a C would be "C" if the DECIMAL type should be declared as CIC<)> where I and I are integer values. For a C it would be "C". NULL (C) is returned for data types for which this is not applicable. =item NULLABLE (integer) Indicates whether the data type accepts a NULL value: C<0> or an empty string = no, C<1> = yes, C<2> = unknown. =item CASE_SENSITIVE (boolean) Indicates whether the data type is case sensitive in collations and comparisons. =item SEARCHABLE (integer) Indicates how the data type can be used in a WHERE clause, as follows: 0 - Cannot be used in a WHERE clause 1 - Only with a LIKE predicate 2 - All comparison operators except LIKE 3 - Can be used in a WHERE clause with any comparison operator =item UNSIGNED_ATTRIBUTE (boolean) Indicates whether the data type is unsigned. NULL (C) is returned for data types for which this is not applicable. =item FIXED_PREC_SCALE (boolean) Indicates whether the data type always has the same precision and scale (such as a money type). NULL (C) is returned for data types for which this is not applicable. =item AUTO_UNIQUE_VALUE (boolean) Indicates whether a column of this data type is automatically set to a unique value whenever a new row is inserted. NULL (C) is returned for data types for which this is not applicable. =item LOCAL_TYPE_NAME (string) Localized version of the C for use in dialog with users. NULL (C) is returned if a localized name is not available (in which case C should be used). =item MINIMUM_SCALE (integer) The minimum scale of the data type. If a data type has a fixed scale, then C holds the same value. NULL (C) is returned for data types for which this is not applicable. =item MAXIMUM_SCALE (integer) The maximum scale of the data type. If a data type has a fixed scale, then C holds the same value. NULL (C) is returned for data types for which this is not applicable. =item SQL_DATA_TYPE (integer) This column is the same as the C column, except for interval and datetime data types. For interval and datetime data types, the C field will return C or C, and the C field below will return the subcode for the specific interval or datetime data type. If this field is NULL, then the driver does not support or report on interval or date subtypes. =item SQL_DATETIME_SUB (integer) For interval or datetime data types, where the C field above is C or C, this field will hold the subcode for the specific interval or datetime data type. Otherwise it will be NULL (C). =item NUM_PREC_RADIX (integer) The radix value of the data type. For approximate numeric types, C contains the value 2 and C holds the number of bits. For exact numeric types, C contains the value 10 and C holds the number of decimal digits. NULL (C) is returned either for data types for which this is not applicable or if the driver cannot report this information. =item INTERVAL_PRECISION (integer) The interval leading precision for interval types. NULL is returned either for data types for which this is not applicable or if the driver cannot report this information. =back For example, to find the type name for the fields in a select statement you can do: @names = map { scalar $dbh->type_info($_)->{TYPE_NAME} } @{ $sth->{TYPE} } Since DBI and ODBC drivers vary in how they map their types into the ISO standard types you may need to search for more than one type. Here's an example looking for a usable type to store a date: $my_date_type = $dbh->type_info( [ SQL_DATE, SQL_TIMESTAMP ] ); Similarly, to more reliably find a type to store small integers, you could use a list starting with C, C, C, etc. See also L. =item C $sql = $dbh->quote($value); $sql = $dbh->quote($value, $data_type); Quote a string literal for use as a literal value in an SQL statement, by escaping any special characters (such as quotation marks) contained within the string and adding the required type of outer quotation marks. $sql = sprintf "SELECT foo FROM bar WHERE baz = %s", $dbh->quote("Don't"); For most database types, quote would return C<'Don''t'> (including the outer quotation marks). An undefined C<$value> value will be returned as the string C (without single quotation marks) to match how NULLs are represented in SQL. If C<$data_type> is supplied, it is used to try to determine the required quoting behavior by using the information returned by L. As a special case, the standard numeric types are optimized to return C<$value> without calling C. Quote will probably I be able to deal with all possible input (such as binary data or data containing newlines), and is not related in any way with escaping or quoting shell meta-characters. There is no need to quote values being used with L. =item C $sql = $dbh->quote_identifier( $name ); $sql = $dbh->quote_identifier( $name1, $name2, $name3, \%attr ); Quote an identifier (table name etc.) for use in an SQL statement, by escaping any special characters (such as double quotation marks) it contains and adding the required type of outer quotation marks. Undefined names are ignored and the remainder are quoted and then joined together, typically with a dot (C<.>) character. For example: $id = $dbh->quote_identifier( undef, 'Her schema', 'My table' ); would, for most database types, return C<"Her schema"."My table"> (including all the double quotation marks). If three names are supplied then the first is assumed to be a catalog name and special rules may be applied based on what L returns for SQL_CATALOG_NAME_SEPARATOR (41) and SQL_CATALOG_LOCATION (114). For example, for Oracle: $id = $dbh->quote_identifier( 'link', 'schema', 'table' ); would return C<"schema"."table"@"link">. =back =head2 Database Handle Attributes This section describes attributes specific to database handles. Changes to these database handle attributes do not affect any other existing or future database handles. 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->{AutoCommit} = ...; # set/write ... = $h->{AutoCommit}; # get/read =over 4 =item C (boolean) If true, then database changes cannot be rolled-back (undone). If false, then database changes automatically occur within a "transaction", which must either be committed or rolled back using the C or C methods. Drivers should always default to C mode (an unfortunate choice largely forced on the DBI by ODBC and JDBC conventions.) Attempting to set C to an unsupported value is a fatal error. This is an important feature of the DBI. Applications that need full transaction behavior can set C<$dbh->EC<{AutoCommit} = 0> (or set C to 0 via L) without having to check that the value was assigned successfully. For the purposes of this description, we can divide databases into three categories: Databases which don't support transactions at all. Databases in which a transaction is always active. Databases in which a transaction must be explicitly started (C<'BEGIN WORK'>). B<* Databases which don't support transactions at all> For these databases, attempting to turn C off is a fatal error. C and C both issue warnings about being ineffective while C is in effect. B<* Databases in which a transaction is always active> These are typically mainstream commercial relational databases with "ANSI standard" transaction behavior. If C is off, then changes to the database won't have any lasting effect unless L is called (but see also L). If L is called then any changes since the last commit are undone. If C is on, then the effect is the same as if the DBI called C automatically after every successful database operation. So calling C or C explicitly while C is on would be ineffective because the changes would have already been commited. Changing C from off to on will trigger a L. For databases which don't support a specific auto-commit mode, the driver has to commit each statement automatically using an explicit C after it completes successfully (and roll it back using an explicit C if it fails). The error information reported to the application will correspond to the statement which was executed, unless it succeeded and the commit or rollback failed. B<* Databases in which a transaction must be explicitly started> For these databases, the intention is to have them act like databases in which a transaction is always active (as described above). To do this, the driver will automatically begin an explicit transaction when C is turned off, or after a L or L (or when the application issues the next database operation after one of those events). In this way, the application does not have to treat these databases as a special case. See L, L and L for other important notes about transactions. =item C (handle) Holds the handle of the parent driver. The only recommended use for this is to find the name of the driver using: $dbh->{Driver}->{Name} =item C (string) Holds the "name" of the database. Usually (and recommended to be) the same as the "C" string used to connect to the database, but with the leading "C" removed. =item C (string, read-only) Returns the statement string passed to the most recent L method called in this database handle, even if that method failed. This is especially useful where C is enabled and the exception handler checks $@ and sees that a 'prepare' method call failed. =item C (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 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 statement, C returns the number of rows affected, if known. If no rows were affected, then C returns "C<0E0>", which Perl will treat as 0 but will regard as true. Note that it is I an error for no rows to be affected by a statement. If the number of rows affected is not known, then C returns -1. For C statement, the driver should automatically call C for you. So you should I normally need to call it explicitly I 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 methods may be better anyway. Adding calls to C 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 method can be used to tell the server that the buffer space can be freed. Calling C resets the L attribute for the statement. It may also make some statement handle attributes (such as C and C) unavailable if they have not already been accessed (and thus cached). The C 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 and the L attribute. The C method should have been called C. =item C $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-C statement. For C statements is not recommended. One alternative method to get a row count for a C statement to a Perl variable. See C below for an example. Note that column numbers count up from 1. Whenever a row is fetched from the database, the corresponding Perl variable is automatically updated. There is no need to fetch and assign the values manually. The binding is performed at a very low level using Perl aliasing so there is no extra copying taking place. This makes using bound variables very efficient. For maximum portability between drivers, C should be called after C. This restriction may be removed in a later version of the DBI. You do not need to bind output columns in order to fetch data, but it can be useful for some applications which need either maximum performance or greater clarity of code. The L method performs a similar but opposite function for input variables. =item C $rc = $sth->bind_columns(@list_of_refs_to_vars_to_bind); Calls L for each column of the C statements, then this attribute holds the number of un-fetched rows in the cache. If the driver doesn't, then it returns C. Note that some drivers pre-fetch rows on execute, whereas others wait till the first fetch. See also the L database handle attribute. =back =head1 FURTHER INFORMATION =head2 Catalog Methods An application can retrieve metadata information from the DBMS by issuing appropriate queries on the views of the Information Schema. Unfortunately, C views are seldom supported by the DBMS. Special methods (catalog methods) are available to return result sets for a small but important portion of that metadata: column_info foreign_key_info primary_key_info table_info All catalog methods accept arguments in order to restrict the result sets. Passing C to an optional argument does not constrain the search for that argument. However, an empty string ('') is treated as a regular search criteria and will only match an empty value. B: SQL/CLI and ODBC differ in the handling of empty strings. An empty string will not restrict the result set in SQL/CLI. Most arguments in the catalog methods accept only I, e.g. the arguments of C. Such arguments are treated as a literal string, i.e. the case is significant and quote characters are taken literally. Some arguments in the catalog methods accept I (strings containing '_' and/or '%'), e.g. the C<$table> argument of C. Passing '%' is equivalent to leaving the argument C. B: The underscore ('_') is valid and often used in SQL identifiers. Passing such a value to a search pattern argument may return more rows than expected! To include pattern characters as literals, they must be preceded by an escape character which can be achieved with $esc = $dbh->get_info( 14 ); # SQL_SEARCH_PATTERN_ESCAPE $search_pattern =~ s/([_%])/$esc$1/g; The ODBC and SQL/CLI specifications define a way to change the default behavior described above: All arguments (except I) are treated as I if the C attribute is set to C. I are very similar to I, i.e. their body (the string within the quotes) is interpreted literally. I are compared in UPPERCASE. The DBI (currently) does not support the C attribute, i.e. it behaves like an ODBC driver where C is set to C. =head2 Transactions Transactions are a fundamental part of any robust database system. They protect against errors and database corruption by ensuring that sets of related changes to the database take place in atomic (indivisible, all-or-nothing) units. This section applies to databases that support transactions and where C is off. See L for details of using C with various types of databases. The recommended way to implement robust transactions in Perl applications is to use C and S> (which is very fast, unlike S>). For example: $dbh->{AutoCommit} = 0; # enable transactions, if possible $dbh->{RaiseError} = 1; eval { foo(...) # do lots of work here bar(...) # including inserts baz(...) # and updates $dbh->commit; # commit the changes if we get this far }; if ($@) { warn "Transaction aborted because $@"; $dbh->rollback; # undo the incomplete changes # add other application on-error-clean-up code here } If the C attribute is not set, then DBI calls would need to be manually checked for errors, typically like this: $h->method(@args) or die $h->errstr; With C set, the DBI will automatically C if any DBI method call on that handle (or a child handle) fails, so you don't have to test the return value of each method call. See L for more details. A major advantage of the C approach is that the transaction will be properly rolled back if I code (not just DBI calls) in the inner application dies for any reason. The major advantage of using the C<$h->EC<{RaiseError}> attribute is that all DBI calls will be checked automatically. Both techniques are strongly recommended. After calling C or C many drivers will not let you fetch from a previously active C statements. See L and L for other important information about transactions. =head2 Handling BLOB / LONG / Memo Fields Many databases support "blob" (binary large objects), "long", or similar datatypes for holding very long strings or large amounts of binary data in a single field. Some databases support variable length long values over 2,000,000,000 bytes in length. Since values of that size can't usually be held in memory, and because databases can't usually know in advance the length of the longest long that will be returned from a C