# $Id: DBI.pm,v 11.13 2002/06/05 22:39:41 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.25; # ==> 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); $ary_ref = $dbh->selectrow_arrayref($statement); @row_ary = $dbh->selectrow_array($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( { ... } ); $ary_ref = $sth->fetchall_arrayref( [ ... ] ); $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 subscribe to 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. 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. =head2 NOTE This is the DBI specification that corresponds to the DBI version 1.25 (C<$Date: 2002/06/05 22:39:41 $>). The DBI specification is evolving at a steady pace, so it's important 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. Note also that whenever the DBI changes, the drivers 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 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.13 $, 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::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") } sub DBI::var::DESTROY { } { package DBI::DBI_tie; # used to catch DBI->{Attrib} mistake sub TIEHASH { bless {} } sub STORE { Carp::carp("DBI->{$_[1]} is invalid syntax (you probably want \$h->{$_[1]})");} *FETCH = \&STORE; } tie %DBI::DBI => 'DBI::DBI_tie'; # --- Dynamically create the DBI Standard Interface my $std = undef; my $keeperr = { O=>0x04 }; my @TieHash_IF = ( # Generic Tied Hash Interface 'STORE' => { O=>0x10 }, 'FETCH' => $keeperr, 'FIRSTKEY'=> $keeperr, 'NEXTKEY' => $keeperr, 'EXISTS' => $keeperr, 'CLEAR' => $keeperr, 'DESTROY' => undef, # hardwired internally ); my @Common_IF = ( # Interface functions common to all DBI classes func => { O=>0x06 }, event => { U =>[2,0,'$type, @args'], O=>0x04 }, 'trace' => { U =>[1,3,'[$trace_level, [$filename]]'], O=>0x04 }, trace_msg => { U =>[2,3,'$message_text [, $min_level ]' ], O=>0x04, T=>8 }, debug => { U =>[1,2,'[$debug_level]'], O=>0x04 }, # old name for trace private_data => { U =>[1,1], O=>0x04 }, err => $keeperr, errstr => $keeperr, state => { U =>[1,1], O=>0x04 }, set_err => { }, _not_impl => $std, ); %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 ]'] }, commit => { U =>[1,1], O=>0x0080 }, rollback => { U =>[1,1], O=>0x0080 }, '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 ] ]'] }, handler => { U =>[2,2,'\&handler'] }, ping => { U =>[1,1], O=>0x04 }, disconnect => { U =>[1,1] }, quote => { U =>[2,3, '$string [, $data_type ]' ], O=>0x30 }, quote_identifier=> { U =>[2,5, '$name [, ...]' ], O=>0x30 }, rows => $keeperr, tables => { U =>[1,6,'$catalog, $schema, $table, $type [, \%attr ]' ] }, table_info => { U =>[1,6,'$catalog, $schema, $table, $type [, \%attr ]' ] }, column_info => { U =>[1,6,'$catalog, $schema, $table, $column [, \%attr ]' ] }, primary_key_info=> { U =>[4,5,'$catalog, $schema, $table [, \%attr ]' ] }, primary_key => { U =>[4,5,'$catalog, $schema, $table [, \%attr ]' ] }, foreign_key_info=> { U =>[1,7,'$pk_catalog, $pk_schema, $pk_table, $fk_catalog, $fk_schema, $fk_table' ] }, type_info_all => { U =>[1,1] }, type_info => { U =>[1,2,'$data_type'] }, get_info => { U =>[2,2,'$info_type'] }, }, 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,2] }, fetchall_hashref => { U =>[2,2] }, 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; } # --- 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 'Switch') { $dsn = "$ENV{DBI_AUTOPROXY};dsn=dbi:$driver:$dsn"; $driver = 'Proxy'; DBI->trace_msg(" DBI_AUTOPROXY: dbi:$driver:$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)) { my $msg = "$class->connect($dsn) failed: ".$drh->errstr; if (%attr) { # XXX add $attr{HandleError} logic here? Carp::croak($msg) if $attr->{RaiseError}; Carp::carp ($msg) if $attr->{PrintError}; } DBI->trace_msg(" $msg\n"); $! = 0; # for the daft people who do DBI->connect(...) || die "$!"; return undef; } # 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); } no strict 'refs'; unless (@{"$rebless_class\::db::ISA"}) { Carp::carp("DBI subclass '$rebless_class\::db' isn't setup, 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"; } } 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($_) 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; eval { require $module.'.pm'; }; return 1 unless $@; return 0 if $@ =~ /^\b\@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; } 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'); } # Drivers are required to implement *::db::DESTROY to encourage tidy-up sub DESTROY { Carp::croak("Driver has not implemented DESTROY for @_") } 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); return unless $sth; $sth->execute(@bind) || return; my $row = $sth->$method(); $sth->finish if $row; return $row; } sub selectrow_arrayref { return _do_selectrow('fetchrow_arrayref', @_); } sub selectrow_hashref { return _do_selectrow('fetchrow_hashref', @_); } sub selectrow_array { my ($dbh, $stmt, $attr, @bind) = @_; my $sth = (ref $stmt) ? $stmt : $dbh->prepare($stmt, $attr); return unless $sth; $sth->execute(@bind) || return; my @row = $sth->fetchrow_array; $sth->finish if @row; return $row[0] unless wantarray; return @row; } sub selectall_arrayref { my ($dbh, $stmt, $attr, @bind) = @_; my $sth = (ref $stmt) ? $stmt : $dbh->prepare($stmt, $attr); return unless $sth; $sth->execute(@bind) || return; 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); } 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 use Data::Dumper; #warn Dumper([ \@bind_ids, \%hash_of_arrays]); for (my $i=0; $i < $len; ++$i) { # for each tuple my @tuple = map { my $a = $hash_of_arrays{$_}; #warn "$_=$a"; ref($a) ? $a->[$i] : $a } @bind_ids; #warn Dumper(\@tuple); 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 { my $sth = shift; my $slice= shift || []; my $mode = ref $slice; my @rows; my $row; if ($mode eq 'ARRAY') { # we copy the array here because fetch (currently) always # returns the same array ref. XXX if (@$slice) { push @rows, [ @{$row}[ @$slice] ] while($row = $sth->fetch); } else { # return $sth->_fetchall_arrayref; push @rows, [ @$row ] while($row = $sth->fetch); } } elsif ($mode eq 'HASH') { if (keys %$slice) { my @o_keys = keys %$slice; my @i_keys = map { lc } keys %$slice; while ($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 ($row = $sth->fetchrow_hashref()); } } else { Carp::croak("fetchall_arrayref($mode) invalid") } return \@rows; } sub fetchall_hashref { my ($sth, $key_field) = @_; my $hash_key_name = $sth->{FetchHashKeyName}; 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 } # Drivers are required to implement *::st::DESTROY to encourage tidy-up sub DESTROY { Carp::croak("Driver has not implemented DESTROY for @_") } } { # See install_driver { package DBD::_mem::dr; @ISA = qw(DBD::_mem::common); } { package DBD::_mem::db; @ISA = qw(DBD::_mem::common); } { package DBD::_mem::st; @ISA = qw(DBD::_mem::common); } # DBD::_mem::common::DESTROY is implemented in DBI.xs } 1; __END__ =head1 DESCRIPTION The 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. 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) I 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 get 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 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() and do() database handle methods. (The exact format of the appended text is subject to change.) =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 which attribute name the fetchrow_hashref() method should use to get the field names for the hash keys. For historical reasons it defaults to 'C' but it is recommended to set it to 'C' or 'C' according to your preference. It can only be set for driver and database tables. 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 data fetched from the database is tainted, and 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 Taint 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 (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. If called in a scalar context, it returns the first field of the first row. 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. In a scalar context, C returns the value of the first field. An C is returned if there are no matching rows or an error occurred. Since that C can't be distinguished from an C returned because the first field value was NULL, calling C in a scalar context should be used with caution. =item C $ary_ref = $dbh->selectrow_array($statement); $ary_ref = $dbh->selectrow_array($statement, \%attr); $ary_ref = $dbh->selectrow_array($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 $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 will have C. =item C (integer, read-only) The number of parameters (placeholders) in the prepared statement. See SUBSTITUTION VARIABLES below for more details. =item C (array-ref, read-only) Returns a reference to an array of field names for each column. The names may contain spaces but should not be truncated or have any trailing space. Note that the names have the letter case (upper, lower or mixed) as returned by the driver being used. Portable applications should use L or L. print "First column name: $sth->{NAME}->[0]\n"; =item C (array-ref, read-only) Like L but always returns lowercase names. =item C (array-ref, read-only) Like L but always returns uppercase names. =item C (hash-ref, read-only) =item C (hash-ref, read-only) =item C (hash-ref, read-only) The C, C, and C attributes return column name information as a reference to a hash. The keys of the hash are the names of the columns. The letter case of the keys corresponds to the letter case returned by the C, C, and C attributes respectively (as described above). The value of each hash entry is the perl index number of the corresponding column (counting from 0). For example: $sth = $dbh->prepare("select Id, Name from table"); $sth->execute; @row = $sth->fetchrow_array; print "Name $row[ $sth->{NAME_lc_hash}{name} ]\n"; =item C (array-ref, read-only) Returns a reference to an array of integer values for each column. The value indicates the data type of the corresponding column. The values correspond to the international standards (ANSI X3.135 and ISO/IEC 9075) which, in general terms, means ODBC. Driver-specific types that don't exactly match standard types should generally return the same values as an ODBC driver supplied by the makers of the database. That might include private type numbers in ranges the vendor has officially registered with the ISO working group: ftp://sqlstandards.org/SC32/SQL_Registry/ Where there's no vendor-supplied ODBC driver to be compatible with, the DBI driver can use type numbers in the range that is now officially reserved for use by the DBI: -9999 to -9000. All possible values for C should have at least one entry in the output of the C method (see L). =item C (array-ref, read-only) Returns a reference to an array of integer values for each column. For non-numeric columns, the value generally refers to either the maximum length or the defined length of the column. For numeric columns, the value refers to the maximum number of significant digits used by the data type (without considering a sign character or decimal point). Note that for floating point types (REAL, FLOAT, DOUBLE), the "display size" can be up to 7 characters greater than the precision. (for the sign + decimal point + the letter E + a sign + 2 or 3 digits). =item C (array-ref, read-only) Returns a reference to an array of integer values for each column. NULL (C) values indicate columns where scale is not applicable. =item C (array-ref, read-only) Returns a reference to an array indicating the possibility of each column returning a null. Possible values are C<0> (or an empty string) = no, C<1> = yes, C<2> = unknown. print "First column may return NULL\n" if $sth->{NULLABLE}->[0]; =item C (string, read-only) Returns the name of the cursor associated with the statement handle, if available. If not available or if the database driver does not support the C<"where current of ..."> SQL syntax, then it returns C. =item C (string, read-only) Returns the statement string passed to the L method. =item C (integer, read-only) If the driver supports a local row cache for C statement handle that's a child of the same database handle. A typical way round this is to connect the the database twice and use one connection for C statement (unlike other data types), some special handling is required. In this situation, the value of the C<$h->EC<{LongReadLen}> attribute is used to determine how much buffer space to allocate when fetching such fields. The C<$h->EC<{LongTruncOk}> attribute is used to determine how to behave if a fetched value can't fit into the buffer. When trying to insert long or binary values, placeholders should be used since there are often limits on the maximum size of an C statement and the L method generally can't cope with binary data. See L. =head2 Simple Examples Here's a complete example program to select and fetch some data: my $dbh = DBI->connect("dbi:DriverName:db_name", $user, $password) or die "Can't connect to $data_source: $DBI::errstr"; my $sth = $dbh->prepare( q{ SELECT name, phone FROM mytelbook }) or die "Can't prepare statement: $DBI::errstr"; my $rc = $sth->execute or die "Can't execute statement: $DBI::errstr"; print "Query will return $sth->{NUM_OF_FIELDS} fields.\n\n"; print "Field names: @{ $sth->{NAME} }\n"; while (($name, $phone) = $sth->fetchrow_array) { print "$name: $phone\n"; } # check for problems which may have terminated the fetch early die $sth->errstr if $sth->err; $dbh->disconnect; Here's a complete example program to insert some data from a file. (This example uses C to avoid needing to check each call). my $dbh = DBI->connect("dbi:DriverName:db_name", $user, $password, { RaiseError => 1, AutoCommit => 0 }); my $sth = $dbh->prepare( q{ INSERT INTO table (name, phone) VALUES (?, ?) }); open FH, ") { chomp; my ($name, $phone) = split /,/; $sth->execute($name, $phone); } close FH; $dbh->commit; $dbh->disconnect; Here's how to convert fetched NULLs (undefined values) into empty strings: while($row = $sth->fetchrow_arrayref) { # this is a fast and simple way to deal with nulls: foreach (@$row) { $_ = '' unless defined } print "@$row\n"; } The C style quoting used in these examples 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. =head2 Threads and Thread Safety If the DBI is built using a Perl that has perl 5.005 style threads enabled then it will use a per-driver mutex to ensure that only one thread is with a driver at any one time. However perl 5.005 style threads are unstable and shoud not be used in production. Perl 5.7 and later support a new threading model called iThreads which is much more stable. However the DBI has not yet had any extra code added or testing done to make sure it works properly with iThreads. So at this time the DBI should not be used with iThreads in situations where more than one thread may enter a cloned instance of the DBI. It is I safe to have multiple threads load the DBI module I they have been created, but the DBI probably won't be providing any protection against multiple threads entering a databases own library code. As many databases don't have thread safe libraries that is likely to cause problems. Summary: Using DBI with perl threads of any kind is not recommended for production environments. =head2 Signal Handling and Canceling Operations The first thing to say is that signal handling in Perl is currently I safe. There is always a small risk of Perl crashing and/or core dumping when, or after, handling a signal. (The risk was reduced with 5.004_04 but is still present.) The two most common uses of signals in relation to the DBI are for canceling operations when the user types Ctrl-C (interrupt), and for implementing a timeout using C and C<$SIG{ALRM}>. To assist in implementing these operations, the DBI provides a C method for statement handles. The C method should abort the current operation and is designed to be called from a signal handler. However, it must be stressed that: a) few drivers implement this at the moment (the DBI provides a default method that just returns C); and b) even if implemented, there is still a possibility that the statement handle, and possibly the parent database handle, will not be usable afterwards. If C returns true, then it has successfully invoked the database engine's own cancel function. If it returns false, then C failed. If it returns C, then the database engine does not have cancel implemented. =head2 Subclassing the DBI DBI can be subclassed and extended just like any other object oriented module. Before we talk about how to do that, it's important to be clear about how the DBI classes and how they work together. By default C<$dbh = DBI->EC returns a $dbh blessed into the C class. And the C<$dbh->EC method returns an $sth blessed into the C class (actually it simply changes the last four characters of the calling handle class to be C<::st>). The leading 'C' is known as the 'root class' and the extra 'C<::db>' or 'C<::st>' are the 'handle type suffixes'. If you want to subclass the DBI you'll need to put your overriding methods into the appropriate classes. For example, if you want to use a root class of C and override the do(), prepare() and execute() methods, then your do() and prepare() methods should be in the C class and the execute() method should be in the C class. To setup the inheritance hierarchy the @ISA variable in C should include C and the @ISA variable in C should include C. The C root class itself isn't currently used for anything visible and so, apart from setting @ISA to include C, it should be left empty. So, having put your overriding methods into the right classes, and setup the inheritance hierarchy, how do you get the DBI to use them? You have two choices, either a static method call using the name of your subclass: $dbh = MySubDBI->connect(...); or specifying a C attribute: $dbh = DBI->connect(..., { RootClass => 'MySubDBI' }); The only difference between the two is that using an explicit RootClass attribute will make the DBI automatically attempt to load a module by that name (and not complain if such a module can't be found). If both forms are used then the attribute takes precedence. Here's a brief example of a DBI subclass. A more thorough example can be found in t/subclass.t in the DBI distribution. package MySubDBI; use strict; use DBI; use vars qw(@ISA); @ISA = qw(DBI); package MySubDBI::db; use vars qw(@ISA); @ISA = qw(DBI::db); sub prepare { my ($dbh, @args) = @_; my $sth = $dbh->SUPER::prepare(@args) or return; $sth->{private_mysubdbi_info} = { foo => 'bar' }; return $sth; } package MySubDBI::st; use vars qw(@ISA); @ISA = qw(DBI::st); sub fetch { my ($sth, @args) = @_; my $row = $sth->SUPER::fetch(@args) or return; do_something_magical_with_row_data($row) or return $sth->set_err(1234, "The magic failed", undef, "fetch"); return $row; } When calling a SUPER::method that returns a handle, be careful to check the return value before trying to do other things with it in your overridden method. This is especially important if you want to set a hash attribute on the handle, as Perl's autovivification will bite you by (in)conveniently creating an unblessed hashref, which your method will then return with usually baffling results later on. It's best to check right after the call and return undef immediately on error, just like DBI would and just like the example above. If your method needs to record an error it should call the set_err() method with the error code and error string, as shown in the example above. The error code and error string will be recorded in the handle and available via C<$h->EC and C<$DBI::errstr> etc. The set_err() method always returns an undef or empty list as approriate. Since your method should nearly always return an undef or empty list as soon as an error is detected it's handy to simply return what set_err() returns, as shown in the example above. If the handle has C, C, or C etc. set then the set_err() method will honour them. This means that if C is set then set_err() won't return in the normal way but will 'throw an exception' that can be caught with an C block. You can stash private data into DBI handles via C<$h->EC<{private_..._*}>. See the entry under L for info and important caveats. =head1 DEBUGGING In addition to the L method, you can enable the same trace information by setting the C environment variable before starting Perl. On Unix-like systems using a Bourne-like shell, you can do this easily on the command line: DBI_TRACE=2 perl your_test_script.pl If C is set to a non-numeric value, then it is assumed to be a file name and the trace level will be set to 2 with all trace output appended to that file. If the name begins with a number followed by an equal sign (C<=>), then the number and the equal sign are stripped off from the name, and the number is used to set the trace level. For example: DBI_TRACE=1=dbitrace.log perl your_test_script.pl See also the L method. It can sometimes be handy to compare trace files from two different runs of the same script. However using a tool like C doesn't work well because the trace file is full of object addresses that may differ each run. Here's a handy little command to strip those out: perl -pe 's/\b0x[\da-f]{6,}/0xNNNN/gi; s/\b[\da-f]{6,}//gi' =head1 WARNING AND ERROR MESSAGES =head2 Fatal Errors =over 4 =item Can't call method "prepare" without a package or object reference The C<$dbh> handle you're using to call C is probably undefined because the preceding C failed. You should always check the return status of DBI methods, or use the L attribute. =item Can't call method "execute" without a package or object reference The C<$sth> handle you're using to call C is probably undefined because the preceeding C failed. You should always check the return status of DBI methods, or use the L attribute. =item DBI/DBD internal version mismatch The DBD driver module was built with a different version of DBI than the one currently being used. You should rebuild the DBD module under the current version of DBI. (Some rare platforms require "static linking". On those platforms, there may be an old DBI or DBD driver version actually embedded in the Perl executable being used.) =item DBD driver has not implemented the AutoCommit attribute The DBD driver implementation is incomplete. Consult the author. =item Can't [sg]et %s->{%s}: unrecognised attribute You attempted to set or get an unknown attribute of a handle. Make sure you have spelled the attribute name correctly; case is significant (e.g., "Autocommit" is not the same as "AutoCommit"). =back =head1 Pure-Perl DBI A pure-perl emulation of the DBI is included in the distribution for people using pure-perl drivers who, for whatever reason, can't install the compiled DBI. See L. =head1 SEE ALSO =head2 Driver and Database Documentation Refer to the documentation for the DBD driver that you are using. Refer to the SQL Language Reference Manual for the database engine that you are using. =head2 Standards Reference Information More detailed information about the semantics of certain DBI methods that are based on ODBC and SQL/CLI standards is available on-line via microsoft.com, for ODBC, and www.jtc1sc32.org for the SQL/CLI standard: DBI method ODBC function SQL/CLI Working Draft ---------- ------------- --------------------- column_info SQLColumns Page 124 foreign_key_info SQLForeignKeys Page 163 get_info SQLGetInfo Page 214 primary_key_info SQLPrimaryKeys Page 254 table_info SQLTables Page 294 type_info SQLGetTypeInfo Page 239 For example, for ODBC information on SQLColumns you'd visit: http://msdn.microsoft.com/library/en-us/odbc/htm/odbcsqlcolumns.asp If that URL ceases to work then use the MSDN search facility at: http://search.microsoft.com/us/dev/ and search for C using the exact phrase option. The link you want will probably just be called C and will be part of the Data Access SDK. And for SQL/CLI standard information on SQLColumns you'd read page 124 of the (very large) SQL/CLI Working Draft available from: http://www.jtc1sc32.org/sc32/jtc1sc32.nsf/Attachments/7E3B41486BD99C3488256B410064C877/$FILE/32N0744T.PDF A hyperlinked, browsable version of the BNF syntax for SQL92 (plus Oracle 7 SQL and PL/SQL) is available here: http://cui.unige.ch/db-research/Enseignement/analyseinfo/SQL92/BNFindex.html A BNF syntax for SQL3 is available here: http://www.sqlstandards.org/SC32/WG3/Progression_Documents/Informal_working_drafts/iso-9075-2-1999.bnf =head2 Books and Journals Programming the Perl DBI, by Alligator Descartes and Tim Bunce. Programming Perl 2nd Ed. by Larry Wall, Tom Christiansen & Randal Schwartz. Learning Perl by Randal Schwartz. Dr Dobb's Journal, November 1996. The Perl Journal, April 1997. =head2 Perl Modules Index of DBI related modules available from CPAN: http://search.cpan.org/search?mode=module&query=DBIx%3A%3A http://search.cpan.org/search?mode=doc&query=DBI For a good comparison of RDBMS-OO mappers and some OO-RDBMS mappers (including Class::DBI, Alzabo, and DBIx::RecordSet in the former category and Tangram and SPOPS in the latter) see the Perl Object-Oriented Persistence project pages at: http://poop.sourceforge.net =head2 Manual Pages L, L, L =head2 Mailing List The I mailing list is the primary means of communication among users of the DBI and its related modules. For details send email to: dbi-users-help@perl.org There are typically between 700 and 900 messages per month. You have to subscribe in order to be able to post. However you can opt for a 'post-only' subscription. Mailing list archives (of variable quality) are held at: http://www.xray.mpe.mpg.de/mailing-lists/dbi/ http://groups.yahoo.com/group/dbi-users http://www.bitmechanic.com/mail-archives/dbi-users/ http://marc.theaimsgroup.com/?l=perl-dbi&r=1&w=2 http://www.mail-archive.com/dbi-users%40perl.org/ =head2 Assorted Related WWW Links The DBI "Home Page": http://dbi.perl.org/ Other DBI related links: http://tegan.deltanet.com/~phlip/DBUIdoc.html http://dc.pm.org/perl_db.html http://wdvl.com/Authoring/DB/Intro/toc.html http://www.hotwired.com/webmonkey/backend/tutorials/tutorial1.html http://bumppo.net/lists/macperl/1999/06/msg00197.html Other database related links: http://www.jcc.com/sql_stnd.html http://cuiwww.unige.ch/OSG/info/FreeDB/FreeDB.home.html Commercial and Data Warehouse Links http://www.dwinfocenter.org http://www.datawarehouse.com http://www.datamining.org http://www.olapcouncil.org http://www.idwa.org http://www.knowledgecenters.org/dwcenter.asp Recommended Perl Programming Links http://language.perl.com/style/ =head2 FAQ Please also read the DBI FAQ which is installed as a DBI::FAQ module. You can use I to read it by executing the C command. =head1 AUTHORS DBI by Tim Bunce. This pod text by Tim Bunce, J. Douglas Dunlop, Jonathan Leffler and others. Perl by Larry Wall and the C. =head1 COPYRIGHT The DBI module is Copyright (c) 1994-2002 Tim Bunce. Ireland. All rights reserved. You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file. =head1 ACKNOWLEDGEMENTS I would like to acknowledge the valuable contributions of the many people I have worked with on the DBI project, especially in the early years (1992-1994). In no particular order: Kevin Stock, Buzz Moschetti, Kurt Andersen, Ted Lemon, William Hails, Garth Kennedy, Michael Peppler, Neil S. Briscoe, Jeff Urlwin, David J. Hughes, Jeff Stander, Forrest D Whitcher, Larry Wall, Jeff Fried, Roy Johnson, Paul Hudson, Georg Rehfeld, Steve Sizemore, Ron Pool, Jon Meek, Tom Christiansen, Steve Baumgarten, Randal Schwartz, and a whole lot more. Then, of course, there are the poor souls who have struggled through untold and undocumented obstacles to actually implement DBI drivers. Among their ranks are Jochen Wiedmann, Alligator Descartes, Jonathan Leffler, Jeff Urlwin, Michael Peppler, Henrik Tougaard, Edwin Pratomo, Davide Migliavacca, Jan Pazdziora, Peter Haworth, Edmund Mergl, Steve Williams, Thomas Lowery, and Phlip Plumlee. Without them, the DBI would not be the practical reality it is today. I'm also especially grateful to Alligator Descartes for starting work on the "Programming the Perl DBI" book and letting me jump on board. Much of the DBI and DBD::Oracle was developed while I was Technical Director (CTO) of the Paul Ingram Group (www.ig.co.uk). So I'd especially like to thank Paul for his generosity and vision in supporting this work for many years. =head1 TRANSLATIONS A German translation of this manual (possibly slightly out of date) is available, thanks to O'Reilly, at: http://www.oreilly.de/catalog/perldbiger/ Some other translations: http://cronopio.net/perl/ - Spanish http://member.nifty.ne.jp/hippo2000/dbimemo.htm - Japanese =head1 SUPPORT / WARRANTY The DBI is free software. IT COMES WITHOUT WARRANTY OF ANY KIND. Commercial support for Perl and the DBI, DBD::Oracle and Oraperl modules can be arranged via The Perl Clinic. For more details visit: http://www.perlclinic.com For direct DBI and DBD::Oracle support, enhancement, and related work I am available for consultancy on standard commercial terms. =head1 TRAINING References to DBI related training resources. No recommendation implied. http://www.treepax.co.uk/ http://www.keller.com/dbweb/ =head1 FREQUENTLY ASKED QUESTIONS See the DBI FAQ for a more comprehensive list of FAQs. Use the C command to read it. =head2 How fast is the DBI? To measure the speed of the DBI and DBD::Oracle code, I modified DBD::Oracle so you can set an attribute that will cause the same row to be fetched from the row cache over and over again (without involving Oracle code but exercising *all* the DBI and DBD::Oracle code in the code path for a fetch). The results (on my lightly loaded old Sparc 10) fetching 50000 rows using: 1 while $csr->fetch; were: one field: 5300 fetches per cpu second (approx) ten fields: 4000 fetches per cpu second (approx) Obviously results will vary between platforms (newer faster platforms can reach around 50000 fetches per second), but it does give a feel for the maximum performance: fast. By way of comparison, using the code: 1 while @row = $csr->fetchrow_array; (C is roughly the same as C) gives: one field: 3100 fetches per cpu second (approx) ten fields: 1000 fetches per cpu second (approx) Notice the slowdown and the more dramatic impact of extra fields. (The fields were all one char long. The impact would be even bigger for longer strings.) Changing that slightly to represent actually doing something in Perl with the fetched data: while(@row = $csr->fetchrow_array) { $hash{++$i} = [ @row ]; } gives: ten fields: 500 fetches per cpu second (approx) That simple addition has *halved* the performance. I therefore conclude that DBI and DBD::Oracle overheads are small compared with Perl language overheads (and probably database overheads). So, if you think the DBI or your driver is slow, try replacing your fetch loop with just: 1 while $csr->fetch; and time that. If that helps then point the finger at your own code. If that doesn't help much then point the finger at the database, the platform, the network etc. But think carefully before pointing it at the DBI or your driver. (Having said all that, if anyone can show me how to make the DBI or drivers even more efficient, I'm all ears.) =head2 Why doesn't my CGI script work right? Read the information in the references below. Please do I post CGI related questions to the I mailing list (or to me). http://www.perl.com/cgi-bin/pace/pub/doc/FAQs/cgi/perl-cgi-faq.html http://www3.pair.com/webthing/docs/cgi/faqs/cgifaq.shtml http://www-genome.wi.mit.edu/WWW/faqs/www-security-faq.html http://www.boutell.com/faq/ http://www.perl.com/perl/faq/ General problems and good ideas: Use the CGI::ErrorWrap module. Remember that many env vars won't be set for CGI scripts. =head2 How can I maintain a WWW connection to a database? For information on the Apache httpd server and the C module see http://perl.apache.org/ =head2 What about ODBC? A DBD::ODBC module is available. =head2 Does the DBI have a year 2000 problem? No. The DBI has no knowledge or understanding of dates at all. Individual drivers (DBD::*) may have some date handling code but are unlikely to have year 2000 related problems within their code. However, your application code which I the DBI and DBD drivers may have year 2000 related problems if it has not been designed and written well. See also the "Does Perl have a year 2000 problem?" section of the Perl FAQ: http://www.perl.com/CPAN/doc/FAQs/FAQ/PerlFAQ.html =head1 OTHER RELATED WORK AND PERL MODULES =over 4 =item Apache::DBI by E.Mergl@bawue.de To be used with the Apache daemon together with an embedded Perl interpreter like C. Establishes a database connection which remains open for the lifetime of the HTTP daemon. This way the CGI connect and disconnect for every database access becomes superfluous. =item JDBC Server by Stuart 'Zen' Bishop zen@bf.rmit.edu.au The server is written in Perl. The client classes that talk to it are of course in Java. Thus, a Java applet or application will be able to comunicate via the JDBC API with any database that has a DBI driver installed. The URL used is in the form C. It seems to be very similar to some commercial products, such as jdbcKona. =item Remote Proxy DBD support As of DBI 1.02, a complete implementation of a DBD::Proxy driver and the DBI::ProxyServer are part of the DBI distribution. =item SQL Parser See also the SQL::Statement module, a very simple SQL parser and engine, base of the DBD::CSV driver. =back =cut