NAME

DBI - Database independent interface for Perl

SYNOPSIS

  use DBI;

  @driver_names = DBI->available_drivers;
  %drivers      = DBI->installed_drivers;
  @data_sources = DBI->data_sources($driver_name, \%attr);

  $dbh = DBI->connect($data_source, $username, $auth, \%attr);

  $rv  = $dbh->do($statement);
  $rv  = $dbh->do($statement, \%attr);
  $rv  = $dbh->do($statement, \%attr, @bind_values);

  $ary_ref  = $dbh->selectall_arrayref($statement);
  $hash_ref = $dbh->selectall_hashref($statement, $key_field);

  $ary_ref  = $dbh->selectcol_arrayref($statement);
  $ary_ref  = $dbh->selectcol_arrayref($statement, \%attr);

  @row_ary  = $dbh->selectrow_array($statement);
  $ary_ref  = $dbh->selectrow_arrayref($statement);
  $hash_ref = $dbh->selectrow_hashref($statement);

  $sth = $dbh->prepare($statement);
  $sth = $dbh->prepare_cached($statement);

  $rc = $sth->bind_param($p_num, $bind_value);
  $rc = $sth->bind_param($p_num, $bind_value, $bind_type);
  $rc = $sth->bind_param($p_num, $bind_value, \%attr);

  $rv = $sth->execute;
  $rv = $sth->execute(@bind_values);
  $rv = $sth->execute_array(\%attr, ...);

  $rc = $sth->bind_col($col_num, \$col_variable);
  $rc = $sth->bind_columns(@list_of_refs_to_vars_to_bind);

  @row_ary  = $sth->fetchrow_array;
  $ary_ref  = $sth->fetchrow_arrayref;
  $hash_ref = $sth->fetchrow_hashref;

  $ary_ref  = $sth->fetchall_arrayref;
  $ary_ref  = $sth->fetchall_arrayref( $slice, $max_rows );

  $hash_ref = $sth->fetchall_hashref( $key_field );

  $rv  = $sth->rows;

  $rc  = $dbh->begin_work;
  $rc  = $dbh->commit;
  $rc  = $dbh->rollback;

  $quoted_string = $dbh->quote($string);

  $rc  = $h->err;
  $str = $h->errstr;
  $rv  = $h->state;

  $rc  = $dbh->disconnect;

The synopsis above only lists the major methods and parameters.

GETTING HELP

If you have questions about DBI, or DBD driver modules, you can get help from the dbi-users@perl.org mailing list. You can get help on subscribing and using the list by emailing dbi-users-help@perl.org.

To help you make the best use of the dbi-users mailing list, and any other lists or forums you may use, I strongly recommend that you read "How To Ask Questions The Smart Way" by Eric Raymond: http://www.catb.org/~esr/faqs/smart-questions.html.

If you think you've found a bug then please also read "How to Report Bugs Effectively" by Simon Tatham: http://www.chiark.greenend.org.uk/~sgtatham/bugs.html.

The DBI home page at http://dbi.perl.org/ is always worth a visit and includes an FAQ and links to other resources.

Before asking any questions, reread this document, consult the archives and read the DBI FAQ. The archives are listed at the end of this document and on the DBI home page. An FAQ is installed as a the DBI::FAQ manpage module so you can read it by executing perldoc DBI::FAQ. However the DBI::FAQ module is currently (2004) outdated relative to the online FAQ on the DBI home page.

This document often uses terms like references, objects, methods. If you're not familar with those terms then it would be a good idea to read at least the following perl manuals first: the perlreftut manpage, the perldsc manpage, the perllol manpage, and the perlboot manpage.

Please note that Tim Bunce does not maintain the mailing lists or the web page (generous volunteers do that). So please don't send mail directly to him; he just doesn't have the time to answer questions personally. The dbi-users mailing list has lots of experienced people who should be able to help you if you need it. If you do email Tim he's very likely to just forward it to the mailing list.

NOTES

This is the DBI specification that corresponds to the DBI version 1.58 ($Revision: 9678 $).

The DBI is evolving at a steady pace, so it's good to check that you have the latest copy.

The significant user-visible changes in each release are documented in the the DBI::Changes manpage module so you can read them by executing perldoc DBI::Changes.

Some DBI changes require changes in the drivers, but the drivers can take some time to catch up. Newer versions of the DBI have added features that may not yet be supported by the drivers you use. Talk to the authors of your drivers if you need a new feature that's not yet supported.

Features added after DBI 1.21 (February 2002) are marked in the text with the version number of the DBI release they first appeared in.

