Contents


Connecting PHP Applications to Apache Derby

Comments

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:

  1. Cloudscape 10.0 (this includes the IBM DB2 JDBC Universal Driver)
  2. 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.
ODBC data source connection
ODBC data source connection

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.


Downloadable resources


Related topics

  • 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.

Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Open source
ArticleID=23536
ArticleTitle=Connecting PHP Applications to Apache Derby
publish-date=09302004