Sidebar 1: Commands

Core Functions

These are the most commonly used functions, used to access and manipulate data.

Command: use

Requires: Module name

Returns: Loads the requested module into Perl-space

Example: use DBI;

The use command is actually the Perl command that loads external modules; however, you need to load the DBI module before you can access it. So use DBI; should appear early in every DBI-based Perl script.

Command: connect

Requires: Data source, username, and password (minimum)

Returns: Database handle

Example: $dbh = DBI->connect('dbi:Oracle:db01', 'scott/tiger');

Database handles are created with the connect command. The connect string varies from database to database, and so there are many variants. However, each requires at least two strings, the first specifying the DBD driver (the Oracle driver in the example), and the second a username/password pair. Other databases may require the username and password as separate arguments, like this:

$dbh = DBI->connect('dbi:Pg', 'scott', 'tiger');

You can do nothing with the database without a valid database handle. Also, you can create multiple database handles - one for each unique database.

Note: If the database name is not specified in the connect string, the Oracle driver uses the database specified in the TWO_TASK or ORACLE_SID environment variable. So, before connecting, specify the database with the Perl ENV variable, like this:

$ENV{TWO_TASK} = 'dbname';
$dbh = DBI->connect('dbi:Oracle:', 'scott/tiger')

This technique may work with other databases as well, if the database uses an environment variable to specify the database. Some database drivers accept database specification in the connect string - for instance, you can connect to the Postgres database "foo" like this:

$dbh = DBI->connect('dbi:Pg:dbname=foo');

Check the DBD-specific Perl documentation.

% perldoc DBD::Pg

Command: quote

Requires: A string

Returns: The string, prepared for insertion into the database

Example: $string = $dbh->quote("O'Leary's Burgers and Brew");

Some strings are not properly formed for insertion into the database. This command quotes any strange characters (such as "'" in the example) and ensures the string is quoted properly. Use this function liberally on any Perl variables before using them in a database statement.

Command: do

Requires: Database statement

Returns: Number of rows affected, or error code

Example: $rv = $dbh->do(q{ DELETE FROM my_tables });

The do command sends a database statement to the database for immediate execution. Use this for non-select statements that are executed only once.

Command: prepare

Requires: Database statement

Returns: Statement handle

Example: $sth = $dbh->prepare(q{ SELECT table_name FROM all_tables });

The prepare command simply associates a database statement with a statement handle. Also, some databases validate the statement and prepare an execution path. The prepare command should never execute the statement, just prepare to execute it. In practice, this is not always true. For instance, the Oracle driver executes data definition statements when creating or dropping tables. These few exceptions should not be a problem, though.

The prepare command may also use placeholders. A placeholder may appear as a database value, and looks like this:

$sth = $dbh->prepare(q{
        SELECT * FROM     all_tables
                 WHERE    table_name LIKE ? });

or

$sth = $dbh->prepare(q{
        INSERT INTO       addressbook (street, city)
               VALUES     (?, ?) });

The placeholders are bound at execution.

Command: execute

Requires: Placeholder values (optional)

Returns: Number of rows affected, or error code

Example: $rv = $sth->execute;

This command executes the previously prepared statement. It returns the number of rows affected, or UNDEF if an error occurs. Note that even if the return code is 0, this is treated as true by Perl. It simply means that no rows were affected.

For SELECT statements, execute does not return the number of rows affected, because this is often not known in advance. A successful execution will still return TRUE, and an error will result in UNDEF.

If the prepared statement has placeholders, the execute command must bind values to the placeholders. So, using the second "prepare" example above, an execute statement may look like this:

$rv = $sth->execute('Cathedral Way', 'Sitka');

Command: bind_param

Requires: Placeholder

Returns: Error code

Example: $rc = $sth->bind_param(1, 'Cathedral Way');

The bind_param command binds a value to a placeholder before execution. This is useful in any situation where execution of a prepared statement is delayed. For example, this would work in subroutines that take a statement handle as an argument, where the statements require the same type of information bound to the same placeholders.

Command: fetchrow_array

Requires: Previously executed statement handle

Returns: Array consisting of one row of data

Example: @row = $sth->fetchrow_array;

After executing a prepared select statement, use this statement to extract the results from the database, one row at a time. The results are returned in the same order specified in the select statement.

while (($city, $street) = $table_handle->fetchrow_array) {
        &do_stuff ($city, $street);
}

A Note on Placeholders

The prepare command has one important feature: placeholders. Placeholders allow the creation of generic SQL statements that are modified dynamically.

