Use Ajax with PHP and DB2 9 - Xajax

Document options Document options requiring JavaScript are not displayed Discuss New site feature Check out our new article design and features. Tell us what you think. Rate this page Help us improve this content

Level: Introductory

Deepak Vohra (dvohra09@yahoo.com), Web Developer, Independent

25 Jan 2007

PHP is a scripting language commonly used in developing Web applications. Asynchronous JavaScript and XML (Ajax) is a Web technique that combines JavaScript, Document Object Model (DOM), and XMLHttpRequest technologies to provide dynamic interactions between a client and a server. Various PHP class libraries are available for using Ajax with PHP, among them Xajax, an open source, object-oriented, PHP class library. In this article, learn how to implement PHP, Ajax, and Xajax to develop a dynamic Web application solution accessing IBM® DB2® 9 for Linux®, UNIX®, and Windows®.

Introduction

More than 40% of Web applications today use PHP as their scripting language. Asynchronous JavaScript and XML (Ajax) is a Web technique that combines JavaScript, Document Object Model (DOM), and XMLHttpRequest technologies to provide dynamic interaction between a client and a server. Various PHP class libraries are available for using Ajax with PHP. Xajax is an open source, object-oriented, PHP class library with which Ajax may be used with PHP.

As an example, suppose a user fills out a form to register with a Web site. The validity of data in the form is not checked until the form is submitted. With Ajax, the data added to the form is dynamically validated using business logic in a server application. Thus, a complete form does not have to be posted to the server to check to see whether or not the data in the form is valid. Asynchronous implies that the send() method of an XMLHttpRequest object returns immediately without the HTTP request being complete and the processing of the HTML/JavaScript page that initiated the request continues. As a result, a developer may find that a HTML/JavaScript page that initiates an XMLHttpRequest request may continue to be processed as the XMLHttpRequest object processes the HTTP response asynchronously.

Back to top

Overview of Xajax

Xajax is used to communicate asynchronously between a client application and a server side application composed of PHP scripts. Xajax generates JavaScript wrapper functions for PHP functions on the server side that may be accessed from a client application. When a client application invokes the wrapper functions, an XMLHttpRequest object is initiated and an XMLHttpRequest HTTP request is sent to the server.

On the server, the xajax object receives the XMLHttpRequest and invokes the PHP functions corresponding to the JavaScript wrapper functions. The default request type of PHP functions registered through Xajax is POST. The PHP functions return an XML response that is returned to the client application by the xajax object. Based on the instructions in the XML response, the Xajax's JavaScript message pump updates the content of the client input page. Xajax has a feature that ensures that data is updated only if data has been modified.

To get you started with creating your own Web application, let's first walk through the process of installing the products and creating a database. Then you can delve into the creation of the application itself.

Back to top

Install DB2 for Linux, UNIX, and Windows

The PHP extension for DB2 database may be used with DB2 for Linux, UNIX, and Windows V8.2 or later. The following installation instructions use DB2 Enterprise 9 for Windows:

1. Download DB2 9 data server (see Resources). (Although this example uses DB2 Enterprise 9, you may use other editions of DB2 as well.)
2. Extract db2_v9_ese_win_32.zip to a directory.
3. To install DB2, double-click on C:\DB2\ESE\image\setup.exe application.
4. In the IBM DB2 Setup Launchpad, click on Install a Product.
5. Click on Install New in the DB2 Enterprise Server header. The DB2 Setup Wizard will start. Click on Next.
6. Accept license agreement, and click on Next.
7. Select an Installation Type (Typical ), and click on Next.
8. Select the installation and response file creation (default), and click on Next.
9. Select an installation folder (C:\Program Files\IBM\SQLLIB\ is the default), and click on Next.
10. Specify a user name (db2) and password (db2admin), and click on Next.
11. DB2 instance DB2 is listed as the default DB2 instance. You can configure the service name and protocol for the default DB2 instance by clicking on the Configure button. The default service name is db2c_DB2, and the default port is 5000. Click on Next.
12. Select the default setting in the Prepare the DB2 tools catalog frame, and click on Next.
13. Set up notifications, if required, and click on Next.
14. Select the default settings in the Enable operating system security for DB2 objects, and click on Next.
15. Click on the Finish button to install the database server.

