DevX.com EarthWeb.com internet.com
Articles
Tutorials
Webcasts
This production-ready version of DB2, complete with pureXML technology, is free to develop, deploy, and distribute. No limits--just data. Download Now!
DevX Update for IBM DB2

More Newsletters
 Print Print
Getting Started: Configure and Use PHP with IBM Cloudscape on Windows
Though the Zend/IBM partnership currently focuses on Linux and AIX platforms, Windows developers have some new toys to play with, as well. This walkthrough outlines the process of installing and configuring the necessary components for a PHP/Cloudscape solution. It also gives you some sample code for your first, simple PHP script. 

Last February, IBM announced a partnership with Zend Technologies to create a PHP solution that integrates with IBM's Cloudscape and DB2 databases (see the full story here).

The offspring of this marriage is a slick baby called "Zend Core for IBM," which in its initial release is aimed at Linux and IBM AIX platforms. But there are a few interesting PHP-related things out for Windows users too. For instance, one of IBM's key additions to PHP is a new PECL extension that gives you some added advantages over an ODBC connection.

Considering that PHP is free, and Cloudscape is free, there's a lot to like about using Cloudscape as your PHP back end. To give this idea a test drive, this walkthrough will take you through the basic installation and setup of all the necessary components, as well as the deployment of a simple test script. Though this article focuses on the Windows platform, the PHP script should be the same for Linux users. Of course, the installation process will be much different for Linux gurus using Zend Core.

Download and Install
Assuming you need one or the other, installation for both Cloudscape and PHP is covered elsewhere much more thoroughly than I can address here. To learn how to install and use Cloudscape, read our "How to Install and Use Cloudscape," by Andrew Binstock.

You can find the PHP installation files in the online PHP manual. Windows installation is covered in "Chapter 6. Installation on Windows systems". Also be sure to read about the "Installation of PECL Extensions," which you can download and install from here.

Unless you already have a version of DB2 installed, you'll also need to install the DB2 RunTime Client. There is a compact DB2 RunTime Client on Windows (new with DB2 UDB v8.2) called DB2 Run-Time Client Lite. It includes support for Java- and C- based applications. The native PECL extension for Cloudscape and DB2 requires the C libraries provided with this package.

You'll need to register with IBM if you haven't already. Registration includes a name/password as well as a required survey. From the download page, probably the easiest thing to do is to download the installer for just your language/country (a 15MB download). If you accept the defaults, this will install the necessary files to your C:\Program Files\IBM\SQLLIB directory.

Be sure to reboot after installing the DB2 RunTime Client. Otherwise, you may have some problems trying to run the DB2 Command Line Processor further down the road.

Create the dbtest Database
The primary purpose of this walkthrough is to prove that you're connected to the database and can read it. To that end, you'll create two simple tables, with one field each.


Figure 1.
Java version 1.4.2_04

Open a DOS command console (Start -> Run -> "cmd" [enter]). The next series of commands assumes you've already correctly installed Java 1.4.2 and have configured all of your system variables and paths and things. To test your version of Java, type "java -version" and hit enter. You should see something like Figure 1.

In DOS, navigate to your new NetworkServer\bin directory. I'm lazy, so I made a .bat file with the following command:

cd C:\Program Files\IBM\Cloudscape_10.0\frameworks\ NetworkServer\bin

Type the following three commands to set your environment variables. Again, I found it easier to just copy these lines and paste them into a batch file, primarily because they need to be run every time you open the command window, unless you edit your system Environment Variables. Note that you'll need some of the classpath statement for this demo and some for your own work later on. I've included commonly used jars here.

set install=c:\program files\ibm\cloudscape_10.0

set path=c:\program files\ibm\cloudscape_10.0\ibm-jre-n142p\jre\bin

set classpath=c:\program files\ibm\cloudscape_10.0\ 
  lib\derby.jar;c:\program files\ibm\cloudscape_10.0\
  lib\derbytools.jar;c:\program files\ibm\cloudscape_10.0\ 
  lib\derbynet.jar;c:\program files\ibm\cloudscape_10.0\
  lib\db2jcc.jar;c:\program files\ibm\cloudscape_10.0\ 
  lib\db2jcc_license_c.jar

