PHP Database Programming with DB2

Being a PHP programmer, I naturally experimented in MySQL. After developing a collection of successful Websites based on the powerful combination of PHP-MySQL, I had the opportunity to develop a data-driven Website using IBM's DB2 UDB (universal database).

In the world of industry database management, there are three major players: IBM DB2, Microsoft SQL Server and Oracle. There is a lot of literature about using both Microsoft and Oracle's products, but information on using DB2 specifically with PHP was difficult to find. I developed this tutorial to help the age PHP-MySQL application developer expand his or database horizons to include IBM's powerful database offering.

Before we get started, here's a brief comparison between the licensing and features of MySQL and DB2.

For more information about DB2, see the dedicated subsection of the IBM site.

Pre-requisites

To start using DB2, you'll need a server with the following installed:

1. Apache 2.0 (may work with older versions)


2. PHP 3.0.6+ (compiled with the --with-ibm-db2 switch. Note: the latest binary is compiled with code for DB2, so you can just download a binary if you like. Not tested with PHP5)


3. DB2 8.1+ (may work with older versions)

If you're having trouble installing any of these components, see
this article to troubleshoot the installation.

Also, don't forget to download this tutorial's code -- it'll com in handy as you work through the follwing procedures.

Setup

Ok, so everything is installed. Let's start up the DB2 Control Center. This application is a bit slow, but be patient. It will allow us to manage DB2 with a Graphic User Interface.

1. Create DB

• Press the plus sign beside each ucon in the left panel until you see a list of databases.
• You should see toolsdb and sample (depending on the options you selected during installation).
• Right click the folder labeled Databases and choose Create > Database using Wizard.
• Follow this wizard to create a database in DB2. This may take a few minutes – the timeframe will depend on your system.

2. Create User

• We will need a user that we can use to connect to this database.
• DB2 uses Operating System Authentication, so you will need to create a local or network user against which DB2 can authenticate.
• Back in the Control Center, click the plus beside your new database. Right-click the folder labeled DB Users and select Add.
• Type the username that you wish to give access to your new database in the select box at the top of this screen. Select all checkboxes to give the user full rights for this database.

3. Create DSN Link

To connect to DB2, we need a System DSN Link. To create this on a windows machine:

• Go to: Control Panel \ Administrative Tools \ Data Sources (ODBC)
• Choose System DSN and select Add
• Select IBM DB2 ODBC DRIVER
• Now type the name for this DSN Link and choose the database we just created

Enough Setup!

Well, that was fun. Now, let's start to build our PHP application. I don't really like the way ODBC database calls are handled in PHP, so let's write a Database Class that we can use whenever we want to connect to DB2.

<?php

class DB2
{
 var $handle;
 
 // bool connect(string $dsn, string $user, stirng $pass)
 function connect($dsn, $user, $pass)
 {
   $this->handle = odbc_connect($dsn, $user, $pass);
   if (!$this->handle)
     return false;
   return true;
 }
 
 // resourceid query(string $sql)
 function query($sql)
 {
   $rs = @odbc_exec($this->handle, $sql);
       if ($rs)
         {
return $rs;
       }
         else
       {
$sErr = "<b>Error:</b> " . $this->getErrorMsgs() . "<br>\n";
$sErr .= "<b>SQL:</b> " . $sql;
die($sErr);
}
 }

 // string getErrorMsgs()
function getErrorMsgs()
{
return odbc_errormsg($this->handle);
}

// void disconnect()
 function disconnect()
 {
   if ($this->handle)
     odbc_close($this->handle);
 }
}

?>

We'll expand this class to encapsulate some more database functionality later, but for now, this should be all we need. Let's see how we'd go about using this class:

<?php

require_once($HTTP_SERVER_VARS['DOCUMENT_ROOT'] . "/path/to/class.php");

$db = new DB2();
print "connecting to DB2...";
if (!$db->connect("testdb", "username", "password"))
{
 print "Error!\n";
 exit();
}
print "Connected!<br>\n";

print "Creating Database...";
$sql = "";
$sql .= "CREATE TABLE testtbl (id INTEGER NOT NULL GENERATED";
$sql .= "  ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1, NO";
$sql .= "  CACHE), name VARCHAR (64) NOT NULL , created DATE  NOT NULL)";
$db->query($sql);

print "Created Table!<br>\n";

print "Disconnecting...";
$db->disconnect();
print "Disconnected!<br>\n";

?>

This short application should connect to the database and create a three-column table. Note that the SQL syntax is different from MySQL and others. If you're having trouble with the SQL syntax, remember that many actions can be completed from the Control Center (and you can click "Show SQL" to see the SQL to do the same action yourself).