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! |
| Read the Sidebar: How to Use These Examples |
<?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
db2_connect ( string database, string username, string password [, array options] )
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;
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] )
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] )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
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] )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.";
}
}
Alexander added to the list of films.
db2_execute ( resource statement [, array parameters] )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 )db2_pconnect, then db2_close is ignored and the connection remains available. It returns TRUE or FALSE indicating success.db2_conn_error ( [resource connection] )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] )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.db2_stmt_error ( [resource statement] )db2_conn_error but shows up after an execution error has occurred.db2_stmt_errormsg ( [resource statement] )
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 indb2_autocommit ( resource connection [, bool value] )
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 )
db2_rollback ( resource connection )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 1db2_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 )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 Pittdb2_bind_param ( resource statement, int parameter-number, string variable-name [, int parameter-type [, int data-type [, int precision [, int scale]]]] )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
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.)
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 />";
}
db2_fetch_array ( resource statement [, int row_number] )
db2_fetch_assoc ( resource statement [, int row_number] )
db2_fetch_both ( resource statement [, int row_number] )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] )
db2_fetch_row ( resource statement [, int row_number] )db2_result, which then retrieves the data.
db2_result ( resource statement , mixed column )db2_fetch_row.
db2_next_result ( resource statement )db2_next_result .)
db2_free_result ( resource statement )
db2_free_stmt ( resource 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 />";