The DBperl Relational Database API

Perry is senior programmer for Arco Oil and can be contacted at laspws@aai .arco.com.

DBperl is a Perl 5 package that implements a relational database API for a variety of database engines; among them, Oracle, Informix, Ingres, Interbase, Postgres, Sybase, and Unify 5.0. The seamless combination of Perl 5 and database functionality is a powerful resource for delivering relational data to heterogeneous clients. The possibilities for its use range from two-tier application programming to advanced web development. At Arco Alaska, DBperl has been the focus of several useful applications, and figures prominently in our plans for utilizing Internet technology. The official DBperl archive is at ftp://ftp.demon .co.uk in the /pub/perl/db directory.

Perl is an interpreted language with powerful string-, scalar-, and array-processing capabilities. Since relational database operations are typically text oriented, Perl is well suited to manage dataflow. The goal of the DBperl Group is to create a standardized set of Perl function extensions, based on the SQL model, for manipulating a relational database. This will provide a portable Perl interface to a variety of relational database engines. In theory, says Buzz Moschetti of the DBperl Group, any database engine that implements a dynamic SQL interpreter in its high-level interface can be bolted onto the Perl front end with predictable results.

From the developer's perspective, it is the DBperl API, commonly referred to as the "DBI," that makes this possible. According to DBperl author Tim Bunce, the "DBI API specification defines a set of functions, variables, and conventions that provide a consistent database interface independent of the actual database being used."