Now create a sample database. As a prerequisite, set the DB2INSTANCE environment variable to DB2: Select System Properties > Advanced > Environment Variables > System Variables. You can also set the DB2INSTANCE environment variable using the following command in a DB2 command window:

set DB2INSTANCE=DB2

To create a sample database, select Database Creation in the DB2 First Steps frame. Click on the Create SAMPLE Database button. You can also create a sample database using the following command:

db2sampl

Register the DB2 database SAMPLE as an ODBC data source with the following DB2 command:

db2 catalog system odbc data source sample

Back to top

Install PHP and Xajax

As Xajax is a PHP class library, first download and install PHP 5. PHP 5 may be installed on a number of different Web servers. This article discusses configuring PHP 5 with the Apache Web server on Windows.

Here are the steps for installing and configuring PHP:

1. Download PHP 5.2.0 (See Resources).
2. Extract the PHP zip file to an installation directory (C:/PHP, for example).
3. Download and install the Apache HTTP Server 2.0.
4. To the PATH environment system variable, add the directory in which PHP 5 is installed (C:/PHP).
5. Modify the php.init-recommended file in C:/PHP directory to php.ini.
   

The example application in this article stores and fetches data from the DB2 database. You will use the PHP PDO extension to connect to the DB2 database.

In the php.ini configuration file, you need to set the extension directory by specifying extension_dir = "./ext". You need to activate the PDO extension by removing the ';' before the following line in the php.ini file:

extension=php_pdo.dll

The PDO ODBC driver is also required to be activated. In the php.ini file, remove the ';' before the following line:

extension=php_pdo_odbc.dll

Install PHP 5 in the Apache HTTP server. To the <Apache2>/conf/httpd.conf file, add:

# For PHP 5 
LoadModule php5_module "C:/PHP/php5apache2.dll"
AddType application/x-httpd-php .php

# configure the path to php.ini
PHPIniDir "C:/PHP/"

By default, the <Apache2> directory is the directory C:/Program Files/Apache Group/Apache2. We have installed the Apache2 server in the C:/Apache2/Apache2 directory. If PHP 5 is installed in a directory other than C:/PHP, replace C:/PHP with the directory in which PHP 5 is installed. Restart Apache Web server after modifying the httpd.conf directory. Copy the PHP files to run in Apache Web server to C:/Apache2/Apache2/htdocs directory.

Download Xajax 0.2.4. Create an xajax directory in the C:/Apache2/Apache2/htdocs directory. Extract the xajax_0.2.4.zip file to the xajax directory.

Back to top

Create a DB2 database table

The Xajax application retrieves data from a DB2 database table. The database table may be created with a PHP script. Create a PHP script, createTable.php. Define variables for user and password:

$user = 'db2';
$password = 'db2admin';

Obtain a connection with the DB2 SAMPLE database using the PDO constructor:

$connection = new PDO('odbc:SAMPLE', $user, $password);

By default, the PDO constructor creates a non-persistent connection. The PDO constructor returns a DB2 database connection resource if the connection is successful. The output message reads, "Connection succeeded" if the connection is successful. If the database connection fails, the connection error message may be output. Define an SQL statement to create a table:

$createtable = "CREATE TABLE DB2.Catalog(CatalogId VARCHAR(25), 
Journal VARCHAR(25), Section Varchar(25), Edition 
VARCHAR(25), Title Varchar(45), Author Varchar(25))";

Run the SQL statement using the exec() function:

$connection->exec($createtable);

Define an SQL statement to add a table row:

$addrow = "INSERT INTO DB2.Catalog VALUES('catalog1', 'IBM developerWorks',  
'XML', 'July 2006', 'The Java XPath API', 'Elliotte Harold')";

Run the SQL statement using the exec() function:

$result=$connection->exec($addrow);

A database table row gets added. Output the message to indicate number of rows added:

print("Added $result rows.\n");

Similarly, add another row to database table Catalog:

$addrow = "INSERT INTO DB2.Catalog VALUES('catalog2', 'IBM developerWorks',  
'XML', 'October 2005', 'JAXP validation',  'Brett McLaughlin')";

$result=$connection->exec($addrow);

The PHP scriptcreateTable.php is shown in Listing 1. Copy createTable.php to the C:/Apache2/Apache2/htdocs directory. Start Apache Web server, if it is not already started. Run the PHP script in a browser with URL http://localhost/createTable.php. A database table, DB2.Catalog, gets generated, and data gets added to the table.

                
<?php

try {

$user = 'db2';
$password = 'db2admin';

$connection = new PDO('odbc:SAMPLE', $user, $password); 
echo "Connection succeeded\n";

$createtable = "CREATE TABLE DB2.Catalog(CatalogId VARCHAR(25), 
Journal VARCHAR(25), Section Varchar(25), Edition 
VARCHAR(25), Title Varchar(45), Author Varchar(25))";

$connection->exec($createtable);

$addrow = "INSERT INTO DB2.Catalog VALUES('catalog1', 'IBM developerWorks',  
'XML', 'July 2006', 'The Java XPath API',   
'Elliotte Harold')";

$result=$connection->exec($addrow);

print("Added $result rows.\n");

$addrow = "INSERT INTO DB2.Catalog VALUES('catalog2', 'IBM developerWorks',  
'XML', 'October 2005', 'JAXP validation',  
 'Brett McLaughlin')";

$result=$connection->exec($addrow);



} catch (PDOException $e) {

   $e->getMessage();
}


?>

Back to top

Sending a request

Now let's develop an example application comprising of an input form. The input form takes data to add a catalog entry to database table Catalog. As a user begins to enter data in the input field Catalog Id, a XMLHttpRequest HTTP request is sent to the server to validate the Catalog Id value added. If the Catalog Id is not already defined in the database, a message "Catalog Id is Valid" is displayed. If the Catalog Id is already defined in the database, a message "Catalog Id is not Valid" is displayed, the Create Catalog button is disabled, and field values for the Catalog Id are added to the form.

The xajax PHP object performs the function of an intermediary between the client application and the server. First, include the xajax class library:

require('./xajax/xajax_0.2.4/xajax.inc.php');

Create an xajax object:

$xajax = new xajax();

The server-side processing is performed by PHP functions. Create PHP functions validateCatalogId($formValues) and updateCatalog($formValues). Both these functions take a $formValues parameter:

function validateCatalogId($formValues){}
function updateCatalog($formValues){}

Register the PHP functions with the xajax object using the registerFunction() method. The xajax object creates wrapper functions for the PHP functions that may be invoked from a PHP script or an input form event handler.

$xajax->registerFunction("validateCatalogId");
$xajax->registerFunction("updateCatalog");

Xajax generates asynchronous wrapper functions for the registered PHP functions. A wrapper function name is of the format xajax_phpfunction. Variable phpfunction is a server-side PHP function for which a wrapper function is to be defined. Xajax provides asynchronous form processing with the getFormValues(string formId) method. Using the getFormValues() method, an array of form field values may be submitted as an argument to an xajax asynchronous wrapper function. Sections of a form may also be submitted, instead of the complete form, with the getFormValues(string formID ,boolean submitDisabledElements, string prefix]) function. The prefix parameter specifies that only form elements starting with that prefix should be submitted. Boolean parameter submitDisabledElements specifies if disabled elements are to be submitted. PHP functions validateCatalogId and updateCatalog define a parameter for an array of form field values. Before an XMLHttpRequest is initiated, specify the xajax object to handle requests with the processRequests() function:

$xajax->processRequests();

Also specify in the <head></head> tags of the input form that xajax should generate any required JavaScript after an XML response has been sent back from the server:

<?php $xajax->printJavascript('./xajax/xajax_0.2.4');
?>

An XMLHttpRequest is initiated by a client application. In the example application, an XMLHttpRequest is initiated by the onkeyup event handler in input field catalogId:

<tr><td>Catalog Id:</td><td><input    type="text"
            size="20"  
              id="catalogId"
            name="catalogId"
    autocomplete="off"
         onkeyup="xajax_validateCatalogId(xajax.getFormValues
           ('validationForm'));"></td>
         <td><div id="validationMessage"></div></td>
