oci8po: fix SelectLimit() with prepared statements

The ADOdb_oci8::SelectLimit() method performs Oracle-specific query
optimization, manipulating the SQL to apply hints in a way that is not
compatible with the oci8po driver, due to conversion of query
parameters ('?' vs oci8 native ':xx').

To avoid the problem, we define the SelectLimit() method directly in
ADODB_oci8po, and rely on the slower ADOConnection::SelectLimit()
method from the base class.

Also, to avoid issues with prepared statements causing PHP to throw a
Warning: "oci_execute(): supplied resource is not a valid oci8
statement resource", we retrieve the prepared statement's SQL and pass
it on to ADOConnection::SelectLimit().

Fixes #282

2 files changed, 42 insertions, 26 deletions

diff --git a/drivers/adodb-oci8.inc.php b/drivers/adodb-oci8.inc.php
index 68223925..a20ecec8 100644
--- a/drivers/adodb-oci8.inc.php
+++ b/drivers/adodb-oci8.inc.php
@@ -703,9 +703,19 @@ END;
 	 * This implementation does not appear to work with oracle 8.0.5 or earlier.
 	 * Comment out this function then, and the slower SelectLimit() in the base
 	 * class will be used.
+	 *
+	 * Note: FIRST_ROWS hinting is only used if $sql is a string; when
+	 * processing a prepared statement's handle, no hinting is performed.
 	 */
 	function SelectLimit($sql,$nrows=-1,$offset=-1, $inputarr=false,$secs2cache=0)
 	{
+		// Since the methods used to limit the number of returned rows rely
+		// on modifying the provided SQL query, we can't work with prepared
+		// statements so we just extract the SQL string.
+		if(is_array($sql)) {
+			$sql = $sql[0];
+		}
+
 		// seems that oracle only supports 1 hint comment in 8i
 		if ($this->firstrows) {
 			if ($nrows > 500 && $nrows < 1000) {
@@ -731,20 +741,13 @@ END;
 				if ($offset > 0) {
 					$nrows += $offset;
 				}
-				//$inputarr['adodb_rownum'] = $nrows;
-				if ($this->databaseType == 'oci8po') {
-					$sql = "select * from (".$sql.") where rownum <= ?";
-				} else {
-					$sql = "select * from (".$sql.") where rownum <= :adodb_offset";
-				}
+				$sql = "select * from (".$sql.") where rownum <= :adodb_offset";
 				$inputarr['adodb_offset'] = $nrows;
 				$nrows = -1;
 			}
 			// note that $nrows = 0 still has to work ==> no rows returned
 
-			$rs = ADOConnection::SelectLimit($sql,$nrows,$offset,$inputarr,$secs2cache);
-			return $rs;
-
+			return ADOConnection::SelectLimit($sql, $nrows, $offset, $inputarr, $secs2cache);
 		} else {
 			// Algorithm by Tomas V V Cox, from PEAR DB oci8.php
 
@@ -807,24 +810,19 @@ END;
 			}
 			$offset += 1; // in Oracle rownum starts at 1
 
-			if ($this->databaseType == 'oci8po') {
-					$sql = "SELECT $hint $fields FROM".
-						"(SELECT rownum as adodb_rownum, $fields FROM".
-						" ($sql) WHERE rownum <= ?".
-						") WHERE adodb_rownum >= ?";
-				} else {
-					$sql = "SELECT $hint $fields FROM".
-						"(SELECT rownum as adodb_rownum, $fields FROM".
-						" ($sql) WHERE rownum <= :adodb_nrows".
-						") WHERE adodb_rownum >= :adodb_offset";
-				}
-				$inputarr['adodb_nrows'] = $nrows;
-				$inputarr['adodb_offset'] = $offset;
+			$sql = "SELECT $hint $fields FROM".
+				"(SELECT rownum as adodb_rownum, $fields FROM".
+				" ($sql) WHERE rownum <= :adodb_nrows".
+				") WHERE adodb_rownum >= :adodb_offset";
+			$inputarr['adodb_nrows'] = $nrows;
+			$inputarr['adodb_offset'] = $offset;
 
 			if ($secs2cache > 0) {
 				$rs = $this->CacheExecute($secs2cache, $sql,$inputarr);
 			}
-			else $rs = $this->Execute($sql,$inputarr);
+			else {
+				$rs = $this->Execute($sql, $inputarr);
+			}
 			return $rs;
 		}
 	}
diff --git a/drivers/adodb-oci8po.inc.php b/drivers/adodb-oci8po.inc.php
index 00134679..06b96fc9 100644
--- a/drivers/adodb-oci8po.inc.php
+++ b/drivers/adodb-oci8po.inc.php
@@ -56,6 +56,21 @@ class ADODB_oci8po extends ADODB_oci8 {
 		return ADOConnection::Execute($sql,$inputarr);
 	}
 
+	/**
+	 * The optimizations performed by ADODB_oci8::SelectLimit() are not
+	 * compatible with the oci8po driver, so we rely on the slower method
+	 * from the base class.
+	 * We can't properly handle prepared statements either due to preprocessing
+	 * of query parameters, so we treat them as regular SQL statements.
+	 */
+	function SelectLimit($sql, $nrows=-1, $offset=-1, $inputarr=false, $secs2cache=0)
+	{
+		if(is_array($sql)) {
+//			$sql = $sql[0];
+		}
+		return ADOConnection::SelectLimit($sql, $nrows, $offset, $inputarr, $secs2cache);
+	}
+
 	// emulate handling of parameters ? ?, replacing with :bind0 :bind1
 	function _query($sql,$inputarr=false)
 	{
@@ -74,11 +89,14 @@ class ADODB_oci8po extends ADODB_oci8 {
 					$sql = str_replace($qmMatch, $qmReplace, $sql);
 				}
 
+				// Replace parameters if any were found
 				$sqlarr = explode('?',$sql);
-				$sql = $sqlarr[0];
+				if(count($sqlarr) > 1) {
+					$sql = $sqlarr[0];
 
-				foreach($inputarr as $k => $v) {
-					$sql .=  ":$k" . $sqlarr[++$i];
+					foreach ($inputarr as $k => $v) {
+						$sql .= ":$k" . $sqlarr[++$i];
+					}
 				}
 
 				$sql = str_replace('-QUESTIONMARK-', '?', $sql);