| 1 | # $Id: DBI.pm,v 10.37 2001/06/04 17:20:21 timbo Exp $ |
| 2 | # |
| 3 | # Copyright (c) 1994-2000 Tim Bunce England |
| 4 | # |
| 5 | # See COPYRIGHT section in pod text below for usage and distribution rights. |
| 6 | # |
| 7 | |
| 8 | require 5.004; |
| 9 | |
| 10 | BEGIN { |
| 11 | $DBI::VERSION = "1.18"; # ==> ALSO update the version in the pod text below! |
| 12 | } |
| 13 | |
| 14 | =head1 NAME |
| 15 | |
| 16 | DBI - Database independent interface for Perl |
| 17 | |
| 18 | =head1 SYNOPSIS |
| 19 | |
| 20 | use DBI; |
| 21 | |
| 22 | @driver_names = DBI->available_drivers; |
| 23 | @data_sources = DBI->data_sources($driver_name, \%attr); |
| 24 | |
| 25 | $dbh = DBI->connect($data_source, $username, $auth, \%attr); |
| 26 | |
| 27 | $rv = $dbh->do($statement); |
| 28 | $rv = $dbh->do($statement, \%attr); |
| 29 | $rv = $dbh->do($statement, \%attr, @bind_values); |
| 30 | |
| 31 | $ary_ref = $dbh->selectall_arrayref($statement); |
| 32 | $ary_ref = $dbh->selectall_hashref($statement); |
| 33 | |
| 34 | $ary_ref = $dbh->selectcol_arrayref($statement); |
| 35 | |
| 36 | $ary_ref = $dbh->selectrow_arrayref($statement); |
| 37 | @row_ary = $dbh->selectrow_array($statement); |
| 38 | |
| 39 | $sth = $dbh->prepare($statement); |
| 40 | $sth = $dbh->prepare_cached($statement); |
| 41 | |
| 42 | $rv = $sth->bind_param($p_num, $bind_value); |
| 43 | $rv = $sth->bind_param($p_num, $bind_value, $bind_type); |
| 44 | $rv = $sth->bind_param($p_num, $bind_value, \%attr); |
| 45 | |
| 46 | $rv = $sth->execute; |
| 47 | $rv = $sth->execute(@bind_values); |
| 48 | |
| 49 | $rc = $sth->bind_col($col_num, \$col_variable); |
| 50 | $rc = $sth->bind_columns(@list_of_refs_to_vars_to_bind); |
| 51 | |
| 52 | @row_ary = $sth->fetchrow_array; |
| 53 | $ary_ref = $sth->fetchrow_arrayref; |
| 54 | $hash_ref = $sth->fetchrow_hashref; |
| 55 | |
| 56 | $ary_ref = $sth->fetchall_arrayref; |
| 57 | |
| 58 | $rv = $sth->rows; |
| 59 | |
| 60 | $rc = $dbh->commit; |
| 61 | $rc = $dbh->rollback; |
| 62 | |
| 63 | $sql = $dbh->quote($string); |
| 64 | |
| 65 | $rc = $h->err; |
| 66 | $str = $h->errstr; |
| 67 | $rv = $h->state; |
| 68 | |
| 69 | $rc = $dbh->disconnect; |
| 70 | |
| 71 | I<This synopsis above only lists the major methods.> |
| 72 | |
| 73 | |
| 74 | =head2 GETTING HELP |
| 75 | |
| 76 | If you have questions about DBI, you can get help from |
| 77 | the I<dbi-users@perl.org> mailing list. |
| 78 | You can subscribe to the list by emailing: |
| 79 | |
| 80 | dbi-users-help@perl.org |
| 81 | |
| 82 | Also worth a visit is the DBI home page at: |
| 83 | |
| 84 | http://dbi.perl.org/ |
| 85 | |
| 86 | Before asking any questions, reread this document, consult the |
| 87 | archives and read the DBI FAQ. The archives are listed |
| 88 | at the end of this document. |
| 89 | The FAQ is installed as a DBI::FAQ module so |
| 90 | you can read it by executing C<perldoc DBI::FAQ>. |
| 91 | |
| 92 | Please note that Tim Bunce does not maintain the mailing lists or the |
| 93 | web page (generous volunteers do that). So please don't send mail |
| 94 | directly to him; he just doesn't have the time to answer questions |
| 95 | personally. The I<dbi-users> mailing list has lots of experienced |
| 96 | people who should be able to help you if you need it. |
| 97 | |
| 98 | =head2 NOTE |
| 99 | |
| 100 | This is the DBI specification that corresponds to the DBI version 1.17 |
| 101 | (C<$Date: 2001/06/04 17:20:21 $>). |
| 102 | |
| 103 | The DBI specification is evolving at a steady pace, so it's |
| 104 | important to check that you have the latest copy. The RECENT CHANGES |
| 105 | section below has a summary of user-visible changes. The F<Changes> |
| 106 | file supplied with the DBI holds more detailed change information. |
| 107 | |
| 108 | Note also that whenever the DBI changes, the drivers take some time to |
| 109 | catch up. Recent versions of the DBI have added new features |
| 110 | (marked I<NEW> in the text) that may not yet be supported by the drivers |
| 111 | you use. Talk to the authors of those drivers if you need the features. |
| 112 | |
| 113 | Extensions to the DBI and other DBI related modules use the C<DBIx::*> |
| 114 | namespace. See L</Naming Conventions and Name Space> and: |
| 115 | |
| 116 | http://www.perl.com/CPAN/modules/by-module/DBIx/ |
| 117 | |
| 118 | =head2 RECENT CHANGES |
| 119 | |
| 120 | Here is a brief summary of significant user-visible changes in recent versions. |
| 121 | (If a recent version isn't mentioned, it simply means that there were no |
| 122 | significant user-visible changes in that version.) |
| 123 | |
| 124 | =over 4 |
| 125 | |
| 126 | =item DBI 1.15 |
| 127 | |
| 128 | Added selectall_hashref, selectrow_hashref, selectrow_arrayref methods. |
| 129 | |
| 130 | =back |
| 131 | |
| 132 | =cut |
| 133 | |
| 134 | # The POD text continues at the end of the file. |
| 135 | |
| 136 | # DBI file-private variables |
| 137 | my %installed_rootclass; |
| 138 | |
| 139 | |
| 140 | { |
| 141 | package DBI; |
| 142 | |
| 143 | my $Revision = substr(q$Revision: 10.37 $, 10); |
| 144 | |
| 145 | use Carp; |
| 146 | use DynaLoader (); |
| 147 | use Exporter (); |
| 148 | |
| 149 | BEGIN { |
| 150 | @ISA = qw(Exporter DynaLoader); |
| 151 | |
| 152 | # Make some utility functions available if asked for |
| 153 | @EXPORT = (); # we export nothing by default |
| 154 | @EXPORT_OK = qw(%DBI %DBI_methods); # also populated by export_ok_tags: |
| 155 | %EXPORT_TAGS = ( |
| 156 | sql_types => [ qw( |
| 157 | SQL_ALL_TYPES |
| 158 | SQL_CHAR SQL_NUMERIC SQL_DECIMAL SQL_INTEGER SQL_SMALLINT |
| 159 | SQL_FLOAT SQL_REAL SQL_DOUBLE SQL_VARCHAR |
| 160 | SQL_DATE SQL_TIME SQL_TIMESTAMP |
| 161 | SQL_LONGVARCHAR SQL_BINARY SQL_VARBINARY SQL_LONGVARBINARY |
| 162 | SQL_BIGINT SQL_TINYINT |
| 163 | SQL_WCHAR SQL_WVARCHAR SQL_WLONGVARCHAR |
| 164 | SQL_BIT |
| 165 | ) ], |
| 166 | preparse_flags => [ qw( |
| 167 | DBIpp_cm_cs DBIpp_cm_hs DBIpp_cm_dd DBIpp_cm_br |
| 168 | DBIpp_ph_qm DBIpp_ph_cn DBIpp_ph_cs DBIpp_ph_sp |
| 169 | ) ], |
| 170 | utils => [ qw( |
| 171 | neat neat_list dump_results looks_like_number |
| 172 | ) ], |
| 173 | ); |
| 174 | Exporter::export_ok_tags('sql_types', 'utils'); |
| 175 | |
| 176 | $DBI::dbi_debug = $ENV{DBI_TRACE} || $ENV{PERL_DBI_DEBUG} || 0; |
| 177 | |
| 178 | # If you get an error here like "Can't find loadable object ..." |
| 179 | # then you haven't installed the DBI correctly. Read the README |
| 180 | # then install it again. |
| 181 | bootstrap DBI; |
| 182 | |
| 183 | } |
| 184 | *trace_msg = \&DBD::_::common::trace_msg; |
| 185 | |
| 186 | use strict; |
| 187 | |
| 188 | my $connect_via = "connect"; |
| 189 | |
| 190 | # check if user wants a persistent database connection ( Apache + mod_perl ) |
| 191 | if ($INC{'Apache/DBI.pm'} && substr($ENV{GATEWAY_INTERFACE}||'',0,8) eq 'CGI-Perl') { |
| 192 | $connect_via = "Apache::DBI::connect"; |
| 193 | DBI->trace_msg("DBI connect via $INC{'Apache/DBI.pm'}\n"); |
| 194 | } |
| 195 | |
| 196 | |
| 197 | if ($DBI::dbi_debug) { |
| 198 | @DBI::dbi_debug = ($DBI::dbi_debug); |
| 199 | |
| 200 | if ($DBI::dbi_debug !~ m/^\d$/) { |
| 201 | # dbi_debug is a file name to write trace log to. |
| 202 | # Default level is 2 but if file starts with "digits=" then the |
| 203 | # digits (and equals) are stripped off and used as the level |
| 204 | unshift @DBI::dbi_debug, 2; |
| 205 | @DBI::dbi_debug = ($1,$2) if $DBI::dbi_debug =~ m/^(\d+)=(.*)/; |
| 206 | } |
| 207 | DBI->trace(@DBI::dbi_debug); |
| 208 | } |
| 209 | |
| 210 | %DBI::installed_drh = (); # maps driver names to installed driver handles |
| 211 | |
| 212 | |
| 213 | # Setup special DBI dynamic variables. See DBI::var::FETCH for details. |
| 214 | # These are dynamically associated with the last handle used. |
| 215 | tie $DBI::err, 'DBI::var', '*err'; # special case: referenced via IHA list |
| 216 | tie $DBI::state, 'DBI::var', '"state'; # special case: referenced via IHA list |
| 217 | tie $DBI::lasth, 'DBI::var', '!lasth'; # special case: return boolean |
| 218 | tie $DBI::errstr, 'DBI::var', '&errstr'; # call &errstr in last used pkg |
| 219 | tie $DBI::rows, 'DBI::var', '&rows'; # call &rows in last used pkg |
| 220 | sub DBI::var::TIESCALAR{ my $var = $_[1]; bless \$var, 'DBI::var'; } |
| 221 | sub DBI::var::STORE { Carp::croak("Can't modify \$DBI::${$_[0]} special variable") } |
| 222 | sub DBI::var::DESTROY { } |
| 223 | |
| 224 | { package DBI::DBI_tie; # used to catch DBI->{Attrib} mistake |
| 225 | sub TIEHASH { bless {} } |
| 226 | sub STORE { Carp::carp("DBI->{$_[1]} is invalid syntax (you probably want \$h->{$_[1]})");} |
| 227 | *FETCH = \&STORE; |
| 228 | } |
| 229 | tie %DBI::DBI => 'DBI::DBI_tie'; |
| 230 | |
| 231 | |
| 232 | # --- Dynamically create the DBI Standard Interface |
| 233 | |
| 234 | my $std = undef; |
| 235 | my $keeperr = { O=>0x04 }; |
| 236 | |
| 237 | my @TieHash_IF = ( # Generic Tied Hash Interface |
| 238 | 'STORE' => { O=>0x10 }, |
| 239 | 'FETCH' => $keeperr, |
| 240 | 'FIRSTKEY'=> $keeperr, |
| 241 | 'NEXTKEY' => $keeperr, |
| 242 | 'EXISTS' => $keeperr, |
| 243 | 'CLEAR' => $keeperr, |
| 244 | 'DESTROY' => undef, # hardwired internally |
| 245 | ); |
| 246 | my @Common_IF = ( # Interface functions common to all DBI classes |
| 247 | func => { O=>0x06 }, |
| 248 | event => { U =>[2,0,'$type, @args'], O=>0x04 }, |
| 249 | 'trace' => { U =>[1,3,'[$trace_level, [$filename]]'], O=>0x04 }, |
| 250 | trace_msg => { U =>[2,3,'$message_text [, $min_level ]' ], O=>0x04, T=>8 }, |
| 251 | debug => { U =>[1,2,'[$debug_level]'], O=>0x04 }, # old name for trace |
| 252 | private_data => { U =>[1,1], O=>0x04 }, |
| 253 | err => $keeperr, |
| 254 | errstr => $keeperr, |
| 255 | state => { U =>[1,1], O=>0x04 }, |
| 256 | _not_impl => $std, |
| 257 | ); |
| 258 | |
| 259 | %DBI::DBI_methods = ( # Define the DBI interface methods per class: |
| 260 | |
| 261 | dr => { # Database Driver Interface |
| 262 | @Common_IF, |
| 263 | @TieHash_IF, |
| 264 | 'connect' => { U =>[1,5,'[$db [,$user [,$passwd [,\%attr]]]]'], H=>3 }, |
| 265 | 'connect_cached'=>{U=>[1,5,'[$db [,$user [,$passwd [,\%attr]]]]'], H=>3 }, |
| 266 | 'disconnect_all'=>{ U =>[1,1] }, |
| 267 | data_sources => { U =>[1,2,'[\%attr]' ] }, |
| 268 | default_user => { U =>[3,4,'$user, $pass [, \%attr]' ] }, |
| 269 | }, |
| 270 | db => { # Database Session Class Interface |
| 271 | @Common_IF, |
| 272 | @TieHash_IF, |
| 273 | commit => { U =>[1,1] }, |
| 274 | rollback => { U =>[1,1] }, |
| 275 | 'do' => { U =>[2,0,'$statement [, \%attr [, @bind_params ] ]'] }, |
| 276 | prepare => { U =>[2,3,'$statement [, \%attr]'] }, |
| 277 | prepare_cached => { U =>[2,4,'$statement [, \%attr [, $allow_active ] ]'] }, |
| 278 | selectrow_array => { U =>[2,0,'$statement [, \%attr [, @bind_params ] ]'] }, |
| 279 | selectrow_arrayref=>{U =>[2,0,'$statement [, \%attr [, @bind_params ] ]'] }, |
| 280 | selectrow_hashref=>{ U =>[2,0,'$statement [, \%attr [, @bind_params ] ]'] }, |
| 281 | selectall_arrayref=>{U =>[2,0,'$statement [, \%attr [, @bind_params ] ]'] }, |
| 282 | selectall_hashref=>{ U =>[2,0,'$statement [, \%attr [, @bind_params ] ]'] }, |
| 283 | selectcol_arrayref=>{U =>[2,0,'$statement [, \%attr [, @bind_params ] ]'] }, |
| 284 | handler => { U =>[2,2,'\&handler'] }, |
| 285 | ping => { U =>[1,1] }, |
| 286 | disconnect => { U =>[1,1] }, |
| 287 | quote => { U =>[2,3, '$string [, $data_type ]' ], O=>0x30 }, |
| 288 | rows => $keeperr, |
| 289 | |
| 290 | tables => { U =>[1,2,'[ \%attr ]' ] }, |
| 291 | table_info => { U =>[1,2,'[ \%attr ]' ] }, |
| 292 | type_info_all => { U =>[1,1] }, |
| 293 | type_info => { U =>[1,2] }, |
| 294 | get_info => { U =>[2,2] }, |
| 295 | }, |
| 296 | st => { # Statement Class Interface |
| 297 | @Common_IF, |
| 298 | @TieHash_IF, |
| 299 | bind_col => { U =>[3,4,'$column, \\$var [, \%attr]'] }, |
| 300 | bind_columns => { U =>[2,0,'\\$var1 [, \\$var2, ...]'] }, |
| 301 | bind_param => { U =>[3,4,'$parameter, $var [, \%attr]'] }, |
| 302 | bind_param_inout=> { U =>[4,5,'$parameter, \\$var, $maxlen, [, \%attr]'] }, |
| 303 | execute => { U =>[1,0,'[@args]'] }, |
| 304 | |
| 305 | fetch => undef, # alias for fetchrow_arrayref |
| 306 | fetchrow_arrayref => undef, |
| 307 | fetchrow_hashref => undef, |
| 308 | fetchrow_array => undef, |
| 309 | fetchrow => undef, # old alias for fetchrow_array |
| 310 | |
| 311 | fetchall_arrayref => { U =>[1,2] }, |
| 312 | |
| 313 | blob_read => { U =>[4,5,'$field, $offset, $len [, \\$buf [, $bufoffset]]'] }, |
| 314 | blob_copy_to_file => { U =>[3,3,'$field, $filename_or_handleref'] }, |
| 315 | dump_results => { U =>[1,5,'$maxfieldlen, $linesep, $fieldsep, $filehandle'] }, |
| 316 | more_results => { U =>[1,1] }, |
| 317 | finish => { U =>[1,1] }, |
| 318 | cancel => { U =>[1,1] }, |
| 319 | rows => $keeperr, |
| 320 | |
| 321 | _get_fbav => undef, |
| 322 | _set_fbav => { T=>6 }, |
| 323 | }, |
| 324 | ); |
| 325 | |
| 326 | my($class, $method); |
| 327 | foreach $class (keys %DBI::DBI_methods){ |
| 328 | my %pkgif = %{ $DBI::DBI_methods{$class} }; |
| 329 | foreach $method (keys %pkgif){ |
| 330 | DBI->_install_method("DBI::${class}::$method", 'DBI.pm', |
| 331 | $pkgif{$method}); |
| 332 | } |
| 333 | } |
| 334 | |
| 335 | # End of init code |
| 336 | |
| 337 | |
| 338 | END { |
| 339 | return unless defined &DBI::trace_msg; # return unless bootstrap'd ok |
| 340 | local ($!,$?); |
| 341 | DBI->trace_msg(" -- DBI::END\n", 2); |
| 342 | # Let drivers know why we are calling disconnect_all: |
| 343 | $DBI::PERL_ENDING = $DBI::PERL_ENDING = 1; # avoid typo warning |
| 344 | DBI->disconnect_all() if %DBI::installed_drh; |
| 345 | } |
| 346 | |
| 347 | |
| 348 | |
| 349 | # --- The DBI->connect Front Door methods |
| 350 | |
| 351 | sub connect_cached { |
| 352 | # XXX we expect Apache::DBI users to still call connect() |
| 353 | my ($class, $dsn, $user, $pass, $attr) = @_; |
| 354 | ($attr ||= {})->{dbi_connect_method} = 'connect_cached'; |
| 355 | return $class->connect($dsn, $user, $pass, $attr); |
| 356 | } |
| 357 | |
| 358 | sub connect { |
| 359 | my $class = shift; |
| 360 | my($dsn, $user, $pass, $attr, $old_driver) = @_; |
| 361 | my $driver; |
| 362 | my $dbh; |
| 363 | |
| 364 | # switch $old_driver<->$attr if called in old style |
| 365 | ($old_driver, $attr) = ($attr, $old_driver) if $attr and !ref($attr); |
| 366 | |
| 367 | my $connect_meth = (ref $attr) ? $attr->{dbi_connect_method} : undef; |
| 368 | $connect_meth ||= $connect_via; # fallback to default |
| 369 | |
| 370 | $dsn ||= $ENV{DBI_DSN} || $ENV{DBI_DBNAME} || '' unless $old_driver; |
| 371 | |
| 372 | if ($DBI::dbi_debug) { |
| 373 | local $^W = 0; |
| 374 | pop @_ if $connect_meth ne 'connect'; |
| 375 | my @args = @_; $args[2] = '****'; # hide password |
| 376 | DBI->trace_msg(" -> $class->$connect_meth(".join(", ",@args).")\n"); |
| 377 | } |
| 378 | Carp::croak('Usage: $class->connect([$dsn [,$user [,$passwd [,\%attr]]]])') |
| 379 | if (ref $old_driver or ($attr and not ref $attr) or ref $pass); |
| 380 | |
| 381 | # extract dbi:driver prefix from $dsn into $1 |
| 382 | $dsn =~ s/^dbi:(\w*?)(?:\((.*?)\))?://i |
| 383 | or '' =~ /()/; # ensure $1 etc are empty if match fails |
| 384 | my $driver_attrib_spec = $2; |
| 385 | |
| 386 | # Set $driver. Old style driver, if specified, overrides new dsn style. |
| 387 | $driver = $old_driver || $1 || $ENV{DBI_DRIVER} |
| 388 | or Carp::croak("Can't connect(@_), no database driver specified " |
| 389 | ."and DBI_DSN env var not set"); |
| 390 | |
| 391 | if ($ENV{DBI_AUTOPROXY} && $driver ne 'Proxy' && $driver ne 'Switch') { |
| 392 | $dsn = "$ENV{DBI_AUTOPROXY};dsn=dbi:$driver:$dsn"; |
| 393 | $driver = 'Proxy'; |
| 394 | DBI->trace_msg(" DBI_AUTOPROXY: dbi:$driver:$dsn\n"); |
| 395 | } |
| 396 | |
| 397 | unless ($old_driver) { # new-style connect so new default semantics |
| 398 | $driver_attrib_spec = { split /\s*=>?\s*|\s*,\s*/, $driver_attrib_spec } |
| 399 | if $driver_attrib_spec; |
| 400 | $attr = { |
| 401 | PrintError=>1, AutoCommit=>1, |
| 402 | ref $attr ? %$attr : (), |
| 403 | ref $driver_attrib_spec ? %$driver_attrib_spec : (), |
| 404 | }; |
| 405 | # XXX to be enabled for DBI v2.0 |
| 406 | #Carp::carp("AutoCommit attribute not specified in DBI->connect") |
| 407 | # if $^W && !defined($attr->{AutoCommit}); |
| 408 | } |
| 409 | |
| 410 | my $drh = $class->install_driver($driver) |
| 411 | || die "panic: install_driver($driver) failed"; |
| 412 | |
| 413 | ($user, $pass) = $drh->default_user($user, $pass, $attr) |
| 414 | if !(defined $user && defined $pass); |
| 415 | |
| 416 | unless ($dbh = $drh->$connect_meth($dsn, $user, $pass, $attr)) { |
| 417 | my $msg = "$class->connect($dsn) failed: ".$drh->errstr; |
| 418 | if (ref $attr) { |
| 419 | Carp::croak($msg) if $attr->{RaiseError}; |
| 420 | Carp::carp ($msg) if $attr->{PrintError}; |
| 421 | } |
| 422 | DBI->trace_msg(" $msg\n"); |
| 423 | $! = 0; # for the daft people who do DBI->connect(...) || die "$!"; |
| 424 | return undef; |
| 425 | } |
| 426 | |
| 427 | # XXX this is inelegant but practical in the short term, sigh. |
| 428 | if ($installed_rootclass{$class}) { |
| 429 | $dbh->{RootClass} = $class; |
| 430 | bless $dbh => $class.'::db'; |
| 431 | my ($outer, $inner) = DBI::_handles($dbh); |
| 432 | bless $inner => $class.'::db'; |
| 433 | } |
| 434 | |
| 435 | if (ref $attr) { |
| 436 | my %a = %$attr; |
| 437 | my $a; |
| 438 | # handle these attributes first |
| 439 | foreach $a (qw(RaiseError PrintError AutoCommit)) { |
| 440 | next unless exists $a{$a}; |
| 441 | $dbh->{$a} = $a{$a}; |
| 442 | delete $a{$a}; |
| 443 | } |
| 444 | foreach $a (keys %a) { |
| 445 | $dbh->{$a} = $a{$a}; |
| 446 | } |
| 447 | } |
| 448 | DBI->trace_msg(" <- connect= $dbh\n") if $DBI::dbi_debug; |
| 449 | |
| 450 | $dbh; |
| 451 | } |
| 452 | |
| 453 | |
| 454 | sub disconnect_all { |
| 455 | foreach(keys %DBI::installed_drh){ |
| 456 | my $drh = $DBI::installed_drh{$_}; |
| 457 | next unless ref $drh; # avoid problems on premature death |
| 458 | $drh->disconnect_all(); |
| 459 | } |
| 460 | } |
| 461 | |
| 462 | |
| 463 | sub disconnect { # a regular beginners bug |
| 464 | Carp::croak("DBI->disconnect is not a DBI method. Read the DBI manual."); |
| 465 | } |
| 466 | |
| 467 | |
| 468 | sub install_driver { # croaks on failure |
| 469 | my $class = shift; |
| 470 | my($driver, $attr) = @_; |
| 471 | my $drh; |
| 472 | local $@; |
| 473 | |
| 474 | $driver ||= $ENV{DBI_DRIVER} || ''; |
| 475 | |
| 476 | # allow driver to be specified as a 'dbi:driver:' string |
| 477 | $driver = $1 if $driver =~ s/^DBI:(.*?)://i; |
| 478 | |
| 479 | Carp::croak("usage: $class->install_driver(\$driver [, \%attr])") |
| 480 | unless ($driver and @_<=3); |
| 481 | |
| 482 | # already installed |
| 483 | return $drh if $drh = $DBI::installed_drh{$driver}; |
| 484 | |
| 485 | DBI->trace_msg(" -> $class->install_driver($driver" |
| 486 | .") for perl=$] pid=$$ ruid=$< euid=$>\n") |
| 487 | if $DBI::dbi_debug; |
| 488 | |
| 489 | # --- load the code |
| 490 | my $driver_class = "DBD::$driver"; |
| 491 | eval "package DBI::_firesafe; require $driver_class"; |
| 492 | if ($@) { |
| 493 | my $err = $@; |
| 494 | my $advice = ""; |
| 495 | if ($err =~ /Can't find loadable object/) { |
| 496 | $advice = "Perhaps DBD::$driver was statically linked into a new perl binary." |
| 497 | ."\nIn which case you need to use that new perl binary." |
| 498 | ."\nOr perhaps only the .pm file was installed but not the shared object file." |
| 499 | } |
| 500 | elsif ($err =~ /Can't locate.*?DBD\/$driver\.pm in \@INC/) { |
| 501 | my @drv = DBI->available_drivers(1); |
| 502 | $advice = "Perhaps the DBD::$driver perl module hasn't been fully installed,\n" |
| 503 | ."or perhaps the capitalisation of '$driver' isn't right.\n" |
| 504 | ."Available drivers: ".join(", ", @drv)."."; |
| 505 | } |
| 506 | elsif ($err =~ /Can't load .*? for module DBD::/) { |
| 507 | $advice = "Perhaps a required shared library or dll isn't installed where expected"; |
| 508 | } |
| 509 | elsif ($err =~ /Can't locate .*? in \@INC/) { |
| 510 | $advice = "Perhaps a module that DBD::$driver requires hasn't been fully installed"; |
| 511 | } |
| 512 | Carp::croak("install_driver($driver) failed: $err$advice\n"); |
| 513 | } |
| 514 | if ($DBI::dbi_debug) { |
| 515 | no strict 'refs'; |
| 516 | (my $driver_file = $driver_class) =~ s/::/\//g; |
| 517 | my $dbd_ver = ${"$driver_class\::VERSION"} || "undef"; |
| 518 | DBI->trace_msg(" install_driver: $driver_class version $dbd_ver" |
| 519 | ." loaded from $INC{qq($driver_file.pm)}\n"); |
| 520 | } |
| 521 | |
| 522 | # --- do some behind-the-scenes checks and setups on the driver |
| 523 | _setup_driver($driver_class); |
| 524 | |
| 525 | # --- run the driver function |
| 526 | $drh = eval { $driver_class->driver($attr || {}) }; |
| 527 | unless ($drh && ref $drh && !$@) { |
| 528 | my $advice = ""; |
| 529 | # catch people on case in-sensitive systems using the wrong case |
| 530 | $advice = "\nPerhaps the capitalisation of DBD '$driver' isn't right." |
| 531 | if $@ =~ /locate object method/; |
| 532 | croak("$driver_class initialisation failed: $@$advice"); |
| 533 | } |
| 534 | |
| 535 | $DBI::installed_drh{$driver} = $drh; |
| 536 | DBI->trace_msg(" <- install_driver= $drh\n") if $DBI::dbi_debug; |
| 537 | $drh; |
| 538 | } |
| 539 | |
| 540 | *driver = \&install_driver; # currently an alias, may change |
| 541 | |
| 542 | |
| 543 | sub _setup_driver { |
| 544 | my $driver_class = shift; |
| 545 | my $type; |
| 546 | foreach $type (qw(dr db st)){ |
| 547 | my $class = $driver_class."::$type"; |
| 548 | no strict 'refs'; |
| 549 | push @{"${class}::ISA"}, "DBD::_::$type"; |
| 550 | push @{"${class}_mem::ISA"}, "DBD::_mem::$type"; |
| 551 | } |
| 552 | } |
| 553 | |
| 554 | |
| 555 | sub init_rootclass { |
| 556 | my $rootclass = shift; |
| 557 | no strict 'refs'; |
| 558 | croak("Can't init '$rootclass' without '$rootclass\::db' class.") |
| 559 | unless defined ${"$rootclass\::db::"}{ISA}; |
| 560 | |
| 561 | $installed_rootclass{$rootclass} = 1; |
| 562 | # may do checks on ::db and ::st classes later |
| 563 | return 1; |
| 564 | } |
| 565 | |
| 566 | |
| 567 | *internal = \&DBD::Switch::dr::driver; |
| 568 | #sub internal { return DBD::Switch::dr::driver(@_); } |
| 569 | |
| 570 | |
| 571 | sub available_drivers { |
| 572 | my($quiet) = @_; |
| 573 | my(@drivers, $d, $f); |
| 574 | local(*DBI::DIR, $@); |
| 575 | my(%seen_dir, %seen_dbd); |
| 576 | my $haveFileSpec = eval { require File::Spec }; |
| 577 | foreach $d (@INC){ |
| 578 | chomp($d); # Perl 5 beta 3 bug in #!./perl -Ilib from Test::Harness |
| 579 | my $dbd_dir = |
| 580 | ($haveFileSpec ? File::Spec->catdir($d, 'DBD') : "$d/DBD"); |
| 581 | next unless -d $dbd_dir; |
| 582 | next if $seen_dir{$d}; |
| 583 | $seen_dir{$d} = 1; |
| 584 | # XXX we have a problem here with case insensitive file systems |
| 585 | # XXX since we can't tell what case must be used when loading. |
| 586 | opendir(DBI::DIR, $dbd_dir) || Carp::carp "opendir $dbd_dir: $!\n"; |
| 587 | foreach $f (readdir(DBI::DIR)){ |
| 588 | next unless $f =~ s/\.pm$//; |
| 589 | next if $f eq 'NullP' || $f eq 'Sponge'; |
| 590 | if ($seen_dbd{$f}){ |
| 591 | Carp::carp "DBD::$f in $d is hidden by DBD::$f in $seen_dbd{$f}\n" |
| 592 | unless $quiet; |
| 593 | } else { |
| 594 | push(@drivers, $f); |
| 595 | } |
| 596 | $seen_dbd{$f} = $d; |
| 597 | } |
| 598 | closedir(DBI::DIR); |
| 599 | } |
| 600 | return sort @drivers; |
| 601 | } |
| 602 | |
| 603 | sub data_sources { |
| 604 | my ($class, $driver, @attr) = @_; |
| 605 | my $drh = $class->install_driver($driver); |
| 606 | my @ds = $drh->data_sources(@attr); |
| 607 | return @ds; |
| 608 | } |
| 609 | |
| 610 | sub neat_list { |
| 611 | my ($listref, $maxlen, $sep) = @_; |
| 612 | $maxlen = 0 unless defined $maxlen; # 0 == use internal default |
| 613 | $sep = ", " unless defined $sep; |
| 614 | join($sep, map { neat($_,$maxlen) } @$listref); |
| 615 | } |
| 616 | |
| 617 | |
| 618 | sub dump_results { # also aliased as a method in DBD::_::st |
| 619 | my ($sth, $maxlen, $lsep, $fsep, $fh) = @_; |
| 620 | return 0 unless $sth; |
| 621 | $maxlen ||= 35; |
| 622 | $lsep ||= "\n"; |
| 623 | $fh ||= \*STDOUT; |
| 624 | my $rows = 0; |
| 625 | my $ref; |
| 626 | while($ref = $sth->fetch) { |
| 627 | print $fh $lsep if $rows++ and $lsep; |
| 628 | my $str = neat_list($ref,$maxlen,$fsep); |
| 629 | print $fh $str; # done on two lines to avoid 5.003 errors |
| 630 | } |
| 631 | print $fh "\n$rows rows".($DBI::err ? " ($DBI::err: $DBI::errstr)" : "")."\n"; |
| 632 | $rows; |
| 633 | } |
| 634 | |
| 635 | |
| 636 | |
| 637 | sub connect_test_perf { |
| 638 | my($class, $dsn,$dbuser,$dbpass, $attr) = @_; |
| 639 | croak("connect_test_perf needs hash ref as fourth arg") unless ref $attr; |
| 640 | # these are non standard attributes just for this special method |
| 641 | my $loops ||= $attr->{dbi_loops} || 5; |
| 642 | my $par ||= $attr->{dbi_par} || 1; # parallelism |
| 643 | my $verb ||= $attr->{dbi_verb} || 1; |
| 644 | print "$dsn: testing $loops sets of $par connections:\n"; |
| 645 | require Benchmark; |
| 646 | require "FileHandle.pm"; # don't let toke.c create empty FileHandle package |
| 647 | $| = 1; |
| 648 | my $t0 = new Benchmark; # not currently used |
| 649 | my $drh = $class->install_driver($dsn) or Carp::croak("Can't install $dsn driver\n"); |
| 650 | my $t1 = new Benchmark; |
| 651 | my $loop; |
| 652 | for $loop (1..$loops) { |
| 653 | my @cons; |
| 654 | print "Connecting... " if $verb; |
| 655 | for (1..$par) { |
| 656 | print "$_ "; |
| 657 | push @cons, ($drh->connect($dsn,$dbuser,$dbpass) |
| 658 | or Carp::croak("Can't connect # $_: $DBI::errstr\n")); |
| 659 | } |
| 660 | print "\nDisconnecting...\n" if $verb; |
| 661 | for (@cons) { |
| 662 | $_->disconnect or warn "bad disconnect $DBI::errstr" |
| 663 | } |
| 664 | } |
| 665 | my $t2 = new Benchmark; |
| 666 | my $td = Benchmark::timediff($t2, $t1); |
| 667 | printf "Made %2d connections in %s\n", $loops*$par, Benchmark::timestr($td); |
| 668 | print "\n"; |
| 669 | return $td; |
| 670 | } |
| 671 | |
| 672 | |
| 673 | # Help people doing DBI->errstr, might even document it one day |
| 674 | # XXX probably best moved to cheaper XS code |
| 675 | sub err { $DBI::err } |
| 676 | sub errstr { $DBI::errstr } |
| 677 | |
| 678 | |
| 679 | # --- Private Internal Function for Creating New DBI Handles |
| 680 | |
| 681 | sub _new_handle { |
| 682 | my ($class, $parent, $attr, $imp_data, $imp_class) = @_; |
| 683 | |
| 684 | Carp::croak('Usage: DBI::_new_handle' |
| 685 | .'($class_name, parent_handle, \%attr, $imp_data)'."\n" |
| 686 | .'got: ('.join(", ",$class, $parent, $attr, $imp_data).")\n") |
| 687 | unless (@_ == 5 and (!$parent or ref $parent) |
| 688 | and ref $attr eq 'HASH' |
| 689 | and $imp_class); |
| 690 | |
| 691 | $attr->{ImplementorClass} = $imp_class |
| 692 | or Carp::croak("_new_handle($class): 'ImplementorClass' attribute not given"); |
| 693 | |
| 694 | DBI->trace_msg(" New $class (for $imp_class, parent=$parent, id=".($imp_data||'').")\n") |
| 695 | if $DBI::dbi_debug >= 3; |
| 696 | |
| 697 | # This is how we create a DBI style Object: |
| 698 | my(%hash, $i, $h); |
| 699 | $i = tie %hash, $class, $attr; # ref to inner hash (for driver) |
| 700 | $h = bless \%hash, $class; # ref to outer hash (for application) |
| 701 | # The above tie and bless may migrate down into _setup_handle()... |
| 702 | # Now add magic so DBI method dispatch works |
| 703 | DBI::_setup_handle($h, $imp_class, $parent, $imp_data); |
| 704 | |
| 705 | return $h unless wantarray; |
| 706 | ($h, $i); |
| 707 | } |
| 708 | # minimum constructors for the tie's (alias to XS version) |
| 709 | sub DBI::st::TIEHASH { bless $_[1] => $_[0] }; |
| 710 | *DBI::dr::TIEHASH = \&DBI::st::TIEHASH; |
| 711 | *DBI::db::TIEHASH = \&DBI::st::TIEHASH; |
| 712 | |
| 713 | |
| 714 | # These three special constructors are called by the drivers |
| 715 | # The way they are called is likey to change. |
| 716 | |
| 717 | sub _new_drh { # called by DBD::<drivername>::driver() |
| 718 | my ($class, $initial_attr, $imp_data) = @_; |
| 719 | # Provide default storage for State,Err and Errstr. |
| 720 | # Note that these are shared by all child handles by default! XXX |
| 721 | # State must be undef to get automatic faking in DBI::var::FETCH |
| 722 | my ($h_state_store, $h_err_store, $h_errstr_store) = (undef, 0, ''); |
| 723 | my $attr = { |
| 724 | # these attributes get copied down to child handles by default |
| 725 | 'Handlers' => [], |
| 726 | 'State' => \$h_state_store, # Holder for DBI::state |
| 727 | 'Err' => \$h_err_store, # Holder for DBI::err |
| 728 | 'Errstr' => \$h_errstr_store, # Holder for DBI::errstr |
| 729 | 'Debug' => 0, |
| 730 | %$initial_attr, |
| 731 | }; |
| 732 | _new_handle('DBI::dr', '', $attr, $imp_data, $class); |
| 733 | } |
| 734 | |
| 735 | sub _new_dbh { # called by DBD::<drivername>::dr::connect() |
| 736 | my ($drh, $attr, $imp_data) = @_; |
| 737 | my $imp_class = $drh->{ImplementorClass} |
| 738 | or Carp::croak("DBI _new_dbh: $drh has no ImplementorClass"); |
| 739 | substr($imp_class,-4,4) = '::db'; |
| 740 | my $app_class = ref $drh; |
| 741 | substr($app_class,-4,4) = '::db'; |
| 742 | _new_handle($app_class, $drh, $attr||{}, $imp_data, $imp_class); |
| 743 | } |
| 744 | |
| 745 | sub _new_sth { # called by DBD::<drivername>::db::prepare) |
| 746 | my ($dbh, $attr, $imp_data) = @_; |
| 747 | my $imp_class = $dbh->{ImplementorClass} |
| 748 | or Carp::croak("DBI _new_sth: $dbh has no ImplementorClass"); |
| 749 | substr($imp_class,-4,4) = '::st'; |
| 750 | my $app_class = ref $dbh; |
| 751 | substr($app_class,-4,4) = '::st'; |
| 752 | _new_handle($app_class, $dbh, $attr, $imp_data, $imp_class); |
| 753 | } |
| 754 | |
| 755 | |
| 756 | } # end of DBI package scope |
| 757 | |
| 758 | |
| 759 | |
| 760 | # -------------------------------------------------------------------- |
| 761 | # === The internal DBI Switch pseudo 'driver' class === |
| 762 | |
| 763 | { package DBD::Switch::dr; |
| 764 | DBI::_setup_driver('DBD::Switch'); # sets up @ISA |
| 765 | require Carp; |
| 766 | |
| 767 | $imp_data_size = 0; |
| 768 | $imp_data_size = 0; # avoid typo warning |
| 769 | $err = 0; |
| 770 | |
| 771 | sub driver { |
| 772 | return $drh if $drh; # a package global |
| 773 | |
| 774 | my $inner; |
| 775 | ($drh, $inner) = DBI::_new_drh('DBD::Switch::dr', { |
| 776 | 'Name' => 'Switch', |
| 777 | 'Version' => $DBI::VERSION, |
| 778 | # the Attribution is defined as a sub as an example |
| 779 | 'Attribution' => sub { "DBI $DBI::VERSION by Tim Bunce" }, |
| 780 | }, \$err); |
| 781 | Carp::croak("DBD::Switch init failed!") unless ($drh && $inner); |
| 782 | return $drh; |
| 783 | } |
| 784 | |
| 785 | sub FETCH { |
| 786 | my($drh, $key) = @_; |
| 787 | return DBI->trace if $key eq 'DebugDispatch'; |
| 788 | return undef if $key eq 'DebugLog'; # not worth fetching, sorry |
| 789 | return $drh->DBD::_::dr::FETCH($key); |
| 790 | undef; |
| 791 | } |
| 792 | sub STORE { |
| 793 | my($drh, $key, $value) = @_; |
| 794 | if ($key eq 'DebugDispatch') { |
| 795 | DBI->trace($value); |
| 796 | } elsif ($key eq 'DebugLog') { |
| 797 | DBI->trace(-1, $value); |
| 798 | } else { |
| 799 | $drh->DBD::_::dr::STORE($key, $value); |
| 800 | } |
| 801 | } |
| 802 | } |
| 803 | |
| 804 | |
| 805 | # -------------------------------------------------------------------- |
| 806 | # === OPTIONAL MINIMAL BASE CLASSES FOR DBI SUBCLASSES === |
| 807 | |
| 808 | # We only define default methods for harmless functions. |
| 809 | # We don't, for example, define a DBD::_::st::prepare() |
| 810 | |
| 811 | { package DBD::_::common; # ====== Common base class methods ====== |
| 812 | use strict; |
| 813 | |
| 814 | # methods common to all handle types: |
| 815 | |
| 816 | sub _not_impl { |
| 817 | my ($h, $method) = @_; |
| 818 | $h->trace_msg("Driver does not implement the $method method.\n"); |
| 819 | return; # empty list / undef |
| 820 | } |
| 821 | |
| 822 | # generic TIEHASH default methods: |
| 823 | sub FIRSTKEY { } |
| 824 | sub NEXTKEY { } |
| 825 | sub EXISTS { defined($_[0]->FETCH($_[1])) } # XXX undef? |
| 826 | sub CLEAR { Carp::carp "Can't CLEAR $_[0] (DBI)" } |
| 827 | |
| 828 | *dump_handle = \&DBI::dump_handle; |
| 829 | } |
| 830 | |
| 831 | |
| 832 | { package DBD::_::dr; # ====== DRIVER ====== |
| 833 | @ISA = qw(DBD::_::common); |
| 834 | use strict; |
| 835 | |
| 836 | sub default_user { |
| 837 | my ($drh, $user, $pass) = @_; |
| 838 | unless (defined $user) { |
| 839 | $user = $ENV{DBI_USER}; |
| 840 | carp("DBI connect: user not defined and DBI_USER env var not set") |
| 841 | if 0 && !defined $user && $drh->{Warn}; # XXX enable later |
| 842 | } |
| 843 | unless (defined $pass) { |
| 844 | $pass = $ENV{DBI_PASS}; |
| 845 | carp("DBI connect: password not defined and DBI_PASS env var not set") |
| 846 | if 0 && !defined $pass && $drh->{Warn}; # XXX enable later |
| 847 | } |
| 848 | return ($user, $pass); |
| 849 | } |
| 850 | |
| 851 | sub connect { # normally overridden, but a handy default |
| 852 | my ($drh, $dsn, $user, $auth) = @_; |
| 853 | my ($this) = DBI::_new_dbh($drh, { |
| 854 | 'Name' => $dsn, |
| 855 | 'User' => $user, |
| 856 | }); |
| 857 | $this; |
| 858 | } |
| 859 | |
| 860 | |
| 861 | sub connect_cached { |
| 862 | my $drh = shift; |
| 863 | my ($dsn, $user, $auth, $attr)= @_; |
| 864 | |
| 865 | # Needs support at dbh level to clear cache before complaining about |
| 866 | # active children. The XS template code does this. Drivers not using |
| 867 | # the template must handle clearing the cache themselves. |
| 868 | my $cache = $drh->FETCH('CachedKids'); |
| 869 | $drh->STORE('CachedKids', $cache = {}) unless $cache; |
| 870 | |
| 871 | my @attr_keys = $attr ? sort keys %$attr : (); |
| 872 | my $key = join "~~", $dsn, $user||'', $auth||'', |
| 873 | $attr ? (@attr_keys,@{$attr}{@attr_keys}) : (); |
| 874 | my $dbh = $cache->{$key}; |
| 875 | return $dbh if $dbh && $dbh->FETCH('Active') && eval { $dbh->ping }; |
| 876 | $dbh = $drh->connect(@_); |
| 877 | $cache->{$key} = $dbh; # replace prev entry, even if connect failed |
| 878 | return $dbh; |
| 879 | } |
| 880 | |
| 881 | |
| 882 | sub disconnect_all { # Driver must take responsibility for this |
| 883 | # XXX Umm, may change later. |
| 884 | Carp::croak("Driver has not implemented the disconnect_all method."); |
| 885 | } |
| 886 | |
| 887 | sub data_sources { |
| 888 | shift->_not_impl('data_sources'); |
| 889 | } |
| 890 | |
| 891 | } |
| 892 | |
| 893 | |
| 894 | { package DBD::_::db; # ====== DATABASE ====== |
| 895 | @ISA = qw(DBD::_::common); |
| 896 | use strict; |
| 897 | |
| 898 | sub disconnect { |
| 899 | shift->_not_impl('disconnect'); |
| 900 | } |
| 901 | |
| 902 | # Drivers are required to implement *::db::DESTROY to encourage tidy-up |
| 903 | sub DESTROY { Carp::croak("Driver has not implemented DESTROY for @_") } |
| 904 | |
| 905 | sub quote { |
| 906 | my ($dbh, $str, $data_type) = @_; |
| 907 | return "NULL" unless defined $str; |
| 908 | unless ($data_type) { |
| 909 | $str =~ s/'/''/g; # ISO SQL2 |
| 910 | return "'$str'"; |
| 911 | } |
| 912 | # Optimise for standard numerics which need no quotes |
| 913 | return $str if $data_type == DBI::SQL_INTEGER |
| 914 | || $data_type == DBI::SQL_SMALLINT |
| 915 | || $data_type == DBI::SQL_DECIMAL |
| 916 | || $data_type == DBI::SQL_FLOAT |
| 917 | || $data_type == DBI::SQL_REAL |
| 918 | || $data_type == DBI::SQL_DOUBLE |
| 919 | || $data_type == DBI::SQL_NUMERIC; |
| 920 | my $ti = $dbh->type_info($data_type); |
| 921 | # XXX needs checking |
| 922 | my $lp = $ti ? $ti->{LITERAL_PREFIX} || "" : "'"; |
| 923 | my $ls = $ti ? $ti->{LITERAL_SUFFIX} || "" : "'"; |
| 924 | # XXX don't know what the standard says about escaping |
| 925 | # in the 'general case' (where $lp != "'"). |
| 926 | # So we just do this and hope: |
| 927 | $str =~ s/$lp/$lp$lp/g |
| 928 | if $lp && $lp eq $ls && ($lp eq "'" || $lp eq '"'); |
| 929 | return "$lp$str$ls"; |
| 930 | } |
| 931 | |
| 932 | sub rows { -1 } # here so $DBI::rows 'works' after using $dbh |
| 933 | |
| 934 | sub do { |
| 935 | my($dbh, $statement, $attr, @params) = @_; |
| 936 | my $sth = $dbh->prepare($statement, $attr) or return undef; |
| 937 | $sth->execute(@params) or return undef; |
| 938 | my $rows = $sth->rows; |
| 939 | ($rows == 0) ? "0E0" : $rows; |
| 940 | } |
| 941 | |
| 942 | sub _do_selectrow { |
| 943 | my ($method, $dbh, $stmt, $attr, @bind) = @_; |
| 944 | my $sth = (ref $stmt) ? $stmt |
| 945 | : $dbh->prepare($stmt, $attr); |
| 946 | return unless $sth; |
| 947 | $sth->execute(@bind) || return; |
| 948 | my $row = $sth->$method(); |
| 949 | $sth->finish if $row; |
| 950 | return $row; |
| 951 | } |
| 952 | |
| 953 | sub selectrow_arrayref { return _do_selectrow('fetchrow_arrayref', @_); } |
| 954 | sub selectrow_hashref { return _do_selectrow('fetchrow_hashref', @_); } |
| 955 | |
| 956 | sub selectrow_array { |
| 957 | my ($dbh, $stmt, $attr, @bind) = @_; |
| 958 | my $sth = (ref $stmt) ? $stmt |
| 959 | : $dbh->prepare($stmt, $attr); |
| 960 | return unless $sth; |
| 961 | $sth->execute(@bind) || return; |
| 962 | my @row = $sth->fetchrow_array; |
| 963 | $sth->finish if @row; |
| 964 | return $row[0] unless wantarray; |
| 965 | return @row; |
| 966 | } |
| 967 | |
| 968 | sub selectall_arrayref { |
| 969 | my ($dbh, $stmt, $attr, @bind) = @_; |
| 970 | my $sth = (ref $stmt) ? $stmt |
| 971 | : $dbh->prepare($stmt, $attr); |
| 972 | return unless $sth; |
| 973 | $sth->execute(@bind) || return; |
| 974 | my $slice = $attr->{dbi_fetchall_arrayref_attr}; # typically undef |
| 975 | return $sth->fetchall_arrayref($slice); |
| 976 | } |
| 977 | |
| 978 | sub selectall_hashref { |
| 979 | my ($dbh, $stmt, $attr, @bind) = @_; |
| 980 | my $sth = (ref $stmt) ? $stmt |
| 981 | : $dbh->prepare($stmt, $attr); |
| 982 | return unless $sth; |
| 983 | $sth->execute(@bind) || return; |
| 984 | my ($row, @rows); |
| 985 | push @rows, $row while ($row = $sth->fetchrow_hashref()); |
| 986 | return \@rows; |
| 987 | } |
| 988 | |
| 989 | sub selectcol_arrayref { |
| 990 | my ($dbh, $stmt, $attr, @bind) = @_; |
| 991 | my $sth = (ref $stmt) ? $stmt |
| 992 | : $dbh->prepare($stmt, $attr); |
| 993 | return unless $sth; |
| 994 | $sth->execute(@bind) || return; |
| 995 | my $column = 1; |
| 996 | my $value; |
| 997 | $sth->bind_col($column, \$value) || return; |
| 998 | my @col; |
| 999 | push @col, $value while $sth->fetch; |
| 1000 | return \@col; |
| 1001 | } |
| 1002 | |
| 1003 | sub prepare_cached { |
| 1004 | my ($dbh, $statement, $attr, $allow_active) = @_; |
| 1005 | # Needs support at dbh level to clear cache before complaining about |
| 1006 | # active children. The XS template code does this. Drivers not using |
| 1007 | # the template must handle clearing the cache themselves. |
| 1008 | my $cache = $dbh->FETCH('CachedKids'); |
| 1009 | $dbh->STORE('CachedKids', $cache = {}) unless $cache; |
| 1010 | my @attr_keys = ($attr) ? sort keys %$attr : (); |
| 1011 | my $key = ($attr) ? join("~~", $statement, @attr_keys, @{$attr}{@attr_keys}) : $statement; |
| 1012 | my $sth = $cache->{$key}; |
| 1013 | if ($sth) { |
| 1014 | if ($sth->FETCH('Active')) { |
| 1015 | Carp::carp("prepare_cached($statement) statement handle $sth was still active") |
| 1016 | if !$allow_active; |
| 1017 | $sth->finish; |
| 1018 | } |
| 1019 | return $sth; |
| 1020 | } |
| 1021 | $sth = $dbh->prepare($statement, $attr); |
| 1022 | $cache->{$key} = $sth if $sth; |
| 1023 | return $sth; |
| 1024 | } |
| 1025 | |
| 1026 | sub ping { |
| 1027 | shift->_not_impl('ping'); |
| 1028 | "0 but true"; # special kind of true 0 |
| 1029 | } |
| 1030 | |
| 1031 | sub commit { |
| 1032 | shift->_not_impl('commit'); |
| 1033 | } |
| 1034 | sub rollback { |
| 1035 | shift->_not_impl('rollback'); |
| 1036 | } |
| 1037 | |
| 1038 | sub get_info { |
| 1039 | shift->_not_impl("get_info @_"); |
| 1040 | } |
| 1041 | |
| 1042 | sub table_info { |
| 1043 | shift->_not_impl('table_info'); |
| 1044 | } |
| 1045 | |
| 1046 | sub tables { |
| 1047 | my ($dbh, @args) = @_; |
| 1048 | my $sth = $dbh->table_info(@args); |
| 1049 | return unless $sth; |
| 1050 | my ($row, @tables); |
| 1051 | while($row = $sth->fetch) { |
| 1052 | my $name = $row->[2]; |
| 1053 | if ($row->[1]) { |
| 1054 | my $schema = $row->[1]; |
| 1055 | # a little hack |
| 1056 | my $quote = ($schema eq uc($schema)) ? '' : '"'; |
| 1057 | $name = "$quote$schema$quote.$name" |
| 1058 | } |
| 1059 | push @tables, $name; |
| 1060 | } |
| 1061 | return @tables; |
| 1062 | } |
| 1063 | |
| 1064 | sub type_info_all { |
| 1065 | my ($dbh) = @_; |
| 1066 | $dbh->_not_impl('type_info_all'); |
| 1067 | my $ti = [ {} ]; |
| 1068 | return $ti; |
| 1069 | } |
| 1070 | |
| 1071 | sub type_info { |
| 1072 | my ($dbh, $data_type) = @_; |
| 1073 | my $tia = $dbh->type_info_all; |
| 1074 | return unless @$tia; |
| 1075 | my $idx_hash = shift @$tia; |
| 1076 | |
| 1077 | my $dt_idx = $idx_hash->{DATA_TYPE} || $idx_hash->{data_type}; |
| 1078 | Carp::croak("type_info_all returned non-standard DATA_TYPE index value ($dt_idx != 1)") |
| 1079 | if $dt_idx && $dt_idx != 1; |
| 1080 | |
| 1081 | # --- simple DATA_TYPE match filter |
| 1082 | my @ti; |
| 1083 | my @data_type_list = (ref $data_type) ? @$data_type : ($data_type); |
| 1084 | foreach $data_type (@data_type_list) { |
| 1085 | if (defined($data_type) && $data_type != DBI::SQL_ALL_TYPES()) { |
| 1086 | push @ti, grep { $_->[$dt_idx] == $data_type } @$tia; |
| 1087 | } |
| 1088 | else { # SQL_ALL_TYPES |
| 1089 | push @ti, @$tia; |
| 1090 | } |
| 1091 | last if @ti; # found at least one match |
| 1092 | } |
| 1093 | |
| 1094 | # --- format results into list of hash refs |
| 1095 | my $idx_fields = keys %$idx_hash; |
| 1096 | my @idx_names = map { uc($_) } keys %$idx_hash; |
| 1097 | my @idx_values = values %$idx_hash; |
| 1098 | Carp::croak "type_info_all result has $idx_fields keys but ".(@{$ti[0]})." fields" |
| 1099 | if @ti && @{$ti[0]} != $idx_fields; |
| 1100 | my @out = map { |
| 1101 | my %h; @h{@idx_names} = @{$_}[ @idx_values ]; \%h; |
| 1102 | } @ti; |
| 1103 | return $out[0] unless wantarray; |
| 1104 | return @out; |
| 1105 | } |
| 1106 | } |
| 1107 | |
| 1108 | |
| 1109 | { package DBD::_::st; # ====== STATEMENT ====== |
| 1110 | @ISA = qw(DBD::_::common); |
| 1111 | use strict; |
| 1112 | |
| 1113 | sub cancel { undef } |
| 1114 | sub bind_param { Carp::croak("Can't bind_param, not implement by driver") } |
| 1115 | |
| 1116 | sub fetchall_arrayref { |
| 1117 | my $sth = shift; |
| 1118 | my $slice= shift || []; |
| 1119 | my $mode = ref $slice; |
| 1120 | my @rows; |
| 1121 | my $row; |
| 1122 | if ($mode eq 'ARRAY') { |
| 1123 | # we copy the array here because fetch (currently) always |
| 1124 | # returns the same array ref. XXX |
| 1125 | if (@$slice) { |
| 1126 | push @rows, [ @{$row}[ @$slice] ] while($row = $sth->fetch); |
| 1127 | } |
| 1128 | else { |
| 1129 | push @rows, [ @$row ] while($row = $sth->fetch); |
| 1130 | } |
| 1131 | } |
| 1132 | elsif ($mode eq 'HASH') { |
| 1133 | my @o_keys = keys %$slice; |
| 1134 | if (@o_keys) { |
| 1135 | my %i_names = map { (lc($_)=>$_) } @{ $sth->FETCH('NAME') }; |
| 1136 | my @i_keys = map { $i_names{lc($_)} } @o_keys; |
| 1137 | while ($row = $sth->fetchrow_hashref) { |
| 1138 | my %hash; |
| 1139 | @hash{@o_keys} = @{$row}{@i_keys}; |
| 1140 | push @rows, \%hash; |
| 1141 | } |
| 1142 | } |
| 1143 | else { |
| 1144 | # XXX assumes new ref each fetchhash |
| 1145 | push @rows, $row while ($row = $sth->fetchrow_hashref); |
| 1146 | } |
| 1147 | } |
| 1148 | else { Carp::croak("fetchall_arrayref($mode) invalid") } |
| 1149 | return \@rows; |
| 1150 | } |
| 1151 | |
| 1152 | *dump_results = \&DBI::dump_results; |
| 1153 | |
| 1154 | sub blob_copy_to_file { # returns length or undef on error |
| 1155 | my($self, $field, $filename_or_handleref, $blocksize) = @_; |
| 1156 | my $fh = $filename_or_handleref; |
| 1157 | my($len, $buf) = (0, ""); |
| 1158 | $blocksize ||= 512; # not too ambitious |
| 1159 | local(*FH); |
| 1160 | unless(ref $fh) { |
| 1161 | open(FH, ">$fh") || return undef; |
| 1162 | $fh = \*FH; |
| 1163 | } |
| 1164 | while(defined($self->blob_read($field, $len, $blocksize, \$buf))) { |
| 1165 | print $fh $buf; |
| 1166 | $len += length $buf; |
| 1167 | } |
| 1168 | close(FH); |
| 1169 | $len; |
| 1170 | } |
| 1171 | |
| 1172 | sub more_results { |
| 1173 | shift->{syb_more_results}; # handy grandfathering |
| 1174 | } |
| 1175 | |
| 1176 | # Drivers are required to implement *::st::DESTROY to encourage tidy-up |
| 1177 | sub DESTROY { Carp::croak("Driver has not implemented DESTROY for @_") } |
| 1178 | } |
| 1179 | |
| 1180 | { # See install_driver |
| 1181 | { package DBD::_mem::dr; @ISA = qw(DBD::_mem::common); } |
| 1182 | { package DBD::_mem::db; @ISA = qw(DBD::_mem::common); } |
| 1183 | { package DBD::_mem::st; @ISA = qw(DBD::_mem::common); } |
| 1184 | # DBD::_mem::common::DESTROY is implemented in DBI.xs |
| 1185 | } |
| 1186 | |
| 1187 | 1; |
| 1188 | __END__ |
| 1189 | |
| 1190 | =head1 DESCRIPTION |
| 1191 | |
| 1192 | The DBI is a database access module for the Perl programming language. It defines |
| 1193 | a set of methods, variables, and conventions that provide a consistent |
| 1194 | database interface, independent of the actual database being used. |
| 1195 | |
| 1196 | It is important to remember that the DBI is just an interface. |
| 1197 | The DBI is a layer |
| 1198 | of "glue" between an application and one or more database I<driver> |
| 1199 | modules. It is the driver modules which do most of the real work. The DBI |
| 1200 | provides a standard interface and framework for the drivers to operate |
| 1201 | within. |
| 1202 | |
| 1203 | |
| 1204 | =head2 Architecture of a DBI Application |
| 1205 | |
| 1206 | |<- Scope of DBI ->| |
| 1207 | .-. .--------------. .-------------. |
| 1208 | .-------. | |---| XYZ Driver |---| XYZ Engine | |
| 1209 | | Perl | | | `--------------' `-------------' |
| 1210 | | script| |A| |D| .--------------. .-------------. |
| 1211 | | using |--|P|--|B|---|Oracle Driver |---|Oracle Engine| |
| 1212 | | DBI | |I| |I| `--------------' `-------------' |
| 1213 | | API | | |... |
| 1214 | |methods| | |... Other drivers |
| 1215 | `-------' | |... |
| 1216 | `-' |
| 1217 | |
| 1218 | The API, or Application Programming Interface, defines the |
| 1219 | call interface and variables for Perl scripts to use. The API |
| 1220 | is implemented by the Perl DBI extension. |
| 1221 | |
| 1222 | The DBI "dispatches" the method calls to the appropriate driver for |
| 1223 | actual execution. The DBI is also responsible for the dynamic loading |
| 1224 | of drivers, error checking and handling, providing default |
| 1225 | implementations for methods, and many other non-database specific duties. |
| 1226 | |
| 1227 | Each driver |
| 1228 | contains implementations of the DBI methods using the |
| 1229 | private interface functions of the corresponding database engine. Only authors |
| 1230 | of sophisticated/multi-database applications or generic library |
| 1231 | functions need be concerned with drivers. |
| 1232 | |
| 1233 | =head2 Notation and Conventions |
| 1234 | |
| 1235 | The following conventions are used in this document: |
| 1236 | |
| 1237 | $dbh Database handle object |
| 1238 | $sth Statement handle object |
| 1239 | $drh Driver handle object (rarely seen or used in applications) |
| 1240 | $h Any of the handle types above ($dbh, $sth, or $drh) |
| 1241 | $rc General Return Code (boolean: true=ok, false=error) |
| 1242 | $rv General Return Value (typically an integer) |
| 1243 | @ary List of values returned from the database, typically a row of data |
| 1244 | $rows Number of rows processed (if available, else -1) |
| 1245 | $fh A filehandle |
| 1246 | undef NULL values are represented by undefined values in Perl |
| 1247 | \%attr Reference to a hash of attribute values passed to methods |
| 1248 | |
| 1249 | Note that Perl will automatically destroy database and statement handle objects |
| 1250 | if all references to them are deleted. |
| 1251 | |
| 1252 | |
| 1253 | =head2 Outline Usage |
| 1254 | |
| 1255 | To use DBI, |
| 1256 | first you need to load the DBI module: |
| 1257 | |
| 1258 | use DBI; |
| 1259 | use strict; |
| 1260 | |
| 1261 | (The C<use strict;> isn't required but is strongly recommended.) |
| 1262 | |
| 1263 | Then you need to L</connect> to your data source and get a I<handle> for that |
| 1264 | connection: |
| 1265 | |
| 1266 | $dbh = DBI->connect($dsn, $user, $password, |
| 1267 | { RaiseError => 1, AutoCommit => 0 }); |
| 1268 | |
| 1269 | Since connecting can be expensive, you generally just connect at the |
| 1270 | start of your program and disconnect at the end. |
| 1271 | |
| 1272 | Explicitly defining the required C<AutoCommit> behavior is strongly |
| 1273 | recommended and may become mandatory in a later version. This |
| 1274 | determines whether changes are automatically committed to the |
| 1275 | database when executed, or need to be explicitly committed later. |
| 1276 | |
| 1277 | The DBI allows an application to "prepare" statements for later |
| 1278 | execution. A prepared statement is identified by a statement handle |
| 1279 | held in a Perl variable. |
| 1280 | We'll call the Perl variable C<$sth> in our examples. |
| 1281 | |
| 1282 | The typical method call sequence for a C<SELECT> statement is: |
| 1283 | |
| 1284 | prepare, |
| 1285 | execute, fetch, fetch, ... |
| 1286 | execute, fetch, fetch, ... |
| 1287 | execute, fetch, fetch, ... |
| 1288 | |
| 1289 | for example: |
| 1290 | |
| 1291 | $sth = $dbh->prepare("SELECT foo, bar FROM table WHERE baz=?"); |
| 1292 | |
| 1293 | $sth->execute( $baz ); |
| 1294 | |
| 1295 | while ( @row = $sth->fetchrow_array ) { |
| 1296 | print "@row\n"; |
| 1297 | } |
| 1298 | |
| 1299 | The typical method call sequence for a I<non>-C<SELECT> statement is: |
| 1300 | |
| 1301 | prepare, |
| 1302 | execute, |
| 1303 | execute, |
| 1304 | execute. |
| 1305 | |
| 1306 | for example: |
| 1307 | |
| 1308 | $sth = $dbh->prepare("INSERT INTO table(foo,bar,baz) VALUES (?,?,?)"); |
| 1309 | |
| 1310 | while(<CSV>) { |
| 1311 | chomp; |
| 1312 | my ($foo,$bar,$baz) = split /,/; |
| 1313 | $sth->execute( $foo, $bar, $baz ); |
| 1314 | } |
| 1315 | |
| 1316 | The C<do()> method can be used for non repeated I<non>-C<SELECT> statement |
| 1317 | (or with drivers that don't support placeholders): |
| 1318 | |
| 1319 | $rows_affected = $dbh->do("UPDATE your_table SET foo = foo + 1"); |
| 1320 | |
| 1321 | To commit your changes to the database (when L</AutoCommit> is off): |
| 1322 | |
| 1323 | $dbh->commit; # or call $dbh->rollback; to undo changes |
| 1324 | |
| 1325 | Finally, when you have finished working with the data source, you should |
| 1326 | L</disconnect> from it: |
| 1327 | |
| 1328 | $dbh->disconnect; |
| 1329 | |
| 1330 | |
| 1331 | =head2 General Interface Rules & Caveats |
| 1332 | |
| 1333 | The DBI does not have a concept of a "current session". Every session |
| 1334 | has a handle object (i.e., a C<$dbh>) returned from the C<connect> method. |
| 1335 | That handle object is used to invoke database related methods. |
| 1336 | |
| 1337 | Most data is returned to the Perl script as strings. (Null values are |
| 1338 | returned as C<undef>.) This allows arbitrary precision numeric data to be |
| 1339 | handled without loss of accuracy. Beware that Perl may not preserve |
| 1340 | the same accuracy when the string is used as a number. |
| 1341 | |
| 1342 | Dates and times are returned as character strings in the current |
| 1343 | default format of the corresponding database engine. Time zone effects |
| 1344 | are database/driver dependent. |
| 1345 | |
| 1346 | Perl supports binary data in Perl strings, and the DBI will pass binary |
| 1347 | data to and from the driver without change. It is up to the driver |
| 1348 | implementors to decide how they wish to handle such binary data. |
| 1349 | |
| 1350 | Most databases that understand multiple character sets have a |
| 1351 | default global charset. Text stored in the database is, or should |
| 1352 | be, stored in that charset; if not, then that's the fault of either |
| 1353 | the database or the application that inserted the data. When text is |
| 1354 | fetched it should be automatically converted to the charset of the |
| 1355 | client, presumably based on the locale. If a driver needs to set a |
| 1356 | flag to get that behavior, then it should do so; it should not require |
| 1357 | the application to do that. |
| 1358 | |
| 1359 | Multiple SQL statements may not be combined in a single statement |
| 1360 | handle (C<$sth>), although some databases and drivers do support this |
| 1361 | (notably Sybase and SQL Server). |
| 1362 | |
| 1363 | Non-sequential record reads are not supported in this version of the DBI. |
| 1364 | In other words, records can only be fetched in the order that the |
| 1365 | database returned them, and once fetched they are forgotten. |
| 1366 | |
| 1367 | Positioned updates and deletes are not directly supported by the DBI. |
| 1368 | See the description of the C<CursorName> attribute for an alternative. |
| 1369 | |
| 1370 | Individual driver implementors are free to provide any private |
| 1371 | functions and/or handle attributes that they feel are useful. |
| 1372 | Private driver functions can be invoked using the DBI C<func()> method. |
| 1373 | Private driver attributes are accessed just like standard attributes. |
| 1374 | |
| 1375 | Many methods have an optional C<\%attr> parameter which can be used to |
| 1376 | pass information to the driver implementing the method. Except where |
| 1377 | specifically documented, the C<\%attr> parameter can only be used to pass |
| 1378 | driver specific hints. In general, you can ignore C<\%attr> parameters |
| 1379 | or pass it as C<undef>. |
| 1380 | |
| 1381 | |
| 1382 | =head2 Naming Conventions and Name Space |
| 1383 | |
| 1384 | The DBI package and all packages below it (C<DBI::*>) are reserved for |
| 1385 | use by the DBI. Extensions and related modules use the C<DBIx::> |
| 1386 | namespace (see C<http://www.perl.com/CPAN/modules/by-module/DBIx/>). |
| 1387 | Package names beginning with C<DBD::> are reserved for use |
| 1388 | by DBI database drivers. All environment variables used by the DBI |
| 1389 | or by individual DBDs begin with "C<DBI_>" or "C<DBD_>". |
| 1390 | |
| 1391 | The letter case used for attribute names is significant and plays an |
| 1392 | important part in the portability of DBI scripts. The case of the |
| 1393 | attribute name is used to signify who defined the meaning of that name |
| 1394 | and its values. |
| 1395 | |
| 1396 | Case of name Has a meaning defined by |
| 1397 | ------------ ------------------------ |
| 1398 | UPPER_CASE Standards, e.g., X/Open, ISO SQL92 etc (portable) |
| 1399 | MixedCase DBI API (portable), underscores are not used. |
| 1400 | lower_case Driver or database engine specific (non-portable) |
| 1401 | |
| 1402 | It is of the utmost importance that Driver developers only use |
| 1403 | lowercase attribute names when defining private attributes. Private |
| 1404 | attribute names must be prefixed with the driver name or suitable |
| 1405 | abbreviation (e.g., "C<ora_>" for Oracle, "C<ing_>" for Ingres, etc). |
| 1406 | |
| 1407 | Driver Specific Prefix Registry: |
| 1408 | |
| 1409 | ad_ DBD::AnyData |
| 1410 | ado_ DBD::ADO |
| 1411 | best_ DBD::BestWins |
| 1412 | csv_ DBD::CSV |
| 1413 | db2_ DBD::DB2 |
| 1414 | f_ DBD::File |
| 1415 | file_ DBD::TextFile |
| 1416 | ib_ DBD::InterBase |
| 1417 | ing_ DBD::Ingres |
| 1418 | ix_ DBD::Informix |
| 1419 | msql_ DBD::mSQL |
| 1420 | mysql_ DBD::mysql |
| 1421 | odbc_ DBD::ODBC |
| 1422 | ora_ DBD::Oracle |
| 1423 | pg_ DBD::Pg |
| 1424 | proxy_ DBD::Proxy |
| 1425 | rdb_ DBD::RDB |
| 1426 | sapdb_ DBD::SAP_DB |
| 1427 | solid_ DBD::Solid |
| 1428 | syb_ DBD::Sybase |
| 1429 | tdat_ DBD::Teradata |
| 1430 | tuber_ DBD::Tuber |
| 1431 | uni_ DBD::Unify |
| 1432 | xbase_ DBD::XBase |
| 1433 | |
| 1434 | |
| 1435 | =head2 SQL - A Query Language |
| 1436 | |
| 1437 | Most DBI drivers require applications to use a dialect of SQL |
| 1438 | (Structured Query Language) to interact with the database engine. |
| 1439 | The following links provide useful information and further links about |
| 1440 | SQL: |
| 1441 | |
| 1442 | http://www.altavista.com/query?q=sql+tutorial |
| 1443 | http://www.jcc.com/sql_stnd.html |
| 1444 | http://www.contrib.andrew.cmu.edu/~shadow/sql.html |
| 1445 | |
| 1446 | The DBI itself does not mandate or require any particular language to |
| 1447 | be used; it is language independent. In ODBC terms, the DBI is in |
| 1448 | "pass-thru" mode, although individual drivers might not be. The only requirement |
| 1449 | is that queries and other statements must be expressed as a single |
| 1450 | string of characters passed as the first argument to the L</prepare> or |
| 1451 | L</do> methods. |
| 1452 | |
| 1453 | For an interesting diversion on the I<real> history of RDBMS and SQL, |
| 1454 | from the people who made it happen, see: |
| 1455 | |
| 1456 | http://ftp.digital.com/pub/DEC/SRC/technical-notes/SRC-1997-018-html/sqlr95.html |
| 1457 | |
| 1458 | Follow the "And the rest" and "Intergalactic dataspeak" links for the |
| 1459 | SQL history. |
| 1460 | |
| 1461 | =head2 Placeholders and Bind Values |
| 1462 | |
| 1463 | Some drivers support placeholders and bind values. |
| 1464 | I<Placeholders>, also called parameter markers, are used to indicate |
| 1465 | values in a database statement that will be supplied later, |
| 1466 | before the prepared statement is executed. For example, an application |
| 1467 | might use the following to insert a row of data into the SALES table: |
| 1468 | |
| 1469 | INSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?) |
| 1470 | |
| 1471 | or the following, to select the description for a product: |
| 1472 | |
| 1473 | SELECT description FROM products WHERE product_code = ? |
| 1474 | |
| 1475 | The C<?> characters are the placeholders. The association of actual |
| 1476 | values with placeholders is known as I<binding>, and the values are |
| 1477 | referred to as I<bind values>. |
| 1478 | |
| 1479 | When using placeholders with the SQL C<LIKE> qualifier, you must |
| 1480 | remember that the placeholder substitutes for the whole string. |
| 1481 | So you should use "C<... LIKE ? ...>" and include any wildcard |
| 1482 | characters in the value that you bind to the placeholder. |
| 1483 | |
| 1484 | B<Null Values> |
| 1485 | |
| 1486 | Undefined values, or C<undef>, can be used to indicate null values. |
| 1487 | However, care must be taken in the particular case of trying to use |
| 1488 | null values to qualify a C<SELECT> statement. Consider: |
| 1489 | |
| 1490 | SELECT description FROM products WHERE product_code = ? |
| 1491 | |
| 1492 | Binding an C<undef> (NULL) to the placeholder will I<not> select rows |
| 1493 | which have a NULL C<product_code>! Refer to the SQL manual for your database |
| 1494 | engine or any SQL book for the reasons for this. To explicitly select |
| 1495 | NULLs you have to say "C<WHERE product_code IS NULL>" and to make that |
| 1496 | general you have to say: |
| 1497 | |
| 1498 | ... WHERE (product_code = ? OR (? IS NULL AND product_code IS NULL)) |
| 1499 | |
| 1500 | and bind the same value to both placeholders. |
| 1501 | |
| 1502 | B<Performance> |
| 1503 | |
| 1504 | Without using placeholders, the insert statement shown previously would have to |
| 1505 | contain the literal values to be inserted and would have to be |
| 1506 | re-prepared and re-executed for each row. With placeholders, the insert |
| 1507 | statement only needs to be prepared once. The bind values for each row |
| 1508 | can be given to the C<execute> method each time it's called. By avoiding |
| 1509 | the need to re-prepare the statement for each row, the application |
| 1510 | typically runs many times faster. Here's an example: |
| 1511 | |
| 1512 | my $sth = $dbh->prepare(q{ |
| 1513 | INSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?) |
| 1514 | }) or die $dbh->errstr; |
| 1515 | while (<>) { |
| 1516 | chomp; |
| 1517 | my ($product_code, $qty, $price) = split /,/; |
| 1518 | $sth->execute($product_code, $qty, $price) or die $dbh->errstr; |
| 1519 | } |
| 1520 | $dbh->commit or die $dbh->errstr; |
| 1521 | |
| 1522 | See L</execute> and L</bind_param> for more details. |
| 1523 | |
| 1524 | The C<q{...}> style quoting used in this example avoids clashing with |
| 1525 | quotes that may be used in the SQL statement. Use the double-quote like |
| 1526 | C<qq{...}> operator if you want to interpolate variables into the string. |
| 1527 | See L<perlop/"Quote and Quote-like Operators"> for more details. |
| 1528 | |
| 1529 | See also the L</bind_column> method, which is used to associate Perl |
| 1530 | variables with the output columns of a C<SELECT> statement. |
| 1531 | |
| 1532 | =head1 THE DBI PACKAGE AND CLASS |
| 1533 | |
| 1534 | In this section, we cover the DBI class methods, utility functions, |
| 1535 | and the dynamic attributes associated with generic DBI handles. |
| 1536 | |
| 1537 | =head2 DBI Constants |
| 1538 | |
| 1539 | The following SQL standard type constants can be imported individually |
| 1540 | or, by importing the special C<:sql_types> tag, all together: |
| 1541 | |
| 1542 | SQL_CHAR SQL_NUMERIC SQL_DECIMAL SQL_INTEGER SQL_SMALLINT |
| 1543 | SQL_FLOAT SQL_REAL SQL_DOUBLE SQL_VARCHAR |
| 1544 | SQL_DATE SQL_TIME SQL_TIMESTAMP |
| 1545 | SQL_LONGVARCHAR SQL_BINARY SQL_VARBINARY SQL_LONGVARBINARY |
| 1546 | SQL_BIGINT SQL_TINYINT |
| 1547 | SQL_WCHAR SQL_WVARCHAR SQL_WLONGVARCHAR |
| 1548 | SQL_BIT |
| 1549 | SQL_ALL_TYPES |
| 1550 | |
| 1551 | See the L</type_info>, L</type_info_all>, and L</bind_param> methods |
| 1552 | for possible uses. |
| 1553 | |
| 1554 | =head2 DBI Class Methods |
| 1555 | |
| 1556 | The following methods are provided by the DBI class: |
| 1557 | |
| 1558 | =over 4 |
| 1559 | |
| 1560 | =item C<connect> |
| 1561 | |
| 1562 | $dbh = DBI->connect($data_source, $username, $password) |
| 1563 | or die $DBI::errstr; |
| 1564 | $dbh = DBI->connect($data_source, $username, $password, \%attr) |
| 1565 | or die $DBI::errstr; |
| 1566 | |
| 1567 | Establishes a database connection, or session, to the requested C<$data_source>. |
| 1568 | Returns a database handle object if the connection succeeds. Use |
| 1569 | C<$dbh->E<gt>C<disconnect> to terminate the connection. |
| 1570 | |
| 1571 | If the connect fails (see below), it returns C<undef> and sets both C<$DBI::err> |
| 1572 | and C<$DBI::errstr>. (It does I<not> set C<$!>, etc.) You should generally |
| 1573 | test the return status of C<connect> and C<print $DBI::errstr> if it has failed. |
| 1574 | |
| 1575 | Multiple simultaneous connections to multiple databases through multiple |
| 1576 | drivers can be made via the DBI. Simply make one C<connect> call for each |
| 1577 | database and keep a copy of each returned database handle. |
| 1578 | |
| 1579 | The C<$data_source> value should begin with "C<dbi:>I<driver_name>C<:>". The |
| 1580 | I<driver_name> specifies the driver that will be used to make the |
| 1581 | connection. (Letter case is significant.) |
| 1582 | |
| 1583 | As a convenience, if the C<$data_source> parameter is undefined or empty, the |
| 1584 | DBI will substitute the value of the environment variable C<DBI_DSN>. |
| 1585 | If just the I<driver_name> part is empty (i.e., the C<$data_source> prefix is "C<dbi::>"), |
| 1586 | the environment variable C<DBI_DRIVER> is used. If neither variable is set, |
| 1587 | then C<connect> dies. |
| 1588 | |
| 1589 | Examples of C<$data_source> values are: |
| 1590 | |
| 1591 | dbi:DriverName:database_name |
| 1592 | dbi:DriverName:database_name@hostname:port |
| 1593 | dbi:DriverName:database=database_name;host=hostname;port=port |
| 1594 | |
| 1595 | There is I<no standard> for the text following the driver name. Each |
| 1596 | driver is free to use whatever syntax it wants. The only requirement the |
| 1597 | DBI makes is that all the information is supplied in a single string. |
| 1598 | You must consult the documentation for the drivers you are using for a |
| 1599 | description of the syntax they require. (Where a driver author needs |
| 1600 | to define a syntax for the C<$data_source>, it is recommended that |
| 1601 | they follow the ODBC style, shown in the last example above.) |
| 1602 | |
| 1603 | If the environment variable C<DBI_AUTOPROXY> is defined (and the driver in |
| 1604 | C<$data_source> is not "C<Proxy>") then the connect request will |
| 1605 | automatically be changed to: |
| 1606 | |
| 1607 | dbi:Proxy:$ENV{DBI_AUTOPROXY};dsn=$data_source |
| 1608 | |
| 1609 | and passed to the DBD::Proxy module. C<DBI_AUTOPROXY> is typically set as |
| 1610 | "C<hostname=...;port=...>". See the DBD::Proxy documentation for more details. |
| 1611 | |
| 1612 | If C<$username> or C<$password> are undefined (rather than just empty), |
| 1613 | then the DBI will substitute the values of the C<DBI_USER> and C<DBI_PASS> |
| 1614 | environment variables, respectively. The DBI will warn if the |
| 1615 | environment variables are not defined. However, the everyday use of |
| 1616 | these environment |
| 1617 | variables is not recommended for security reasons. The mechanism is |
| 1618 | primarily intended to simplify testing. |
| 1619 | |
| 1620 | C<DBI->E<gt>C<connect> automatically installs the driver if it has not been |
| 1621 | installed yet. Driver installation either returns a valid driver |
| 1622 | handle, or it I<dies> with an error message that includes the string |
| 1623 | "C<install_driver>" and the underlying problem. So C<DBI->E<gt>C<connect> |
| 1624 | will die |
| 1625 | on a driver installation failure and will only return C<undef> on a |
| 1626 | connect failure, in which case C<$DBI::errstr> will hold the error message. |
| 1627 | |
| 1628 | The C<$data_source> argument (with the "C<dbi:...:>" prefix removed) and the |
| 1629 | C<$username> and C<$password> arguments are then passed to the driver for |
| 1630 | processing. The DBI does not define any interpretation for the |
| 1631 | contents of these fields. The driver is free to interpret the |
| 1632 | C<$data_source>, C<$username>, and C<$password> fields in any way, and supply |
| 1633 | whatever defaults are appropriate for the engine being accessed. |
| 1634 | (Oracle, for example, uses the ORACLE_SID and TWO_TASK environment |
| 1635 | variables if no C<$data_source> is specified.) |
| 1636 | |
| 1637 | The C<AutoCommit> and C<PrintError> attributes for each connection default to |
| 1638 | "on". (See L</AutoCommit> and L</PrintError> for more information.) |
| 1639 | However, it is strongly recommended that you explicitly define C<AutoCommit> |
| 1640 | rather than rely on the default. Future versions of |
| 1641 | the DBI may issue a warning if C<AutoCommit> is not explicitly defined. |
| 1642 | |
| 1643 | The C<\%attr> parameter can be used to alter the default settings of |
| 1644 | C<PrintError>, C<RaiseError>, C<AutoCommit>, and other attributes. For example: |
| 1645 | |
| 1646 | $dbh = DBI->connect($data_source, $user, $pass, { |
| 1647 | PrintError => 0, |
| 1648 | AutoCommit => 0 |
| 1649 | }); |
| 1650 | |
| 1651 | You can also define connection attribute values within the C<$data_source> |
| 1652 | parameter. For example: |
| 1653 | |
| 1654 | dbi:DriverName(PrintError=>0,Taint=>1):... |
| 1655 | |
| 1656 | Individual attributes values specified in this way take precedence over |
| 1657 | any conflicting values specified via the C<\%attr> parameter to C<connect>. |
| 1658 | |
| 1659 | The C<dbi_connect_method> attribute can be used to specify which driver |
| 1660 | method should be called to establish the connection. The only useful |
| 1661 | values are 'connect', 'connect_cached', or some specialized case like |
| 1662 | 'Apache::DBI::connect' (which is automatically the default when running |
| 1663 | within Apache). |
| 1664 | |
| 1665 | Where possible, each session (C<$dbh>) is independent from the transactions |
| 1666 | in other sessions. This is useful when you need to hold cursors open |
| 1667 | across transactions--for example, if you use one session for your long lifespan |
| 1668 | cursors (typically read-only) and another for your short update |
| 1669 | transactions. |
| 1670 | |
| 1671 | For compatibility with old DBI scripts, the driver can be specified by |
| 1672 | passing its name as the fourth argument to C<connect> (instead of C<\%attr>): |
| 1673 | |
| 1674 | $dbh = DBI->connect($data_source, $user, $pass, $driver); |
| 1675 | |
| 1676 | In this "old-style" form of C<connect>, the C<$data_source> should not start |
| 1677 | with "C<dbi:driver_name:>". (If it does, the embedded driver_name |
| 1678 | will be ignored). Also note that in this older form of C<connect>, |
| 1679 | the C<$dbh->E<gt>C<{AutoCommit}> attribute is I<undefined>, the |
| 1680 | C<$dbh->E<gt>C<{PrintError}> attribute is off, and the old C<DBI_DBNAME> |
| 1681 | environment variable is |
| 1682 | checked if C<DBI_DSN> is not defined. Beware that this "old-style" |
| 1683 | C<connect> will be withdrawn in a future version of DBI. |
| 1684 | |
| 1685 | =item C<connect_cached> I<NEW> |
| 1686 | |
| 1687 | $dbh = DBI->connect_cached($data_source, $username, $password) |
| 1688 | or die $DBI::errstr; |
| 1689 | $dbh = DBI->connect_cached($data_source, $username, $password, \%attr) |
| 1690 | or die $DBI::errstr; |
| 1691 | |
| 1692 | C<connect_cached> is like L</connect>, except that the database handle |
| 1693 | returned is also |
| 1694 | stored in a hash associated with the given parameters. If another call |
| 1695 | is made to C<connect_cached> with the same parameter values, then the |
| 1696 | corresponding cached C<$dbh> will be returned if it is still valid. |
| 1697 | The cached database handle is replaced with a new connection if it |
| 1698 | has been disconnected or if the C<ping> method fails. |
| 1699 | |
| 1700 | Note that the behavior of this method differs in several respects from the |
| 1701 | behavior of presistent connections implemented by Apache::DBI. |
| 1702 | |
| 1703 | Caching can be useful in some applications, but it can also cause |
| 1704 | problems and should be used with care. The exact behavior of this |
| 1705 | method is liable to change, so if you intend to use it in any production |
| 1706 | applications you should discuss your needs on the I<dbi-users> mailing list. |
| 1707 | |
| 1708 | The cache can be accessed (and cleared) via the L</CachedKids> attribute. |
| 1709 | |
| 1710 | |
| 1711 | =item C<available_drivers> |
| 1712 | |
| 1713 | @ary = DBI->available_drivers; |
| 1714 | @ary = DBI->available_drivers($quiet); |
| 1715 | |
| 1716 | Returns a list of all available drivers by searching for C<DBD::*> modules |
| 1717 | through the directories in C<@INC>. By default, a warning is given if |
| 1718 | some drivers are hidden by others of the same name in earlier |
| 1719 | directories. Passing a true value for C<$quiet> will inhibit the warning. |
| 1720 | |
| 1721 | |
| 1722 | =item C<data_sources> |
| 1723 | |
| 1724 | @ary = DBI->data_sources($driver); |
| 1725 | @ary = DBI->data_sources($driver, \%attr); |
| 1726 | |
| 1727 | Returns a list of all data sources (databases) available via the named |
| 1728 | driver. If C<$driver> is empty or C<undef>, then the value of the |
| 1729 | C<DBI_DRIVER> environment variable is used. |
| 1730 | |
| 1731 | The driver will be loaded if it hasn't been already. Note that if the |
| 1732 | driver loading fails then it I<dies> with an error message that |
| 1733 | includes the string "C<install_driver>" and the underlying problem. |
| 1734 | |
| 1735 | Data sources are returned in a form suitable for passing to the |
| 1736 | L</connect> method (that is, they will include the "C<dbi:$driver:>" prefix). |
| 1737 | |
| 1738 | Note that many drivers have no way of knowing what data sources might |
| 1739 | be available for it. These drivers return an empty or incomplete list |
| 1740 | or may require driver-specific attributes, such as a connected database |
| 1741 | handle, to be supplied. |
| 1742 | |
| 1743 | |
| 1744 | =item C<trace> |
| 1745 | |
| 1746 | DBI->trace($trace_level) |
| 1747 | DBI->trace($trace_level, $trace_filename) |
| 1748 | |
| 1749 | DBI trace information can be enabled for all handles using the C<trace> |
| 1750 | DBI class method. To enable trace information for a specific handle, use |
| 1751 | the similar C<$h->E<gt>C<trace> method described elsewhere. |
| 1752 | |
| 1753 | Trace levels are as follows: |
| 1754 | |
| 1755 | 0 - Trace disabled. |
| 1756 | 1 - Trace DBI method calls returning with results or errors. |
| 1757 | 2 - Trace method entry with parameters and returning with results. |
| 1758 | 3 - As above, adding some high-level information from the driver |
| 1759 | and some internal information from the DBI. |
| 1760 | 4 - As above, adding more detailed information from the driver. |
| 1761 | Also includes DBI mutex information when using threaded Perl. |
| 1762 | 5 and above - As above but with more and more obscure information. |
| 1763 | |
| 1764 | Trace level 1 is best for a simple overview of what's happening. |
| 1765 | Trace level 2 is a good choice for general purpose tracing. Levels 3 |
| 1766 | and above (up to 9) are best reserved for investigating a |
| 1767 | specific problem, when you need to see "inside" the driver and DBI. |
| 1768 | |
| 1769 | The trace output is detailed and typically very useful. Much of the |
| 1770 | trace output is formatted using the L</neat> function, so strings |
| 1771 | in the trace output may be edited and truncated. |
| 1772 | |
| 1773 | Initially trace output is written to C<STDERR>. If C<$trace_filename> is |
| 1774 | specified and can be opened in append mode then all trace |
| 1775 | output (including that from other handles) is redirected to that file. |
| 1776 | A warning is generated is the file can't be opened. |
| 1777 | Further calls to C<trace> without a C<$trace_filename> do not alter where |
| 1778 | the trace output is sent. If C<$trace_filename> is undefined, then |
| 1779 | trace output is sent to C<STDERR> and the previous trace file is closed. |
| 1780 | The C<trace> method returns the I<previous> tracelevel. |
| 1781 | |
| 1782 | See also the C<$h->E<gt>C<trace> and C<$h->E<gt>C<trace_msg> methods and the |
| 1783 | L</DEBUGGING> section |
| 1784 | for information about the C<DBI_TRACE> environment variable. |
| 1785 | |
| 1786 | |
| 1787 | =back |
| 1788 | |
| 1789 | |
| 1790 | =head2 DBI Utility Functions |
| 1791 | |
| 1792 | In addition to the methods listed in the previous section, |
| 1793 | the DBI package also provides these utility functions: |
| 1794 | |
| 1795 | =over 4 |
| 1796 | |
| 1797 | =item C<neat> |
| 1798 | |
| 1799 | $str = DBI::neat($value, $maxlen); |
| 1800 | |
| 1801 | Return a string containing a neat (and tidy) representation of the |
| 1802 | supplied value. |
| 1803 | |
| 1804 | Strings will be quoted, although internal quotes will I<not> be escaped. |
| 1805 | Values known to be numeric will be unquoted. Undefined (NULL) values |
| 1806 | will be shown as C<undef> (without quotes). Unprintable characters will |
| 1807 | be replaced by dot (.). |
| 1808 | |
| 1809 | For result strings longer than C<$maxlen> the result string will be |
| 1810 | truncated to C<$maxlen-4> and "C<...'>" will be appended. If C<$maxlen> is 0 |
| 1811 | or C<undef>, it defaults to C<$DBI::neat_maxlen> which, in turn, defaults to 400. |
| 1812 | |
| 1813 | This function is designed to format values for human consumption. |
| 1814 | It is used internally by the DBI for L</trace> output. It should |
| 1815 | typically I<not> be used for formatting values for database use. |
| 1816 | (See also L</quote>.) |
| 1817 | |
| 1818 | =item C<neat_list> |
| 1819 | |
| 1820 | $str = DBI::neat_list(\@listref, $maxlen, $field_sep); |
| 1821 | |
| 1822 | Calls C<DBI::neat> on each element of the list and returns a string |
| 1823 | containing the results joined with C<$field_sep>. C<$field_sep> defaults |
| 1824 | to C<", ">. |
| 1825 | |
| 1826 | =item C<looks_like_number> |
| 1827 | |
| 1828 | @bool = DBI::looks_like_number(@array); |
| 1829 | |
| 1830 | Returns true for each element that looks like a number. |
| 1831 | Returns false for each element that does not look like a number. |
| 1832 | Returns C<undef> for each element that is undefined or empty. |
| 1833 | |
| 1834 | =back |
| 1835 | |
| 1836 | |
| 1837 | =head2 DBI Dynamic Attributes |
| 1838 | |
| 1839 | Dynamic attributes are always associated with the I<last handle used> |
| 1840 | (that handle is represented by C<$h> in the descriptions below). |
| 1841 | |
| 1842 | Where an attribute is equivalent to a method call, then refer to |
| 1843 | the method call for all related documentation. |
| 1844 | |
| 1845 | Warning: these attributes are provided as a convenience but they |
| 1846 | do have limitations. Specifically, they have a short lifespan: |
| 1847 | because they are associated with |
| 1848 | the last handle used, they should only be used I<immediately> after |
| 1849 | calling the method that "sets" them. |
| 1850 | If in any doubt, use the corresponding method call. |
| 1851 | |
| 1852 | =over 4 |
| 1853 | |
| 1854 | =item C<$DBI::err> |
| 1855 | |
| 1856 | Equivalent to C<$h->E<gt>C<err>. |
| 1857 | |
| 1858 | =item C<$DBI::errstr> |
| 1859 | |
| 1860 | Equivalent to C<$h->E<gt>C<errstr>. |
| 1861 | |
| 1862 | =item C<$DBI::state> |
| 1863 | |
| 1864 | Equivalent to C<$h->E<gt>C<state>. |
| 1865 | |
| 1866 | =item C<$DBI::rows> |
| 1867 | |
| 1868 | Equivalent to C<$h->E<gt>C<rows>. Please refer to the documentation |
| 1869 | for the L</rows> method. |
| 1870 | |
| 1871 | =item C<$DBI::lasth> |
| 1872 | |
| 1873 | Returns the DBI object handle used for the most recent DBI method call. |
| 1874 | If the last DBI method call was a DESTROY then $DBI::lasth will return |
| 1875 | the handle of the parent of the destroyed handle, if there is one. |
| 1876 | |
| 1877 | =back |
| 1878 | |
| 1879 | |
| 1880 | =head1 METHODS COMMON TO ALL HANDLES |
| 1881 | |
| 1882 | The following methods can be used by all types of DBI handles. |
| 1883 | |
| 1884 | =over 4 |
| 1885 | |
| 1886 | =item C<err> |
| 1887 | |
| 1888 | $rv = $h->err; |
| 1889 | |
| 1890 | Returns the I<native> database engine error code from the last driver |
| 1891 | method called. The code is typically an integer but you should not |
| 1892 | assume that. |
| 1893 | |
| 1894 | The DBI resets $h->err to undef before most DBI method calls, so the |
| 1895 | value only has a short lifespan. Also, most drivers share the same |
| 1896 | error variables across all their handles, so calling a method on |
| 1897 | one handle will typically reset the error on all the other handles |
| 1898 | that are children of that driver. |
| 1899 | |
| 1900 | If you need to test for individual errors I<and> have your program be |
| 1901 | portable to different database engines, then you'll need to determine |
| 1902 | what the corresponding error codes are for all those engines and test for |
| 1903 | all of them. |
| 1904 | |
| 1905 | =item C<errstr> |
| 1906 | |
| 1907 | $str = $h->errstr; |
| 1908 | |
| 1909 | Returns the native database engine error message from the last driver |
| 1910 | method called. This has the same lifespan issues as the L</err> method |
| 1911 | described above. |
| 1912 | |
| 1913 | =item C<state> |
| 1914 | |
| 1915 | $str = $h->state; |
| 1916 | |
| 1917 | Returns an error code in the standard SQLSTATE five character format. |
| 1918 | Note that the specific success code C<00000> is translated to 'C<>' |
| 1919 | (false). If the driver does not support SQLSTATE (and most don't), |
| 1920 | then state will return C<S1000> (General Error) for all errors. |
| 1921 | |
| 1922 | The driver is free to return any value via C<state>, e.g., warning |
| 1923 | codes, even if it has not declared an error by returning a true value |
| 1924 | via the L</err> method described above. |
| 1925 | |
| 1926 | =item C<trace> |
| 1927 | |
| 1928 | $h->trace($trace_level); |
| 1929 | $h->trace($trace_level, $trace_filename); |
| 1930 | |
| 1931 | DBI trace information can be enabled for a specific handle (and any |
| 1932 | future children of that handle) by setting the trace level using the |
| 1933 | C<trace> method. |
| 1934 | |
| 1935 | Trace level 1 is best for a simple overview of what's happening. |
| 1936 | Trace level 2 is a good choice for general purpose tracing. Levels 3 |
| 1937 | and above (up to 9) are best reserved for investigating a |
| 1938 | specific problem, when you need to see "inside" the driver and DBI. |
| 1939 | Set C<$trace_level> to 0 to disable the trace. |
| 1940 | |
| 1941 | The trace output is detailed and typically very useful. Much of the |
| 1942 | trace output is formatted using the L</neat> function, so strings |
| 1943 | in the trace output may be edited and truncated. |
| 1944 | |
| 1945 | Initially, trace output is written to C<STDERR>. If C<$trace_filename> is |
| 1946 | specified, then the file is opened in append mode and I<all> trace |
| 1947 | output (including that from other handles) is redirected to that file. |
| 1948 | Further calls to trace without a C<$trace_filename> do not alter where |
| 1949 | the trace output is sent. If C<$trace_filename> is undefined, then |
| 1950 | trace output is sent to C<STDERR> and the previous trace file is closed. |
| 1951 | |
| 1952 | See also the C<DBI->E<gt>C<trace> method and L</DEBUGGING> for information |
| 1953 | about the C<DBI_TRACE> environment variable. |
| 1954 | |
| 1955 | |
| 1956 | =item C<trace_msg> |
| 1957 | |
| 1958 | $h->trace_msg($message_text); |
| 1959 | $h->trace_msg($message_text, $min_level); |
| 1960 | |
| 1961 | Writes C<$message_text> to the trace file if trace is enabled for C<$h> or |
| 1962 | for the DBI as a whole. Can also be called as C<DBI->E<gt>C<trace_msg($msg)>. |
| 1963 | See L</trace>. |
| 1964 | |
| 1965 | If C<$min_level> is defined, then the message is output only if the trace |
| 1966 | level is equal to or greater than that level. C<$min_level> defaults to 1. |
| 1967 | |
| 1968 | |
| 1969 | =item C<func> |
| 1970 | |
| 1971 | $h->func(@func_arguments, $func_name); |
| 1972 | |
| 1973 | The C<func> method can be used to call private non-standard and |
| 1974 | non-portable methods implemented by the driver. Note that the function |
| 1975 | name is given as the last argument. |
| 1976 | |
| 1977 | This method is not directly related to calling stored procedures. |
| 1978 | Calling stored procedures is currently not defined by the DBI. |
| 1979 | Some drivers, such as DBD::Oracle, support it in non-portable ways. |
| 1980 | See driver documentation for more details. |
| 1981 | |
| 1982 | =back |
| 1983 | |
| 1984 | |
| 1985 | =head1 ATTRIBUTES COMMON TO ALL HANDLES |
| 1986 | |
| 1987 | These attributes are common to all types of DBI handles. |
| 1988 | |
| 1989 | Some attributes are inherited by child handles. That is, the value |
| 1990 | of an inherited attribute in a newly created statement handle is the |
| 1991 | same as the value in the parent database handle. Changes to attributes |
| 1992 | in the new statement handle do not affect the parent database handle |
| 1993 | and changes to the database handle do not affect existing statement |
| 1994 | handles, only future ones. |
| 1995 | |
| 1996 | Attempting to set or get the value of an unknown attribute is fatal, |
| 1997 | except for private driver specific attributes (which all have names |
| 1998 | starting with a lowercase letter). |
| 1999 | |
| 2000 | Example: |
| 2001 | |
| 2002 | $h->{AttributeName} = ...; # set/write |
| 2003 | ... = $h->{AttributeName}; # get/read |
| 2004 | |
| 2005 | =over 4 |
| 2006 | |
| 2007 | =item C<Warn> (boolean, inherited) |
| 2008 | |
| 2009 | Enables useful warnings for certain bad practices. Enabled by default. Some |
| 2010 | emulation layers, especially those for Perl 4 interfaces, disable warnings. |
| 2011 | Since warnings are generated using the Perl C<warn> function, they can be |
| 2012 | intercepted using the Perl C<$SIG{__WARN__}> hook. |
| 2013 | |
| 2014 | =item C<Active> (boolean, read-only) |
| 2015 | |
| 2016 | True if the handle object is "active". This is rarely used in |
| 2017 | applications. The exact meaning of active is somewhat vague at the |
| 2018 | moment. For a database handle it typically means that the handle is |
| 2019 | connected to a database (C<$dbh->E<gt>C<disconnect> sets C<Active> off). For |
| 2020 | a statement handle it typically means that the handle is a C<SELECT> |
| 2021 | that may have more data to fetch. (Fetching all the data or calling C<$sth->E<gt>C<finish> |
| 2022 | sets C<Active> off.) |
| 2023 | |
| 2024 | =item C<Kids> (integer, read-only) |
| 2025 | |
| 2026 | For a driver handle, C<Kids> is the number of currently existing database |
| 2027 | handles that were created from that driver handle. For a database |
| 2028 | handle, C<Kids> is the number of currently existing statement handles that |
| 2029 | were created from that database handle. |
| 2030 | |
| 2031 | =item C<ActiveKids> (integer, read-only) |
| 2032 | |
| 2033 | Like C<Kids>, but only counting those that are C<Active> (as above). |
| 2034 | |
| 2035 | =item C<CachedKids> (hash ref) |
| 2036 | |
| 2037 | For a database handle, returns a reference to the cache (hash) of |
| 2038 | statement handles created by the L</prepare_cached> method. For a |
| 2039 | driver handle, returns a reference to the cache (hash) of |
| 2040 | database handles created by the L</connect_cached> method. |
| 2041 | |
| 2042 | =item C<CompatMode> (boolean, inherited) |
| 2043 | |
| 2044 | Used by emulation layers (such as Oraperl) to enable compatible behavior |
| 2045 | in the underlying driver (e.g., DBD::Oracle) for this handle. Not normally |
| 2046 | set by application code. |
| 2047 | |
| 2048 | =item C<InactiveDestroy> (boolean) |
| 2049 | |
| 2050 | This attribute can be used to disable the I<database engine> related |
| 2051 | effect of DESTROYing a handle (which would normally close a prepared |
| 2052 | statement or disconnect from the database etc). |
| 2053 | |
| 2054 | For a database handle, this attribute does not disable an I<explicit> |
| 2055 | call to the disconnect method, only the implicit call from DESTROY. |
| 2056 | |
| 2057 | This attribute is specifically designed for use in Unix applications |
| 2058 | that "fork" child processes. Either the parent or the child process, |
| 2059 | but not both, should set C<InactiveDestroy> on all their shared handles. |
| 2060 | Note that some databases, including Oracle, don't support passing a |
| 2061 | database connection across a fork. |
| 2062 | |
| 2063 | =item C<PrintError> (boolean, inherited) |
| 2064 | |
| 2065 | This attribute can be used to force errors to generate warnings (using |
| 2066 | C<warn>) in addition to returning error codes in the normal way. When set |
| 2067 | "on", any method which results in an error occuring will cause the DBI to |
| 2068 | effectively do a C<warn("$class $method failed: $DBI::errstr")> where C<$class> |
| 2069 | is the driver class and C<$method> is the name of the method which failed. E.g., |
| 2070 | |
| 2071 | DBD::Oracle::db prepare failed: ... error text here ... |
| 2072 | |
| 2073 | By default, C<DBI->E<gt>C<connect> sets C<PrintError> "on". |
| 2074 | |
| 2075 | If desired, the warnings can be caught and processed using a C<$SIG{__WARN__}> |
| 2076 | handler or modules like CGI::Carp and CGI::ErrorWrap. |
| 2077 | |
| 2078 | =item C<RaiseError> (boolean, inherited) |
| 2079 | |
| 2080 | This attribute can be used to force errors to raise exceptions rather |
| 2081 | than simply return error codes in the normal way. It is "off" by default. |
| 2082 | When set "on", any method which results in an error will cause |
| 2083 | the DBI to effectively do a C<die("$class $method failed: $DBI::errstr")>, |
| 2084 | where C<$class> is the driver class and C<$method> is the name of the method |
| 2085 | that failed. E.g., |
| 2086 | |
| 2087 | DBD::Oracle::db prepare failed: ... error text here ... |
| 2088 | |
| 2089 | If you turn C<RaiseError> on then you'd normally turn C<PrintError> off. |
| 2090 | If C<PrintError> is also on, then the C<PrintError> is done first (naturally). |
| 2091 | |
| 2092 | Typically C<RaiseError> is used in conjunction with C<eval { ... }> |
| 2093 | to catch the exception that's been thrown and followed by an |
| 2094 | C<if ($@) { ... }> block to handle the caught exception. In that eval |
| 2095 | block the $DBI::lasth variable can be useful for diagnosis and reporting. |
| 2096 | For example, $DBI::lasth->{Type} and $DBI::lasth->{Statement}. |
| 2097 | |
| 2098 | If you want to temporarily turn C<RaiseError> off (inside a library function |
| 2099 | that is likely to fail, for example), the recommended way is like this: |
| 2100 | |
| 2101 | { |
| 2102 | local $h->{RaiseError}; # localize and turn off for this block |
| 2103 | ... |
| 2104 | } |
| 2105 | |
| 2106 | The original value will automatically and reliably be restored by Perl, |
| 2107 | regardless of how the block is exited. |
| 2108 | The same logic applies to other attributes, including C<PrintError>. |
| 2109 | |
| 2110 | Sadly, this doesn't work for Perl versions up to and including 5.004_04. |
| 2111 | Even more sadly, for Perl 5.5 and 5.6.0 it does work but leaks memory! |
| 2112 | For backwards compatibility, you could just use C<eval { ... }> instead. |
| 2113 | |
| 2114 | |
| 2115 | =item C<ShowErrorStatement> (boolean, inherited) I<NEW> |
| 2116 | |
| 2117 | This attribute can be used to cause the relevant Statement text to be |
| 2118 | appended to the error messages generated by the C<RaiseError> and |
| 2119 | C<PrintError> attributes. Only applies to errors on statement handles |
| 2120 | plus the prepare() and do() database handle methods. |
| 2121 | (The exact format of the appended text is subject to change.) |
| 2122 | |
| 2123 | |
| 2124 | =item C<ChopBlanks> (boolean, inherited) |
| 2125 | |
| 2126 | This attribute can be used to control the trimming of trailing space |
| 2127 | characters from fixed width character (CHAR) fields. No other field |
| 2128 | types are affected, even where field values have trailing spaces. |
| 2129 | |
| 2130 | The default is false (although it is possible that the default may change). |
| 2131 | Applications that need specific behavior should set the attribute as |
| 2132 | needed. Emulation interfaces should set the attribute to match the |
| 2133 | behavior of the interface they are emulating. |
| 2134 | |
| 2135 | Drivers are not required to support this attribute, but any driver which |
| 2136 | does not support it must arrange to return C<undef> as the attribute value. |
| 2137 | |
| 2138 | |
| 2139 | =item C<LongReadLen> (unsigned integer, inherited) |
| 2140 | |
| 2141 | This attribute may be used to control the maximum length of long fields |
| 2142 | ("blob", "memo", etc.) which the driver will read from the |
| 2143 | database automatically when it fetches each row of data. The |
| 2144 | C<LongReadLen> attribute only relates to fetching and reading long values; it |
| 2145 | is not involved in inserting or updating them. |
| 2146 | |
| 2147 | A value of 0 means not to automatically fetch any long data. (C<fetch> |
| 2148 | should return C<undef> for long fields when C<LongReadLen> is 0.) |
| 2149 | |
| 2150 | The default is typically 0 (zero) bytes but may vary between drivers. |
| 2151 | Applications fetching long fields should set this value to slightly |
| 2152 | larger than the longest long field value to be fetched. |
| 2153 | |
| 2154 | Some databases return some long types encoded as pairs of hex digits. |
| 2155 | For these types, C<LongReadLen> relates to the underlying data length and not the |
| 2156 | doubled-up length of the encoded string. |
| 2157 | |
| 2158 | Changing the value of C<LongReadLen> for a statement handle after it |
| 2159 | has been C<prepare>'d will typically have no effect, so it's common to |
| 2160 | set C<LongReadLen> on the C<$dbh> before calling C<prepare>. |
| 2161 | |
| 2162 | Note that the value used here has a direct effect on the memory used |
| 2163 | by the application, so don't be too generous. |
| 2164 | |
| 2165 | See L</LongTruncOk> for more information on truncation behavior. |
| 2166 | |
| 2167 | =item C<LongTruncOk> (boolean, inherited) |
| 2168 | |
| 2169 | This attribute may be used to control the effect of fetching a long |
| 2170 | field value which has been truncated (typically because it's longer |
| 2171 | than the value of the C<LongReadLen> attribute). |
| 2172 | |
| 2173 | By default, C<LongTruncOk> is false and so fetching a long value that |
| 2174 | needs to be truncated will cause the fetch to fail. |
| 2175 | (Applications should always be sure to |
| 2176 | check for errors after a fetch loop in case an error, such as a divide |
| 2177 | by zero or long field truncation, caused the fetch to terminate |
| 2178 | prematurely.) |
| 2179 | |
| 2180 | If a fetch fails due to a long field truncation when C<LongTruncOk> is |
| 2181 | false, many drivers will allow you to continue fetching further rows. |
| 2182 | |
| 2183 | See also L</LongReadLen>. |
| 2184 | |
| 2185 | =item C<Taint> (boolean, inherited) |
| 2186 | |
| 2187 | If this attribute is set to a true value I<and> Perl is running in |
| 2188 | taint mode (e.g., started with the C<-T> option), then all data |
| 2189 | fetched from the database is tainted, and the arguments to most DBI |
| 2190 | method calls are checked for being tainted. I<This may change.> |
| 2191 | |
| 2192 | The attribute defaults to off, even if Perl is in taint mode. |
| 2193 | See L<perlsec> for more about taint mode. If Perl is not |
| 2194 | running in taint mode, this attribute has no effect. |
| 2195 | |
| 2196 | When fetching data that you trust you can turn off the Taint attribute, |
| 2197 | for that statement handle, for the duration of the fetch loop. |
| 2198 | |
| 2199 | Currently only fetched data is tainted. It is possible that the results |
| 2200 | of other DBI method calls, and the value of fetched attributes, may |
| 2201 | also be tainted in future versions. That change may well break your |
| 2202 | applications unless you take great care now. If you use DBI Taint mode, |
| 2203 | please report your experience and any suggestions for changes. |
| 2204 | |
| 2205 | |
| 2206 | =item C<private_*> |
| 2207 | |
| 2208 | The DBI provides a way to store extra information in a DBI handle as |
| 2209 | "private" attributes. The DBI will allow you to store and retreive any |
| 2210 | attribute which has a name starting with "C<private_>". It is strongly |
| 2211 | recommended that you use just I<one> private attribute (e.g., use a |
| 2212 | hash ref) and give it a long and unambiguous name that includes the |
| 2213 | module or application name that the attribute relates to (e.g., |
| 2214 | "C<private_YourFullModuleName_thingy>"). |
| 2215 | |
| 2216 | =back |
| 2217 | |
| 2218 | |
| 2219 | =head1 DBI DATABASE HANDLE OBJECTS |
| 2220 | |
| 2221 | This section covers the methods and attributes associated with |
| 2222 | database handles. |
| 2223 | |
| 2224 | =head2 Database Handle Methods |
| 2225 | |
| 2226 | The following methods are specified for DBI database handles: |
| 2227 | |
| 2228 | =over 4 |
| 2229 | |
| 2230 | =item C<do> |
| 2231 | |
| 2232 | $rc = $dbh->do($statement) or die $dbh->errstr; |
| 2233 | $rc = $dbh->do($statement, \%attr) or die $dbh->errstr; |
| 2234 | $rv = $dbh->do($statement, \%attr, @bind_values) or ... |
| 2235 | |
| 2236 | Prepare and execute a single statement. Returns the number of rows |
| 2237 | affected or C<undef> on error. A return value of C<-1> means the |
| 2238 | number of rows is not known or is not available. |
| 2239 | |
| 2240 | This method is typically most useful for I<non>-C<SELECT> statements that |
| 2241 | either cannot be prepared in advance (due to a limitation of the |
| 2242 | driver) or do not need to be executed repeatedly. It should not |
| 2243 | be used for C<SELECT> statements because it does not return a statement |
| 2244 | handle (so you can't fetch any data). |
| 2245 | |
| 2246 | The default C<do> method is logically similar to: |
| 2247 | |
| 2248 | sub do { |
| 2249 | my($dbh, $statement, $attr, @bind_values) = @_; |
| 2250 | my $sth = $dbh->prepare($statement, $attr) or return undef; |
| 2251 | $sth->execute(@bind_values) or return undef; |
| 2252 | my $rows = $sth->rows; |
| 2253 | ($rows == 0) ? "0E0" : $rows; # always return true if no error |
| 2254 | } |
| 2255 | |
| 2256 | For example: |
| 2257 | |
| 2258 | my $rows_deleted = $dbh->do(q{ |
| 2259 | DELETE FROM table |
| 2260 | WHERE status = ? |
| 2261 | }, undef, 'DONE') or die $dbh->errstr; |
| 2262 | |
| 2263 | Using placeholders and C<@bind_values> with the C<do> method can be |
| 2264 | useful because it avoids the need to correctly quote any variables |
| 2265 | in the C<$statement>. But if you'll be executing the statement many |
| 2266 | times then it's more efficient to C<prepare> it once and call |
| 2267 | C<execute> many times instead. |
| 2268 | |
| 2269 | The C<q{...}> style quoting used in this example avoids clashing with |
| 2270 | quotes that may be used in the SQL statement. Use the double-quote-like |
| 2271 | C<qq{...}> operator if you want to interpolate variables into the string. |
| 2272 | See L<perlop/"Quote and Quote-like Operators"> for more details. |
| 2273 | |
| 2274 | =item C<selectrow_array> |
| 2275 | |
| 2276 | @row_ary = $dbh->selectrow_array($statement); |
| 2277 | @row_ary = $dbh->selectrow_array($statement, \%attr); |
| 2278 | @row_ary = $dbh->selectrow_array($statement, \%attr, @bind_values); |
| 2279 | |
| 2280 | This utility method combines L</prepare>, L</execute> and |
| 2281 | L</fetchrow_array> into a single call. If called in a list context, it |
| 2282 | returns the first row of data from the statement. If called in a scalar |
| 2283 | context, it returns the first field of the first row. The C<$statement> |
| 2284 | parameter can be a previously prepared statement handle, in which case |
| 2285 | the C<prepare> is skipped. |
| 2286 | |
| 2287 | If any method fails, and L</RaiseError> is not set, C<selectrow_array> |
| 2288 | will return an empty list. |
| 2289 | |
| 2290 | In a scalar context, C<selectrow_array> returns the value of the first |
| 2291 | field. An C<undef> is returned if there are no matching rows or an error |
| 2292 | occurred. Since that C<undef> can't be distinguished from an C<undef> returned |
| 2293 | because the first field value was NULL, calling C<selectrow_array> in |
| 2294 | a scalar context should be used with caution. |
| 2295 | |
| 2296 | |
| 2297 | =item C<selectall_arrayref> |
| 2298 | |
| 2299 | $ary_ref = $dbh->selectall_arrayref($statement); |
| 2300 | $ary_ref = $dbh->selectall_arrayref($statement, \%attr); |
| 2301 | $ary_ref = $dbh->selectall_arrayref($statement, \%attr, @bind_values); |
| 2302 | |
| 2303 | This utility method combines L</prepare>, L</execute> and |
| 2304 | L</fetchall_arrayref> into a single call. It returns a reference to an |
| 2305 | array containing a reference to an array for each row of data fetched. |
| 2306 | |
| 2307 | The C<$statement> parameter can be a previously prepared statement handle, |
| 2308 | in which case the C<prepare> is skipped. This is recommended if the |
| 2309 | statement is going to be executed many times. |
| 2310 | |
| 2311 | If L</RaiseError> is not set and any method except C<fetchall_arrayref> |
| 2312 | fails then C<selectall_arrayref> will return C<undef>; if |
| 2313 | C<fetchall_arrayref> fails then it will return with whatever data it |
| 2314 | has been fetched thus far. $DBI::err should be checked to catch that. |
| 2315 | |
| 2316 | |
| 2317 | =item C<selectall_hashref> |
| 2318 | |
| 2319 | $ary_ref = $dbh->selectall_hashref($statement); |
| 2320 | $ary_ref = $dbh->selectall_hashref($statement, \%attr); |
| 2321 | $ary_ref = $dbh->selectall_hashref($statement, \%attr, @bind_values); |
| 2322 | |
| 2323 | This utility method combines L</prepare>, L</execute> and |
| 2324 | L</fetchrow_hashref> into a single call. It returns a reference to an |
| 2325 | array containing, for each row of data fetched, a reference to a hash |
| 2326 | containing field name and value pairs for that row. |
| 2327 | |
| 2328 | The C<$statement> parameter can be a previously prepared statement handle, |
| 2329 | in which case the C<prepare> is skipped. This is recommended if the |
| 2330 | statement is going to be executed many times. |
| 2331 | |
| 2332 | If any method except C<fetchrow_hashref> fails, and L</RaiseError> is not set, |
| 2333 | C<selectall_hashref> will return C<undef>. If C<fetchrow_hashref> fails and |
| 2334 | L</RaiseError> is not set, then it will return with whatever data it |
| 2335 | has fetched thus far. $DBI::err should be checked to catch that. |
| 2336 | |
| 2337 | |
| 2338 | =item C<selectcol_arrayref> |
| 2339 | |
| 2340 | $ary_ref = $dbh->selectcol_arrayref($statement); |
| 2341 | $ary_ref = $dbh->selectcol_arrayref($statement, \%attr); |
| 2342 | $ary_ref = $dbh->selectcol_arrayref($statement, \%attr, @bind_values); |
| 2343 | |
| 2344 | This utility method combines L</prepare>, L</execute>, and fetching one |
| 2345 | column from all the rows, into a single call. It returns a reference to |
| 2346 | an array containing the values of the first column from each row. |
| 2347 | |
| 2348 | The C<$statement> parameter can be a previously prepared statement handle, |
| 2349 | in which case the C<prepare> is skipped. This is recommended if the |
| 2350 | statement is going to be executed many times. |
| 2351 | |
| 2352 | If any method except C<fetch> fails, and L</RaiseError> is not set, |
| 2353 | C<selectcol_arrayref> will return C<undef>. If C<fetch> fails and |
| 2354 | L</RaiseError> is not set, then it will return with whatever data it |
| 2355 | has fetched thus far. $DBI::err should be checked to catch that. |
| 2356 | |
| 2357 | |
| 2358 | =item C<prepare> |
| 2359 | |
| 2360 | $sth = $dbh->prepare($statement) or die $dbh->errstr; |
| 2361 | $sth = $dbh->prepare($statement, \%attr) or die $dbh->errstr; |
| 2362 | |
| 2363 | Prepares a single statement for later execution by the database |
| 2364 | engine and returns a reference to a statement handle object. |
| 2365 | |
| 2366 | The returned statement handle can be used to get attributes of the |
| 2367 | statement and invoke the L</execute> method. See L</Statement Handle Methods>. |
| 2368 | |
| 2369 | Drivers for engines without the concept of preparing a |
| 2370 | statement will typically just store the statement in the returned |
| 2371 | handle and process it when C<$sth->E<gt>C<execute> is called. Such drivers are |
| 2372 | unlikely to give much useful information about the |
| 2373 | statement, such as C<$sth->E<gt>C<{NUM_OF_FIELDS}>, until after C<$sth->E<gt>C<execute> |
| 2374 | has been called. Portable applications should take this into account. |
| 2375 | |
| 2376 | In general, DBI drivers do not parse the contents of the statement |
| 2377 | (other than simply counting any L</Placeholders>). The statement is |
| 2378 | passed directly to the database engine, sometimes known as pass-thru |
| 2379 | mode. This has advantages and disadvantages. On the plus side, you can |
| 2380 | access all the functionality of the engine being used. On the downside, |
| 2381 | you're limited if you're using a simple engine, and you need to take extra care if |
| 2382 | writing applications intended to be portable between engines. |
| 2383 | |
| 2384 | Portable applications should not assume that a new statement can be |
| 2385 | prepared and/or executed while still fetching results from a previous |
| 2386 | statement. |
| 2387 | |
| 2388 | Some command-line SQL tools use statement terminators, like a semicolon, |
| 2389 | to indicate the end of a statement. Such terminators should not normally |
| 2390 | be used with the DBI. |
| 2391 | |
| 2392 | |
| 2393 | =item C<prepare_cached> |
| 2394 | |
| 2395 | $sth = $dbh->prepare_cached($statement) |
| 2396 | $sth = $dbh->prepare_cached($statement, \%attr) |
| 2397 | $sth = $dbh->prepare_cached($statement, \%attr, $allow_active) |
| 2398 | |
| 2399 | Like L</prepare> except that the statement handle returned will be |
| 2400 | stored in a hash associated with the C<$dbh>. If another call is made to |
| 2401 | C<prepare_cached> with the same C<$statement> and C<%attr> values, then the |
| 2402 | corresponding cached C<$sth> will be returned without contacting the |
| 2403 | database server. |
| 2404 | |
| 2405 | This caching can be useful in some applications, but it can also cause |
| 2406 | problems and should be used with care. If the cached C<$sth> being |
| 2407 | returned is active (i.e., is a C<SELECT> that may still have data to be |
| 2408 | fetched) then a warning will be generated and C<finish> will be called |
| 2409 | for you. The warning can be suppressed by setting C<$allow_active> to |
| 2410 | true. The cache can be accessed (and cleared) via the L</CachedKids> |
| 2411 | attribute. |
| 2412 | |
| 2413 | Here's an example of one possible use of C<prepare_cached>: |
| 2414 | |
| 2415 | while ( ($field, $value) = each %search_fields ) { |
| 2416 | push @sql, "$field = ?"; |
| 2417 | push @values, $value; |
| 2418 | } |
| 2419 | $qualifier = ""; |
| 2420 | $qualifier = "where ".join(" and ", @sql) if @sql; |
| 2421 | $sth = $dbh->prepare_cached("SELECT * FROM table $qualifier"); |
| 2422 | $sth->execute(@values); |
| 2423 | |
| 2424 | |
| 2425 | =item C<commit> |
| 2426 | |
| 2427 | $rc = $dbh->commit or die $dbh->errstr; |
| 2428 | |
| 2429 | Commit (make permanent) the most recent series of database changes |
| 2430 | if the database supports transactions and AutoCommit is off. |
| 2431 | |
| 2432 | If C<AutoCommit> is on, then calling |
| 2433 | C<commit> will issue a "commit ineffective with AutoCommit" warning. |
| 2434 | |
| 2435 | See also L</Transactions> in the L</FURTHER INFORMATION> section below. |
| 2436 | |
| 2437 | =item C<rollback> |
| 2438 | |
| 2439 | $rc = $dbh->rollback or die $dbh->errstr; |
| 2440 | |
| 2441 | Rollback (undo) the most recent series of uncommitted database |
| 2442 | changes if the database supports transactions and AutoCommit is off. |
| 2443 | |
| 2444 | If C<AutoCommit> is on, then calling |
| 2445 | C<rollback> will issue a "rollback ineffective with AutoCommit" warning. |
| 2446 | |
| 2447 | See also L</Transactions> in the L</FURTHER INFORMATION> section below. |
| 2448 | |
| 2449 | |
| 2450 | =item C<disconnect> |
| 2451 | |
| 2452 | $rc = $dbh->disconnect or warn $dbh->errstr; |
| 2453 | |
| 2454 | Disconnects the database from the database handle. C<disconnect> is typically only used |
| 2455 | before exiting the program. The handle is of little use after disconnecting. |
| 2456 | |
| 2457 | The transaction behavior of the C<disconnect> method is, sadly, |
| 2458 | undefined. Some database systems (such as Oracle and Ingres) will |
| 2459 | automatically commit any outstanding changes, but others (such as |
| 2460 | Informix) will rollback any outstanding changes. Applications not |
| 2461 | using C<AutoCommit> should explicitly call C<commit> or C<rollback> before |
| 2462 | calling C<disconnect>. |
| 2463 | |
| 2464 | The database is automatically disconnected by the C<DESTROY> method if |
| 2465 | still connected when there are no longer any references to the handle. |
| 2466 | The C<DESTROY> method for each driver should implicitly call C<rollback> to |
| 2467 | undo any uncommitted changes. This is vital behavior to ensure that |
| 2468 | incomplete transactions don't get committed simply because Perl calls |
| 2469 | C<DESTROY> on every object before exiting. Also, do not rely on the order |
| 2470 | of object destruction during "global destruction", as it is undefined. |
| 2471 | |
| 2472 | Generally, if you want your changes to be commited or rolled back when |
| 2473 | you disconnect, then you should explicitly call L</commit> or L</rollback> |
| 2474 | before disconnecting. |
| 2475 | |
| 2476 | If you disconnect from a database while you still have active statement |
| 2477 | handles, you will get a warning. The statement handles should either be |
| 2478 | cleared (destroyed) before disconnecting, or the C<finish> method |
| 2479 | should be called on |
| 2480 | each one. |
| 2481 | |
| 2482 | |
| 2483 | =item C<ping> |
| 2484 | |
| 2485 | $rc = $dbh->ping; |
| 2486 | |
| 2487 | Attempts to determine, in a reasonably efficient way, if the database |
| 2488 | server is still running and the connection to it is still working. |
| 2489 | Individual drivers should implement this function in the most suitable |
| 2490 | manner for their database engine. |
| 2491 | |
| 2492 | The current I<default> implementation always returns true without |
| 2493 | actually doing anything. Actually, it returns "C<0 but true>" which is |
| 2494 | true but zero. That way you can tell if the return value is genuine or |
| 2495 | just the default. Drivers should override this method with one that |
| 2496 | does the right thing for their type of database. |
| 2497 | |
| 2498 | Few applications would have direct use for this method. See the specialized |
| 2499 | Apache::DBI module for one example usage. |
| 2500 | |
| 2501 | |
| 2502 | =item C<table_info> I<NEW> |
| 2503 | |
| 2504 | B<Warning:> This method is experimental and may change. |
| 2505 | |
| 2506 | $sth = $dbh->table_info; |
| 2507 | $sth = $dbh->table_info( \%attr ); |
| 2508 | |
| 2509 | Returns an active statement handle that can be used to fetch |
| 2510 | information about tables and views that exist in the database. |
| 2511 | |
| 2512 | The following attributes (all or separate) may be used as selection criteria: |
| 2513 | |
| 2514 | %attr = ( |
| 2515 | TABLE_CAT => $CatVal # String value of the catalog name |
| 2516 | , TABLE_SCHEM => $SchVal # String value of the schema name |
| 2517 | , TABLE_NAME => $TblVal # String value of the table name |
| 2518 | , TABLE_TYPE => $TypVal # String value of the table type(s) |
| 2519 | ); |
| 2520 | |
| 2521 | Note: The support for the selection criteria is driver specific. If the |
| 2522 | driver doesn't support one or more then them then you'll get back more |
| 2523 | than you asked for and can do the filtering yourself. |
| 2524 | |
| 2525 | The arguments TABLE_CAT, TABLE_SCHEM and TABLE_NAME may accept search |
| 2526 | patterns according to the database/driver, for example: |
| 2527 | |
| 2528 | $sth = $dbh->table_info( { TABLE_NAME => '%TAB%'} ); |
| 2529 | |
| 2530 | The value of TABLE_TYPE is a comma-separated list of one or more types |
| 2531 | of tables to be returned in the result set. Each value may optionally be |
| 2532 | quoted, e.g.: |
| 2533 | |
| 2534 | $sth = $dbh->table_info( { TABLE_TYPE => "TABLE" } ); |
| 2535 | $sth = $dbh->table_info( { TABLE_TYPE => "'TABLE', 'VIEW'" } ); |
| 2536 | |
| 2537 | In addition the following special cases may also be supported by some drivers: |
| 2538 | |
| 2539 | =over 4 |
| 2540 | |
| 2541 | =item * |
| 2542 | If the value of TABLE_CAT is '%' and TABLE_SCHEM and TABLE_NAME name |
| 2543 | are empty strings, the result set contains a list of catalog names. |
| 2544 | For example: |
| 2545 | |
| 2546 | $sth = $dbh->table_info({ TABLE_CAT=>'%', TABLE_SCHEM=>'', TABLE_NAME=>'' }); |
| 2547 | |
| 2548 | =item * |
| 2549 | If the value of TABLE_SCHEM is '%' and TABLE_CAT and TABLE_NAME are |
| 2550 | empty strings, the result set contains a list of schema names. |
| 2551 | |
| 2552 | =item * |
| 2553 | If the value of TABLE_TYPE is '%' and TABLE_CAT, TABLE_SCHEM, and |
| 2554 | TABLE_NAME are all empty strings, the result set contains a list of |
| 2555 | table types. |
| 2556 | |
| 2557 | =back |
| 2558 | |
| 2559 | The statement handle returned has at least the following fields in the |
| 2560 | order show below. Other fields, after these, may also be present. |
| 2561 | |
| 2562 | B<TABLE_CAT>: Table catalog identifier. This field is NULL (C<undef>) if not |
| 2563 | applicable to the data source, which is usually the case. This field |
| 2564 | is empty if not applicable to the table. |
| 2565 | |
| 2566 | B<TABLE_SCHEM>: The name of the schema containing the TABLE_NAME value. |
| 2567 | This field is NULL (C<undef>) if not applicable to data source, and |
| 2568 | empty if not applicable to the table. |
| 2569 | |
| 2570 | B<TABLE_NAME>: Name of the table (or view, synonym, etc). |
| 2571 | |
| 2572 | B<TABLE_TYPE>: One of the following: "TABLE", "VIEW", "SYSTEM TABLE", |
| 2573 | "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM" or a type |
| 2574 | identifier that is specific to the data |
| 2575 | source. |
| 2576 | |
| 2577 | B<REMARKS>: A description of the table. May be NULL (C<undef>). |
| 2578 | |
| 2579 | Note that C<table_info> might not return records for all tables. |
| 2580 | Applications can use any valid table regardless of whether it's |
| 2581 | returned by C<table_info>. See also L</tables>. |
| 2582 | |
| 2583 | For more detailed information about the fields and their meanings, |
| 2584 | you can refer to: |
| 2585 | |
| 2586 | http://msdn.microsoft.com/library/psdk/dasdk/odch6wqb.htm |
| 2587 | |
| 2588 | If that URL ceases to work then use the MSDN search facility at: |
| 2589 | |
| 2590 | http://search.microsoft.com/us/dev/ |
| 2591 | |
| 2592 | and search for C<SQLTables returns> using the exact phrase option. |
| 2593 | The link you want will probably just be called C<SQLTables> and will |
| 2594 | be part of the Data Access SDK. |
| 2595 | |
| 2596 | See also page 306 of the (very large) SQL/CLI specification: |
| 2597 | |
| 2598 | http://www.jtc1sc32.org/sc32/jtc1sc32.nsf/Attachments/DF86E81BE70151D58525699800643F56/$FILE/32N0595T.PDF |
| 2599 | |
| 2600 | =item C<tables> I<NEW> |
| 2601 | |
| 2602 | B<Warning:> This method is experimental and may change. |
| 2603 | |
| 2604 | @names = $dbh->tables; |
| 2605 | @names = $dbh->tables( \%attr ); |
| 2606 | |
| 2607 | Returns a list of table and view names, possibly including a schema prefix. |
| 2608 | This list should include all |
| 2609 | tables that can be used in a C<SELECT> statement without further |
| 2610 | qualification. |
| 2611 | |
| 2612 | Note that C<table_info> might not return records for all tables. |
| 2613 | Applications can use any valid table regardless of whether it's |
| 2614 | returned by tables. See also L</table_info>. |
| 2615 | |
| 2616 | |
| 2617 | =item C<type_info_all> I<NEW> |
| 2618 | |
| 2619 | B<Warning:> This method is experimental and may change. |
| 2620 | |
| 2621 | $type_info_all = $dbh->type_info_all; |
| 2622 | |
| 2623 | Returns a reference to an array which holds information about each data |
| 2624 | type variant supported by the database and driver. The array and its |
| 2625 | contents should be treated as read-only. |
| 2626 | |
| 2627 | The first item is a reference to an 'index' hash of C<Name =>E<gt> C<Index> pairs. |
| 2628 | The items following that are references to arrays, one per supported data |
| 2629 | type variant. The leading index hash defines the names and order of the |
| 2630 | fields within the arrays that follow it. |
| 2631 | For example: |
| 2632 | |
| 2633 | $type_info_all = [ |
| 2634 | { TYPE_NAME => 0, |
| 2635 | DATA_TYPE => 1, |
| 2636 | COLUMN_SIZE => 2, # was PRECISION originally |
| 2637 | LITERAL_PREFIX => 3, |
| 2638 | LITERAL_SUFFIX => 4, |
| 2639 | CREATE_PARAMS => 5, |
| 2640 | NULLABLE => 6, |
| 2641 | CASE_SENSITIVE => 7, |
| 2642 | SEARCHABLE => 8, |
| 2643 | UNSIGNED_ATTRIBUTE=> 9, |
| 2644 | FIXED_PREC_SCALE => 10, # was MONEY originally |
| 2645 | AUTO_UNIQUE_VALUE => 11, # was AUTO_INCREMENT originally |
| 2646 | LOCAL_TYPE_NAME => 12, |
| 2647 | MINIMUM_SCALE => 13, |
| 2648 | MAXIMUM_SCALE => 14, |
| 2649 | NUM_PREC_RADIX => 15, |
| 2650 | }, |
| 2651 | [ 'VARCHAR', SQL_VARCHAR, |
| 2652 | undef, "'","'", undef,0, 1,1,0,0,0,undef,1,255, undef |
| 2653 | ], |
| 2654 | [ 'INTEGER', SQL_INTEGER, |
| 2655 | undef, "", "", undef,0, 0,1,0,0,0,undef,0, 0, 10 |
| 2656 | ], |
| 2657 | ]; |
| 2658 | |
| 2659 | Note that more than one row may have the same value in the C<DATA_TYPE> |
| 2660 | field if there are different ways to spell the type name and/or there |
| 2661 | are variants of the type with different attributes (e.g., with and |
| 2662 | without C<AUTO_UNIQUE_VALUE> set, with and without C<UNSIGNED_ATTRIBUTE>, etc). |
| 2663 | |
| 2664 | The rows are ordered by C<DATA_TYPE> first and then by how closely each |
| 2665 | type maps to the corresponding ODBC SQL data type, closest first. |
| 2666 | |
| 2667 | The meaning of the fields is described in the documentation for |
| 2668 | the L</type_info> method. The index values shown above (e.g., |
| 2669 | C<NULLABLE =>E<gt> C<6>) are for illustration only. Drivers may define the |
| 2670 | fields with a different order. |
| 2671 | |
| 2672 | This method is not normally used directly. The L</type_info> method |
| 2673 | provides a more useful interface to the data. |
| 2674 | |
| 2675 | Even though an 'index' hash is provided, all the field names in the |
| 2676 | index hash defined above will always have the index values defined |
| 2677 | above. This is defined behaviour so that you don't need to rely on the |
| 2678 | index hash, which is handy because the lettercase of the keys is not |
| 2679 | defined. It is usually uppercase, as show here, but drivers are free to |
| 2680 | return names with any lettercase. Drivers are also free to return extra |
| 2681 | driver-specific columns of information - though it's recommended that |
| 2682 | they start at column index 50 to leave room for expansion of the |
| 2683 | DBI/ODBC specification. |
| 2684 | |
| 2685 | |
| 2686 | =item C<type_info> I<NEW> |
| 2687 | |
| 2688 | B<Warning:> This method is experimental and may change. |
| 2689 | |
| 2690 | @type_info = $dbh->type_info($data_type); |
| 2691 | |
| 2692 | Returns a list of hash references holding information about one or more |
| 2693 | variants of C<$data_type>. The list is ordered by C<DATA_TYPE> first and |
| 2694 | then by how closely each type maps to the corresponding ODBC SQL data |
| 2695 | type, closest first. If called in a scalar context then only the first |
| 2696 | (best) element is returned. |
| 2697 | |
| 2698 | If C<$data_type> is undefined or C<SQL_ALL_TYPES>, then the list will |
| 2699 | contain hashes for all data type variants supported by the database and driver. |
| 2700 | |
| 2701 | If C<$data_type> is an array reference then C<type_info> returns the |
| 2702 | information for the I<first> type in the array that has any matches. |
| 2703 | |
| 2704 | The keys of the hash follow the same letter case conventions as the |
| 2705 | rest of the DBI (see L</Naming Conventions and Name Space>). The |
| 2706 | following items should exist: |
| 2707 | |
| 2708 | =over 4 |
| 2709 | |
| 2710 | =item TYPE_NAME (string) |
| 2711 | |
| 2712 | Data type name for use in CREATE TABLE statements etc. |
| 2713 | |
| 2714 | =item DATA_TYPE (integer) |
| 2715 | |
| 2716 | SQL data type number. |
| 2717 | |
| 2718 | =item COLUMN_SIZE (integer) |
| 2719 | |
| 2720 | For numeric types, this is either the total number of digits (if the |
| 2721 | NUM_PREC_RADIX value is 10) or the total number of bits allowed in the |
| 2722 | column (if NUM_PREC_RADIX is 2). |
| 2723 | |
| 2724 | For string types, this is the maximum size of the string in bytes. |
| 2725 | |
| 2726 | For date and interval types, this is the maximum number of characters |
| 2727 | needed to display the value. |
| 2728 | |
| 2729 | =item LITERAL_PREFIX (string) |
| 2730 | |
| 2731 | Characters used to prefix a literal. A typical prefix is "C<'>" for characters, |
| 2732 | or possibly "C<0x>" for binary values passed as hexadecimal. NULL (C<undef>) is |
| 2733 | returned for data types for which this is not applicable. |
| 2734 | |
| 2735 | |
| 2736 | =item LITERAL_SUFFIX (string) |
| 2737 | |
| 2738 | Characters used to suffix a literal. Typically "C<'>" for characters. |
| 2739 | NULL (C<undef>) is returned for data types where this is not applicable. |
| 2740 | |
| 2741 | =item CREATE_PARAMS (string) |
| 2742 | |
| 2743 | Parameter names for data type definition. For example, C<CREATE_PARAMS> for a |
| 2744 | C<DECIMAL> would be "C<precision,scale>" if the DECIMAL type should be |
| 2745 | declared as C<DECIMAL(>I<precision,scale>C<)> where I<precision> and I<scale> |
| 2746 | are integer values. For a C<VARCHAR> it would be "C<max length>". |
| 2747 | NULL (C<undef>) is returned for data types for which this is not applicable. |
| 2748 | |
| 2749 | =item NULLABLE (integer) |
| 2750 | |
| 2751 | Indicates whether the data type accepts a NULL value: |
| 2752 | C<0> or an empty string = no, C<1> = yes, C<2> = unknown. |
| 2753 | |
| 2754 | =item CASE_SENSITIVE (boolean) |
| 2755 | |
| 2756 | Indicates whether the data type is case sensitive in collations and |
| 2757 | comparisons. |
| 2758 | |
| 2759 | =item SEARCHABLE (integer) |
| 2760 | |
| 2761 | Indicates how the data type can be used in a WHERE clause, as |
| 2762 | follows: |
| 2763 | |
| 2764 | 0 - Cannot be used in a WHERE clause |
| 2765 | 1 - Only with a LIKE predicate |
| 2766 | 2 - All comparison operators except LIKE |
| 2767 | 3 - Can be used in a WHERE clause with any comparison operator |
| 2768 | |
| 2769 | =item UNSIGNED_ATTRIBUTE (boolean) |
| 2770 | |
| 2771 | Indicates whether the data type is unsigned. NULL (C<undef>) is returned |
| 2772 | for data types for which this is not applicable. |
| 2773 | |
| 2774 | =item FIXED_PREC_SCALE (boolean) |
| 2775 | |
| 2776 | Indicates whether the data type always has the same precision and scale |
| 2777 | (such as a money type). NULL (C<undef>) is returned for data types |
| 2778 | for which |
| 2779 | this is not applicable. |
| 2780 | |
| 2781 | =item AUTO_UNIQUE_VALUE (boolean) |
| 2782 | |
| 2783 | Indicates whether a column of this data type is automatically set to a |
| 2784 | unique value whenever a new row is inserted. NULL (C<undef>) is returned |
| 2785 | for data types for which this is not applicable. |
| 2786 | |
| 2787 | =item LOCAL_TYPE_NAME (string) |
| 2788 | |
| 2789 | Localized version of the C<TYPE_NAME> for use in dialog with users. |
| 2790 | NULL (C<undef>) is returned if a localized name is not available (in which |
| 2791 | case C<TYPE_NAME> should be used). |
| 2792 | |
| 2793 | =item MINIMUM_SCALE (integer) |
| 2794 | |
| 2795 | The minimum scale of the data type. If a data type has a fixed scale, |
| 2796 | then C<MAXIMUM_SCALE> holds the same value. NULL (C<undef>) is returned for |
| 2797 | data types for which this is not applicable. |
| 2798 | |
| 2799 | =item MAXIMUM_SCALE (integer) |
| 2800 | |
| 2801 | The maximum scale of the data type. If a data type has a fixed scale, |
| 2802 | then C<MINIMUM_SCALE> holds the same value. NULL (C<undef>) is returned for |
| 2803 | data types for which this is not applicable. |
| 2804 | |
| 2805 | =item SQL_DATA_TYPE (integer) |
| 2806 | |
| 2807 | This column is the same as the C<DATA_TYPE> column, except for interval |
| 2808 | and datetime data types. For interval and datetime data types, the |
| 2809 | C<SQL_DATA_TYPE> field will return C<SQL_INTERVAL> or C<SQL_DATETIME>, and the |
| 2810 | C<SQL_DATETIME_SUB> field below will return the subcode for the specific |
| 2811 | interval or datetime data type. If this field is NULL, then the driver |
| 2812 | does not support or report on interval or date subtypes. |
| 2813 | |
| 2814 | =item SQL_DATETIME_SUB (integer) |
| 2815 | |
| 2816 | For interval or datetime data types, where the C<SQL_DATA_TYPE> field |
| 2817 | above is C<SQL_INTERVAL> or C<SQL_DATETIME>, this field will hold the subcode |
| 2818 | for the specific interval or datetime data type. Otherwise it will be |
| 2819 | NULL (C<undef>). |
| 2820 | |
| 2821 | =item NUM_PREC_RADIX (integer) |
| 2822 | |
| 2823 | The radix value of the data type. For approximate numeric types, |
| 2824 | C<NUM_PREC_RADIX> |
| 2825 | contains the value 2 and C<COLUMN_SIZE> holds the number of bits. For |
| 2826 | exact numeric types, C<NUM_PREC_RADIX> contains the value 10 and C<COLUMN_SIZE> holds |
| 2827 | the number of decimal digits. NULL (C<undef>) is returned either for data types |
| 2828 | for which this is not applicable or if the driver cannot report this information. |
| 2829 | |
| 2830 | =item INTERVAL_PRECISION (integer) |
| 2831 | |
| 2832 | The interval leading precision for interval types. NULL is returned |
| 2833 | either for data types for which this is not applicable or if the driver |
| 2834 | cannot report this information. |
| 2835 | |
| 2836 | =back |
| 2837 | |
| 2838 | For example, to find the type name for the fields in a select statement |
| 2839 | you can do: |
| 2840 | |
| 2841 | @names = map { scalar $dbh->type_info($_)->{TYPE_NAME} } @{ $sth->{TYPE} } |
| 2842 | |
| 2843 | Since DBI and ODBC drivers vary in how they map their types into the |
| 2844 | ISO standard types you may need to search for more than one type. |
| 2845 | Here's an example looking for a usable type to store a date: |
| 2846 | |
| 2847 | $my_date_type = $dbh->type_info( [ SQL_DATE, SQL_TIMESTAMP ] ); |
| 2848 | |
| 2849 | Similarly, to more reliably find a type to store small integers, you could |
| 2850 | use a list starting with C<SQL_SMALLINT>, C<SQL_INTEGER>, C<SQL_DECIMAL>, etc. |
| 2851 | |
| 2852 | For more detailed information about these fields and their meanings, you |
| 2853 | can refer to: |
| 2854 | |
| 2855 | http://msdn.microsoft.com/library/psdk/dasdk/odch6yy7.htm |
| 2856 | |
| 2857 | If that URL ceases to work then use the MSDN search facility at |
| 2858 | |
| 2859 | http://search.microsoft.com/us/dev/ |
| 2860 | |
| 2861 | and search the MSDN Library for C<SQLGetTypeInfo returns> using the exact phrase option. |
| 2862 | The link you want will probably just be called C<SQLGetTypeInfo> (there |
| 2863 | may be more than one). |
| 2864 | |
| 2865 | The individual data types are currently described here: |
| 2866 | |
| 2867 | http://msdn.microsoft.com/library/psdk/dasdk/odap8fcj.htm |
| 2868 | |
| 2869 | If that URL ceases to work, or to get more general information, use the |
| 2870 | MSDN search facility as described above and search for C<SQL Data Types>. |
| 2871 | |
| 2872 | =item C<quote> |
| 2873 | |
| 2874 | $sql = $dbh->quote($value); |
| 2875 | $sql = $dbh->quote($value, $data_type); |
| 2876 | |
| 2877 | Quote a string literal for use as a literal value in an SQL statement, |
| 2878 | by escaping any special characters (such as quotation marks) |
| 2879 | contained within the string and adding the required type of outer |
| 2880 | quotation marks. |
| 2881 | |
| 2882 | $sql = sprintf "SELECT foo FROM bar WHERE baz = %s", |
| 2883 | $dbh->quote("Don't"); |
| 2884 | |
| 2885 | For most database types, quote would return C<'Don''t'> (including the |
| 2886 | outer quotation marks). |
| 2887 | |
| 2888 | An undefined C<$value> value will be returned as the string C<NULL> (without |
| 2889 | quotation marks) to match how NULLs are represented in SQL. |
| 2890 | |
| 2891 | If C<$data_type> is supplied, it is used to try to determine the required |
| 2892 | quoting behavior by using the information returned by L</type_info>. |
| 2893 | As a special case, the standard numeric types are optimized to return |
| 2894 | C<$value> without calling C<type_info>. |
| 2895 | |
| 2896 | Quote will probably I<not> be able to deal with all possible input |
| 2897 | (such as binary data or data containing newlines), and is not related in |
| 2898 | any way with escaping or quoting shell meta-characters. There is no |
| 2899 | need to quote values being used with L</"Placeholders and Bind Values">. |
| 2900 | |
| 2901 | =back |
| 2902 | |
| 2903 | |
| 2904 | =head2 Database Handle Attributes |
| 2905 | |
| 2906 | This section describes attributes specific to database handles. |
| 2907 | |
| 2908 | Changes to these database handle attributes do not affect any other |
| 2909 | existing or future database handles. |
| 2910 | |
| 2911 | Attempting to set or get the value of an unknown attribute is fatal, |
| 2912 | except for private driver-specific attributes (which all have names |
| 2913 | starting with a lowercase letter). |
| 2914 | |
| 2915 | Example: |
| 2916 | |
| 2917 | $h->{AutoCommit} = ...; # set/write |
| 2918 | ... = $h->{AutoCommit}; # get/read |
| 2919 | |
| 2920 | =over 4 |
| 2921 | |
| 2922 | =item C<AutoCommit> (boolean) |
| 2923 | |
| 2924 | If true, then database changes cannot be rolled-back (undone). If false, |
| 2925 | then database changes automatically occur within a "transaction", which |
| 2926 | must either be committed or rolled back using the C<commit> or C<rollback> |
| 2927 | methods. |
| 2928 | |
| 2929 | Drivers should always default to C<AutoCommit> mode (an unfortunate |
| 2930 | choice largely forced on the DBI by ODBC and JDBC conventions.) |
| 2931 | |
| 2932 | Attempting to set C<AutoCommit> to an unsupported value is a fatal error. |
| 2933 | This is an important feature of the DBI. Applications that need |
| 2934 | full transaction behavior can set C<$dbh->E<gt>C<{AutoCommit} = 0> (or |
| 2935 | set C<AutoCommit> to 0 via L</connect>) |
| 2936 | without having to check that the value was assigned successfully. |
| 2937 | |
| 2938 | For the purposes of this description, we can divide databases into three |
| 2939 | categories: |
| 2940 | |
| 2941 | Databases which don't support transactions at all. |
| 2942 | Databases in which a transaction is always active. |
| 2943 | Databases in which a transaction must be explicitly started (C<'BEGIN WORK'>). |
| 2944 | |
| 2945 | B<* Databases which don't support transactions at all> |
| 2946 | |
| 2947 | For these databases, attempting to turn C<AutoCommit> off is a fatal error. |
| 2948 | C<commit> and C<rollback> both issue warnings about being ineffective while |
| 2949 | C<AutoCommit> is in effect. |
| 2950 | |
| 2951 | B<* Databases in which a transaction is always active> |
| 2952 | |
| 2953 | These are typically mainstream commercial relational databases with |
| 2954 | "ANSI standard" transaction behavior. |
| 2955 | If C<AutoCommit> is off, then changes to the database won't have any |
| 2956 | lasting effect unless L</commit> is called (but see also |
| 2957 | L</disconnect>). If L</rollback> is called then any changes since the |
| 2958 | last commit are undone. |
| 2959 | |
| 2960 | If C<AutoCommit> is on, then the effect is the same as if the DBI |
| 2961 | called C<commit> automatically after every successful database |
| 2962 | operation. In other words, calling C<commit> or C<rollback> explicitly while |
| 2963 | C<AutoCommit> is on would be ineffective because the changes would |
| 2964 | have already been commited. |
| 2965 | |
| 2966 | Changing C<AutoCommit> from off to on should issue a L</commit> in most drivers. |
| 2967 | |
| 2968 | Changing C<AutoCommit> from on to off should have no immediate effect. |
| 2969 | |
| 2970 | For databases which don't support a specific auto-commit mode, the |
| 2971 | driver has to commit each statement automatically using an explicit |
| 2972 | C<COMMIT> after it completes successfully (and roll it back using an |
| 2973 | explicit C<ROLLBACK> if it fails). The error information reported to the |
| 2974 | application will correspond to the statement which was executed, unless |
| 2975 | it succeeded and the commit or rollback failed. |
| 2976 | |
| 2977 | B<* Databases in which a transaction must be explicitly started> |
| 2978 | |
| 2979 | For these databases, the intention is to have them act like databases in |
| 2980 | which a transaction is always active (as described above). |
| 2981 | |
| 2982 | To do this, the DBI driver will automatically begin a transaction when |
| 2983 | C<AutoCommit> is turned off (from the default "on" state) and will |
| 2984 | automatically begin another transaction after a L</commit> or L</rollback>. |
| 2985 | In this way, the application does not have to treat these databases as a |
| 2986 | special case. |
| 2987 | |
| 2988 | See L</commit>, L</disconnect> and L</Transactions> for other important |
| 2989 | notes about transactions. |
| 2990 | |
| 2991 | |
| 2992 | =item C<Driver> (handle) |
| 2993 | |
| 2994 | Holds the handle of the parent driver. The only recommended use for this |
| 2995 | is to find the name of the driver using: |
| 2996 | |
| 2997 | $dbh->{Driver}->{Name} |
| 2998 | |
| 2999 | |
| 3000 | =item C<Name> (string) |
| 3001 | |
| 3002 | Holds the "name" of the database. Usually (and recommended to be) the |
| 3003 | same as the "C<dbi:DriverName:...>" string used to connect to the database, |
| 3004 | but with the leading "C<dbi:DriverName:>" removed. |
| 3005 | |
| 3006 | |
| 3007 | =item C<Statement> (string, read-only) |
| 3008 | |
| 3009 | Returns the statement string passed to the most recent L</prepare> method |
| 3010 | called in this database handle, even if that method failed. This is especially |
| 3011 | useful where C<RaiseError> is enabled and the exception handler checks $@ |
| 3012 | and sees that a 'prepare' method call failed. |
| 3013 | |
| 3014 | |
| 3015 | =item C<RowCacheSize> (integer) |
| 3016 | |
| 3017 | A hint to the driver indicating the size of the local row cache that the |
| 3018 | application would like the driver to use for future C<SELECT> statements. |
| 3019 | If a row cache is not implemented, then setting C<RowCacheSize> is ignored |
| 3020 | and getting the value returns C<undef>. |
| 3021 | |
| 3022 | Some C<RowCacheSize> values have special meaning, as follows: |
| 3023 | |
| 3024 | 0 - Automatically determine a reasonable cache size for each C<SELECT> |
| 3025 | 1 - Disable the local row cache |
| 3026 | >1 - Cache this many rows |
| 3027 | <0 - Cache as many rows that will fit into this much memory for each C<SELECT>. |
| 3028 | |
| 3029 | Note that large cache sizes may require a very large amount of memory |
| 3030 | (I<cached rows * maximum size of row>). Also, a large cache will cause |
| 3031 | a longer delay not only for the first fetch, but also whenever the |
| 3032 | cache needs refilling. |
| 3033 | |
| 3034 | See also the L</RowsInCache> statement handle attribute. |
| 3035 | |
| 3036 | =back |
| 3037 | |
| 3038 | |
| 3039 | =head1 DBI STATEMENT HANDLE OBJECTS |
| 3040 | |
| 3041 | This section lists the methods and attributes associated with DBI |
| 3042 | statement handles. |
| 3043 | |
| 3044 | =head2 Statement Handle Methods |
| 3045 | |
| 3046 | The DBI defines the following methods for use on DBI statement handles: |
| 3047 | |
| 3048 | =over 4 |
| 3049 | |
| 3050 | =item C<bind_param> |
| 3051 | |
| 3052 | $rc = $sth->bind_param($p_num, $bind_value) or die $sth->errstr; |
| 3053 | $rv = $sth->bind_param($p_num, $bind_value, \%attr) or ... |
| 3054 | $rv = $sth->bind_param($p_num, $bind_value, $bind_type) or ... |
| 3055 | |
| 3056 | The C<bind_param> method can be used to bind a value |
| 3057 | with a placeholder embedded in the prepared statement. Placeholders |
| 3058 | are indicated with question mark character (C<?>). For example: |
| 3059 | |
| 3060 | $dbh->{RaiseError} = 1; # save having to check each method call |
| 3061 | $sth = $dbh->prepare("SELECT name, age FROM people WHERE name LIKE ?"); |
| 3062 | $sth->bind_param(1, "John%"); # placeholders are numbered from 1 |
| 3063 | $sth->execute; |
| 3064 | DBI::dump_results($sth); |
| 3065 | |
| 3066 | Note that the C<?> is not enclosed in quotation marks, even when the |
| 3067 | placeholder represents a string. Some drivers also allow placeholders |
| 3068 | like C<:>I<name> and C<:>I<n> (e.g., C<:1>, C<:2>, and so on) |
| 3069 | in addition to C<?>, but their use |
| 3070 | is not portable. Undefined bind values or C<undef> can be used to |
| 3071 | indicate null values. |
| 3072 | |
| 3073 | Some drivers do not support placeholders. |
| 3074 | |
| 3075 | With most drivers, placeholders can't be used for any element of a |
| 3076 | statement that would prevent the database server from validating the |
| 3077 | statement and creating a query execution plan for it. For example: |
| 3078 | |
| 3079 | "SELECT name, age FROM ?" # wrong (will probably fail) |
| 3080 | "SELECT name, ? FROM people" # wrong (but may not 'fail') |
| 3081 | |
| 3082 | Also, placeholders can only represent single scalar values. |
| 3083 | For example, the following |
| 3084 | statement won't work as expected for more than one value: |
| 3085 | |
| 3086 | "SELECT name, age FROM people WHERE name IN (?)" # wrong |
| 3087 | |
| 3088 | B<Data Types for Placeholders> |
| 3089 | |
| 3090 | The C<\%attr> parameter can be used to hint at the data type the |
| 3091 | placeholder should have. Typically, the driver is only interested in |
| 3092 | knowing if the placeholder should be bound as a number or a string. |
| 3093 | |
| 3094 | $sth->bind_param(1, $value, { TYPE => SQL_INTEGER }); |
| 3095 | |
| 3096 | As a short-cut for this common case, the data type can be passed |
| 3097 | directly, in place of the C<\%attr> hash reference. This example is |
| 3098 | equivalent to the one above: |
| 3099 | |
| 3100 | $sth->bind_param(1, $value, SQL_INTEGER); |
| 3101 | |
| 3102 | The C<TYPE> value indicates the standard (non-driver-specific) type for |
| 3103 | this parameter. To specify the driver-specific type, the driver may |
| 3104 | support a driver-specific attribute, such as C<{ ora_type =>E<gt>C< 97 }>. The |
| 3105 | data type for a placeholder cannot be changed after the first |
| 3106 | C<bind_param> call. However, it can be left unspecified, in which case it |
| 3107 | defaults to the previous value. |
| 3108 | |
| 3109 | The SQL_INTEGER and other related constants can be imported using |
| 3110 | |
| 3111 | use DBI qw(:sql_types); |
| 3112 | |
| 3113 | See L</"DBI Constants"> for more information. |
| 3114 | |
| 3115 | Perl only has string and number scalar data types. All database types |
| 3116 | that aren't numbers are bound as strings and must be in a format the |
| 3117 | database will understand. |
| 3118 | |
| 3119 | As an alternative to specifying the data type in the C<bind_param> call, |
| 3120 | you can let the driver pass the value as the default type (C<VARCHAR>). |
| 3121 | You can then use an SQL function to convert the type within the statement. |
| 3122 | For example: |
| 3123 | |
| 3124 | INSERT INTO price(code, price) VALUES (?, CONVERT(MONEY,?)) |
| 3125 | |
| 3126 | The C<CONVERT> function used here is just an example. The actual function |
| 3127 | and syntax will vary between different databases and is non-portable. |
| 3128 | |
| 3129 | See also L</"Placeholders and Bind Values"> for more information. |
| 3130 | |
| 3131 | |
| 3132 | =item C<bind_param_inout> |
| 3133 | |
| 3134 | $rc = $sth->bind_param_inout($p_num, \$bind_value, $max_len) or die $sth->errstr; |
| 3135 | $rv = $sth->bind_param_inout($p_num, \$bind_value, $max_len, \%attr) or ... |
| 3136 | $rv = $sth->bind_param_inout($p_num, \$bind_value, $max_len, $bind_type) or ... |
| 3137 | |
| 3138 | This method acts like L</bind_param>, but also enables values to be |
| 3139 | updated by the statement. The statement is typically |
| 3140 | a call to a stored procedure. The C<$bind_value> must be passed as a |
| 3141 | reference to the actual value to be used. |
| 3142 | |
| 3143 | Note that unlike L</bind_param>, the C<$bind_value> variable is not |
| 3144 | read when C<bind_param_inout> is called. Instead, the value in the |
| 3145 | variable is read at the time L</execute> is called. |
| 3146 | |
| 3147 | The additional C<$max_len> parameter specifies the minimum amount of |
| 3148 | memory to allocate to C<$bind_value> for the new value. If the value |
| 3149 | returned from the database is too |
| 3150 | big to fit, then the execution should fail. If unsure what value to use, |
| 3151 | pick a generous length, i.e., a length larger than the longest value that would ever be |
| 3152 | returned. The only cost of using a larger value than needed is wasted memory. |
| 3153 | |
| 3154 | It is expected that few drivers will support this method. The only |
| 3155 | driver currently known to do so is DBD::Oracle (DBD::ODBC may support |
| 3156 | it in a future release). Therefore it should not be used for database |
| 3157 | independent applications. |
| 3158 | |
| 3159 | Undefined values or C<undef> are used to indicate null values. |
| 3160 | See also L</"Placeholders and Bind Values"> for more information. |
| 3161 | |
| 3162 | |
| 3163 | =item C<execute> |
| 3164 | |
| 3165 | $rv = $sth->execute or die $sth->errstr; |
| 3166 | $rv = $sth->execute(@bind_values) or die $sth->errstr; |
| 3167 | |
| 3168 | Perform whatever processing is necessary to execute the prepared |
| 3169 | statement. An C<undef> is returned if an error occurs. A successful |
| 3170 | C<execute> always returns true regardless of the number of rows affected, |
| 3171 | even if it's zero (see below). It is always important to check the |
| 3172 | return status of C<execute> (and most other DBI methods) for errors. |
| 3173 | |
| 3174 | For a I<non>-C<SELECT> statement, C<execute> returns the number of rows |
| 3175 | affected, if known. If no rows were affected, then C<execute> returns |
| 3176 | "C<0E0>", which Perl will treat as 0 but will regard as true. Note that it |
| 3177 | is I<not> an error for no rows to be affected by a statement. If the |
| 3178 | number of rows affected is not known, then C<execute> returns -1. |
| 3179 | |
| 3180 | For C<SELECT> statements, execute simply "starts" the query within the |
| 3181 | database engine. Use one of the fetch methods to retreive the data after |
| 3182 | calling C<execute>. The C<execute> method does I<not> return the number of |
| 3183 | rows that will be returned by the query (because most databases can't |
| 3184 | tell in advance), it simply returns a true value. |
| 3185 | |
| 3186 | If any arguments are given, then C<execute> will effectively call |
| 3187 | L</bind_param> for each value before executing the statement. |
| 3188 | Values bound in this way are usually treated as C<SQL_VARCHAR> types |
| 3189 | unless the driver can determine the correct type (which is rare), or |
| 3190 | unless |
| 3191 | C<bind_param> (or C<bind_param_inout>) has already been used to specify the |
| 3192 | type. |
| 3193 | |
| 3194 | |
| 3195 | =item C<fetchrow_arrayref> |
| 3196 | |
| 3197 | $ary_ref = $sth->fetchrow_arrayref; |
| 3198 | $ary_ref = $sth->fetch; # alias |
| 3199 | |
| 3200 | Fetches the next row of data and returns a reference to an array |
| 3201 | holding the field values. Null fields are returned as C<undef> |
| 3202 | values in the array. |
| 3203 | This is the fastest way to fetch data, particularly if used with |
| 3204 | C<$sth->E<gt>C<bind_columns>. |
| 3205 | |
| 3206 | If there are no more rows or if an error occurs, then C<fetchrow_arrayref> |
| 3207 | returns an C<undef>. You should check C<$sth->E<gt>C<err> afterwards (or use the |
| 3208 | C<RaiseError> attribute) to discover if the C<undef> returned was due to an |
| 3209 | error. |
| 3210 | |
| 3211 | Note that the same array reference is returned for each fetch, so don't |
| 3212 | store the reference and then use it after a later fetch. Also, the |
| 3213 | elements of the array are also reused for each row, so take care if you |
| 3214 | want to take a reference to an element. See also L</bind_columns>. |
| 3215 | |
| 3216 | =item C<fetchrow_array> |
| 3217 | |
| 3218 | @ary = $sth->fetchrow_array; |
| 3219 | |
| 3220 | An alternative to C<fetchrow_arrayref>. Fetches the next row of data |
| 3221 | and returns it as a list containing the field values. Null fields |
| 3222 | are returned as C<undef> values in the list. |
| 3223 | |
| 3224 | If there are no more rows or if an error occurs, then C<fetchrow_array> |
| 3225 | returns an empty list. You should check C<$sth->E<gt>C<err> afterwards (or use |
| 3226 | the C<RaiseError> attribute) to discover if the empty list returned was |
| 3227 | due to an error. |
| 3228 | |
| 3229 | In a scalar context, C<fetchrow_array> returns the value of the first |
| 3230 | field. An C<undef> is returned if there are no more rows or if an error |
| 3231 | occurred. Since that C<undef> can't be distinguished from an C<undef> returned |
| 3232 | because the first field value was NULL, you should exercise some |
| 3233 | caution if you use C<fetchrow_array> in a scalar context. |
| 3234 | |
| 3235 | =item C<fetchrow_hashref> |
| 3236 | |
| 3237 | $hash_ref = $sth->fetchrow_hashref; |
| 3238 | $hash_ref = $sth->fetchrow_hashref($name); |
| 3239 | |
| 3240 | An alternative to C<fetchrow_arrayref>. Fetches the next row of data |
| 3241 | and returns it as a reference to a hash containing field name and field |
| 3242 | value pairs. Null fields are returned as C<undef> values in the hash. |
| 3243 | |
| 3244 | If there are no more rows or if an error occurs, then C<fetchrow_hashref> |
| 3245 | returns an C<undef>. You should check C<$sth->E<gt>C<err> afterwards (or use the |
| 3246 | C<RaiseError> attribute) to discover if the C<undef> returned was due to an |
| 3247 | error. |
| 3248 | |
| 3249 | The optional C<$name> parameter specifies the name of the statement handle |
| 3250 | attribute. For historical reasons it defaults to "C<NAME>", however using either |
| 3251 | "C<NAME_lc>" or "C<NAME_uc>" is recomended for portability. |
| 3252 | |
| 3253 | The keys of the hash are the same names returned by C<$sth->E<gt>C<{$name}>. If |
| 3254 | more than one field has the same name, there will only be one entry in |
| 3255 | the returned hash for those fields. |
| 3256 | |
| 3257 | Because of the extra work C<fetchrow_hashref> and Perl have to perform, it |
| 3258 | is not as efficient as C<fetchrow_arrayref> or C<fetchrow_array>. |
| 3259 | |
| 3260 | Currently, a new hash reference is returned for each row. I<This will |
| 3261 | change> in the future to return the same hash ref each time, so don't |
| 3262 | rely on the current behaviour. |
| 3263 | |
| 3264 | |
| 3265 | =item C<fetchall_arrayref> |
| 3266 | |
| 3267 | $tbl_ary_ref = $sth->fetchall_arrayref; |
| 3268 | $tbl_ary_ref = $sth->fetchall_arrayref( $slice_array_ref ); |
| 3269 | $tbl_ary_ref = $sth->fetchall_arrayref( $slice_hash_ref ); |
| 3270 | |
| 3271 | The C<fetchall_arrayref> method can be used to fetch all the data to be |
| 3272 | returned from a prepared and executed statement handle. It returns a |
| 3273 | reference to an array that contains one reference per row. |
| 3274 | |
| 3275 | If there are no rows to return, C<fetchall_arrayref> returns a reference |
| 3276 | to an empty array. If an error occurs, C<fetchall_arrayref> returns the |
| 3277 | data fetched thus far, which may be none. You should check C<$sth->E<gt>C<err> |
| 3278 | afterwards (or use the C<RaiseError> attribute) to discover if the data is |
| 3279 | complete or was truncated due to an error. |
| 3280 | |
| 3281 | When passed an array reference, C<fetchall_arrayref> uses L</fetchrow_arrayref> |
| 3282 | to fetch each row as an array ref. If the parameter array is not empty |
| 3283 | then it is used as a slice to select individual columns by index number. |
| 3284 | |
| 3285 | With no parameters, C<fetchall_arrayref> acts as if passed an empty array ref. |
| 3286 | |
| 3287 | When passed a hash reference, C<fetchall_arrayref> uses L</fetchrow_hashref> |
| 3288 | to fetch each row as a hash reference. If the parameter hash is empty then |
| 3289 | fetchrow_hashref is simply called in a tight loop and the keys in the hashes |
| 3290 | have whatever name lettercase is returned by default from fetchrow_hashref. |
| 3291 | |
| 3292 | If the parameter hash is not empty, then it is used as a slice to |
| 3293 | select individual columns by name. The names should be lower case |
| 3294 | regardless of the letter case in C<$sth->E<gt>C<{NAME}>. The values of |
| 3295 | the hash should be set to 1. |
| 3296 | |
| 3297 | For example, to fetch just the first column of every row: |
| 3298 | |
| 3299 | $tbl_ary_ref = $sth->fetchall_arrayref([0]); |
| 3300 | |
| 3301 | To fetch the second to last and last column of every row: |
| 3302 | |
| 3303 | $tbl_ary_ref = $sth->fetchall_arrayref([-2,-1]); |
| 3304 | |
| 3305 | To fetch all fields of every row as a hash ref: |
| 3306 | |
| 3307 | $tbl_ary_ref = $sth->fetchall_arrayref({}); |
| 3308 | |
| 3309 | To fetch only the fields called "foo" and "bar" of every row as a hash ref: |
| 3310 | |
| 3311 | $tbl_ary_ref = $sth->fetchall_arrayref({ foo=>1, bar=>1 }); |
| 3312 | |
| 3313 | The first two examples return a reference to an array of array refs. The last |
| 3314 | returns a reference to an array of hash refs. |
| 3315 | |
| 3316 | |
| 3317 | =item C<fetchall_hashref> |
| 3318 | |
| 3319 | $tbl_ary_ref = $sth->fetchall_hashref; |
| 3320 | |
| 3321 | The C<fetchall_hashref> method can be used to fetch all the data to be |
| 3322 | returned from a prepared and executed statement handle. It returns a |
| 3323 | reference to an array that contains one hash of field name and value |
| 3324 | pairs per row. |
| 3325 | |
| 3326 | If there are no rows to return, C<fetchall_hashref> returns a reference |
| 3327 | to an empty array. If an error occurs, C<fetchall_hashref> returns the |
| 3328 | data fetched thus far, which may be none. You should check C<$sth->E<gt>C<err> |
| 3329 | afterwards (or use the C<RaiseError> attribute) to discover if the data is |
| 3330 | complete or was truncated due to an error. |
| 3331 | |
| 3332 | |
| 3333 | =item C<finish> |
| 3334 | |
| 3335 | $rc = $sth->finish; |
| 3336 | |
| 3337 | Indicates that no more data will be fetched from this statement handle |
| 3338 | before it is either executed again or destroyed. The C<finish> method |
| 3339 | is rarely needed, but can sometimes be helpful in very specific |
| 3340 | situations to allow the server to free up resources (such as sort |
| 3341 | buffers). |
| 3342 | |
| 3343 | When all the data has been fetched from a C<SELECT> statement, the driver |
| 3344 | should automatically call C<finish> for you. So you should not normally |
| 3345 | need to call it explicitly. |
| 3346 | |
| 3347 | Consider a query like: |
| 3348 | |
| 3349 | SELECT foo FROM table WHERE bar=? ORDER BY foo |
| 3350 | |
| 3351 | where you want to select just the first (smallest) "foo" value from a |
| 3352 | very large table. When executed, the database server will have to use |
| 3353 | temporary buffer space to store the sorted rows. If, after executing |
| 3354 | the handle and selecting one row, the handle won't be re-executed for |
| 3355 | some time and won't be destroyed, the C<finish> method can be used to tell |
| 3356 | the server that the buffer space can be freed. |
| 3357 | |
| 3358 | Calling C<finish> resets the L</Active> attribute for the statement. It |
| 3359 | may also make some statement handle attributes (such as C<NAME> and C<TYPE>) |
| 3360 | unavailable if they have not already been accessed (and thus cached). |
| 3361 | |
| 3362 | The C<finish> method does not affect the transaction status of the |
| 3363 | database connection. It has nothing to do with transactions. It's mostly an |
| 3364 | internal "housekeeping" method that is rarely needed. There's no need |
| 3365 | to call C<finish> if you're about to destroy or re-execute the statement |
| 3366 | handle. See also L</disconnect> and the L</Active> attribute. |
| 3367 | |
| 3368 | The C<finish> method should have been called C<cancel_select>. |
| 3369 | |
| 3370 | |
| 3371 | =item C<rows> |
| 3372 | |
| 3373 | $rv = $sth->rows; |
| 3374 | |
| 3375 | Returns the number of rows affected by the last row affecting command, |
| 3376 | or -1 if the number of rows is not known or not available. |
| 3377 | |
| 3378 | Generally, you can only rely on a row count after a I<non>-C<SELECT> |
| 3379 | C<execute> (for some specific operations like C<UPDATE> and C<DELETE>), or |
| 3380 | after fetching all the rows of a C<SELECT> statement. |
| 3381 | |
| 3382 | For C<SELECT> statements, it is generally not possible to know how many |
| 3383 | rows will be returned except by fetching them all. Some drivers will |
| 3384 | return the number of rows the application has fetched so far, but |
| 3385 | others may return -1 until all rows have been fetched. So use of the |
| 3386 | C<rows> method or C<$DBI::rows> with C<SELECT> statements is not |
| 3387 | recommended. |
| 3388 | |
| 3389 | One alternative method to get a row count for a C<SELECT> is to execute a |
| 3390 | "SELECT COUNT(*) FROM ..." SQL statement with the same "..." as your |
| 3391 | query and then fetch the row count from that. |
| 3392 | |
| 3393 | |
| 3394 | =item C<bind_col> |
| 3395 | |
| 3396 | $rc = $sth->bind_col($column_number, \$var_to_bind); |
| 3397 | |
| 3398 | Binds an output column (field) of a C<SELECT> statement to a Perl variable. |
| 3399 | See C<bind_columns> below for an example. Note that column numbers count |
| 3400 | up from 1. |
| 3401 | |
| 3402 | Whenever a row is fetched from the database, the corresponding Perl |
| 3403 | variable is automatically updated. There is no need to fetch and assign |
| 3404 | the values manually. The binding is performed at a very low level |
| 3405 | using Perl aliasing so there is no extra copying taking place. This |
| 3406 | makes using bound variables very efficient. |
| 3407 | |
| 3408 | For maximum portability between drivers, C<bind_col> should be called after |
| 3409 | C<execute>. This restriction may be removed in a later version of the DBI. |
| 3410 | |
| 3411 | You do not need to bind output columns in order to fetch data, but it |
| 3412 | can be useful for some applications which need either maximum performance |
| 3413 | or greater clarity of code. The L</bind_param> method |
| 3414 | performs a similar but opposite function for input variables. |
| 3415 | |
| 3416 | =item C<bind_columns> |
| 3417 | |
| 3418 | $rc = $sth->bind_columns(@list_of_refs_to_vars_to_bind); |
| 3419 | |
| 3420 | Calls L</bind_col> for each column of the C<SELECT> statement. |
| 3421 | The C<bind_columns> method will die if the number of references does not |
| 3422 | match the number of fields. |
| 3423 | |
| 3424 | For maximum portability between drivers, C<bind_columns> should be called |
| 3425 | after C<execute>. |
| 3426 | |
| 3427 | For example: |
| 3428 | |
| 3429 | $dbh->{RaiseError} = 1; # do this, or check every call for errors |
| 3430 | $sth = $dbh->prepare(q{ SELECT region, sales FROM sales_by_region }); |
| 3431 | $sth->execute; |
| 3432 | my ($region, $sales); |
| 3433 | |
| 3434 | # Bind Perl variables to columns: |
| 3435 | $rv = $sth->bind_columns(\$region, \$sales); |
| 3436 | |
| 3437 | # you can also use Perl's \(...) syntax (see perlref docs): |
| 3438 | # $sth->bind_columns(\($region, $sales)); |
| 3439 | |
| 3440 | # Column binding is the most efficient way to fetch data |
| 3441 | while ($sth->fetch) { |
| 3442 | print "$region: $sales\n"; |
| 3443 | } |
| 3444 | |
| 3445 | For compatibility with old scripts, the first parameter will be |
| 3446 | ignored if it is C<undef> or a hash reference. |
| 3447 | |
| 3448 | Here's a more fancy example that binds columns to the values I<inside> |
| 3449 | a hash (thanks to H.Merijn Brand): |
| 3450 | |
| 3451 | $sth->execute; |
| 3452 | my %row; |
| 3453 | $sth->bind_columns( \( @row{ @{$sth->{NAME_lc} } } )); |
| 3454 | while ($sth->fetch) { |
| 3455 | print "$row{region}: $row{sales}\n"; |
| 3456 | } |
| 3457 | |
| 3458 | |
| 3459 | =item C<dump_results> |
| 3460 | |
| 3461 | $rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh); |
| 3462 | |
| 3463 | Fetches all the rows from C<$sth>, calls C<DBI::neat_list> for each row, and |
| 3464 | prints the results to C<$fh> (defaults to C<STDOUT>) separated by C<$lsep> |
| 3465 | (default C<"\n">). C<$fsep> defaults to C<", "> and C<$maxlen> defaults to 35. |
| 3466 | |
| 3467 | This method is designed as a handy utility for prototyping and |
| 3468 | testing queries. Since it uses L</neat_list> to |
| 3469 | format and edit the string for reading by humans, it is not recomended |
| 3470 | for data transfer applications. |
| 3471 | |
| 3472 | =back |
| 3473 | |
| 3474 | |
| 3475 | =head2 Statement Handle Attributes |
| 3476 | |
| 3477 | This section describes attributes specific to statement handles. Most |
| 3478 | of these attributes are read-only. |
| 3479 | |
| 3480 | Changes to these statement handle attributes do not affect any other |
| 3481 | existing or future statement handles. |
| 3482 | |
| 3483 | Attempting to set or get the value of an unknown attribute is fatal, |
| 3484 | except for private driver specific attributes (which all have names |
| 3485 | starting with a lowercase letter). |
| 3486 | |
| 3487 | Example: |
| 3488 | |
| 3489 | ... = $h->{NUM_OF_FIELDS}; # get/read |
| 3490 | |
| 3491 | Note that some drivers cannot provide valid values for some or all of |
| 3492 | these attributes until after C<$sth->E<gt>C<execute> has been called. |
| 3493 | |
| 3494 | See also L</finish> to learn more about the effect it |
| 3495 | may have on some attributes. |
| 3496 | |
| 3497 | =over 4 |
| 3498 | |
| 3499 | =item C<NUM_OF_FIELDS> (integer, read-only) |
| 3500 | |
| 3501 | Number of fields (columns) the prepared statement will return. Non-C<SELECT> |
| 3502 | statements will have C<NUM_OF_FIELDS == 0>. |
| 3503 | |
| 3504 | |
| 3505 | =item C<NUM_OF_PARAMS> (integer, read-only) |
| 3506 | |
| 3507 | The number of parameters (placeholders) in the prepared statement. |
| 3508 | See SUBSTITUTION VARIABLES below for more details. |
| 3509 | |
| 3510 | |
| 3511 | =item C<NAME> (array-ref, read-only) |
| 3512 | |
| 3513 | Returns a reference to an array of field names for each column. The |
| 3514 | names may contain spaces but should not be truncated or have any |
| 3515 | trailing space. Note that the names have the letter case (upper, lower |
| 3516 | or mixed) as returned by the driver being used. Portable applications |
| 3517 | should use L</NAME_lc> or L</NAME_uc>. |
| 3518 | |
| 3519 | print "First column name: $sth->{NAME}->[0]\n"; |
| 3520 | |
| 3521 | =item C<NAME_lc> (array-ref, read-only) |
| 3522 | |
| 3523 | Like L</NAME> but always returns lowercase names. |
| 3524 | |
| 3525 | =item C<NAME_uc> (array-ref, read-only) |
| 3526 | |
| 3527 | Like L</NAME> but always returns uppercase names. |
| 3528 | |
| 3529 | =item C<TYPE> (array-ref, read-only) |
| 3530 | |
| 3531 | Returns a reference to an array of integer values for each |
| 3532 | column. The value indicates the data type of the corresponding column. |
| 3533 | |
| 3534 | The values correspond to the international standards (ANSI X3.135 |
| 3535 | and ISO/IEC 9075) which, in general terms, means ODBC. Driver-specific |
| 3536 | types that don't exactly match standard types should generally return |
| 3537 | the same values as an ODBC driver supplied by the makers of the |
| 3538 | database. That might include private type numbers in ranges the vendor |
| 3539 | has officially registered with the ISO working group: |
| 3540 | |
| 3541 | ftp://jerry.ece.umassd.edu/isowg3/dbl/SQL_Registry |
| 3542 | |
| 3543 | Where there's no vendor-supplied ODBC driver to be compatible with, the |
| 3544 | DBI driver can use type numbers in the range that is now officially reserved |
| 3545 | for use by the DBI: -9999 to -9000. |
| 3546 | |
| 3547 | All possible values for C<TYPE> should have at least one entry in the |
| 3548 | output of the C<type_info_all> method (see L</type_info_all>). |
| 3549 | |
| 3550 | =item C<PRECISION> (array-ref, read-only) |
| 3551 | |
| 3552 | Returns a reference to an array of integer values for each |
| 3553 | column. For non-numeric columns, the value generally refers to either |
| 3554 | the maximum length or the defined length of the column. For numeric |
| 3555 | columns, the value refers to the maximum number of significant digits |
| 3556 | used by the data type (without considering a sign character or decimal |
| 3557 | point). Note that for floating point types (REAL, FLOAT, DOUBLE), the |
| 3558 | "display size" can be up to 7 characters greater than the precision. |
| 3559 | (for the sign + decimal point + the letter E + a sign + 2 or 3 digits). |
| 3560 | |
| 3561 | =item C<SCALE> (array-ref, read-only) |
| 3562 | |
| 3563 | Returns a reference to an array of integer values for each column. |
| 3564 | NULL (C<undef>) values indicate columns where scale is not applicable. |
| 3565 | |
| 3566 | =item C<NULLABLE> (array-ref, read-only) |
| 3567 | |
| 3568 | Returns a reference to an array indicating the possibility of each |
| 3569 | column returning a null. Possible values are C<0> |
| 3570 | (or an empty string) = no, C<1> = yes, C<2> = unknown. |
| 3571 | |
| 3572 | print "First column may return NULL\n" if $sth->{NULLABLE}->[0]; |
| 3573 | |
| 3574 | |
| 3575 | =item C<CursorName> (string, read-only) |
| 3576 | |
| 3577 | Returns the name of the cursor associated with the statement handle, if |
| 3578 | available. If not available or if the database driver does not support the |
| 3579 | C<"where current of ..."> SQL syntax, then it returns C<undef>. |
| 3580 | |
| 3581 | |
| 3582 | =item C<Statement> (string, read-only) |
| 3583 | |
| 3584 | Returns the statement string passed to the L</prepare> method. |
| 3585 | |
| 3586 | |
| 3587 | =item C<RowsInCache> (integer, read-only) |
| 3588 | |
| 3589 | If the driver supports a local row cache for C<SELECT> statements, then |
| 3590 | this attribute holds the number of un-fetched rows in the cache. If the |
| 3591 | driver doesn't, then it returns C<undef>. Note that some drivers pre-fetch |
| 3592 | rows on execute, whereas others wait till the first fetch. |
| 3593 | |
| 3594 | See also the L</RowCacheSize> database handle attribute. |
| 3595 | |
| 3596 | =back |
| 3597 | |
| 3598 | |
| 3599 | =head1 FURTHER INFORMATION |
| 3600 | |
| 3601 | =head2 Transactions |
| 3602 | |
| 3603 | Transactions are a fundamental part of any robust database system. They |
| 3604 | protect against errors and database corruption by ensuring that sets of |
| 3605 | related changes to the database take place in atomic (indivisible, |
| 3606 | all-or-nothing) units. |
| 3607 | |
| 3608 | This section applies to databases that support transactions and where |
| 3609 | C<AutoCommit> is off. See L</AutoCommit> for details of using C<AutoCommit> |
| 3610 | with various types of databases. |
| 3611 | |
| 3612 | The recommended way to implement robust transactions in Perl |
| 3613 | applications is to use C<RaiseError> and S<C<eval { ... }>> |
| 3614 | (which is very fast, unlike S<C<eval "...">>). For example: |
| 3615 | |
| 3616 | $dbh->{AutoCommit} = 0; # enable transactions, if possible |
| 3617 | $dbh->{RaiseError} = 1; |
| 3618 | eval { |
| 3619 | foo(...) # do lots of work here |
| 3620 | bar(...) # including inserts |
| 3621 | baz(...) # and updates |
| 3622 | $dbh->commit; # commit the changes if we get this far |
| 3623 | }; |
| 3624 | if ($@) { |
| 3625 | warn "Transaction aborted because $@"; |
| 3626 | $dbh->rollback; # undo the incomplete changes |
| 3627 | # add other application on-error-clean-up code here |
| 3628 | } |
| 3629 | |
| 3630 | If the C<RaiseError> attribute is not set, then DBI calls would need to be |
| 3631 | manually checked for errors, typically like this: |
| 3632 | |
| 3633 | $h->method(@args) or die $h->errstr; |
| 3634 | |
| 3635 | With C<RaiseError> set, the DBI will automatically C<die> if any DBI method |
| 3636 | call on that handle (or a child handle) fails, so you don't have to |
| 3637 | test the return value of each method call. See L</RaiseError> for more |
| 3638 | details. |
| 3639 | |
| 3640 | A major advantage of the C<eval> approach is that the transaction will be |
| 3641 | properly rolled back if I<any> code (not just DBI calls) in the inner |
| 3642 | application dies for any reason. The major advantage of using the |
| 3643 | C<$h->E<gt>C<{RaiseError}> attribute is that all DBI calls will be checked |
| 3644 | automatically. Both techniques are strongly recommended. |
| 3645 | |
| 3646 | After calling C<commit> or C<rollback> many drivers will not let you |
| 3647 | fetch from a previously active C<SELECT> statement handle that's a child |
| 3648 | of the same database handle. A typical way round this is to connect the |
| 3649 | the database twice and use one connection for C<SELECT> statements. |
| 3650 | |
| 3651 | |
| 3652 | =head2 Handling BLOB / LONG / Memo Fields |
| 3653 | |
| 3654 | Many databases support "blob" (binary large objects), "long", or similar |
| 3655 | datatypes for holding very long strings or large amounts of binary |
| 3656 | data in a single field. Some databases support variable length long |
| 3657 | values over 2,000,000,000 bytes in length. |
| 3658 | |
| 3659 | Since values of that size can't usually be held in memory, and because |
| 3660 | databases can't usually know in advance the length of the longest long |
| 3661 | that will be returned from a C<SELECT> statement (unlike other data |
| 3662 | types), some special handling is required. |
| 3663 | |
| 3664 | In this situation, the value of the C<$h->E<gt>C<{LongReadLen}> attribute is used |
| 3665 | to determine how much buffer space to allocate when fetching such |
| 3666 | fields. The C<$h->E<gt>C<{LongTruncOk}> attribute is used to determine how to |
| 3667 | behave if a fetched value can't fit into the buffer. |
| 3668 | |
| 3669 | When trying to insert long or binary values, placeholders should be used |
| 3670 | since there are often limits on the maximum size of an C<INSERT> |
| 3671 | statement and the L</quote> method generally can't cope with binary |
| 3672 | data. See L</Placeholders and Bind Values>. |
| 3673 | |
| 3674 | |
| 3675 | =head2 Simple Examples |
| 3676 | |
| 3677 | Here's a complete example program to select and fetch some data: |
| 3678 | |
| 3679 | my $dbh = DBI->connect("dbi:DriverName:db_name", $user, $password) |
| 3680 | or die "Can't connect to $data_source: $DBI::errstr"; |
| 3681 | |
| 3682 | my $sth = $dbh->prepare( q{ |
| 3683 | SELECT name, phone |
| 3684 | FROM mytelbook |
| 3685 | }) or die "Can't prepare statement: $DBI::errstr"; |
| 3686 | |
| 3687 | my $rc = $sth->execute |
| 3688 | or die "Can't execute statement: $DBI::errstr"; |
| 3689 | |
| 3690 | print "Query will return $sth->{NUM_OF_FIELDS} fields.\n\n"; |
| 3691 | print "Field names: @{ $sth->{NAME} }\n"; |
| 3692 | |
| 3693 | while (($name, $phone) = $sth->fetchrow_array) { |
| 3694 | print "$name: $phone\n"; |
| 3695 | } |
| 3696 | # check for problems which may have terminated the fetch early |
| 3697 | die $sth->errstr if $sth->err; |
| 3698 | |
| 3699 | $dbh->disconnect; |
| 3700 | |
| 3701 | Here's a complete example program to insert some data from a file. |
| 3702 | (This example uses C<RaiseError> to avoid needing to check each call). |
| 3703 | |
| 3704 | my $dbh = DBI->connect("dbi:DriverName:db_name", $user, $password, { |
| 3705 | RaiseError => 1, AutoCommit => 0 |
| 3706 | }); |
| 3707 | |
| 3708 | my $sth = $dbh->prepare( q{ |
| 3709 | INSERT INTO table (name, phone) VALUES (?, ?) |
| 3710 | }); |
| 3711 | |
| 3712 | open FH, "<phone.csv" or die "Unable to open phone.csv: $!"; |
| 3713 | while (<FH>) { |
| 3714 | chomp; |
| 3715 | my ($name, $phone) = split /,/; |
| 3716 | $sth->execute($name, $phone); |
| 3717 | } |
| 3718 | close FH; |
| 3719 | |
| 3720 | $dbh->commit; |
| 3721 | $dbh->disconnect; |
| 3722 | |
| 3723 | Here's how to convert fetched NULLs (undefined values) into empty strings: |
| 3724 | |
| 3725 | while($row = $sth->fetchrow_arrayref) { |
| 3726 | # this is a fast and simple way to deal with nulls: |
| 3727 | foreach (@$row) { $_ = '' unless defined } |
| 3728 | print "@$row\n"; |
| 3729 | } |
| 3730 | |
| 3731 | The C<q{...}> style quoting used in these examples avoids clashing with |
| 3732 | quotes that may be used in the SQL statement. Use the double-quote like |
| 3733 | C<qq{...}> operator if you want to interpolate variables into the string. |
| 3734 | See L<perlop/"Quote and Quote-like Operators"> for more details. |
| 3735 | |
| 3736 | |
| 3737 | =head2 Threads and Thread Safety |
| 3738 | |
| 3739 | Perl versions 5.004_50 and later include optional experimental support |
| 3740 | for multiple threads on many platforms. If the DBI is built using a |
| 3741 | Perl that has threads enabled then it will use a per-driver mutex to |
| 3742 | ensure that only one thread is with a driver at any one time. |
| 3743 | Please note that support for threads in Perl is still experimental and |
| 3744 | is known to have some significant problems. It's use is not recommended. |
| 3745 | |
| 3746 | |
| 3747 | =head2 Signal Handling and Canceling Operations |
| 3748 | |
| 3749 | The first thing to say is that signal handling in Perl is currently |
| 3750 | I<not> safe. There is always a small risk of Perl crashing and/or |
| 3751 | core dumping when, or after, handling a signal. (The risk was reduced |
| 3752 | with 5.004_04 but is still present.) |
| 3753 | |
| 3754 | The two most common uses of signals in relation to the DBI are for |
| 3755 | canceling operations when the user types Ctrl-C (interrupt), and for |
| 3756 | implementing a timeout using C<alarm()> and C<$SIG{ALRM}>. |
| 3757 | |
| 3758 | To assist in implementing these operations, the DBI provides a C<cancel> |
| 3759 | method for statement handles. The C<cancel> method should abort the current |
| 3760 | operation and is designed to be called from a signal handler. |
| 3761 | |
| 3762 | However, it must be stressed that: a) few drivers implement this at |
| 3763 | the moment (the DBI provides a default method that just returns C<undef>); |
| 3764 | and b) even if implemented, there is still a possibility that the statement |
| 3765 | handle, and possibly the parent database handle, will not be usable |
| 3766 | afterwards. |
| 3767 | |
| 3768 | If C<cancel> returns true, then it has successfully |
| 3769 | invoked the database engine's own cancel function. If it returns false, |
| 3770 | then C<cancel> failed. If it returns C<undef>, then the database |
| 3771 | engine does not have cancel implemented. |
| 3772 | |
| 3773 | |
| 3774 | =head1 DEBUGGING |
| 3775 | |
| 3776 | In addition to the L</trace> method, you can enable the same trace |
| 3777 | information by setting the C<DBI_TRACE> environment variable before |
| 3778 | starting Perl. |
| 3779 | |
| 3780 | On Unix-like systems using a Bourne-like shell, you can do this easily |
| 3781 | on the command line: |
| 3782 | |
| 3783 | DBI_TRACE=2 perl your_test_script.pl |
| 3784 | |
| 3785 | If C<DBI_TRACE> is set to a non-numeric value, then it is assumed to |
| 3786 | be a file name and the trace level will be set to 2 with all trace |
| 3787 | output appended to that file. If the name begins with a number |
| 3788 | followed by an equal sign (C<=>), then the number and the equal sign are |
| 3789 | stripped off from the name, and the number is used to set the trace |
| 3790 | level. For example: |
| 3791 | |
| 3792 | DBI_TRACE=1=dbitrace.log perl your_test_script.pl |
| 3793 | |
| 3794 | See also the L</trace> method. |
| 3795 | |
| 3796 | It can sometimes be handy to compare trace files from two different |
| 3797 | runs of the same script. However using a tool like C<diff> doesn't work |
| 3798 | well because the trace file is full of object addresses that may |
| 3799 | differ each run. Here's a handy little command to strip those out: |
| 3800 | |
| 3801 | perl -pe 's/\b0x[\da-f]{6,}/0xNNNN/gi; s/\b[\da-f]{6,}/<long number>/gi' |
| 3802 | |
| 3803 | |
| 3804 | =head1 WARNING AND ERROR MESSAGES |
| 3805 | |
| 3806 | =head2 Fatal Errors |
| 3807 | |
| 3808 | =over 4 |
| 3809 | |
| 3810 | =item Can't call method "prepare" without a package or object reference |
| 3811 | |
| 3812 | The C<$dbh> handle you're using to call C<prepare> is probably undefined because |
| 3813 | the preceding C<connect> failed. You should always check the return status of |
| 3814 | DBI methods, or use the L</RaiseError> attribute. |
| 3815 | |
| 3816 | =item Can't call method "execute" without a package or object reference |
| 3817 | |
| 3818 | The C<$sth> handle you're using to call C<execute> is probably undefined because |
| 3819 | the preceeding C<prepare> failed. You should always check the return status of |
| 3820 | DBI methods, or use the L</RaiseError> attribute. |
| 3821 | |
| 3822 | =item DBI/DBD internal version mismatch |
| 3823 | |
| 3824 | The DBD driver module was built with a different version of DBI than |
| 3825 | the one currently being used. You should rebuild the DBD module under |
| 3826 | the current version of DBI. |
| 3827 | |
| 3828 | (Some rare platforms require "static linking". On those platforms, there |
| 3829 | may be an old DBI or DBD driver version actually embedded in the Perl |
| 3830 | executable being used.) |
| 3831 | |
| 3832 | =item DBD driver has not implemented the AutoCommit attribute |
| 3833 | |
| 3834 | The DBD driver implementation is incomplete. Consult the author. |
| 3835 | |
| 3836 | =item Can't [sg]et %s->{%s}: unrecognised attribute |
| 3837 | |
| 3838 | You attempted to set or get an unknown attribute of a handle. Make |
| 3839 | sure you have spelled the attribute name correctly; case is significant |
| 3840 | (e.g., "Autocommit" is not the same as "AutoCommit"). |
| 3841 | |
| 3842 | =back |
| 3843 | |
| 3844 | =head2 Warnings |
| 3845 | |
| 3846 | =over 4 |
| 3847 | |
| 3848 | =item Database handle destroyed without explicit disconnect |
| 3849 | |
| 3850 | A C<$dbh> handle went out of scope or the program ended before the handle |
| 3851 | was disconnected from the database. You should always explicitly call |
| 3852 | C<disconnect> when you are finished using a database handle. If using |
| 3853 | transactions then you should also explicitly call C<commit> or C<rollback> |
| 3854 | before C<disconnect>. |
| 3855 | |
| 3856 | =item DBI Handle cleared whilst still holding %d cached kids! |
| 3857 | |
| 3858 | Most probably due to a DBI bug. Possibly a DBD driver bug. Please report it. |
| 3859 | |
| 3860 | =item DBI Handle cleared whilst still active! |
| 3861 | |
| 3862 | Most probably due to a DBI bug. Possibly a DBD driver bug. Please report it. |
| 3863 | |
| 3864 | =item DBI Handle has uncleared implementors data |
| 3865 | |
| 3866 | Most probably a DBD driver bug. Please report it. |
| 3867 | |
| 3868 | =item DBI Handle has %d uncleared child handles |
| 3869 | |
| 3870 | Most probably due to a DBI bug. Possibly a DBD driver bug. Please report it. |
| 3871 | |
| 3872 | =back |
| 3873 | |
| 3874 | =head1 SEE ALSO |
| 3875 | |
| 3876 | =head2 Driver and Database Documentation |
| 3877 | |
| 3878 | Refer to the documentation for the DBD driver that you are using. |
| 3879 | |
| 3880 | Refer to the SQL Language Reference Manual for the database engine that you are using. |
| 3881 | |
| 3882 | =head2 Books and Journals |
| 3883 | |
| 3884 | Programming the Perl DBI, by Alligator Descartes and Tim Bunce. |
| 3885 | |
| 3886 | Programming Perl 2nd Ed. by Larry Wall, Tom Christiansen & Randal Schwartz. |
| 3887 | |
| 3888 | Learning Perl by Randal Schwartz. |
| 3889 | |
| 3890 | Dr Dobb's Journal, November 1996. |
| 3891 | |
| 3892 | The Perl Journal, April 1997. |
| 3893 | |
| 3894 | =head2 Manual Pages |
| 3895 | |
| 3896 | L<perl(1)>, L<perlmod(1)>, L<perlbook(1)> |
| 3897 | |
| 3898 | =head2 Mailing List |
| 3899 | |
| 3900 | The I<dbi-users> mailing list is the primary means of communication among |
| 3901 | users of the DBI and its related modules. For details send email to: |
| 3902 | |
| 3903 | dbi-users-help@perl.org |
| 3904 | |
| 3905 | There are typically between 700 and 900 messages per month. You have |
| 3906 | to subscribe in order to be able to post. However you can opt for a |
| 3907 | 'post-only' subscription. |
| 3908 | |
| 3909 | Mailing list archives are held at: |
| 3910 | |
| 3911 | http://www.xray.mpe.mpg.de/mailing-lists/dbi/ |
| 3912 | http://groups.yahoo.com/group/dbi-users |
| 3913 | http://www.bitmechanic.com/mail-archives/dbi-users/ |
| 3914 | http://marc.theaimsgroup.com/?l=perl-dbi&r=1&w=2 |
| 3915 | http://www.mail-archive.com/dbi-users%40perl.org/ |
| 3916 | http://www.mail-archive.com/dbi-users%40perl.org/ |
| 3917 | |
| 3918 | =head2 Assorted Related WWW Links |
| 3919 | |
| 3920 | The DBI "Home Page": |
| 3921 | |
| 3922 | http://dbi.perl.org/ |
| 3923 | |
| 3924 | Other DBI related links: |
| 3925 | |
| 3926 | http://tegan.deltanet.com/~phlip/DBUIdoc.html |
| 3927 | http://dc.pm.org/perl_db.html |
| 3928 | http://wdvl.com/Authoring/DB/Intro/toc.html |
| 3929 | http://www.hotwired.com/webmonkey/backend/tutorials/tutorial1.html |
| 3930 | http://bumppo.net/lists/macperl/1999/06/msg00197.html |
| 3931 | |
| 3932 | Other database related links: |
| 3933 | |
| 3934 | http://www.jcc.com/sql_stnd.html |
| 3935 | http://cuiwww.unige.ch/OSG/info/FreeDB/FreeDB.home.html |
| 3936 | |
| 3937 | Commercial and Data Warehouse Links |
| 3938 | |
| 3939 | http://www.dwinfocenter.org |
| 3940 | http://www.datawarehouse.com |
| 3941 | http://www.datamining.org |
| 3942 | http://www.olapcouncil.org |
| 3943 | http://www.idwa.org |
| 3944 | http://www.knowledgecenters.org/dwcenter.asp |
| 3945 | |
| 3946 | Recommended Perl Programming Links |
| 3947 | |
| 3948 | http://language.perl.com/style/ |
| 3949 | |
| 3950 | =head2 FAQ |
| 3951 | |
| 3952 | Please also read the DBI FAQ which is installed as a DBI::FAQ module. |
| 3953 | You can use I<perldoc> to read it by executing the C<perldoc DBI::FAQ> command. |
| 3954 | |
| 3955 | =head1 AUTHORS |
| 3956 | |
| 3957 | DBI by Tim Bunce. This pod text by Tim Bunce, J. Douglas Dunlop, |
| 3958 | Jonathan Leffler and others. Perl by Larry Wall and the |
| 3959 | C<perl5-porters>. |
| 3960 | |
| 3961 | =head1 COPYRIGHT |
| 3962 | |
| 3963 | The DBI module is Copyright (c) 1994-2000 Tim Bunce. England. |
| 3964 | All rights reserved. |
| 3965 | |
| 3966 | You may distribute under the terms of either the GNU General Public |
| 3967 | License or the Artistic License, as specified in the Perl README file. |
| 3968 | |
| 3969 | =head1 ACKNOWLEDGEMENTS |
| 3970 | |
| 3971 | I would like to acknowledge the valuable contributions of the many |
| 3972 | people I have worked with on the DBI project, especially in the early |
| 3973 | years (1992-1994). In no particular order: Kevin Stock, Buzz Moschetti, |
| 3974 | Kurt Andersen, Ted Lemon, William Hails, Garth Kennedy, Michael Peppler, |
| 3975 | Neil S. Briscoe, Jeff Urlwin, David J. Hughes, Jeff Stander, |
| 3976 | Forrest D Whitcher, Larry Wall, Jeff Fried, Roy Johnson, Paul Hudson, |
| 3977 | Georg Rehfeld, Steve Sizemore, Ron Pool, Jon Meek, Tom Christiansen, |
| 3978 | Steve Baumgarten, Randal Schwartz, and a whole lot more. |
| 3979 | |
| 3980 | Then, of course, there are the poor souls who have struggled through |
| 3981 | untold and undocumented obstacles to actually implement DBI drivers. |
| 3982 | Among their ranks are Jochen Wiedmann, Alligator Descartes, Jonathan |
| 3983 | Leffler, Jeff Urlwin, Michael Peppler, Henrik Tougaard, Edwin Pratomo, |
| 3984 | Davide Migliavacca, Jan Pazdziora, Peter Haworth, Edmund Mergl, Steve |
| 3985 | Williams, Thomas Lowery, and Phlip Plumlee. Without them, the DBI would |
| 3986 | not be the practical reality it is today. I'm also especially grateful |
| 3987 | to Alligator Descartes for starting work on the "Programming the Perl |
| 3988 | DBI" book and letting me jump on board. |
| 3989 | |
| 3990 | =head1 TRANSLATIONS |
| 3991 | |
| 3992 | A German translation of this manual (possibly slightly out of date) is |
| 3993 | available, thanks to O'Reilly, at: |
| 3994 | |
| 3995 | http://www.oreilly.de/catalog/perldbiger/ |
| 3996 | |
| 3997 | Some other translations: |
| 3998 | |
| 3999 | http://cronopio.net/perl/ - Spanish |
| 4000 | http://member.nifty.ne.jp/hippo2000/dbimemo.htm - Japanese |
| 4001 | |
| 4002 | |
| 4003 | =head1 SUPPORT / WARRANTY |
| 4004 | |
| 4005 | The DBI is free software. IT COMES WITHOUT WARRANTY OF ANY KIND. |
| 4006 | |
| 4007 | Commercial support for Perl and the DBI, DBD::Oracle and |
| 4008 | Oraperl modules can be arranged via The Perl Clinic. |
| 4009 | For more details visit: |
| 4010 | |
| 4011 | http://www.perlclinic.com |
| 4012 | |
| 4013 | =head1 TRAINING |
| 4014 | |
| 4015 | References to DBI related training resources. No recommendation implied. |
| 4016 | |
| 4017 | http://www.treepax.co.uk/ |
| 4018 | http://www.keller.com/dbweb/ |
| 4019 | |
| 4020 | =head1 FREQUENTLY ASKED QUESTIONS |
| 4021 | |
| 4022 | See the DBI FAQ for a more comprehensive list of FAQs. Use the |
| 4023 | C<perldoc DBI::FAQ> command to read it. |
| 4024 | |
| 4025 | =head2 How fast is the DBI? |
| 4026 | |
| 4027 | To measure the speed of the DBI and DBD::Oracle code, I modified |
| 4028 | DBD::Oracle so you can set an attribute that will cause the |
| 4029 | same row to be fetched from the row cache over and over again (without |
| 4030 | involving Oracle code but exercising *all* the DBI and DBD::Oracle code |
| 4031 | in the code path for a fetch). |
| 4032 | |
| 4033 | The results (on my lightly loaded old Sparc 10) fetching 50000 rows using: |
| 4034 | |
| 4035 | 1 while $csr->fetch; |
| 4036 | |
| 4037 | were: |
| 4038 | one field: 5300 fetches per cpu second (approx) |
| 4039 | ten fields: 4000 fetches per cpu second (approx) |
| 4040 | |
| 4041 | Obviously results will vary between platforms (newer faster platforms |
| 4042 | can reach around 50000 fetches per second), but it does give a feel for |
| 4043 | the maximum performance: fast. By way of comparison, using the code: |
| 4044 | |
| 4045 | 1 while @row = $csr->fetchrow_array; |
| 4046 | |
| 4047 | (C<fetchrow_array> is roughly the same as C<ora_fetch>) gives: |
| 4048 | |
| 4049 | one field: 3100 fetches per cpu second (approx) |
| 4050 | ten fields: 1000 fetches per cpu second (approx) |
| 4051 | |
| 4052 | Notice the slowdown and the more dramatic impact of extra fields. |
| 4053 | (The fields were all one char long. The impact would be even bigger for |
| 4054 | longer strings.) |
| 4055 | |
| 4056 | Changing that slightly to represent actually doing something in Perl |
| 4057 | with the fetched data: |
| 4058 | |
| 4059 | while(@row = $csr->fetchrow_array) { |
| 4060 | $hash{++$i} = [ @row ]; |
| 4061 | } |
| 4062 | |
| 4063 | gives: ten fields: 500 fetches per cpu second (approx) |
| 4064 | |
| 4065 | That simple addition has *halved* the performance. |
| 4066 | |
| 4067 | I therefore conclude that DBI and DBD::Oracle overheads are small |
| 4068 | compared with Perl language overheads (and probably database overheads). |
| 4069 | |
| 4070 | So, if you think the DBI or your driver is slow, try replacing your |
| 4071 | fetch loop with just: |
| 4072 | |
| 4073 | 1 while $csr->fetch; |
| 4074 | |
| 4075 | and time that. If that helps then point the finger at your own code. If |
| 4076 | that doesn't help much then point the finger at the database, the |
| 4077 | platform, the network etc. But think carefully before pointing it at |
| 4078 | the DBI or your driver. |
| 4079 | |
| 4080 | (Having said all that, if anyone can show me how to make the DBI or |
| 4081 | drivers even more efficient, I'm all ears.) |
| 4082 | |
| 4083 | |
| 4084 | =head2 Why doesn't my CGI script work right? |
| 4085 | |
| 4086 | Read the information in the references below. Please do I<not> post |
| 4087 | CGI related questions to the I<dbi-users> mailing list (or to me). |
| 4088 | |
| 4089 | http://www.perl.com/cgi-bin/pace/pub/doc/FAQs/cgi/perl-cgi-faq.html |
| 4090 | http://www3.pair.com/webthing/docs/cgi/faqs/cgifaq.shtml |
| 4091 | http://www-genome.wi.mit.edu/WWW/faqs/www-security-faq.html |
| 4092 | http://www.boutell.com/faq/ |
| 4093 | http://www.perl.com/perl/faq/ |
| 4094 | |
| 4095 | General problems and good ideas: |
| 4096 | |
| 4097 | Use the CGI::ErrorWrap module. |
| 4098 | Remember that many env vars won't be set for CGI scripts. |
| 4099 | |
| 4100 | =head2 How can I maintain a WWW connection to a database? |
| 4101 | |
| 4102 | For information on the Apache httpd server and the C<mod_perl> module see |
| 4103 | |
| 4104 | http://perl.apache.org/ |
| 4105 | |
| 4106 | =head2 What about ODBC? |
| 4107 | |
| 4108 | A DBD::ODBC module is available. |
| 4109 | |
| 4110 | =head2 Does the DBI have a year 2000 problem? |
| 4111 | |
| 4112 | No. The DBI has no knowledge or understanding of dates at all. |
| 4113 | |
| 4114 | Individual drivers (DBD::*) may have some date handling code but are |
| 4115 | unlikely to have year 2000 related problems within their code. However, |
| 4116 | your application code which I<uses> the DBI and DBD drivers may have |
| 4117 | year 2000 related problems if it has not been designed and written well. |
| 4118 | |
| 4119 | See also the "Does Perl have a year 2000 problem?" section of the Perl FAQ: |
| 4120 | |
| 4121 | http://www.perl.com/CPAN/doc/FAQs/FAQ/PerlFAQ.html |
| 4122 | |
| 4123 | =head1 OTHER RELATED WORK AND PERL MODULES |
| 4124 | |
| 4125 | =over 4 |
| 4126 | |
| 4127 | =item Apache::DBI by E.Mergl@bawue.de |
| 4128 | |
| 4129 | To be used with the Apache daemon together with an embedded Perl |
| 4130 | interpreter like C<mod_perl>. Establishes a database connection which |
| 4131 | remains open for the lifetime of the HTTP daemon. This way the CGI |
| 4132 | connect and disconnect for every database access becomes superfluous. |
| 4133 | |
| 4134 | =item JDBC Server by Stuart 'Zen' Bishop zen@bf.rmit.edu.au |
| 4135 | |
| 4136 | The server is written in Perl. The client classes that talk to it are |
| 4137 | of course in Java. Thus, a Java applet or application will be able to |
| 4138 | comunicate via the JDBC API with any database that has a DBI driver installed. |
| 4139 | The URL used is in the form C<jdbc:dbi://host.domain.etc:999/Driver/DBName>. |
| 4140 | It seems to be very similar to some commercial products, such as jdbcKona. |
| 4141 | |
| 4142 | =item Remote Proxy DBD support |
| 4143 | |
| 4144 | As of DBI 1.02, a complete implementation of a DBD::Proxy driver and the |
| 4145 | DBI::ProxyServer are part of the DBI distribution. |
| 4146 | |
| 4147 | =item SQL Parser |
| 4148 | |
| 4149 | Hugo van der Sanden <hv@crypt.compulink.co.uk> |
| 4150 | Stephen Zander <stephen.zander@mckesson.com> |
| 4151 | |
| 4152 | Based on the O'Reilly lex/yacc book examples and C<byacc>. |
| 4153 | |
| 4154 | See also the SQL::Statement module, a very simple SQL parser and engine, |
| 4155 | base of the DBD::CSV driver. |
| 4156 | |
| 4157 | =back |
| 4158 | |
| 4159 | =cut |