DBI specifies the common programming interface for database interaction, and is the core of the DBperl project, although the DBD drivers (vendor-supplied database drivers) perform most of the vendor-specific database engine interactions. DBD drivers exist for Oracle, Sybase, mSQL, Ingres, Informix, Empress, DB2, Quickbase, and Interbase (ftp://ftp. mcqueen.com/ pub/databases/DBI/DBD/<Driver>/DBD -<DRIVER>-x.xx.tar.gz). In C++ terminology, the DBI interface is analogous to a virtual base class. The DBD drivers are analogous to instantiable children of DBI, since they fill in virtual DBI functions with real vendor implementations.

The class structure of DBI is simple yet elegant. The three primary classes are driver, database, and statement. These classes are subclassed from a base class called common. The driver class is rarely used directly by the user. It can create an instance of a database class that manages a single database connection. Instances of the statement class are created from database instances and manage SQL parsing and processing. DBD driver, database, and statement classes are subclassed from the corresponding DBI classes.

A typical DBI-based program is, at the highest level, a Perl script that uses the DBperl package. To use DBperl, you'll need Perl5.001m or later to get DBI and DBD working, since it makes use of the latest object-oriented Perl features. DBI drivers can be found at ftp://ftp.mcqueen .com/pub/databases/DBI/DBI/DBI-x.xx .tar.gz. The methods exposed by the DBI package communicate with the DBI Switch (written by Bunce), which dispatches DBI function calls to the appropriate DBD drivers. The database drivers communicate directly with a specific vendor database such as Oracle or Informix. Oracle's DBD driver, for example, uses OCI (Oracle Call Interface) calls to communicate directly with the Oracle database engine. (For more information on OCI, see "The Oracle Call Interface and C++," by Jeremy Woo-Sam and Tony Murphy, DDJ, November 1995.)

A partial list of the main DBI exposed functions follows.

Connect/Disconnect. The connect method returns a database handle that references a Perl class containing methods for handling transaction-level processes such as commit and rollback, statement preparation, disconnection, and so on. The DBI has a flexible way of handling database connections. There is no concept of a "current connection." This means that simultaneous connections to multiple databases or multiple connections to a single database (if the engine supports it) are allowed. The disconnect method is defined only in the DBD implementations. It supports the expected behavior of each implemented database engine, including rollbacks of uncommitted changes and deallocation of resources.

Record Set Manipulation. The prepare method of a database handle creates a statement handle. The statement handle is encapsulated by a Perl class that has methods for parsing SQL, binding program variables to a statement, fetching rows, and closing cursors. A prepared statement can be executed with the execute method, which is analogous to the open cursor function. It associates bound variables with the corresponding variable placeholders and takes whatever steps are necessary to execute the statement. The fetch method retrieves a single row from the database. Currently, there is no other way to process records than to move through them sequentially. However, when future DBI releases are mapped over ODBC, random cursor row access will certainly be included.

The multirow record sets are processed with DBperl in the usual way; see Example 1. The finish method is analogous to a close cursor function. It releases the resources for a statement.

Single-Statement Execution. DBI provides the do convenience function for executing single SQL statements that do not return rows such as update, insert, and delete. The do function performs a prepare, execute, and finish on these simple statements instead of requiring the user to do so. Similarly, it is fairly trivial to implement a lookup convenience function for retrieving data from single-row queries. In Listing One, for instance, the core of the DBD interface is practically guaranteed to remain the same after the new ODBC version of DBI comes out. That's great news for those of us who have written a lot of DBD code. There are some subtle differences in syntax between Oraperl DBD and DBI; for example, positional parameter representation and the syntax of the do function. Listing Two is the same example, except it is based on Oraperl + Oracle DBD. The preferred interface to Oracle at the moment is the latter example, the Oraperl emulation.

Stored Procedures and BLOBs. The authors of DBI have wisely noted that too many "standards" exist for the stored-procedure API. Although some of the individual DBD drivers may implement them for specific database vendors, stored procedures are not a part of the common DBI interface. DBD developers have the option of implementing a stored procedure method as a private function within the DBD driver, however.

I have noticed a couple of methods in the DBI interface for Binary Large Object (BLOB) support within the statement class. It appears that some consideration has been given to providing the ability to read BLOB files and write them to disk. Since the documentation refers to the DBI readblob method as "experimental," I have not tried this feature, but I plan to when the ODBC version of DBI comes out.

Transaction Management. DBI defines the commit and the rollback methods for transaction management. commit and rollback completely define the scope of a transaction set; that is, no explicit "Start Transaction" or "End Transaction" delimiters are required. An early version of the documentation sparked my interest with the mention of a "savepoint" feature for partial rollback to a certain point in the transaction process. Although this feature is not currently implemented in the DBI code, it would certainly be helpful for large, complex transaction sets with significant potential for midstream failure.

Using DBperl in the Real World

DBperl is at the heart of the Petroleum Engineering Surveillance Package, used at Arco Alaska's Kuparuk River Unit Oil Field for its engineering surveillance programs at the North Slope of Alaska. DBperl programs are executed as the second tier of a three-tier client/server system over a Wide Area Network. Remote users on the WAN request services from the DBperl programs, which are executed on machines tightly connected to the central Oracle database server. The DBperl modules process the data request, compress the output, and transmit it back to the remote site. The result is a reasonably priced solution for data delivery over a slow WAN. DBperl was a critical piece of this system as it allowed for rapid prototyping and efficient database processing. (That the software is free also weighed heavily in its favor.)

We've also implemented a Cost Reporting and Tracking System that uses DBperl to extract data from an Oracle database for business reports. According to Greg Gennette of the technical development team:

We used the power of DBperl to organize accounting information for cost reporting and analysis. This decision was based upon the flexibility of the DBperl package in combination with the multiple subscripted associative perl arrays. The hash indexing of associative arrays means that data can be stored and retrieved with meaningful keys; e.g., "OIL," "1994," "Sub-Total," etc. One of the requirements for the Kuparuk Infomation System (KIS) was that the information needed to be accessed through a web browser. The popular association of Perl and web programming provided further support for the choice of DBperl. We were able to retrieve and categorize all the DBMS data with one fetch loop using multiple subscripted associative arrays. Associative arrays provided multilevel support for subtotaling, accumulation, and page-break logic. Once populated, the associative arrays were retrieved via nested loops with the key function and then written out to static HTML pages. DBperl allowed us to integrate nested database functions without having to resort to tools outside the base Perl language. These DBperl programs are invoked monthly and produce over 800 reports using concise modules that are easily maintained.

This system illustrates the web possibilities of DBperl. Other web-related Perl packages (CGI.pm or cgi-lib.pl, for instance) can be used in combination with DBperl to create dynamic forms using relational information. Listing Three is from the Kuparuk Information System which uses some home-grown HTML interfaces.

The Future of DBperl

Tim Bunce and the DBI crew are launching in a new direction for the DBI interface-the SQL CLI/ODBC API international standard. Refining the DBI interface to the full-featured ODBC interface would require a considerable duplication of effort. Free ODBC driver managers exist (iODBC) and others could be written fairly easily. It seems imperative that ODBC be supported, given the attention it is receiving from the database vendors.

What does this mean to the DBperl users? We can only benefit from an ODBC port. The added flexibility and universality of ODBC will attract a larger user and developer following. Although the majority of the DBI internals will have to be rewritten, much of the higher-level DBD interfaces will remain the same. Another advantage of the ODBC effort is that DBI will be more clearly understood and documented. This is important for commercial acceptance of the tool. Given the quality of the original work by Tim Bunce and the rest of the DBperl team, I have high hopes for the effort and give them my full support.

Example 1: Processing multirow sets.

Prepare
    Execute, Fetch, Fetch, Fetch,... , Finish.
    Execute, Fetch, Fetch, Fetch,... , Finish.
    . . .

Listing One

#!/usr/local/bin/perl -w

use DBI;

### Connect to an Oracle database using the connect method.
$dbh = DBI->connect ( 'T:myMachine:TESTDB', 'testID', 'testPASS', 'Oracle' );
die $DBI::errstr unless $dbh;

### Create an SQL statement with 2 wildcard parameters.
$SQL = "select user_id, login_time from dbperl_users \
    where last_name like ? and first_name like ?";
### Parse the SQL.
$prepSQL = $dbh->prepare ( $SQL );
### Associate two values with the parameters.
$prepSQL->execute ( "Smi%", "J%" );
### Loop through the cursor to retrieve the rows.
while ( ($user_id, $login_time) = $prepSQL->fetchrow ) {
    printf "%s %s\n", $user_id, $login_time ;
}
### Close the cursor.
$prepSQL->finish;
### Execute a single delete statement (Delete all PRO*C users!)
$deleteSQL = "Delete from dbperl_users where preference = ?";
$dbh->do ( $deleteSQL, "PRO*C" );
### Execute a single row query with the lookup function (see below)
### Find the DB preference for the one DBperl user whose name is John Smith.
$pref = &lookup ( $dbh, "Select preference from dbperl_users \
     where last_name = 'Smith' and first_name = 'John'" );
### Disconnect from the database.
$dbh->disconnect;
exit;
### 'Lookup' function for returning 1 row for a single-row query.
sub lookup {
    local ($dbh,$stat,@bounds) = @_;
    local($prepSQL) = $dbh->prepare ( $stat ) ||
        return undef;
    $prepSQL->execute ( @bounds ) ||
        return undef;
    local(@row) = $prepSQL->fetchrow ||
        return undef;
    $prepSQL->finish || warn "Problem closing SQL statement in lookup()";
    @row;
}

#!/usr/local/bin/perl -w

use Oraperl;

### Connect to an Oracle database using the connect method.
$dbh = &ora_login ( 'T:myMachine:TESTDB', 'testID', 'testPASS' );
### Create an SQL statement with 2 wildcard parameters.
$SQL = "select user_id, login_time from dbperl_users \
    where last_name like :1 and first_name like :2"; ### Parse the SQL.
$prepSQL = &ora_open ( $dbh, $SQL );
### Associate two values with the parameters.
&ora_bind ( $prepSQL, "Smi%", "J%" );
### Loop through the cursor to retrieve the rows.
while ( ($user_id, $login_time) = &ora_fetch ( $prepSQL ) ) {
    printf "%s %s\n", $user_id, $login_time ;
}
### Close the cursor.
&ora_close ( $prepSQL );
### Execute a single delete statement (Delete all PRO*C users!)
$deleteSQL = "Delete from dbperl_users where preference = :1";
$deleteStat = $ora_open ( $dbh, $deleteSQL );
&ora_bind ( $deleteStat, "PRO*C" );
&ora_close ( $deleteStat );
### Execute a single row query with the lookup function (see below)
### Find the DB preference for the one DBperl user whose name is John Smith.
$pref = &lookup ( $dbh, "Select preference from dbperl_users \
     where last_name = 'Smith' and first_name = 'John'" );
### Disconnect from the database.
&ora_logoff ( $dbh );

exit;

### 'Lookup' function for returning 1 row for a single-row query.
### Includes the familiar look & feel of old Oraperl modules.
sub lookup {
    local ($dbh,$stat,@bounds) = @_;
    local($prepSQL) = &ora_open ( $stat ) ||
        return undef;
    &ora_bind ( $prepSQL, @bounds ) ||
        return undef;
    local(@row) = &ora_fetch ( $prepSQL ) ||
        return undef;
    &ora_close ( $prepSQL ) || warn "Problem closing SQL statement";
    @row;
}

#-------------------------------------------------------------------
# 1st level:  Facility name (equtes to the page break)
# 2nd level:  Cost Category ( equates to a row w/ 8 columns )
# 3rd level:  8 columns of Date-valued information.
#-------------------------------------------------------------------
{
    ...
### Retrieve Oracle data and stuff into associative array.
    while ( ($fac_name,$desc_txt,$dt,$amt) = &ora_fetch ( $stmt ) ) {
        $amounts{$fac_name}{$desc_txt}{$dt} = $amt;
    }
    &ora_close ( $stmt );
    ...
    &CreateHTML_Index($CurrentYear,"3Prof",$MonthMinus[0], "Cost Summary");
    foreach $fac_name ( sort keys(%amounts) ){
        &CreateHTML_File ($CurrentYear,"3Prof",$MonthMinus[0],             $facility_ids{$fac_name} );
        &WriteFileToHTML_Index($facility_ids{$fac_name},$fac_name);
        &CreateHTML_Title("Monthly Facility Direct Cost Summary for ",
            $fac_name, $MonthMinus[0], $CurrentYear);
        &CreateHTML_Table();
        &CreateHTML_ColumnHeadings (
            "\$000's Gross<BR>Direct Costs by Cost Category",
            $MonthMinus[5], $MonthMinus[4], $MonthMinus[3],
            $MonthMinus[2], $MonthMinus[1], $MonthMinus[0],
            "YTD", "FY PACE" );
        foreach $desc_txt ( sort keys( %{$amounts{$fac_name}} ) ) {
            print FP "<TR>\n";
            $tmp_text = "   " . $desc_test;
            printf FP "<TD><PRE>%s", $tmp_text;
            foreach $dt ( @columnInexes ) {

            ### Use of DBPerl-generated 3-level associative array.
                $_amt_ = $amounts{$fac_name}{$desc_txt}{$dt};
                &PrintAmount( $_amt_ );

            }
        }
        #---------------
        # Print Totals
        #---------------
        printf FP "<TFOOT>\n";
        printf FP "<TR>\n";
        printf FP "<TH ALIGN=LEFT>TOTAL DIRECT COSTS";
        foreach $dt ( @columnIndexes ) {
            $_amt_ = $totals{$fac_name}{$dt};
            &PrintTotal ( $_amt_ );
        }
        &CloseHTML_File();
    }
    &CloseHTML_IndexFile();
}