Use DB2 native XML with PHP

Leverage DB2 native XML functionality and PHP to simplify apps using XML data

Learn about the effectiveness of using the native XML capabilities coming in the next version of DB2® Universal Database™ for Linux®, UNIX®, and Windows® to simplify application code and the relational schemas. This article looks at the impact of schema evolution on the application and walks the reader through a usage scenario to illustrate the ease of setting up a PHP environment; the ease of integrating DB2 native XML functionality with PHP applications, including Web services written in PHP and XQuery; and the benefit of pushing the business logic and data transformations into the database, using XQuery, stored procedures, and views. To highlight the impact of using DB2 native XML support on PHP application code and relational schema design, the scenario creates a parallel environment using a database that does not have any XML capabilities (for example, MySQL). We show the difference in the application code, database queries, and the relational schemas for the two environments.

Share:

Hardeep Singh (hardeep@us.ibm.com), Architect DB2 XML tooling, DB2 XML application migration, IBM, Software Group

Photo: Hardeep SinghHardeep Singh is a member of DB2 Native XML team. He is the architect for DB2 XML tooling (DB2 Xquery Builder). He is also responsible for the XML application migration strategy for the next version of DB2 UDB. He has over 21 years of industry experience. .



Amir Malik (a@unoc.net), Student intern, IBM, Software Group

Amir MalikAmir Malik has worked as a co-op with the DB2 Native XML team for the past year at IBM's Silicon Valley Laboratory. He is currently completing a B.S. in Computer Engineering at UC Santa Cruz.



27 October 2005

Also available in Russian

Introduction

PHP enables a simple Web application development and deployment environment. This is one of the reasons for its popularity. DB2's native XML capabilities, coming in the next version and referred to as the DB2 Viper release, further simplify the development process. This simplification appears in the form of:

  • Smaller application code size and reduced complexity
  • Simpler relational schema
  • Better management of schema evolution due to changing business requirements

To understand the foundation of DB2's native XML support, we recommend that you start by reading a DB2 Magazine article, Firing Up the Hybrid Engine by Anjul Bhambhri, DB2 development manager for native XML support.

In this article, we build on that foundation and demonstrate the effectiveness of using DB2's native XML capabilities to simplify the application code and the relational schemas. We will also look into the effects of changes in the business requirements on the data (schema evolution) and its impact on the application code and the relational schema.

To illustrate our reasoning we will follow a usage scenario modeled around an online shop selling antique silverware to registered customers.

Some of the points we will show in the course of the scenario are:

  • The ease of setting up a PHP environment
  • The ease of integrating DB2 native XML functionality with PHP applications, including Web services written in PHP and XQuery
  • Pushing the business logic and data transformations into the database, using XQuery, stored procedures, and views.

The DB2 XML features we will be using in the scenario will cover the following areas:

  • XML document storage in a column as a parsed structure
  • Using XQuery to search and publish
  • Support for XML in DB2 stored procedures and views
  • Use of XML indexes to boost performance

To highlight the impact of using DB2 native XML support on PHP application code and relational schema design, the scenario will create a parallel environment using a database that does not have any XML capabilities (for example MySQL). We will explore the difference in the application code, database queries and the relational schemas for the two environments. We will explain our justification for choosing a particular code, schema, or query, and the alternatives if possible.


Scenario

The scenario is modeled around an online shop selling antique silverware to registered customers. Since one of the goals of the scenario is to illustrate the different database environments and their impact on the application code, we will try to do a side-by-side demonstration of the two applications (one using DB2 native XML, and the other an open source RDBMS like MySQL with limited or no XML capabilities).

For this reason, a customer visiting the Web site will be presented with a page that consists of two vertical panels. Each panel will show a version of the same application featuring the same user experience but employing different databases at the back end:

  • DB2 with native XML support
  • Another RDBMS (in this case, DB2 without using any XML features)

To reveal the difference in the application code, each panel is further split into two horizontal frames, with the upper frame showing the online store and the lower part showing snippets of the code. On any action taken by the user, such as clicking on a category or a product image, a new page is generated in the upper part. The lower part shows the code that was needed to create this page.

Figure 1. Example application panels
Example application panels

This will demonstrate that although the user's experience does not change in either case, the code complexity varies a lot. This contrast will highlight the advantage of exploiting DB2's native XML capabilities for normal SMB applications written in PHP.

Note: An assumption we are making for this scenario is that the business data is already in XML, even if the database might not have any XML capabilities. This will result in the PHP application code using XML features (like SimpleXML) available to it. The database with limited or no XML capabilities would store the XML data either as a CLOB/BLOB data type, or shredded to relational fields.

The Web site will offer the users an index that lists the categories and brands of all the silverware available in the shop. When the user clicks on a category or brand, a list of items in that category or brand will be displayed. Selecting any of these items in the list will result in the details of the item being shown on the page. The user will have the ability to add these to a shopping cart. Once the user submits the order, a purchase order is created and the user is presented an invoice based on this purchase order. At any time the user can check the items in the shopping cart. Users can also get a report on all items that they have ordered in the past.


Application architecture

Figure 2 shows the basic application architecture for our sample application.

Figure 2. Application architecture
Application architecture

Relational and XML schemas

XML documents and schemas