</tr>

			

The input field invokes the wrapper function xajax_validateCatalogId with an array of form field values as a parameter. The wrapper function sends an XMLHttpRequest to the server. The xajax object receives the XMLHttpRequest and invokes the corresponding PHP function validateCatalogId($formValues).

Back to top

Processing a request

Xajax provides the xajaxResponse class to send a response to the client application. In the validateCatalogId function, create an xajaxResponse object:

$objResponse = new xajaxResponse();

The validateCatalogId function validates a Catalog Id value added in the input form. From the $formValues array, retrieve the value of the catalogId field:

$catalogId=trim($formValues['catalogId']);

Next, use the PHP PDO extension to connect with the DB2 database and determine if a Catalog table row is defined for the catalog id value input in the input form.

Define variables for user and password:

$user = 'db2';
$password = 'db2admin';

Obtain a connection with the DB2 database using the PDO constructor:

$connection = new PDO('odbc:SAMPLE', $user, $password);

The PDO constructor returns a DB2 database connection resource if the connection is successful. Prepare an SQL-prepared statement to select a row of data for the catalog id value input in the form. Use the prepare ( string statement [, array driver_options] ) function to compile a prepared statement:

$stmt = $connection->prepare("SELECT * from DB2.CATALOG WHERE CATALOGID=?");

Set the cursor to be a scrollable cursor using the PDO::ATTR_CURSOR attribute:

$stmt ->setAttribute(PDO::ATTR_CURSOR, PDO::CURSOR_SCROLL);

Bind the CatalogId parameter using the bindParam() method:

$stmt->bindParam(1, $catalogId);

Run the SQL query in the prepared statement with the execute ( [array input_parameters] ) function:

$stmt->execute();

Fetch the result set row using the fetch ( [int fetch_style [, int cursor_orientation [, int cursor_offset]]] ) function:

$row = $stmt->fetch();

The fetch() function returns TRUE if the requested row exists and FALSE if the requested row does not exist. If fetch() returns FALSE, the result set is empty, thus a Catalog table row for the catalog id value is not defined in the database table. Therefore, the Catalog Id field value added in the form is valid. Next, let's generate a response to be sent to the client application. A response contains one or more command messages. Some of the commonly used command messages are discussed in Table 1:

If the fetch() method returns FALSE, a message is displayed in the validationMessage div: "Catalog Id is Valid". The addAssign method sets the innerHTML of the validationMessage div."

$objResponse->addAssign("validationMessage","innerHTML","Catalog Id is Valid");

If the fetch() function returns TRUE, the catalog id value is defined in the Catalog table. Therefore, the Catalog Id value added in the input form is not valid. If the fetch() method returns a row, set the innerHTML of the validationMessage div to "Catalog Id is not Valid":

$objResponse->addAssign("validationMessage","innerHTML","Catalog Id is not Valid");

Next, fetch values from the result set row and set the values in the input form fields. The fetch() function sets the result set pointer to the next row.

Retrieve field values from the result set row with $row[mixed column] function. The column parameter may be specified as column index (0 based) or column name. For example, the journal column value is obtained with PHP code in following listing:

$journal=$row['JOURNAL'];

Set the value attribute of the input form field elements with addAssign method. For example, the value attribute of the journal element is set as shown below:

$objResponse->addAssign("journal","value",$journal);

Also, disable the submit button:

$objResponse->addAssign("submitForm","disabled",true);

Return the $objResponse object from the validateCatalogId function as an XML string:

return $objResponse->getXML();

The XML response is sent to the xajax processor, which sends the XML response to the xajax's JavaScript message pump. The message pump parses the XML instructions and sets the elements in the input page. Thus, the data specified in the $xmlResponse object with addAssign method is set in the input form.

The updateCatalog($formValues) function is used to update the database table Catalog from the input form. If the Catalog Id field value is valid, a new catalog entry may be created by adding values to the other fields of the form. Click on the Create Catalog button to submit the form. The onsubmit event handler invokes the wrapper function xajax_updateCatalog, which sends an XMLHttpRequest to the server:

onsubmit="xajax_updateCatalog(xajax.getFormValues('validationForm'));"

The xajax object receives the XMLHttpRequest and invokes the corresponding PHP function updateCatalog($formValues). In the updateCatalog function, retrieve the form field values and create an SQL statement to add a row to database table Catalog. Obtain a connection with the database and run the SQL statement. The input.php script is listed in Listing 2:

                

<?php require('./xajax/xajax_0.2.4/xajax.inc.php');
$xajax = new xajax();
$xajax->cleanBufferOn();  

$xajax->registerFunction("validateCatalogId");
$xajax->registerFunction("updateCatalog");


function validateCatalogId($formValues){
$objResponse = new xajaxResponse();


$catalogId=trim($formValues['catalogId']);


$user = 'db2';
$password = 'db2admin';


$connection = new PDO('odbc:SAMPLE', $user, $password);

$stmt = $connection->prepare("SELECT * from DB2.CATALOG WHERE CATALOGID=?");
$stmt ->setAttribute(PDO::ATTR_CURSOR, PDO::CURSOR_SCROLL);
$stmt->bindParam(1, $catalogId);
$stmt->execute();
$row = $stmt->fetch();

if(!$row){
$objResponse->addAssign("validationMessage","innerHTML","Catalog Id is Valid");
$objResponse->addAssign("submitForm","disabled",false);

$objResponse->addAssign("journal","value","");
$objResponse->addAssign("section","value","");

$objResponse->addAssign("edition","value","");
$objResponse->addAssign("title","value","");
$objResponse->addAssign("author","value","");

}

if($row){
$objResponse->addAssign("validationMessage","innerHTML",
"Catalog Id is not Valid");

$catalogId=$row['CATALOGID'];
$journal=$row['JOURNAL'];
$section=$row['SECTION'];
$edition=$row['EDITION'];
$title=$row['TITLE'];
$author=$row['AUTHOR'];

$objResponse->addAssign("journal","value",$journal);
$objResponse->addAssign("section","value",$section);

$objResponse->addAssign("edition","value",$edition);
$objResponse->addAssign("title","value",$title);
$objResponse->addAssign("author","value",$author);
$objResponse->addAssign("submitForm","disabled",true);

}

return $objResponse->getXML(); 

}

