Making the Most of PHP with DB2

So you're developing a PHP app using DB2. You've downloaded the version of the ibm_db2 PECL extension appropriate for your version of PHP. Now what?

Saying Hello to the World yet again hardly scratches the surface of what you're capable of. Using the ibm_db2 PECL extension, you have the ability to do many great and powerful things, some of which may surprise you.

I've collected here the various PHP methods available to you, grouped by general purpose and functionality. More detailed explanations for each of these functions may be found in the PHP documentation or the online Zend manual.

So, you want to get started quickly... The ibm_db2 PECL extension is enabled by default in Zend Core for IBM. Go get it!

(If you want an introduction to this whole topic, read "Develop IBM Cloudscape and DB2 Universal Database applications with PHP" by Dan Scott. You can also visit the IBM developerWorks PHP Zone at http://www.ibm.com/developerworks/opensource/top-projects/php.html .)

Essential Functions
First and foremost, the ibm_db2 PECL extension gives you a set of general database management functions that allow you to connect to your database, execute SQL, and troubleshoot any problems you run into along the way. In this section, I've included those functions you'll use pretty much no matter what you're trying to accomplish.

Read the Sidebar: How to Use These Examples

To illustrate use of these functions, here is some basic code for connecting to the database "ANGELINA" and pulling a list of movies. DB2-related functions are in boldface.

<?php
$database = 'angelina';
$user = 'db2admin';
$password = 'db2admin';

$conn = db2_connect($database, $user, $password);

if ($conn) {
   echo "Connection succeeded.<br />";

   $sql = "SELECT moviename FROM movies ORDER BY releasedate";
   $stmt = db2_prepare($conn, $sql);
   db2_execute($stmt);
   $movielist = array();
   $i=0;

   while (db2_fetch_row($stmt)) {
      $movielist[$i] = db2_result($stmt, 0);
      echo "$movielist[$i]<br />";
      $i += 1;
   }

   echo count($movielist) . " movies listed.<br /><br />";

   db2_close($conn);

}
else {
   echo "Connection failed.<br />";
   echo db2_conn_errormsg();
}
?>

Result:

Connection succeeded.

Lookin' to Get Out
Cyborg 2
Hackers
Playing God
Gia
The Bone Collector
Girl, Interrupted
Lara Croft: Tomb Raider
Mr. & Mrs. Smith
9 movies listed.

db2_connect ( string database, string username, string password [, array options] )
Creates a new database connection. The command operates in slightly different ways depending on whether the database is currently cataloged or uncataloged when the connection is attempted. This shows the versatility of the PECL functions in communicating with a database that hasn't already been cataloged on the server prior to executing the script.

If the database is currently cataloged, you can connect with it by entering its name, username, and password. If it's not cataloged, then username and password are both null while the name parameter becomes a much longer list of variables, including database name, hostname, port, username, and password, in this format:

DRIVER={IBM DB2 ODBC DRIVER};DATABASE=database;HOSTNAME=hostname;
PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password;

Optionally, you can manually disable AUTOCOMMIT by specifying it in the db2_connect parameter list, thusly:

$database = 'angelina';
$user = 'usr';
$password = 'pwd';
$options = array('autocommit' => DB2_AUTOCOMMIT_OFF);
$conn = db2_connect($database, $user, $password, $options);

db2_pconnect ( string database, string username, string password [, array options] )
Returns a persistent database connection. Similar to db2_connect, this operates in essentially the same way except that a successful db2_close command doesn't actually close the connection, but rather leaves it open to service the next db2_pconnect request.

As advised by the documentation, you probably shouldn't use this with AUTOCOMMIT turned off, because the db2_commit and db2_rollback commands affects every persistent connection currently using that same underlying DB2 connection.

db2_exec ( resource connection, string statement [, array options] )
Prepares and executes a SQL statement in a single step. Though it works fine, this function is not as efficient as using the db2_prepare / db2_execute combo described below, particularly when issuing the same SQL statement with different parameters. Also, it introduces some security risks if you plan on incorporating PHP variables into the SQL statement. If you do use db2_exec, however, here's what it looks like:

$sql = "SELECT moviename FROM movies WHERE costar='Jon Voight'";
$stmt = db2_exec($conn, $sql);

while (db2_fetch_row($stmt)) {
   echo db2_result($stmt, 0) . "<br />";
}

Result:

Lookin' to Get Out
Lara Croft: Tomb Raider

As you can see, the other key difference between db2_exec and db2_execute is that db2_exec returns a resource statement, whereas db2_execute a boolean, the resource statement having already been returned by db2_prepare (below).

db2_prepare ( resource connection, string statement [, array options] )
Prepares a SQL statement for execution. Parameter markers (? characters) can be included to represent input, output, or input/output parameters. These parameters can then be passed to the prepared statement using either an array passed to db2_execute or by using the db2_bind_param statement. Here's an example of an INSERT statement that uses an array of parameters:

$movie = array('Alexander', 2004, 'Colin Farrell', 6);

$sql = 'INSERT INTO movies (moviename, releasedate,
   costar, rating) VALUES (?, ?, ?, ?)';

