This article is more than 1 year old

Accessing DB2 UDB with PHP (Part 2)

Using PHP with IBM's DB2 universal database

Tutorial As mentioned in the previous tutorial on PHP/DB2, PHP is widely used in web applications and most web applications have a database component. The PHP extension for DB2 database provides access to one particular widely-used database from a PHP script.

In this part of the tutorial, we shall install the DB2 extension, install the DB2 database, create a database connection, create a database table, and retrieve a result set from the database.

Installing the PHP DB2 extension

PHP 5 may be installed on different servers and we will configure it with Apache web server on MS Windows. First, download PHP 5.1.4. Then extract the PHP zip file to an installation directory, C:/PHP for example, and download and install the Apache HTTP Server 2.0 from here.

To the PATH environment variable add C:/PHP, the directory in which PHP 5 is installed. We need to modify the php.init-recommended file in C:/PHP directory to php.ini.

Now, download the Collection of PECL modules for PHP 5.1.4 from here (and the relevant PHP manuals can be found here). Extract pecl-5.1.4-Win32.zip file to a directory. We need to copy php_ibm_db2.dll to the C:/PHP/ext directory.

In the php.ini configuration file, we need to set the extension directory by specifying extension_dir = "./ext". We then activate the DB2 extension by adding the following line to the php.ini file:

extension=php_ibm_db2.dll

We also need to install PHP 5 in the Apache HTTP server. To the <Apache2>/conf/httpd.conf file, we add:

# For PHP 5 
LoadModule php5_module "C:/PHP/php5apache2.dll"
AddType application/x-httpd-php .php

# configure the path to php.ini
PHPIniDir "C:/PHP/"

<Apache2> is the directory in which Apache 2 is installed - by default, this is C:/Program Files/Apache Group/Apache2. If PHP 5 is installed in a directory other than C:/PHP, replace PHPIniDir value, “C:/PHP/”, with the directory in which PHP 5 is installed.

Restart the Apache web server after modifying the httpd.conf file. Copy the PHP files that are to be run in the Apache web server to the C:/Program Files/Apache Group/Apache2/htdocs directory.

Installing DB2 UDB

To install DB2, first download DB2 Viper data server and DB2 Viper Runtime Client (you will need to register for an IBM ID – or have a PartnerWorld or developerWorks user ID). Extract the db2_v9_rc1_ese_win_32.zip and db2_v9_rc1_rtcl_win_32.zip files into a suitable directory.

To install DB2 UDB, double-click on C:\DB2\PE\image\setup.exe application. In the IBM DB2 Setup Launchpad, click on Install Product link. Click on Next. This starts the DB2 Setup Wizard.

Click on Next. Accept the license agreement and click on Next. Select an Installation Type and click on Next. Select the installation and response file creation and click on Next. Select an installation folder and click on Next. Specify a user name and password and click on Next.

DB2 UDB instance ‘DB2’ is listed as the default DB2 instance. The service name and protocol for the default DB2 instance may be configured by clicking on the Protocols button. The default service name is db2c_DB2 and the default port is 50000. Click on Next. Select the default setting in the Prepare the DB2 tools catalog frame and click on Next. Setup notifications and click on Next. Select the default settings in the Enable operating system security for DB2 objects frame and click on Next. Click on the Finish button to install the database.

Next, create a sample database. Set the DB2INSTANCE environment variable to DB2 by selecting System Properties>Advanced>Environment Variables. The DB2INSTANCE environment variable may also be set with the following command in the Command Window.

set DB2INSTANCE=DB2

Next, start the Database Manager with the following command in the Command Line Processor.

START DATABASE MANAGER

The database manager may also be started with the following command in the Command Window:

db2start

Create a sample database with the Create Sample Database button in the First Steps console. The sample database may also be created with following command in the Command Window:

db2sampl

Next, install the DB2 Viper runtime client. Double click on C:\DB2\RTCL\image\setup.exe. This starts the DB2 Runtime client Setup wizard.

Click on Next. Select the license agreement and click on Next. Select the installation type and click on Next. Set the DB2 copy name, which identifies a location where DB2 products are installed, and click on Next. Click on Install, which installs the runtime client.

Creating a connection

In this section, we shall obtain a connection to the DB2 Viper database using the PHP extension for DB2. We shall run a PHP script in the Apache web server. We create a PHP script file, db2connection.php. First, define variables for database, user, password, hostname, and port. As shown in following listing, database is ‘SAMPLE’, hostname is ‘localhost’ and port is 50000.

