Basic Program Flow

The program flow for accessing DB2 and retrieving data from the database is similar to that of CLI (shown in Figure 13.1). There is still an initialization phase, transaction processing phase, and a disconnecting and termination phase. These phases take on a slightly different form than that of CLI because the DBI methods encapsulate much of the initialization and termination phases for you.

The Perl DBI module supports only dynamic SQL. When you need to execute a statement multiple times, you can improve the performance of your Perl DB2 applications by issuing a prepare call to prepare the statement. This method is described later during the transaction phase.

For current information on the restrictions of the version of the DBD::DB2 driver that you install on your workstation, refer to the CAVEATS file in the DBD::DB2 driver package.

Initialization

Within a Perl script, you need to activate the DBI environment before any methods from the module can be used. After this module has been made available for the script, then the DBD::DB2 module is automatically loaded during the connection method. In fact, many of the initialization steps that are normally required for a CLI application are automatically performed during the connection method. This includes setting up the environment handle and the connection handle. Looking at the following sample, you can see that the return value from the connection method is an assigned connection handle:

#!/usr/bin/perl
use DBI;

my $database='dbi:DB2:db2mall';
my $user='';
my $password='';

my $dbhandle = DBI->connect($database, $user, $password) or die "Can't connect to
 $database: $DBI::errstr";

The first line is standard for all Perl scripts and tells the OS that the following script should be interpreted by Perl, found in the directory path /usr/bin/perl.

The line

Use DBI;

is included to enable Perl to load the DBI module.

The DBI module automatically loads the DBD::DB2 driver when you create a database handle using the DBI->connect statement.

my $dbhandle = DBI->connect($database, $user, $password)

Where:

• $dbhandle— Represents the database handle returned by the connect statement.

• $database— Represents a DB2 alias cataloged in your DB2 database directory. The database name part "DBI:DB2" tells the connection method that you want to load the database driver for DB2. If Oracle were the back-end database, then the string would look like "DBI:Oracle".

• $userID— Represents the user ID used to connect to the database.

• $password— Represents the password for the user ID used to connect to the database.

Transaction Processing

After a database handle has been created, you can use that handle to either execute statements immediately (similar to the CLI function SQLExecDirect()) or to create and prepare a statement handle that will then be executed. This executed statement handle returns a result set that can then be retrieved into the Perl script.

To immediately execute an SQL statement, use the Perl DBI method do() provided by the $dbhandle returned from the connect statement. For example:

$dbhandle->do("INSERT INTO table1 VALUES (value1)");

To first prepare a statement before executing it, use the prepare() method provided by the $dbhandle. This method will return a statement handle which can then be executed using the execute() method provided by the statement handle.

my $sthandle = $dbhandle->prepare(
     'INSERT INTO table1 VALUES (value1)'),
my $rc = $sthandle->execute();

The execute() method returns the error code for the execute call.

To enable executing a prepared statement using different input values for specified fields, the Perl DBI module enables you to prepare and execute a statement using parameter markers. To include a parameter marker in an SQL statement, use the question mark (?) character.

The following Perl code creates a statement handle that accepts a parameter marker for the value of the VALUES clause of the INSERT statement. The code then executes the statement twice using two different values to replace the parameter marker.

   my $sthandle = $dbhandle->prepare(
      'INSERT INTO table1 VALUES (?)'
      );

   my $rc = $sthandle->execute($value1);

.
.
.

   my $rc = $sthandle->execute($value2);

A statement handle is required if data from a result fetch is needed by the Perl application. To gain a statement handle, an SQL statement must first be prepared and then executed. The result set can then be retrieved using the fetchrow() method of the statement handle. The Perl DBI returns a row as an array with one value per column. For example, you can prepare the following statement, execute it, and then fetch the results:

my $sthandle = $dbhandle->prepare(
     'SELECT col1, col2 FROM table1 WHERE col1 = ?'),

my $rc = $sthandle->execute($val1);

print "Query will return $sthandle->{NUM_OF_FIELDS} fields.

";
print "$sth->{NAME}->[0]: $sth->{NAME}->[1]
";

while (($col1, $col2) = $sthandle->fetchrow()) {
      print "$col1: $col2
";
}

Error Handling

To return the SQLSTATE associated with a Perl DBI database handle or statement handle, call the state method. For example, to return the SQLSTATE associated with the database handle $dbhandle, include the following Perl statement in your application:

my $sqlstate = $dbhandle->state;

To return the SQLCODE associated with a Perl DBI database handle or statement handle, call the err method. To return the message for an SQLCODE associated with a Perl DBI database handle or statement handle, call the errstr method. For example, to return the SQLCODE associated with the database handle $dbhandle, include the following Perl statement in your application:

my $sqlcode = $dbhandle->err;

Disconnecting and Termination

The termination phase is simple in Perl. Once finished with a statement handle, all that is required is to call the finish() method. Once finished with the database handle, call the disconnect() method. This method will free the database handle and any memory associated with the connection as well as finish any network connect that may have been established with the database. For example:

$sthandle->finish();
$dbhandle->disconnect();