PHP data access

The Query Zero for PHP feature provides a data access layer for PHP. The initial goal is to wrap the existing Query Zero Java APIs with an XAPI-J extension. The APIs map very closely to the Java counterparts, but with a PHP flavor.

This extension provides a handle-based API. Handles are used to represent instances of Java objects. The actual type and value of the handles is not useful for a PHP Developer, although most APIs require one or more handle arguments. The following sections of this article provide information about PHP data access:

See the PHP Function Reference for detailed documentation of the Query Zero for PHP API functions. Note that all of the functions begin with the string 'data'.

Using PHP data access

To use PHP data access in your application, add the zero.data.php and zero.php dependencies to the ivy.xml file. Then add the following line to your php.ini file:

extension = zero.data.php.QueryExtension

The example application demonstrates dataExec, which is the most useful QueryExtension function. Unlike the query and update function, the dataExec function can be used to execute all types of SQL statements. The return value of this API depends on the type of statement being executed. It will either be a nested array for SELECT statements, an integer for other statements, and either a boolean or null for errors.

For convenience, the example code below also demonstrates the dataExec function. The setup code at the top is common to all four snippets. The database used in these snippets is the same one that the example application uses.

Configuration

The database connection properties are setup the same way as the Java and Groovy data access in zero.data.

Setup

// Retrieve a handle to the Manager that is configured in the zero.config file.
$dataManager = dataManager("todo");

SELECT statement

$sql = "SELECT * FROM items WHERE id=?";
$params = array(1);

$items = dataExec($dataManager,$sql,$params);

// Result manipulation is the same for both cases.
if (is_array($items)) {
        foreach ($items as $row) {
                foreach ($row as $columnName => $value) {
                        echo "$columnName => $value\n";
                }
        }
}
else if (is_null($items)) {
        // Error
}

INSERT statement

$sql = "INSERT INTO items (name,description) VALUES (?,?)";
$params = array("Paint house","Exterior and trim");

$rowsAffected = dataExec($dataManager,$sql,$params);

// Result manipulation is the same for both cases.
if ($rowsAffected == 1) {
        echo "Success!";
}
else if (is_null($rowsAffected) || $rowsAffected == 0) {
        echo "Error";
}

UPDATE statement

$sql = "UPDATE items SET completed=1 WHERE id=?";
$params = array(1);

$rowsAffected = dataExec($dataManager,$sql,$params);

// Result manipulation is the same for both cases.
if ($rowsAffected == 1) {
        echo "Success!";
}
else if (is_null($rowsAffected) || $rowsAffected == 0) {
        echo "Error";
}

DELETE statement

$sql = "DELETE FROM items WHERE id=?";
$params = array(1);

$rowsAffected = dataExec($dataManager,$sql,$params);

// Result manipulation is the same for both cases.
if ($rowsAffected == 1) {
        echo "Success!";
}
else if (is_null($rowsAffected) || $rowsAffected == 0) {
        echo "Error";
}

There is also an optimized form of the exec API, named dataExecOpt. It dispatches to queryFirst for SELECT statements and update for other statements. A simple example of the update case is below.

$sql = "UPDATE $dbname SET value=:value WHERE grouping=:group";
$params = array(
array( "group" => 0, "value" => 100),
array( "group" => 1, "value" => 100),
array( "group" => 2, "value" => 100),
array( "group" => 3, "value" => 100),
);
$result = dataExecOpt($manager,$sql,$params);

foreach ($result as $key => $value) {
        echo "$key => $value\n";
}

How this differs from Query Zero for Java

The Query Zero for PHP library is modeled after the Java counterpart. However, there are several important differences. These differences allow the library to be familiar to PHP Developers. There are also some similarities with several existing PHP database access libraries like the MySQL Improved Extension (mysqli) or PHP Data Objects (PDO).

The basic calling pattern is the same as the Java version, with the exception that the calling convention is handle-based instead of object-oriented. This means that the first argument to most API functions is a handle.

SQL parameters

