A.2.1. Architecture of a DBI Application

Figure A-1. DBI application architecture

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 to be concerned with drivers.

A.2.2. Notation and Conventions

$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.

A.2.3. Outline Usage

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 the 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.

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>) {
  chop;
  my ($foo,$bar,$baz) = split /,/;
      $sth->execute( $foo, $bar, $baz );
}

The do() method can be used for non-repeated, non-SELECT statements (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

$dbh->disconnect;

A.2.4. General Interface Rules and Caveats

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 native 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.

Most databases that understand multiple character sets have a default global charset. Text stored in the database is, or should be, stored in that charset; if not, then that's the fault of either the database or the application that inserted the data. When text is fetched, it should be automatically converted to the charset of the client, presumably based on the locale. If a driver needs to set a flag to get that behavior, then it should do so; it should not require the application to do that.

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

Non-sequential record reads are not supported in this version of the DBI. In other words, records can be fetched only 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.

A.2.5. Naming Conventions and Name Space

It is of the utmost importance that driver developers use only 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.).

ado_     DBD::ADO
best_    DBD::BestWins
csv_     DBD::CSV
db2_     DBD::DB2
f_       DBD::File
file_    DBD::TextFile
ib_      DBD::InterBase
ing_     DBD::Ingres
ix_      DBD::Informix
msql_    DBD::mSQL
mysql_   DBD::mysql
odbc_    DBD::ODBC
ora_     DBD::Oracle
proxy_   DBD::Proxy
solid_   DBD::Solid
syb_     DBD::Sybase
tuber_   DBD::Tuber
xbase_   DBD::XBase

A.2.6. SQL -- A Query Language

A.2.7. Placeholders and Bind Values

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.

When using placeholders with the SQL LIKE qualifier, you must remember that the placeholder substitutes for the whole string. So you should use "... LIKE ? ..." and include any wildcard characters in the value that you bind to the placeholder.