Bo's occasional PHP-Oracle rant....

PHP 5.1 Data Object (PDO) Abstraction Layer vs. Oracle Call Interface (OCI) APIs and ADODB

The method for accessing data from Oracle through PHP has undergone several changes over the years. By abstracting the database layer you can use the same API for any database. The concept is similar to that of any database abstraction layer such as the ADOdb Database Abstraction Library (http://adodb.sourceforge.net). ADOdb provides a unified API for accessing SQL databases. In the new 5.1 version, php has built in a data abstraction layer called PDO so I had to check it out,
The ADODB driver has been proven to be faster in benchmarks performed by phpLense (http://phplense.com/lens/adodb/) but PDO promises a refactored database API. From php.net:
" PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data. PDO does not provide a database abstraction; it doesn't rewrite SQL or emulate missing features. You should use a full-blown abstraction layer if you need that facility."

Here is a sample of my own testing:


$strUser = "scott";
$strPwd = "tiger";
//construct query string
$strSQL = "SELECT ID, NAME ".
"FROM STAFF.NAMES WHERE ID = 10";

$strDB="(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = orac.subdomain.domain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mytest)
)
)";
//================ START CODE ====
//ADODB===========================

$db = NewADOConnection('oci8');
$db->Connect($strDB, $strUser, $strPwd);
$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
try {
$rs=$db->Execute($strSQL);
} catch (exception $e) {
print_r($e);
}

foreach($rs as $k => $row) {
echo $row[0]." ".$row[1]."
";
}
$floEndTime = round(microtime(true)-$floStartTime,4);
echo "
Query took ".$floEndTime." seconds
";




/******************* OCI EXPERIMENTAL ********************/
$floStartTime = microtime(true);
echo "

OCI========================================

";



//login to database
$objDBConn = OCILogon($strUser, $strPwd, $strDB)or die( "Could not connect to database!");
//test DB object
if($objDBConn){
//parse query string
$objQuery = oci_parse($objDBConn, $strSQL);
//execute SQL statement
$objResult = oci_execute($objQuery);
//test SQL statement result
if($objResult){
//loop through query result set
while(oci_fetch($objQuery)){
echo oci_result($objQuery, 1);
echo " ";
echo oci_result($objQuery, 2);
echo "
";
}//end while
}else{
//error in query
$arrError = oci_error($objQuery);
}//end objResult if
$objDBConn = oci_close();
}else{
//error in connection
$arrError = oci_error();
}//end objDBConn if

if(isset($arrError)){
echo "ERROR: ".$arrError['message'];
}
$floEndTime = round(microtime(true)-$floStartTime,4);
echo "
Query took ".$floEndTime." seconds
";





/******************* PDO EXPERIMENTAL ********************/
$strDB = "oci:dbname=pdotest;host=oracle.server.address.com";

$floStartTime = microtime(true);
echo "

PDO========================================

";

// If php_pdo.dll is loaded in php.ini then try it
try{
$objDB = new PDO($strDB, $strUser, $strPwd);
}catch (PDOException $objException){
echo "ERROR: " . $objException->getMessage();
exit;
}

foreach($objDB->query($strSQL) as $arrRow){
echo $arrRow[0];
echo " ";
echo $arrRow[1];
echo "
";
}
$floEndTime = round(microtime(true)-$floStartTime,4);
echo "
Query took ".$floEndTime." seconds";

?>

================ END CODE ======================

The average results for this routine after 20 tries in random order:
ADODB took 0.0834 seconds, OCI 0.0671 seconds and PDO 0.1717 seconds.

It looks like PDO has a way to go.
Too bad though. I would rather see ADODB built into the php core but that would be too much to ask.