$stmt = db2_prepare($conn, $sql);
if ($stmt) {
   $result = db2_execute($stmt, $movie);
   if ($result) {
       print "Alexander added to the list of films.";
   }
}

Result:

Alexander added to the list of films.

db2_execute ( resource statement [, array parameters] )
Executes a statement prepared by db2_prepare. See the examples above for how it works. It returns a TRUE or FALSE indicating success but operates on the existing resource statement returned by db2_prepare, which can then be managed using some of the fetch functions. Optionally, you can add an array of parameters.

db2_close ( resource connection )
Closes a non-persistent database connection and frees resources back to the database server. If trying to close a persistent database connection, opened using db2_pconnect, then db2_close is ignored and the connection remains available. It returns TRUE or FALSE indicating success.

Error Functions
I know it's rare, but sometimes code just doesn't work. That's why it's good to have a few troubleshooting tools in your belt. When an error occurs, you usually get two error codes to work with, SQLSTATE and SQLCODE. To retrieve these codes after an error, the DB2 PECL gives you four different error functions. When troubleshooting, you can look up the error codes in the command line processor by typing "? <value>". You can also find a list of SQLSTATE and SQLCODE values in IBM's DB2 Information Center.

db2_conn_error ( [resource connection] )
Returns the SQLSTATE code for a failed connection attempt. If db2_connect returns FALSE, then use this to capture just the SQLSTATE code. For a more detailed error message, use db2_conn_errormsg.

db2_conn_errormsg ( [resource connection] )
Returns an error message, including SQLSTATE and SQLCODE, for a failed connection attempt. If db2_connect returns FALSE, use this to retrieve a somewhat detailed explanation.

In the example below, I've deliberately misspelled the name of the database in order to generate a connection error.

$database = 'angelinna';
$user = 'db2admin';
$password = 'db2admin';

$conn = db2_connect($database, $user, $password);

if ($conn) {
   echo "Connection succeeded.<br />";
   db2_close($conn);
}
else {
   echo "Connection failed.<br />";
   echo "error: " . db2_conn_error() . "<br />";
   echo "errormsg: " . db2_conn_errormsg() . "<br /><br />";
}

Result:

Connection failed.
error: HY009
errormsg: [IBM][CLI Driver] CLI0124E Invalid argument value. SQLSTATE=HY009 SQLCODE=-99999

db2_stmt_error ( [resource statement] )
Returns the SQLSTATE code generated by a SQL statement error. This is similar to db2_conn_error but shows up after an execution error has occurred.

db2_stmt_errormsg ( [resource statement] )
Returns a more detailed error message, including SQLSTATE, SQLCODE, and an explanation.

In this example, I've used the same code as the sample for db2_prepare (with a different movie), but with another intentional error. I'm trying to shove a text value into the releasedate field, which is an Integer field. Note how the statement is prepared but not executed. Also note that in order for this particular error to show up, it was necessary to pass the resource statement as a parameter.

$movie = array('Shark Tale', 'June 2004', 'Will Smith', 6);

$sql = 'INSERT INTO movies (moviename, releasedate, costar,
   rating) VALUES (?, ?, ?, ?)';

