Commit | Line | Data |
---|---|---|
86530b38 AT |
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 |