 | Level: Introductory Moira Casey (caseymo@us.ibm.com), Quality Assurance, IBM
30 Sep 2004 PHP (PHP:Hypertext Processor) is an open source scripting language for building Web pages. With PHP and Open Database Connectivity (ODBC) you can dynamically generate Web pages from data stored in IBM® Cloudscape™ and Apache Derby databases. This article shows you how to install and configure PHP on Windows®. Also, you'll gain an understanding of the PHP scripting language by learning how to connect to, insert data into, and select data from a Derby database.
Overview
PHP is an open source general-purpose scripting language that can be embedded
into HTML, making it suitable for Web development.
IBM Cloudscape is the commercial release of the Apache Derby open source database.
Wouldn't it be nice to use PHP to dynamically build Web pages from data stored in a Derby database? ODBC provides the middle link that lets you do exactly that. PHP supports ODBC and the IBM DB2® ODBC driver can be used to access Derby databases. This technical paper shows you how to tie all three together. First, it explains how to configure the IBM DB2 ODBC driver to work with Derby. Next, it shows you how to install PHP on Windows and configure the Web server. Finally, it provides you with sample PHP code that executes SQL statements in a Derby database.
Software requirements
The software described in this section is available for download at no cost.
Install one of the database options listed below:
-
Cloudscape 10.0
(this includes the IBM DB2 JDBC Universal Driver)
-
Apache
Derby and the
IBM DB2
JDBC Universal Driver
Additionally install all software listed below:
-
PHP
The instructions in this technical article were tested with PHP version 4.3.29 and 5.0.2;
version 4.3.29 is recommended.
-
IBM DB2 ODBC
Using ODBC with Cloudscape is currently a beta configuration.
- A Web server.
The instructions in this technical article were tested with
the Apache HTTP Server 2.0.50.
Additionally, they should work with the IBM HTTP Server 2.0
Configure the IBM DB2 ODBC client
To use the IBM DB2 ODBC driver with a Derby database, the IBM DB2 client software needs to know where the Derby database is and what its name is, and it also needs to know that it is an ODBC data source. This section shows how to provide all this information by cataloging the node.
The instructions in this section assume the following:
- The Derby Network Server runs on the default port of
1527.
- You created a Derby database
named
DB1.
- The Derby database is on the same machine that the Web server
is running on ("localhost").
First, identify the host and port that the remote database is running on. In this case, the Derby database is on the local machine ("localhost") and the Network Server is listening on the default port of
1527. You also need to provide a name for this node; for example purposes,
call it CNS.
Open a db2cmd window and catalog the CNS
node to access localhost on port 1527, as shown below:
db2 catalog tcpip node CNS remote localhost server 1527
|
Next, provide the name of the database to access on the
CNS node, as shown below:
db2 catalog db DB1 at node CNS authentication server
|
Finally, catalog the database as an ODBC data source. You can do this using the Windows ODBC Data Source Administrator, or by using the CLP command shown below:
db2 catalog system odbc data source DB1
|
To check if the previous steps worked correctly to create the ODBC data source
for the DB1 database, issue this command:
db2 list system odbc data sources
|
If the steps did work correctly you will see confirmation of the DB1
Data source name existing of type IBM DB2 ODBC DRIVER.
The commands shown above will fail if the node or database is already catalogued. If you experience any failures,
the DB2 UDB commands below show how to uncatalog them and go back to the very beginning:
db2 uncatalog node CNS
db2 uncatalog db DB1
|
For a complete description of the IBM DB2 UDB
catalog command, see the
IBM DB2 Universal Database Command Reference.
Verify ODBC works with Derby
Use the ODBC Data Sources tool in Windows to set up DB1 for a quick way to determine if ODBC can connect to the Derby Data Source. To access this tool in Windows XP go to Start --> Control Panel --> Administrative Tools -->Data Sources (ODBC). This will bring up the ODBC Data Source Administrator. Select the
'System DSN' tab and if you cataloged the DB1 Data Source correctly
you'll see it in this list.
Highlight the DB1 under the 'Name' column and click the 'Configure...' button on the left.
You'll see a window appear similar to the image below.
Figure 1. Confirming
the ODBC connection to the Derby data source.
With the Cloudscape Network Server running on the localhost at port 1527 fill out the
'User ID' and 'Password' values, then click the 'Connect' button to test the connection
to the Cloudscape Data Source, 'DB1'. If the connection is successful a pop-up box
will appear informing you it was.
Install PHP
Go to http://www.php.net/downloads.php.
For Windows, select the PHP 4.3.9 zip package under Windows Binaries. The setup
recommend for integrating PHP with Apache is to configure Apache to use PHP as a Dynamic
Shared Object, or Server Module.
Not all versions of PHP available at www.php.net support Apache Modules. The 4.3.9 zip
package is the version you need to get the required Apache dll's. The
PHP 4.3.9 installer package only supports CGI which makes your
Web server subject to security problems and attacks.
Additionally, server modules provide significantly better performance.
Unzip the php-4.3.9-Win32.zip file into a directory.
Next, look for a file called php.ini-recommended under
the location where you unzipped PHP. Copy that file to a file called
php.ini
Now open php.ini and edit the line "doc_root" to point
to your Apache document root. Here is an example entry:
doc_root ="C:\Program Files\Apache Group\Apache2\htdocs"
|
Now copy the file php4apache2.dll under
the sapi directory to the main PHP directory one level up.
To use the command line version of PHP to test scripts, the php.exe
file under the cli directory where you installed PHP must be used.
Additionally, to use the command line version the php4ts.dll file
must be in the same directory. Therefore the php.exe in
the PHP home directory should be saved with another name, and the php.exe under the cli directory
should be moved to the home directory. Now the php4ts.dll and the php.exe file which was originally under the cli
directory, are both in the PHP home directory.
Your configuration for PHP is now complete, now let's execute a simple script from the command
line to test our set up.
Test PHP from the command line
The code in Listing 1 is a simple PHP script to test your PHP environment.
Listing 1: PHP environment test
<!-- begin of test.php-->
<?php phpinfo(); ?>
<!--end of script-->
|
Copy the contents of Listing 1 into a file called test.php and save it to the PHP home
directory. Now make sure
php.exe under the PHP home directory is in
your path or specify the full path to it and run the script.
Here is the command to run the test.php file and a partial listing of sample output
from running the script.
C:\php-4.3.9-Win32>php.exe test.php
<!-- begin of test.php-->
phpinfo()
PHP Version => 4.3.9
System => Windows NT ELIZA2 5.1 build 2600
Build Date => Sep 21 2004 14:03:10
Server API => Command Line Interface
Virtual Directory Support => enabled
Configuration File (php.ini) Path => C:\php-4.3.9-Win32\php.ini
PHP API => 20020918
PHP Extension => 20020429
Zend Extension => 20021010
Debug Build => no
Thread Safety => enabled
Registered PHP Streams => php, http, ftp, compress.zlib
|
Configure the Web server
The httpd.conf file
configures the Web server and is located under the Web
server installation directory path.
For Apache this file is under
the ..\Apache2\conf directory.
To use the PHP modules for Apache that came with the 4.3.9 zip file for
Windows, edit the httpd.conf file.
At the end of the Dynamic Shared Object section, after all of the other "LoadModule" statements,
add the directives shown below:
LoadModule php4_module "c:/php-4.3.9-Win32/php4apache2.dll"
AddType application/x-httpd-php .php
# configure the path to php.ini
PHPIniDir "C:/php-4.3.9-Win32"
|
In the above entries the LoadModule section specifies the path to the dynamic shared
object, php4apache2.dll that came with the PHP distribution you unzipped.
The PHPIniDir points to the php.ini file you copied above that was originally
name php.ini-recommended.
Once you add these entries you will need to stop and start Apache for the changes
to take affect. If you can not start your Apache Web server after making these
changes look in your logs/error.log file for problems. Most likely the path to
the dll is incorrect.
Test PHP and the Web server
Copy the file test.php you just created above to the directory under the Web server's htdocs directory.
Then open a browser and enter
http://<web_server_Name>/test.php, for example:
http://localhost/test.php
|
The PHP version information table should be displayed.
Test PHP/Web server/Derby
This section tests your PHP connection to Derby.
But first make sure you can establish a Derby Network Server connection to the
DB1 database using
ij, the Derby SQL scripting tool.
If ij doesn't work,
your PHP connection isn't likely to work either.
The java command for starting
ij on the Network Server
is shown below, but for readability, it is split across several lines:
C:\>java -Dij.driver=com.ibm.db2.jcc.DB2Driver
-Dij.protocol=jdbc:derby:net://localhost:1527/
-Dij.user=APP -Dij.password=APP org.apache.derby.tools.ij
ij version 10.0 (C) Copyright IBM Corp. 1997, 2004.
ij> connect 'DB1';
|
If the ij command fails, check:
If the ij test succeeded, you're ready for
Listing 2, which contains a sample PHP script that creates a table, inserts
into it, then fetches from it. This database, named 'DB1', is defined in Window's ODBC Data Source manager.
- Copy the contents of Listing 2 to a file called
phptest.php.
- Put this script under the Web server's
htdocs directory.
- Run the script in a Web browser;
for example,
http://web_server_Name/phptest.php. A formatted result set should display.
- If any errors occur, or the expected result set does not display,
check the
error.log file
under the Web server's log directory.
- To check that PHP, ODBC and Derby are all configured correctly, while leaving
the Web server out of the picture, try running the below script from the
command line.
For example: C:\php-4.3.9-Win32\php.exe phptest.php
This will display an html table with result sets if it executed properly.
Listing 2: Derby test
<!---------------- begin script phptest.php----------------->
<?php
$username = "dbadmin";
$password = "testpw";
$dbname = "DB1";
putenv("DB2INSTANCE=DB2");
/* connect to the database with a specified username and password */
$dbconn = odbc_connect($dbname,$username,$password);
/* override the auto commit option, set autocommit to TRUE */
odbc_autocommit($dbconn, TRUE);
if ($dbconn != 0)
{
/* Create the STAFF table */
CreateTable($dbconn);
/* Insert rows into the STAFF table */
BasicInsert($dbconn);
/* Select from the STAFF table */
SelectTable($dbconn);
/* Drop the STAFF table */
DropTable($dbconn);
}
/* disconnect from the database */
DbDisconn($dbconn);
/*******************************************************************************
** Description : This routine uses the odbc_exec function to prepare and
** execute the SQL statement to CREATE the STAFF table.
*******************************************************************************/
function CreateTable($dbconn)
{
$statement = "CREATE TABLE STAFF
(ID SMALLINT NOT NULL,
NAME VARCHAR(9),
DEPT SMALLINT,
JOB CHAR(5),
SALARY DECIMAL(7,2)) ";
$result = odbc_exec($dbconn, $statement);
} /* CreateTable */
/*******************************************************************************
** Description : This routine uses the odbc_exec function to prepare and
** execute the SQL statement to INSERT rows into the STAFF table.
** The odbc_errormsg function will return the last error message.
*******************************************************************************/
function BasicInsert($dbconn)
{
$statement = "INSERT INTO STAFF(id, name, dept, job, salary) " .
" VALUES (380, 'Pearce', 38, 'Clerk', 13217.50), ".
" (310, 'Brown', 37, 'Clerk', 20000.00), " .
" (390, 'Hachey', 38, 'Mgr', 21270.00), " .
" (400, 'Wagland', 38, 'Clerk', 14575.00) ";
$result = odbc_exec($dbconn, $statement);
if ($result == 0)
{
$sqlerror = odbc_errormsg($dbconn);
print "** Error INSERTING into table. $sqlerror **";
}
} /* BasicInsert */
/*******************************************************************************
** Description : This routine uses the odbc_do function to execute a query
** on the given connection.
** The odbc_result_all function prints the result as an HTML table.
** odbc_result_all has an optional
** string argument called format. This option allows for overall table
** formatting, such as BORDER style.
*******************************************************************************/
function SelectTable($dbconn)
{
//SQL query
$Query = "Select id, name, dept, job, salary from STAFF";
//execute query
$queryexe = odbc_do($dbconn, $Query);
//output results to standard output
odbc_result_all($queryexe, "BORDER=1");
} /* SelectTable */
/*******************************************************************************
** Description : This routine uses the odbc_exec runction to prepare and
** execute the SQL statement to DROP the STAFF table.
** The odbc_errormsg function will return the last error message.
*******************************************************************************/
function DropTable($dbconn)
{
$statement = "DROP TABLE STAFF" ;
$result = odbc_exec($dbconn, $statement);
} /* DropTable */
/*******************************************************************************
** Description : This routine uses the odbc_commit function to commit
** non-committed transactions.
** The odbc_close() function will close down the given connection
** to the database server
********************************************************************************/
function DbDisconn($dbconn)
{
/* commit all non-committed transactions to release database locks */
if (!odbc_commit($dbconn))
{
print "Error on commit\n";
}
odbc_close($dbconn);
} /* DbDisconn */
?>
|
 |
Summary
This paper covered how to install and configure PHP, described how to catalog a Derby node for ODBC using the IBM DB2 ODBC driver, and demonstrated how to perform basic database operations with PHP.
Resources
- Share your questions and views on this article with the author
and other readers in the IBM Cloudscape discussion forum.
- To learn more about IBM Cloudscape, visit the developerWorks Cloudscape zone.
You'll find technical documentation, how-to articles, education, downloads,
product information, and more.
- Downloads, faqs, documentation and mailing lists for PHP are available at php.net.
About the author | | | Moira Casey is an Advisory Engineer in DB2. For the past 6 years she has been a test engineer for DB2, and more recently, Cloudscape. |
Rate this page
| |