$stmt = db2_prepare($conn, $sql);
if ($stmt) {
   $result = db2_execute($stmt, $movie);
   if ($result) {
      echo "Shark Tale added.<br />";
   } else {
      echo "exec error: " . db2_stmt_error($stmt) . "<br />";
      echo "exec errormsg: " . db2_stmt_errormsg($stmt) . "<br />";
   }
} else {
   echo "prep error: " . db2_stmt_error($stmt) . "<br />";
   echo "prep errormsg: " . db2_stmt_errormsg($stmt) . "<br />";
}

Result:

Warning: db2_execute() [function.db2-execute]: Statement Execute Failed in
c:\Inetpub\wwwroot\dbtest\php_methods.php on line 104
exec error: 22005
exec errormsg: [IBM][CLI Driver] CLI0112E Error in assignment. SQLSTATE=22005

Transaction Functions
The ibm_db2 PECL extension also includes a few methods specifically for dealing with transactions.

db2_autocommit ( resource connection [, bool value] )
Returns the AUTOCOMMIT state for the specified result set, or sets it to a new value if the optional boolean parameter is included. The AUTOCOMMIT value for a result set, normally defined during the connection process as TRUE or ON by default, determines whether or not changes to the data will take place immediately (TRUE) or when the entire transaction is explicitly committed (FALSE).

If no boolean parameter is included, then db2_autocommit returns a 0 for false or 1 for true. If setting a new AUTOCOMMIT state, then the function returns either a TRUE or FALSE, depending on whether or not the new state was successfully assigned.

db2_commit ( resource connection )
Commits the current transaction. If AUTOCOMMIT has been set to FALSE, then you'll want to use this to explicitly commit changes to the data. If AUTOCOMMIT is currently TRUE, then this isn't necessary. This function returns a TRUE or FALSE, based on its success.

db2_rollback ( resource connection )
Cancels the current transaction and begins a new one. You can only use this if AUTOCOMMIT is currently set to FALSE.

In the following example, AUTOCOMMIT is toggled to demonstrate the effect of updating a single record. Note that when AUTOCOMMIT is turned off, a SELECT query run on an updated but uncommitted record will return the new information, even though the transaction can still be rolled back.

echo "AUTOCOMMIT is currently " . db2_autocommit($conn) . "<br />";

//Prepare two SQL statements.
$updsql = "UPDATE movies SET rating = ? WHERE moviename = 'Gia'";
$upd = db2_prepare($conn, $updsql);
$selsql = "SELECT rating FROM movies WHERE moviename = 'Gia'";
$sel = db2_prepare($conn, $selsql);

//Change the rating to 8.
$rating = array(8);
$result = db2_execute($upd, $rating);
$result = db2_execute($sel);
$rec = db2_fetch_array($sel);
echo "Rating is now " . $rec[0] . "<br />";

//Change the rating back to 7.
$rating = array(7);
$result = db2_execute($upd, $rating);
$result = db2_execute($sel);
$rec = db2_fetch_array($sel);
echo "Rating is now " . $rec[0] . "<br />";

//Turn off AUTOCOMMIT.
db2_autocommit($conn, DB2_AUTOCOMMIT_OFF);
echo "AUTOCOMMIT is currently " . db2_autocommit($conn) . "<br />";

//Change the rating to 9.
$rating = array(9);
$result = db2_execute($upd, $rating);
$result = db2_execute($sel);
$rec = db2_fetch_array($sel);
echo "Rating is now " . $rec[0] . "<br />";

//Commit the update.
db2_commit($conn);
$result = db2_execute($sel);
$rec = db2_fetch_array($sel);
echo "Rating is now " . $rec[0] . "<br />";

//Change the rating to 7.
$rating = array(7);
$result = db2_execute($upd, $rating);
$result = db2_execute($sel);
$rec = db2_fetch_array($sel);
echo "Rating is now " . $rec[0] . "<br />";

//Rollback the update.
db2_rollback($conn);
$result = db2_execute($sel);
$rec = db2_fetch_array($sel);
echo "Rating is now " . $rec[0] . "<br />";

