X

The leading edge of scripting languages and Oracle Database brought to you by the Data Access Development team

  • php
    September 18, 2007

New PDO_OCI Attributes

Christopher Jones
Senior Principal Product Manager

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

Remember that PDO_OCI is still marked "Experimental" and there are known bugs with it, as there are also with the generic PDO code.  PDO's time is coming but it is not quite here yet.

Join the discussion

Comments ( 4 )
  • sf php Friday, September 21, 2007
    All PDO_* has that same API ;)
  • Christopher Jones Friday, September 21, 2007
    There is still a lot of variance in the drivers on these. Some may accept attributes but do nothing, or give errors. For example, PDO_OCI is the only driver where prefetching has any effect, as far as I can see from the code.
  • Gabriel PREDA Wednesday, July 29, 2009
    Where is PDO_OCI now ? PDO is quite mature nowdays ? Please create a new post about PDO and Oracle !
  • Christopher Jones Thursday, July 30, 2009
    The real question is "where are all PDO drivers now"? The answer is, being used but no one from the community has stepped up to maintain them. In many ways they are immature and inconsistent. I had hopes for PDO but they have not yet eventuated. Luckily I still have hopes.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.