Note: A common problem when using Cloudscape is to specify the wrong JRE. Be sure to use the IBM JRE that comes with Cloudscape, as shown above.

Now you're ready to create the database. First, launch Cloudscape's ij tool by typing:

java org.apache.derby.tools.ij

From this point until you exit the ij utility, you'll see the ij> prompt.

Create and connect to the dbtest database in a single step:

connect 'jdbc:derby:dbtest;create=true;user=usr;password=pwd';

A few things to note:

  • First, adding "create=true" tells Cloudscape to create the database at the same time. In the future, you would change that to "create=false".
  • Second, feel free to change your username and password from "usr" and "pwd" to something more relevant to you.
  • Third, these commands end with a semicolon, so don't forget to add that one at the end.

By default, Cloudscape will create your database in

C:\Program Files\IBM\Cloudscape_10.0\frameworks\NetworkServer\bin

This is a good thing. Some of IBM's documentation advises that you create a directory for your database manually, then specifies a different directory, specifically ...Cloudscape_10.0\demo\programs. For the purposes of using Cloudscape with PHP, Don't Do It! Though you can change the server configuration to look for databases here, it's not worth the trouble. And if your database is in the wrong place, you may be faced with hours of troubleshooting vague and (currently) incomplete error messages. Your main clue that you're in the right directory will be the presence of the derby.log file, which you should find in ...Cloudscape_10.0\frameworks\NetworkServer\bin.

Now, create the two tables that will be used in the demo, Races and Classes:

create table Races(race varchar(50));
create table Classes(class varchar(50));

You'll see the tables created as in Figure 2.


Figure 2.
Tables Created

To give you some variety and show you an alternate way to run SQL commands in the ij utility, copy the following commands to a text file and save as "inserts.sql" in your C:\Program Files\IBM\Cloudscape_10.0\frameworks\NetworkServer\bin directory.

    insert into Races values ('Dwarf');
    insert into Races values ('Gnome');
    insert into Races values ('Human');
    insert into Races values ('Night Elf');
    insert into Races values ('Orc');
    insert into Races values ('Undead');
    insert into Races values ('Tauren');
    insert into Races values ('Troll');
    insert into Classes values ('Druid');
    insert into Classes values ('Hunter');
    insert into Classes values ('Mage');
    insert into Classes values ('Paladin');
    insert into Classes values ('Priest');
    insert into Classes values ('Rogue');
    insert into Classes values ('Shaman');
    insert into Classes values ('Warlock');
    insert into Classes values ('Warrior');


Figure 3.
Rows Inserted

Back in the ij window, enter the following to execute the script you just created:

run 'inserts.sql';

You should see each of the statements execute one at a time, followed by the response "1 row inserted/updated/deleted". See Figure 3 for an example.

For kicks, test your database and make sure the data really is in there. Run a simple select query on your Classes table:

select * from Classes;

You should see something like Figure 4.

Disconnect from the dbtest database. Here, I specify "all" as a safety net since I'm about to exit.

disconnect all;

And then exit the ij utility.

exit;

Close the command window.


Figure 4.
Select From Classes

Install the IBM_DB2 Extension
The key element to making all of this work is the newly developed PECL extension. Though you can also use Unified ODBC to connect your PHP script to a Cloudscape database, the PECL extension gives you additional features and flexibility, as well as being generally easier to use.

You can find the current PHP builds at http://snaps.php.net/—at the time of this writing, the most recent stable build is version 5.0. You can find the Windows PECL Extensions for this build at http://snaps.php.net/win32/PECL_5_0/. Download php_ibm_db2.dll to your \ext\ directory as mentioned earlier. By default, this should be C:\PHP\ext.

