Christopher Jones on OPAL

After PHP 5.2.4 shipped and a new release cycle began, I merged a couple of enhancements to PDO_OCI attribute handling.  Specifically I added $dbh->getAttribute() support for ATTR_SERVER_VERSION, ATTR_SERVER_INFO, ATTR_CLIENT_VERSION and. ATTR_AUTOCOMMIT.  At the moment these are only available in the PHP snapshots on http://snaps.php.net/. Let me know if there are any issues so they can be resolved before the next PHP release.

I wrote some notes on the existing and newly available attributes. I have not yet covered ATTR_PERSISTENT, ATTR_TIMEOUT, ATTR_ORACLE_NULLS, ATTR_CASE and ATTR_ERRMODE.  Here is what I have so far.  I can see where I need to add detail, but I think you'll understand:

Pre-fetching

PDO_OCI uses a prefetch limit to restrict memory allocated for data pre-fetched from the database.  Pre-fetching allows better optimization of network and database resources.

The prefetch size can be only be set as an option to the prepare method:

$s = $dbh->prepare("select city from locations", array(PDO::ATTR_PREFETCH => 15));

The default prefetch value is 100.  This limit is used to set both an upper bound on the number of rows, and on the maximum number of kilobytes of data ever prefetched at a time.

You can turn off prefetching by setting PDO::ATTR_PREFETCH to 0.

Setting it on a connection handle has no effect.  The value in use for a statement cannot be shown with getAttribute().

Transaction Management

For maximum cross-database compatibility, PDO will autocommit by default. This is bad for performance and transaction consistency.

Transaction management needs care because there are various ways to commit or rollback. These include PDO methods, explicit SQL calls, or by executing a statement such as CREATE or DROP.

Transaction Management in PDO can be done in two ways.

You can use PDO transactions:

$dbh->beginTransaction();$dbh->exec("insert into sometable (col1) values ('data')");$dbh->commit();

Both $dbh->commit() and $dbh->rollback() will only succeed if $dbh->beginTransaction() was previously called.  Both of them conclude the transaction so you need to do $dbh->beginTransaction() again for each successive transaction.

You can neatly combine exception handling with transactions like:

$dbh->beginTransaction();
try {
    $dbh->exec("insert into sometable (col1) values ('data')");
    $dbh->exec("update sometable set col1 = 'new data' where col1 = 'data'");
    $dbh->commit();} catch (Exception $e) {
    $dbh->rollBack();
}

An alternative is to use raw transaction functionality in Oracle. When a connection is created, specify not to autocommit.  Later, SQL COMMIT or ROLLBACK statements can be executed:

$dbh = new PDO('oci:dbname=localhost/XE', 'hr', 'hrpwd', array(PDO::ATTR_AUTOCOMMIT => false));
$dbh->exec("insert into sometable (col1) values ('data')");
$dbh->exec("commit"); // this is a SQL statement

The ATTR_AUTOCOMMIT commit mode of FALSE used like this stays in effect for the duration of the connection, or until setAttribute() changes it:

$dbh->setAttribute(PDO::ATTR_AUTOCOMMIT, true);

You can see the current value:

echo $dbh->getAttribute(PDO::ATTR_AUTOCOMMIT);

If you change the value of PDO::ATTR_AUTOCOMMIT while in a PDO transaction (after calling $dbh->beginTransaction()), a commit is executed and the PDO transaction ended. This means that $dbh->commit() and $dbh->rollback will fail until $dbh->beginTransaction() is called again.

When a connection is closed, PDO_OCI will rollback any outstanding transactions.

Oracle Client and Server

You can get information about the system you are using:

var_dump($dbh->getAttribute(PDO::ATTR_SERVER_INFO));

gives:

string(67) "Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production"

And

var_dump($dbh->getAttribute(PDO::ATTR_SERVER_VERSION));

gives:

string(10) "10.2.0.1.0"

You can even find out what version of the Oracle client libraries are in use.  This may be useful for problem reporting.  If PHP was compiled with Oracle 10.2 client libraries or more recent, the full client version being used is shown.

var_dump($dbh->getAttribute(PDO::ATTR_CLIENT_VERSION));

gives

string(10) "10.2.0.1.0"

With earlier versions, only the major release of the libraries used to compile PHP is given:

string(3) "10.1"

Conclusion