function &selectLimit($sql, $nrows = -1, $offset = -1, $inputarr = false, $secs2cache = 0)
{
// seems that oracle only supports 1 hint comment in 8i
if (strpos($sql, '/*+') !== false) {
$sql = str_replace('/*+ ', '/*+FIRST_ROWS ', $sql);
} else {
$sql = preg_replace('/^[ \\t\\n]*select/i', 'select /*+FIRST_ROWS*/', $sql);
}
/*
The following is only available from 8.1.5 because order by in inline views not
available before then...
http://www.jlcomp.demon.co.uk/faq/top_sql.html
if ($nrows > 0) {
if ($offset > 0) $nrows += $offset;
$sql = "select * from ($sql) where rownum <= $nrows";
$nrows = -1;
}
*/
return ADOConnection::selectLimit($sql, $nrows, $offset, $inputarr, $secs2cache);
}
function &selectLimit($sql, $nrows = -1, $offset = -1, $inputarr = false, $secs2cache = 0)
{
if ($secs2cache > 0) {
// we do not cache rowcount, so we have to load entire recordset
$rs =& ADOConnection::selectLimit($sql, $nrows, $offset, $inputarr, $secs2cache);
return $rs;
}
$nrows = (int) $nrows;
$offset = (int) $offset;
$cnt = $nrows >= 0 ? $nrows : 999999999;
if ($offset > 0 && $cnt) {
$cnt += $offset;
}
$this->Execute("set rowcount {$cnt}");
$rs =& ADOConnection::selectLimit($sql, $nrows, $offset, $inputarr, 0);
$this->Execute("set rowcount 0");
return $rs;
}
function &selectLimit($sql, $nrows = -1, $offset = -1, $inputArr = false)
{
$nrows = (int) $nrows;
if ($offset <= 0) {
// could also use " OPTIMIZE FOR $nrows ROWS "
if ($nrows >= 0) {
$sql .= " FETCH FIRST {$nrows} ROWS ONLY ";
}
$rs =& $this->Execute($sql, $inputArr);
} else {
if ($offset > 0 && $nrows < 0) {
} else {
$nrows += $offset;
$sql .= " FETCH FIRST {$nrows} ROWS ONLY ";
}
$rs =& ADOConnection::selectLimit($sql, -1, $offset, $inputArr);
}
return $rs;
}
function &selectLimit($sql, $nrows = -1, $offset = -1, $inputarr = false, $secs2cache = 0)
{
// seems that oracle only supports 1 hint comment in 8i
if ($this->firstrows) {
if (strpos($sql, '/*+') !== false) {
$sql = str_replace('/*+ ', '/*+FIRST_ROWS ', $sql);
} else {
$sql = preg_replace('/^[ \\t\\n]*select/i', 'select /*+FIRST_ROWS*/', $sql);
}
}
if ($offset < $this->selectOffsetAlg1 && 0 < $nrows && $nrows < 1000) {
if ($nrows > 0) {
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";
}
$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;
} else {
// Algorithm by Tomas V V Cox, from PEAR DB oci8.php
// Let Oracle return the name of the columns
$q_fields = "select * FROM (" . $sql . ") WHERE NULL = NULL";
$false = false;
if (!($stmt_arr = $this->Prepare($q_fields))) {
return $false;
}
$stmt = $stmt_arr[1];
if (is_array($inputarr)) {
foreach ($inputarr as $k => $v) {
if (is_array($v)) {
if (sizeof($v) == 2) {
// suggested by g.giunta@libero.
OCIBindByName($stmt, ":{$k}", $inputarr[$k][0], $v[1]);
} else {
OCIBindByName($stmt, ":{$k}", $inputarr[$k][0], $v[1], $v[2]);
}
} else {
$len = -1;
if ($v === ' ') {
$len = 1;
}
if (isset($bindarr)) {
// is prepared sql, so no need to ocibindbyname again
$bindarr[$k] = $v;
} else {
// dynamic sql, so rebind every time
OCIBindByName($stmt, ":{$k}", $inputarr[$k], $len);
}
}
}
}
if (!OCIExecute($stmt, OCI_DEFAULT)) {
OCIFreeStatement($stmt);
return $false;
}
$ncols = OCINumCols($stmt);
for ($i = 1; $i <= $ncols; $i++) {
$cols[] = '"' . OCIColumnName($stmt, $i) . '"';
}
$result = false;
OCIFreeStatement($stmt);
$fields = implode(',', $cols);
$nrows += $offset;
$offset += 1;
// in Oracle rownum starts at 1
if ($this->databaseType == 'oci8po') {
$sql = "select {$fields} FROM" . "(select rownum as adodb_rownum, {$fields} FROM" . " ({$sql}) WHERE rownum <= ?" . ") WHERE adodb_rownum >= ?";
} else {
$sql = "select {$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);
}
return $rs;
}
}
function selectLimit($sql, $nrows = -1, $offset = -1, $inputarr = false, $secs2cache = 0)
{
$ret = ADOConnection::selectLimit($sql, $nrows, $offset, $inputarr, $secs2cache);
return $ret;
}
function &selectLimit($sql, $nrows = -1, $offset = -1, $inputarr = false, $secs2cache = 0)
{
$this->hasTop = preg_match('/ORDER[ \\t\\r\\n]+BY/is', $sql) ? 'top' : false;
$ret = ADOConnection::selectLimit($sql, $nrows, $offset, $inputarr, $secs2cache);
return $ret;
}
function &selectLimit($sql, $nrows = -1, $offset = -1, $inputarr = false, $secs2cache = 0)
{
if ($nrows > 0 && $offset <= 0) {
$sql = preg_replace('/(^\\s*select\\s+(distinctrow|distinct)?)/i', '\\1 ' . $this->hasTop . " {$nrows} ", $sql);
$rs =& $this->Execute($sql, $inputarr);
} else {
$rs =& ADOConnection::selectLimit($sql, $nrows, $offset, $inputarr, $secs2cache);
}
return $rs;
}
function &selectLimit($sql, $nrows = -1, $offset = -1, $inputarr = false, $secs2cache = 0)
{
// TOP requires ORDER BY for Visual FoxPro
if ($this->odbc_driver == ODB_DRIVER_FOXPRO) {
if (!preg_match('/ORDER[ \\t\\r\\n]+BY/is', $sql)) {
$sql .= ' ORDER BY 1';
}
}
$ret =& ADOConnection::selectLimit($sql, $nrows, $offset, $inputarr, $secs2cache);
return $ret;
}
