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.
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.
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');
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.
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:
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.
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).
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?)
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
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.
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
DevX.com 
Print