$database = 'SAMPLE';
$user = 'db2';
$password = 'db2admin';
$hostname = 'localhost';
$port = 50000;

We obtain a connection with the DB2 UDB database using the db2_connect() function. First, we need to specify the connection string for the DB2 UDB database:

$conn_string = “DRIVER={IBM DB2 ODBC DRIVER};DATABASE=$database;” .
“HOSTNAME=$hostname;PORT=$port;
     PROTOCOL=TCPIP;UID=$user;PWD=$password;”;

Subsequently, we connect with the DB2 UDB database using the db2_connect() function. Function db2_connect() creates a non persistent connection, which disconnects and frees up the connection resources when db2_close() is invoked; or the connection resource is set to NULL; or the PHP script ends.

PHP script performance may be reduced if database sessions are made and removed frequently. If large number of database operations are to be performed use db2_pconnect() function to obtain a persistent connection. A database connection obtained with db2_pconnect() does not close when db2_close() is invoked or after the PHP script has run and, when a new connection is requested, PHP reuses a connection obtained with db2_pconnect().

$connection = db2_connect($conn_string, '', '');

The db2_connect() function returns a DB2 database connection resource if the connection is successful and we then output the message, “Connection succeeded”. If the database connection fails, an appropriate connection error message may be output with the db2_conn_errormsg() function.

if ($connection) {
   echo "Connection succeeded."."\n";
}
else {
   echo "Connection failed."."\n";
   echo db2_conn_errormsg ($connection);
}

Creating a database table

In this section, we shall create a database table in the SAMPLE DB2 database schema. We define an SQL statement to create a table, Catalog:

$createtable = "CREATE TABLE DB2.Catalog(CatalogId VARCHAR(25),
 Journal VARCHAR(25), Section Varchar(25), Edition VARCHAR(25),
 Title Varchar(45), Author Varchar(25))";

We then run the SQL statement using the db2_exec() function:

$result=db2_exec($connection, $createtable);

This creates a database table and we then output the message “Database Table Created” if this is successful. If the database table does not get created, we output an error message with the db2_stmt_errormsg() function.

if($result){
echo "Database Table Created."."\n";

}
else {
echo "Database Table not Created."."\n";
echo db2_stmt_errormsg();
}

Define an SQL statement to add a table row.

$addrow = "INSERT INTO DB2.Catalog VALUES('catalog1',
 'IBM developerWorks',  'XML', 'January 2006',
 'Managing XML data: Tag URIs', 'Elliotte Harold')";

We can run the SQL statement using the db2_exec() function:

$result=db2_exec ($connection,$addrow);

This adds a database table row and we output the message, “Row Added”, if it’s successful; otherwise we output an error message with db2_stmt_errormsg().

if($result){
echo "Row Added."."\n";
}
else {
echo "Row not Added."."\n";
echo db2_stmt_errormsg();
}

Similarly, we can add another row to the database table Catalog.

$addrow = "INSERT INTO DB2.Catalog VALUES('catalog2',
 'IBM developerWorks',  'XML', 'January 2006',
 'Practical data binding', 
'Brett McLaughlin')";
$result=db2_exec($connection,$addrow);

Obtaining a result set

In this section, we shall retrieve a result set from the DB2 UDB database with PHP DB2 UDB functions. First, we define, for example, a SQL query that selects all the rows in the Catalog table:

$select= "SELECT * FROM DB2.Catalog";

We can run this SQL query and obtain a result set with the db2_exec() function. The ibm_db2 extension supports two types of cursors: forward only cursors are the default and fetch a result set row by row unidirectionally, and are suited for read-only operations; scrollable cursors may also be used, which update the result set when the database changes. To use a forward only cursor:

$result= db2_exec($connection,$select);

If a scrollable cursor is to be used the db2_exec() function is used as follows:

$result= db2_exec($connection,$select, array('cursor' => DB2_SCROLLABLE));

We may output the result of the SQL query in a HTML table. First, output the table header row:

echo "<table><tr><td>Catalog Id</td
><td>Journal</td><td>Section</td
><td>Edition</td><td>Title</td
><td>Author</td></tr>";

Then, iterate over the result set and retrieve a row with the db2_fetch_array() function. A row retrieved with db2_fetch_array() function consists of an enumerated array of column values, which may be output in the HTML table:

while ($row=db2_fetch_array($result))
     { 
          echo "<tr><td>$row[0]</td>";
          echo "<td>$row[1]</td>";
          echo "<td>$row[2]</td>";
          echo "<td>$row[3]</td>";
          echo "<td>$row[4]</td>";
          echo "<td>$row[5]</td></tr>";

     }

