![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Introducing PHP: Hypertext Processor
PHP: Hypertext Processor (PHP) is a powerful and increasingly popular server-side scripting language for creating Web content. Portability is one of the primary reasons for PHP's popularity:
- You can compile and run PHP on Microsoft Windows, Linux, BSD, Macintosh OS X, and UNIX servers.
- PHP can be tightly integrated with most popular Web servers, including Apache and Microsoft IIS, or it can be used as a standalone CGI interpreter.
- The source code for PHP is freely available, and you are free to write and distribute PHP applications for both commercial and non-commercial use.
The open source community that has developed around, and which contributes to, the PHP language is supportive and enthusiastic. As a result of their efforts, the PHP language offers an impressive collection of extensions offering features ranging from XML transforms and on-the-fly generation of images and Adobe Portable Document Format (PDF) files to support for SOAP clients and servers.
After a brief introduction to the PHP scripting language, this article describes how to compile PHP as a dynamically loaded Apache module with DB2 client support. The article concludes with several examples that demonstrate how to connect to, insert data into, and select data from an IBM Cloudscape or IBM DB2 Universal Database server using PHP. This article updates my previous article to cover the major version advances in every aspect of the stack: from PHP 4 to PHP 5, Apache 1.3.x to 2.0.x, DB2 UDB Version 7 to Version 8, and from Linux distributions based on the 2.4 kernel to the 2.6 kernel.
PHP is primarily a server-side scripting language that enables you to embed application logic within HTML pages, or create entire HTML pages from PHP functions. When the Web server receives a request for a PHP page, control passes to the PHP engine. The PHP engine loads the PHP page, executes any PHP functions within the page, then returns the resulting HTML to the Web server.
To call a PHP function within a Web page, you simply embed the PHP functions within the normal HTML source.
PHP functions are embedded in HTML-style tags that begin with ?php and end with
?. For example, <?php echo 'Hello!'; ?> demonstrates
a call to the PHP function echo(). To include more than one function within the
PHP tag, separate the functions by a semicolon (;) character.
Of course, you can include many PHP tags in a single PHP script. To demonstrate how easy it is to write Web pages using PHP, here's a short script that prints a sequence of numbers starting and ending at arbitrary values.
Listing 1. Print sequence function: print.php
<?php function print_sequence ($start, $stop, $increment) {
if ($start > $stop) {
return(FALSE);
}
elseif ($increment <= 0) {
return(FALSE);
}
else {
for ($i = $start; $i < $stop; $i = $i + $increment) {
print "$i ";
}
}
return(TRUE);
}
?>
<html><head>
<title>Counting from x to y</title>
</head><body bgcolor="white">
<h1>Counting from x to y</h1>
<p>Counting from 1 to 10 by 1: <?php print_sequence(1, 10, 1)?></p>
<p>Counting from 2 to 20 by 2: <?php print_sequence(2, 20, 2)?></p>
</body>
</html> |
Many current PHP applications, in contrast, separate their code into functions and
generate HTML from those functions. This approach makes it much easier to untangle
the sequence of calls that generate the eventual output for your PHP program. In
the following example, we have moved the formerly raw HTML into a function of its
own named print_html(), which is called as the only
executed statement within the PHP program.
Listing 2. Print sequence function: print_better.php
<?php
function print_sequence ($start, $stop, $increment) {
if ($start > $stop) {
return(FALSE);
}
elseif ($increment <= 0) {
return(FALSE);
}
$numbers = "<p>Counting from $start to $stop by $increment: ";
for ($i = $start; $i < $stop; $i = $i + $increment) {
$numbers .= "$i ";
}
$numbers .= "</p>\n";
return($numbers);
}
function print_html() {
$html = '<html><head>
<title>Counting from x to y</title>
</head><body bgcolor="white">
<h1>Counting from x to y</h1>'
. print_sequence(1, 10, 1)
. print_sequence(2, 20, 2)
. '
</body>
</html>';
return($html);
}
echo print_html();
?> |
Another advantage of combining this approach with collecting PHP functions into modules is that you can easily standardize the header and footer for your PHP pages into a separate module. Changing the look and feel of your complete PHP site can be accomplished by simply modifying the standard header and footer functions in one module instead of altering every individual PHP page on your site. There is no absolute right or wrong method: finding the right balance between encapsulating PHP output into functions and inserting PHP code into HTML templates is a personal choice. You just need to be aware of the advantages of each approach, and follow the approach that best meets your needs.
Installing and configuring the DB2 client
This article assumes that you are running a DB2 Universal Database server or an IBM Cloudscape database in Network Server mode. To connect to a DB2 or Cloudscape database server, the Unified ODBC extension for PHP relies on the Call Level Interface (CLI) layer of the DB2 client. The DB2 client must be installed on the same computer as the PHP interpreter. To begin, download the DB2 Runtime Client from the DB2 support site. Ensure you choose the appropriate client for your operating system and 'bitness' level.
Installing the DB2 client on Linux
- Install the DB2 client, ensuring that you select the application development headers and libraries.
- When you create a DB2 instance, choose a user name and password appropriate for your needs. For the purposes of this article, we will assume the defaults of user db2inst1 with password ibmdb2.
- Confirm that you have correctly installed the application development component by ensuring that /home/db2inst1/sqllib/include/ holds files like sqlcli1.h.
Installing the DB2 client on Windows
The PHP project offers precompiled binaries for the Windows operating systems. These binaries include a version of Unified ODBC which has been compiled against the Windows ODBC Driver Manager. This lets us trade off a fraction of our performance for an install of PHP with DB2 and Cloudscape support that requires no compile steps. Install the DB2 client for Windows and proceed to the next step, Setting up a connection to a database.
Setting up a connection to a database
- (Linux only): Inherit the DB2 instance environment into your shell session with the following command:
bash$ source /home/db2inst1/sqllib/db2profile
- Catalog the server you want to connect to. For example, to catalog
a DB2 or Cloudscape server named 'db2air.toronto.ibm.com' running on port 1527, issue the following command:
bash$ db2 CATALOG TCPIP NODE airnode REMOTE db2air.toronto.ibm.com SERVER 1527
The node name 'airnode' helps you to distinguish it from other database servers in your catalog.
- Catalog the database you want to connect to. For example, to catalog
a database called 'SAMPLE' on a database server with the node name 'airnode', issue the following command:
- Cloudscape databases: You must specify that the Cloudscape database will perform the authentication:
bash$ db2 CATALOG DB SAMPLE AT NODE airnode AUTHENTICATION SERVER
- DB2 databases: DB2 supports a variety of client/server authentication mechanisms. Check with your
administrator for the correct authentication value if the default does not work:
bash$ db2 CATALOG DB SAMPLE AT NODE airnode
- Cloudscape databases: You must specify that the Cloudscape database will perform the authentication:
- (Windows only): Catalog the database as a System ODBC data source:
bash$ db2 CATALOG SYSTEM ODBC DATA SOURCE SAMPLE
- Connect to the database to ensure that the connection was properly cataloged:
bash$ db2 CONNECT TO SAMPLE USER username USING password
Installing and configuring PHP with Apache 2
The PHP community has stated that PHP can be used in production environments with Apache 2, as long as you use the prefork MPM to avoid thread-safety issues. This is good news, because most modern Linux distributions only ship with Apache 2!
Installing and configuring PHP with Apache 2 (Linux)
The PHP project does not offer installable binaries on Linux, so for the latest Unified ODBC support you will have to download and compile the PHP source. Most modern Linux distributions, however, do ship with Apache 2, so these instructions will target an Apache 2 web server. To compile PHP, your Linux distribution must include at least the apache2-devel (httpd-devel on Red Hat Enterprise Linux 4), autoconf, automake, bison, flex, gcc, and libxml2-devel packages.
- To connect to DB2 or Cloudscape databases, the Apache 2 Web server must inherit
the DB2 client instance environment. Edit /etc/sysconfig/apache2, /etc/sysconfig/httpd,
/etc/init.d/apache2, or /etc/init.d/httpd on your system to include the following line:
source /home/db2inst1/sqllib/db2profile
- Download the source for the latest version of PHP. The version used in testing and developing this article was PHP 5.0.3.
- Extract the files from the tarball:
bash$ tar xjf php-5.0.3.tar.bz2
- Configure the PHP source with IBM DB2 support in Unified ODBC and specify that you are creating a version of PHP for the Apache 2 Web server:
bash$ cd php-5.0.3 && ./configure --with-ibm-db2=/home/db2inst1/sqllib \ --with-apxs2=/usr/sbin/apxs
- Build and install PHP:
bash$ make && su -c 'make install'
If all goes well, you will be asked for your root password and your new PHP binaries will be installed in the /usr/local/lib/php/ directory.
If you receive an error message while building the binaries, you may be missing a required development package on your Linux distribution, or you may have specified the incorrect location of your DB2 instance.
If you receive an error message while installing the binaries, PHP may not have understood the Apache configuration files on your distribution of choice. To work around this problem, modify the following line in the Makefile to move install-sapi to the end of the list:
Makefile: original line
install_targets = install-sapi install-cli install-pear \ install-build install-headers install-programs |
Makefile: modified line
install_targets = install-cli install-pear install-build \ install-headers install-programs install-sapi |
The PHP make install command tries to update your Apache 2 configuration files on your behalf. However, some Linux distributions heavily modify the configuration files, and in those cases your intervention is required. On SuSE Linux Professional 9.2, for example, you must create a new file named php5.conf in the /etc/apache2/conf.d/ directory and add the following lines:
php5.conf settings
LoadModule php5_module /usr/lib/apache2/libphp5.so AddType application/x-httpd-php php |
Now restart the Apache Web server by issuing the command /etc/init.d/apache2 restart
or /etc/init.d/httpd restart, depending on your Linux distribution. If your
Linux distribution offers SELinux support,
you may be required to modify the SELinux policy or disable SELinux support to enable the Apache user to inherit
the instance user's environment. For example, on Red Hat Enterprise Linux 4 the Apache Web server runs as the
apache user, and the default SELinux policy prevents the apache user from reading the /home/db2inst1/sqllib/db2profile
environment script. Issuing the command setenforce 0 disables SELinux temporarily and
will enable you to start the Apache Web server.
Installing and configuring PHP with Apache 2 (Windows)
The PHP project makes the life of the Windows developer easy by offering ready-to-deploy binaries with a complete set of extensions. Even better, Unified ODBC support is built into the PHP binary itself. To install PHP on Windows with a default installation of Apache 2:
- Download the Windows binaries for the latest version of PHP. The version used in testing and developing this article was PHP 5.0.3. Choose the ZIP file, rather than the Windows installer, as the ZIP file includes the Unified ODBC extension.
- Unzip the files. For the purposes of this article, we'll assume that you unzipped the files into the C:\php\ directory.
- Copy the C:\php\php.ini-recommended file into the C:\php\ directory as a new file named php.ini. The recommended configuration provides a relatively safe set of defaults for your PHP environment.
- Edit the C:\Program Files\Apache Group\Apache2\conf\httpd.conf file to add the following lines:
php5.conf settingsLoadModule php5_module 'c:/php/php5apache2.dll' AddType application/x-httpd-php .php PHPIniDir 'c:/php'
- Restart Apache.
Writing PHP scripts that connect to DB2 Universal Database
To demonstrate some of the basic operations you can perform with PHP and DB2, we'll create a set of Web pages to help you manage a database table that contains data on a set of authors. First we will create a table to hold our author data, then write a PHP script that enables us to browse through existing authors. Finally, we will write a script that enables users to add their own author data to the table.
Connecting to a database using PHP
To use this database, we need to insert some data into the table. We could issue some Data Manipulation Language (DML) statements, but because we have PHP installed, we will create and use a simple PHP form for inserting new records in the database. All of the following PHP scripts use the Unified ODBC functions described in the PHP documentation.
Before we can insert data, we must create a database connection within a PHP script. Once we have confirmed that the connection is successful by returning a list of the tables with our user name, we can re-use that connection function within the rest of the scripts that we write.
The syntax for connecting to a database using PHP is as follows:
int odbc_connect() (string dsn, string user, string password [, int cursor_type]); |
where:
- dsn: The name of the database as registered in the DB2 catalog.
- user: The name of the user that will connect to the database.
- password: The password for user.
- cursor_type: Optional arguments to specify cursor behavior.
The AUTHOR table contains four columns: LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, and AUTHOR_ID, a unique identifier generated by the database server that serves as the primary key of this table. The Data Definition Language (DDL) statement contained in the following PHP script creates this table:
Listing 3. Create the AUTHOR table (create_table.php)
<?php
// connect to the database
$conn = odbc_connect('SAMPLE', 'db2inst1', 'ibmdb2');
// define our SQL
$sql = 'CREATE TABLE author (last_name VARCHAR(32) NOT NULL,
first_name VARCHAR(32) NOT NULL,
middle_initial VARCHAR(1),
author_id INTEGER GENERATED ALWAYS AS IDENTITY,
PRIMARY KEY (author_id))';
// issue our SQL statement directly
odbc_exec($conn, $sql);
// close the database connection
odbc_close($conn);
?> |
To create this table, copy the PHP code into a file called create_table.php. Inherit the DB2 instance environment and run the program from the command line by issuing the following commands:
bash$ source /home/db2inst1/sqllib/db2profile bash$ php create_table.php |
As you can see, PHP isn't just a Web scripting language--you can write convenient command-line scripts as well. Notice that the username and password is exposed in the script. Not only is this a rather insecure practice to follow--if you have to debug a script, you may inadvertently share your password with another developer--it is also quite inconvenient when you have to change your password in every script. Instead, we can create our own module db2lib.php with a common function to connect to our sample database SAMPLE with user name 'db2inst1' and password 'ibmdb2'. Then by including db2lib.php, we can use the following function in our scripts:
Listing 4. Simple database connection function: db2lib.php
<?php
function dbconnect($verbose=FALSE) {
$dbname = "SAMPLE";
$username = "db2inst1";
$password = "ibmdb2";
// odbc_connect returns 0 if the connection attempt fails;
// otherwise it returns a connection ID used by other ODBC functions
$dbconn = odbc_connect($dbname, $username, $password);
if (($verbose == TRUE) && ($dbconn == 0)) {
echo("Connection to database failed.");
$sqlerror = odbc_errormsg($dbconn);
echo($sqlerror);
}
return($dbconn);
}
function my_header($title) {
$html = "<html>
<head><title>$title</title></head>
<body>
<h1 style='text-align: center; margin-bottom: 0px; padding-bottom: 0px;'>$title</h1>
<div style='text-align: center; background: cyan; color: black;'>
<a href='prepinsert.php'>Insert</a> | <a href='db2select.php'>Select</a></div>
<hr />";
return $html;
}
?> |
Note that the $verbose parameter for dbconnect()
defaults to FALSE if no value has been specified. If the script successfully connects to the database,
it simply returns the database connection handle to the caller.
If the connection fails and you have asked for verbose output, the script tells you it failed to
connect to the database and calls the odbc_errormsg() function to
print the error message returned from the database.
Save the code in a file named db2lib.php so that we can include the function in the rest of our scripts.
Connecting to uncataloged databases
In most cases, you should catalog the database connections as described in Setting up a connection to a database so that you can test the connection from the command line. However, it is possible to connect to an uncataloged DB2 or Cloudscape database using the IBM DB2 ODBC driver connection string. To connect using a driver connection string, pass the following string to the DSN argument of the odbc_connect() method, substituting the correct hostname, database name, and port number:
DRIVER={IBM DB2 ODBC DRIVER};HOSTNAME=localhost;DATABASE=dbname;PROTOCOL=TCPIP;PORT=1527; |
Once you have successfully connected to the database, you can start doing some
more interesting work such as inserting, updating, and retrieving data.
You can issue a simple SQL statement--one that contains no parameter markers
for variable input--using the odbc_exec() function.
The following script inserts new rows into the AUTHOR table:
Listing 5. Static INSERT statement: db2insert.php
<?php
// include the dbconnect() and my_header() functions
include_once("db2lib.php");
echo(my_header"INSERT data into AUTHOR table");
$author_insert = "INSERT INTO author" .
"(last_name, first_name, middle_initial)" .
"VALUES('Scott', 'Daniel', 'B')";
$verbose = TRUE;
$dbconn = dbconnect($verbose);
if ($dbconn != 0) {
// odbc_exec returns 0 if the statement fails; otherwise
// it returns a result set ID
$result = odbc_exec($dbconn, $author_insert);
if ($result == 0) {
echo("INSERT statement failed.");
$sqlerror = odbc_errormsg($dbconn);
echo($sqlerror);
}
else {
echo("Successfully inserted one row.");
}
}
else {
echo("<p>Connection failed.</p>");
}
echo("</body></html>");
?>
|
Issuing SQL statements with parameter markers
In the real world, of course, you probably wouldn't write a script to repeatedly insert
the same row into the database. You might write a script that provides HTML forms for
you to ease your data entry tasks (or the data entry tasks of the users of your Web site).
Fortunately, PHP makes handling HTML form input easy. When a PHP script is the target action
of an HTML <form> element, CGI parameters are
automatically converted into members of the $_GET[]
or $_POST[] superglobal arrays
available within the PHP script. The array in which the CGI parameters are held
depends on the value of the action attribute in the <form>
element. The superglobal array will contain fields with the CGI parameter values
named according to the NAME attribute of the respective input field in the form.
For example, a form composed of <form action='submit.php' method='post'><input type='hidden' name='myparm' value='BLUE' /></form>
will create a field in the $_POST[] superglobal named 'myparm' with the value 'BLUE'
in the submit.php script. You can access that value using $_POST['myparm'].
If you want to issue the same statement multiple times, the odbc_exec()
function is an expensive approach because it prepares and executes the statement every time.
You can improve the performance of your application by preparing the statement in
a separate step with odbc_prepare() and executing that
prepared statement by calling odbc_execute() multiple times.
In the following example, we create another PHP module and a script that
displays an HTML form to help us insert data into the AUTHOR table. The module
demonstrates how to prepare an INSERT statement that contains parameter markers using
the odbc_prepare() function. We use PHP's powerful HTML
form parsing to determine if the script was invoked with any CGI variable values;
if not, we simply return the HTML form. If the script was invoked with CGI variable
values, we parse the contents of the form fields and pass the arguments as an
array to the odbc_execute() function to insert the
data into the table.
Listing 6. Dynamic INSERT using odbc_prepare and form submission module: db2form.php
<?php
function insert_data( $dbconn, $last_name, $first_name, $middle_initial) {
$insert_stmt = "INSERT INTO author " .
"(last_name, first_name, middle_initial) VALUES(?, ?, ?)";
// odbc_exec returns 0 if the statement fails; otherwise
// it returns a result set ID
$result = odbc_prepare($dbconn, $insert_stmt);
if ($result == 0) {
echo("odbc_prepare() failed.");
return(FALSE);
}
else {
odbc_execute($result, array($last_name, $first_name, $middle_initial));
echo("Inserted $last_name and $first_name and $middle_initial");
}
return(TRUE);
}
function author_form($action) {
// $form is a HERE string that spans multiple lines
$form = <<<HERE
<pre>
<form action="$action" method="post">
Last name: <input type="text" name="last_name" size="32" maxlength="32">
First name:<input type="text" name="first_name" size="32" maxlength="32">
Initial:<input type="text" name="middle_initial" size="1" maxlength="1">
<input type="submit" value="Insert data">
</form>
</pre>
HERE;
return($form);
}
?>
|
db2form.php is our second PHP module, containing the
insert_data() and author_form()
functions. Placing these functions in a module enables us to re-use them in other
scripts. The following script, prepinsert.php, performs
exactly that role; it includes db2lib.php to make use of
the standard database connection function, and db2form.php
for the standard INSERT and form submission functions. The script returns a Web page
with a simple form that lets us insert new rows in our database, and is really
nothing more than a wrapper that calls our module functions.
A significant aspect of the script is the set of preg_replace()
calls that return a cleansed version of the CGI input data. A common way to abuse
Web applications is to pass deliberately malevolent input into form variables;
if the Web application simply uses that input as-is and passes the data to an
SQL statement, uses it as a filename, or otherwise trusts the input data,
unexpected results may occur. In this script, we use the Perl-compatible regular
expression function preg_replace() to ensure that the
input strings will contain only alphabetic characters before we insert
those values into our database.
Listing 7. Dynamic INSERT and form submission script: prepinsert.php
<?php
/* The form within this PHP script calls itself as
the action-handler using the $_SERVER['PHP_SELF']
superglobal variable.
If the script is called without CGI parameter values,
only the form is displayed on the page.
If the script is called with CGI parameter values,
we also call the custom function insert_data(). */
// include our custom function libraries
include_once("db2lib.php");
include_once("db2form.php");
echo(my_header("INSERT using odbc_prepare"));
$first_name = '';
$last_name = '';
$middle_initial = null;
// cleanse CGI variable input; allow names with alphabetic chars only
$name_pattern = '/^\s*([a-z]+)\s*$/i';
$name_replacement = '$1';
if (array_key_exists('first', $_POST) && array_key_exists('last', $_POST)) {
$first_name = preg_replace($name_pattern, $name_replacement, $_POST['first']);
$last_name = preg_replace($name_pattern, $name_replacement, $_POST['last']);
$middle_initial = preg_replace($name_pattern, $name_replacement, $_POST['middle']);
}
if (($last_name != '') && ($first_name != '')) {
if ($middle_initial == '') {
// insert a true NULL value
$middle_initial = null;
}
$verbose = TRUE;
$dbconn = dbconnect($verbose);
if ($dbconn != 0) {
insert_data($dbconn, $last_name, $first_name, $middle_initial);
// always close your database connection
odbc_close($dbconn);
}
}
// $_SERVER['PHP_SELF'] is a superglobal variable that contains the name of this script
echo(author_form($_SERVER['PHP_SELF']));
echo('</body></html>');
?>
|
SELECT statements and result sets
SELECT statements normally return multiple rows of data. When you call the
odbc_exec() function for a SELECT statement, the
function returns a result set identifier. A result set is an array consisting
of 0 or more rows that match a database query; a result set identifier is
simply a value that you pass to other functions to work with the rows in
the result set.
Once we retrieve our result set identifier, we can retrieve the contents of
the result set in a number of ways. One of the most convenient methods is
to iterate over the odbc_fetch_array() function
as demonstrated in the following example:
Listing 8. Simple SELECT statement: db2select.php
<?php
// include our custom function libraries
include_once("db2lib.php");
include_once("db2form.php");
echo(my_header('Simple SELECT statement'));
function display_authors($dbconn) {
// select all rows from the AUTHOR table
$select_stmt = 'SELECT last_name, first_name, middle_initial, author_id
FROM author';
if ($dbconn != 0) {
// odbc_exec returns 0 if the statement fails;
// otherwise it returns a result set ID
$result = odbc_exec($dbconn, $select_stmt);
if ($result == 0) {
echo("SELECT statement failed.");
$sqlerror = odbc_errormsg($dbconn);
echo($sqlerror);
}
else {
print '<table>
<tr><th>Last</th><th>First</th><th>Initial</th><th>ID</th></tr>';
while ($row = odbc_fetch_array($result)) {
print '<tr><td>' . $row['LAST_NAME'] . '</td>';
print '<td>' . $row['FIRST_NAME'] . '</td>';
print '<td>' . $row['MIDDLE_INITIAL'] . '</td>';
print '<td>' . $row['AUTHOR_ID'] . '</td></tr>';
}
print '</table>';
}
}
}
$verbose = TRUE;
$dbconn = dbconnect($verbose);
display_authors($dbconn);
echo('</body></html>');
// always close your database connection
odbc_close($dbconn);
?>
|
db2select.php defines a new function called
display_authors(); normally we would add this
function to an existing module, but for the purposes of this article we
will add it inline to the page. The function displays a list of all authors
in the AUTHOR table by iterating over the odbc_fetch_array()
function in a while() loop. Each time the
while() condition is evaluated, odbc_fetch_array()
returns an array variable named $row representing the
requested row. The fields of the array are named fields that map to the
upper case column names that were requested in the SELECT statement.
When there are no more rows to fetch from the result set,
odbc_fetch_array() returns FALSE and
the while() loop ends.
Overcoming common problems with Unified ODBC
While the Unified ODBC extension for PHP has been developed, maintained, and actively used for many years, there are still some areas in which its match with Cloudscape and DB2 databases is not perfect. This is not the fault of the Unified ODBC extension: by its very nature, it has to support many heterogenenous database servers, and not every database client hews as closely to the ODBC specification as the DB2 client. In this section you will learn how to overcome a few of the common problems that Cloudscape and DB2 developers experience when creating a PHP application with the Unified ODBC extension.
Improving client-to-server performance
One of the most commonly raised issues with using the Unified ODBC extension to connect to Cloudscape and DB2 databases is the discovery that remote client-to-server performance is slow by default. Developers often build and test their applications with PHP, the DB2 client, and the database server all on the same machine, and in this configuration the combination performs extremely well. However, as soon as PHP and the DB2 client are running on a machine separate from the database server, performance is dramatically slower.
The reason for this performance issue is that Unified ODBC uses scrollable dynamic cursors to issue SELECT statements. Unfortunately, the Cloudscape and DB2 database servers are forced to emulate this kind of cursor, resulting in a massive amount of network bandwidth being consumed for each row that is fetched from the server. Rather than suffering the network overhead and slow performance, you can force the DB2 client to automatically substitute forward-only cursors by issuing the following command from the DB2 command line (where dbname represents the name of the database):
bash$ db2 UPDATE CLI CFG FOR SECTION dbname USING PATCH2 6 |
Without this setting, the time required to fetch 10,000 rows of three VARCHAR columns took an average of 22 seconds. When this setting was applied, the same test took an average of 2 seconds!
Note that this setting will modify the behavior of some Unified ODBC functions;
odbc_num_rows will not reflect the number of rows returned
by a SELECT statement, and the odbc_fetch_* functions
will always fetch the next row from the result set.
Counting the number of rows for SELECT statements
A common pattern in PHP applications is to issue a SELECT statement,
count the number of rows returned using odbc_num_rows(),
and only process the result set if the number is > 0.
Listing 9: Typical, but unreliable, practice for counting rows
$select_stmt = 'SELECT last_name FROM author';
$result = odbc_exec($dbconn, $select_stmt);
if (odbc_num_rows($result) > 0) {
while ($row = odbc_fetch_array($result)) {
// do stuff
}
}
else {
print "No results found.";
}
|
Unfortunately, odbc_num_rows() is only guaranteed to return the number of rows affected by INSERT, UPDATE, and DELETE statements. The common approach does not work with SELECT statements when you have implemented the
the performance workaround for Unified ODBC with Cloudscape and DB2 databases.
Instead, enter a while loop using odbc_fetch_array() as
your condition statement; if there are no rows to fetch, the while loop will never
iterate. If you need to print a summary message stating how many rows were fetched,
or stating that no results were found, initialize a counter variable to 0 and
increment it within the while loop.
Listing 10: Recommended practice for counting rows
$select_stmt = 'SELECT last_name FROM author';
$result = odbc_exec($dbconn, $select_stmt);
$i = 0;
while ($row = odbc_fetch_array($result)) {
// do stuff
$i++;
}
if ($i == 0) {
print "No results found.";
}
|
The recommended practice also has the advantage of working when you have implemented
the performance workaround for Unified ODBC with Cloudscape
and DB2 databases. odbc_num_rows will not return a useful
result from SELECT statements with forward-only cursors.
Support for INOUT and OUT parameters of stored procedures
The Unified ODBC API does not offer support for INOUT and OUT parameters of stored procedures. If you must make use of INOUT and OUT parameters, a workaround is available for DB2 databases. It is easy to create SQL stored procedures, so for each stored procedure with INOUT and OUT parameters, you can create a wrapper stored procedure. The wrapper stored procedure simply calls the existing stored procedure and returns the OUT and INOUT parameters from the wrapped stored procedure as a result set in the wrapper stored procedure.
In the following example, we were faced with the challenge of calling a stored procedure named OUT_LANGUAGE that returns one OUT parameter named OUT_LANGUAGE. We issue a CREATE PROCEDURE statement to create a wrapper SQL stored procedure named WRAP_OUT. This new stored procedure returns the OUT_LANGUAGE parameter as a column in a single-row result set.
Listing 11: Sample wrapper stored procedure
CREATE PROCEDURE wrap_out()
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE lang CHAR(8);
DECLARE c1 CURSOR WITH RETURN TO CLIENT FOR
SELECT CONCAT('', lang) FROM SYSIBM.SYSDUMMY1;
CALL OUT_LANGUAGE(lang);
OPEN c1;
END @
|
To call the WRAP_OUT stored procedure from your PHP application, issue the
CALL WRAP_OUT() statement using odbc_exec() and fetch the row from the
result set just as you would fetch rows from a SELECT statement.
Thanks to the authors and maintainers of the Unified ODBC extension who made this article possible: Stig Bakken, Andreas Karajannis, Frank M. Kromann, and Dan Kalowsky. Thanks also to George Schlossnagle for providing helpful feedback.
| Description | Name | Size | Download method |
|---|---|---|---|
| PHP sample code used in this article | php_samples.zip | 10 KB | HTTP |
Information about download methods
- PHP.net is the home of the PHP development community, where you can find
the PHP source and binary downloads, the PHP manual (including user annotations), and the
PHP Extension and Application Repository (PEAR) and
PHP Extension Community Library (PECL)
repositories of PHP language extensions that you can use in your own applications.
- Connecting PHP Applications to Apache Derby
focuses on using PHP and the Unified ODBC extension on Windows to connect to IBM Cloudscape and Apache Derby databases.
- Application Development Experiences with PHP and IBM DB2 Universal Database Version 8 provides an in-depth look at the Unified ODBC extension and offers some patches to the source code to improve BLOB support.
- IBM DB2 Universal Database information center offers the complete set of official documentation and samples for the DB2 Universal Database for Linux, UNIX, and Windows products.
- Share your questions and views on this article and PHP with the author and
other readers in the DB2 UDB and Cloudscape Open Source programming language forum.
- Visit the Speed-start
your Linux app site for the latest no-charge trial downloads for
Linux (WebSphere Studio Application Developer, WebSphere Application
Server, DB2 Universal Database, Tivoli Access Manager, and Tivoli
Directory Server), as well as how-to articles and tech support.
- Get involved in the developerWorks community by participating in
developerWorks
blogs.
- Purchase DB2 books at discounted prices in the DB2 section of the Developer Bookstore.
Dan Scott has worked in the application development area with IBM DB2 Universal Database since 1998. He has written articles, contributed to books, and presented at conferences on the subject of using PHP and other scripting languages with IBM databases. Dan enjoys motorcycles, mountain bikes, music, speculative fiction, and the smell of freshly ground coffee. He currently lives in Toronto with his wife and cat.