SQL parameters are passed in an array instead of a variable argument list. Passing a null indicates that there are no parameters. There are two styles of parameters you can choose from, positional and named, as shown in the following examples:

Positional

$sql = "INSERT INTO items (id,value) VALUES (?,?)";
$params = array(101, "Item 1");
$result = dataExec($manager,$sql,$params);

Named

$sql = "INSERT INTO items(id,value) VALUES (:id,:value)";
$params = array(
"id" => 101, 
"value" => "Item 1"
);
$result = dataExec($manager,$sql,$params);

Method overloading

Method overloading is not allowed in PHP, so method names are based on the argument types. The extension functions shown in the following example provide the same functionality as the DataManager.queryFirst() method from Java:

function queryFirst($handle, $sql, $params);
function queryFirstByClass($handle, $sql, $className, $params);
function queryFirstByFactory($handle, $sql, $rowFactory, $params);

Callbacks

In order to support the ResultHandler and RowFactory patterns naturally, the library supports the PHP callback convention. Only simple function callbacks are enabled. The following example demonstrates a simple function callback:

function factoryCallback($arg) {
        return "Callback Result\n";
}

$sql = "SELECT * FROM item WHERE name LIKE ?";
$callback = "factoryCallback";
$result = dataQueryFirstByFactory($manager,$sql,$callback,array("Shoes"));

Java class leakage

Several JDBC and JDK classes leak through the interface, so the library provides wrapper functions for operating on objects of these types. The following Java types are supported:

java.util.Iterator
java.sql.ResultSet

JDBC datatype handling

JDBC datatype handling is an important piece of the puzzle, and might change significantly as the Query Zero for PHP library evolves. Datatypes are currently handled in one of two ways. They are either converted between Java and PHP representations, or they are wrapped inside a PHP resource. The two methods are described in detail in the following sections.

For each method, there are two ways in which the datatype needs to be handled. Inbound handling occurs when a value is passed to the library. The primary case is passing in SQL parameters. Outbound handling occurs when a value is returned from the library. The primary case is returning query results.

Conversion

Inbound handling

This conversion is entirely handled by the P8 engine. Discussion of this default conversion is outside the scope of this document.

Outbound handling

Resource wrapping

The following types are resource wrapped:

java.sql.Blob
java.sql.Clob

Each of these types has a set of functions for retrieving and manipulating the value. See the PHP Function Reference for more information.

Inbound handling

To indicate that a PHP value should be treated as a particular JDBC type, the following functions are provided:

Outbound handling

The types listed in the previous table are wrapped in non-persistent resources.

ResultHandler

The ResultHandler pattern is very important to Query Zero. However, it poses several challenges to the PHP Developer. The current level of support is very basic, and definitely subject to change in future versions.

The ResultHandler is passed as the third argument to the dataQuery function. The following types of argument are enabled:

1. A PHP callback. See the discussion of callbacks in the preceding section for details.
2. A ResultHandler resource. This type of resource can only be created by the dataNewResultHandler function.

CAUTION: It is important to understand the consequences of using a ResultHandler the return type of which does not convert easily to a PHP type. There is no mechanism for providing type converters dynamically. Unintended behaviors, such as application crashes, might occur.

JSONResultHandler

The zero.data.php dependency provides a sample ResultHandler that is written in PHP. It is implemented in the file JSONResultHandler.php and automatically included in the PHP include path whenever you have zero.data.php as a dependency. This handler takes the result of a query operation, which is a JDBC ResultSet, and renders it as JSON. Specifically, this is an array of maps with column names as keys.

This handler requires the following line in your php.ini file:

extension = zero.php.JSONExtension

You will also need the zero.php dependency in your ivy.xml file.

The following simple example uses the JSONResultHandler.

<?php
include("JSONResultHandler.php");

$manager = dataManager("mydb");

$sql = "SELECT * FROM item";
$callback = "JSONResultHandler";
$result = dataQuery($manager,$sql,$callback,null);
echo $result;
?>

The echoed result of this example would look like the following.