Finally, close the connection with the db2_close() function. Statement resources may be deallocated with the db2_free_stmt() function and result set resources may be deallocated with db2_free_result() function. If the db2_free_stmt() and db2_free_result() functions are not invoked the statement resources and result set resources get deallocated when the script has run.

db2_close($connection);

To run the PHP script in the Apache web server, copy the PHP file, db2connection.php, to the C:/Program Files/Apache Group/Apache2/htdocs directory. The db2connection.php script is listed below:

<?php

$database = 'SAMPLE';
$user = 'db2';
$password = 'db2admin';
$hostname = 'localhost';
$port = 50000;

$conn_string = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=$database;" .
  "HOSTNAME=$hostname;PORT=$port;
  PROTOCOL=TCPIP;UID=$user;PWD=$password;";
$connection = db2_connect($conn_string, '', '');

if ($connection) {
   echo "Connection succeeded."."\n";

}
else {
   echo "Connection failed."."\n";
   echo db2_conn_errormsg ($connection);

}

$createtable = "CREATE TABLE DB2.Catalog(CatalogId VARCHAR(25),
 Journal VARCHAR(25), Section Varchar(25),
 Edition VARCHAR(25), Title Varchar(45), Author Varchar(25))";

$result=db2_exec($connection, $createtable); 

if($result){
echo "Database Table Created."."\n";

}
else {
echo "Database Table not Created."."\n";
echo db2_stmt_errormsg();
}

$addrow = "INSERT INTO DB2.Catalog VALUES('catalog1',
 'IBM developerWorks',  'XML', 'January 2006',
 'Managing XML data: Tag URIs',
 'Elliotte Harold')";

$result=db2_exec ($connection,$addrow);

if($result){
echo "Row Added."."\n";
}
else {
echo "Row not Added."."\n";
echo db2_stmt_errormsg();
}

$addrow = "INSERT INTO DB2.Catalog VALUES('catalog2',
 'IBM developerWorks',  'XML', 'January 2006',
 'Practical data binding', 
 'Brett McLaughlin')";

$result=db2_exec($connection,$addrow);
if($result){
echo "Row Added."."\n";
}
else {
echo "Row not Added."."\n";
echo db2_stmt_errormsg();
}

$select= "SELECT * FROM DB2.Catalog";
$result= db2_exec($connection,$select);

if($result){
echo "Result Set created."."\n";
}
else {
echo "Result Set not created."."\n";

}
echo "<table><tr><td>Catalog Id</td><td>Journal</td
><td>Section</td
><td>Edition</td><td>Title</td
><td>Author</td></tr>";

while ($row=db2_fetch_array($result))
     { 
          echo "<tr><td>$row[0]</td>";
          echo "<td>$row[1]</td>";
          echo "<td>$row[2]</td>";
          echo "<td>$row[3]</td>";
          echo "<td>$row[4]</td>";
          echo "<td>$row[5]</td></tr>";

     }

echo "</table>";
db2_close($connection);

?>

You can use the following URL to run this PHP script with a web browser:

http://localhost/db2connection.php

A connection gets established with the DB2 UDB database and a table gets created in the database. The output from the db2connection.php script is shown in Figure 1.

Displays figure 1. Output from db2connection.php.

Conclusion

PHP scripts facilitate the development of web applications and the PHP 5 extension for DB2 UDB provides useful database support.

An alternative to using the PHP DB2 extension is the more general PDO extension, which provides generic database connectivity. Nevertheless, the specific PHP extension for the DB2 database provides additional database access functions (such as db2_procedures(), db2_statistics(), and db2_tables() , among others), compared to the generic PDO solution; and a disadvantage of using the PDO extension is that it doesn’t support PHP versions earlier than PHP 5.

And, of course, DB2 is only one of the databases you could employ, but it is in wide use in bigger organisations (and free versions of DB2 are available too, as Martin Banks points out here).

The strongest reason for using DB2 is probably that it is already in use in your organisation and it is fully supported by your IT and/or DBA groups, but it is an extremely powerful and scalable database in its own right (see Mark Whitehorn here) – it has, for example, had demonstrably robust transaction processing capabilities for years.

On the other hand, the main incentive for using PHP in your web application is probably its increasing acceptance in the industry generally, as indicated by this Netcraft survey of web servers, but it is a lightweight and flexible scripting language for many web applications.

Whether more traditional languages – or application generators or, perhaps, MDA environments – are possibly more suitable for resilient commercial applications, is outside the scope of this tutorial. ®

More about

TIP US OFF

Send us news


Other stories you might like