10.4.1.1 Basic SQL accesses

SQL statements can be executed like this (the equivalent of execute immediate in embedded SQL):

$dbh->do("delete from emptable where status != 'active'");
print "Error: $DBI::err .... $DBI::errstr" if $DBI::err;

If you make the same query or a similar-looking query a number of times, you force the system to parse it over and over again. To avoid this overhead, you can compile a parameterized query using prepare, and execute it many times.

The prepare method is given a query with parameter placeholders indicated by "?":

$sth = $dbh->prepare ('insert into emptable (name, age)
                                     values (?,    ?)');

You can execute this statement over and over again using the returned statement handle, each time supplying it an array of values corresponding to each placeholder. These values are sometimes referred to as binding parameters. In fact, do internally prepares and executes the query given to it.

The following piece of code reads employee names and ages from standard input and uses the statement handle created above to insert rows into the database:

while (defined($line = <>)) {
    chomp($line);

    # id, name, age separated by tab
    ($id, $name, $age) = split (/\t/, $line); 
    $sth->execute($id, $name, $age);
    die "Error: $DBI::err .... $DBI::errstr" if $DBI::err;
}

If the field is nullable, you can indicate a null value by passing undef to execute.

10.4.1.2 Select

The following example shows how to retrieve information in bulk, using the SQL select statement:

$cur = $dbh->prepare('select name, age from emptable where age < 40');
$cur->execute();
die "Prepare error: $DBI::err .... $DBI::errstr" if $DBI::err;
while (($name, $age) = $cur->fetchrow) {
    print "Name:$name, Age: $age \n";
}
$cur->finish();

The prepare statement returns a statement handle as before. When executed, this handle is associated internally with an open database cursor and is used to fetch each row returned by the database. fetchrow returns the values corresponding to the fields specified in the select query. finish closes the cursor.

10.4.1.3 Query meta-data

Once a statement is prepared and executed, DBI stores the following pieces of information as attributes of the statement handle:

$DBI::rows

The number of rows affected or returned

$sth->{NUM_FIELDS}

The number of fields returned by a select

$sth->{NUM_PARAMS}

The number of parameters returned by any query

After a select query, the following attributes contain references to arrays of field-specific information:

$sth->{NAME}

Column names returned by the query

$sth->{NULLABLE}

Booleans indicating whether fields are nullable or not

$sth->{TYPE}

Field types

$sth->{PRECISION}

Floating-point precision of field

$sth->{SCALE}

Field lengths

Let us use what we have learned this far and create a Perl replacement for the interactive SQL frontends shipped with most relational databases (programs such as sqlplus and isql). Example 10.1 does this.

use DBI;
$dbname = 'DEMO732'; $user = 'scott'; 
$password = 'tiger'; $dbd = 'Oracle';

$dbh = DBI->connect($dbname,$user,$password,$dbd) ||
       die "Error connecting $DBI::errstr\n";;

while(1) {
    print "SQL> ";                      # Prompt
    $stmt = <STDIN>;
    last unless defined($stmt);
    last if ($stmt =~ /^\s*exit/);
    chomp ($stmt);
    $stmt =~ s/;\s*$//;

    $sth = $dbh->prepare($stmt);
    if ($DBI::err) {
        print STDERR "$DBI::errstr\n";
        next;
    }
    $sth->execute() ;
    if ($DBI::err) {
        print STDERR "$DBI::errstr\n";
        next;
    }
    if ($stmt =~ /^\s*select/i) {
        my $rl_names = $sth->{NAME};         # ref. to array of col. names
        while (@results = $sth->fetchrow) {  # retrieve results
            if ($DBI::err) {
                print STDERR $DBI::errstr,"\n";
                last;
            }
            foreach $field_name (@$rl_names) {
                printf "%10s: %s\n", $field_name, shift @results;
            }
            print "\n";
        }
        $sth->finish;
    }
}
$dbh->commit;

The script prepares and executes all statements. If the statement is a select query, it fetches each row and prints out each value annotated by the corresponding column name. Note that fetchrow returns a reference to an array of values in a scalar context.

10.4.1.4 Transactions

When a database connection is created by using connect, DBI (or the database) automatically starts a transaction. To end a transaction, you can use the commit or rollback methods on the database handle; a new transaction is immediately started implicitly. Distributed transactions, as defined by the XA standard, are not supported.

10.4.1.5 Special functions

Driver-specific functions can be called by using the func method of the database handle. For example, the mSQL database driver provides an internal function called _ListFields, which returns information about columns in a table. It is invoked as follows:

$ref = $dbh->func($table, '_ListFields');

Clearly, using func is a nonportable solution.

10.4.1.6 What DBI does not provide

It is instructive to list common database tasks for which DBI does not currently provide an interface. This is not meant as a slight on the DBI/DBD implementors; it is just an indicator of the fact that databases vary enormously in every aspect not touched by the standards committees.

Meta-data

DBI provides a $dbh->tables() method to fetch a list of all accessible table names. However, there is no function to return the names of columns in a given table. Fortunately, there is a simple, portable solution. Because a select query returns meta-information, we can use a dummy query that we know will definitely not fetch any rows but will execute successfully:

    select * from $table where 1 = 0;

The where clause is perfectly valid, but the condition will never succeed. The "*" makes it return all the columns, which we can study using $sth's attributes, as explained earlier in the section Section 10.4.1.3, "Query meta-data."

Creating databases

Database APIs differ widely in how databases (not tables) are created; you have to use vendor-specific APIs or tools for this. Once a database is set up, however, DBI can be used to create or drop tables in that database.

Inserts/creates from arrays

Bulk insertion or updates of data from arrays is not a standard SQL CLI[5] feature. If there is truly a large amount of data to be inserted, you are likely better off dumping the data into a file and using the appropriate bulk copy utility (such as Sybase's bcp) to transfer it to the database at high speeds. (For even better performance, drop the indexes before loading the data and recreate them later.)

[5] Call Level Interface - another name for the standardized C API that all RDBMS vendors are expected to support.

Stored procedures and triggers

Stored procedures and triggers are very different from vendor to vendor. All Perl database modules such as oraperl and sybperl provide such access to their native database facilities, but DBI does not attempt to generalize any of them. Please refer to the module documentation for details or to the DBI web site [6] for suggested approaches.

Uniform error numbers

DBI may be portable, but it doesn't provide a portable set of common error codes. For example, assume that you want to create a table if it is not already present. You might try something like this:

$dbh->do("create table emptable (id   char(15), name char(40),
                                 age  integer)");

If $DBI::err contains an error code, you don't want to take it seriously if it says something like "table/view already present." Unfortunately, if you are using Oracle, this error code is 955, and for Sybase it is something entirely different. There goes portability out of the window!