Develop IBM Cloudscape and DB2 Universal Database applications with PHP

Using the Unified ODBC extension for PHP with Apache 2

Learn how to configure IBM® Cloudscape™ 10.0 and IBM DB2® Universal Database™ (DB2 UDB) Version 8.2 servers for access from PHP 4.x and PHP 5.x. Write database applications using the Unified ODBC extension. Overcome common performance issues due to scrollable cursors and avoid functional limitations in stored procedures. The Unified ODBC extension for PHP offers a common interface for developing PHP applications that connect to databases through an Open Database Connectivity (ODBC) driver. The DB2 client includes an ODBC driver that enables you to access data in Apache Derby, Cloudscape, DB2 UDB for Linux™, UNIX®, and Windows®, and DB2 UDB for iSeries™ and zSeries® servers. Even better, you can compile the Unified ODBC extension directly against the DB2 Call Level Interface (DB2 CLI) so that PHP applications can avoid the overhead of an ODBC Driver Manager and talk directly to your target database server

Dan Scott (dan.scott@ca.ibm.com), Product Manager, IBM DB2 Universal Database for Linux, IBM Canada

Author photoDan 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.



24 February 2005

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.

How PHP scripting works

Confessions from 2001: Sin one

In my previous article I introduced readers to a few questionable PHP coding practices. In my defense, they were the predominant practices at the time, but the state of PHP development and security awareness has advanced dramatically in the last four years. The first convention is that while the PHP interpreter enables you to place <?php ... ?> PHP code escape sequences directly inside HTML, many developers now write a single PHP file within a single <?php .... ?> block and generate HTML as part of the code flow.

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

  1. Install the DB2 client, ensuring that you select the application development headers and libraries.
  2. 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.
  3. 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

  1. (Linux only): Inherit the DB2 instance environment into your shell session with the following command:
    bash$ source /home/db2inst1/sqllib/db2profile
  2. 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.

  3. 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
  4. (Windows only): Catalog the database as a System ODBC data source:
    bash$ db2 CATALOG SYSTEM ODBC DATA SOURCE SAMPLE
  5. 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.

  1. 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
  2. Download the source for the latest version of PHP. The version used in testing and developing this article was PHP 5.0.3.
  3. Extract the files from the tarball:
    bash$ tar xjf php-5.0.3.tar.bz2
  4. 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
  5. 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:

  1. 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.
  2. Unzip the files. For the purposes of this article, we'll assume that you unzipped the files into the C:\php\ directory.
  3. 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.
  4. Edit the C:\Program Files\Apache Group\Apache2\conf\httpd.conf file to add the following lines:
    php5.conf settings
    LoadModule php5_module 'c:/php/php5apache2.dll'
    AddType application/x-httpd-php .php
    PHPIniDir 'c:/php'
  5. 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:

Defining SQL statements

We define the SQL statement as a string variable for two reasons:

  1. Defining all of our SQL statements at the top of the file makes it easier to read the code and modify the ements to tweak our application. This is especially helpful for long SQL statements that include JOIN operations or complex WHERE clauses that might wrap over several lines.
  2. In many database applications we need to repeatedly issue the same statement, sometimes with slightly different values. Declaring the statement as a string variable when you develop your application makes it easier to convert to a production application.
  • 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.

Creating the author table

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;

Issuing INSERT statements

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

Confessions from 2001: Sin two

The second sin was to assume that the register_globals php.ini directive would be turned on. This directive had the effect of automatically making CGI parameters turn into PHP variables with the same name with a global scope inside a given PHP script. While this was a convenient feature, it was also horribly insecure and the PHP developers wisely chose to turn register_globals off by default shortly after my article had been published. Of course, that broke many of the samples in the article. The samples in this article are guaranteed to work!

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);
}
?>

Confessions from 2001: Sin three

My final and most serious sin was to fail to warn developers about the importance of cleansing input and output data. The PHP Security Consortium provides a number of free, high-quality resources on this subject and many more advanced security subjects. Let's just say if you fail to ensure that the data you're using in your script is safe, you should not be surprised if your server falls victim to a malevolent party.

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.

Credits

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.


Download

DescriptionNameSize
PHP sample code used in this articlephp_samples.zip10 KB

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Open source
ArticleID=358033
ArticleTitle=Develop IBM Cloudscape and DB2 Universal Database applications with PHP
publish-date=02242005