The DBI Class (Programming the Perl DBI)

In this section, we cover the DBI class methods, utility functions, and the dynamic attributes associated with generic DBI handles.

A.3.1. DBI Class Methods

The following methods are provided by the DBI class:

connect

$dbh = DBI->connect($data_source, $username, $password)
          || die $DBI::errstr;
$dbh = DBI->connect($data_source, $username, $password, \%attr)
          || die $DBI::errstr;

connect establishes a database connection, or session, to the requested $data_source . Returns a database handle object if the connection succeeds. Use $dbh->disconnect to terminate the connection.

If the connect fails (see below), it returns undef and sets both $DBI::err and $DBI::errstr. (It does not set $!, etc.) You should generally test the return status of connect and print $DBI::errstr if it has failed.

Multiple simultaneous connections to multiple databases through multiple drivers can be made via the DBI. Simply make one connect call for each database and keep a copy of each returned database handle.

The $data_source value should begin with dbi:driver_name:. The driver_name specifies the driver that will be used to make the connection. (Letter case is significant.)

As a convenience, if the $data_source parameter is undefined or empty, the DBI will substitute the value of the environment variable DBI_DSN. If just the driver_name part is empty (i.e., the $data_source prefix is dbi::), the environment variable DBI_DRIVER is used. If neither variable is set, then connect dies.

Examples of $data_source values are:

dbi:DriverName:database_name
dbi:DriverName:database_name@hostname:port
dbi:DriverName:database=database_name;host=hostname;port=port

There is no standard for the text following the driver name. Each driver is free to use whatever syntax it wants. The only requirement the DBI makes is that all the information is supplied in a single string. You must consult the documentation for the drivers you are using for a description of the syntax they require. (Where a driver author needs to define a syntax for the $data_source, it is recommended that he or she follow the ODBC style, shown in the last example above.)

If the environment variable DBI_AUTOPROXY is defined (and the driver in $data_source is not Proxy) then the connect request will automatically be changed to:

dbi:Proxy:$ENV{DBI_AUTOPROXY};dsn=$data_source

and passed to the DBD::Proxy module. DBI_AUTOPROXY is typically set as "hostname=...;port=...". See the DBD::Proxy documentation for more details.

If $username or $password are undefined (rather than just empty), then the DBI will substitute the values of the DBI_USER and DBI_PASS environment variables, respectively. The DBI will warn if the environment variables are not defined. However, the everyday use of these environment variables is not recommended for security reasons. The mechanism is primarily intended to simplify testing.

DBI->connect automatically installs the driver if it has not been installed yet. Driver installation either returns a valid driver handle, or it dies with an error message that includes the string install_driver and the underlying problem. So DBI->connect will die on a driver installation failure and will only return undef on a connect failure, in which case $DBI::errstr will hold the error message.

The $data_source argument (with the "dbi:...:" prefix removed) and the $username and $password arguments are then passed to the driver for processing. The DBI does not define any interpretation for the contents of these fields. The driver is free to interpret the $data_source, $username, and $password fields in any way, and supply whatever defaults are appropriate for the engine being accessed. (Oracle, for example, uses the ORACLE_SID and TWO_TASK environment variables if no $data_source is specified.)

The AutoCommit and PrintError attributes for each connection default to "on." (See AutoCommit and PrintError for more information.) However, it is strongly recommended that you explicitly define AutoCommit rather than rely on the default. Future versions of the DBI may issue a warning if AutoCommit is not explicitly defined.

The \%attr parameter can be used to alter the default settings of PrintError, RaiseError , AutoCommit, and other attributes. For example:

$dbh = DBI->connect($data_source, $user, $pass, {
      PrintError => 0,
      AutoCommit => 0
});

You can also define connection attribute values within the $data_source parameter. For example:

dbi:DriverName(PrintError=>0,Taint=>1):...

Individual attribute values specified in this way take precedence over any conflicting values specified via the \%attr parameter to connect.

