1DBI(3) User Contributed Perl Documentation DBI(3)
2
6 DBI - Database independent interface for Perl
7
9 use DBI;
10 @driver_names = DBI->available_drivers;
12 %drivers = DBI->installed_drivers;
13 @data_sources = DBI->data_sources($driver_name, \%attr);
14 $dbh = DBI->connect($data_source, $username, $auth, \%attr);
16 $rv = $dbh->do($statement);
18 $rv = $dbh->do($statement, \%attr);
19 $rv = $dbh->do($statement, \%attr, @bind_values);
20 $ary_ref = $dbh->selectall_arrayref($statement);
22 $hash_ref = $dbh->selectall_hashref($statement, $key_field);
23 $ary_ref = $dbh->selectcol_arrayref($statement);
25 $ary_ref = $dbh->selectcol_arrayref($statement, \%attr);
26 @row_ary = $dbh->selectrow_array($statement);
28 $ary_ref = $dbh->selectrow_arrayref($statement);
29 $hash_ref = $dbh->selectrow_hashref($statement);
30 $sth = $dbh->prepare($statement);
32 $sth = $dbh->prepare_cached($statement);
33 $rc = $sth->bind_param($p_num, $bind_value);
35 $rc = $sth->bind_param($p_num, $bind_value, $bind_type);
36 $rc = $sth->bind_param($p_num, $bind_value, \%attr);
37 $rv = $sth->execute;
39 $rv = $sth->execute(@bind_values);
40 $rv = $sth->execute_array(\%attr, ...);
41 $rc = $sth->bind_col($col_num, \$col_variable);
43 $rc = $sth->bind_columns(@list_of_refs_to_vars_to_bind);
44 @row_ary = $sth->fetchrow_array;
46 $ary_ref = $sth->fetchrow_arrayref;
47 $hash_ref = $sth->fetchrow_hashref;
48 $ary_ref = $sth->fetchall_arrayref;
50 $ary_ref = $sth->fetchall_arrayref( $slice, $max_rows );
51 $hash_ref = $sth->fetchall_hashref( $key_field );
53 $rv = $sth->rows;
55 $rc = $dbh->begin_work;
57 $rc = $dbh->commit;
58 $rc = $dbh->rollback;
59 $quoted_string = $dbh->quote($string);
61 $rc = $h->err;
63 $str = $h->errstr;
64 $rv = $h->state;
65 $rc = $dbh->disconnect;
67 The synopsis above only lists the major methods and parameters.
69 GETTING HELP
71 If you have questions about DBI, or DBD driver modules, you can get
73 help from the dbi-users@perl.org mailing list. You can get help on
74 subscribing and using the list by emailing dbi-users-help@perl.org.
75 To help you make the best use of the dbi-users mailing list, and any
77 other lists or forums you may use, I strongly recommend that you read
78 "How To Ask Questions The Smart Way" by Eric Raymond:
79 <http://www.catb.org/~esr/faqs/smart-questions.html>.
80 If you think you've found a bug then please also read "How to Report
82 Bugs Effectively" by Simon Tatham: <http://www.chiark.gree‐
83 nend.org.uk/~sgtatham/bugs.html>.
84 The DBI home page at <http://dbi.perl.org/> is always worth a visit and
86 includes an FAQ and links to other resources.
87 Before asking any questions, reread this document, consult the archives
89 and read the DBI FAQ. The archives are listed at the end of this docu‐
90 ment and on the DBI home page. An FAQ is installed as a DBI::FAQ mod‐
91 ule so you can read it by executing "perldoc DBI::FAQ". However the
92 DBI::FAQ module is currently (2004) outdated relative to the online FAQ
93 on the DBI home page.
94 This document often uses terms like references, objects, methods. If
96 you're not familar with those terms then it would be a good idea to
97 read at least the following perl manuals first: perlreftut, perldsc,
98 perllol, and perlboot.
99 Please note that Tim Bunce does not maintain the mailing lists or the
101 web page (generous volunteers do that). So please don't send mail
102 directly to him; he just doesn't have the time to answer questions per‐
103 sonally. The dbi-users mailing list has lots of experienced people who
104 should be able to help you if you need it. If you do email Tim he's
105 very likely to just forward it to the mailing list.
106 NOTES
108 This is the DBI specification that corresponds to the DBI version 1.53.
110 The DBI is evolving at a steady pace, so it's good to check that you
112 have the latest copy.
113 The significant user-visible changes in each release are documented in
115 the DBI::Changes module so you can read them by executing "perldoc
116 DBI::Changes".
117 Some DBI changes require changes in the drivers, but the drivers can
119 take some time to catch up. Newer versions of the DBI have added fea‐
120 tures that may not yet be supported by the drivers you use. Talk to
121 the authors of your drivers if you need a new feature that's not yet
122 supported.
123 Features added after DBI 1.21 (February 2002) are marked in the text
125 with the version number of the DBI release they first appeared in.
126 Extensions to the DBI API often use the "DBIx::*" namespace. See "Nam‐
128 ing Conventions and Name Space". DBI extension modules can be found at
129 <http://search.cpan.org/search?mode=module&query=DBIx>. And all mod‐
130 ules related to the DBI can be found at
131 <http://search.cpan.org/search?query=DBI&mode=all>.
132
134 The DBI is a database access module for the Perl programming language.
135 It defines a set of methods, variables, and conventions that provide a
136 consistent database interface, independent of the actual database being
137 used.
138 It is important to remember that the DBI is just an interface. The DBI
140 is a layer of "glue" between an application and one or more database
141 driver modules. It is the driver modules which do most of the real
142 work. The DBI provides a standard interface and framework for the driv‐
143 ers to operate within.
144 Architecture of a DBI Application
146 ⎪<- Scope of DBI ->⎪
148 .-. .--------------. .-------------.
149 .-------. ⎪ ⎪---⎪ XYZ Driver ⎪---⎪ XYZ Engine ⎪
150 ⎪ Perl ⎪ ⎪ ⎪ `--------------' `-------------'
151 ⎪ script⎪ ⎪A⎪ ⎪D⎪ .--------------. .-------------.
152 ⎪ using ⎪--⎪P⎪--⎪B⎪---⎪Oracle Driver ⎪---⎪Oracle Engine⎪
153 ⎪ DBI ⎪ ⎪I⎪ ⎪I⎪ `--------------' `-------------'
154 ⎪ API ⎪ ⎪ ⎪...
155 ⎪methods⎪ ⎪ ⎪... Other drivers
156 `-------' ⎪ ⎪...
157 `-'
158 The API, or Application Programming Interface, defines the call inter‐
160 face and variables for Perl scripts to use. The API is implemented by
161 the Perl DBI extension.
162 The DBI "dispatches" the method calls to the appropriate driver for
164 actual execution. The DBI is also responsible for the dynamic loading
165 of drivers, error checking and handling, providing default implementa‐
166 tions for methods, and many other non-database specific duties.
167 Each driver contains implementations of the DBI methods using the pri‐
169 vate interface functions of the corresponding database engine. Only
170 authors of sophisticated/multi-database applications or generic library
171 functions need be concerned with drivers.
172 Notation and Conventions
174 The following conventions are used in this document:
176 $dbh Database handle object
178 $sth Statement handle object
179 $drh Driver handle object (rarely seen or used in applications)
180 $h Any of the handle types above ($dbh, $sth, or $drh)
181 $rc General Return Code (boolean: true=ok, false=error)
182 $rv General Return Value (typically an integer)
183 @ary List of values returned from the database, typically a row of data
184 $rows Number of rows processed (if available, else -1)
185 $fh A filehandle
186 undef NULL values are represented by undefined values in Perl
187 \%attr Reference to a hash of attribute values passed to methods
188 Note that Perl will automatically destroy database and statement handle
190 objects if all references to them are deleted.
191 Outline Usage
193 To use DBI, first you need to load the DBI module:
195 use DBI;
197 use strict;
198 (The "use strict;" isn't required but is strongly recommended.)
200 Then you need to "connect" to your data source and get a handle for
202 that connection:
203 $dbh = DBI->connect($dsn, $user, $password,
205 { RaiseError => 1, AutoCommit => 0 });
206 Since connecting can be expensive, you generally just connect at the
208 start of your program and disconnect at the end.
209 Explicitly defining the required "AutoCommit" behaviour is strongly
211 recommended and may become mandatory in a later version. This deter‐
212 mines whether changes are automatically committed to the database when
213 executed, or need to be explicitly committed later.
214 The DBI allows an application to "prepare" statements for later execu‐
216 tion. A prepared statement is identified by a statement handle held in
217 a Perl variable. We'll call the Perl variable $sth in our examples.
218 The typical method call sequence for a "SELECT" statement is:
220 prepare,
222 execute, fetch, fetch, ...
223 execute, fetch, fetch, ...
224 execute, fetch, fetch, ...
225 for example:
227 $sth = $dbh->prepare("SELECT foo, bar FROM table WHERE baz=?");
229 $sth->execute( $baz );
231 while ( @row = $sth->fetchrow_array ) {
233 print "@row\n";
234 }
235 The typical method call sequence for a non-"SELECT" statement is:
237 prepare,
239 execute,
240 execute,
241 execute.
242 for example:
244 $sth = $dbh->prepare("INSERT INTO table(foo,bar,baz) VALUES (?,?,?)");
246 while(<CSV>) {
248 chomp;
249 my ($foo,$bar,$baz) = split /,/;
250 $sth->execute( $foo, $bar, $baz );
251 }
252 The "do()" method can be used for non repeated non-"SELECT" statement
254 (or with drivers that don't support placeholders):
255 $rows_affected = $dbh->do("UPDATE your_table SET foo = foo + 1");
257 To commit your changes to the database (when "AutoCommit" is off):
259 $dbh->commit; # or call $dbh->rollback; to undo changes
261 Finally, when you have finished working with the data source, you
263 should "disconnect" from it:
264 $dbh->disconnect;
266 General Interface Rules & Caveats
268 The DBI does not have a concept of a "current session". Every session
270 has a handle object (i.e., a $dbh) returned from the "connect" method.
271 That handle object is used to invoke database related methods.
272 Most data is returned to the Perl script as strings. (Null values are
274 returned as "undef".) This allows arbitrary precision numeric data to
275 be handled without loss of accuracy. Beware that Perl may not preserve
276 the same accuracy when the string is used as a number.
277 Dates and times are returned as character strings in the current
279 default format of the corresponding database engine. Time zone effects
280 are database/driver dependent.
281 Perl supports binary data in Perl strings, and the DBI will pass binary
283 data to and from the driver without change. It is up to the driver
284 implementors to decide how they wish to handle such binary data.
285 Most databases that understand multiple character sets have a default
287 global charset. Text stored in the database is, or should be, stored in
288 that charset; if not, then that's the fault of either the database or
289 the application that inserted the data. When text is fetched it should
290 be automatically converted to the charset of the client, presumably
291 based on the locale. If a driver needs to set a flag to get that behav‐
292 iour, then it should do so; it should not require the application to do
293 that.
294 Multiple SQL statements may not be combined in a single statement han‐
296 dle ($sth), although some databases and drivers do support this
297 (notably Sybase and SQL Server).
298 Non-sequential record reads are not supported in this version of the
300 DBI. In other words, records can only be fetched in the order that the
301 database returned them, and once fetched they are forgotten.
302 Positioned updates and deletes are not directly supported by the DBI.
304 See the description of the "CursorName" attribute for an alternative.
305 Individual driver implementors are free to provide any private func‐
307 tions and/or handle attributes that they feel are useful. Private
308 driver functions can be invoked using the DBI "func()" method. Private
309 driver attributes are accessed just like standard attributes.
310 Many methods have an optional "\%attr" parameter which can be used to
312 pass information to the driver implementing the method. Except where
313 specifically documented, the "\%attr" parameter can only be used to
314 pass driver specific hints. In general, you can ignore "\%attr" parame‐
315 ters or pass it as "undef".
316 Naming Conventions and Name Space
318 The DBI package and all packages below it ("DBI::*") are reserved for
320 use by the DBI. Extensions and related modules use the "DBIx::" names‐
321 pace (see <http://www.perl.com/CPAN/modules/by-module/DBIx/>). Package
322 names beginning with "DBD::" are reserved for use by DBI database driv‐
323 ers. All environment variables used by the DBI or by individual DBDs
324 begin with ""DBI_"" or ""DBD_"".
325 The letter case used for attribute names is significant and plays an
327 important part in the portability of DBI scripts. The case of the
328 attribute name is used to signify who defined the meaning of that name
329 and its values.
330 Case of name Has a meaning defined by
332 ------------ ------------------------
333 UPPER_CASE Standards, e.g., X/Open, ISO SQL92 etc (portable)
334 MixedCase DBI API (portable), underscores are not used.
335 lower_case Driver or database engine specific (non-portable)
336 It is of the utmost importance that Driver developers only use lower‐
338 case attribute names when defining private attributes. Private
339 attribute names must be prefixed with the driver name or suitable
340 abbreviation (e.g., ""ora_"" for Oracle, ""ing_"" for Ingres, etc).
341 SQL - A Query Language
343 Most DBI drivers require applications to use a dialect of SQL (Struc‐
345 tured Query Language) to interact with the database engine. The "Stan‐
346 dards Reference Information" section provides links to useful informa‐
347 tion about SQL.
348 The DBI itself does not mandate or require any particular language to
350 be used; it is language independent. In ODBC terms, the DBI is in
351 "pass-thru" mode, although individual drivers might not be. The only
352 requirement is that queries and other statements must be expressed as a
353 single string of characters passed as the first argument to the "pre‐
354 pare" or "do" methods.
355 For an interesting diversion on the real history of RDBMS and SQL, from
357 the people who made it happen, see:
358 http://ftp.digital.com/pub/DEC/SRC/technical-notes/SRC-1997-018-html/sqlr95.html
360 Follow the "Full Contents" then "Intergalactic dataspeak" links for the
362 SQL history.
363 Placeholders and Bind Values
365 Some drivers support placeholders and bind values. Placeholders, also
367 called parameter markers, are used to indicate values in a database
368 statement that will be supplied later, before the prepared statement is
369 executed. For example, an application might use the following to
370 insert a row of data into the SALES table:
371 INSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?)
373 or the following, to select the description for a product:
375 SELECT description FROM products WHERE product_code = ?
377 The "?" characters are the placeholders. The association of actual
379 values with placeholders is known as binding, and the values are
380 referred to as bind values.
381 Note that the "?" is not enclosed in quotation marks, even when the
383 placeholder represents a string. Some drivers also allow placeholders
384 like ":"name and ":"n (e.g., ":1", ":2", and so on) in addition to "?",
385 but their use is not portable.
386 With most drivers, placeholders can't be used for any element of a
388 statement that would prevent the database server from validating the
389 statement and creating a query execution plan for it. For example:
390 "SELECT name, age FROM ?" # wrong (will probably fail)
392 "SELECT name, ? FROM people" # wrong (but may not 'fail')
393 Also, placeholders can only represent single scalar values. For exam‐
395 ple, the following statement won't work as expected for more than one
396 value:
397 "SELECT name, age FROM people WHERE name IN (?)" # wrong
399 "SELECT name, age FROM people WHERE name IN (?,?)" # two names
400 When using placeholders with the SQL "LIKE" qualifier, you must remem‐
402 ber that the placeholder substitutes for the whole string. So you
403 should use ""... LIKE ? ..."" and include any wildcard characters in
404 the value that you bind to the placeholder.
405 NULL Values
407 Undefined values, or "undef", are used to indicate NULL values. You
409 can insert and update columns with a NULL value as you would a non-NULL
410 value. These examples insert and update the column "age" with a NULL
411 value:
412 $sth = $dbh->prepare(qq{
414 INSERT INTO people (fullname, age) VALUES (?, ?)
415 });
416 $sth->execute("Joe Bloggs", undef);
417 $sth = $dbh->prepare(qq{
419 UPDATE people SET age = ? WHERE fullname = ?
420 });
421 $sth->execute(undef, "Joe Bloggs");
422 However, care must be taken when trying to use NULL values in a "WHERE"
424 clause. Consider:
425 SELECT fullname FROM people WHERE age = ?
427 Binding an "undef" (NULL) to the placeholder will not select rows which
429 have a NULL "age"! At least for database engines that conform to the
430 SQL standard. Refer to the SQL manual for your database engine or any
431 SQL book for the reasons for this. To explicitly select NULLs you have
432 to say ""WHERE age IS NULL"".
433 A common issue is to have a code fragment handle a value that could be
435 either "defined" or "undef" (non-NULL or NULL) at runtime. A simple
436 technique is to prepare the appropriate statement as needed, and sub‐
437 stitute the placeholder for non-NULL cases:
438 $sql_clause = defined $age? "age = ?" : "age IS NULL";
440 $sth = $dbh->prepare(qq{
441 SELECT fullname FROM people WHERE $sql_clause
442 });
443 $sth->execute(defined $age ? $age : ());
444 The following technique illustrates qualifying a "WHERE" clause with
446 several columns, whose associated values ("defined" or "undef") are in
447 a hash %h:
448 for my $col ("age", "phone", "email") {
450 if (defined $h{$col}) {
451 push @sql_qual, "$col = ?";
452 push @sql_bind, $h{$col};
453 }
454 else {
455 push @sql_qual, "$col IS NULL";
456 }
457 }
458 $sql_clause = join(" AND ", @sql_qual);
459 $sth = $dbh->prepare(qq{
460 SELECT fullname FROM people WHERE $sql_clause
461 });
462 $sth->execute(@sql_bind);
463 The techniques above call prepare for the SQL statement with each call
465 to execute. Because calls to prepare() can be expensive, performance
466 can suffer when an application iterates many times over statements like
467 the above.
468 A better solution is a single "WHERE" clause that supports both NULL
470 and non-NULL comparisons. Its SQL statement would need to be prepared
471 only once for all cases, thus improving performance. Several examples
472 of "WHERE" clauses that support this are presented below. But each
473 example lacks portability, robustness, or simplicity. Whether an exam‐
474 ple is supported on your database engine depends on what SQL extensions
475 it provides, and where it supports the "?" placeholder in a statement.
476 0) age = ?
478 1) NVL(age, xx) = NVL(?, xx)
479 2) ISNULL(age, xx) = ISNULL(?, xx)
480 3) DECODE(age, ?, 1, 0) = 1
481 4) age = ? OR (age IS NULL AND ? IS NULL)
482 5) age = ? OR (age IS NULL AND SP_ISNULL(?) = 1)
483 6) age = ? OR (age IS NULL AND ? = 1)
484 Statements formed with the above "WHERE" clauses require execute state‐
486 ments as follows. The arguments are required, whether their values are
487 "defined" or "undef".
488 0,1,2,3) $sth->execute($age);
490 4,5) $sth->execute($age, $age);
491 6) $sth->execute($age, defined($age) ? 0 : 1);
492 Example 0 should not work (as mentioned earlier), but may work on a few
494 database engines anyway (e.g. Sybase). Example 0 is part of examples
495 4, 5, and 6, so if example 0 works, these other examples may work, even
496 if the engine does not properly support the right hand side of the "OR"
497 expression.
498 Examples 1 and 2 are not robust: they require that you provide a valid
500 column value xx (e.g. '~') which is not present in any row. That means
501 you must have some notion of what data won't be stored in the column,
502 and expect clients to adhere to that.
503 Example 5 requires that you provide a stored procedure (SP_ISNULL in
505 this example) that acts as a function: it checks whether a value is
506 null, and returns 1 if it is, or 0 if not.
507 Example 6, the least simple, is probably the most portable, i.e., it
509 should work with with most, if not all, database engines.
510 Here is a table that indicates which examples above are known to work
512 on various database engines:
513 -----Examples------
515 0 1 2 3 4 5 6
516 - - - - - - -
517 Oracle 9 N Y N Y Y ? Y
518 Informix IDS 9 N N N Y N Y Y
519 MS SQL N N Y N Y ? Y
520 Sybase Y N N N N N Y
521 AnyData,DBM,CSV Y N N N Y Y* Y
522 * Works only because Example 0 works.
524 DBI provides a sample perl script that will test the examples above on
526 your database engine and tell you which ones work. It is located in
527 the ex/ subdirectory of the DBI source distribution, or here:
528 <http://svn.perl.org/modules/dbi/trunk/ex/perl_dbi_nulls_test.pl>
529 Please use the script to help us fill-in and maintain this table.
530 Performance
532 Without using placeholders, the insert statement shown previously would
534 have to contain the literal values to be inserted and would have to be
535 re-prepared and re-executed for each row. With placeholders, the insert
536 statement only needs to be prepared once. The bind values for each row
537 can be given to the "execute" method each time it's called. By avoiding
538 the need to re-prepare the statement for each row, the application typ‐
539 ically runs many times faster. Here's an example:
540 my $sth = $dbh->prepare(q{
542 INSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?)
543 }) or die $dbh->errstr;
544 while (<>) {
545 chomp;
546 my ($product_code, $qty, $price) = split /,/;
547 $sth->execute($product_code, $qty, $price) or die $dbh->errstr;
548 }
549 $dbh->commit or die $dbh->errstr;
550 See "execute" and "bind_param" for more details.
552 The "q{...}" style quoting used in this example avoids clashing with
554 quotes that may be used in the SQL statement. Use the double-quote like
555 "qq{...}" operator if you want to interpolate variables into the
556 string. See "Quote and Quote-like Operators" in perlop for more
557 details.
558 See also the "bind_columns" method, which is used to associate Perl
560 variables with the output columns of a "SELECT" statement.
561
563 In this section, we cover the DBI class methods, utility functions, and
564 the dynamic attributes associated with generic DBI handles.
565 DBI Constants
567 Constants representing the values of the SQL standard types can be
569 imported individually by name, or all together by importing the special
570 ":sql_types" tag.
571 The names and values of all the defined SQL standard types can be pro‐
573 duced like this:
574 foreach (@{ $DBI::EXPORT_TAGS{sql_types} }) {
576 printf "%s=%d\n", $_, &{"DBI::$_"};
577 }
578 These constants are defined by SQL/CLI, ODBC or both. "SQL_BIGINT" is
580 (currently) omitted, because SQL/CLI and ODBC provide conflicting
581 codes.
582 See the "type_info", "type_info_all", and "bind_param" methods for pos‐
584 sible uses.
585 Note that just because the DBI defines a named constant for a given
587 data type doesn't mean that drivers will support that data type.
588 DBI Class Methods
590 The following methods are provided by the DBI class:
592 "parse_dsn"
594 ($scheme, $driver, $attr_string, $attr_hash, $driver_dsn) = DBI->parse_dsn($dsn)
595 or die "Can't parse DBI DSN '$dsn'";
596 Breaks apart a DBI Data Source Name (DSN) and returns the individ‐
598 ual parts. If $dsn doesn't contain a valid DSN then parse_dsn()
599 returns an empty list.
600 $scheme is the first part of the DSN and is currently always 'dbi'.
602 $driver is the driver name, possibly defaulted to $ENV{DBI_DRIVER},
603 and may be undefined. $attr_string is the contents of the optional
604 attribute string, which may be undefined. If $attr_string is not
605 empty then $attr_hash is a reference to a hash containing the
606 parsed attribute names and values. $driver_dsn is the last part of
607 the DBI DSN string. For example:
608 ($scheme, $driver, $attr_string, $attr_hash, $driver_dsn)
610 = DBI->parse_dsn("DBI:MyDriver(RaiseError=>1):db=test;port=42");
611 $scheme = 'dbi';
612 $driver = 'MyDriver';
613 $attr_string = 'RaiseError=>1';
614 $attr_hash = { 'RaiseError' => '1' };
615 $driver_dsn = 'db=test;port=42';
616 The parse_dsn() method was added in DBI 1.43.
618 "connect"
620 $dbh = DBI->connect($data_source, $username, $password)
621 or die $DBI::errstr;
622 $dbh = DBI->connect($data_source, $username, $password, \%attr)
623 or die $DBI::errstr;
624 Establishes a database connection, or session, to the requested
626 $data_source. Returns a database handle object if the connection
627 succeeds. Use "$dbh->disconnect" to terminate the connection.
628 If the connect fails (see below), it returns "undef" and sets both
630 $DBI::err and $DBI::errstr. (It does not explicitly set $!.) You
631 should generally test the return status of "connect" and "print
632 $DBI::errstr" if it has failed.
633 Multiple simultaneous connections to multiple databases through
635 multiple drivers can be made via the DBI. Simply make one "connect"
636 call for each database and keep a copy of each returned database
637 handle.
638 The $data_source value must begin with ""dbi:"driver_name":"". The
640 driver_name specifies the driver that will be used to make the con‐
641 nection. (Letter case is significant.)
642 As a convenience, if the $data_source parameter is undefined or
644 empty, the DBI will substitute the value of the environment vari‐
645 able "DBI_DSN". If just the driver_name part is empty (i.e., the
646 $data_source prefix is ""dbi::""), the environment variable
647 "DBI_DRIVER" is used. If neither variable is set, then "connect"
648 dies.
649 Examples of $data_source values are:
651 dbi:DriverName:database_name
653 dbi:DriverName:database_name@hostname:port
654 dbi:DriverName:database=database_name;host=hostname;port=port
655 There is no standard for the text following the driver name. Each
657 driver is free to use whatever syntax it wants. The only require‐
658 ment the DBI makes is that all the information is supplied in a
659 single string. You must consult the documentation for the drivers
660 you are using for a description of the syntax they require.
661 It is recommended that drivers support the ODBC style, shown in the
663 last example above. It is also recommended that that they support
664 the three common names '"host"', '"port"', and '"database"' (plus
665 '"db"' as an alias for "database"). This simplifies automatic con‐
666 struction of basic DSNs: "dbi:$driver:data‐
667 base=$db;host=$host;port=$port". Drivers should aim to 'do some‐
668 thing reasonable' when given a DSN in this form, but if any part is
669 meaningless for that driver (such as 'port' for Informix) it should
670 generate an error if that part is not empty.
671 If the environment variable "DBI_AUTOPROXY" is defined (and the
673 driver in $data_source is not ""Proxy"") then the connect request
674 will automatically be changed to:
675 $ENV{DBI_AUTOPROXY};dsn=$data_source
677 "DBI_AUTOPROXY" is typically set as ""dbi:Proxy:host‐
679 name=...;port=..."". If $ENV{DBI_AUTOPROXY} doesn't begin with
680 '"dbi:"' then "dbi:Proxy:" will be prepended to it first. See the
681 DBD::Proxy documentation for more details.
682 If $username or $password are undefined (rather than just empty),
684 then the DBI will substitute the values of the "DBI_USER" and
685 "DBI_PASS" environment variables, respectively. The DBI will warn
686 if the environment variables are not defined. However, the every‐
687 day use of these environment variables is not recommended for secu‐
688 rity reasons. The mechanism is primarily intended to simplify test‐
689 ing. See below for alternative way to specify the username and
690 password.
691 "DBI->connect" automatically installs the driver if it has not been
693 installed yet. Driver installation either returns a valid driver
694 handle, or it dies with an error message that includes the string
695 ""install_driver"" and the underlying problem. So "DBI->connect"
696 will die on a driver installation failure and will only return
697 "undef" on a connect failure, in which case $DBI::errstr will hold
698 the error message. Use "eval { ... }" if you need to catch the
699 ""install_driver"" error.
700 The $data_source argument (with the ""dbi:...:"" prefix removed)
702 and the $username and $password arguments are then passed to the
703 driver for processing. The DBI does not define any interpretation
704 for the contents of these fields. The driver is free to interpret
705 the $data_source, $username, and $password fields in any way, and
706 supply whatever defaults are appropriate for the engine being
707 accessed. (Oracle, for example, uses the ORACLE_SID and TWO_TASK
708 environment variables if no $data_source is specified.)
709 The "AutoCommit" and "PrintError" attributes for each connection
711 default to "on". (See "AutoCommit" and "PrintError" for more infor‐
712 mation.) However, it is strongly recommended that you explicitly
713 define "AutoCommit" rather than rely on the default. The "Print‐
714 Warn" attribute defaults to on if $^W is true, i.e., perl is run‐
715 ning with warnings enabled.
716 The "\%attr" parameter can be used to alter the default settings of
718 "PrintError", "RaiseError", "AutoCommit", and other attributes. For
719 example:
720 $dbh = DBI->connect($data_source, $user, $pass, {
722 PrintError => 0,
723 AutoCommit => 0
724 });
725 The username and password can also be specified using the
727 attributes "Username" and "Password", in which case they take
728 precedence over the $username and $password parameters.
729 You can also define connection attribute values within the
731 $data_source parameter. For example:
732 dbi:DriverName(PrintWarn=>1,PrintError=>0,Taint=>1):...
734 Individual attributes values specified in this way take precedence
736 over any conflicting values specified via the "\%attr" parameter to
737 "connect".
738 The "dbi_connect_method" attribute can be used to specify which
740 driver method should be called to establish the connection. The
741 only useful values are 'connect', 'connect_cached', or some spe‐
742 cialized case like 'Apache::DBI::connect' (which is automatically
743 the default when running within Apache).
744 Where possible, each session ($dbh) is independent from the trans‐
746 actions in other sessions. This is useful when you need to hold
747 cursors open across transactions--for example, if you use one ses‐
748 sion for your long lifespan cursors (typically read-only) and
749 another for your short update transactions.
750 For compatibility with old DBI scripts, the driver can be specified
752 by passing its name as the fourth argument to "connect" (instead of
753 "\%attr"):
754 $dbh = DBI->connect($data_source, $user, $pass, $driver);
756 In this "old-style" form of "connect", the $data_source should not
758 start with ""dbi:driver_name:"". (If it does, the embedded
759 driver_name will be ignored). Also note that in this older form of
760 "connect", the "$dbh->{AutoCommit}" attribute is undefined, the
761 "$dbh->{PrintError}" attribute is off, and the old "DBI_DBNAME"
762 environment variable is checked if "DBI_DSN" is not defined. Beware
763 that this "old-style" "connect" will soon be withdrawn in a future
764 version of DBI.
765 "connect_cached"
767 $dbh = DBI->connect_cached($data_source, $username, $password)
768 or die $DBI::errstr;
769 $dbh = DBI->connect_cached($data_source, $username, $password, \%attr)
770 or die $DBI::errstr;
771 "connect_cached" is like "connect", except that the database handle
773 returned is also stored in a hash associated with the given parame‐
774 ters. If another call is made to "connect_cached" with the same
775 parameter values, then the corresponding cached $dbh will be
776 returned if it is still valid. The cached database handle is
777 replaced with a new connection if it has been disconnected or if
778 the "ping" method fails.
779 That the behaviour of this method differs in several respects from
781 the behaviour of persistent connections implemented by Apache::DBI.
782 However, if Apache::DBI is loaded then "connect_cached" will use
783 it.
784 Caching connections can be useful in some applications, but it can
786 also cause problems, such as too many connections, and so should be
787 used with care. In particular, avoid changing the attributes of a
788 database handle created via connect_cached() because it will affect
789 other code that may be using the same handle.
790 Where multiple separate parts of a program are using con‐
792 nect_cached() to connect to the same database with the same (ini‐
793 tial) attributes it is a good idea to add a private attribute to
794 the connect_cached() call to effectively limit the scope of the
795 caching. For example:
796 DBI->connect_cached(..., { private_foo_cachekey => "Bar", ... });
798 Handles returned from that connect_cached() call will only be
800 returned by other connect_cached() call elsewhere in the code if
801 those other calls also pass in the same attribute values, including
802 the private one. (I've used "private_foo_cachekey" here as an
803 example, you can use any attribute name with a "private_" prefix.)
804 Taking that one step further, you can limit a particular con‐
806 nect_cached() call to return handles unique to that one place in
807 the code by setting the private attribute to a unique value for
808 that place:
809 DBI->connect_cached(..., { private_foo_cachekey => __FILE__.__LINE__, ... });
811 By using a private attribute you still get connection caching for
813 the individual calls to connect_cached() but, by making separate
814 database conections for separate parts of the code, the database
815 handles are isolated from any attribute changes made to other han‐
816 dles.
817 The cache can be accessed (and cleared) via the "CachedKids"
819 attribute:
820 my $CachedKids_hashref = $dbh->{Driver}->{CachedKids};
822 %$CachedKids_hashref = () if $CachedKids_hashref;
823 "available_drivers"
825 @ary = DBI->available_drivers;
826 @ary = DBI->available_drivers($quiet);
827 Returns a list of all available drivers by searching for "DBD::*"
829 modules through the directories in @INC. By default, a warning is
830 given if some drivers are hidden by others of the same name in ear‐
831 lier directories. Passing a true value for $quiet will inhibit the
832 warning.
833 "installed_drivers"
835 %drivers = DBI->installed_drivers();
836 Returns a list of driver name and driver handle pairs for all driv‐
838 ers 'installed' (loaded) into the current process. The driver name
839 does not include the 'DBD::' prefix.
840 To get a list of all drivers available in your perl instalation you
842 can use "available_drivers".
843 Added in DBI 1.49.
845 "installed_versions"
847 DBI->installed_versions;
848 @ary = DBI->installed_versions;
849 %hash = DBI->installed_versions;
850 Calls available_drivers() and attempts to load each of them in turn
852 using install_driver(). For each load that succeeds the driver
853 name and version number are added to a hash. When running under
854 DBI::PurePerl drivers which appear not be pure-perl are ignored.
855 When called in array context the list of successfully loaded driv‐
857 ers is returned (without the 'DBD::' prefix).
858 When called in scalar context a reference to the hash is returned
860 and the hash will also contain other entries for the "DBI" version,
861 "OS" name, etc.
862 When called in a void context the installed_versions() method will
864 print out a formatted list of the hash contents, one per line.
865 Due to the potentially high memory cost and unknown risks of load‐
867 ing in an unknown number of drivers that just happen to be
868 installed on the system, this method is nor recommended for general
869 use. Use available_drivers() instead.
870 The installed_versions() method is primarily intended as a quick
872 way to see from the command line what's installed. For example:
873 perl -MDBI -e 'DBI->installed_versions'
875 The installed_versions() method was added in DBI 1.38.
877 "data_sources"
879 @ary = DBI->data_sources($driver);
880 @ary = DBI->data_sources($driver, \%attr);
881 Returns a list of data sources (databases) available via the named
883 driver. If $driver is empty or "undef", then the value of the
884 "DBI_DRIVER" environment variable is used.
885 The driver will be loaded if it hasn't been already. Note that if
887 the driver loading fails then data_sources() dies with an error
888 message that includes the string ""install_driver"" and the under‐
889 lying problem.
890 Data sources are returned in a form suitable for passing to the
892 "connect" method (that is, they will include the ""dbi:$driver:""
893 prefix).
894 Note that many drivers have no way of knowing what data sources
896 might be available for it. These drivers return an empty or incom‐
897 plete list or may require driver-specific attributes.
898 There is also a data_sources() method defined for database handles.
900 "trace"
902 DBI->trace($trace_setting)
903 DBI->trace($trace_setting, $trace_filename)
904 $trace_setting = DBI->trace;
905 The "DBI->trace" method sets the global default trace settings and
907 returns the previous trace settings. It can also be used to change
908 where the trace output is sent.
909 There's a similar method, "$h->trace", which sets the trace set‐
911 tings for the specific handle it's called on.
912 See the "TRACING" section for full details about the DBI's powerful
914 tracing facilities.
915 DBI Utility Functions
917 In addition to the DBI methods listed in the previous section, the DBI
919 package also provides several utility functions.
920 These can be imported into your code by listing them in the "use"
922 statement. For example:
923 use DBI qw(neat data_diff);
925 Alternatively, all these utility functions (except hash) can be
927 imported using the ":utils" import tag. For example:
928 use DBI qw(:utils);
930 "data_string_desc"
932 $description = data_string_desc($string);
933 Returns an informal description of the string. For example:
935 UTF8 off, ASCII, 42 characters 42 bytes
937 UTF8 off, non-ASCII, 42 characters 42 bytes
938 UTF8 on, non-ASCII, 4 characters 6 bytes
939 UTF8 on but INVALID encoding, non-ASCII, 4 characters 6 bytes
940 UTF8 off, undef
941 The initial "UTF8" on/off refers to Perl's internal SvUTF8 flag.
943 If $string has the SvUTF8 flag set but the sequence of bytes it
944 contains are not a valid UTF-8 encoding then data_string_desc()
945 will report "UTF8 on but INVALID encoding".
946 The "ASCII" vs "non-ASCII" portion shows "ASCII" if all the charac‐
948 ters in the string are ASCII (have code points <= 127).
949 The data_string_desc() function was added in DBI 1.46.
951 "data_string_diff"
953 $diff = data_string_diff($a, $b);
954 Returns an informal description of the first character difference
956 between the strings. If both $a and $b contain the same sequence of
957 characters then data_string_diff() returns an empty string. For
958 example:
959 Params a & b Result
961 ------------ ------
962 'aaa', 'aaa' ''
963 'aaa', 'abc' 'Strings differ at index 2: a[2]=a, b[2]=b'
964 'aaa', undef 'String b is undef, string a has 3 characters'
965 'aaa', 'aa' 'String b truncated after 2 characters'
966 Unicode characters are reported in "\x{XXXX}" format. Unicode code
968 points in the range U+0800 to U+08FF are unassigned and most likely
969 to occur due to double-encoding. Characters in this range are
970 reported as "\x{08XX}='C'" where "C" is the corresponding latin-1
971 character.
972 The data_string_diff() function only considers logical characters
974 and not the underlying encoding. See "data_diff" for an alterna‐
975 tive.
976 The data_string_diff() function was added in DBI 1.46.
978 "data_diff"
980 $diff = data_diff($a, $b);
981 $diff = data_diff($a, $b, $logical);
982 Returns an informal description of the difference between two
984 strings. It calls "data_string_desc" and "data_string_diff" and
985 returns the combined results as a multi-line string.
986 For example, "data_diff("abc", "ab\x{263a}")" will return:
988 a: UTF8 off, ASCII, 3 characters 3 bytes
990 b: UTF8 on, non-ASCII, 3 characters 5 bytes
991 Strings differ at index 2: a[2]=c, b[2]=\x{263A}
992 If $a and $b are identical in both the characters they contain and
994 their physical encoding then data_diff() returns an empty string.
995 If $logical is true then physical encoding differences are ignored
996 (but are still reported if there is a difference in the charac‐
997 ters).
998 The data_diff() function was added in DBI 1.46.
1000 "neat"
1002 $str = neat($value);
1003 $str = neat($value, $maxlen);
1004 Return a string containing a neat (and tidy) representation of the
1006 supplied value.
1007 Strings will be quoted, although internal quotes will not be
1009 escaped. Values known to be numeric will be unquoted. Undefined
1010 (NULL) values will be shown as "undef" (without quotes).
1011 If the string is flagged internally as utf8 then double quotes will
1013 be used, otherwise single quotes are used and unprintable charac‐
1014 ters will be replaced by dot (.).
1015 For result strings longer than $maxlen the result string will be
1017 truncated to "$maxlen-4" and ""...'"" will be appended. If $maxlen
1018 is 0 or "undef", it defaults to $DBI::neat_maxlen which, in turn,
1019 defaults to 400.
1020 This function is designed to format values for human consumption.
1022 It is used internally by the DBI for "trace" output. It should typ‐
1023 ically not be used for formatting values for database use. (See
1024 also "quote".)
1025 "neat_list"
1027 $str = neat_list(\@listref, $maxlen, $field_sep);
1028 Calls "neat" on each element of the list and returns a string con‐
1030 taining the results joined with $field_sep. $field_sep defaults to
1031 ", ".
1032 "looks_like_number"
1034 @bool = looks_like_number(@array);
1035 Returns true for each element that looks like a number. Returns
1037 false for each element that does not look like a number. Returns
1038 "undef" for each element that is undefined or empty.
1039 "hash"
1041 $hash_value = DBI::hash($buffer, $type);
1042 Return a 32-bit integer 'hash' value corresponding to the contents
1044 of $buffer. The $type parameter selects which kind of hash algo‐
1045 rithm should be used.
1046 For the technically curious, type 0 (which is the default if $type
1048 isn't specified) is based on the Perl 5.1 hash except that the
1049 value is forced to be negative (for obscure historical reasons).
1050 Type 1 is the better "Fowler / Noll / Vo" (FNV) hash. See
1051 <http://www.isthe.com/chongo/tech/comp/fnv/> for more information.
1052 Both types are implemented in C and are very fast.
1053 This function doesn't have much to do with databases, except that
1055 it can be handy to store hash values in a database.
1056 DBI Dynamic Attributes
1058 Dynamic attributes are always associated with the last handle used
1060 (that handle is represented by $h in the descriptions below).
1061 Where an attribute is equivalent to a method call, then refer to the
1063 method call for all related documentation.
1064 Warning: these attributes are provided as a convenience but they do
1066 have limitations. Specifically, they have a short lifespan: because
1067 they are associated with the last handle used, they should only be used
1068 immediately after calling the method that "sets" them. If in any
1069 doubt, use the corresponding method call.
1070 $DBI::err
1072 Equivalent to "$h->err".
1073 $DBI::errstr
1075 Equivalent to "$h->errstr".
1076 $DBI::state
1078 Equivalent to "$h->state".
1079 $DBI::rows
1081 Equivalent to "$h->rows". Please refer to the documentation for the
1082 "rows" method.
1083 $DBI::lasth
1085 Returns the DBI object handle used for the most recent DBI method
1086 call. If the last DBI method call was a DESTROY then $DBI::lasth
1087 will return the handle of the parent of the destroyed handle, if
1088 there is one.
1089
1091 The following methods can be used by all types of DBI handles.
1092 "err"
1094 $rv = $h->err;
1095 Returns the native database engine error code from the last driver
1097 method called. The code is typically an integer but you should not
1098 assume that.
1099 The DBI resets $h->err to undef before almost all DBI method calls,
1101 so the value only has a short lifespan. Also, for most drivers, the
1102 statement handles share the same error variable as the parent data‐
1103 base handle, so calling a method on one handle may reset the error
1104 on the related handles.
1105 (Methods which don't reset err before being called include err()
1107 and errstr(), obviously, state(), rows(), func(), trace(),
1108 trace_msg(), ping(), and the tied hash attribute FETCH() and
1109 STORE() methods.)
1110 If you need to test for specific error conditions and have your
1112 program be portable to different database engines, then you'll need
1113 to determine what the corresponding error codes are for all those
1114 engines and test for all of them.
1115 A driver may return 0 from err() to indicate a warning condition
1117 after a method call. Similarly, a driver may return an empty string
1118 to indicate a 'success with information' condition. In both these
1119 cases the value is false but not undef. The errstr() and state()
1120 methods may be used to retrieve extra information in these cases.
1121 See "set_err" for more information.
1123 "errstr"
1125 $str = $h->errstr;
1126 Returns the native database engine error message from the last DBI
1128 method called. This has the same lifespan issues as the "err"
1129 method described above.
1130 The returned string may contain multiple messages separated by new‐
1132 line characters.
1133 The errstr() method should not be used to test for errors, use
1135 err() for that, because drivers may return 'success with informa‐
1136 tion' or warning messages via errstr() for methods that have not
1137 'failed'.
1138 See "set_err" for more information.
1140 "state"
1142 $str = $h->state;
1143 Returns a state code in the standard SQLSTATE five character for‐
1145 mat. Note that the specific success code 00000 is translated to
1146 any empty string (false). If the driver does not support SQLSTATE
1147 (and most don't), then state will return "S1000" (General Error)
1148 for all errors.
1149 The driver is free to return any value via "state", e.g., warning
1151 codes, even if it has not declared an error by returning a true
1152 value via the "err" method described above.
1153 The state() method should not be used to test for errors, use err()
1155 for that, because drivers may return a 'success with information'
1156 or warning state code via errstr() for methods that have not
1157 'failed'.
1158 "set_err"
1160 $rv = $h->set_err($err, $errstr);
1161 $rv = $h->set_err($err, $errstr, $state);
1162 $rv = $h->set_err($err, $errstr, $state, $method);
1163 $rv = $h->set_err($err, $errstr, $state, $method, $rv);
1164 Set the "err", "errstr", and "state" values for the handle. This
1166 method is typically only used by DBI drivers and DBI subclasses.
1167 If the "HandleSetErr" attribute holds a reference to a subroutine
1169 it is called first. The subroutine can alter the $err, $errstr,
1170 $state, and $method values. See "HandleSetErr" for full details.
1171 If the subroutine returns a true value then the handle "err",
1172 "errstr", and "state" values are not altered and set_err() returns
1173 an empty list (it normally returns $rv which defaults to undef, see
1174 below).
1175 Setting "err" to a true value indicates an error and will trigger
1177 the normal DBI error handling mechanisms, such as "RaiseError" and
1178 "HandleError", if they are enabled, when execution returns from the
1179 DBI back to the application.
1180 Setting "err" to "" indicates an 'information' state, and setting
1182 it to "0" indicates a 'warning' state. Setting "err" to "undef"
1183 also sets "errstr" to undef, and "state" to "", irrespective of the
1184 values of the $errstr and $state parameters.
1185 The $method parameter provides an alternate method name for the
1187 "RaiseError"/"PrintError"/"PrintWarn" error string instead of the
1188 fairly unhelpful '"set_err"'.
1189 The "set_err" method normally returns undef. The $rv parameter
1191 provides an alternate return value.
1192 Some special rules apply if the "err" or "errstr" values for the
1194 handle are already set...
1195 If "errstr" is true then: "" [err was %s now %s]"" is appended if
1197 $err is true and "err" is already true; "" [state was %s now %s]""
1198 is appended if $state is true and "state" is already true; then
1199 ""\n"" and the new $errstr are appended. Obviously the %s's above
1200 are replaced by the corresponding values.
1201 The handle "err" value is set to $err if: $err is true; or handle
1203 "err" value is undef; or $err is defined and the length is greater
1204 than the handle "err" length. The effect is that an 'information'
1205 state only overrides undef; a 'warning' overrides undef or 'infor‐
1206 mation', and an 'error' state overrides anything.
1207 The handle "state" value is set to $state if $state is true and the
1209 handle "err" value was set (by the rules above).
1210 Support for warning and information states was added in DBI 1.41.
1212 "trace"
1214 $h->trace($trace_settings);
1215 $h->trace($trace_settings, $trace_filename);
1216 $trace_settings = $h->trace;
1217 The trace() method is used to alter the trace settings for a handle
1219 (and any future children of that handle). It can also be used to
1220 change where the trace output is sent.
1221 There's a similar method, "DBI->trace", which sets the global
1223 default trace settings.
1224 See the "TRACING" section for full details about the DBI's powerful
1226 tracing facilities.
1227 "trace_msg"
1229 $h->trace_msg($message_text);
1230 $h->trace_msg($message_text, $min_level);
1231 Writes $message_text to the trace file if the trace level is
1233 greater than or equal to $min_level (which defaults to 1). Can
1234 also be called as "DBI->trace_msg($msg)".
1235 See "TRACING" for more details.
1237 "func"
1239 $h->func(@func_arguments, $func_name) or die ...;
1240 The "func" method can be used to call private non-standard and non-
1242 portable methods implemented by the driver. Note that the function
1243 name is given as the last argument.
1244 It's also important to note that the func() method does not clear a
1246 previous error ($DBI::err etc.) and it does not trigger automatic
1247 error detection (RaiseError etc.) so you must check the return sta‐
1248 tus and/or $h->err to detect errors.
1249 (This method is not directly related to calling stored procedures.
1251 Calling stored procedures is currently not defined by the DBI.
1252 Some drivers, such as DBD::Oracle, support it in non-portable ways.
1253 See driver documentation for more details.)
1254 See also "install_method" for how you can avoid needing to use
1256 func() and gain.
1257 "can"
1259 $is_implemented = $h->can($method_name);
1260 Returns true if $method_name is implemented by the driver or a
1262 default method is provided by the DBI. It returns false where a
1263 driver hasn't implemented a method and the default method is pro‐
1264 vided by the DBI is just an empty stub.
1265 "parse_trace_flags"
1267 $trace_settings_integer = $h->parse_trace_flags($trace_settings);
1268 Parses a string containing trace settings and returns the corre‐
1270 sponding integer value used internally by the DBI and drivers.
1271 The $trace_settings argument is a string containing a trace level
1273 between 0 and 15 and/or trace flag names separated by vertical bar
1274 (""⎪"") or comma ("","") characters. For example: "SQL⎪3⎪foo".
1275 It uses the parse_trace_flag() method, described below, to process
1277 the individual trage flag names.
1278 The parse_trace_flags() method was added in DBI 1.42.
1280 "parse_trace_flag"
1282 $bit_flag = $h->parse_trace_flag($trace_flag_name);
1283 Returns the bit flag corresponding to the trace flag name in
1285 $trace_flag_name. Drivers are expected to override this method and
1286 check if $trace_flag_name is a driver specific trace flags and, if
1287 not, then call the DBIs default parse_trace_flag().
1288 The parse_trace_flag() method was added in DBI 1.42.
1290 "swap_inner_handle"
1292 $rc = $h1->swap_inner_handle( $h2 );
1293 $rc = $h1->swap_inner_handle( $h2, $allow_reparent );
1294 Brain transplants for handles. You don't need to know about this
1296 unless you want to become a handle surgeon.
1297 A DBI handle is a reference to a tied hash. A tied hash has an
1299 inner hash that actually holds the contents. The swap_inner_han‐
1300 dle() method swaps the inner hashes between two handles. The $h1
1301 and $h2 handles still point to the same tied hashes, but what those
1302 hashes are tied to has been swapped. In effect $h1 becomes $h2 and
1303 vice-versa. This is powerful stuff, expect problems. Use with care.
1304 As a small safety measure, the two handles, $h1 and $h2, have to
1306 share the same parent unless $allow_reparent is true.
1307 The swap_inner_handle() method was added in DBI 1.44.
1309 Here's a quick kind of 'diagram' as a worked example to help think
1311 about what's happening:
1312 Original state:
1314 dbh1o -> dbh1i
1315 sthAo -> sthAi(dbh1i)
1316 dbh2o -> dbh2i
1317 swap_inner_handle dbh1o with dbh2o:
1319 dbh2o -> dbh1i
1320 sthAo -> sthAi(dbh1i)
1321 dbh1o -> dbh2i
1322 create new sth from dbh1o:
1324 dbh2o -> dbh1i
1325 sthAo -> sthAi(dbh1i)
1326 dbh1o -> dbh2i
1327 sthBo -> sthBi(dbh2i)
1328 swap_inner_handle sthAo with sthBo:
1330 dbh2o -> dbh1i
1331 sthBo -> sthAi(dbh1i)
1332 dbh1o -> dbh2i
1333 sthAo -> sthBi(dbh2i)
1334
1336 These attributes are common to all types of DBI handles.
1337 Some attributes are inherited by child handles. That is, the value of
1339 an inherited attribute in a newly created statement handle is the same
1340 as the value in the parent database handle. Changes to attributes in
1341 the new statement handle do not affect the parent database handle and
1342 changes to the database handle do not affect existing statement han‐
1343 dles, only future ones.
1344 Attempting to set or get the value of an unknown attribute generates a
1346 warning, except for private driver specific attributes (which all have
1347 names starting with a lowercase letter).
1348 Example:
1350 $h->{AttributeName} = ...; # set/write
1352 ... = $h->{AttributeName}; # get/read
1353 "Warn" (boolean, inherited)
1355 The "Warn" attribute enables useful warnings for certain bad prac‐
1356 tices. It is enabled by default and should only be disabled in rare
1357 circumstances. Since warnings are generated using the Perl "warn"
1358 function, they can be intercepted using the Perl $SIG{__WARN__}
1359 hook.
1360 The "Warn" attribute is not related to the "PrintWarn" attribute.
1362 "Active" (boolean, read-only)
1364 The "Active" attribute is true if the handle object is "active".
1365 This is rarely used in applications. The exact meaning of active is
1366 somewhat vague at the moment. For a database handle it typically
1367 means that the handle is connected to a database ("$dbh->discon‐
1368 nect" sets "Active" off). For a statement handle it typically
1369 means that the handle is a "SELECT" that may have more data to
1370 fetch. (Fetching all the data or calling "$sth->finish" sets
1371 "Active" off.)
1372 "Executed" (boolean)
1374 The "Executed" attribute is true if the handle object has been
1375 "executed". Currently only the $dbh do() method and the $sth exe‐
1376 cute(), execute_array(), and execute_for_fetch() methods set the
1377 "Executed" attribute.
1378 When it's set on a handle it is also set on the parent handle at
1380 the same time. So calling execute() on a $sth also sets the "Exe‐
1381 cuted" attribute on the parent $dbh.
1382 The "Executed" attribute for a database handle is cleared by the
1384 commit() and rollback() methods. The "Executed" attribute of a
1385 statement handle is not cleared by the DBI under any circumstances
1386 and so acts as a permanent record of whether the statement handle
1387 was ever used.
1388 The "Executed" attribute was added in DBI 1.41.
1390 "Kids" (integer, read-only)
1392 For a driver handle, "Kids" is the number of currently existing
1393 database handles that were created from that driver handle. For a
1394 database handle, "Kids" is the number of currently existing state‐
1395 ment handles that were created from that database handle. For a
1396 statement handle, the value is zero.
1397 "ActiveKids" (integer, read-only)
1399 Like "Kids", but only counting those that are "Active" (as above).
1400 "CachedKids" (hash ref)
1402 For a database handle, "CachedKids" returns a reference to the
1403 cache (hash) of statement handles created by the "prepare_cached"
1404 method. For a driver handle, returns a reference to the cache
1405 (hash) of database handles created by the "connect_cached" method.
1406 "Type" (scalar)
1408 The "Type" attribute identifies the type of a DBI handle. Returns
1409 "dr" for driver handles, "db" for database handles and "st" for
1410 statement handles.
1411 "ChildHandles" (array ref)
1413 The ChildHandles attribute contains a reference to an array of all
1414 the handles created by this handle which are still accessible. The
1415 contents of the array are weak-refs and will become undef when the
1416 handle goes out of scope.
1417 "ChildHandles" returns undef if your perl version does not support
1419 weak references (check the Scalar::Util module). The referenced
1420 array returned should be treated as read-only.
1421 For example, to enumerate all driver handles, database handles and
1423 statement handles:
1424 sub show_child_handles {
1426 my ($h, $level) = @_;
1427 $level ⎪⎪= 0;
1428 printf "%sh %s %s\n", $h->{Type}, "\t" x $level, $h;
1429 show_child_handles($_, $level + 1)
1430 for (grep { defined } @{$h->{ChildHandles}});
1431 }
1432 my %drivers = DBI->installed_drivers();
1434 show_child_handles($_) for (values %drivers);
1435 "CompatMode" (boolean, inherited)
1437 The "CompatMode" attribute is used by emulation layers (such as
1438 Oraperl) to enable compatible behaviour in the underlying driver
1439 (e.g., DBD::Oracle) for this handle. Not normally set by applica‐
1440 tion code.
1441 It also has the effect of disabling the 'quick FETCH' of attribute
1443 values from the handles attribute cache. So all attribute values
1444 are handled by the drivers own FETCH method. This makes them
1445 slightly slower but is useful for special-purpose drivers like
1446 DBD::Multiplex.
1447 "InactiveDestroy" (boolean)
1449 The default value, false, means a handle will be fully destroyed as
1450 normal when the last reference to it is removed, just as you'd
1451 expect.
1452 If set true then the handle will be treated by the DESTROY as if it
1454 was no longer Active, and so the database engine related effects of
1455 DESTROYing a handle will be skipped.
1456 Think of the name as meaning 'treat the handle as not-Active in the
1458 DESTROY method'.
1459 For a database handle, this attribute does not disable an explicit
1461 call to the disconnect method, only the implicit call from DESTROY
1462 that happens if the handle is still marked as "Active".
1463 This attribute is specifically designed for use in Unix applica‐
1465 tions that "fork" child processes. Either the parent or the child
1466 process, but not both, should set "InactiveDestroy" true on all
1467 their shared handles. (Note that some databases, including Oracle,
1468 don't support passing a database connection across a fork.)
1469 To help tracing applications using fork the process id is shown in
1471 the trace log whenever a DBI or handle trace() method is called.
1472 The process id also shown for every method call if the DBI trace
1473 level (not handle trace level) is set high enough to show the trace
1474 from the DBI's method dispatcher, e.g. >= 9.
1475 "PrintWarn" (boolean, inherited)
1477 The "PrintWarn" attribute controls the printing of warnings
1478 recorded by the driver. When set to a true value the DBI will
1479 check method calls to see if a warning condition has been set. If
1480 so, the DBI will effectively do a "warn("$class $method warning:
1481 $DBI::errstr")" where $class is the driver class and $method is the
1482 name of the method which failed. E.g.,
1483 DBD::Oracle::db execute warning: ... warning text here ...
1485 By default, "DBI->connect" sets "PrintWarn" "on" if $^W is true,
1487 i.e., perl is running with warnings enabled.
1488 If desired, the warnings can be caught and processed using a
1490 $SIG{__WARN__} handler or modules like CGI::Carp and CGI::Error‐
1491 Wrap.
1492 See also "set_err" for how warnings are recorded and "HandleSetErr"
1494 for how to influence it.
1495 Fetching the full details of warnings can require an extra round-
1497 trip to the database server for some drivers. In which case the
1498 driver may opt to only fetch the full details of warnings if the
1499 "PrintWarn" attribute is true. If "PrintWarn" is false then these
1500 drivers should still indicate the fact that there were warnings by
1501 setting the warning string to, for example: "3 warnings".
1502 "PrintError" (boolean, inherited)
1504 The "PrintError" attribute can be used to force errors to generate
1505 warnings (using "warn") in addition to returning error codes in the
1506 normal way. When set "on", any method which results in an error
1507 occuring will cause the DBI to effectively do a "warn("$class
1508 $method failed: $DBI::errstr")" where $class is the driver class
1509 and $method is the name of the method which failed. E.g.,
1510 DBD::Oracle::db prepare failed: ... error text here ...
1512 By default, "DBI->connect" sets "PrintError" "on".
1514 If desired, the warnings can be caught and processed using a
1516 $SIG{__WARN__} handler or modules like CGI::Carp and CGI::Error‐
1517 Wrap.
1518 "RaiseError" (boolean, inherited)
1520 The "RaiseError" attribute can be used to force errors to raise
1521 exceptions rather than simply return error codes in the normal way.
1522 It is "off" by default. When set "on", any method which results in
1523 an error will cause the DBI to effectively do a "die("$class
1524 $method failed: $DBI::errstr")", where $class is the driver class
1525 and $method is the name of the method that failed. E.g.,
1526 DBD::Oracle::db prepare failed: ... error text here ...
1528 If you turn "RaiseError" on then you'd normally turn "PrintError"
1530 off. If "PrintError" is also on, then the "PrintError" is done
1531 first (naturally).
1532 Typically "RaiseError" is used in conjunction with "eval { ... }"
1534 to catch the exception that's been thrown and followed by an "if
1535 ($@) { ... }" block to handle the caught exception. For example:
1536 eval {
1538 ...
1539 $sth->execute();
1540 ...
1541 };
1542 if ($@) {
1543 # $sth->err and $DBI::err will be true if error was from DBI
1544 warn $@; # print the error
1545 ... # do whatever you need to deal with the error
1546 }
1547 In that eval block the $DBI::lasth variable can be useful for diag‐
1549 nosis and reporting if you can't be sure which handle triggered the
1550 error. For example, $DBI::lasth->{Type} and $DBI::lasth->{State‐
1551 ment}.
1552 See also "Transactions".
1554 If you want to temporarily turn "RaiseError" off (inside a library
1556 function that is likely to fail, for example), the recommended way
1557 is like this:
1558 {
1560 local $h->{RaiseError}; # localize and turn off for this block
1561 ...
1562 }
1563 The original value will automatically and reliably be restored by
1565 Perl, regardless of how the block is exited. The same logic
1566 applies to other attributes, including "PrintError".
1567 "HandleError" (code ref, inherited)
1569 The "HandleError" attribute can be used to provide your own alter‐
1570 native behaviour in case of errors. If set to a reference to a sub‐
1571 routine then that subroutine is called when an error is detected
1572 (at the same point that "RaiseError" and "PrintError" are handled).
1573 The subroutine is called with three parameters: the error message
1575 string that "RaiseError" and "PrintError" would use, the DBI handle
1576 being used, and the first value being returned by the method that
1577 failed (typically undef).
1578 If the subroutine returns a false value then the "RaiseError"
1580 and/or "PrintError" attributes are checked and acted upon as nor‐
1581 mal.
1582 For example, to "die" with a full stack trace for any error:
1584 use Carp;
1586 $h->{HandleError} = sub { confess(shift) };
1587 Or to turn errors into exceptions:
1589 use Exception; # or your own favourite exception module
1591 $h->{HandleError} = sub { Exception->new('DBI')->raise($_[0]) };
1592 It is possible to 'stack' multiple HandleError handlers by using
1594 closures:
1595 sub your_subroutine {
1597 my $previous_handler = $h->{HandleError};
1598 $h->{HandleError} = sub {
1599 return 1 if $previous_handler and &$previous_handler(@_);
1600 ... your code here ...
1601 };
1602 }
1603 Using a "my" inside a subroutine to store the previous "HandleEr‐
1605 ror" value is important. See perlsub and perlref for more informa‐
1606 tion about closures.
1607 It is possible for "HandleError" to alter the error message that
1609 will be used by "RaiseError" and "PrintError" if it returns false.
1610 It can do that by altering the value of $_[0]. This example appends
1611 a stack trace to all errors and, unlike the previous example using
1612 Carp::confess, this will work "PrintError" as well as "RaiseError":
1613 $h->{HandleError} = sub { $_[0]=Carp::longmess($_[0]); 0; };
1615 It is also possible for "HandleError" to hide an error, to a lim‐
1617 ited degree, by using "set_err" to reset $DBI::err and
1618 $DBI::errstr, and altering the return value of the failed method.
1619 For example:
1620 $h->{HandleError} = sub {
1622 return 0 unless $_[0] =~ /^\S+ fetchrow_arrayref failed:/;
1623 return 0 unless $_[1]->err == 1234; # the error to 'hide'
1624 $h->set_err(undef,undef); # turn off the error
1625 $_[2] = [ ... ]; # supply alternative return value
1626 return 1;
1627 };
1628 This only works for methods which return a single value and is hard
1630 to make reliable (avoiding infinite loops, for example) and so
1631 isn't recommended for general use! If you find a good use for it
1632 then please let me know.
1633 "HandleSetErr" (code ref, inherited)
1635 The "HandleSetErr" attribute can be used to intercept the setting
1636 of handle "err", "errstr", and "state" values. If set to a refer‐
1637 ence to a subroutine then that subroutine is called whenever
1638 set_err() is called, typically by the driver or a subclass.
1639 The subroutine is called with five arguments, the first five that
1641 were passed to set_err(): the handle, the "err", "errstr", and
1642 "state" values being set, and the method name. These can be altered
1643 by changing the values in the @_ array. The return value affects
1644 set_err() behaviour, see "set_err" for details.
1645 It is possible to 'stack' multiple HandleSetErr handlers by using
1647 closures. See "HandleError" for an example.
1648 The "HandleSetErr" and "HandleError" subroutines differ in subtle
1650 but significant ways. HandleError is only invoked at the point
1651 where the DBI is about to return to the application with "err" set
1652 true. It's not invoked by the failure of a method that's been
1653 called by another DBI method. HandleSetErr, on the other hand, is
1654 called whenever set_err() is called with a defined "err" value,
1655 even if false. So it's not just for errors, despite the name, but
1656 also warn and info states. The set_err() method, and thus Handle‐
1657 SetErr, may be called multiple times within a method and is usually
1658 invoked from deep within driver code.
1659 In theory a driver can use the return value from HandleSetErr via
1661 set_err() to decide whether to continue or not. If set_err()
1662 returns an empty list, indicating that the HandleSetErr code has
1663 'handled' the 'error', the driver could then continue instead of
1664 failing (if that's a reasonable thing to do). This isn't excepted
1665 to be common and any such cases should be clearly marked in the
1666 driver documentation and discussed on the dbi-dev mailing list.
1667 The "HandleSetErr" attribute was added in DBI 1.41.
1669 "ErrCount" (unsigned integer)
1671 The "ErrCount" attribute is incremented whenever the set_err()
1672 method records an error. It isn't incremented by warnings or infor‐
1673 mation states. It is not reset by the DBI at any time.
1674 The "ErrCount" attribute was added in DBI 1.41. Older drivers may
1676 not have been updated to use set_err() to record errors and so this
1677 attribute may not be incremented when using them.
1678 "ShowErrorStatement" (boolean, inherited)
1680 The "ShowErrorStatement" attribute can be used to cause the rele‐
1681 vant Statement text to be appended to the error messages generated
1682 by the "RaiseError", "PrintError", and "PrintWarn" attributes.
1683 Only applies to errors on statement handles plus the prepare(),
1684 do(), and the various "select*()" database handle methods. (The
1685 exact format of the appended text is subject to change.)
1686 If "$h->{ParamValues}" returns a hash reference of parameter
1688 (placeholder) values then those are formatted and appended to the
1689 end of the Statement text in the error message.
1690 "TraceLevel" (integer, inherited)
1692 The "TraceLevel" attribute can be used as an alternative to the
1693 "trace" method to set the DBI trace level and trace flags for a
1694 specific handle. See "TRACING" for more details.
1695 The "TraceLevel" attribute is especially useful combined with
1697 "local" to alter the trace settings for just a single block of
1698 code.
1699 "FetchHashKeyName" (string, inherited)
1701 The "FetchHashKeyName" attribute is used to specify whether the
1702 fetchrow_hashref() method should perform case conversion on the
1703 field names used for the hash keys. For historical reasons it
1704 defaults to '"NAME"' but it is recommended to set it to '"NAME_lc"'
1705 (convert to lower case) or '"NAME_uc"' (convert to upper case)
1706 according to your preference. It can only be set for driver and
1707 database handles. For statement handles the value is frozen when
1708 prepare() is called.
1709 "ChopBlanks" (boolean, inherited)
1711 The "ChopBlanks" attribute can be used to control the trimming of
1712 trailing space characters from fixed width character (CHAR) fields.
1713 No other field types are affected, even where field values have
1714 trailing spaces.
1715 The default is false (although it is possible that the default may
1717 change). Applications that need specific behaviour should set the
1718 attribute as needed.
1719 Drivers are not required to support this attribute, but any driver
1721 which does not support it must arrange to return "undef" as the
1722 attribute value.
1723 "LongReadLen" (unsigned integer, inherited)
1725 The "LongReadLen" attribute may be used to control the maximum
1726 length of 'long' type fields (LONG, BLOB, CLOB, MEMO, etc.) which
1727 the driver will read from the database automatically when it
1728 fetches each row of data.
1729 The "LongReadLen" attribute only relates to fetching and reading
1731 long values; it is not involved in inserting or updating them.
1732 A value of 0 means not to automatically fetch any long data. Driv‐
1734 ers may return undef or an empty string for long fields when "Lon‐
1735 gReadLen" is 0.
1736 The default is typically 0 (zero) bytes but may vary between driv‐
1738 ers. Applications fetching long fields should set this value to
1739 slightly larger than the longest long field value to be fetched.
1740 Some databases return some long types encoded as pairs of hex dig‐
1742 its. For these types, "LongReadLen" relates to the underlying data
1743 length and not the doubled-up length of the encoded string.
1744 Changing the value of "LongReadLen" for a statement handle after it
1746 has been "prepare"'d will typically have no effect, so it's common
1747 to set "LongReadLen" on the $dbh before calling "prepare".
1748 For most drivers the value used here has a direct effect on the
1750 memory used by the statement handle while it's active, so don't be
1751 too generous. If you can't be sure what value to use you could exe‐
1752 cute an extra select statement to determine the longest value. For
1753 example:
1754 $dbh->{LongReadLen} = $dbh->selectrow_array(qq{
1756 SELECT MAX(OCTET_LENGTH(long_column_name))
1757 FROM table WHERE ...
1758 });
1759 $sth = $dbh->prepare(qq{
1760 SELECT long_column_name, ... FROM table WHERE ...
1761 });
1762 You may need to take extra care if the table can be modified
1764 between the first select and the second being executed. You may
1765 also need to use a different function if OCTET_LENGTH() does not
1766 work for long types in your database. For example, for Sybase use
1767 DATALENGTH() and for Oracle use LENGTHB().
1768 See also "LongTruncOk" for information on truncation of long types.
1770 "LongTruncOk" (boolean, inherited)
1772 The "LongTruncOk" attribute may be used to control the effect of
1773 fetching a long field value which has been truncated (typically
1774 because it's longer than the value of the "LongReadLen" attribute).
1775 By default, "LongTruncOk" is false and so fetching a long value
1777 that needs to be truncated will cause the fetch to fail. (Applica‐
1778 tions should always be sure to check for errors after a fetch loop
1779 in case an error, such as a divide by zero or long field trunca‐
1780 tion, caused the fetch to terminate prematurely.)
1781 If a fetch fails due to a long field truncation when "LongTruncOk"
1783 is false, many drivers will allow you to continue fetching further
1784 rows.
1785 See also "LongReadLen".
1787 "TaintIn" (boolean, inherited)
1789 If the "TaintIn" attribute is set to a true value and Perl is run‐
1790 ning in taint mode (e.g., started with the "-T" option), then all
1791 the arguments to most DBI method calls are checked for being
1792 tainted. This may change.
1793 The attribute defaults to off, even if Perl is in taint mode. See
1795 perlsec for more about taint mode. If Perl is not running in taint
1796 mode, this attribute has no effect.
1797 When fetching data that you trust you can turn off the TaintIn
1799 attribute, for that statement handle, for the duration of the fetch
1800 loop.
1801 The "TaintIn" attribute was added in DBI 1.31.
1803 "TaintOut" (boolean, inherited)
1805 If the "TaintOut" attribute is set to a true value and Perl is run‐
1806 ning in taint mode (e.g., started with the "-T" option), then most
1807 data fetched from the database is considered tainted. This may
1808 change.
1809 The attribute defaults to off, even if Perl is in taint mode. See
1811 perlsec for more about taint mode. If Perl is not running in taint
1812 mode, this attribute has no effect.
1813 When fetching data that you trust you can turn off the TaintOut
1815 attribute, for that statement handle, for the duration of the fetch
1816 loop.
1817 Currently only fetched data is tainted. It is possible that the
1819 results of other DBI method calls, and the value of fetched
1820 attributes, may also be tainted in future versions. That change may
1821 well break your applications unless you take great care now. If you
1822 use DBI Taint mode, please report your experience and any sugges‐
1823 tions for changes.
1824 The "TaintOut" attribute was added in DBI 1.31.
1826 "Taint" (boolean, inherited)
1828 The "Taint" attribute is a shortcut for "TaintIn" and "TaintOut"
1829 (it is also present for backwards compatibility).
1830 Setting this attribute sets both "TaintIn" and "TaintOut", and
1832 retrieving it returns a true value if and only if "TaintIn" and
1833 "TaintOut" are both set to true values.
1834 "Profile" (inherited)
1836 The "Profile" attribute enables the collection and reporting of
1837 method call timing statistics. See the DBI::Profile module docu‐
1838 mentation for much more detail.
1839 The "Profile" attribute was added in DBI 1.24.
1841 "private_your_module_name_*"
1843 The DBI provides a way to store extra information in a DBI handle
1844 as "private" attributes. The DBI will allow you to store and
1845 retrieve any attribute which has a name starting with ""private_"".
1846 It is strongly recommended that you use just one private attribute
1848 (e.g., use a hash ref) and give it a long and unambiguous name that
1849 includes the module or application name that the attribute relates
1850 to (e.g., ""private_YourFullModuleName_thingy"").
1851 Because of the way the Perl tie mechanism works you cannot reliably
1853 use the "⎪⎪=" operator directly to initialise the attribute, like
1854 this:
1855 my $foo = $dbh->{private_yourmodname_foo} ⎪⎪= { ... }; # WRONG
1857 you should use a two step approach like this:
1859 my $foo = $dbh->{private_yourmodname_foo};
1861 $foo ⎪⎪= $dbh->{private_yourmodname_foo} = { ... };
1862 This attribute is primarily of interest to people sub-classing DBI.
1864
1866 This section covers the methods and attributes associated with database
1867 handles.
1868 Database Handle Methods
1870 The following methods are specified for DBI database handles:
1872 "clone"
1874 $new_dbh = $dbh->clone();
1875 $new_dbh = $dbh->clone(\%attr);
1876 The "clone" method duplicates the $dbh connection by connecting
1878 with the same parameters ($dsn, $user, $password) as originally
1879 used.
1880 The attributes for the cloned connect are the same as those used
1882 for the original connect, with some other attribute merged over
1883 them depending on the \%attr parameter.
1884 If \%attr is given then the attributes it contains are merged into
1886 the original attributes and override any with the same names.
1887 Effectively the same as doing:
1888 %attribues_used = ( %original_attributes, %attr );
1890 If \%attr is not given then it defaults to a hash containing all
1892 the attributes in the attribute cache of $dbh excluding any non-
1893 code references, plus the main boolean attributes (RaiseError,
1894 PrintError, AutoCommit, etc.). This behaviour is subject to change.
1895 The clone method can be used even if the database handle is discon‐
1897 nected.
1898 The "clone" method was added in DBI 1.33. It is very new and likely
1900 to change.
1901 "data_sources"
1903 @ary = $dbh->data_sources();
1904 @ary = $dbh->data_sources(\%attr);
1905 Returns a list of data sources (databases) available via the $dbh
1907 driver's data_sources() method, plus any extra data sources that
1908 the driver can discover via the connected $dbh. Typically the extra
1909 data sources are other databases managed by the same server process
1910 that the $dbh is connected to.
1911 Data sources are returned in a form suitable for passing to the
1913 "connect" method (that is, they will include the ""dbi:$driver:""
1914 prefix).
1915 The data_sources() method, for a $dbh, was added in DBI 1.38.
1917 "do"
1919 $rows = $dbh->do($statement) or die $dbh->errstr;
1920 $rows = $dbh->do($statement, \%attr) or die $dbh->errstr;
1921 $rows = $dbh->do($statement, \%attr, @bind_values) or die ...
1922 Prepare and execute a single statement. Returns the number of rows
1924 affected or "undef" on error. A return value of "-1" means the num‐
1925 ber of rows is not known, not applicable, or not available.
1926 This method is typically most useful for non-"SELECT" statements
1928 that either cannot be prepared in advance (due to a limitation of
1929 the driver) or do not need to be executed repeatedly. It should not
1930 be used for "SELECT" statements because it does not return a state‐
1931 ment handle (so you can't fetch any data).
1932 The default "do" method is logically similar to:
1934 sub do {
1936 my($dbh, $statement, $attr, @bind_values) = @_;
1937 my $sth = $dbh->prepare($statement, $attr) or return undef;
1938 $sth->execute(@bind_values) or return undef;
1939 my $rows = $sth->rows;
1940 ($rows == 0) ? "0E0" : $rows; # always return true if no error
1941 }
1942 For example:
1944 my $rows_deleted = $dbh->do(q{
1946 DELETE FROM table
1947 WHERE status = ?
1948 }, undef, 'DONE') or die $dbh->errstr;
1949 Using placeholders and @bind_values with the "do" method can be
1951 useful because it avoids the need to correctly quote any variables
1952 in the $statement. But if you'll be executing the statement many
1953 times then it's more efficient to "prepare" it once and call "exe‐
1954 cute" many times instead.
1955 The "q{...}" style quoting used in this example avoids clashing
1957 with quotes that may be used in the SQL statement. Use the double-
1958 quote-like "qq{...}" operator if you want to interpolate variables
1959 into the string. See "Quote and Quote-like Operators" in perlop
1960 for more details.
1961 "last_insert_id"
1963 $rv = $dbh->last_insert_id($catalog, $schema, $table, $field);
1964 $rv = $dbh->last_insert_id($catalog, $schema, $table, $field, \%attr);
1965 Returns a value 'identifying' the row just inserted, if possible.
1967 Typically this would be a value assigned by the database server to
1968 a column with an auto_increment or serial type. Returns undef if
1969 the driver does not support the method or can't determine the
1970 value.
1971 The $catalog, $schema, $table, and $field parameters may be
1973 required for some drivers (see below). If you don't know the
1974 parameter values and your driver does not need them, then use
1975 "undef" for each.
1976 There are several caveats to be aware of with this method if you
1978 want to use it for portable applications:
1979 * For some drivers the value may only available immediately after
1981 the insert statement has executed (e.g., mysql, Informix).
1982 * For some drivers the $catalog, $schema, $table, and $field param‐
1984 eters are required, for others they are ignored (e.g., mysql).
1985 * Drivers may return an indeterminate value if no insert has been
1987 performed yet.
1988 * For some drivers the value may only be available if placeholders
1990 have not been used (e.g., Sybase, MS SQL). In this case the value
1991 returned would be from the last non-placeholder insert statement.
1992 * Some drivers may need driver-specific hints about how to get the
1994 value. For example, being told the name of the database 'sequence'
1995 object that holds the value. Any such hints are passed as driver-
1996 specific attributes in the \%attr parameter.
1997 * If the underlying database offers nothing better, then some driv‐
1999 ers may attempt to implement this method by executing ""select
2000 max($field) from $table"". Drivers using any approach like this
2001 should issue a warning if "AutoCommit" is true because it is gener‐
2002 ally unsafe - another process may have modified the table between
2003 your insert and the select. For situations where you know it is
2004 safe, such as when you have locked the table, you can silence the
2005 warning by passing "Warn" => 0 in \%attr.
2006 * If no insert has been performed yet, or the last insert failed,
2008 then the value is implementation defined.
2009 Given all the caveats above, it's clear that this method must be
2011 used with care.
2012 The "last_insert_id" method was added in DBI 1.38.
2014 "selectrow_array"
2016 @row_ary = $dbh->selectrow_array($statement);
2017 @row_ary = $dbh->selectrow_array($statement, \%attr);
2018 @row_ary = $dbh->selectrow_array($statement, \%attr, @bind_values);
2019 This utility method combines "prepare", "execute" and
2021 "fetchrow_array" into a single call. If called in a list context,
2022 it returns the first row of data from the statement. The $state‐
2023 ment parameter can be a previously prepared statement handle, in
2024 which case the "prepare" is skipped.
2025 If any method fails, and "RaiseError" is not set, "selectrow_array"
2027 will return an empty list.
2028 If called in a scalar context for a statement handle that has more
2030 than one column, it is undefined whether the driver will return the
2031 value of the first column or the last. So don't do that. Also, in
2032 a scalar context, an "undef" is returned if there are no more rows
2033 or if an error occurred. That "undef" can't be distinguished from
2034 an "undef" returned because the first field value was NULL. For
2035 these reasons you should exercise some caution if you use "selec‐
2036 trow_array" in a scalar context, or just don't do that.
2037 "selectrow_arrayref"
2039 $ary_ref = $dbh->selectrow_arrayref($statement);
2040 $ary_ref = $dbh->selectrow_arrayref($statement, \%attr);
2041 $ary_ref = $dbh->selectrow_arrayref($statement, \%attr, @bind_values);
2042 This utility method combines "prepare", "execute" and
2044 "fetchrow_arrayref" into a single call. It returns the first row of
2045 data from the statement. The $statement parameter can be a previ‐
2046 ously prepared statement handle, in which case the "prepare" is
2047 skipped.
2048 If any method fails, and "RaiseError" is not set, "selectrow_array"
2050 will return undef.
2051 "selectrow_hashref"
2053 $hash_ref = $dbh->selectrow_hashref($statement);
2054 $hash_ref = $dbh->selectrow_hashref($statement, \%attr);
2055 $hash_ref = $dbh->selectrow_hashref($statement, \%attr, @bind_values);
2056 This utility method combines "prepare", "execute" and
2058 "fetchrow_hashref" into a single call. It returns the first row of
2059 data from the statement. The $statement parameter can be a previ‐
2060 ously prepared statement handle, in which case the "prepare" is
2061 skipped.
2062 If any method fails, and "RaiseError" is not set, "selec‐
2064 trow_hashref" will return undef.
2065 "selectall_arrayref"
2067 $ary_ref = $dbh->selectall_arrayref($statement);
2068 $ary_ref = $dbh->selectall_arrayref($statement, \%attr);
2069 $ary_ref = $dbh->selectall_arrayref($statement, \%attr, @bind_values);
2070 This utility method combines "prepare", "execute" and
2072 "fetchall_arrayref" into a single call. It returns a reference to
2073 an array containing a reference to an array (or hash, see below)
2074 for each row of data fetched.
2075 The $statement parameter can be a previously prepared statement
2077 handle, in which case the "prepare" is skipped. This is recommended
2078 if the statement is going to be executed many times.
2079 If "RaiseError" is not set and any method except
2081 "fetchall_arrayref" fails then "selectall_arrayref" will return
2082 "undef"; if "fetchall_arrayref" fails then it will return with
2083 whatever data has been fetched thus far. You should check
2084 "$sth->err" afterwards (or use the "RaiseError" attribute) to dis‐
2085 cover if the data is complete or was truncated due to an error.
2086 The "fetchall_arrayref" method called by "selectall_arrayref" sup‐
2088 ports a $max_rows parameter. You can specify a value for $max_rows
2089 by including a '"MaxRows"' attribute in \%attr. In which case fin‐
2090 ish() is called for you after fetchall_arrayref() returns.
2091 The "fetchall_arrayref" method called by "selectall_arrayref" also
2093 supports a $slice parameter. You can specify a value for $slice by
2094 including a '"Slice"' or '"Columns"' attribute in \%attr. The only
2095 difference between the two is that if "Slice" is not defined and
2096 "Columns" is an array ref, then the array is assumed to contain
2097 column index values (which count from 1), rather than perl array
2098 index values. In which case the array is copied and each value
2099 decremented before passing to "/fetchall_arrayref".
2100 You may often want to fetch an array of rows where each row is
2102 stored as a hash. That can be done simple using:
2103 my $emps = $dbh->selectall_arrayref(
2105 "SELECT ename FROM emp ORDER BY ename",
2106 { Slice => {} }
2107 );
2108 foreach my $emp ( @$emps ) {
2109 print "Employee: $emp->{ename}\n";
2110 }
2111 Or, to fetch into an array instead of an array ref:
2113 @result = @{ $dbh->selectall_arrayref($sql, { Slice => {} }) };
2115 See "fetchall_arrayref" method for more details.
2117 "selectall_hashref"
2119 $hash_ref = $dbh->selectall_hashref($statement, $key_field);
2120 $hash_ref = $dbh->selectall_hashref($statement, $key_field, \%attr);
2121 $hash_ref = $dbh->selectall_hashref($statement, $key_field, \%attr, @bind_values);
2122 This utility method combines "prepare", "execute" and
2124 "fetchall_hashref" into a single call. It returns a reference to a
2125 hash containing one entry, at most, for each row, as returned by
2126 fetchall_hashref().
2127 The $statement parameter can be a previously prepared statement
2129 handle, in which case the "prepare" is skipped. This is recom‐
2130 mended if the statement is going to be executed many times.
2131 The $key_field parameter defines which column, or columns, are used
2133 as keys in the returned hash. It can either be the name of a single
2134 field, or a reference to an array containing multiple field names.
2135 Using multiple names yields a tree of nested hashes.
2136 If a row has the same key as an earlier row then it replaces the
2138 earlier row.
2139 If any method except "fetchrow_hashref" fails, and "RaiseError" is
2141 not set, "selectall_hashref" will return "undef". If
2142 "fetchrow_hashref" fails and "RaiseError" is not set, then it will
2143 return with whatever data it has fetched thus far. $DBI::err should
2144 be checked to catch that.
2145 See fetchall_hashref() for more details.
2147 "selectcol_arrayref"
2149 $ary_ref = $dbh->selectcol_arrayref($statement);
2150 $ary_ref = $dbh->selectcol_arrayref($statement, \%attr);
2151 $ary_ref = $dbh->selectcol_arrayref($statement, \%attr, @bind_values);
2152 This utility method combines "prepare", "execute", and fetching one
2154 column from all the rows, into a single call. It returns a refer‐
2155 ence to an array containing the values of the first column from
2156 each row.
2157 The $statement parameter can be a previously prepared statement
2159 handle, in which case the "prepare" is skipped. This is recommended
2160 if the statement is going to be executed many times.
2161 If any method except "fetch" fails, and "RaiseError" is not set,
2163 "selectcol_arrayref" will return "undef". If "fetch" fails and
2164 "RaiseError" is not set, then it will return with whatever data it
2165 has fetched thus far. $DBI::err should be checked to catch that.
2166 The "selectcol_arrayref" method defaults to pushing a single column
2168 value (the first) from each row into the result array. However, it
2169 can also push another column, or even multiple columns per row,
2170 into the result array. This behaviour can be specified via a '"Col‐
2171 umns"' attribute which must be a ref to an array containing the
2172 column number or numbers to use. For example:
2173 # get array of id and name pairs:
2175 my $ary_ref = $dbh->selectcol_arrayref("select id, name from table", { Columns=>[1,2] });
2176 my %hash = @$ary_ref; # build hash from key-value pairs so $hash{$id} => name
2177 You can specify a maximum number of rows to fetch by including a
2179 '"MaxRows"' attribute in \%attr.
2180 "prepare"
2182 $sth = $dbh->prepare($statement) or die $dbh->errstr;
2183 $sth = $dbh->prepare($statement, \%attr) or die $dbh->errstr;
2184 Prepares a statement for later execution by the database engine and
2186 returns a reference to a statement handle object.
2187 The returned statement handle can be used to get attributes of the
2189 statement and invoke the "execute" method. See "Statement Handle
2190 Methods".
2191 Drivers for engines without the concept of preparing a statement
2193 will typically just store the statement in the returned handle and
2194 process it when "$sth->execute" is called. Such drivers are
2195 unlikely to give much useful information about the statement, such
2196 as "$sth->{NUM_OF_FIELDS}", until after "$sth->execute" has been
2197 called. Portable applications should take this into account.
2198 In general, DBI drivers do not parse the contents of the statement
2200 (other than simply counting any "Placeholders"). The statement is
2201 passed directly to the database engine, sometimes known as pass-
2202 thru mode. This has advantages and disadvantages. On the plus side,
2203 you can access all the functionality of the engine being used. On
2204 the downside, you're limited if you're using a simple engine, and
2205 you need to take extra care if writing applications intended to be
2206 portable between engines.
2207 Portable applications should not assume that a new statement can be
2209 prepared and/or executed while still fetching results from a previ‐
2210 ous statement.
2211 Some command-line SQL tools use statement terminators, like a semi‐
2213 colon, to indicate the end of a statement. Such terminators should
2214 not normally be used with the DBI.
2215 "prepare_cached"
2217 $sth = $dbh->prepare_cached($statement)
2218 $sth = $dbh->prepare_cached($statement, \%attr)
2219 $sth = $dbh->prepare_cached($statement, \%attr, $if_active)
2220 Like "prepare" except that the statement handle returned will be
2222 stored in a hash associated with the $dbh. If another call is made
2223 to "prepare_cached" with the same $statement and %attr parameter
2224 values, then the corresponding cached $sth will be returned without
2225 contacting the database server.
2226 The $if_active parameter lets you adjust the behaviour if an
2228 already cached statement handle is still Active. There are several
2229 alternatives:
2230 0: A warning will be generated, and finish() will be called on the
2232 statement handle before it is returned. This is the default behav‐
2233 iour if $if_active is not passed.
2234 1: finish() will be called on the statement handle, but the warning
2235 is suppressed.
2236 2: Disables any checking.
2237 3: The existing active statement handle will be removed from the
2238 cache and a new statement handle prepared and cached in its place.
2239 This is the safest option because it doesn't affect the state of
2240 the old handle, it just removes it from the cache. [Added in DBI
2241 1.40]
2242 Here are some examples of "prepare_cached":
2244 sub insert_hash {
2246 my ($table, $field_values) = @_;
2247 # sort to keep field order, and thus sql, stable for prepare_cached
2248 my @fields = sort keys %$field_values;
2249 my @values = @{$field_values}{@fields};
2250 my $sql = sprintf "insert into %s (%s) values (%s)",
2251 $table, join(",", @fields), join(",", ("?")x@fields);
2252 my $sth = $dbh->prepare_cached($sql);
2253 return $sth->execute(@values);
2254 }
2255 sub search_hash {
2257 my ($table, $field_values) = @_;
2258 # sort to keep field order, and thus sql, stable for prepare_cached
2259 my @fields = sort keys %$field_values;
2260 my @values = @{$field_values}{@fields};
2261 my $qualifier = "";
2262 $qualifier = "where ".join(" and ", map { "$_=?" } @fields) if @fields;
2263 $sth = $dbh->prepare_cached("SELECT * FROM $table $qualifier");
2264 return $dbh->selectall_arrayref($sth, {}, @values);
2265 }
2266 Caveat emptor: This caching can be useful in some applications, but
2268 it can also cause problems and should be used with care. Here is a
2269 contrived case where caching would cause a significant problem:
2270 my $sth = $dbh->prepare_cached('SELECT * FROM foo WHERE bar=?');
2272 $sth->execute(...);
2273 while (my $data = $sth->fetchrow_hashref) {
2274 # later, in some other code called within the loop...
2276 my $sth2 = $dbh->prepare_cached('SELECT * FROM foo WHERE bar=?');
2277 $sth2->execute(...);
2278 while (my $data2 = $sth2->fetchrow_arrayref) {
2279 do_stuff(...);
2280 }
2281 }
2282 In this example, since both handles are preparing the exact same
2284 statement, $sth2 will not be its own statement handle, but a dupli‐
2285 cate of $sth returned from the cache. The results will certainly
2286 not be what you expect. Typically the the inner fetch loop will
2287 work normally, fetching all the records and terminating when there
2288 are no more, but now $sth is the same as $sth2 the outer fetch loop
2289 will also terminate.
2290 You'll know if you run into this problem because prepare_cached()
2292 will generate a warning by default (when $if_active is false).
2293 The cache used by prepare_cached() is keyed by both the statement
2295 and any attributes so you can also avoid this issue by doing some‐
2296 thing like:
2297 $sth = $dbh->prepare_cached("...", { dbi_dummy => __FILE__.__LINE__ });
2299 which will ensure that prepare_cached only returns statements
2301 cached by that line of code in that source file.
2302 If you'd like the cache to managed intelligently, you can tie the
2304 hashref returned by "CachedKids" to an appropriate caching module,
2305 such as Tie::Cache::LRU:
2306 my $cache = $dbh->{CachedKids};
2308 tie %$cache, 'Tie::Cache::LRU', 500;
2309 "commit"
2311 $rc = $dbh->commit or die $dbh->errstr;
2312 Commit (make permanent) the most recent series of database changes
2314 if the database supports transactions and AutoCommit is off.
2315 If "AutoCommit" is on, then calling "commit" will issue a "commit
2317 ineffective with AutoCommit" warning.
2318 See also "Transactions" in the "FURTHER INFORMATION" section below.
2320 "rollback"
2322 $rc = $dbh->rollback or die $dbh->errstr;
2323 Rollback (undo) the most recent series of uncommitted database
2325 changes if the database supports transactions and AutoCommit is
2326 off.
2327 If "AutoCommit" is on, then calling "rollback" will issue a "roll‐
2329 back ineffective with AutoCommit" warning.
2330 See also "Transactions" in the "FURTHER INFORMATION" section below.
2332 "begin_work"
2334 $rc = $dbh->begin_work or die $dbh->errstr;
2335 Enable transactions (by turning "AutoCommit" off) until the next
2337 call to "commit" or "rollback". After the next "commit" or "roll‐
2338 back", "AutoCommit" will automatically be turned on again.
2339 If "AutoCommit" is already off when "begin_work" is called then it
2341 does nothing except return an error. If the driver does not support
2342 transactions then when "begin_work" attempts to set "AutoCommit"
2343 off the driver will trigger a fatal error.
2344 See also "Transactions" in the "FURTHER INFORMATION" section below.
2346 "disconnect"
2348 $rc = $dbh->disconnect or warn $dbh->errstr;
2349 Disconnects the database from the database handle. "disconnect" is
2351 typically only used before exiting the program. The handle is of
2352 little use after disconnecting.
2353 The transaction behaviour of the "disconnect" method is, sadly,
2355 undefined. Some database systems (such as Oracle and Ingres) will
2356 automatically commit any outstanding changes, but others (such as
2357 Informix) will rollback any outstanding changes. Applications not
2358 using "AutoCommit" should explicitly call "commit" or "rollback"
2359 before calling "disconnect".
2360 The database is automatically disconnected by the "DESTROY" method
2362 if still connected when there are no longer any references to the
2363 handle. The "DESTROY" method for each driver should implicitly
2364 call "rollback" to undo any uncommitted changes. This is vital be‐
2365 haviour to ensure that incomplete transactions don't get committed
2366 simply because Perl calls "DESTROY" on every object before exiting.
2367 Also, do not rely on the order of object destruction during "global
2368 destruction", as it is undefined.
2369 Generally, if you want your changes to be commited or rolled back
2371 when you disconnect, then you should explicitly call "commit" or
2372 "rollback" before disconnecting.
2373 If you disconnect from a database while you still have active
2375 statement handles (e.g., SELECT statement handles that may have
2376 more data to fetch), you will get a warning. The warning may indi‐
2377 cate that a fetch loop terminated early, perhaps due to an uncaught
2378 error. To avoid the warning call the "finish" method on the active
2379 handles.
2380 "ping"
2382 $rc = $dbh->ping;
2383 Attempts to determine, in a reasonably efficient way, if the data‐
2385 base server is still running and the connection to it is still
2386 working. Individual drivers should implement this function in the
2387 most suitable manner for their database engine.
2388 The current default implementation always returns true without
2390 actually doing anything. Actually, it returns ""0 but true"" which
2391 is true but zero. That way you can tell if the return value is gen‐
2392 uine or just the default. Drivers should override this method with
2393 one that does the right thing for their type of database.
2394 Few applications would have direct use for this method. See the
2396 specialized Apache::DBI module for one example usage.
2397 "get_info"
2399 $value = $dbh->get_info( $info_type );
2400 Returns information about the implementation, i.e. driver and data
2402 source capabilities, restrictions etc. It returns "undef" for
2403 unknown or unimplemented information types. For example:
2404 $database_version = $dbh->get_info( 18 ); # SQL_DBMS_VER
2406 $max_select_tables = $dbh->get_info( 106 ); # SQL_MAXIMUM_TABLES_IN_SELECT
2407 See "Standards Reference Information" for more detailed information
2409 about the information types and their meanings and possible return
2410 values.
2411 The DBI::Const::GetInfoType module exports a %GetInfoType hash that
2413 can be used to map info type names to numbers. For example:
2414 $database_version = $dbh->get_info( $GetInfoType{SQL_DBMS_VER} );
2416 The names are a merging of the ANSI and ODBC standards (which dif‐
2418 fer in some cases). See DBI::Const::GetInfoType for more details.
2419 Because some DBI methods make use of get_info(), drivers are
2421 strongly encouraged to support at least the following very minimal
2422 set of information types to ensure the DBI itself works properly:
2423 Type Name Example A Example B
2425 ---- -------------------------- ------------ ----------------
2426 17 SQL_DBMS_NAME 'ACCESS' 'Oracle'
2427 18 SQL_DBMS_VER '03.50.0000' '08.01.0721 ...'
2428 29 SQL_IDENTIFIER_QUOTE_CHAR '`' '"'
2429 41 SQL_CATALOG_NAME_SEPARATOR '.' '@'
2430 114 SQL_CATALOG_LOCATION 1 2
2431 "table_info"
2433 $sth = $dbh->table_info( $catalog, $schema, $table, $type );
2434 $sth = $dbh->table_info( $catalog, $schema, $table, $type, \%attr );
2435 Returns an active statement handle that can be used to fetch infor‐
2437 mation about tables and views that exist in the database.
2438 The arguments $catalog, $schema and $table may accept search pat‐
2440 terns according to the database/driver, for example: $table =
2441 '%FOO%'; Remember that the underscore character ('"_"') is a search
2442 pattern that means match any character, so 'FOO_%' is the same as
2443 'FOO%' and 'FOO_BAR%' will match names like 'FOO1BAR'.
2444 The value of $type is a comma-separated list of one or more types
2446 of tables to be returned in the result set. Each value may option‐
2447 ally be quoted, e.g.:
2448 $type = "TABLE";
2450 $type = "'TABLE','VIEW'";
2451 In addition the following special cases may also be supported by
2453 some drivers:
2454 * If the value of $catalog is '%' and $schema and $table name are
2456 empty strings, the result set contains a list of catalog names. For
2457 example:
2458 $sth = $dbh->table_info('%', '', '');
2459 * If the value of $schema is '%' and $catalog and $table are empty
2461 strings, the result set contains a list of schema names.
2462 * If the value of $type is '%' and $catalog, $schema, and $table
2463 are all empty strings, the result set contains a list of table
2464 types.
2465 If your driver doesn't support one or more of the selection filter
2467 parameters then you may get back more than you asked for and can do
2468 the filtering yourself.
2469 This method can be expensive, and can return a large amount of
2471 data. (For example, small Oracle installation returns over 2000
2472 rows.) So it's a good idea to use the filters to limit the data as
2473 much as possible.
2474 The statement handle returned has at least the following fields in
2476 the order show below. Other fields, after these, may also be
2477 present.
2478 TABLE_CAT: Table catalog identifier. This field is NULL ("undef")
2480 if not applicable to the data source, which is usually the case.
2481 This field is empty if not applicable to the table.
2482 TABLE_SCHEM: The name of the schema containing the TABLE_NAME
2484 value. This field is NULL ("undef") if not applicable to data
2485 source, and empty if not applicable to the table.
2486 TABLE_NAME: Name of the table (or view, synonym, etc).
2488 TABLE_TYPE: One of the following: "TABLE", "VIEW", "SYSTEM TABLE",
2490 "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM" or a type
2491 identifier that is specific to the data source.
2492 REMARKS: A description of the table. May be NULL ("undef").
2494 Note that "table_info" might not return records for all tables.
2496 Applications can use any valid table regardless of whether it's
2497 returned by "table_info".
2498 See also "tables", "Catalog Methods" and "Standards Reference
2500 Information".
2501 "column_info"
2503 $sth = $dbh->column_info( $catalog, $schema, $table, $column );
2504 Returns an active statement handle that can be used to fetch infor‐
2506 mation about columns in specified tables.
2507 The arguments $schema, $table and $column may accept search pat‐
2509 terns according to the database/driver, for example: $table =
2510 '%FOO%';
2511 Note: The support for the selection criteria is driver specific. If
2513 the driver doesn't support one or more of them then you may get
2514 back more than you asked for and can do the filtering yourself.
2515 The statement handle returned has at least the following fields in
2517 the order shown below. Other fields, after these, may also be
2518 present.
2519 TABLE_CAT: The catalog identifier. This field is NULL ("undef") if
2521 not applicable to the data source, which is often the case. This
2522 field is empty if not applicable to the table.
2523 TABLE_SCHEM: The schema identifier. This field is NULL ("undef")
2525 if not applicable to the data source, and empty if not applicable
2526 to the table.
2527 TABLE_NAME: The table identifier. Note: A driver may provide col‐
2529 umn metadata not only for base tables, but also for derived objects
2530 like SYNONYMS etc.
2531 COLUMN_NAME: The column identifier.
2533 DATA_TYPE: The concise data type code.
2535 TYPE_NAME: A data source dependent data type name.
2537 COLUMN_SIZE: The column size. This is the maximum length in char‐
2539 acters for character data types, the number of digits or bits for
2540 numeric data types or the length in the representation of temporal
2541 types. See the relevant specifications for detailed information.
2542 BUFFER_LENGTH: The length in bytes of transferred data.
2544 DECIMAL_DIGITS: The total number of significant digits to the right
2546 of the decimal point.
2547 NUM_PREC_RADIX: The radix for numeric precision. The value is 10
2549 or 2 for numeric data types and NULL ("undef") if not applicable.
2550 NULLABLE: Indicates if a column can accept NULLs. The following
2552 values are defined:
2553 SQL_NO_NULLS 0
2555 SQL_NULLABLE 1
2556 SQL_NULLABLE_UNKNOWN 2
2557 REMARKS: A description of the column.
2559 COLUMN_DEF: The default value of the column.
2561 SQL_DATA_TYPE: The SQL data type.
2563 SQL_DATETIME_SUB: The subtype code for datetime and interval data
2565 types.
2566 CHAR_OCTET_LENGTH: The maximum length in bytes of a character or
2568 binary data type column.
2569 ORDINAL_POSITION: The column sequence number (starting with 1).
2571 IS_NULLABLE: Indicates if the column can accept NULLs. Possible
2573 values are: 'NO', 'YES' and ''.
2574 SQL/CLI defines the following additional columns:
2576 CHAR_SET_CAT
2578 CHAR_SET_SCHEM
2579 CHAR_SET_NAME
2580 COLLATION_CAT
2581 COLLATION_SCHEM
2582 COLLATION_NAME
2583 UDT_CAT
2584 UDT_SCHEM
2585 UDT_NAME
2586 DOMAIN_CAT
2587 DOMAIN_SCHEM
2588 DOMAIN_NAME
2589 SCOPE_CAT
2590 SCOPE_SCHEM
2591 SCOPE_NAME
2592 MAX_CARDINALITY
2593 DTD_IDENTIFIER
2594 IS_SELF_REF
2595 Drivers capable of supplying any of those values should do so in
2597 the corresponding column and supply undef values for the others.
2598 Drivers wishing to provide extra database/driver specific informa‐
2600 tion should do so in extra columns beyond all those listed above,
2601 and use lowercase field names with the driver-specific prefix
2602 (i.e., 'ora_...'). Applications accessing such fields should do so
2603 by name and not by column number.
2604 The result set is ordered by TABLE_CAT, TABLE_SCHEM, TABLE_NAME and
2606 ORDINAL_POSITION.
2607 Note: There is some overlap with statement attributes (in perl) and
2609 SQLDescribeCol (in ODBC). However, SQLColumns provides more meta‐
2610 data.
2611 See also "Catalog Methods" and "Standards Reference Information".
2613 "primary_key_info"
2615 $sth = $dbh->primary_key_info( $catalog, $schema, $table );
2616 Returns an active statement handle that can be used to fetch infor‐
2618 mation about columns that make up the primary key for a table. The
2619 arguments don't accept search patterns (unlike table_info()).
2620 For example:
2622 $sth = $dbh->primary_key_info( undef, $user, 'foo' );
2624 $data = $sth->fetchall_arrayref;
2625 The statement handle will return one row per column, ordered by TA‐
2627 BLE_CAT, TABLE_SCHEM, TABLE_NAME, and KEY_SEQ. If there is no pri‐
2628 mary key then the statement handle will fetch no rows.
2629 Note: The support for the selection criteria, such as $catalog, is
2631 driver specific. If the driver doesn't support catalogs and/or
2632 schemas, it may ignore these criteria.
2633 The statement handle returned has at least the following fields in
2635 the order shown below. Other fields, after these, may also be
2636 present.
2637 TABLE_CAT: The catalog identifier. This field is NULL ("undef") if
2639 not applicable to the data source, which is often the case. This
2640 field is empty if not applicable to the table.
2641 TABLE_SCHEM: The schema identifier. This field is NULL ("undef")
2643 if not applicable to the data source, and empty if not applicable
2644 to the table.
2645 TABLE_NAME: The table identifier.
2647 COLUMN_NAME: The column identifier.
2649 KEY_SEQ: The column sequence number (starting with 1). Note: This
2651 field is named ORDINAL_POSITION in SQL/CLI.
2652 PK_NAME: The primary key constraint identifier. This field is NULL
2654 ("undef") if not applicable to the data source.
2655 See also "Catalog Methods" and "Standards Reference Information".
2657 "primary_key"
2659 @key_column_names = $dbh->primary_key( $catalog, $schema, $table );
2660 Simple interface to the primary_key_info() method. Returns a list
2662 of the column names that comprise the primary key of the specified
2663 table. The list is in primary key column sequence order. If there
2664 is no primary key then an empty list is returned.
2665 "foreign_key_info"
2667 $sth = $dbh->foreign_key_info( $pk_catalog, $pk_schema, $pk_table
2668 , $fk_catalog, $fk_schema, $fk_table );
2669 $sth = $dbh->foreign_key_info( $pk_catalog, $pk_schema, $pk_table
2671 , $fk_catalog, $fk_schema, $fk_table
2672 , \%attr );
2673 Returns an active statement handle that can be used to fetch infor‐
2675 mation about foreign keys in and/or referencing the specified ta‐
2676 ble(s). The arguments don't accept search patterns (unlike ta‐
2677 ble_info()).
2678 $pk_catalog, $pk_schema, $pk_table identify the primary (unique)
2680 key table (PKT).
2681 $fk_catalog, $fk_schema, $fk_table identify the foreign key table
2683 (FKT).
2684 If both PKT and FKT are given, the function returns the foreign
2686 key, if any, in table FKT that refers to the primary (unique) key
2687 of table PKT. (Note: In SQL/CLI, the result is implementa‐
2688 tion-defined.)
2689 If only PKT is given, then the result set contains the primary key
2691 of that table and all foreign keys that refer to it.
2692 If only FKT is given, then the result set contains all foreign keys
2694 in that table and the primary keys to which they refer. (Note: In
2695 SQL/CLI, the result includes unique keys too.)
2696 For example:
2698 $sth = $dbh->foreign_key_info( undef, $user, 'master');
2700 $sth = $dbh->foreign_key_info( undef, undef, undef , undef, $user, 'detail');
2701 $sth = $dbh->foreign_key_info( undef, $user, 'master', undef, $user, 'detail');
2702 Note: The support for the selection criteria, such as $catalog, is
2704 driver specific. If the driver doesn't support catalogs and/or
2705 schemas, it may ignore these criteria.
2706 The statement handle returned has the following fields in the order
2708 shown below. Because ODBC never includes unique keys, they define
2709 different columns in the result set than SQL/CLI. SQL/CLI column
2710 names are shown in parentheses.
2711 PKTABLE_CAT ( UK_TABLE_CAT ): The primary (unique) key ta‐
2713 ble catalog identifier. This field is NULL ("undef") if not appli‐
2714 cable to the data source, which is often the case. This field is
2715 empty if not applicable to the table.
2716 PKTABLE_SCHEM ( UK_TABLE_SCHEM ): The primary (unique) key ta‐
2718 ble schema identifier. This field is NULL ("undef") if not appli‐
2719 cable to the data source, and empty if not applicable to the table.
2720 PKTABLE_NAME ( UK_TABLE_NAME ): The primary (unique) key ta‐
2722 ble identifier.
2723 PKCOLUMN_NAME (UK_COLUMN_NAME ): The primary (unique) key col‐
2725 umn identifier.
2726 FKTABLE_CAT ( FK_TABLE_CAT ): The foreign key table catalog
2728 identifier. This field is NULL ("undef") if not applicable to the
2729 data source, which is often the case. This field is empty if not
2730 applicable to the table.
2731 FKTABLE_SCHEM ( FK_TABLE_SCHEM ): The foreign key table schema
2733 identifier. This field is NULL ("undef") if not applicable to the
2734 data source, and empty if not applicable to the table.
2735 FKTABLE_NAME ( FK_TABLE_NAME ): The foreign key table identi‐
2737 fier.
2738 FKCOLUMN_NAME ( FK_COLUMN_NAME ): The foreign key column iden‐
2740 tifier.
2741 KEY_SEQ ( ORDINAL_POSITION ): The column sequence number
2743 (starting with 1).
2744 UPDATE_RULE ( UPDATE_RULE ): The referential action for
2746 the UPDATE rule. The following codes are defined:
2747 CASCADE 0
2749 RESTRICT 1
2750 SET NULL 2
2751 NO ACTION 3
2752 SET DEFAULT 4
2753 DELETE_RULE ( DELETE_RULE ): The referential action for
2755 the DELETE rule. The codes are the same as for UPDATE_RULE.
2756 FK_NAME ( FK_NAME ): The foreign key name.
2758 PK_NAME ( UK_NAME ): The primary (unique) key
2760 name.
2761 DEFERRABILITY ( DEFERABILITY ): The deferrability of the for‐
2763 eign key constraint. The following codes are defined:
2764 INITIALLY DEFERRED 5
2766 INITIALLY IMMEDIATE 6
2767 NOT DEFERRABLE 7
2768 ( UNIQUE_OR_PRIMARY ): This column is necessary if a
2770 driver includes all candidate (i.e. primary and alternate) keys in
2771 the result set (as specified by SQL/CLI). The value of this column
2772 is UNIQUE if the foreign key references an alternate key and PRI‐
2773 MARY if the foreign key references a primary key, or it may be
2774 undefined if the driver doesn't have access to the information.
2775 See also "Catalog Methods" and "Standards Reference Information".
2777 "statistics_info"
2779 Warning: This method is experimental and may change.
2780 $sth = $dbh->statistics_info( $catalog, $schema, $table, $unique_only, $quick );
2782 Returns an active statement handle that can be used to fetch sta‐
2784 tistical information about a table and its indexes.
2785 The arguments don't accept search patterns (unlike "table_info").
2787 If the boolean argument $unique_only is true, only UNIQUE indexes
2789 will be returned in the result set, otherwise all indexes will be
2790 returned.
2791 If the boolean argument $quick is set, the actual statistical
2793 information columns (CARDINALITY and PAGES) will only be returned
2794 if they are readily available from the server, and might not be
2795 current. Some databases may return stale statistics or no statis‐
2796 tics at all with this flag set.
2797 For example:
2799 $sth = $dbh->statistics_info( undef, $user, 'foo', 1, 1 );
2801 $data = $sth->fetchall_arrayref;
2802 The statement handle will return at most one row per column name
2804 per index, plus at most one row for the entire table itself,
2805 ordered by NON_UNIQUE, TYPE, INDEX_QUALIFIER, INDEX_NAME, and ORDI‐
2806 NAL_POSITION.
2807 Note: The support for the selection criteria, such as $catalog, is
2809 driver specific. If the driver doesn't support catalogs and/or
2810 schemas, it may ignore these criteria.
2811 The statement handle returned has at least the following fields in
2813 the order shown below. Other fields, after these, may also be
2814 present.
2815 TABLE_CAT: The catalog identifier. This field is NULL ("undef") if
2817 not applicable to the data source, which is often the case. This
2818 field is empty if not applicable to the table.
2819 TABLE_SCHEM: The schema identifier. This field is NULL ("undef")
2821 if not applicable to the data source, and empty if not applicable
2822 to the table.
2823 TABLE_NAME: The table identifier.
2825 NON_UNIQUE: Unique index indicator. Returns 0 for unique indexes,
2827 1 for non-unique indexes
2828 INDEX_QUALIFIER: Index qualifier identifier. The identifier that
2830 is used to qualify the index name when doing a "DROP INDEX"; NULL
2831 ("undef") is returned if an index qualifier is not supported by the
2832 data source. If a non-NULL (defined) value is returned in this
2833 column, it must be used to qualify the index name on a "DROP INDEX"
2834 statement; otherwise, the TABLE_SCHEM should be used to qualify the
2835 index name.
2836 INDEX_NAME: The index identifier.
2838 TYPE: The type of information being returned. Can be any of the
2840 following values: 'table', 'btree', 'clustered', 'content',
2841 'hashed', or 'other'.
2842 In the case that this field is 'table', all fields other than TA‐
2844 BLE_CAT, TABLE_SCHEM, TABLE_NAME, TYPE, CARDINALITY, and PAGES will
2845 be NULL ("undef").
2846 ORDINAL_POSITION: Column sequence number (starting with 1).
2848 COLUMN_NAME: The column identifier.
2850 ASC_OR_DESC: Column sort sequence. "A" for Ascending, "D" for
2852 Descending, or NULL ("undef") if not supported for this index.
2853 CARDINALITY: Cardinality of the table or index. For indexes, this
2855 is the number of unique values in the index. For tables, this is
2856 the number of rows in the table. If not supported, the value will
2857 be NULL ("undef").
2858 PAGES: Number of storage pages used by this table or index. If not
2860 supported, the value will be NULL ("undef").
2861 FILTER_CONDITION: The index filter condition as a string. If the
2863 index is not a filtered index, or it cannot be determined whether
2864 the index is a filtered index, this value is NULL ("undef"). If
2865 the index is a filtered index, but the filter condition cannot be
2866 determined, this value is the empty string ''. Otherwise it will
2867 be the literal filter condition as a string, such as "SALARY <=
2868 4500".
2869 See also "Catalog Methods" and "Standards Reference Information".
2871 "tables"
2873 @names = $dbh->tables( $catalog, $schema, $table, $type );
2874 @names = $dbh->tables; # deprecated
2875 Simple interface to table_info(). Returns a list of matching table
2877 names, possibly including a catalog/schema prefix.
2878 See "table_info" for a description of the parameters.
2880 If "$dbh->get_info(29)" returns true (29 is SQL_IDENTI‐
2882 FIER_QUOTE_CHAR) then the table names are constructed and quoted by
2883 "quote_identifier" to ensure they are usable even if they contain
2884 whitespace or reserved words etc. This means that the table names
2885 returned will include quote characters.
2886 "type_info_all"
2888 $type_info_all = $dbh->type_info_all;
2889 Returns a reference to an array which holds information about each
2891 data type variant supported by the database and driver. The array
2892 and its contents should be treated as read-only.
2893 The first item is a reference to an 'index' hash of "Name =">
2895 "Index" pairs. The items following that are references to arrays,
2896 one per supported data type variant. The leading index hash defines
2897 the names and order of the fields within the arrays that follow it.
2898 For example:
2899 $type_info_all = [
2901 { TYPE_NAME => 0,
2902 DATA_TYPE => 1,
2903 COLUMN_SIZE => 2, # was PRECISION originally
2904 LITERAL_PREFIX => 3,
2905 LITERAL_SUFFIX => 4,
2906 CREATE_PARAMS => 5,
2907 NULLABLE => 6,
2908 CASE_SENSITIVE => 7,
2909 SEARCHABLE => 8,
2910 UNSIGNED_ATTRIBUTE=> 9,
2911 FIXED_PREC_SCALE => 10, # was MONEY originally
2912 AUTO_UNIQUE_VALUE => 11, # was AUTO_INCREMENT originally
2913 LOCAL_TYPE_NAME => 12,
2914 MINIMUM_SCALE => 13,
2915 MAXIMUM_SCALE => 14,
2916 SQL_DATA_TYPE => 15,
2917 SQL_DATETIME_SUB => 16,
2918 NUM_PREC_RADIX => 17,
2919 INTERVAL_PRECISION=> 18,
2920 },
2921 [ 'VARCHAR', SQL_VARCHAR,
2922 undef, "'","'", undef,0, 1,1,0,0,0,undef,1,255, undef
2923 ],
2924 [ 'INTEGER', SQL_INTEGER,
2925 undef, "", "", undef,0, 0,1,0,0,0,undef,0, 0, 10
2926 ],
2927 ];
2928 More than one row may have the same value in the "DATA_TYPE" field
2930 if there are different ways to spell the type name and/or there are
2931 variants of the type with different attributes (e.g., with and
2932 without "AUTO_UNIQUE_VALUE" set, with and without
2933 "UNSIGNED_ATTRIBUTE", etc).
2934 The rows are ordered by "DATA_TYPE" first and then by how closely
2936 each type maps to the corresponding ODBC SQL data type, closest
2937 first.
2938 The meaning of the fields is described in the documentation for the
2940 "type_info" method.
2941 An 'index' hash is provided so you don't need to rely on index val‐
2943 ues defined above. However, using DBD::ODBC with some old ODBC
2944 drivers may return older names, shown as comments in the example
2945 above. Another issue with the index hash is that the lettercase of
2946 the keys is not defined. It is usually uppercase, as show here, but
2947 drivers may return names with any lettercase.
2948 Drivers are also free to return extra driver-specific columns of
2950 information - though it's recommended that they start at column
2951 index 50 to leave room for expansion of the DBI/ODBC specification.
2952 The type_info_all() method is not normally used directly. The
2954 "type_info" method provides a more usable and useful interface to
2955 the data.
2956 "type_info"
2958 @type_info = $dbh->type_info($data_type);
2959 Returns a list of hash references holding information about one or
2961 more variants of $data_type. The list is ordered by "DATA_TYPE"
2962 first and then by how closely each type maps to the corresponding
2963 ODBC SQL data type, closest first. If called in a scalar context
2964 then only the first (best) element is returned.
2965 If $data_type is undefined or "SQL_ALL_TYPES", then the list will
2967 contain hashes for all data type variants supported by the database
2968 and driver.
2969 If $data_type is an array reference then "type_info" returns the
2971 information for the first type in the array that has any matches.
2972 The keys of the hash follow the same letter case conventions as the
2974 rest of the DBI (see "Naming Conventions and Name Space"). The fol‐
2975 lowing uppercase items should always exist, though may be undef:
2976 TYPE_NAME (string)
2978 Data type name for use in CREATE TABLE statements etc.
2979 DATA_TYPE (integer)
2981 SQL data type number.
2982 COLUMN_SIZE (integer)
2984 For numeric types, this is either the total number of digits
2985 (if the NUM_PREC_RADIX value is 10) or the total number of bits
2986 allowed in the column (if NUM_PREC_RADIX is 2).
2987 For string types, this is the maximum size of the string in
2989 characters.
2990 For date and interval types, this is the maximum number of
2992 characters needed to display the value.
2993 LITERAL_PREFIX (string)
2995 Characters used to prefix a literal. A typical prefix is ""'""
2996 for characters, or possibly ""0x"" for binary values passed as
2997 hexadecimal. NULL ("undef") is returned for data types for
2998 which this is not applicable.
2999 LITERAL_SUFFIX (string)
3001 Characters used to suffix a literal. Typically ""'"" for char‐
3002 acters. NULL ("undef") is returned for data types where this
3003 is not applicable.
3004 CREATE_PARAMS (string)
3006 Parameter names for data type definition. For example, "CRE‐
3007 ATE_PARAMS" for a "DECIMAL" would be ""precision,scale"" if the
3008 DECIMAL type should be declared as "DECIMAL("precision,scale")"
3009 where precision and scale are integer values. For a "VARCHAR"
3010 it would be ""max length"". NULL ("undef") is returned for
3011 data types for which this is not applicable.
3012 NULLABLE (integer)
3014 Indicates whether the data type accepts a NULL value: 0 or an
3015 empty string = no, 1 = yes, 2 = unknown.
3016 CASE_SENSITIVE (boolean)
3018 Indicates whether the data type is case sensitive in collations
3019 and comparisons.
3020 SEARCHABLE (integer)
3022 Indicates how the data type can be used in a WHERE clause, as
3023 follows:
3024 0 - Cannot be used in a WHERE clause
3026 1 - Only with a LIKE predicate
3027 2 - All comparison operators except LIKE
3028 3 - Can be used in a WHERE clause with any comparison operator
3029 UNSIGNED_ATTRIBUTE (boolean)
3031 Indicates whether the data type is unsigned. NULL ("undef") is
3032 returned for data types for which this is not applicable.
3033 FIXED_PREC_SCALE (boolean)
3035 Indicates whether the data type always has the same precision
3036 and scale (such as a money type). NULL ("undef") is returned
3037 for data types for which this is not applicable.
3038 AUTO_UNIQUE_VALUE (boolean)
3040 Indicates whether a column of this data type is automatically
3041 set to a unique value whenever a new row is inserted. NULL
3042 ("undef") is returned for data types for which this is not
3043 applicable.
3044 LOCAL_TYPE_NAME (string)
3046 Localized version of the "TYPE_NAME" for use in dialog with
3047 users. NULL ("undef") is returned if a localized name is not
3048 available (in which case "TYPE_NAME" should be used).
3049 MINIMUM_SCALE (integer)
3051 The minimum scale of the data type. If a data type has a fixed
3052 scale, then "MAXIMUM_SCALE" holds the same value. NULL
3053 ("undef") is returned for data types for which this is not
3054 applicable.
3055 MAXIMUM_SCALE (integer)
3057 The maximum scale of the data type. If a data type has a fixed
3058 scale, then "MINIMUM_SCALE" holds the same value. NULL
3059 ("undef") is returned for data types for which this is not
3060 applicable.
3061 SQL_DATA_TYPE (integer)
3063 This column is the same as the "DATA_TYPE" column, except for
3064 interval and datetime data types. For interval and datetime
3065 data types, the "SQL_DATA_TYPE" field will return "SQL_INTER‐
3066 VAL" or "SQL_DATETIME", and the "SQL_DATETIME_SUB" field below
3067 will return the subcode for the specific interval or datetime
3068 data type. If this field is NULL, then the driver does not sup‐
3069 port or report on interval or datetime subtypes.
3070 SQL_DATETIME_SUB (integer)
3072 For interval or datetime data types, where the "SQL_DATA_TYPE"
3073 field above is "SQL_INTERVAL" or "SQL_DATETIME", this field
3074 will hold the subcode for the specific interval or datetime
3075 data type. Otherwise it will be NULL ("undef").
3076 Although not mentioned explicitly in the standards, it seems
3078 there is a simple relationship between these values:
3079 DATA_TYPE == (10 * SQL_DATA_TYPE) + SQL_DATETIME_SUB
3081 NUM_PREC_RADIX (integer)
3083 The radix value of the data type. For approximate numeric
3084 types, "NUM_PREC_RADIX" contains the value 2 and "COLUMN_SIZE"
3085 holds the number of bits. For exact numeric types,
3086 "NUM_PREC_RADIX" contains the value 10 and "COLUMN_SIZE" holds
3087 the number of decimal digits. NULL ("undef") is returned either
3088 for data types for which this is not applicable or if the
3089 driver cannot report this information.
3090 INTERVAL_PRECISION (integer)
3092 The interval leading precision for interval types. NULL is
3093 returned either for data types for which this is not applicable
3094 or if the driver cannot report this information.
3095 For example, to find the type name for the fields in a select
3097 statement you can do:
3098 @names = map { scalar $dbh->type_info($_)->{TYPE_NAME} } @{ $sth->{TYPE} }
3100 Since DBI and ODBC drivers vary in how they map their types into
3102 the ISO standard types you may need to search for more than one
3103 type. Here's an example looking for a usable type to store a date:
3104 $my_date_type = $dbh->type_info( [ SQL_DATE, SQL_TIMESTAMP ] );
3106 Similarly, to more reliably find a type to store small integers,
3108 you could use a list starting with "SQL_SMALLINT", "SQL_INTEGER",
3109 "SQL_DECIMAL", etc.
3110 See also "Standards Reference Information".
3112 "quote"
3114 $sql = $dbh->quote($value);
3115 $sql = $dbh->quote($value, $data_type);
3116 Quote a string literal for use as a literal value in an SQL state‐
3118 ment, by escaping any special characters (such as quotation marks)
3119 contained within the string and adding the required type of outer
3120 quotation marks.
3121 $sql = sprintf "SELECT foo FROM bar WHERE baz = %s",
3123 $dbh->quote("Don't");
3124 For most database types, quote would return 'Don''t' (including the
3126 outer quotation marks).
3127 An undefined $value value will be returned as the string "NULL"
3129 (without single quotation marks) to match how NULLs are represented
3130 in SQL.
3131 If $data_type is supplied, it is used to try to determine the
3133 required quoting behaviour by using the information returned by
3134 "type_info". As a special case, the standard numeric types are
3135 optimized to return $value without calling "type_info".
3136 Quote will probably not be able to deal with all possible input
3138 (such as binary data or data containing newlines), and is not
3139 related in any way with escaping or quoting shell meta-characters.
3140 It is valid for the quote() method to return an SQL expression that
3142 evaluates to the desired string. For example:
3143 $quoted = $dbh->quote("one\ntwo\0three")
3145 may return something like:
3147 CONCAT('one', CHAR(12), 'two', CHAR(0), 'three')
3149 The quote() method should not be used with "Placeholders and Bind
3151 Values".
3152 "quote_identifier"
3154 $sql = $dbh->quote_identifier( $name );
3155 $sql = $dbh->quote_identifier( $catalog, $schema, $table, \%attr );
3156 Quote an identifier (table name etc.) for use in an SQL statement,
3158 by escaping any special characters (such as double quotation marks)
3159 it contains and adding the required type of outer quotation marks.
3160 Undefined names are ignored and the remainder are quoted and then
3162 joined together, typically with a dot (".") character. For example:
3163 $id = $dbh->quote_identifier( undef, 'Her schema', 'My table' );
3165 would, for most database types, return "Her schema"."My table"
3167 (including all the double quotation marks).
3168 If three names are supplied then the first is assumed to be a cata‐
3170 log name and special rules may be applied based on what "get_info"
3171 returns for SQL_CATALOG_NAME_SEPARATOR (41) and SQL_CATALOG_LOCA‐
3172 TION (114). For example, for Oracle:
3173 $id = $dbh->quote_identifier( 'link', 'schema', 'table' );
3175 would return "schema"."table"@"link".
3177 "take_imp_data"
3179 $imp_data = $dbh->take_imp_data;
3180 Leaves the $dbh in an almost dead, zombie-like, state and returns a
3182 binary string of raw implementation data from the driver which
3183 describes the current database connection. Effectively it detaches
3184 the underlying database API connection data from the DBI handle.
3185 After calling take_imp_data(), all other methods except "DESTROY"
3186 will generate a warning and return undef.
3187 Why would you want to do this? You don't, forget I even mentioned
3189 it. Unless, that is, you're implementing something advanced like a
3190 multi-threaded connection pool. See DBI::Pool.
3191 The returned $imp_data can be passed as a "dbi_imp_data" attribute
3193 to a later connect() call, even in a separate thread in the same
3194 process, where the driver can use it to 'adopt' the existing con‐
3195 nection that the implementation data was taken from.
3196 Some things to keep in mind...
3198 * the $imp_data holds the only reference to the underlying database
3200 API connection data. That connection is still 'live' and won't be
3201 cleaned up properly unless the $imp_data is used to create a new
3202 $dbh which is then allowed to disconnect() normally.
3203 * using the same $imp_data to create more than one other new $dbh
3205 at a time may well lead to unpleasant problems. Don't do that.
3206 Any child statement handles are effectively destroyed when
3208 take_imp_data() is called.
3209 The "take_imp_data" method was added in DBI 1.36 but wasn't useful
3211 till 1.49.
3212 Database Handle Attributes
3214 This section describes attributes specific to database handles.
3216 Changes to these database handle attributes do not affect any other
3218 existing or future database handles.
3219 Attempting to set or get the value of an unknown attribute generates a
3221 warning, except for private driver-specific attributes (which all have
3222 names starting with a lowercase letter).
3223 Example:
3225 $h->{AutoCommit} = ...; # set/write
3227 ... = $h->{AutoCommit}; # get/read
3228 "AutoCommit" (boolean)
3230 If true, then database changes cannot be rolled-back (undone). If
3231 false, then database changes automatically occur within a "transac‐
3232 tion", which must either be committed or rolled back using the
3233 "commit" or "rollback" methods.
3234 Drivers should always default to "AutoCommit" mode (an unfortunate
3236 choice largely forced on the DBI by ODBC and JDBC conventions.)
3237 Attempting to set "AutoCommit" to an unsupported value is a fatal
3239 error. This is an important feature of the DBI. Applications that
3240 need full transaction behaviour can set "$dbh->{AutoCommit} = 0"
3241 (or set "AutoCommit" to 0 via "connect") without having to check
3242 that the value was assigned successfully.
3243 For the purposes of this description, we can divide databases into
3245 three categories:
3246 Databases which don't support transactions at all.
3248 Databases in which a transaction is always active.
3249 Databases in which a transaction must be explicitly started (C<'BEGIN WORK'>).
3250 * Databases which don't support transactions at all
3252 For these databases, attempting to turn "AutoCommit" off is a fatal
3254 error. "commit" and "rollback" both issue warnings about being
3255 ineffective while "AutoCommit" is in effect.
3256 * Databases in which a transaction is always active
3258 These are typically mainstream commercial relational databases with
3260 "ANSI standard" transaction behaviour. If "AutoCommit" is off,
3261 then changes to the database won't have any lasting effect unless
3262 "commit" is called (but see also "disconnect"). If "rollback" is
3263 called then any changes since the last commit are undone.
3264 If "AutoCommit" is on, then the effect is the same as if the DBI
3266 called "commit" automatically after every successful database oper‐
3267 ation. So calling "commit" or "rollback" explicitly while "AutoCom‐
3268 mit" is on would be ineffective because the changes would have
3269 already been commited.
3270 Changing "AutoCommit" from off to on will trigger a "commit".
3272 For databases which don't support a specific auto-commit mode, the
3274 driver has to commit each statement automatically using an explicit
3275 "COMMIT" after it completes successfully (and roll it back using an
3276 explicit "ROLLBACK" if it fails). The error information reported
3277 to the application will correspond to the statement which was exe‐
3278 cuted, unless it succeeded and the commit or rollback failed.
3279 * Databases in which a transaction must be explicitly started
3281 For these databases, the intention is to have them act like data‐
3283 bases in which a transaction is always active (as described above).
3284 To do this, the driver will automatically begin an explicit trans‐
3286 action when "AutoCommit" is turned off, or after a "commit" or
3287 "rollback" (or when the application issues the next database opera‐
3288 tion after one of those events).
3289 In this way, the application does not have to treat these databases
3291 as a special case.
3292 See "commit", "disconnect" and "Transactions" for other important
3294 notes about transactions.
3295 "Driver" (handle)
3297 Holds the handle of the parent driver. The only recommended use for
3298 this is to find the name of the driver using:
3299 $dbh->{Driver}->{Name}
3301 "Name" (string)
3303 Holds the "name" of the database. Usually (and recommended to be)
3304 the same as the ""dbi:DriverName:..."" string used to connect to
3305 the database, but with the leading ""dbi:DriverName:"" removed.
3306 "Statement" (string, read-only)
3308 Returns the statement string passed to the most recent "prepare"
3309 method called in this database handle, even if that method failed.
3310 This is especially useful where "RaiseError" is enabled and the
3311 exception handler checks $@ and sees that a 'prepare' method call
3312 failed.
3313 "RowCacheSize" (integer)
3315 A hint to the driver indicating the size of the local row cache
3316 that the application would like the driver to use for future
3317 "SELECT" statements. If a row cache is not implemented, then set‐
3318 ting "RowCacheSize" is ignored and getting the value returns
3319 "undef".
3320 Some "RowCacheSize" values have special meaning, as follows:
3322 0 - Automatically determine a reasonable cache size for each C<SELECT>
3324 1 - Disable the local row cache
3325 >1 - Cache this many rows
3326 <0 - Cache as many rows that will fit into this much memory for each C<SELECT>.
3327 Note that large cache sizes may require a very large amount of mem‐
3329 ory (cached rows * maximum size of row). Also, a large cache will
3330 cause a longer delay not only for the first fetch, but also when‐
3331 ever the cache needs refilling.
3332 See also the "RowsInCache" statement handle attribute.
3334 "Username" (string)
3336 Returns the username used to connect to the database.
3337
3339 This section lists the methods and attributes associated with DBI
3340 statement handles.
3341 Statement Handle Methods
3343 The DBI defines the following methods for use on DBI statement handles:
3345 "bind_param"
3347 $sth->bind_param($p_num, $bind_value)
3348 $sth->bind_param($p_num, $bind_value, \%attr)
3349 $sth->bind_param($p_num, $bind_value, $bind_type)
3350 The "bind_param" method takes a copy of $bind_value and associates
3352 it (binds it) with a placeholder, identified by $p_num, embedded in
3353 the prepared statement. Placeholders are indicated with question
3354 mark character ("?"). For example:
3355 $dbh->{RaiseError} = 1; # save having to check each method call
3357 $sth = $dbh->prepare("SELECT name, age FROM people WHERE name LIKE ?");
3358 $sth->bind_param(1, "John%"); # placeholders are numbered from 1
3359 $sth->execute;
3360 DBI::dump_results($sth);
3361 See "Placeholders and Bind Values" for more information.
3363 Data Types for Placeholders
3365 The "\%attr" parameter can be used to hint at the data type the
3367 placeholder should have. This is rarely needed. Typically, the
3368 driver is only interested in knowing if the placeholder should be
3369 bound as a number or a string.
3370 $sth->bind_param(1, $value, { TYPE => SQL_INTEGER });
3372 As a short-cut for the common case, the data type can be passed
3374 directly, in place of the "\%attr" hash reference. This example is
3375 equivalent to the one above:
3376 $sth->bind_param(1, $value, SQL_INTEGER);
3378 The "TYPE" value indicates the standard (non-driver-specific) type
3380 for this parameter. To specify the driver-specific type, the driver
3381 may support a driver-specific attribute, such as "{ ora_type => 97
3382 }".
3383 The SQL_INTEGER and other related constants can be imported using
3385 use DBI qw(:sql_types);
3387 See "DBI Constants" for more information.
3389 The data type for a placeholder cannot be changed after the first
3391 "bind_param" call. In fact the whole \%attr parameter is 'sticky'
3392 in the sense that a driver only needs to consider the \%attr param‐
3393 eter for the first call, for a given $sth and parameter. After that
3394 the driver may ignore the \%attr parameter for that placeholder.
3395 Perl only has string and number scalar data types. All database
3397 types that aren't numbers are bound as strings and must be in a
3398 format the database will understand except where the bind_param()
3399 TYPE attribute specifies a type that implies a particular format.
3400 For example, given:
3401 $sth->bind_param(1, $value, SQL_DATETIME);
3403 the driver should expect $value to be in the ODBC standard
3405 SQL_DATETIME format, which is 'YYYY-MM-DD HH:MM:SS'. Similarly for
3406 SQL_DATE, SQL_TIME etc.
3407 As an alternative to specifying the data type in the "bind_param"
3409 call, you can let the driver pass the value as the default type
3410 ("VARCHAR"). You can then use an SQL function to convert the type
3411 within the statement. For example:
3412 INSERT INTO price(code, price) VALUES (?, CONVERT(MONEY,?))
3414 The "CONVERT" function used here is just an example. The actual
3416 function and syntax will vary between different databases and is
3417 non-portable.
3418 See also "Placeholders and Bind Values" for more information.
3420 "bind_param_inout"
3422 $rc = $sth->bind_param_inout($p_num, \$bind_value, $max_len) or die $sth->errstr;
3423 $rv = $sth->bind_param_inout($p_num, \$bind_value, $max_len, \%attr) or ...
3424 $rv = $sth->bind_param_inout($p_num, \$bind_value, $max_len, $bind_type) or ...
3425 This method acts like "bind_param", but also enables values to be
3427 updated by the statement. The statement is typically a call to a
3428 stored procedure. The $bind_value must be passed as a reference to
3429 the actual value to be used.
3430 Note that unlike "bind_param", the $bind_value variable is not
3432 copied when "bind_param_inout" is called. Instead, the value in the
3433 variable is read at the time "execute" is called.
3434 The additional $max_len parameter specifies the minimum amount of
3436 memory to allocate to $bind_value for the new value. If the value
3437 returned from the database is too big to fit, then the execution
3438 should fail. If unsure what value to use, pick a generous length,
3439 i.e., a length larger than the longest value that would ever be
3440 returned. The only cost of using a larger value than needed is
3441 wasted memory.
3442 Undefined values or "undef" are used to indicate null values. See
3444 also "Placeholders and Bind Values" for more information.
3445 "bind_param_array"
3447 $rc = $sth->bind_param_array($p_num, $array_ref_or_value)
3448 $rc = $sth->bind_param_array($p_num, $array_ref_or_value, \%attr)
3449 $rc = $sth->bind_param_array($p_num, $array_ref_or_value, $bind_type)
3450 The "bind_param_array" method is used to bind an array of values to
3452 a placeholder embedded in the prepared statement which is to be
3453 executed with "execute_array". For example:
3454 $dbh->{RaiseError} = 1; # save having to check each method call
3456 $sth = $dbh->prepare("INSERT INTO staff (first_name, last_name, dept) VALUES(?, ?, ?)");
3457 $sth->bind_param_array(1, [ 'John', 'Mary', 'Tim' ]);
3458 $sth->bind_param_array(2, [ 'Booth', 'Todd', 'Robinson' ]);
3459 $sth->bind_param_array(3, "SALES"); # scalar will be reused for each row
3460 $sth->execute_array( { ArrayTupleStatus => \my @tuple_status } );
3461 The %attr ($bind_type) argument is the same as defined for
3463 "bind_param". Refer to "bind_param" for general details on using
3464 placeholders.
3465 (Note that bind_param_array() can not be used to expand a place‐
3467 holder into a list of values for a statement like "SELECT foo WHERE
3468 bar IN (?)". A placeholder can only ever represent one value per
3469 execution.)
3470 Scalar values, including "undef", may also be bound by
3472 "bind_param_array". In which case the same value will be used for
3473 each "execute" call. Driver-specific implementations may behave
3474 differently, e.g., when binding to a stored procedure call, some
3475 databases may permit mixing scalars and arrays as arguments.
3476 The default implementation provided by DBI (for drivers that have
3478 not implemented array binding) is to iteratively call "execute" for
3479 each parameter tuple provided in the bound arrays. Drivers may
3480 provide more optimized implementations using whatever bulk opera‐
3481 tion support the database API provides. The default driver behav‐
3482 iour should match the default DBI behaviour, but always consult
3483 your driver documentation as there may be driver specific issues to
3484 consider.
3485 Note that the default implementation currently only supports non-
3487 data returning statements (INSERT, UPDATE, but not SELECT). Also,
3488 "bind_param_array" and "bind_param" cannot be mixed in the same
3489 statement execution, and "bind_param_array" must be used with "exe‐
3490 cute_array"; using "bind_param_array" will have no effect for "exe‐
3491 cute".
3492 The "bind_param_array" method was added in DBI 1.22.
3494 "execute"
3496 $rv = $sth->execute or die $sth->errstr;
3497 $rv = $sth->execute(@bind_values) or die $sth->errstr;
3498 Perform whatever processing is necessary to execute the prepared
3500 statement. An "undef" is returned if an error occurs. A success‐
3501 ful "execute" always returns true regardless of the number of rows
3502 affected, even if it's zero (see below). It is always important to
3503 check the return status of "execute" (and most other DBI methods)
3504 for errors if you're not using "RaiseError".
3505 For a non-"SELECT" statement, "execute" returns the number of rows
3507 affected, if known. If no rows were affected, then "execute"
3508 returns "0E0", which Perl will treat as 0 but will regard as true.
3509 Note that it is not an error for no rows to be affected by a state‐
3510 ment. If the number of rows affected is not known, then "execute"
3511 returns -1.
3512 For "SELECT" statements, execute simply "starts" the query within
3514 the database engine. Use one of the fetch methods to retrieve the
3515 data after calling "execute". The "execute" method does not return
3516 the number of rows that will be returned by the query (because most
3517 databases can't tell in advance), it simply returns a true value.
3518 You can tell if the statement was a "SELECT" statement by checking
3520 if "$sth->{NUM_OF_FIELDS}" is greater than zero after calling "exe‐
3521 cute".
3522 If any arguments are given, then "execute" will effectively call
3524 "bind_param" for each value before executing the statement. Values
3525 bound in this way are usually treated as "SQL_VARCHAR" types unless
3526 the driver can determine the correct type (which is rare), or
3527 unless "bind_param" (or "bind_param_inout") has already been used
3528 to specify the type.
3529 If execute() is called on a statement handle that's still active
3531 ($sth->{Active} is true) then it should effectively call finish()
3532 to tidy up the previous execution results before starting this new
3533 execution.
3534 "execute_array"
3536 $tuples = $sth->execute_array(\%attr) or die $sth->errstr;
3537 $tuples = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
3538 ($tuples, $rows) = $sth->execute_array(\%attr) or die $sth->errstr;
3540 ($tuples, $rows) = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
3541 Execute the prepared statement once for each parameter tuple (group
3543 of values) provided either in the @bind_values, or by prior calls
3544 to "bind_param_array", or via a reference passed in \%attr.
3545 When called in scalar context the execute_array() method returns
3547 the number of tuples executed, or "undef" if an error occured.
3548 Like execute(), a successful execute_array() always returns true
3549 regardless of the number of tuples executed, even if it's zero. If
3550 there were any errors the ArrayTupleStatus array can be used to
3551 discover which tuples failed and with what errors.
3552 When called in list context the execute_array() method returns two
3554 scalars; $tuples is the same as calling execute_array() in scalar
3555 context and $rows is the sum of the number of rows affected for
3556 each tuple, if available or -1 if the driver cannot determine this.
3557 If you are doing an update operation the returned rows affected may
3558 not be what you expect if, for instance, one or more of the tuples
3559 affected the same row multiple times. Some drivers may not yet
3560 support list context, in which case $rows will be undef, or may not
3561 be able to provide the number of rows affected when performing this
3562 batch operation, in which case $rows will be -1.
3563 Bind values for the tuples to be executed may be supplied row-wise
3565 by an "ArrayTupleFetch" attribute, or else column-wise in the
3566 @bind_values argument, or else column-wise by prior calls to
3567 "bind_param_array".
3568 Where column-wise binding is used (via the @bind_values argument or
3570 calls to bind_param_array()) the maximum number of elements in any
3571 one of the bound value arrays determines the number of tuples exe‐
3572 cuted. Placeholders with fewer values in their parameter arrays are
3573 treated as if padded with undef (NULL) values.
3574 If a scalar value is bound, instead of an array reference, it is
3576 treated as a variable length array with all elements having the
3577 same value. It's does not influence the number of tuples executed,
3578 so if all bound arrays have zero elements then zero tuples will be
3579 executed. If all bound values are scalars then one tuple will be
3580 executed, making execute_array() act just like execute().
3581 The "ArrayTupleFetch" attribute can be used to specify a reference
3583 to a subroutine that will be called to provide the bind values for
3584 each tuple execution. The subroutine should return an reference to
3585 an array which contains the appropriate number of bind values, or
3586 return an undef if there is no more data to execute.
3587 As a convienience, the "ArrayTupleFetch" attribute can also be used
3589 to specify a statement handle. In which case the
3590 fetchrow_arrayref() method will be called on the given statement
3591 handle in order to provide the bind values for each tuple execu‐
3592 tion.
3593 The values specified via bind_param_array() or the @bind_values
3595 parameter may be either scalars, or arrayrefs. If any @bind_values
3596 are given, then "execute_array" will effectively call
3597 "bind_param_array" for each value before executing the statement.
3598 Values bound in this way are usually treated as "SQL_VARCHAR" types
3599 unless the driver can determine the correct type (which is rare),
3600 or unless "bind_param", "bind_param_inout", "bind_param_array", or
3601 "bind_param_inout_array" has already been used to specify the type.
3602 See "bind_param_array" for details.
3603 The "ArrayTupleStatus" attribute can be used to specify a reference
3605 to an array which will receive the execute status of each executed
3606 parameter tuple. Note the "ArrayTupleStatus" attribute was manda‐
3607 tory until DBI 1.38.
3608 For tuples which are successfully executed, the element at the same
3610 ordinal position in the status array is the resulting rowcount. If
3611 the execution of a tuple causes an error, then the corresponding
3612 status array element will be set to a reference to an array con‐
3613 taining the error code and error string set by the failed execu‐
3614 tion.
3615 If any tuple execution returns an error, "execute_array" will
3617 return "undef". In that case, the application should inspect the
3618 status array to determine which parameter tuples failed. Some
3619 databases may not continue executing tuples beyond the first fail‐
3620 ure. In this case the status array will either hold fewer elements,
3621 or the elements beyond the failure will be undef.
3622 If all parameter tuples are successfully executed, "execute_array"
3624 returns the number tuples executed. If no tuples were executed,
3625 then execute_array() returns "0E0", just like execute() does, which
3626 Perl will treat as 0 but will regard as true.
3627 For example:
3629 $sth = $dbh->prepare("INSERT INTO staff (first_name, last_name) VALUES (?, ?)");
3631 my $tuples = $sth->execute_array(
3632 { ArrayTupleStatus => \my @tuple_status },
3633 \@first_names,
3634 \@last_names,
3635 );
3636 if ($tuples) {
3637 print "Successfully inserted $tuples records\n";
3638 }
3639 else {
3640 for my $tuple (0..@last_names-1) {
3641 my $status = $tuple_status[$tuple];
3642 $status = [0, "Skipped"] unless defined $status;
3643 next unless ref $status;
3644 printf "Failed to insert (%s, %s): %s\n",
3645 $first_names[$tuple], $last_names[$tuple], $status->[1];
3646 }
3647 }
3648 Support for data returning statements such as SELECT is driver-spe‐
3650 cific and subject to change. At present, the default implementation
3651 provided by DBI only supports non-data returning statements.
3652 Transaction semantics when using array binding are driver and data‐
3654 base specific. If "AutoCommit" is on, the default DBI implementa‐
3655 tion will cause each parameter tuple to be inidividually committed
3656 (or rolled back in the event of an error). If "AutoCommit" is off,
3657 the application is responsible for explicitly committing the entire
3658 set of bound parameter tuples. Note that different drivers and
3659 databases may have different behaviours when some parameter tuples
3660 cause failures. In some cases, the driver or database may automati‐
3661 cally rollback the effect of all prior parameter tuples that suc‐
3662 ceeded in the transaction; other drivers or databases may retain
3663 the effect of prior successfully executed parameter tuples. Be sure
3664 to check your driver and database for its specific behaviour.
3665 Note that, in general, performance will usually be better with
3667 "AutoCommit" turned off, and using explicit "commit" after each
3668 "execute_array" call.
3669 The "execute_array" method was added in DBI 1.22, and ArrayTuple‐
3671 Fetch was added in 1.36.
3672 "execute_for_fetch"
3674 $tuples = $sth->execute_for_fetch($fetch_tuple_sub);
3675 $tuples = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
3676 ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub);
3678 ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
3679 The execute_for_fetch() method is used to perform bulk operations
3681 and is most often used via the execute_array() method, not
3682 directly.
3683 The fetch subroutine, referenced by $fetch_tuple_sub, is expected
3685 to return a reference to an array (known as a 'tuple') or undef.
3686 The execute_for_fetch() method calls $fetch_tuple_sub, without any
3688 parameters, until it returns a false value. Each tuple returned is
3689 used to provide bind values for an $sth->execute(@$tuple) call.
3690 In scalar context execute_for_fetch() returns "undef" if there were
3692 any errors and the number of tuples executed otherwise. Like exe‐
3693 cute() and execute_array() a zero is returned as "0E0" so exe‐
3694 cute_for_fetch() is only false on error. If there were any errors
3695 the @tuple_status array can be used to discover which tuples failed
3696 and with what errors.
3697 When called in list context execute_for_fetch() returns two
3699 scalars; $tuples is the same as calling execute_for_fetch() in
3700 scalar context and $rows is the sum of the number of rows affected
3701 for each tuple, if available or -1 if the driver cannot determine
3702 this. If you are doing an update operation the returned rows
3703 affected may not be what you expect if, for instance, one or more
3704 of the tuples affected the same row multiple times. Some drivers
3705 may not yet support list context, in which case $rows will be
3706 undef, or may not be able to provide the number of rows affected
3707 when performing this batch operation, in which case $rows will be
3708 -1.
3709 If \@tuple_status is passed then the execute_for_fetch method uses
3711 it to return status information. The tuple_status array holds one
3712 element per tuple. If the corresponding execute() did not fail then
3713 the element holds the return value from execute(), which is typi‐
3714 cally a row count. If the execute() did fail then the element holds
3715 a reference to an array containing ($sth->err, $sth->errstr,
3716 $sth->state).
3717 If the driver detects an error that it knows means no further
3719 tuples can be executed then it may return, with an error status,
3720 even though $fetch_tuple_sub may still have more tuples to be exe‐
3721 cuted.
3722 Although each tuple returned by $fetch_tuple_sub is effectively
3724 used to call $sth->execute(@$tuple_array_ref) the exact timing may
3725 vary. Drivers are free to accumulate sets of tuples to pass to the
3726 database server in bulk group operations for more efficient execu‐
3727 tion. However, the $fetch_tuple_sub is specifically allowed to
3728 return the same array reference each time (which is what
3729 fetchrow_arrayref() usually does).
3730 For example:
3732 my $sel = $dbh1->prepare("select foo, bar from table1");
3734 $sel->execute;
3735 my $ins = $dbh2->prepare("insert into table2 (foo, bar) values (?,?)");
3737 my $fetch_tuple_sub = sub { $sel->fetchrow_arrayref };
3738 my @tuple_status;
3740 $rc = $ins->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
3741 my @errors = grep { ref $_ } @tuple_status;
3742 Similarly, if you already have an array containing the data rows to
3744 be processed you'd use a subroutine to shift off and return each
3745 array ref in turn:
3746 $ins->execute_for_fetch( sub { shift @array_of_arrays }, \@tuple_status);
3748 The "execute_for_fetch" method was added in DBI 1.38.
3750 "fetchrow_arrayref"
3752 $ary_ref = $sth->fetchrow_arrayref;
3753 $ary_ref = $sth->fetch; # alias
3754 Fetches the next row of data and returns a reference to an array
3756 holding the field values. Null fields are returned as "undef" val‐
3757 ues in the array. This is the fastest way to fetch data, particu‐
3758 larly if used with "$sth->bind_columns".
3759 If there are no more rows or if an error occurs, then
3761 "fetchrow_arrayref" returns an "undef". You should check
3762 "$sth->err" afterwards (or use the "RaiseError" attribute) to dis‐
3763 cover if the "undef" returned was due to an error.
3764 Note that the same array reference is returned for each fetch, so
3766 don't store the reference and then use it after a later fetch.
3767 Also, the elements of the array are also reused for each row, so
3768 take care if you want to take a reference to an element. See also
3769 "bind_columns".
3770 "fetchrow_array"
3772 @ary = $sth->fetchrow_array;
3773 An alternative to "fetchrow_arrayref". Fetches the next row of data
3775 and returns it as a list containing the field values. Null fields
3776 are returned as "undef" values in the list.
3777 If there are no more rows or if an error occurs, then
3779 "fetchrow_array" returns an empty list. You should check
3780 "$sth->err" afterwards (or use the "RaiseError" attribute) to dis‐
3781 cover if the empty list returned was due to an error.
3782 If called in a scalar context for a statement handle that has more
3784 than one column, it is undefined whether the driver will return the
3785 value of the first column or the last. So don't do that. Also, in
3786 a scalar context, an "undef" is returned if there are no more rows
3787 or if an error occurred. That "undef" can't be distinguished from
3788 an "undef" returned because the first field value was NULL. For
3789 these reasons you should exercise some caution if you use
3790 "fetchrow_array" in a scalar context.
3791 "fetchrow_hashref"
3793 $hash_ref = $sth->fetchrow_hashref;
3794 $hash_ref = $sth->fetchrow_hashref($name);
3795 An alternative to "fetchrow_arrayref". Fetches the next row of data
3797 and returns it as a reference to a hash containing field name and
3798 field value pairs. Null fields are returned as "undef" values in
3799 the hash.
3800 If there are no more rows or if an error occurs, then
3802 "fetchrow_hashref" returns an "undef". You should check "$sth->err"
3803 afterwards (or use the "RaiseError" attribute) to discover if the
3804 "undef" returned was due to an error.
3805 The optional $name parameter specifies the name of the statement
3807 handle attribute. For historical reasons it defaults to ""NAME"",
3808 however using either ""NAME_lc"" or ""NAME_uc"" is recomended for
3809 portability.
3810 The keys of the hash are the same names returned by
3812 "$sth->{$name}". If more than one field has the same name, there
3813 will only be one entry in the returned hash for those fields.
3814 Because of the extra work "fetchrow_hashref" and Perl have to per‐
3816 form, it is not as efficient as "fetchrow_arrayref" or
3817 "fetchrow_array".
3818 By default a reference to a new hash is returned for each row. It
3820 is likely that a future version of the DBI will support an
3821 attribute which will enable the same hash to be reused for each
3822 row. This will give a significant performance boost, but it won't
3823 be enabled by default because of the risk of breaking old code.
3824 "fetchall_arrayref"
3826 $tbl_ary_ref = $sth->fetchall_arrayref;
3827 $tbl_ary_ref = $sth->fetchall_arrayref( $slice );
3828 $tbl_ary_ref = $sth->fetchall_arrayref( $slice, $max_rows );
3829 The "fetchall_arrayref" method can be used to fetch all the data to
3831 be returned from a prepared and executed statement handle. It
3832 returns a reference to an array that contains one reference per
3833 row.
3834 If there are no rows to return, "fetchall_arrayref" returns a ref‐
3836 erence to an empty array. If an error occurs, "fetchall_arrayref"
3837 returns the data fetched thus far, which may be none. You should
3838 check "$sth->err" afterwards (or use the "RaiseError" attribute) to
3839 discover if the data is complete or was truncated due to an error.
3840 If $slice is an array reference, "fetchall_arrayref" uses
3842 "fetchrow_arrayref" to fetch each row as an array ref. If the
3843 $slice array is not empty then it is used as a slice to select
3844 individual columns by perl array index number (starting at 0,
3845 unlike column and parameter numbers which start at 1).
3846 With no parameters, or if $slice is undefined, "fetchall_arrayref"
3848 acts as if passed an empty array ref.
3849 If $slice is a hash reference, "fetchall_arrayref" uses
3851 "fetchrow_hashref" to fetch each row as a hash reference. If the
3852 $slice hash is empty then fetchrow_hashref() is simply called in a
3853 tight loop and the keys in the hashes have whatever name lettercase
3854 is returned by default from fetchrow_hashref. (See "FetchHashKey‐
3855 Name" attribute.) If the $slice hash is not empty, then it is used
3856 as a slice to select individual columns by name. The values of the
3857 hash should be set to 1. The key names of the returned hashes
3858 match the letter case of the names in the parameter hash, regard‐
3859 less of the "FetchHashKeyName" attribute.
3860 For example, to fetch just the first column of every row:
3862 $tbl_ary_ref = $sth->fetchall_arrayref([0]);
3864 To fetch the second to last and last column of every row:
3866 $tbl_ary_ref = $sth->fetchall_arrayref([-2,-1]);
3868 To fetch all fields of every row as a hash ref:
3870 $tbl_ary_ref = $sth->fetchall_arrayref({});
3872 To fetch only the fields called "foo" and "bar" of every row as a
3874 hash ref (with keys named "foo" and "BAR"):
3875 $tbl_ary_ref = $sth->fetchall_arrayref({ foo=>1, BAR=>1 });
3877 The first two examples return a reference to an array of array
3879 refs. The third and forth return a reference to an array of hash
3880 refs.
3881 If $max_rows is defined and greater than or equal to zero then it
3883 is used to limit the number of rows fetched before returning.
3884 fetchall_arrayref() can then be called again to fetch more rows.
3885 This is especially useful when you need the better performance of
3886 fetchall_arrayref() but don't have enough memory to fetch and
3887 return all the rows in one go. Here's an example:
3888 my $rows = []; # cache for batches of rows
3890 while( my $row = ( shift(@$rows) ⎪⎪ # get row from cache, or reload cache:
3891 shift(@{$rows=$sth->fetchall_arrayref(undef,10_000)⎪⎪[]}) )
3892 ) {
3893 ...
3894 }
3895 That can be the fastest way to fetch and process lots of rows using
3897 the DBI, but it depends on the relative cost of method calls vs
3898 memory allocation.
3899 A standard "while" loop with column binding is often faster because
3901 the cost of allocating memory for the batch of rows is greater than
3902 the saving by reducing method calls. It's possible that the DBI may
3903 provide a way to reuse the memory of a previous batch in future,
3904 which would then shift the balance back towards
3905 fetchall_arrayref().
3906 "fetchall_hashref"
3908 $hash_ref = $sth->fetchall_hashref($key_field);
3909 The "fetchall_hashref" method can be used to fetch all the data to
3911 be returned from a prepared and executed statement handle. It
3912 returns a reference to a hash containing a key for each distinct
3913 value of the $key_field column that was fetched. For each key the
3914 corresponding value is a reference to a hash containing all the
3915 selected columns and their values, as returned by
3916 fetchrow_hashref().
3917 If there are no rows to return, "fetchall_hashref" returns a refer‐
3919 ence to an empty hash. If an error occurs, "fetchall_hashref"
3920 returns the data fetched thus far, which may be none. You should
3921 check "$sth->err" afterwards (or use the "RaiseError" attribute) to
3922 discover if the data is complete or was truncated due to an error.
3923 The $key_field parameter provides the name of the field that holds
3925 the value to be used for the key for the returned hash. For exam‐
3926 ple:
3927 $dbh->{FetchHashKeyName} = 'NAME_lc';
3929 $sth = $dbh->prepare("SELECT FOO, BAR, ID, NAME, BAZ FROM TABLE");
3930 $sth->execute;
3931 $hash_ref = $sth->fetchall_hashref('id');
3932 print "Name for id 42 is $hash_ref->{42}->{name}\n";
3933 The $key_field parameter can also be specified as an integer column
3935 number (counting from 1). If $key_field doesn't match any column
3936 in the statement, as a name first then as a number, then an error
3937 is returned.
3938 For queries returing more than one 'key' column, you can specify
3940 multiple column names by passing $key_field as a reference to an
3941 array containing one or more key column names (or index numbers).
3942 For example:
3943 $sth = $dbh->prepare("SELECT foo, bar, baz FROM table");
3945 $sth->execute;
3946 $hash_ref = $sth->fetchall_hashref( [ qw(foo bar) ] );
3947 print "For foo 42 and bar 38, baz is $hash_ref->{42}->{38}->{baz}\n";
3948 The fetchall_hashref() method is normally used only where the key
3950 fields values for each row are unique. If multiple rows are
3951 returned with the same values for the key fields then later rows
3952 overwrite earlier ones.
3953 "finish"
3955 $rc = $sth->finish;
3956 Indicate that no more data will be fetched from this statement han‐
3958 dle before it is either executed again or destroyed. The "finish"
3959 method is rarely needed, and frequently overused, but can sometimes
3960 be helpful in a few very specific situations to allow the server to
3961 free up resources (such as sort buffers).
3962 When all the data has been fetched from a "SELECT" statement, the
3964 driver should automatically call "finish" for you. So you should
3965 not normally need to call it explicitly except when you know that
3966 you've not fetched all the data from a statement handle. The most
3967 common example is when you only want to fetch one row, but in that
3968 case the "selectrow_*" methods are usually better anyway. Adding
3969 calls to "finish" after each fetch loop is a common mistake, don't
3970 do it, it can mask genuine problems like uncaught fetch errors.
3971 Consider a query like:
3973 SELECT foo FROM table WHERE bar=? ORDER BY foo
3975 where you want to select just the first (smallest) "foo" value from
3977 a very large table. When executed, the database server will have to
3978 use temporary buffer space to store the sorted rows. If, after exe‐
3979 cuting the handle and selecting one row, the handle won't be re-
3980 executed for some time and won't be destroyed, the "finish" method
3981 can be used to tell the server that the buffer space can be freed.
3982 Calling "finish" resets the "Active" attribute for the statement.
3984 It may also make some statement handle attributes (such as "NAME"
3985 and "TYPE") unavailable if they have not already been accessed (and
3986 thus cached).
3987 The "finish" method does not affect the transaction status of the
3989 database connection. It has nothing to do with transactions. It's
3990 mostly an internal "housekeeping" method that is rarely needed.
3991 See also "disconnect" and the "Active" attribute.
3992 The "finish" method should have been called "discard_pending_rows".
3994 "rows"
3996 $rv = $sth->rows;
3997 Returns the number of rows affected by the last row affecting com‐
3999 mand, or -1 if the number of rows is not known or not available.
4000 Generally, you can only rely on a row count after a non-"SELECT"
4002 "execute" (for some specific operations like "UPDATE" and
4003 "DELETE"), or after fetching all the rows of a "SELECT" statement.
4004 For "SELECT" statements, it is generally not possible to know how
4006 many rows will be returned except by fetching them all. Some driv‐
4007 ers will return the number of rows the application has fetched so
4008 far, but others may return -1 until all rows have been fetched. So
4009 use of the "rows" method or $DBI::rows with "SELECT" statements is
4010 not recommended.
4011 One alternative method to get a row count for a "SELECT" is to exe‐
4013 cute a "SELECT COUNT(*) FROM ..." SQL statement with the same "..."
4014 as your query and then fetch the row count from that.
4015 "bind_col"
4017 $rc = $sth->bind_col($column_number, \$var_to_bind);
4018 $rc = $sth->bind_col($column_number, \$var_to_bind, \%attr );
4019 $rc = $sth->bind_col($column_number, \$var_to_bind, $bind_type );
4020 Binds a Perl variable and/or some attributes to an output column
4022 (field) of a "SELECT" statement. Column numbers count up from 1.
4023 You do not need to bind output columns in order to fetch data. For
4024 maximum portability between drivers, bind_col() should be called
4025 after execute() and not before. See also "bind_columns" for an
4026 example.
4027 The binding is performed at a low level using Perl aliasing. When‐
4029 ever a row is fetched from the database $var_to_bind appears to be
4030 automatically updated simply because it now refers to the same mem‐
4031 ory location as the corresponding column value. This makes using
4032 bound variables very efficient. Binding a tied variable doesn't
4033 work, currently.
4034 The "bind_param" method performs a similar, but opposite, function
4036 for input variables.
4037 Data Types for Column Binding
4039 The "\%attr" parameter can be used to hint at the data type format‐
4041 ting the column should have. For example, you can use:
4042 $sth->bind_col(1, undef, { TYPE => SQL_DATETIME });
4044 to specify that you'd like the column (which presumably is some
4046 kind of datetime type) to be returned in the standard format for
4047 SQL_DATETIME, which is 'YYYY-MM-DD HH:MM:SS', rather than the
4048 native formatting the database would normally use.
4049 There's no $var_to_bind in that example to emphasize the point that
4051 bind_col() works on the underlying column value and not just a par‐
4052 ticular bound variable.
4053 As a short-cut for the common case, the data type can be passed
4055 directly, in place of the "\%attr" hash reference. This example is
4056 equivalent to the one above:
4057 $sth->bind_col(1, undef, SQL_DATETIME);
4059 The "TYPE" value indicates the standard (non-driver-specific) type
4061 for this parameter. To specify the driver-specific type, the driver
4062 may support a driver-specific attribute, such as "{ ora_type => 97
4063 }".
4064 The SQL_DATETIME and other related constants can be imported using
4066 use DBI qw(:sql_types);
4068 See "DBI Constants" for more information.
4070 The data type for a bind variable cannot be changed after the first
4072 "bind_col" call. In fact the whole \%attr parameter is 'sticky' in
4073 the sense that a driver only needs to consider the \%attr parameter
4074 for the first call for a given $sth and column.
4075 The TYPE attribute for bind_col() was first specified in DBI 1.41.
4077 "bind_columns"
4079 $rc = $sth->bind_columns(@list_of_refs_to_vars_to_bind);
4080 Calls "bind_col" for each column of the "SELECT" statement.
4082 The list of references should have the same number of elements as
4084 the number of columns in the "SELECT" statement. If it doesn't then
4085 "bind_columns" will bind the elements given, upto the number of
4086 columns, and then return an error.
4087 For maximum portability between drivers, bind_columns() should be
4089 called after execute() and not before.
4090 For example:
4092 $dbh->{RaiseError} = 1; # do this, or check every call for errors
4094 $sth = $dbh->prepare(q{ SELECT region, sales FROM sales_by_region });
4095 $sth->execute;
4096 my ($region, $sales);
4097 # Bind Perl variables to columns:
4099 $rv = $sth->bind_columns(\$region, \$sales);
4100 # you can also use Perl's \(...) syntax (see perlref docs):
4102 # $sth->bind_columns(\($region, $sales));
4103 # Column binding is the most efficient way to fetch data
4105 while ($sth->fetch) {
4106 print "$region: $sales\n";
4107 }
4108 For compatibility with old scripts, the first parameter will be
4110 ignored if it is "undef" or a hash reference.
4111 Here's a more fancy example that binds columns to the values inside
4113 a hash (thanks to H.Merijn Brand):
4114 $sth->execute;
4116 my %row;
4117 $sth->bind_columns( \( @row{ @{$sth->{NAME_lc} } } ));
4118 while ($sth->fetch) {
4119 print "$row{region}: $row{sales}\n";
4120 }
4121 "dump_results"
4123 $rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);
4124 Fetches all the rows from $sth, calls "DBI::neat_list" for each
4126 row, and prints the results to $fh (defaults to "STDOUT") separated
4127 by $lsep (default "\n"). $fsep defaults to ", " and $maxlen
4128 defaults to 35.
4129 This method is designed as a handy utility for prototyping and
4131 testing queries. Since it uses "neat_list" to format and edit the
4132 string for reading by humans, it is not recomended for data trans‐
4133 fer applications.
4134 Statement Handle Attributes
4136 This section describes attributes specific to statement handles. Most
4138 of these attributes are read-only.
4139 Changes to these statement handle attributes do not affect any other
4141 existing or future statement handles.
4142 Attempting to set or get the value of an unknown attribute generates a
4144 warning, except for private driver specific attributes (which all have
4145 names starting with a lowercase letter).
4146 Example:
4148 ... = $h->{NUM_OF_FIELDS}; # get/read
4150 Some drivers cannot provide valid values for some or all of these
4152 attributes until after "$sth->execute" has been successfully called.
4153 Typically the attribute will be "undef" in these situations.
4154 Some attributes, like NAME, are not appropriate to some types of state‐
4156 ment, like SELECT. Typically the attribute will be "undef" in these
4157 situations.
4158 See also "finish" to learn more about the effect it may have on some
4160 attributes.
4161 "NUM_OF_FIELDS" (integer, read-only)
4163 Number of fields (columns) in the data the prepared statement may
4164 return. Statements that don't return rows of data, like "DELETE"
4165 and "CREATE" set "NUM_OF_FIELDS" to 0 (though it may be undef in
4166 some drivers).
4167 "NUM_OF_PARAMS" (integer, read-only)
4169 The number of parameters (placeholders) in the prepared statement.
4170 See SUBSTITUTION VARIABLES below for more details.
4171 "NAME" (array-ref, read-only)
4173 Returns a reference to an array of field names for each column. The
4174 names may contain spaces but should not be truncated or have any
4175 trailing space. Note that the names have the letter case (upper,
4176 lower or mixed) as returned by the driver being used. Portable
4177 applications should use "NAME_lc" or "NAME_uc".
4178 print "First column name: $sth->{NAME}->[0]\n";
4180 "NAME_lc" (array-ref, read-only)
4182 Like "NAME" but always returns lowercase names.
4183 "NAME_uc" (array-ref, read-only)
4185 Like "NAME" but always returns uppercase names.
4186 "NAME_hash" (hash-ref, read-only)
4188 "NAME_lc_hash" (hash-ref, read-only)
4189 "NAME_uc_hash" (hash-ref, read-only)
4190 The "NAME_hash", "NAME_lc_hash", and "NAME_uc_hash" attributes
4191 return column name information as a reference to a hash.
4192 The keys of the hash are the names of the columns. The letter case
4194 of the keys corresponds to the letter case returned by the "NAME",
4195 "NAME_lc", and "NAME_uc" attributes respectively (as described
4196 above).
4197 The value of each hash entry is the perl index number of the corre‐
4199 sponding column (counting from 0). For example:
4200 $sth = $dbh->prepare("select Id, Name from table");
4202 $sth->execute;
4203 @row = $sth->fetchrow_array;
4204 print "Name $row[ $sth->{NAME_lc_hash}{name} ]\n";
4205 "TYPE" (array-ref, read-only)
4207 Returns a reference to an array of integer values for each column.
4208 The value indicates the data type of the corresponding column.
4209 The values correspond to the international standards (ANSI X3.135
4211 and ISO/IEC 9075) which, in general terms, means ODBC. Driver-spe‐
4212 cific types that don't exactly match standard types should gener‐
4213 ally return the same values as an ODBC driver supplied by the mak‐
4214 ers of the database. That might include private type numbers in
4215 ranges the vendor has officially registered with the ISO working
4216 group:
4217 ftp://sqlstandards.org/SC32/SQL_Registry/
4219 Where there's no vendor-supplied ODBC driver to be compatible with,
4221 the DBI driver can use type numbers in the range that is now offi‐
4222 cially reserved for use by the DBI: -9999 to -9000.
4223 All possible values for "TYPE" should have at least one entry in
4225 the output of the "type_info_all" method (see "type_info_all").
4226 "PRECISION" (array-ref, read-only)
4228 Returns a reference to an array of integer values for each column.
4229 For numeric columns, the value is the maximum number of digits
4231 (without considering a sign character or decimal point). Note that
4232 the "display size" for floating point types (REAL, FLOAT, DOUBLE)
4233 can be up to 7 characters greater than the precision (for the sign
4234 + decimal point + the letter E + a sign + 2 or 3 digits).
4235 For any character type column the value is the OCTET_LENGTH, in
4237 other words the number of bytes, not characters.
4238 (More recent standards refer to this as COLUMN_SIZE but we stick
4240 with PRECISION for backwards compatibility.)
4241 "SCALE" (array-ref, read-only)
4243 Returns a reference to an array of integer values for each column.
4244 NULL ("undef") values indicate columns where scale is not applica‐
4245 ble.
4246 "NULLABLE" (array-ref, read-only)
4248 Returns a reference to an array indicating the possibility of each
4249 column returning a null. Possible values are 0 (or an empty
4250 string) = no, 1 = yes, 2 = unknown.
4251 print "First column may return NULL\n" if $sth->{NULLABLE}->[0];
4253 "CursorName" (string, read-only)
4255 Returns the name of the cursor associated with the statement han‐
4256 dle, if available. If not available or if the database driver does
4257 not support the "where current of ..." SQL syntax, then it returns
4258 "undef".
4259 "Database" (dbh, read-only)
4261 Returns the parent $dbh of the statement handle.
4262 "ParamValues" (hash ref, read-only)
4264 Returns a reference to a hash containing the values currently bound
4265 to placeholders. The keys of the hash are the 'names' of the
4266 placeholders, typically integers starting at 1. Returns undef if
4267 not supported by the driver.
4268 See "ShowErrorStatement" for an example of how this is used.
4270 If the driver supports "ParamValues" but no values have been bound
4272 yet then the driver should return a hash with placeholders names in
4273 the keys but all the values undef, but some drivers may return a
4274 ref to an empty hash.
4275 It is possible that the values in the hash returned by "ParamVal‐
4277 ues" are not exactly the same as those passed to bind_param() or
4278 execute(). The driver may have slightly modified values in some
4279 way based on the TYPE the value was bound with. For example a
4280 floating point value bound as an SQL_INTEGER type may be returned
4281 as an integer. The values returned by "ParamValues" can be passed
4282 to another bind_param() method with the same TYPE and will be seen
4283 by the database as the same value.
4284 It is also possible that the keys in the hash returned by "Param‐
4286 Values" are not exactly the same as those implied by the prepared
4287 statement. For example, DBD::Oracle translates '"?"' placeholders
4288 into '":pN"' where N is a sequence number starting at 1.
4289 The "ParamValues" attribute was added in DBI 1.28.
4291 "ParamArrays" (hash ref, read-only)
4293 Returns a reference to a hash containing the values currently bound
4294 to placeholders with "execute_array" or "bind_param_array". The
4295 keys of the hash are the 'names' of the placeholders, typically
4296 integers starting at 1. Returns undef if not supported by the
4297 driver or no arrays of parameters are bound.
4298 Each key value is an array reference containing a list of the bound
4300 parameters for that column.
4301 For example:
4303 $sth = $dbh->prepare("INSERT INTO staff (id, name) values (?,?)");
4305 $sth->execute_array({},[1,2], ['fred','dave']);
4306 if ($sth->{ParamArrays}) {
4307 foreach $param (keys %{$sth->{ParamArrays}}) {
4308 printf "Parameters for %s : %s\n", $param,
4309 join(",", @{$sth->{ParamArrays}->{$param}});
4310 }
4311 }
4312 It is possible that the values in the hash returned by "ParamAr‐
4314 rays" are not exactly the same as those passed to
4315 "bind_param_array" or "execute_array". The driver may have
4316 slightly modified values in some way based on the TYPE the value
4317 was bound with. For example a floating point value bound as an
4318 SQL_INTEGER type may be returned as an integer.
4319 It is also possible that the keys in the hash returned by "ParamAr‐
4321 rays" are not exactly the same as those implied by the prepared
4322 statement. For example, DBD::Oracle translates '"?"' placeholders
4323 into '":pN"' where N is a sequence number starting at 1.
4324 "ParamTypes" (hash ref, read-only)
4326 Returns a reference to a hash containing the type information cur‐
4327 rently bound to placeholders. The keys of the hash are the 'names'
4328 of the placeholders: either integers starting at 1, or, for drivers
4329 that support named placeholders, the actual parameter name string.
4330 The hash values are hashrefs of type information in the same form
4331 as that provided to the various bind_param() methods (See "Data
4332 Types for Placeholders" for the format and values), plus anything
4333 else that was passed as the third argument to bind_param().
4334 Returns undef if not supported by the driver.
4335 If the driver supports "ParamTypes", but no values have been bound
4337 yet, then the driver should return a hash with the placeholder name
4338 keys, but all the values undef; however, some drivers may return a
4339 ref to an empty hash, or, alternately, may provide type information
4340 supplied by the database (only a few databases can do that).
4341 It is possible that the values in the hash returned by "ParamTypes"
4343 are not exactly the same as those passed to bind_param() or exe‐
4344 cute(). The driver may have modified the type information in some
4345 way based on the bound values, other hints provided by the pre‐
4346 pare()'d SQL statement, or alternate type mappings required by the
4347 driver or target database system.
4348 It is also possible that the keys in the hash returned by "Param‐
4350 Types" are not exactly the same as those implied by the prepared
4351 statement. For example, DBD::Oracle translates '"?"' placeholders
4352 into '":pN"' where N is a sequence number starting at 1.
4353 The "ParamTypes" attribute was added in DBI 1.49. Implementation is
4355 the responsibility of individual drivers; the DBI layer default
4356 implementation simply returns undef.
4357 "Statement" (string, read-only)
4359 Returns the statement string passed to the "prepare" method.
4360 "RowsInCache" (integer, read-only)
4362 If the driver supports a local row cache for "SELECT" statements,
4363 then this attribute holds the number of un-fetched rows in the
4364 cache. If the driver doesn't, then it returns "undef". Note that
4365 some drivers pre-fetch rows on execute, whereas others wait till
4366 the first fetch.
4367 See also the "RowCacheSize" database handle attribute.
4369
4371 "install_method"
4372 DBD::Foo::db->install_method($method_name, \%attr);
4373 Installs the driver-private method named by $method_name into the
4375 DBI method dispatcher so it can be called directly, avoiding the
4376 need to use the func() method.
4377 It is called as a static method on the driver class to which the
4379 method belongs. The method name must begin with the corresponding
4380 registered driver-private prefix. For example, for DBD::Oracle
4381 $method_name must being with '"ora_"', and for DBD::AnyData it must
4382 begin with '"ad_"'.
4383 The attributes can be used to provide fine control over how the DBI
4385 dispatcher handles the dispatching of the method. However, at this
4386 point, it's undocumented and very liable to change. (Volunteers to
4387 polish up and document the interface are very welcome to get in
4388 touch via dbi-dev@perl.org)
4389 Methods installed using install_method default to the standard
4391 error handling behaviour for DBI methods: clearing err and errstr
4392 before calling the method, and checking for errors to trigger
4393 RaiseError etc. on return. This differs from the default behaviour
4394 of func().
4395 Note for driver authors: The DBD::Foo::xx->install_method call
4397 won't work until the class-hierarchy has been setup. Normally the
4398 DBI looks after that just after the driver is loaded. This means
4399 install_method() can't be called at the time the driver is loaded
4400 unless the class-hierarchy is set up first. The way to do that is
4401 to call the setup_driver() method:
4402 DBI->setup_driver('DBD::Foo');
4404 before using install_method().
4406
4408 Catalog Methods
4409 An application can retrieve metadata information from the DBMS by issu‐
4411 ing appropriate queries on the views of the Information Schema. Unfor‐
4412 tunately, "INFORMATION_SCHEMA" views are seldom supported by the DBMS.
4413 Special methods (catalog methods) are available to return result sets
4414 for a small but important portion of that metadata:
4415 column_info
4417 foreign_key_info
4418 primary_key_info
4419 table_info
4420 statistics_info
4421 All catalog methods accept arguments in order to restrict the result
4423 sets. Passing "undef" to an optional argument does not constrain the
4424 search for that argument. However, an empty string ('') is treated as
4425 a regular search criteria and will only match an empty value.
4426 Note: SQL/CLI and ODBC differ in the handling of empty strings. An
4428 empty string will not restrict the result set in SQL/CLI.
4429 Most arguments in the catalog methods accept only ordinary values, e.g.
4431 the arguments of "primary_key_info()". Such arguments are treated as a
4432 literal string, i.e. the case is significant and quote characters are
4433 taken literally.
4434 Some arguments in the catalog methods accept search patterns (strings
4436 containing '_' and/or '%'), e.g. the $table argument of "col‐
4437 umn_info()". Passing '%' is equivalent to leaving the argument
4438 "undef".
4439 Caveat: The underscore ('_') is valid and often used in SQL identi‐
4441 fiers. Passing such a value to a search pattern argument may return
4442 more rows than expected! To include pattern characters as literals,
4443 they must be preceded by an escape character which can be achieved with
4444 $esc = $dbh->get_info( 14 ); # SQL_SEARCH_PATTERN_ESCAPE
4446 $search_pattern =~ s/([_%])/$esc$1/g;
4447 The ODBC and SQL/CLI specifications define a way to change the default
4449 behaviour described above: All arguments (except list value arguments)
4450 are treated as identifier if the "SQL_ATTR_METADATA_ID" attribute is
4451 set to "SQL_TRUE". Quoted identifiers are very similar to ordinary
4452 values, i.e. their body (the string within the quotes) is interpreted
4453 literally. Unquoted identifiers are compared in UPPERCASE.
4454 The DBI (currently) does not support the "SQL_ATTR_METADATA_ID"
4456 attribute, i.e. it behaves like an ODBC driver where "SQL_ATTR_META‐
4457 DATA_ID" is set to "SQL_FALSE".
4458 Transactions
4460 Transactions are a fundamental part of any robust database system. They
4462 protect against errors and database corruption by ensuring that sets of
4463 related changes to the database take place in atomic (indivisible,
4464 all-or-nothing) units.
4465 This section applies to databases that support transactions and where
4467 "AutoCommit" is off. See "AutoCommit" for details of using "AutoCom‐
4468 mit" with various types of databases.
4469 The recommended way to implement robust transactions in Perl applica‐
4471 tions is to use "RaiseError" and "eval { ... }" (which is very fast,
4472 unlike "eval "...""). For example:
4473 $dbh->{AutoCommit} = 0; # enable transactions, if possible
4475 $dbh->{RaiseError} = 1;
4476 eval {
4477 foo(...) # do lots of work here
4478 bar(...) # including inserts
4479 baz(...) # and updates
4480 $dbh->commit; # commit the changes if we get this far
4481 };
4482 if ($@) {
4483 warn "Transaction aborted because $@";
4484 # now rollback to undo the incomplete changes
4485 # but do it in an eval{} as it may also fail
4486 eval { $dbh->rollback };
4487 # add other application on-error-clean-up code here
4488 }
4489 If the "RaiseError" attribute is not set, then DBI calls would need to
4491 be manually checked for errors, typically like this:
4492 $h->method(@args) or die $h->errstr;
4494 With "RaiseError" set, the DBI will automatically "die" if any DBI
4496 method call on that handle (or a child handle) fails, so you don't have
4497 to test the return value of each method call. See "RaiseError" for more
4498 details.
4499 A major advantage of the "eval" approach is that the transaction will
4501 be properly rolled back if any code (not just DBI calls) in the inner
4502 application dies for any reason. The major advantage of using the
4503 "$h->{RaiseError}" attribute is that all DBI calls will be checked
4504 automatically. Both techniques are strongly recommended.
4505 After calling "commit" or "rollback" many drivers will not let you
4507 fetch from a previously active "SELECT" statement handle that's a child
4508 of the same database handle. A typical way round this is to connect the
4509 the database twice and use one connection for "SELECT" statements.
4510 See "AutoCommit" and "disconnect" for other important information about
4512 transactions.
4513 Handling BLOB / LONG / Memo Fields
4515 Many databases support "blob" (binary large objects), "long", or simi‐
4517 lar datatypes for holding very long strings or large amounts of binary
4518 data in a single field. Some databases support variable length long
4519 values over 2,000,000,000 bytes in length.
4520 Since values of that size can't usually be held in memory, and because
4522 databases can't usually know in advance the length of the longest long
4523 that will be returned from a "SELECT" statement (unlike other data
4524 types), some special handling is required.
4525 In this situation, the value of the "$h->{LongReadLen}" attribute is
4527 used to determine how much buffer space to allocate when fetching such
4528 fields. The "$h->{LongTruncOk}" attribute is used to determine how to
4529 behave if a fetched value can't fit into the buffer.
4530 See the description of "LongReadLen" for more information.
4532 When trying to insert long or binary values, placeholders should be
4534 used since there are often limits on the maximum size of an "INSERT"
4535 statement and the "quote" method generally can't cope with binary data.
4536 See "Placeholders and Bind Values".
4537 Simple Examples
4539 Here's a complete example program to select and fetch some data:
4541 my $data_source = "dbi::DriverName:db_name";
4543 my $dbh = DBI->connect($data_source, $user, $password)
4544 or die "Can't connect to $data_source: $DBI::errstr";
4545 my $sth = $dbh->prepare( q{
4547 SELECT name, phone
4548 FROM mytelbook
4549 }) or die "Can't prepare statement: $DBI::errstr";
4550 my $rc = $sth->execute
4552 or die "Can't execute statement: $DBI::errstr";
4553 print "Query will return $sth->{NUM_OF_FIELDS} fields.\n\n";
4555 print "Field names: @{ $sth->{NAME} }\n";
4556 while (($name, $phone) = $sth->fetchrow_array) {
4558 print "$name: $phone\n";
4559 }
4560 # check for problems which may have terminated the fetch early
4561 die $sth->errstr if $sth->err;
4562 $dbh->disconnect;
4564 Here's a complete example program to insert some data from a file.
4566 (This example uses "RaiseError" to avoid needing to check each call).
4567 my $dbh = DBI->connect("dbi:DriverName:db_name", $user, $password, {
4569 RaiseError => 1, AutoCommit => 0
4570 });
4571 my $sth = $dbh->prepare( q{
4573 INSERT INTO table (name, phone) VALUES (?, ?)
4574 });
4575 open FH, "<phone.csv" or die "Unable to open phone.csv: $!";
4577 while (<FH>) {
4578 chomp;
4579 my ($name, $phone) = split /,/;
4580 $sth->execute($name, $phone);
4581 }
4582 close FH;
4583 $dbh->commit;
4585 $dbh->disconnect;
4586 Here's how to convert fetched NULLs (undefined values) into empty
4588 strings:
4589 while($row = $sth->fetchrow_arrayref) {
4591 # this is a fast and simple way to deal with nulls:
4592 foreach (@$row) { $_ = '' unless defined }
4593 print "@$row\n";
4594 }
4595 The "q{...}" style quoting used in these examples avoids clashing with
4597 quotes that may be used in the SQL statement. Use the double-quote like
4598 "qq{...}" operator if you want to interpolate variables into the
4599 string. See "Quote and Quote-like Operators" in perlop for more
4600 details.
4601 Threads and Thread Safety
4603 Perl 5.7 and later support a new threading model called iThreads. (The
4605 old "5.005 style" threads are not supported by the DBI.)
4606 In the iThreads model each thread has it's own copy of the perl inter‐
4608 preter. When a new thread is created the original perl interpreter is
4609 'cloned' to create a new copy for the new thread.
4610 If the DBI and drivers are loaded and handles created before the thread
4612 is created then it will get a cloned copy of the DBI, the drivers and
4613 the handles.
4614 However, the internal pointer data within the handles will refer to the
4616 DBI and drivers in the original interpreter. Using those handles in the
4617 new interpreter thread is not safe, so the DBI detects this and croaks
4618 on any method call using handles that don't belong to the current
4619 thread (except for DESTROY).
4620 Because of this (possibly temporary) restriction, newly created threads
4622 must make their own connctions to the database. Handles can't be shared
4623 across threads.
4624 But BEWARE, some underlying database APIs (the code the DBD driver uses
4626 to talk to the database, often supplied by the database vendor) are not
4627 thread safe. If it's not thread safe, then allowing more than one
4628 thread to enter the code at the same time may cause subtle/serious
4629 problems. In some cases allowing more than one thread to enter the
4630 code, even if not at the same time, can cause problems. You have been
4631 warned.
4632 Using DBI with perl threads is not yet recommended for production envi‐
4634 ronments. For more information see <http://www.perl‐
4635 monks.org/index.pl?node_id=288022>
4636 Note: There is a bug in perl 5.8.2 when configured with threads and
4638 debugging enabled (bug #24463) which causes a DBI test to fail.
4639 Signal Handling and Canceling Operations
4641 [The following only applies to systems with unix-like signal handling.
4643 I'd welcome additions for other systems, especially Windows.]
4644 The first thing to say is that signal handling in Perl versions less
4646 than 5.8 is not safe. There is always a small risk of Perl crashing
4647 and/or core dumping when, or after, handling a signal because the sig‐
4648 nal could arrive and be handled while internal data structures are
4649 being changed. If the signal handling code used those same internal
4650 data structures it could cause all manner of subtle and not-so-subtle
4651 problems. The risk was reduced with 5.4.4 but was still present in all
4652 perls up through 5.8.0.
4653 Beginning in perl 5.8.0 perl implements 'safe' signal handling if your
4655 system has the POSIX sigaction() routine. Now when a signal is deliv‐
4656 ered perl just makes a note of it but does not run the %SIG handler.
4657 The handling is 'defered' until a 'safe' moment.
4658 Although this change made signal handling safe, it also lead to a prob‐
4660 lem with signals being defered for longer than you'd like. If a signal
4661 arrived while executing a system call, such as waiting for data on a
4662 network connection, the signal is noted and then the system call that
4663 was executing returns with an EINTR error code to indicate that it was
4664 interrupted. All fine so far.
4665 The problem comes when the code that made the system call sees the
4667 EINTR code and decides it's going to call it again. Perl doesn't do
4668 that, but database code sometimes does. If that happens then the signal
4669 handler doesn't get called untill later. Maybe much later.
4670 Fortunately there are ways around this which we'll discuss below.
4672 Unfortunately they make signals unsafe again.
4673 The two most common uses of signals in relation to the DBI are for can‐
4675 celing operations when the user types Ctrl-C (interrupt), and for
4676 implementing a timeout using "alarm()" and $SIG{ALRM}.
4677 Cancel
4679 The DBI provides a "cancel" method for statement handles. The "can‐
4680 cel" method should abort the current operation and is designed to
4681 be called from a signal handler. For example:
4682 $SIG{INT} = sub { $sth->cancel };
4684 However, few drivers implement this (the DBI provides a default
4686 method that just returns "undef") and, even if implemented, there
4687 is still a possibility that the statement handle, and even the par‐
4688 ent database handle, will not be usable afterwards.
4689 If "cancel" returns true, then it has successfully invoked the
4691 database engine's own cancel function. If it returns false, then
4692 "cancel" failed. If it returns "undef", then the database driver
4693 does not have cancel implemented.
4694 Timeout
4696 The traditional way to implement a timeout is to set $SIG{ALRM} to
4697 refer to some code that will be executed when an ALRM signal
4698 arrives and then to call alarm($seconds) to schedule an ALRM signal
4699 to be delivered $seconds in the future. For example:
4700 eval {
4702 local $SIG{ALRM} = sub { die "TIMEOUT\n" };
4703 alarm($seconds);
4704 ... code to execute with timeout here ...
4705 alarm(0); # cancel alarm (if code ran fast)
4706 };
4707 alarm(0); # cancel alarm (if eval failed)
4708 if ( $@ eq "TIMEOUT" ) { ... }
4709 Unfortunately, as described above, this won't always work as
4711 expected, depending on your perl version and the underlying data‐
4712 base code.
4713 With Oracle for instance (DBD::Oracle), if the system which hosts
4715 the database is down the DBI->connect() call will hang for several
4716 minutes before returning an error.
4717 The solution on these systems is to use the "POSIX::sigaction()" rou‐
4719 tine to gain low level access to how the signal handler is installed.
4720 The code would look something like this (for the DBD-Oracle connect()):
4722 use POSIX ':signal_h';
4724 my $mask = POSIX::SigSet->new( SIGALRM ); # signals to mask in the handler
4726 my $action = POSIX::SigAction->new(
4727 sub { die "connect timeout" }, # the handler code ref
4728 $mask,
4729 # not using (perl 5.8.2 and later) 'safe' switch or sa_flags
4730 );
4731 my $oldaction = POSIX::SigAction->new();
4732 sigaction( 'ALRM', $action, $oldaction );
4733 my $dbh;
4734 eval {
4735 alarm(5); # seconds before time out
4736 $dbh = DBI->connect("dbi:Oracle:$dsn" ... );
4737 alarm(0); # cancel alarm (if connect worked fast)
4738 };
4739 alarm(0); # cancel alarm (if eval failed)
4740 sigaction( 'ALRM', $oldaction ); # restore original signal handler
4741 if ( $@ ) ....
4742 Similar techniques can be used for canceling statement execution.
4744 Unfortunately, this solution is somewhat messy, and it does not work
4746 with perl versions less than perl 5.8 where "POSIX::sigaction()"
4747 appears to be broken.
4748 For a cleaner implementation that works across perl versions, see Lin‐
4750 coln Baxter's Sys::SigAction module at <http://search.cpan.org/~lbax‐
4751 ter/Sys-SigAction/>. The documentation for Sys::SigAction includes an
4752 longer discussion of this problem, and a DBD::Oracle test script.
4753 Be sure to read all the signal handling sections of the perlipc manual.
4755 And finally, two more points to keep firmly in mind. Firstly, remember
4757 that what we've done here is essentially revert to old style unsafe
4758 handling of these signals. So do as little as possible in the handler.
4759 Ideally just die(). Secondly, the handles in use at the time the signal
4760 is handled may not be safe to use afterwards.
4761 Subclassing the DBI
4763 DBI can be subclassed and extended just like any other object oriented
4765 module. Before we talk about how to do that, it's important to be
4766 clear about the various DBI classes and how they work together.
4767 By default "$dbh = DBI->connect(...)" returns a $dbh blessed into the
4769 "DBI::db" class. And the "$dbh->prepare" method returns an $sth
4770 blessed into the "DBI::st" class (actually it simply changes the last
4771 four characters of the calling handle class to be "::st").
4772 The leading '"DBI"' is known as the 'root class' and the extra '"::db"'
4774 or '"::st"' are the 'handle type suffixes'. If you want to subclass the
4775 DBI you'll need to put your overriding methods into the appropriate
4776 classes. For example, if you want to use a root class of "MySubDBI"
4777 and override the do(), prepare() and execute() methods, then your do()
4778 and prepare() methods should be in the "MySubDBI::db" class and the
4779 execute() method should be in the "MySubDBI::st" class.
4780 To setup the inheritance hierarchy the @ISA variable in "MySubDBI::db"
4782 should include "DBI::db" and the @ISA variable in "MySubDBI::st" should
4783 include "DBI::st". The "MySubDBI" root class itself isn't currently
4784 used for anything visible and so, apart from setting @ISA to include
4785 "DBI", it can be left empty.
4786 So, having put your overriding methods into the right classes, and set‐
4788 up the inheritance hierarchy, how do you get the DBI to use them? You
4789 have two choices, either a static method call using the name of your
4790 subclass:
4791 $dbh = MySubDBI->connect(...);
4793 or specifying a "RootClass" attribute:
4795 $dbh = DBI->connect(..., { RootClass => 'MySubDBI' });
4797 If both forms are used then the attribute takes precedence.
4799 The only differences between the two are that using an explicit Root‐
4801 Class attribute will a) make the DBI automatically attempt to load a
4802 module by that name if the class doesn't exist, and b) won't call your
4803 MySubDBI::connect() method, if you have one.
4804 When subclassing is being used then, after a successful new connect,
4806 the DBI->connect method automatically calls:
4807 $dbh->connected($dsn, $user, $pass, \%attr);
4809 The default method does nothing. The call is made just to simplify any
4811 post-connection setup that your subclass may want to perform. The
4812 parameters are the same as passed to DBI->connect. If your subclass
4813 supplies a connected method, it should be part of the MySubDBI::db
4814 package.
4815 One more thing to note: you must let the DBI do the handle creation.
4817 If you want to override the connect() method in your *::dr class then
4818 it must still call SUPER::connect to get a $dbh to work with. Simi‐
4819 larly, an overridden prepare() method in *::db must still call
4820 SUPER::prepare to get a $sth. If you try to create your own handles
4821 using bless() then you'll find the DBI will reject them with an "is not
4822 a DBI handle (has no magic)" error.
4823 Here's a brief example of a DBI subclass. A more thorough example can
4825 be found in t/subclass.t in the DBI distribution.
4826 package MySubDBI;
4828 use strict;
4830 use DBI;
4832 use vars qw(@ISA);
4833 @ISA = qw(DBI);
4834 package MySubDBI::db;
4836 use vars qw(@ISA);
4837 @ISA = qw(DBI::db);
4838 sub prepare {
4840 my ($dbh, @args) = @_;
4841 my $sth = $dbh->SUPER::prepare(@args)
4842 or return;
4843 $sth->{private_mysubdbi_info} = { foo => 'bar' };
4844 return $sth;
4845 }
4846 package MySubDBI::st;
4848 use vars qw(@ISA);
4849 @ISA = qw(DBI::st);
4850 sub fetch {
4852 my ($sth, @args) = @_;
4853 my $row = $sth->SUPER::fetch(@args)
4854 or return;
4855 do_something_magical_with_row_data($row)
4856 or return $sth->set_err(1234, "The magic failed", undef, "fetch");
4857 return $row;
4858 }
4859 When calling a SUPER::method that returns a handle, be careful to check
4861 the return value before trying to do other things with it in your over‐
4862 ridden method. This is especially important if you want to set a hash
4863 attribute on the handle, as Perl's autovivification will bite you by
4864 (in)conveniently creating an unblessed hashref, which your method will
4865 then return with usually baffling results later on like the error
4866 "dbih_getcom handle HASH(0xa4451a8) is not a DBI handle (has no magic".
4867 It's best to check right after the call and return undef immediately on
4868 error, just like DBI would and just like the example above.
4869 If your method needs to record an error it should call the set_err()
4871 method with the error code and error string, as shown in the example
4872 above. The error code and error string will be recorded in the handle
4873 and available via "$h->err" and $DBI::errstr etc. The set_err() method
4874 always returns an undef or empty list as approriate. Since your method
4875 should nearly always return an undef or empty list as soon as an error
4876 is detected it's handy to simply return what set_err() returns, as
4877 shown in the example above.
4878 If the handle has "RaiseError", "PrintError", or "HandleError" etc. set
4880 then the set_err() method will honour them. This means that if
4881 "RaiseError" is set then set_err() won't return in the normal way but
4882 will 'throw an exception' that can be caught with an "eval" block.
4883 You can stash private data into DBI handles via "$h->{private_..._*}".
4885 See the entry under "ATTRIBUTES COMMON TO ALL HANDLES" for info and
4886 important caveats.
4887
4889 The DBI has a powerful tracing mechanism built in. It enables you to
4890 see what's going on 'behind the scenes', both within the DBI and the
4891 drivers you're using.
4892 Trace Settings
4894 Which details are written to the trace output is controlled by a combi‐
4896 nation of a trace level, an integer from 0 to 15, and a set of trace
4897 flags that are either on or off. Together these are known as the trace
4898 settings and are stored together in a single integer. For normal use
4899 you only need to set the trace level, and generally only to a value
4900 between 1 and 4.
4901 Each handle has it's own trace settings, and so does the DBI. When you
4903 call a method the DBI merges the handles settings into its own for the
4904 duration of the call: the trace flags of the handle are OR'd into the
4905 trace flags of the DBI, and if the handle has a higher trace level then
4906 the DBI trace level is raised to match it. The previous DBI trace set‐
4907 ings are restored when the called method returns.
4908 Trace Levels
4910 Trace levels are as follows:
4912 0 - Trace disabled.
4914 1 - Trace DBI method calls returning with results or errors.
4915 2 - Trace method entry with parameters and returning with results.
4916 3 - As above, adding some high-level information from the driver
4917 and some internal information from the DBI.
4918 4 - As above, adding more detailed information from the driver.
4919 5 to 15 - As above but with more and more obscure information.
4920 Trace level 1 is best for a simple overview of what's happening. Trace
4922 level 2 is a good choice for general purpose tracing. Levels 3 and
4923 above are best reserved for investigating a specific problem, when you
4924 need to see "inside" the driver and DBI.
4925 The trace output is detailed and typically very useful. Much of the
4927 trace output is formatted using the "neat" function, so strings in the
4928 trace output may be edited and truncated by that function.
4929 Trace Flags
4931 Trace flags are used to enable tracing of specific activities within
4933 the DBI and drivers. The DBI defines some trace flags and drivers can
4934 define others. DBI trace flag names begin with a capital letter and
4935 driver specific names begin with a lowercase letter, as usual.
4936 Curently the DBI only defines two trace flags:
4938 ALL - turn on all DBI and driver flags (not recommended)
4940 SQL - trace SQL statements executed (not yet implemented)
4941 The "parse_trace_flags" and "parse_trace_flag" methods are used to con‐
4943 vert trace flag names into the coresponding integer bit flags.
4944 Enabling Trace
4946 The "$h->trace" method sets the trace settings for a handle and
4948 "DBI->trace" does the same for the DBI.
4949 In addition to the "trace" method, you can enable the same trace infor‐
4951 mation, and direct the output to a file, by setting the "DBI_TRACE"
4952 environment variable before starting Perl. See "DBI_TRACE" for more
4953 information.
4954 Finally, you can set, or get, the trace settings for a handle using the
4956 "TraceLevel" attribute.
4957 All of those methods use parse_trace_flags() and so allow you set both
4959 the trace level and multiple trace flags by using a string containing
4960 the trace level and/or flag names separated by vertical bar (""⎪"") or
4961 comma ("","") characters. For example:
4962 local $h->{TraceLevel} = "3⎪SQL⎪foo";
4964 Trace Output
4966 Initially trace output is written to "STDERR". Both the "$h->trace"
4968 and "DBI->trace" methods take an optional $trace_filename parameter. If
4969 specified, and can be opened in append mode, then all trace output
4970 (currently including that from other handles) is redirected to that
4971 file. A warning is generated if the file can't be opened.
4972 Further calls to trace() without a $trace_filename do not alter where
4974 the trace output is sent. If $trace_filename is undefined, then trace
4975 output is sent to "STDERR" and the previous trace file is closed.
4976 Currently $trace_filename can't be a filehandle. But meanwhile you can
4978 use the special strings "STDERR" and "STDOUT" to select those filehan‐
4979 dles.
4980 Trace Content
4982 Many of the values embeded in trace output are formatted using the
4984 neat() utility function. This means they may be quoted, sanitized, and
4985 possibly truncated if longer than $DBI::neat_maxlen. See "neat" for
4986 more details.
4987 Tracing Tips
4989 You can add tracing to your own application code using the "trace_msg"
4991 method.
4992 It can sometimes be handy to compare trace files from two different
4994 runs of the same script. However using a tool like "diff" doesn't work
4995 well because the trace file is full of object addresses that may differ
4996 each run. Here's a handy little command to strip those out:
4997 perl -pe 's/\b0x[\da-f]{6,}/0xNNNN/gi; s/\b[\da-f]{6,}/<long number>/gi'
4999
5001 The DBI module recognizes a number of environment variables, but most
5002 of them should not be used most of the time. It is better to be
5003 explicit about what you are doing to avoid the need for environment
5004 variables, especially in a web serving system where web servers are
5005 stingy about which environment variables are available.
5006 DBI_DSN
5008 The DBI_DSN environment variable is used by DBI->connect if you do not
5010 specify a data source when you issue the connect. It should have a
5011 format such as "dbi:Driver:databasename".
5012 DBI_DRIVER
5014 The DBI_DRIVER environment variable is used to fill in the database
5016 driver name in DBI->connect if the data source string starts "dbi::"
5017 (thereby omitting the driver). If DBI_DSN omits the driver name,
5018 DBI_DRIVER can fill the gap.
5019 DBI_AUTOPROXY
5021 The DBI_AUTOPROXY environment variable takes a string value that starts
5023 "dbi:Proxy:" and is typically followed by "hostname=...;port=...". It
5024 is used to alter the behaviour of DBI->connect. For full details, see
5025 DBI::Proxy documentation.
5026 DBI_USER
5028 The DBI_USER environment variable takes a string value that is used as
5030 the user name if the DBI->connect call is given undef (as distinct from
5031 an empty string) as the username argument. Be wary of the security
5032 implications of using this.
5033 DBI_PASS
5035 The DBI_PASS environment variable takes a string value that is used as
5037 the password if the DBI->connect call is given undef (as distinct from
5038 an empty string) as the password argument. Be extra wary of the secu‐
5039 rity implications of using this.
5040 DBI_DBNAME (obsolete)
5042 The DBI_DBNAME environment variable takes a string value that is used
5044 only when the obsolescent style of DBI->connect (with driver name as
5045 fourth parameter) is used, and when no value is provided for the first
5046 (database name) argument.
5047 DBI_TRACE
5049 The DBI_TRACE environment variable specifies the global default trace
5051 settings for the DBI at startup. Can also be used to direct trace out‐
5052 put to a file. When the DBI is loaded it does:
5053 DBI->trace(split /=/, $ENV{DBI_TRACE}, 2) if $ENV{DBI_TRACE};
5055 So if "DBI_TRACE" contains an ""="" character then what follows it is
5057 used as the name of the file to append the trace to.
5058 output appended to that file. If the name begins with a number followed
5060 by an equal sign ("="), then the number and the equal sign are stripped
5061 off from the name, and the number is used to set the trace level. For
5062 example:
5063 DBI_TRACE=1=dbitrace.log perl your_test_script.pl
5065 On Unix-like systems using a Bourne-like shell, you can do this easily
5067 on the command line:
5068 DBI_TRACE=2 perl your_test_script.pl
5070 See "TRACING" for more information.
5072 PERL_DBI_DEBUG (obsolete)
5074 An old variable that should no longer be used; equivalent to DBI_TRACE.
5076 DBI_PROFILE
5078 The DBI_PROFILE environment variable can be used to enable profiling of
5080 DBI method calls. See DBI::Profile for more information.
5081 DBI_PUREPERL
5083 The DBI_PUREPERL environment variable can be used to enable the use of
5085 DBI::PurePerl. See DBI::PurePerl for more information.
5086
5088 Fatal Errors
5089 Can't call method "prepare" without a package or object reference
5091 The $dbh handle you're using to call "prepare" is probably unde‐
5092 fined because the preceding "connect" failed. You should always
5093 check the return status of DBI methods, or use the "RaiseError"
5094 attribute.
5095 Can't call method "execute" without a package or object reference
5097 The $sth handle you're using to call "execute" is probably unde‐
5098 fined because the preceeding "prepare" failed. You should always
5099 check the return status of DBI methods, or use the "RaiseError"
5100 attribute.
5101 DBI/DBD internal version mismatch
5103 The DBD driver module was built with a different version of DBI
5104 than the one currently being used. You should rebuild the DBD mod‐
5105 ule under the current version of DBI.
5106 (Some rare platforms require "static linking". On those platforms,
5108 there may be an old DBI or DBD driver version actually embedded in
5109 the Perl executable being used.)
5110 DBD driver has not implemented the AutoCommit attribute
5112 The DBD driver implementation is incomplete. Consult the author.
5113 Can't [sg]et %s->{%s}: unrecognised attribute
5115 You attempted to set or get an unknown attribute of a handle. Make
5116 sure you have spelled the attribute name correctly; case is signif‐
5117 icant (e.g., "Autocommit" is not the same as "AutoCommit").
5118
5120 A pure-perl emulation of the DBI is included in the distribution for
5121 people using pure-perl drivers who, for whatever reason, can't install
5122 the compiled DBI. See DBI::PurePerl.
5123
5125 Driver and Database Documentation
5126 Refer to the documentation for the DBD driver that you are using.
5128 Refer to the SQL Language Reference Manual for the database engine that
5130 you are using.
5131 ODBC and SQL/CLI Standards Reference Information
5133 More detailed information about the semantics of certain DBI methods
5135 that are based on ODBC and SQL/CLI standards is available on-line via
5136 microsoft.com, for ODBC, and www.jtc1sc32.org for the SQL/CLI standard:
5137 DBI method ODBC function SQL/CLI Working Draft
5139 ---------- ------------- ---------------------
5140 column_info SQLColumns Page 124
5141 foreign_key_info SQLForeignKeys Page 163
5142 get_info SQLGetInfo Page 214
5143 primary_key_info SQLPrimaryKeys Page 254
5144 table_info SQLTables Page 294
5145 type_info SQLGetTypeInfo Page 239
5146 statistics_info SQLStatistics
5147 For example, for ODBC information on SQLColumns you'd visit:
5149 http://msdn.microsoft.com/library/en-us/odbc/htm/odbcsqlcolumns.asp
5151 If that URL ceases to work then use the MSDN search facility at:
5153 http://search.microsoft.com/us/dev/
5155 and search for "SQLColumns returns" using the exact phrase option. The
5157 link you want will probably just be called "SQLColumns" and will be
5158 part of the Data Access SDK.
5159 And for SQL/CLI standard information on SQLColumns you'd read page 124
5161 of the (very large) SQL/CLI Working Draft available from:
5162 http://jtc1sc32.org/doc/N0701-0750/32N0744T.pdf
5164 Standards Reference Information
5166 A hyperlinked, browsable version of the BNF syntax for SQL92 (plus Ora‐
5168 cle 7 SQL and PL/SQL) is available here:
5169 http://cui.unige.ch/db-research/Enseignement/analyseinfo/SQL92/BNFindex.html
5171 A BNF syntax for SQL3 is available here:
5173 http://www.sqlstandards.org/SC32/WG3/Progression_Documents/Informal_working_drafts/iso-9075-2-1999.bnf
5175 The following links provide further useful information about SQL. Some
5177 of these are rather dated now but may still be useful.
5178 http://www.jcc.com/SQLPages/jccs_sql.htm
5180 http://www.contrib.andrew.cmu.edu/~shadow/sql.html
5181 http://www.altavista.com/query?q=sql+tutorial
5182 Books and Articles
5184 Programming the Perl DBI, by Alligator Descartes and Tim Bunce.
5186 <http://books.perl.org/book/154>
5187 Programming Perl 3rd Ed. by Larry Wall, Tom Christiansen & Jon Orwant.
5189 <http://books.perl.org/book/134>
5190 Learning Perl by Randal Schwartz. <http://books.perl.org/book/101>
5192 Details of many other books related to perl can be found at
5194 <http://books.perl.org>
5195 Perl Modules
5197 Index of DBI related modules available from CPAN:
5199 http://search.cpan.org/search?mode=module&query=DBIx%3A%3A
5201 http://search.cpan.org/search?mode=doc&query=DBI
5202 For a good comparison of RDBMS-OO mappers and some OO-RDBMS mappers
5204 (including Class::DBI, Alzabo, and DBIx::RecordSet in the former cate‐
5205 gory and Tangram and SPOPS in the latter) see the Perl Object-Oriented
5206 Persistence project pages at:
5207 http://poop.sourceforge.net
5209 A similar page for Java toolkits can be found at:
5211 http://c2.com/cgi-bin/wiki?ObjectRelationalToolComparison
5213 Mailing List
5215 The dbi-users mailing list is the primary means of communication among
5217 users of the DBI and its related modules. For details send email to:
5218 dbi-users-help@perl.org
5220 There are typically between 700 and 900 messages per month. You have
5222 to subscribe in order to be able to post. However you can opt for a
5223 'post-only' subscription.
5224 Mailing list archives (of variable quality) are held at:
5226 http://groups.google.com/groups?group=perl.dbi.users
5228 http://www.xray.mpe.mpg.de/mailing-lists/dbi/
5229 http://www.mail-archive.com/dbi-users%40perl.org/
5230 Assorted Related WWW Links
5232 The DBI "Home Page":
5234 http://dbi.perl.org/
5236 Other DBI related links:
5238 http://tegan.deltanet.com/~phlip/DBUIdoc.html
5240 http://dc.pm.org/perl_db.html
5241 http://wdvl.com/Authoring/DB/Intro/toc.html
5242 http://www.hotwired.com/webmonkey/backend/tutorials/tutorial1.html
5243 http://bumppo.net/lists/macperl/1999/06/msg00197.html
5244 http://gmax.oltrelinux.com/dbirecipes.html
5245 Other database related links:
5247 http://www.jcc.com/sql_stnd.html
5249 http://cuiwww.unige.ch/OSG/info/FreeDB/FreeDB.home.html
5250 http://www.connectionstrings.com/
5251 Security, especially the "SQL Injection" attack:
5253 http://www.ngssoftware.com/research/papers.html
5255 http://www.ngssoftware.com/papers/advanced_sql_injection.pdf
5256 http://www.ngssoftware.com/papers/more_advanced_sql_injection.pdf
5257 http://www.esecurityplanet.com/trends/article.php/2243461
5258 http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf
5259 http://www.imperva.com/application_defense_center/white_papers/blind_sql_server_injection.html
5260 http://online.securityfocus.com/infocus/1644
5261 Commercial and Data Warehouse Links
5263 http://www.dwinfocenter.org
5265 http://www.datawarehouse.com
5266 http://www.datamining.org
5267 http://www.olapcouncil.org
5268 http://www.idwa.org
5269 http://www.knowledgecenters.org/dwcenter.asp
5270 Recommended Perl Programming Links
5272 http://language.perl.com/style/
5274 FAQ
5276 Please also read the DBI FAQ which is installed as a DBI::FAQ module.
5278 You can use perldoc to read it by executing the "perldoc DBI::FAQ" com‐
5279 mand.
5280
5282 DBI by Tim Bunce. This pod text by Tim Bunce, J. Douglas Dunlop,
5283 Jonathan Leffler and others. Perl by Larry Wall and the
5284 "perl5-porters".
5285
5287 The DBI module is Copyright (c) 1994-2004 Tim Bunce. Ireland. All
5288 rights reserved.
5289 You may distribute under the terms of either the GNU General Public
5291 License or the Artistic License, as specified in the Perl README file.
5292
5294 The DBI is free Open Source software. IT COMES WITHOUT WARRANTY OF ANY
5295 KIND.
5296 Support
5298 My consulting company, Data Plan Services, offers annual and multi-
5300 annual support contracts for the DBI. These provide sustained support
5301 for DBI development, and sustained value for you in return. Contact me
5302 for details.
5303 Sponsor Enhancements
5305 The DBI Roadmap is available at
5307 <http://search.cpan.org/~timb/DBI/Roadmap.pod>
5308 If your company would benefit from a specific new DBI feature, please
5310 consider sponsoring its development. Work is performed rapidly, and
5311 usually on a fixed-price payment-on-delivery basis. Contact me for
5312 details.
5313 Using such targeted financing allows you to contribute to DBI develop‐
5315 ment, and rapidly get something specific and valuable in return.
5316
5318 I would like to acknowledge the valuable contributions of the many peo‐
5319 ple I have worked with on the DBI project, especially in the early
5320 years (1992-1994). In no particular order: Kevin Stock, Buzz Moschetti,
5321 Kurt Andersen, Ted Lemon, William Hails, Garth Kennedy, Michael Pep‐
5322 pler, Neil S. Briscoe, Jeff Urlwin, David J. Hughes, Jeff Stander, For‐
5323 rest D Whitcher, Larry Wall, Jeff Fried, Roy Johnson, Paul Hudson,
5324 Georg Rehfeld, Steve Sizemore, Ron Pool, Jon Meek, Tom Christiansen,
5325 Steve Baumgarten, Randal Schwartz, and a whole lot more.
5326 Then, of course, there are the poor souls who have struggled through
5328 untold and undocumented obstacles to actually implement DBI drivers.
5329 Among their ranks are Jochen Wiedmann, Alligator Descartes, Jonathan
5330 Leffler, Jeff Urlwin, Michael Peppler, Henrik Tougaard, Edwin Pratomo,
5331 Davide Migliavacca, Jan Pazdziora, Peter Haworth, Edmund Mergl, Steve
5332 Williams, Thomas Lowery, and Phlip Plumlee. Without them, the DBI would
5333 not be the practical reality it is today. I'm also especially grateful
5334 to Alligator Descartes for starting work on the first edition of the
5335 "Programming the Perl DBI" book and letting me jump on board.
5336 The DBI and DBD::Oracle were originally developed while I was Technical
5338 Director (CTO) of the Paul Ingram Group (www.ig.co.uk). So I'd espe‐
5339 cially like to thank Paul for his generosity and vision in supporting
5340 this work for many years.
5341
5343 As you can see above, many people have contributed to the DBI and driv‐
5344 ers in many ways over many years.
5345 If you'd like to help then see <http://dbi.perl.org/contributing> and
5347 <http://search.cpan.org/~timb/DBI/Roadmap.pod>
5348 If you'd like the DBI to do something new or different then a good way
5350 to make that happen is to do it yourself and send me a patch to the
5351 source code that shows the changes. (But read "Speak before you patch"
5352 below.)
5353 Browsing the source code repository
5355 Use http://svn.perl.org/modules/dbi/trunk (basic) or
5357 http://svn.perl.org/viewcvs/modules/ (more useful)
5358 How to create a patch using Subversion
5360 The DBI source code is maintained using Subversion (a replacement for
5362 CVS, see <http://subversion.tigris.org/>). To access the source you'll
5363 need to install a Subversion client. Then, to get the source code, do:
5364 svn checkout http://svn.perl.org/modules/dbi/trunk
5366 If it prompts for a username and password use your perl.org account if
5368 you have one, else just 'guest' and 'guest'. The source code will be in
5369 a new subdirectory called "trunk".
5370 To keep informed about changes to the source you can send an empty
5372 email to svn-commit-modules-dbi-subscribe@perl.org after which you'll
5373 get an email with the change log message and diff of each change
5374 checked-in to the source.
5375 After making your changes you can generate a patch file, but before you
5377 do, make sure your source is still upto date using:
5378 svn update
5380 If you get any conflicts reported you'll need to fix them first. Then
5382 generate the patch file from within the "trunk" directory using:
5383 svn diff > foo.patch
5385 Read the patch file, as a sanity check, and then email it to
5387 dbi-dev@perl.org.
5388 How to create a patch without Subversion
5390 Unpack a fresh copy of the distribution:
5392 tar xfz DBI-1.40.tar.gz
5394 Rename the newly created top level directory:
5396 mv DBI-1.40 DBI-1.40.your_foo
5398 Edit the contents of DBI-1.40.your_foo/* till it does what you want.
5400 Test your changes and then remove all temporary files:
5402 make test && make distclean
5404 Go back to the directory you originally unpacked the distribution:
5406 cd ..
5408 Unpack another copy of the original distribution you started with:
5410 tar xfz DBI-1.40.tar.gz
5412 Then create a patch file by performing a recursive "diff" on the two
5414 top level directories:
5415 diff -r -u DBI-1.40 DBI-1.40.your_foo > DBI-1.40.your_foo.patch
5417 Speak before you patch
5419 For anything non-trivial or possibly controversial it's a good idea to
5421 discuss (on dbi-dev@perl.org) the changes you propose before actually
5422 spending time working on them. Otherwise you run the risk of them being
5423 rejected because they don't fit into some larger plans you may not be
5424 aware of.
5425
5427 A German translation of this manual (possibly slightly out of date) is
5428 available, thanks to O'Reilly, at:
5429 http://www.oreilly.de/catalog/perldbiger/
5431 Some other translations:
5433 http://cronopio.net/perl/ - Spanish
5435 http://member.nifty.ne.jp/hippo2000/dbimemo.htm - Japanese
5436
5438 References to DBI related training resources. No recommendation
5439 implied.
5440 http://www.treepax.co.uk/
5442 http://www.keller.com/dbweb/
5443 (If you offer professional DBI related training services, please send
5445 me your details so I can add them here.)
5446
5448 See the DBI FAQ for a more comprehensive list of FAQs. Use the "perldoc
5449 DBI::FAQ" command to read it.
5450 Why doesn't my CGI script work right?
5452 Read the information in the references below. Please do not post CGI
5454 related questions to the dbi-users mailing list (or to me).
5455 http://www.perl.com/cgi-bin/pace/pub/doc/FAQs/cgi/perl-cgi-faq.html
5457 http://www3.pair.com/webthing/docs/cgi/faqs/cgifaq.shtml
5458 http://www-genome.wi.mit.edu/WWW/faqs/www-security-faq.html
5459 http://www.boutell.com/faq/
5460 http://www.perl.com/perl/faq/
5461 How can I maintain a WWW connection to a database?
5463 For information on the Apache httpd server and the "mod_perl" module
5465 see
5466 http://perl.apache.org/
5468
5470 Apache::DBI by E.Mergl@bawue.de
5471 To be used with the Apache daemon together with an embedded Perl
5472 interpreter like "mod_perl". Establishes a database connection
5473 which remains open for the lifetime of the HTTP daemon. This way
5474 the CGI connect and disconnect for every database access becomes
5475 superfluous.
5476 JDBC Server by Stuart 'Zen' Bishop zen@bf.rmit.edu.au
5478 The server is written in Perl. The client classes that talk to it
5479 are of course in Java. Thus, a Java applet or application will be
5480 able to comunicate via the JDBC API with any database that has a
5481 DBI driver installed. The URL used is in the form
5482 "jdbc:dbi://host.domain.etc:999/Driver/DBName". It seems to be
5483 very similar to some commercial products, such as jdbcKona.
5484 Remote Proxy DBD support
5486 As of DBI 1.02, a complete implementation of a DBD::Proxy driver
5487 and the DBI::ProxyServer are part of the DBI distribution.
5488 SQL Parser
5490 See also the SQL::Statement module, SQL parser and engine.
5491perl v5.8.8 2006-02-07 DBI(3)