Result:

AUTOCOMMIT is currently 1
Rating is now 8
Rating is now 7
AUTOCOMMIT is currently 0
Rating is now 9
Rating is now 9
Rating is now 7
Rating is now 9

Additional Statement Preparation Options
Two PHP functions, db2_cursor_type and db2_bind_param, don't quite fit into other categories but are terribly handy when preparing statements.

db2_cursor_type ( resource statement )
Returns the cursor type currently in use by the specified statement resource, either a 0 for forward-only or a 1 for scrollable. When creating a resource statement, via either db2_prepare or db2_exec, you can set the cursor type with an optional parameter. Use one of the DB2 constants, either DB2_FORWARD_ONLY or DB2_SCROLLABE.

If not specified, cursor type defaults to forward-only, which is much faster, but limited when it comes to fetch options (see below). Scrollable cursors, which can only be used with DB2 databases, allow you to fetch rows non-sequentially.

The example below shows the result of leaving this option blank or specifying cursor type while using db2_exec. Note that the fetch function used, db2_fetch_array, allows you to pluck a particular row out of the result set, but only if that result set has a scrollable cursor. If you try to specify the row number in both examples, you'll get an error message after the first, forward-only, statement, is executed.

$sql = "SELECT DISTINCT costar FROM movies";
$stmt = db2_exec($conn, $sql);
$row = db2_fetch_array($stmt);
echo "$row[0]<br />";
echo "Cursor type is " . db2_cursor_type($stmt) . "<br />";

$stmt = db2_exec($conn, $sql, array('cursor' => DB2_SCROLLABLE));
$row = db2_fetch_array($stmt, 5);
echo "$row[0]<br />";
echo "Cursor type is " . db2_cursor_type($stmt) . "<br />";

Result:

Brad Pitt
Cursor type is 0
Faye Dunaway
Cursor type is 1

db2_bind_param ( resource statement, int parameter-number, string variable-name [, int parameter-type [, int data-type [, int precision [, int scale]]]] )
Binds a PHP variable to one of the parameters in your SQL statement. When you prepare a SQL statement (using db_prepare) that includes parameter markers, you can assign these parameters to a variable either before or after the variable has been given a value.

The db2_bind_param statement requires the prepared statement, the parameter number (starting with 1, not 0), and the name of the PHP variable. You can optionally specify the type of parameter, either DB2_PARAM_IN, DB2_PARAM_OUT, or DB2_PARAM_INOUT, as well as its variable type, precision, and scale.

$sql = "SELECT moviename, releasedate FROM movies
   WHERE releasedate < ? AND releasedate > ?
   ORDER BY releasedate";
$stmt = db2_prepare($conn, $sql);

$beforeyear = 1999;
db2_bind_param($stmt, 1, "beforeyear", DB2_PARAM_IN);
db2_bind_param($stmt, 2, "afteryear", DB2_PARAM_IN);
$afteryear = 1994;

if (db2_execute($stmt)) {
   while ($row = db2_fetch_array($stmt)) {
       echo "$row[0], $row[1]<br />";   
   }
}

Result:

Hackers, 1995
Playing God, 1997
Gia, 1998

Fetch Functions
Once you've defined, prepared, and executed your SQL statement, you have a result set with which to work. To actually use that data, it must be retrieved, or fetched.

Each of these fetch functions accepts a resource statement as a required parameter. Most of them also take an optional row number integer. If a row number is given, that row is fetched. Otherwise, you get the next available row. As noted in the explanation of db2_cursor_type, however, row numbers can only be specified when using scrollable cursor types. (See db2_cursor_type for more information on how to define a cursor type as scrollable.)

In the following extended example, which demonstrates several of the fetch functions, you'll want to note a few things:

• In PHP, row numbering starts with 1, while column numbering starts with 0, by default.
• Column names must be capitalized to match the format returned by the database server. Because it follows the SQL standards, DB2 automatically converts SQL identifiers, like column names, to upper case unless they were enclosed with double quotation marks when you created the table in the first place.
• Notice how the result set pointer is moved to row 2 in the example for db2_fetch_both, then automatically advanced to row 3 in the example for db2_fetch_object, resulting in the same listing as when row 3 was explicitly requested for db2_fetch_array.
  
  

  
  $sql = "SELECT * FROM movies ORDER BY moviename";
  $stmt = db2_prepare($conn, $sql, array('cursor' => DB2_SCROLLABLE));
  if (db2_execute($stmt)) {
     $row = db2_fetch_array($stmt, 3);
     echo "$row[0], $row[1], $row[2], $row[3]<br />";
  
     $row = db2_fetch_assoc($stmt, 5);
     echo $row['MOVIENAME'] . ", ";
     echo $row['RELEASEDATE'] . "<br />";
  
     $row = db2_fetch_both($stmt, 2);
     echo "$row[1], " . $row['RELEASEDATE'] . ", costarring ";
     echo $row['COSTAR'] . " (rating: $row[4])<br />";
  
     $mov = db2_fetch_object($stmt);
     echo "$mov->MOVIENAME, starring {$mov->COSTAR}<br />";
  
     db2_fetch_row($stmt, 7);
     echo db2_result($stmt, 1) . "<br />";
  
  } else {
     echo db2_stmt_errormsg($stmt) . "<br />";
  }
  

Result:

14, Girl, Interrupted, 1999, Winona Ryder
Lara Croft: Tomb Raider, 2001
Gia, 1998, costarring Faye Dunaway (rating: 9)
Girl, Interrupted, starring Winona Ryder
Mr. & Mrs. Smith

db2_fetch_array ( resource statement [, int row_number] )
Returns a row of the result set, either the next available row or an optionally specified row, in the form of an array indexed by column position (starting with 0).

db2_fetch_assoc ( resource statement [, int row_number] )
Returns either the next available or a specific row from the result set, in the form of an associative array indexed by column name.

db2_fetch_both ( resource statement [, int row_number] )
Returns the next available or a specific row from the result set, indexed by both column position (starting with 0) and column name. In other words, it does the job of both db2_fetch_array and db2_fetch_assoc. The drawback, however, is that it takes more memory than either of these two methods.

db2_fetch_object ( resource statement [, int row_number] )
Returns the next available or specific row as an object, with each column in the row as one of its properties, by name.

db2_fetch_row ( resource statement [, int row_number] )
Advances the result set pointer to the next or specified row without retrieving that row data. This is used in conjunction with db2_result, which then retrieves the data.

db2_result ( resource statement , mixed column )
Returns the value of the specified column of the current row. Used in conjunction with db2_fetch_row.

db2_next_result ( resource statement )
Returns the next result set from a stored procedure that returns multiple result sets. Each of these result sets can be accessed using any of the above-referenced fetch functions. (For more information on this one, see the documentation for db2_next_result .)

Free Functions
The last two functions aren't used very often, but are good to know about. They both free resources before the end of your PHP script. When the PHP script finishes executing, resources are automatically freed back to the server and the database. However, you can force that to happen earlier using these two functions.

db2_free_result ( resource statement )
Frees resources currently allocated to the specified result set.

db2_free_stmt ( resource statement )
Frees resources currently allocated to the specified statement.

In the example below, note how the first two calls to db2_fetch_array work fine. The third, however, crashes. That's because the $stmt variable is no longer defined.

$sql = "SELECT * FROM movies ORDER BY moviename";
$stmt = db2_prepare($conn, $sql);
db2_execute($stmt);

$row = db2_fetch_array($stmt);
echo "$row[1], $row[2], $row[3]<br />";

$row = db2_fetch_array($stmt);
echo "$row[1], $row[2], $row[3]<br />";

db2_free_stmt($stmt);

$row = db2_fetch_array($stmt);
echo "$row[1], $row[2], $row[3]<br />";

Result:

Cyborg 2, 1993, Elias Koteas
Gia, 1998, Faye Dunaway


Warning: db2_fetch_array() [function.db2-fetch-array]: Fetch Failure …

Summary
The ibm_db2 PECL extension also includes a ton of metadata functions that return information about the structure of the database itself. But the functions included here should help you explore the power of your database of choice for your PHP Web Applications.