function updateCatalog($formValues){

$catalogId=trim($formValues['catalogId']);
$journal=trim($formValues['journal']);
$section=trim($formValues['section']);
$edition=trim($formValues['edition']);
$title=trim($formValues['title']);
$author=trim($formValues['author']);


$user = 'db2';
$password = 'db2admin';


$connection = new PDO('odbc:SAMPLE', $user, $password);


$sql = "INSERT INTO DB2.Catalog  
VALUES('".$catalogId."','".$journal."','".$section."','".$edition."','".
$title."','".$author."')";

$r=$connection->exec($sql);

$connection->commit();

echo "Added $r rows.\n";

}
$xajax->processRequests();

?>
<head>
<?php $xajax->printJavascript('./xajax/xajax_0.2.4');
?>
</head>
<body>
<h1>Form to Create a Catalog Entry</h1>
<form id="validationForm"  name="validationForm" onsubmit=
"xajax_updateCatalog(xajax.getFormValues('validationForm'));">
<table>
<tr><td>Catalog Id:</td><td><input    type="text"
            size="20"  
              id="catalogId"
            name="catalogId"
   autocomplete="off"
     onkeyup="xajax_validateCatalogId(xajax.getFormValues('validationForm'));"></td>
        <td><div id="validationMessage"></div></td>
</tr>


<tr><td>Journal:</td><td><input    type="text"
            size="20"  
              id="journal"
            name="journal"></td>
</tr>

<tr><td>Section:</td><td><input    type="text"
            size="20"  
              id="section"
            name="section"></td>
</tr>

<tr><td>Edition:</td><td><input    type="text"
            size="20"  
              id="edition"
            name="edition"></td>
</tr>
<tr><td>Title:</td><td><input    type="text"
            size="20"  
              id="title"
            name="title"></td>
</tr>

<tr><td>Author:</td><td><input    type="text"
            size="20"  
              id="author"
            name="author"></td>
</tr>

<tr><td><input    type="submit"
            value="Create Catalog"  
              id="submitForm"
            name="submitForm"></td>
</tr>
</table>

</form>
</body>
</html>

Next, run the input.php script in the Apache Web server. Copy input.php to the C:\Apache2\Apache2\htdocs directory. Run the PHP script with the URL http://localhost/input.php. The input form, as illustrated in Figure 1, gets displayed:

Start adding a value for the Catalog Id field. A message gets displayed to verify if the Catalog Id field value is valid, as shown in Figure 2:

An XMLHttpRequest HTTP request is sent with each modification to the input field.

If a value is specified that is already defined in the Catalog table, a message "Catalog Id is not Valid" gets displayed, as shown in Figure 4. For example, add "catalog1" to Catalog Id field. As "catalog1" is already defined, the message "Catalog Id is not Valid" gets displayed.

You can create a new catalog entry by specifying a Catalog Id field that is valid and adding values to the other form fields. Click on the Create Catalog button to create a catalog entry, as illustrated in Figure 5:

If the Catalog Id field previously used to create a catalog entry, "catalog3" in the example, a message, "Catalog Id is not Valid", gets displayed, as shown in Figure 6:

Back to top

Conclusion

In this article, you've seen how the asynchronous client-to-server XMLHttpRequest request provided by Ajax may be combined with PHP scripts to develop dynamic Web applications. Use these examples to develop your own dynamic Web applications to access your DB2 data.

Resources

• developerWorks resource page for DB2 for Linux, UNIX, and Windows: Read articles and tutorials and connect to other resources to expand your DB2 skills.
  
  
• DB2 Express-C: Learn about the no-charge version of DB2 Express Edition for the community.
  
  
• Ajax resource center: Learn more about Ajax.
  
  
• PHP project resources: Find project information for PHP developers.
  
  
• "Using Ajax with PHP and Sajax" (developerWorks, October 2005): Learn how to use Ajax with PHP, and get an introduction to the Simple Ajax Toolkit (Sajax), a tool written in PHP that lets you integrate server-side PHP with JavaScript that makes this work.
  
  
• "Developing PHP the Ajax way, Part 1: Getting started" (developerWorks, May 2006): Create a simple photo album as an online Web application, entirely in PHP and the Simple Ajax Toolkit (Sajax).
  
  
• "Developing PHP Applications for IBM Data Servers" (IBM Redbook, May 2006): Get an introduction on using PHP with IBM Data Servers. Find installation and configuration details for setting up the IBM data servers and Apache Web application server for PHP applications.
  
  
• "Connecting PHP applications to IBM DB2 Universal Database" (developerWorks, May 2006): Combine PHP with IBM DB2 Universal Database to create Web applications.
  
  
• "Connect PHP to DB2 and Cloudscape with PDO" (developerWorks, March 2005): Learn how to obtain, install, and use PDO to connect to DB2 and Cloudscape databases, insert and retrieve data, and explore more advanced features, such as prepared statements, bound parameters, scrollable cursors, positioned updates and LOBs. Also, get a brief introduction on handling multibyte data.
  
  
• developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Browse the technology bookstore for books on these and other technical topics.
  
  

• xajax_0.2.4.zip: Download the Xajax PHP class library.
  
  
• php-5.2.0-Win32.zip: Download the PHP binaries.
  
  
• DB2 Enterprise 9: Download a free trial version.
  
  
• DB2 Express-C: Now you can use DB2 for free. Download a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.
  
  
• Download IBM product evaluation versions and get your hands on application development tools and middleware products from DB2®, Lotus ®, Rational®, Tivoli®, and WebSphere®.

• Participate in the discussion forum.
  
  
• Check out developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

		Deepak Vohra is a Web developer, a Sun Certified Java Programmer, and a Sun Certified Web Component Developer. Deepak has published articles in XML Journal, WebLogic Developer's Journal, Java Developer's Journal, ONJava.com, and Oracle Magazine.

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top