$sth = $dbh->prepare(q{
        SELECT * FROM     all_tables
                 WHERE    table_name LIKE ?
                      AND owner      LIKE ? });
$return_value = $sth->execute("%SYS%", "SYSTEM");
@row_array = $sth->fetchrow_array;

In this example, we are only interested in the SYSTEM tables that have "SYS" in them somewhere. The "?" is a placeholder, and must be bound during statement execution. In this fashion, a single statement is prepared once and executed many times with different values.

Placeholders can only represent single, scalar values. A statement that requires a list or array will not take a placeholder - so, the following statement will not work:

$sth = $dbh->prepare(q{
        SELECT * FROM    all_tables
                 WHERE   table_name IN (?) });  ;# Does NOT work

Also, a statement using a placeholder must be validated by the database before the placeholder is bound with a value. Therefore, you can't use a placeholder to reference a command, table, or column name. So, the following two statements will not work:

;# These two statements will not work!
$sth = $dbh->prepare(q{ SELECT ? FROM all_tables }); ;# Won't work
$sth = $dbh->prepare(q{ SELECT * FROM ? });          ;# Won't work

Some DBD drivers allow numbered or named placeholders. For example, the Oracle driver allows named placeholders, like this:

$sth = $dbh->prepare(q{
        SELECT * FROM    all_tables
                 WHERE   table_name like :tname });
$sth->bind_param("tname", "%SYS%");

Placeholder naming varies from driver to driver, so this code is not portable. However, in many cases using named placeholders results in much cleaner, easier-to-read code.

These examples use a single quote (q{..}) technique to avoid quoting conflicts with database statements that use standard quotes (" and '). Using this quote, Perl passes the statement untouched to the DBI. If you use the double-quote operator, qq{..}, Perl will interpolate all the variables in the statement. This allows dynamic creation of database statements, like this:

$command = "SELECT * FROM";
$tablename = "All_tables";
$sth = $dbh->prepare(q{ $command $table });  # This is wrong
$sth = $dbh->prepare(qq{ $command $table }); # This will work

So, why use binding variables instead of variable interpolation? It takes much longer to prepare a statement than it does to bind variables into a pre-prepared statement. In many cases, it's much more efficient to prepare a single statement and bind and execute that statement many times.

Because of the limits on binding variables, variable interpolation allows greater flexibility. Since you can build queries on-the-fly, you are not limited to predefined queries.

Error Conditions

There are three commands that provide information after an error: state, errstr, and err. Use these to provide feedback after an unsuccessful command.

Command: state

Requires: Nothing

Returns: The SQLSTATE error for the last database function called

Example: $state = $dbh->state;

This returns the standard SQLSTATE error code of the last database function. Some DBD drivers do not support the SQLSTATE standard; these drivers will return the general error code, S1000, for all errors.

Command: errstr

Requires: Nothing

Returns: The error string for the last database function called

Example: $error = $dbh->errstr;

This command simply provides a human-readable string of the error state of the last database function called. Usually, this is used after an error condition, like this:

$sth->execute or die "Can't execute SQL: $dbh->errstr\n";

Command: err

Requires: Nothing

Returns: Native database error code

Example: $errorcode = $dbh->err;

This is just like the errstr command, but returns the native database error instead of a human-readable descriptive string.

$sth->execute or die "Database error code: $dbh->err\n";

Transaction Management Most databases support the concept of transaction management; changes are not permanent until the session is "committed." Until the session is committed, the program or user has the option to "roll back," or discard, all changes since the last commit.

These are the commands that affect transaction management.

Attribute: AutoCommit

Requires: Flag indicating AutoCommit state

Returns: AutoCommit state

Example: $dbh->{AutoCommit} = 0;

The DBI has two ways of handling transactions. They are either automatically committed upon successful completion, which is known as "autocommit" mode, or the DBI waits for an explicit commit before making the changes permanent.

The AutoCommit attribute tells the DBI how to behave. If the attribute is true, the DBI will autocommit every transaction; if it is false, it will not commit any transaction until it recieves an explicit commit command. The default mode is to AutoCommit; to change to full transactional mode, the Perl program should unset AutoCommit like this:

$dbh->{AutoCommit} = 0;

Checking the state of the AutoCommit attribute is as easy as reading the attribute, like so:

$state = $dbh->{AutoCommit};

Command: commit

Requires: Nothing

Returns: Error code

Example: $rc = $dbh->commit;

This command merely tells the DBI interface to commit all transactions.

Command: rollback

Requires: Nothing

Returns: Error code

Example: $rc = $dbh->rollback;

This command merely tells the DBI interface to discard all transactions that have not yet been committed.