To enable the extension, you'll need to edit your PHP setup script, which you probably have at C:\WINDOWS\php.ini. You may have edited it when you installed and set up PHP. But in addition, make two more changes:

  • Under "Paths and Directories", go to where it says "Directory in which the loadable extensions (modules) reside" and add the following, or the relevant substitute if you're not using default directories:

    extension_dir = "C:\PHP\ext"

  • Under "Dynamic Extensions", go to the end of your list of "Windows Extensions" and add the following. Make sure it's enabled (not commented out with a semicolon):

    extension=php_ibm_db2.dll

Of course, you can add these values anywhere in the script, but we do like to keep things organized, don't we?

Create cloudtest.php
Let's cut to the chase. We all know why you're really reading this—to find out what the database hook is for the PHP script.

So to get right to it, I'll outline the relevant syntax and then dump the entire sample script, which you can deconstruct at your leisure.

db2_connect is the command you want to know about. Reference the online PHP manual for more information about this command and related info. This command has many, many variations in syntax, but the version I'll use here is:

$conn = db2_connect($database, $user, $password);

Here is the complete script. Save this to a PHP file in your wwwroot directory. I saved mine to C:\Inetpub\wwwroot\dbtest\index.php.

<html>
 <head>
   <title>PHP Meets Cloudscape - DB Test</title>
 </head>
 <body>
   <h1>DB test</h1>

<?php

$database = 'dbtest';
$user = 'usr';
$password = 'pwd';

$conn = db2_connect($database, $user, $password);

if ($conn) {
   echo "Connection succeeded.<br />";

   $sql = "SELECT class FROM Classes";
   $stmt = db2_prepare($conn, $sql);
   db2_execute($stmt);

   $classlist = array();
   $i=0;

   while (db2_fetch_row($stmt)) {
      $classlist[$i] = db2_result($stmt, 0);
      echo "$classlist[$i]<br />";
      $i += 1;
   }

   echo count($classlist) . " classes.<br /><br />";

   $sql = "SELECT race FROM Races";
   $stmt = db2_prepare($conn, $sql);
   db2_execute($stmt);
   $racelist = array();
   $i = 0;
   while (db2_fetch_row($stmt)) {
      $racelist[$i] = db2_result($stmt, 0);
      echo "$racelist[$i]<br />";
      $i += 1;
   }

   echo count($racelist) . " races.<br /><br />";

   $thisClass = $classlist[rand(0,count($classlist) - 1)];
   $thisRace = $racelist[rand(0,count($racelist) - 1)];

   echo "<b>Create a $thisRace $thisClass.</b>";

   db2_close($conn);
}
else {
   echo "Connection failed.<br />";
   echo db2_conn_errormsg();
}

?> 

 </body>
</html>

Some of the pertinent commands used in this example are:

db2_close
Close the connection once you've established it.

db2_conn_errormsg
Explains any problems you may encounter.

db2_prepare
Prepares your SQL statement for execution.

db2_execute
Executes the SQL statement you prepared using db2_prepare. This is preferable to db2_exec (see the documentation for more details on why).

db2_fetch_row
Sets the result set pointer to the next or specified row.

db2_result
Returns a single column from a single row of the result set. I'm using this since the table has only one column.

New Release! Download Cloudscape Version 10.1
New functionality, new support, expanded capabilities! Download Cloudscape, the small-footprint database, based on the open-source Apache Derby, for developers building Windows, Java and Linux applications.

Prep the Database Connection
Before running your script, there are a couple more things you need to do.

First, open a new command console and set your path and classpath variables as before. Then type

java org.apache.derby.drda.NetworkServerControl start

A successful result will say "Server is ready to accept connections on port 1527" (assuming that you left the default port at 1527).


Figure 5.
Server Shutdown
Note: If you need to reset the Network Server later on, shut it down by opening another DOS window (and setting your variables), then typing

java org.apache.derby.drda.NetworkServerControl shutdown

This shuts down the server in your Network Server window, allowing you to start it back up in that same window (use F3 to make it super-easy). See Figure 5 for an example.