Native XML store does not require an XML column to be associated with a particular XML schema. Any validation needed for the XML document being inserted to the database is done explicitly in the insert statement using the SQL/XML function XMLVALIDATE.

Relational schema

The relation schema for storing these XML documents will vary for the two databases.

For DB2 native XML, there will be three tables, with each table having two columns.

Figure 3. DB2 native XML schema
Relational schema for native XML scenario

For RDBMS without XML support there will be four tables, each with multiple columns:

Figure 4. Relational schema for scenario without XML support
Relational schema for scenario without XML support

Observe the simplicity of the relational schema for DB2 native XML as compared to the RDBMS without XML support.

We have tried to keep the purchase order table schema simple in the relational-only database by storing the purchase order document as a BLOB. The consequences of this will be apparent when we look at generating order history.

DB2 PHP driver

Before we start on the PHP application code, let's try to understand the DB2 driver for PHP. The ibm_db2 driver supports two methods of connecting to a database: cataloged and uncataloged. A cataloged connection can be a local database (if there is a DB2 server running locally), or it can be a remote DB2 server node. The second method usually applies for remote uncataloged connections, and requires you two build up a connection string (similar to a JDBC URL) to make an uncataloged connection. The following code connects to a cataloged database. (The client application does not need to know or care about whether a cataloged connection is local or remote.)

$conn = db2_connect($dbname, $dbuser, $dbpass);
if(!$conn) {
echo db2_conn_errormsg();
die("Unable to connect to database!");
}

It is also possible to create a persistent connection to the database using db2_pconnect. A persistent connection is not actually closed when db2_close is called, since the connection handle is retained across requests. For more information about the IBM DB2 driver for PHP, visit http://www.php.net/manual/en/ref.ibm-db2.php. In the code snippets that follow, it is assumed that $conn is a valid connection handle.

Populating the database

Before the Web site can go live, the databases need to be populated with the customer information and the product catalog. For our scenario we will not elaborate on how this data was obtained. It is assumed to be in files on the local file system as XML documents. Sample snippets of the PHP code needed to connect to the database and execute the SQL insert statements are shown below.

DB2 Viper

Because each product document includes the product ID as an attribute, we will need to extract it using PHP's SimpleXml API.