Where possible, each session ($dbh ) is independent from the transactions in other sessions. This is useful when you need to hold cursors open across transactions -- for example, if you use one session for your long lifespan cursors (typically read-only) and another for your short update transactions.

For compatibility with old DBI scripts, the driver can be specified by passing its name as the fourth argument to connect (instead of \%attr):

$dbh = DBI->connect($data_source, $user, $pass, $driver);

In this "old-style" form of connect, the $data_source should not start with dbi:driver_name:. (If it does, the embedded driver_name will be ignored). Also note that in this older form of connect, the $dbh->{AutoCommit} attribute is undefined, the $dbh->{PrintError} attribute is off, and the old DBI_DBNAME environment variable is checked if DBI_DSN is not defined. Beware that this "old-style" connect will be withdrawn in a future version of DBI.

connect_cached (NEW )

$dbh = DBI->connect_cached($data_source, $username, $password)
          || die $DBI::errstr;
$dbh = DBI->connect_cached($data_source, $username, $password, \%attr)
          || die $DBI::errstr;

connect_cached is like connect, except that the database handle returned is also stored in a hash associated with the given parameters. If another call is made to connect_cached with the same parameter values, then the corresponding cached $dbh will be returned if it is still valid. The cached database handle is replaced with a new connection if it has been disconnected or if the ping method fails.

Note that the behavior of this method differs in several respects from the behavior of persistent connections implemented by Apache::DBI.

Caching can be useful in some applications, but it can also cause problems and should be used with care. The exact behavior of this method is liable to change, so if you intend to use it in any production applications you should discuss your needs on the dbi-users mailing list.

The cache can be accessed (and cleared) via the CachedKids attribute.

available_drivers

@ary = DBI->available_drivers;
@ary = DBI->available_drivers($quiet);

Returns a list of all available drivers by searching for DBD::* modules through the directories in @INC. By default, a warning is given if some drivers are hidden by others of the same name in earlier directories. Passing a true value for $quiet will inhibit the warning.

data_sources

@ary = DBI->data_sources($driver);
@ary = DBI->data_sources($driver, \%attr);

Returns a list of all data sources (databases) available via the named driver. The driver will be loaded if it hasn't been already. If $driver is empty or undef, then the value of the DBI_DRIVER environment variable is used.

Data sources are returned in a form suitable for passing to the connect method (that is, they will include the dbi:$driver: prefix).

Note that many drivers have no way of knowing what data sources might be available. These drivers return an empty or incomplete list.

trace

DBI->trace($trace_level)
DBI->trace($trace_level, $trace_filename)

DBI trace information can be enabled for all handles using the trace DBI class method. To enable trace information for a specific handle, use the similar $h->trace method described elsewhere.

Trace levels are as follows:

0: Trace disabled.
1: Trace DBI method calls returning with results or errors.
2: Trace method entry with parameters and returning with results.
3: As above, adding some high-level information from the driver and some internal information from the DBI.
4: As above, adding more detailed information from the driver. Also includes DBI mutex information when using threaded Perl.
5 and above: As above, but with more and more obscure information.

Trace level 1 is best for a simple overview of what's happening. Trace level 2 is a good choice for general purpose tracing. Levels 3 and above (up to 9) are best reserved for investigating a specific problem, when you need to see "inside" the driver and DBI.

The trace output is detailed and typically very useful. Much of the trace output is formatted using the neat function, so strings may be edited and truncated.

Initially, trace output is written to STDERR. If $trace_filename is specified, the file is opened in append mode and all trace output (including that from other handles) is redirected to that file. Further calls to trace without a $trace_filename do not alter where the trace output is sent. If $trace_filename is undefined, then trace output is sent to STDERR and the previous trace file is closed.

A.3. The DBI Class

A.3.1. DBI Class Methods

A.3.2. DBI Utility Functions

A.3.3. DBI Dynamic Attributes