Extensions to the DBI API often use the DBIx::* namespace. See Naming Conventions and Name Space. DBI extension modules can be found at http://search.cpan.org/search. And all modules related to the DBI can be found at http://search.cpan.org/search.

DESCRIPTION

The DBI is a database access module for the Perl programming language. It defines a set of methods, variables, and conventions that provide a consistent database interface, independent of the actual database being used.

It is important to remember that the DBI is just an interface. The DBI is a layer of "glue" between an application and one or more database driver modules. It is the driver modules which do most of the real work. The DBI provides a standard interface and framework for the drivers to operate within.

Architecture of a DBI Application

             |<- Scope of DBI ->|
                  .-.   .--------------.   .-------------.
  .-------.       | |---| XYZ Driver   |---| XYZ Engine  |
  | Perl  |       | |   `--------------'   `-------------'
  | script|  |A|  |D|   .--------------.   .-------------.
  | using |--|P|--|B|---|Oracle Driver |---|Oracle Engine|
  | DBI   |  |I|  |I|   `--------------'   `-------------'
  | API   |       | |...
  |methods|       | |... Other drivers
  `-------'       | |...
                  `-'

The API, or Application Programming Interface, defines the call interface and variables for Perl scripts to use. The API is implemented by the Perl DBI extension.

The DBI "dispatches" the method calls to the appropriate driver for actual execution. The DBI is also responsible for the dynamic loading of drivers, error checking and handling, providing default implementations for methods, and many other non-database specific duties.

Each driver contains implementations of the DBI methods using the private interface functions of the corresponding database engine. Only authors of sophisticated/multi-database applications or generic library functions need be concerned with drivers.

Notation and Conventions

The following conventions are used in this document:

  $dbh    Database handle object
  $sth    Statement handle object
  $drh    Driver handle object (rarely seen or used in applications)
  $h      Any of the handle types above ($dbh, $sth, or $drh)
  $rc     General Return Code  (boolean: true=ok, false=error)
  $rv     General Return Value (typically an integer)
  @ary    List of values returned from the database, typically a row of data
  $rows   Number of rows processed (if available, else -1)
  $fh     A filehandle
  undef   NULL values are represented by undefined values in Perl
  \%attr  Reference to a hash of attribute values passed to methods

Note that Perl will automatically destroy database and statement handle objects if all references to them are deleted.

Outline Usage

To use DBI, first you need to load the DBI module:

  use DBI;
  use strict;