Note: This API is much easier to use than manipulating DOM objects, which was the only option before PHP version 5.

  1. Create a database connection
    $conn =db2_connect($dbname, $dbuser, $dbpass);
  2. Open the document from the file into a variable
    $fileContents = file_get_contents("products/p1.xml");
  3. Create a simple XML object from this variable
    $dom = simplexml_load_string($fileContents);
  4. Extract the product ID from the document
    $prodID = (string) $dom["pid"];
  5. Create a prepared statement to insert the XML document into the database
    $stmt =db2_prepare($conn, "INSERT INTO xmlproduct VALUES (?, ?)");
  6. Pass the product ID extracted from the document along with the document as a parameter to the query
    db2_execute($stmt, array($prodID, $fileContents);

Notice that inserting data to an XML column is no different from inserting into any CLOB column. Because this new version of DB2 supports implicit parsing of XML data on insert, we do not need to explicitly call XMLPARSE on the incoming value. If we had wanted to preserve extraneous white space around XML tags, we could have used the XMLPARSE function with the PRESERVE WHITESPACE option.

Note: All queries in these code snippets have been made bold to distinguish them from the PHP application code.

Non-XML RDBMS

Since this database does not have any XML capabilities, the product document needs to be decomposed into the two relational tables. Mapping information between the relational and XML schemas is directly embedded into the PHP application code.

  1. First load document into DOM:
    $fileContents = file_get_contents("$products/p1.xml");
    $dom = simplexml_load_string($fileContents);
  2. Now shred individual elements of the product into local variables:
    $prodID = (string) $dom["pid"];
    $prodName = (string) $dom->description->name;
    $prodDetails = (string) $dom->description->details;
    $prodPrice = (float) $dom->description->price;
  3. The image URLs for each product need to be stored in a separate image table:
    $images = array();
    foreach($dom->description->images->image as $image) {
    switch((string) $image[type']) {
    case thumbnail':$prodImgThumb = (string) $image;
    $prodImgAlias = (string) $image[alias'];
    if(!$prodImgAlias) $prodImgAlias = NULL;
    $stmt = db2_prepare($conn, "INSERT INTO sqlimages(Pid,Type,Alias,Location)
     VALUES (?, ?, ?, ?)");
    db2_execute($stmt, array($prodID, thumbnail', $prodImgAlias, $prodImgThumb));
    case full':
    $prodImgFull = (string) $image;
    $prodImgAlias = (string) $image[alias'];
    if(!$prodImgAlias) $prodImgAlias = NULL;
    $stmt = db2_prepare($conn, "INSERT INTO sqlimages(Pid,Type,Alias,Location)
    VALUES (?, ?, ?, ?)");
    db2_execute($stmt, array($prodID, full', $prodImgAlias, $prodImgFull));
    }
    }
  4. The current implementation, the ibm_db2 driver, does not gracefully handle NULL variables as arguments to the execute function; hence we use an unobtrusive workaround:
    if(!$prodBrand) $prodBrand = " ";
    if(!$prodCategory) $prodCategory = " ";
    if(!$prodImgFull) $prodImgFull = " ";
    Note: This issue has been fixed in the latest version of ibm_db2 driver. You can get it from pecl.php.net/package/ibm_db2
  5. Now save the product information in the product table:
    $stmt = db2_prepare($conn, "
    	INSERT INTO sqlproduct (Pid, Name, Details, Brand,
    		Category, Price, Weight, Size, Description)
    		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
    db2_execute($stmt, array($prodID, $prodName,$prodDetails, $prodBrand, $prodCategory,
    	$prodPrice, $prodWeight, $prodSize, $fileContents));

Creating the main page

The main page consists of an index of categories and brands for all the products available in the online shop. The area on the right hand side of the index shows a list of all items.

Figure 5. Main page
Main page

Creating the index list of categories and brands

The index is created by querying the database for the list of unique categories and brands for all the products in the database. This list is created when the application starts up.

DB2 Viper

  1. First create a DB2 view to list the categories using an XQuery that loops through all the products and returns a sequence of all the unique categories:
    CREATE VIEW Categories(Category) AS SELECT DISTINCT(XMLCAST(
    XMLQUERY(for $i in $t/product/description/category return $i'
    PASSING BY REF T.DESCRIPTION AS "t" RETURNING SEQUENCE)
    AS VARCHAR(128))) FROM
    xmlproduct AS t
  2. Now call the view from the application:
    $stmt = db2_exec($conn, "SELECT * FROM Categories>");
    while(list($cat) = db2_fetch_array($stmt)) {
    echo "<a href=\"catalog.php?category=" . urlencode($cat) . "\">$cat</a><br/>"; }

Non-XML RDBMS

Create a distinct list of categories from the product table:

$stmt = db2_exec($conn, "SELECT DISTINCT(category) FROM SQLPRODUCT");
while(list($cat) = db2_fetch_array($stmt)) {
echo "<a href=\"catalog.php?category=" . urlencode($cat) . 
     "\">$cat</a><br/>";}

The application code in both cases is similar. Creating a view of the XML data helps to abstract the structure of the product XML from the application code, by allowing us to simply query the view. The XQuery in the view would need to be changed to find the brand element, and similarly the SQL call would need to look at the Brand column instead.

Impact of schema evolution on the index lists

Based on customer feedback, we need to let our users browse the site for items that are either silver-plated or made of sterling silver. Let's look at the effect of adding subcategories to the index on: XML Schema, relational schema, queries, and the PHP application code.

Effect on XML Schema and document instance

A new attribute (catx) is added to the category element in the product XML schema. All new XML documents for the products would now have this attribute appropriately populated with sterling or silverplated:

<category catx="silverplated">Miscellaneous</category>

Effect on relational schema

  • DB2 Viper
    This requires no change to the relational schema since the XML document is stored in a single column.
  • Non-XML RDBMS
    In a relational-only database you would need to change the schema for the product table by adding another column called catx. This could involve dropping and reinserting all the product documents.

Effect on queries

  • DB2 Viper The XQuery needed to create the index would change to include this new attribute in the criteria. Similarly, XQueries used to list items based on a selection in the index would also change to take in the new criteria.
  • Non-XML RDBMS
    Insert statement would change to include the new column.

The query needed to create the index would change to include this new column in the WHERE clause. Similarly, queries used to list items based on a selection in the index would also change to take in the new criteria.

Effect on application code

  • DB2 Viper
    There would be no change to the application code
  • Non-XML RDBMS
    • Additional DOM code would be required to shred out the sub-category information.
    • Additional parameters to the INSERT statement would be needed.
    • Potentially, all of the data would need to be reinserted, creating end-user downtime.

Listing items when the user clicks on a category or brand

When a user clicks on a particular category or brand, a list of all items in that category or brand is generated. Each item in the list has a brief description and a URL to a thumbnail image. This list is formatted and displayed on the main page.

Figure 6. List of items in a category
List of items in a category

DB2 Viper

In DB2 the XQuery not only creates the lists but also transforms it to an HTML output that can be directly consumed by the browser. This feature of the XQuery makes it possible to push not only the business logic but also the publishing to the database server, in effect making the middle tier application very simple and thin. This is an ideal reason for using PHP rather than Java™ or VS .NET®.

$xquery =for $i in $t/product
let $thumb := $i/description/images/image[@type="thumbnail"]
where $i/description/category = " . htmlentities($category) . "
return
<div class="float">
<a href="product.php?pid={$i/@pid}">
<img border="0" src="data/images/{$thumb}.jpg" height="100" width="100"/>
</a>
<p> <a href="product.php?pid={$i/@pid}">{$i/description/name}
    </a> </p>
    </div>;>

    $stmt = db2_prepare($conn, "SELECT XMLSERIALIZE(XMLQUERY(
    $xquery' PASSING BY REF T.DESCRIPTION AS \"t\"
    RETURNING SEQUENCE) AS CLOB(32K)) FROM xmlproduct AS t");

    db2_execute($stmt);
    while(list($product) = db2_fetch_array($stmt)){echo $product;}

Looking at the above application code, we note that the PHP code is reduced to two lines, while the database query contains most of the logic.

Note: We need to escape any incoming CGI variables using HTML entities (such as the category), so that they do not contain any illegal non-escaped entities when it is plugged in to the XQuery.

Although XQuery can do publishing transformations, it might not be desirable to do so in many cases. It would be more appropriate for design, performance, and style reasons to uses XSLT transformations in the middle-tier or client to create the final Web page. In many situations, it is more convenient to bundle the two together into a single query. Use of XQuery does not preclude the developer from using XSLT for transformation. Our attempt here is to show the power of the XQuery to not only do data searches but also run complex business logic and transformations on that data.

Non-XML RDBMS

First we query the database to get a row set of all the items that match the selected category:

<?php
$sql = "SELECT P.Pid, P.Name, I.Location FROM sqlproduct P, sqlimages I WHERE P.Category = ? AND I.Pid = P.Pid AND I.Type = ?"); $stmt = db2_prepare($conn, $sql);
db2_execute($stmt, array($category, "thumbnail"));

Next we loop over the result set and create the Web page using the PHP code:

while(list($prodPid, $prodName, $prodImg) = db2_fetch_array($stmt)) {
?>
<div class="float">
<a href="product.php?pid=<?php echo $prodPid ?>">
<img border="0" src="data/images/<?php echo $prodImg ?>.jpg" height="100" width="100"/>
</a>
<p> <a href="product.php?pid=<?php echo $prodPid ?>"><?php echo $prodName ?></a></p>
</div>
<?php
}
?>

Although the publishing code is quite similar to the one in the XQuery, a significant difference is in the fact that this code is run and maintained in the middle tier as opposed to the database server. So if the XQuery was registered in the database server as a stored procedure, the maintenance of that would be done as a part of the database. We will see how to do this in the next step.

Product detail

When the user clicks on any product listed in the store, the application creates a detailed product page containing information such as description, size, price, and possibly additional images of the product.

Figure 7. Product detail
Product detail

DB2 Viper

The XQuery that creates the details information for the product is saved as a stored procedure.

Stored procedure to create product detail

The getProduct stored procedure is written in SQL/PL and accepts the product ID as an argument, and returns a cursor to a record set. The stored procedure basically executes an XQuery which generates a layout showing the product details, along with its picture, as well as additional thumbnails on the side of the page.

CREATE PROCEDURE getProduct(IN id VARCHAR(10))
DYNAMIC RESULT SETS 1 LANGUAGE SQL BEGIN BEGIN DECLARE c_cur CURSOR WITH RETURN FOR SELECT XMLSERIALIZE(XMLQUERY(for $i in $t/product let $thumb := $i/description/images/image[@type="thumbnail"] let $name := $i/description/name/text() let $details := $i/description/details/text() let $price := $i/description/price let $size := $i/description/size return <div id="Product"> <h2>{$name}</h2> { for $j in $i/description/images/image[@type != "thumbnail"][1] return <div class="ProductImageMain"> <img name="mainPic" border="0" src="data/images/{$thumb}.jpg" width="200"/> </div> } <p id="ProductDetails"> <strong>Details: </strong> {$details}<br/> <br/> <strong>Price: </strong> ${$price/text()}<br/><br/> <strong>Size: </strong> {$size/text()} {$size/@units/text()} <br/><br/> <a href="cart.php? action=add&pid={$i/@pid}" >Click here to Buy</a><br/> </p> <div id="ProductImages"> { for $j in $i/description/images/image[@type != "thumbnail"] [position() != 1] return <div class="ProductImageExtra"> <a href="javascript:void()" onMouseover="document.mainPic.src= data/images/{$j/text()}.jpg''" onMouseout="document.mainPic.src=
data/images/{$i/description/images/image[@type != "thumbnail"][1]}.jpg''"> <img border="0" src="data/images/{$j/text()}.jpg" width="100"/> </a> </div> } </div> </div> PASSING T.DESCRIPTION AS "t" RETURNING SEQUENCE) AS CLOB(32K)) FROM xmlproduct T WHERE Pid = id;
OPEN c_cur; END; END

Note: The ampersands in the XQuery need to be escaped. This is true for all special characters.

Important: Since XQuery deals with XML types, all the returned data will have the special characters escaped. A way to work around this is to cast the return of the query to a VARCHAR using a cast function.

Application code

$stmt = db2_prepare($conn, "CALL getProduct(?)");
db2_execute($stmt, array($pid));
list($product) = db2_fetch_array($stmt);
echo $product;

The application is now reduced to a simple call to a stored procedure and the task of outputting the result to the browser. Most of the application code is now maintained and run in the database server. Another fact that comes out is that querying data from a stored procedure is as simple as executing the query directly from your code, if not easier.

Non-XML RDBMS

Although it would be possible to create a stored procedure in the relational case, for this case it would be much more complicated.

  1. Fetch the product details from the product table:
    $stmt = db2_prepare($conn, "SELECT P.Name, P.Details, P.Price, 
    	P.Size, I.Location, I.Alias FROM sqlproduct P, sqlimages I 
    	WHERE P.Pid = ? AND P.Pid = I.Pid AND I.Type = ? 
    	FETCH FIRST ROW ONLY");
    	
    db2_execute($stmt, array($pid, full'));
    list($prodName, $prodDetails, $prodPrice, $prodSize, $prodImgThumb,
    $prodImgAlias) = db2_fetch_array($stmt);
  2. Now create the display by putting the HTML tags around the data:
    <div id="Product">
    <h2><?php echo $prodName ?></h2>
    <div class="ProductImageMain">
    <img border="0" src="data/images/<?php echo $prodImgThumb ?
    >.jpg" width="200"/>
    </div>

    The intermixing of PHP and HTML code can be readily seen here by the use of the<?phpand?>constructs:

    <p id="ProductDetails">
    <strong>Details: </strong> <?php echo $prodDetails ?><br/> <br/>
    <strong>Price: </strong> $<?php echo $prodPrice ?><br/> <br/>
    <strong>Size: </strong> <?php echo $prodSize ?><br/> <br/>
    <a href="cart.php?mode=sql&action=add&pid=<?php echo $pid ?>
    ">Click here to Buy</a><br/>
    </p>
    <div id="ProductImages">
  3. Query the images associated with this product and add their URL to the output Web page:
    <?php
    $stmt = db2_prepare($conn, "SELECT DISTINCT(Location) FROM sqlimages WHERE Pid = ? AND Type = ? AND NOT Location = ?"); db2_execute($stmt, array($pid, full', $prodImgThumb)); while(list($prodImg) = db2_fetch_array($stmt)) {
    ?> <div class="ProductImageExtra"> <img border="0" src="data/images/<?php echo $prodImg ?>.jpg" width="100"/> </div> <?php } ?> </div> </div>

Because the product data has been shredded into two tables and the PHP and HTML code has been intermixed, we need to make two separate queries to get the product details and the image locations.

Shopping cart

The shopping cart is an integral part of any online shop experience. Because of this, it is an ideal utility to be created as a Web service. This Web service can be customized to calculate price, tax, currency conversion, shipping costs, and so on. In our sample implementation, we have created the Web service using PHP and XQuery to do price and tax calculations.

Items added to the shopping cart by the customer are saved as a client-side cookie. This cookie is read in as an associative array in PHP and assigned to the variable $cart.

Figure 8. Shopping cart
Shopping cart

DB2 Viper

Web service provider and query

  1. Once again we have used the XQuery to embed both output layout and business logic in one query. The Web service also accepts a sales tax rate argument besides the shopping cart information, which is an XML document containing the product IDs and their respective quantities. This allows us to pass the shopping cart as an XML value directly into the XQuery.
    <?php
    function getCart($cart, $taxrate) { global $conn; $result = ""; $xquery = for $dummy in (1)
  2. Inside the XQuery the variable $cart can be iterated through just like any other native XML document:
    let $items := for $i in $cart/items/item
    let $product := db2-fn:xmlcolumn("XMLPRODUCT.DESCRIPTION")
    /product[@pid = $i/@pid]/description
    let $name := $product/name/text()
    let $price := $product/price/text()
    let $itemPrice := if($price = 0 or empty($price)) then ("$0.00") 
    else (concat("$", $price))
    return
    <tr>
  3. For each item, calculate the total cost for that item using the quantity passed from the shopping cart information and the price obtained from the product catalog information in the database:
    <noframes>{$price * $i/@quantity}</noframes>
    <td class="itemDetails">
    <a href="product.php?mode=xml&pid={$i/@pid}">{$name}</a>
    </td>
    <td class="itemQuantity">
    {xs:integer($i/@quantity)}
    <br/>
    <a href="cart.php?mode=xml&action=del&pid=
    {$i/@pid}">Remove</a>
    </td>
    <td class="itemPrice">
    {$itemPrice}
    </td>
    </tr>
    return
    <x>
  4. Return an XML (XHTML) structure containing cost calculations for each item, along with the publishing information:
    {$items}<tr><td class="itemDetails"> </td><td class="itemQuantity">
    <strong>Subtotal</strong></td><td class="itemPrice">
  5. Calculate the total cost for all the items in the cart from the returned structure. Effectively we are using the output of one portion of the query as an input variable in another part of the query.
    Total all
    <strong> ${ sum( $items/noframes/text() ) } </strong>
    </td></tr><tr>
    <td class="itemDetails"> </td>
    <td class="itemQuantity"><strong>Tax 
    ({$tax * 100}%)</strong></td>
    <td class="itemPrice">
  6. Use the tax variable passed to the Web service from the client to calculate the payment.

    Note: The sum of all the items that was calculated above could have been allocated to a variable using a let statement. This variable could then have been used in calculating the tax and the total payment.

    <strong> ${ xs:decimal(sum($items/noframes/text())) 
    * $tax } </strong></td></tr><tr>
    <td class="itemDetails"> </td>
    <td class="itemQuantity"><strong>
    Grand Total</strong></td>
    <td class="itemPrice">
    <strong> ${ xs:decimal(sum($items/noframes/text())) * (1 + $tax) } <
    /strong></td></tr></x>;

    We leave it as an exercise to the reader to rewrite the outermost for loop in a more elegant fashion.

  7. Although XQuery does not support runtime parameter binding, there is a workaround. Runtime parameters can be passed into the XQuery by using the XMLQuery functions PASSING BY clause:
    $stmt = db2_prepare($conn, "VALUES( XMLSERIALIZE( 
    XMLQUERY($xquery' PASSING BY
    REF CAST(? AS XML) AS \"cart\" , CAST(? AS DECIMAL
    (10,8)) AS \"tax\" RETURNING SEQUENCE) AS CLOB(32K)))");

    Note the shopping cart information is passed into the query as an XML string. This string is converted to an XML type by using the CAST (? As XML) function

  8. If the query is registered as a stored procedure, then the PHP code required to run this Web service is now reduced to these few lines:
    if($stmt) {
    if(!db2_execute($stmt, array($cart, $taxrate))) {
    return db2_stmt_errormsg($stmt);
    }
    list($result) = db2_fetch_array($stmt);
    if(!$result) return db2_stmt_errormsg($stmt);
    } else {
    $result = db2_stmt_errormsg();
    }return $result;}
  9. Register the Web service:
     $server = new SoapServer(null, array(uri' =
       > http://ibm.com/db2/xml/php'));
    $server->addFunction(getCart');
    $server->handle(); ?>

As you can see from the above code sample, it is very simple to create a Web service using PHP and DB2 native XML support. Furthermore, we did not need to define a WSDL (Web Services Description Language) document due to the simplicity of our service.

Web service client

Now this Web service can be called from the client code to display the cart information on the Web site. The actual shopping cart page is just a client that calls the Web service.

  1. Create the XML string for the cart information to be passed as an argument to the Web service:
    <?php
    $cartXML = "<items>"; foreach($cart as $cpid => $quantity) { $cartXML .= "<item pid=\"$cpid\" quantity=\"$quantity\"/>"; } $cartXML .= "</items>";
  2. Connect to the Web service
    $client = new SoapClient(null, array(location' => 
        http://127.0.0.1/cartsvc.php',
    uri' => http://ibm.com/db2/xml/php'));
    $taxrate = 0.0;
  3. Call the Web service and output the returned string to the browser:
    echo $client->getCart($cartXML, $taxrate); ?>

Note:Because the shopping cart is implemented as a Web service, it is can be called from any language.

Non-XML RDBMS

The relational version of this example does not feature a Web service or sales tax calculation. Although the query looks simpler than the XQuery, we must query the database for each item in the cart, resulting in increased database traffic and slightly more loop-oriented application code. To create a Web service from the relational part would not have been difficult due to the ease with which Web services can be created with PHP. As in the XML version, the getCart function would have accepted the cart as an argument, the format of which would need to be decided in advance. If this would be an XML value, a DOM would need to be used to read the cart, or maybe it could be an associative array, with little change needed to the above code. On the other hand, if you wanted to create a stored procedure that displays the shopping cart with a relational database, you would need to concatenate HTML content with data retrieved from the database. The effort involved would be tedious enough to make more sense to keep the presentation code out of a stored procedure, and in the application.

<?php
foreach($cart as $pid => $quantity) { $stmt = db2_prepare($conn, "SELECT Name, Price FROM sqlproduct WHERE Pid = ?"); db2_execute($stmt, array($pid)); if($stmt) { list($prodName, $prodPrice) = db2_fetch_array($stmt); ?> <tr> <td class="itemDetails"> <a href="product.php?pid=<?php echo $pid ?>"><?php echo $prodName ?></a> </td> <td class="itemQuantity"> <?php echo $quantity ?> <br/> <a href="cart.php?action=del&pid=<?php echo $pid ?>">Remove</a> </td> <td class="itemPrice"> $<?php echo $prodPrice ?> </td> </tr> <?php}}?>

Purchase order

Once the user chooses to check out, the shopping cart is sent to the application, and an XML purchase order document is generated for the items that were purchased.

The XML code and the relational code to create the purchase order are mostly identical, with the only difference being the query to find the current price of the product.

$stmt = db2_prepare($conn, "VALUES (NEXT VALUE FOR POid)");
db2_execute($stmt);
list($POid) = db2_fetch_array($stmt);
foreach($cart as $pid => $quantity) {
$xquery = $t/product/description/price/text()';
$stmt = db2_prepare($conn, "SELECT XMLSERIALIZE(XMLQUERY($xquery' PASSING BY REF
T.DESCRIPTION AS \"t\" RETURNING SEQUENCE) AS VARCHAR(8)) FROM xmlproduct
AS t WHERE Pid = ?");
db2_execute($stmt, array($pid));
list($price) = db2_fetch_array($stmt);

Although we could have used the DOM to create the purchase order, in this case it was simpler to concatenate XML fragments.

$PO .= " <item pid=\"$pid\" quantity=\"$quantity\" price=\"$price\"/>\n";}
$stmt = db2_prepare($conn, "INSERT INTO xmlporder (POid, POrder) VALUES (?, ?)");
db2_execute($stmt, array($POid, $PO));

The purchase order (PO) is stored as a CLOB in the relational database (as opposed to being shredded). An advantage of intact storage is that any schema evolution due to changes in the purchase order (additional information like shipping), is painless. Storing the PO as a CLOB allows you to use the DOM in the application code to retrieve the relevant information. However, the gain in relational storage simplicity and schema evolution is offset by reduced query performance, as we will see when we try to create a purchase history report.

Invoice

An invoice is returned to the customer on checkout. This invoice is generated by querying the purchase order that was just created. Since the purchase order contains no detailed product information, a separate query is needed to find the product details from the product table.

Figure 9. Invoice
Invoice

DB2 Viper

  1. Once again we use a single XQuery to create the final invoice
    $xquery =
    for $po in $t/purchaseOrder
    let $sum := for $item in $po/items/item 
    return $item/@quantity * $item/@price
    let $items := for $item in $po/items/item
  2. Create a join between the purchase order and the product table to get product details
    let $name := for $i in db2-fn:xmlcolumn("XMLPRODUCT.DESCRIPTION")/product 
    where $i/@pid = $item/@pid return $i/description/name/text()
    return
    <tr><td class="itemDetails">
    {$name}
    </td><td class="itemQuantity">
    {xs:string($item/@quantity)}
    </td><td class="itemPrice">
    ${xs:string($item/@price)}
    </td></tr>
    
    return
    <x>
    {$items}
    {$po/text()}
    <tr><td class="itemDetails"> </td>
    <td class="itemQuantity"><strong>Total</strong></td>
    <td class="itemPrice"><strong>${sum($sum)}</strong></td>
    </tr></x>;

It is interesting to note that the above XQuery is similar to the one used to display the contents of the shopping cart:

$stmt = db2_prepare($conn, "SELECT XMLSERIALIZE(XMLQUERY($xquery' PASSING BY REF
T.PORDER AS \"t\" RETURNING SEQUENCE) AS CLOB(32K)) FROM
xmlporder AS t WHERE POid = ?");
db2_execute($stmt, array($id));
list($po) = db2_fetch_array($stmt);
echo $po;

Also note that the amount of PHP application code is minimal, since most of the business logic and transformation is in the query.

Non-XML RDBMS

  1. $stmt = db2_prepare($conn, "SELECT POrder FROM sqlporder WHERE POid = ?");
    db2_execute($stmt, array($id));
    $sum = 0.0;
    while(list($po) = db2_fetch_array($stmt)) {
  2. Because the purchase order is stored as a CLOB, we need to use the DOM to access each product, and its quantity and price. Use a simple DOM to extract the data out from the purchase order:
    $dom = simplexml_load_string($po);
    foreach($dom->items->item as $item) {
    $cpid = (string) $item[pid'];
    $price = (float) $item[price'];
  3. We keep track of the total item price in each row of the HTML table:
    $sum += $price * (integer) $item[quantity'];
  4. A separate query to the databse is needed to find the name of each item.
    $stmt2 = db2_prepare($conn, "SELECT Name FROM sqlproduct WHERE Pid = ?");
    db2_execute($stmt2, array($cpid));
    if($stmt2) {
    list($prodName) = db2_fetch_array($stmt2); ?>
    <tr> <td class="itemDetails"> <?php echo $prodName ?> </td> <td class="itemQuantity"> <?php echo $item[quantity'] ?> </td> <td class="itemPrice"> $<?php echo $price ?> </td> </tr> <?php}}}

The relational version of the code introduces more logic into the application.

Reporting the customer's order history

Customers can list all their purchases by clicking on the Order history link in the index:

Figure 10. Order history
Order history

DB2 Viper

  1. We use SQL/XML to return the customer's purchase orders sorted by date. For each purchase order an XQuery is executed that returns a formatted result of each product subtotal.
    $xquery =
    for $po in $t/purchaseOrderlet $items := for $item in $po/items/item
  2. A join is performed inside the XQuery to show the name of each product, since the purchase order only stores product ID, price, and quantity:
    let $name := for $i in db2-fn:xmlcolumn("XMLPRODUCT.DESCRIPTION")/product
    where $i/@pid = $item/@pid return $i/description/name/text()
    return
    <p><pre>{xs:string($item/@quantity)} x @ ${xs:string($item/@price)} 	 
    {$name}</pre></p>
    return
    <x>
    <h3>Order #{xs:string($po/@id)} placed on {xs:string($po/@orderDate)}</h3>
    {$items}
    </x> ;
    
    $stmt = db2_prepare($conn, "SELECT XMLSERIALIZE(XMLQUERY
    ($xquery' PASSING BY REF
    T.PORDER AS \"t\" RETURNING SEQUENCE) AS CLOB(32K)) 
    FROM xmlporder AS t
    ORDER BY POid DESC");
    db2_execute($stmt);
    while(list($po) = db2_fetch_array($stmt)) {echo $po; }

Non-XML RDBMS

  1. In the relational version, we must execute one query to get a list of products in each purchase order:
    $stmt = db2_prepare($conn, "SELECT POid, POrder FROM sqlporder 
    ORDER BY POid DESC");
    db2_execute($stmt);
    while(list($POid, $po) = db2_fetch_array($stmt)) {
  2. Because the purchase order is stored as a CLOB in the relational database, we must use DOM to access the order date and individual products. PHP 5's SimpleXML functionality comes in handy here again.
    $dom = simplexml_load_string($po);
    ?>
    <h3>Order #<?php echo $POid ?> 
    placed on <?php echo $dom[orderDate'] ?></h3>
    <?php
    foreach($dom->items->item as $item) {
  3. For each product, we must query the detail, in this case, its name:
    $stmt2 = db2_prepare($conn, "SELECT Name FROM sqlproduct WHERE Pid = ?");
    db2_execute($stmt2, array( (string) $item[pid'] ));
    while(list($prodName) = db2_fetch_array($stmt2)) {
    ?>
    <pre><?php echo $item[quantity'] ?> x @ $<?php echo $item[price'] ?> 
         	 <?php echo $prodName ?></pre>
    <?php}}}

XML indexes

Although we have not created any relational indexes, we will create some XML indexes to explain how DB2 native XML support allows us to create an index from any element or attribute in the document. Each product document has a unique Product ID, as well as other vital information that is commonly used when browsing the catalog, including category, brand, and name. For the product XML column, we will create those four indexes.

CREATE UNIQUE INDEX prod_pid ON xmlproduct(description) GENERATE KEY USING
	XMLPATTERN /product/@pid' AS SQL VARCHAR(10)

CREATE INDEX prod_name ON xmlproduct(description) GENERATE KEY USING
	XMLPATTERN /product/description/name' AS SQL VARCHAR(128)

CREATE INDEX prod_category ON xmlproduct(description) GENERATE KEY USING
	XMLPATTERN /product/description/category' AS SQL VARCHAR(128)

CREATE INDEX prod_brand ON xmlproduct(description) GENERATE KEY USING
	XMLPATTERN /product/description/brand' AS SQL VARCHAR(128)

As you can see, it is possible to create an index from an attribute as well as an element.


Setting up the environment to run the application

Setting up Apache and PHP

We will first need to set up the PHP development environment and a Web server to run server-side PHP scripts. These instructions are Windows-centric, but the code also runs unmodified on Linux and other UNIX-like platforms. Refer to the PHP documentation on how to set up the PHP module with Apache.

  1. Make sure you have DB2 Version 8.2 or later client libraries installed.
  2. Download and install the latest 2.0 version of Apache from http://httpd.apache.org/. For development work, it is best not to install Apache as a service, since you may find yourself restarting it frequently. Start Apache and navigate to http://localhost/ to make sure you have a running installation.
  3. Download the zip package of the latest stable version of PHP 5 and extract it to c:\php.
  4. Download the collection of PECL (PHP Extension Community Library) modules from the same page. Extract this zip file to c:\php\ext.
  5. Copy c:\php\php.ini-dist to c:\php\php.ini and open it with an editor.
    1. Find the line extension_dir setting and change it to:
      extension_dir = "c:/php/ext/"
    2. Find theDynamic Extensionssection and add the following lines:
      extension=php_ibm_db2.dll
      extension=php_soap.dll
  6. Open Apache's configuration file httpd.conf with an editor.
    1. Find the Dynamic Shared Object (DSO) Supportsection and at the end of the list of LoadModule directives, add the following lines:
      LoadModule php5_module "c:/php/php5apache2.dll"
      AddType application/x-httpd-php .php
      AddType application/x-httpd-php-source .phps
      PHPIniDir "c:/php"
    2. Find the DirectoryIndexdirective and add index.php to the list:
      DirectoryIndex index.php index.html index.html.var
  7. You may wish to delete the included files in your Apache installation's htdocsdirectory (the value of the DocumentRoot directive). Create a new file info.php in the htdocs directory whose content is:
    <?php phpInfo(); ?>
  8. Start Apache and navigate to http://localhost/info.php. If you see the ibm_db2 configuration when you scroll down the page, you have successfully configured Apache, PHP, and the ibm_db2 driver!

Setting up the application code

Before attempting to run the attached application, ensure that you have a working Web environment with PHP support. The application's front-end was developed to render correctly in Mozilla Firefox; therefore, you may experience awkward page layouts when using another browser. Follow these simple steps to set up and run the application:

  1. Begin by downloading the zip file at the end of this article, and unzip it into your htdocs directory (typically C:\Program Files\Apache Group\Apache\htdocs). Enter the silvercastles directory.
  2. On the database server you will be using, create a database called silver. In order for the XML functionality to work with DB2 Viper, you must explicitly specify a Unicode database:
    CREATE DATABASE silver USING CODESET utf-8 TERRITORY us
  3. If you want to create a cataloged database connection, you must run the following commands on your local machine from within the DB2 Command Window:
    CATALOG TCPIP NODE myNode REMOTE serverAddr SERVER serverPort
    CATALOG DB silver AT NODE myNode
    Be sure to replace serverAddr and serverPort with the hostname or IP address of your database server, and its TCP/IP port (the SVCENAME database manager configuration variable), respectively. If the SVCENAME variable is not set on the server, you will need to set it to a free port, followed by a restart of DB2:
    $ db2 UPDATE DBM CFG USING SVCENAME 12345
    $ db2stop ; db2start
  4. Edit config.php and modify the appropriate values. Make sure to escape characters if needed since you are modifying actual PHP code.
    1. $basedir: path to the silvercastles directory (use forward-slashes to avoid needing to escape the path, for example, C:/Program Files/Apache Group/Apache/htdocs/silvercastles)
    2. $dbname: database name
    3. $dbuser: name of user allowed to connect to database
    4. $dbpass: password of the user

If you are using an uncataloged database connection, you will also need to modify the following values:

    1. $dbhost: hostname or IP address of your database server
    2. $dbport: server's port number
  1. Save the configuration and point your browser to http://localhost/setup.php to create the XML and relational tables and insert the data. Click the Continue link to proceed to the online shop.

Download

DescriptionNameSize
Source code and data for the scenariosilvercastles.zip  ( HTTP | FTP )7000 KB

Resources

Learn

Get products and technologies

  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edtion and provides a solid base to build and deploy applications.
  • Download DB2 9 and try it out today.
  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, XML, SOA and web services
ArticleID=97295
ArticleTitle=Use DB2 native XML with PHP
publish-date=10272005