[
   {
      "DESCRIPTION": "For your feet.",
      "IMAGE": "Shoes image",
      "ITEM_ID": 1,
      "NAME": "Shoes",
      "PRICE": 40.0
   },
   {
      "DESCRIPTION": "For your feet.",
      "IMAGE": "Socks image",
      "ITEM_ID": 2,
      "NAME": "Socks",
      "PRICE": 6.0
   },
   {
      "DESCRIPTION": "For your hands.",
      "IMAGE": "Gloves image",
      "ITEM_ID": 3,
      "NAME": "Gloves",
      "PRICE": 10.0
   }
]

The following example is the source for the JSONResultHandler:

function JSONResultHandler($resultSet) {
        
        if (is_null($resultSet)) return null;
        
        $columnCount = dataResultSetGetColumnCount($resultSet);
        $columnNames = array();
        for ($i=1; $i<=$columnCount; $i++) {
                $name = dataResultSetGetColumnName($resultSet,$i);
                $columnNames[$i] = $name;
        }
        
        $rowArray = array();
        while (dataResultSetNext($resultSet)) {
                $row = array();
                for ($i=1; $i<=$columnCount; $i++) {
                        $obj = dataResultSetGetObject($resultSet,$i);
                        $row[$columnNames[$i]] = $obj;
                }
                $rowArray[] = $row;
        }
        
        return json_encode($rowArray);
}

Note that it is a single PHP function that operates on a ResultSet.

Transactions

Transaction support comes in two flavors. The PHP code can either define a transaction callback function, or it can explicitly begin, commit, and rollback the transaction. The two mechanisms are described below.

Local transactions

Local transactions are supported by allowing the PHP script to define a callback function which contains all of the code to execute inside the transaction. Then the name of this function is passed to the dataTransaction API for execution. A simple transaction example is shown below.

<?php

$manager = dataManager("myDB");
$sql = "SELECT * FROM items";

function doTransaction($manager, $userData) {
        $result = dataExec($manager,$userData);
}

$result = dataTransaction($manager,"doTransaction",$sql);
if ($result === true) {
        echo "Transaction worked!";
}
else ($result === false) {
        echo "Transaction failed!";
}

?>

In the example code, note that the callback function accepts two arguments. Both are optional, but can be very useful for implementing transaction callbacks. The first argument is a handle to the Manager in which the transaction is executing. The second argument can be anything, and allows the script to pass special values or context to the callback. For this example, the second argument contains the SQL query string.

A transaction callback may be explicitly failed either by throwing a PHP exception, or returning false. The following example demonstrates this mechanism.

<?php

$manager = dataManager("myDB");
$sql = "SELECT * FROM items";

function failWithException($manager, $userData) {
        $result = dataExec($manager,$userData);
        throw new Exception("Transaction failed");
}

dataTransaction($manager,"failWithException",$sql);

function failWithReturnValue($manager, $userData) {
        $result = dataExec($manager,$userData);
        return false;
}

dataTransaction($manager,"failWithReturnValue",$sql);

?>

Manual transactions

Manual transactions are supported with the dataBeginTransaction, dataCommitTransaction, and dataRollbackTransaction APIs. The following example demonstrates a manual transaction.

<?php

$manager = dataManager("myDB");
$sql = "SELECT * FROM items";

dataBeginTransaction($manager);
$result = dataExec($manager,$userData);
if (is_null($result)) {
        dataRollbackTransaction($manager);
}
else {
        dataCommitTransaction($manager);
}

?>

Error Handling

After an API fails, information about the failure may be retrieved with the dataLastError API, which returns an associative array. The example below demonstrates a typical usage.

<?php

$manager = dataManager("myDB");
$sql = "SELECT * FROM items";

$result = dataExec($manager,$userData);
if ($result === null || count($result) === 0) {
        echo "Query failed!\n";
        $lastError = dataLastError();
        echo "Error functionName = " . $lastError['functionName'] . "\n";
        echo "Error type = " . $lastError['type'] . "\n";
        echo "Error message = " . $lastError['message'] . "\n";
}

?>