Build an xajax App With Oracle Database 10g
Your how-to guide for working with xajax.
- By Deepak Vohra
The most common PHP AJAX framework is xajax. xajax is an open source, object-oriented PHP class library that you can use for server-side processing. It allows for asynchronous communication between client-side applications and server-side applications that are comprised of PHP scripts.
In this article, you'll learn how to create an xajax application with Oracle Database 10g. However, you also can use other databases that provide PHP extensions, such as MySQL.
Because xajax is a PHP class library, you need to download and install PHP 5. You can install PHP 5 on different servers, but for this example, you'll configure PHP 5 with Apache Web server on an Microsoft Windows OS:
- Download PHP 5.1.2.
- Extract the PHP ZIP file to an installation directory (for this example, use this installation directory: C:/PHP).
- Download and install Apache HTTP Server 2.0.
- To the PATH environment system variable, add C:/PHP.
- Rename the php.init-recommended file in C:/PHP to php.ini.
Your application will store and fetch data from Oracle Database 10g. You need to enable the database extension in the php.ini configuration file. First, set the extension directory by specifying this:
extension_dir = "./ext"
Next, activate the database extension by removing the ';' from the codeline:
To install PHP 5 in the Apache HTTP server, add this to <Apache2>/conf/httpd.conf:
# For PHP 5
AddType application/x-httpd-php .php
# configure the path to php.ini
By default, the <Apache2> directory becomes C:/Program Files/Apache Group/Apache2.
After modifying the httpd.conf directory, restart Apache Web server and copy the PHP 5 files to this directory: C:/Program Files/Apache Group/Apache2/htdocs.
Finally, download xajax 0.2.4. Extract xajax_0.2.4.zip file to directory named xajax in the C:/Program Files/Apache Group/Apache2/htdocs directory. (See Resources.)
Create a Database Table
Now you're ready to create an xajax application that validates an input field in a form. Using Oracle Database 10g and its sample schemas, create an ORCL database instance and a database table in the OE schema. (You can create also the database table with a PHP script.)
Obtain a connection with the database using this method:
oci_connect ( string username,
string password [, string db])
The database parameter can be the local database instance or the tnsnames.ora entry for the database instance. Prepare a statement using this method:
oci_parse ( resource connection,
string query )
Next, run the statement using the oci_execute ( resource stmt) method, and run SQL statements to add data to the database table.
Copy the PHP script named createTable.php to the C:/Program Files/Apache Group/Apache2/htdocs directory (see Listing 1). If the HOST, PORT or SERVICE_NAME are different than those specified in createTable.php, then modify the settings in the script. Finally, start Apache Web server, and run the script in a browser with this URL: http://localhost/createTable.php. This generates a database table named Catalog, and data is added to it.
Send a Request
Next, you'll need to develop an application comprised of an input form. This input form processes data by adding a catalog entry to the Catalog database table. When a user enters data in the input field CatalogId, an XMLHttpRequest object is sent to the server to validate the added CatalogId value. If the CatalogId is not already defined in your database, then a "Catalog Id is Valid" message is displayed. If the Catalog Id is already defined in your database, then a "Catalog Id is not Valid" message is displayed, and the Create Catalog button is disabled and field values for the CatalogId are added to the form.
The xajax PHP object functions as an intermediary between the client application and the server. First, include the xajax class library:
Create a xajax object:
$xajax = new xajax();
The server-side processing is performed by PHP functions. Create the validateCatalogId($formValues) and updateCatalog($formValues) PHP functions. Both these functions take a $formValues parameter:
Next, 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 generates asynchronous wrapper functions for the registered PHP functions. The name of your wrapper function must follow the xajax_<phpfunction> format. The <phpfunction> variable is a server-side PHP function for which you must define the wrapper function.
xajax provides asynchronous form processing with the getFormValues(string formId) method. Using the getFormValues() method, you can submit an array of form field values as an argument to an asynchronous xajax function. You can also submit sections of a form instead of the complete form with this function:
A prefix parameter specifies that only the form elements starting with the indicated prefix can be submitted. The Boolean parameter, submitDisabledElements, specifies that disabled elements can be submitted. In this example, the validateCatalogId and updateCatalog PHP functions define a parameter for an array of form field values. Before an XMLHttpRequest object is initiated, you must specify that the xajax object handles requests with the processRequests() function:
An XMLHttpRequest object is initiated by a client application. In this example, the XMLHttpRequest object is initiated by the onkeyup event handler in the CatalogId input field:
The input field invokes the xajax_validateCatalogId wrapper function with a parameter that's an array of form field values. This wrapper function sends an XMLHttpRequest object to the server. The xajax object receives the XMLHttpRequest object and invokes the corresponding validateCatalogId($formValues) PHP function.
Process a Request
xajax provides the xajaxResponse class for sending a response to a client application. First, in the validateCatalogId function, create an xajaxResponse object:
$objResponse = new xajaxResponse();
The validateCatalogId function validates a new CatalogId value in the input form. From the $formValues array, retrieve the value from the CatalogId field:
Next, you need to use the PHP Oracle extension to connect with the database and determine if a row from the Catalog database table is defined for the CatalogId value. Define the username, password and database with the variables $username, $password and $db, respectively. Specify the value for the $db variable as the database SID value in <Oracle10g>/NETWORK/ADMIN/tnsnames.ora:
(ADDRESS = (PROTOCOL = TCP)
(HOST =localhost)(PORT = 1521))
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
After you've specified this value, use the oci_connect function to obtain a connection with the database:
$connection = oci_connect($username,
To select a row of data for the CatalogId value, you need to prepare an Oracle statement. Use the oci_parse(connection, query) function to compile this statement:
$stmt = oci_parse($connection,
"SELECT * from OE.CATALOG WHERE
Run the SQL query with the oci_execute(statement) function.
$r = oci_execute($stmt);
Next, use the oci_fetch_all(statement, result) function to fetch the rows in the result set:
$nrows = oci_fetch_all($stmt, $result);
The oci_fetch_all function returns the number of rows in the result set. If the result set is empty, then a Catalog database table row for the CatalogId value is not defined in the database table. Therefore, the CatalogId value added in the form is valid.
Next, you need to generate a response to send to the client application. A response contains one or more command messages. Some common command messages are listed in Table 1.
If zero rows in the result set are obtained with the CatalogId value, then you need to display a "Catalog Id is Valid" message in the validationMessage div. The addAssign method sets the innerHTML of the validationMessage div:
"innerHTML","Catalog Id is Valid");
If the result has rows, then the CatalogId value is defined in the Catalog database table. Therefore, the CatalogId value added in the input form is not valid.
If one or more rows in the result set are obtained with the CatalogId, then you need to set the innerHTML of the validationMessage div to a "Catalog Id is not Valid" message:
"innerHTML","Catalog Id is not Valid");
Next, you need to fetch values from the row in the result set row and set the values in the input form fields. Use the oci_fetch(statement) function:
Use the oci_result(statement, field) function to retrieve field values from the result set row. The field parameter may be specified as column index (1 based) or column name. For example, the journal column value is obtained with PHP code here:
Set the value attribute of the field elements in the input form with the addAssign method. For example, the value attribute of the journal element is set here:
You must also disable the submit button:
Next, return the $objResponse object from the validateCatalogId function as an XML string:
Use the updateCatalog($formValues) function to update the Catalog database table with data from the input form. If the CatalogId value is valid, then you can create a new catalog entry by adding values to the other fields in the form.
Click on the Create Catalog button to submit the form. The onsubmit event handler invokes the xajax_updateCatalog wrapper function, and it sends an XMLHttpRequest object to the server:
The xajax object receives the XMLHttpRequest object and invokes the corresponding updateCatalog($formValues) PHP function. In the updateCatalog function, retrieve the values in the form field and create a SQL statement to add a row to Catalog database table. Obtain a connection with the database and run the SQL statement.
The input.php script is shown in Listing 2. Run the input.php script in the Apache Web server. Copy input.php to the C:/Program Files/Apache Group/Apache2/htdocs directory. Then run the script with this URL: http://localhost/input.php. The input form is displayed (see Figure 1).
Next, start adding values to the CatalogId field. An XMLHttpRequest request is sent to the server with each addition to the text field. The input page is updated with each response from the server, which contains instructions about the validity of the values. A message is displayed to verify if the values in the CatalogId field are valid (see Figure 2).
If one of these values is already defined in the Catalog database table, then a "Catalog Id is not Valid" message is displayed. For example, since "catalog1" is already defined in the database table, then the message is displayed (see Figure 3).
You can create a new catalog entry by specifying a valid CatalogId value in the field and adding values to the other form fields. To create a catalog entry, click on the Create Catalog button (see Figure 4). If you specify a CatalogId field that was previously used to create a catalog entry (for example, "catalog4"), a "Catalog Id is not Valid" message is displayed (see Figure 5).
About the Author
Deepak Vohra is a Sun-certified Java programmer and Web component developer. He has published many articles in different industry publications and journals. You can contact him at email@example.com.