(The use strict; isn't required but is strongly recommended.)

Then you need to connect to your data source and get a handle for that connection:

  $dbh = DBI->connect($dsn, $user, $password,
                      { RaiseError => 1, AutoCommit => 0 });

Since connecting can be expensive, you generally just connect at the start of your program and disconnect at the end.

Explicitly defining the required AutoCommit behaviour is strongly recommended and may become mandatory in a later version. This determines whether changes are automatically committed to the database when executed, or need to be explicitly committed later.

The DBI allows an application to "prepare" statements for later execution. A prepared statement is identified by a statement handle held in a Perl variable. We'll call the Perl variable $sth in our examples.

The typical method call sequence for a SELECT statement is:

  prepare,
    execute, fetch, fetch, ...
    execute, fetch, fetch, ...
    execute, fetch, fetch, ...

for example:

  $sth = $dbh->prepare("SELECT foo, bar FROM table WHERE baz=?");

  $sth->execute( $baz );

  while ( @row = $sth->fetchrow_array ) {
    print "@row\n";
  }

The typical method call sequence for a non-SELECT statement is:

  prepare,
    execute,
    execute,
    execute.

for example:

  $sth = $dbh->prepare("INSERT INTO table(foo,bar,baz) VALUES (?,?,?)");

  while(<CSV>) {
    chomp;
    my ($foo,$bar,$baz) = split /,/;
        $sth->execute( $foo, $bar, $baz );
  }

The do() method can be used for non repeated non-SELECT statement (or with drivers that don't support placeholders):

  $rows_affected = $dbh->do("UPDATE your_table SET foo = foo + 1");

To commit your changes to the database (when AutoCommit is off):

  $dbh->commit;  # or call $dbh->rollback; to undo changes

Finally, when you have finished working with the data source, you should disconnect from it:

  $dbh->disconnect;

General Interface Rules & Caveats

The DBI does not have a concept of a "current session". Every session has a handle object (i.e., a $dbh) returned from the connect method. That handle object is used to invoke database related methods.

Most data is returned to the Perl script as strings. (Null values are returned as undef.) This allows arbitrary precision numeric data to be handled without loss of accuracy. Beware that Perl may not preserve the same accuracy when the string is used as a number.

Dates and times are returned as character strings in the current default format of the corresponding database engine. Time zone effects are database/driver dependent.

Perl supports binary data in Perl strings, and the DBI will pass binary data to and from the driver without change. It is up to the driver implementors to decide how they wish to handle such binary data.

Perl supports two kinds of strings: unicode (utf8 internally) and non-unicode (defaults to iso-8859-1 if forced to assume an encoding). Drivers should accept both kinds of strings and, if required, convert them to the character set of the database being used. Similarly, when fetching from the database character data that isn't iso-8859-1 the driver should convert it into utf8.

Multiple SQL statements may not be combined in a single statement handle ($sth), although some databases and drivers do support this (notably Sybase and SQL Server).

Non-sequential record reads are not supported in this version of the DBI. In other words, records can only be fetched in the order that the database returned them, and once fetched they are forgotten.

Positioned updates and deletes are not directly supported by the DBI. See the description of the CursorName attribute for an alternative.

Individual driver implementors are free to provide any private functions and/or handle attributes that they feel are useful. Private driver functions can be invoked using the DBI func() method. Private driver attributes are accessed just like standard attributes.

Many methods have an optional \%attr parameter which can be used to pass information to the driver implementing the method. Except where specifically documented, the \%attr parameter can only be used to pass driver specific hints. In general, you can ignore \%attr parameters or pass it as undef.

Naming Conventions and Name Space

The DBI package and all packages below it (DBI::*) are reserved for use by the DBI. Extensions and related modules use the DBIx:: namespace (see http://www.perl.com/CPAN/modules/by-module/DBIx/). Package names beginning with DBD:: are reserved for use by DBI database drivers. All environment variables used by the DBI or by individual DBDs begin with "DBI_" or "DBD_".

The letter case used for attribute names is significant and plays an important part in the portability of DBI scripts. The case of the attribute name is used to signify who defined the meaning of that name and its values.

  Case of name  Has a meaning defined by
  ------------  ------------------------
  UPPER_CASE    Standards, e.g.,  X/Open, ISO SQL92 etc (portable)
  MixedCase     DBI API (portable), underscores are not used.
  lower_case    Driver or database engine specific (non-portable)

It is of the utmost importance that Driver developers only use lowercase attribute names when defining private attributes. Private attribute names must be prefixed with the driver name or suitable abbreviation (e.g., "ora_" for Oracle, "ing_" for Ingres, etc).

SQL - A Query Language

Most DBI drivers require applications to use a dialect of SQL (Structured Query Language) to interact with the database engine. The Standards Reference Information section provides links to useful information about SQL.

The DBI itself does not mandate or require any particular language to be used; it is language independent. In ODBC terms, the DBI is in "pass-thru" mode, although individual drivers might not be. The only requirement is that queries and other statements must be expressed as a single string of characters passed as the first argument to the prepare or do methods.

For an interesting diversion on the real history of RDBMS and SQL, from the people who made it happen, see:

  http://ftp.digital.com/pub/DEC/SRC/technical-notes/SRC-1997-018-html/sqlr95.html

Follow the "Full Contents" then "Intergalactic dataspeak" links for the SQL history.

Placeholders and Bind Values

Some drivers support placeholders and bind values. Placeholders, also called parameter markers, are used to indicate values in a database statement that will be supplied later, before the prepared statement is executed. For example, an application might use the following to insert a row of data into the SALES table:

  INSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?)

or the following, to select the description for a product:

  SELECT description FROM products WHERE product_code = ?

The ? characters are the placeholders. The association of actual values with placeholders is known as binding, and the values are referred to as bind values.

Note that the ? is not enclosed in quotation marks, even when the placeholder represents a string. Some drivers also allow placeholders like :name and :n (e.g., :1, :2, and so on) in addition to ?, but their use is not portable.

With most drivers, placeholders can't be used for any element of a statement that would prevent the database server from validating the statement and creating a query execution plan for it. For example:

  "SELECT name, age FROM ?"         # wrong (will probably fail)
  "SELECT name, ?   FROM people"    # wrong (but may not 'fail')

Also, placeholders can only represent single scalar values. For example, the following statement won't work as expected for more than one value:

  "SELECT name, age FROM people WHERE name IN (?)"    # wrong
  "SELECT name, age FROM people WHERE name IN (?,?)"  # two names

When using placeholders with the SQL LIKE qualifier, you must remember that the placeholder substitutes for