Second, open another command console and type db2cmd. In the resulting new window, type db2. This opens your DB2 Command Line Processor (DB2 CLP), as in Figure 6, which will be used to execute your database commands. (If the db2cmd is not recognized, then did you remember to restart after installing DB2 RunTime?)


Figure 6.
DB2 Command Line Processor

In the DB2 CLP, you should have a "db2 =>" prompt. Your new database will need to be catalogued before the PHP script will recognize it. Type the following commands. Note that in this example, I left the username as "usr" and the password as "pwd". Also, you can use whatever node name you would like, just substitute "demo" for something more interesting.

Catalog the node. Cloudscape looks for port 1527 by default:

    db2 => catalog tcpip node demo remote localhost server 1527

Catalog the database using the same node.

    db2 => catalog db dbtest at node demo authentication server

Connect to the database.

    db2 => connect to dbtest user usr using pwd


Figure 7.
DB2 CLP Catalog and Connect
Note: If you get a communication error such as "SQL30081N", then make sure you're running the Network Server before starting the DB2 CLP. You'll be able to catalog the node and database just fine without the network server. But trying to connect to the database will result in an error.

A successful series of commands will look like Figure 7.


Figure 8.
Successful Script Execution

Test Your Script
Open up a browser and navigate to your localhost directory where you put the test script. For me, this is http://127.0.0.1/dbtest/ since I configured IIS to automatically execute index.php. If all goes well, you should see the results in Figure 8.

Not too fancy, but it shows you the basic database connection and read operations. I even tossed in some randomization just for kicks.

Troubleshooting
Even with a process as simple and elegant as this, you can sometimes run into problems. Fortunately, you have a couple of convenient resources to help you troubleshoot.

SQLSTATE and SQLCODE Messages
If you get a database error, it will most likely include both a SQLSTATE and a SQLCODE number. More information about these codes can be found in IBM's Information Center under SQLCODE and SQLSTATE concepts.

derby.log
For more challenging problems, look in the directory of your database, specifically C:\Program Files\IBM\Cloudscape_10.0\frameworks\NetworkServer\bin. You should find derby.log. Open it and look at the latest entries.

Shut Down
When you're done, be sure to shut everything down again. In your DB2 CLP window, type:

db2 => disconnect my_test_db

then

db2 => quit

In another DOS window, shut down the Network Server as described before, by typing

java org.apache.derby.drda.NetworkServerControl shutdown

Close all of these DOS windows.

And that's it!

Word from our sources at IBM has it that a Zend Core for IBM will be released for the Windows platform. Though the date has not yet been announced, I imagine its release will kick start an all-new round of adoption by both PHP and general Web developers. Until then, this walkthrough should help give you a head start. Use it as your launching pad and make it your own.

Resources

To learn more, check out the following links.

About Cloudscape / Derby / DB2

  • Cloudscape 10.0.2.1

  • How to Install and Use Cloudscape

  • DB2 Run-Time Client Lite

  • Installing DB2 RunTime Client
    (This paper was especially helpful in preparing this article.)

  • Cloudscape and ODBC

  • ODBC Programming Using Apache Derby

  • IBM DB2 Universal Database, Cloudscape, and Apache Derby, by Dan Scott

  • Apache Derby—Off to the Races: Includes Details of IBM Cloudscape

    About PHP

  • PHP Installation and Configuration

  • PHP Snapshots

    About PECL Extensions

  • Downloading PECL Extensions

  • Installation of PECL Extensions

  • PECL Extensions for Stable Branch

  • PECL for Windows Users

    Putting it All Together

  • Connecting PHP applications to IBM DB2 Universal Database

  • Develop IBM Cloudscape and DB2 Universal Database Applications with PHP

  • IBM DB2, Cloudscape and Apache Derby Functions

  • ibm_db2 Package Information
  • Page 1 of 1
    Justin Whitney is a regular contributor to DevX.com and Jupitermedia. He currently lives in San Francisco, where he consults for leading high-tech firms and writes about emerging technologies.