/**
* Sends the sql to the database and returns the results.
*
* @internal Switches between ADOConnection::Execute() and
* ADOConnection::SelectLimit() depending on the $query parameter's
* $solutionModifier "limit" and "offset" settings.
* Uses $query variable.
*
* @param array $arSql Array that gets a SQL query string once imploded
*
* @return mixed Anything ADOConnection::Execute() may return
* @throws Exception If Database query does not work
*/
function queryDb($arSql, $nOffset, $nLimit)
{
$strSql = SparqlEngineDb_SqlMerger::getSelect($this->query, $arSql);
if ($strSql == '()') {
return new ADORecordSet(false);
}
// I want associative arrays.
$oldmode = $this->dbConn->SetFetchMode(ADODB_FETCH_ASSOC);
if (isset($GLOBALS['debugSparql']) && $GLOBALS['debugSparql']) {
echo 'SQL query: ' . $strSql . "\n";
}
if ($nLimit === null && $nOffset == 0) {
$ret = $this->dbConn->execute($strSql);
} else {
if ($nLimit === null) {
$ret = $this->dbConn->SelectLimit($strSql, -1, $nOffset);
} else {
$ret = $this->dbConn->SelectLimit($strSql, $nLimit, $nOffset);
}
}
//... but others maybe not
$this->dbConn->SetFetchMode($oldmode);
if (!$ret) {
//Error occured
throw new Exception('ADOdb error: ' . $this->dbConn->ErrorMsg() . "\n" . $strSql);
}
return $ret;
}
function &SelectLimit($sql, $nrows = -1, $offset = -1, $inputarr = false, $arg3 = false, $secs2cache = 0)
{
if (!preg_match('/ORDER[ \\t\\r\\n]+BY/is', $sql)) {
$sql .= ' ORDER BY 1';
}
return ADOConnection::SelectLimit($sql, $nrows, $offset, $inputarr, $arg3, $secs2cache);
}
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);
}
/**
* Fonction static permettant d'obtenir la séquence suivante
*
* @name DbSequence::next()
*
* @access public
* @static
* @param ADOConnection $conn Connexion valide à une base de données
* @param string $table Table sur laquelle on doit obtenir la sequence suivante
* @param string $column Nom de la colonne Primary Key de type numerique
*
* @return int Numero de séquence suivante
*/
public static function next(ADOConnection $conn, $table, $column)
{
try {
$sql = "SELECT {$column} FROM {$table} ORDER BY 1 DESC";
$rs = $conn->SelectLimit($sql, 1);
// Si il n'y a aucun enregistrement : insertion du premier
if ($rs->RecordCount() == 0) {
$id = 1;
} else {
while (!$rs->EOF) {
$id = $rs->fields[0];
break;
}
$id++;
}
} catch (exception $e) {
return null;
}
return $id;
}
function &SelectLimit($sql, $nrows = -1, $offset = -1, $inputarr = false, $arg3 = false, $secs2cache = 0)
{
$sql = preg_replace('/^[ \\t]*select/i', 'SELECT /*+FIRST_ROWS*/', $sql);
if ($offset < 100) {
if ($nrows > 0) {
if ($offset > 0) {
$nrows += $offset;
}
//$inputarr['adodb_rownum'] = $nrows;
$sql = "select * from ({$sql}) where rownum <= {$nrows}";
$nrows = -1;
}
// note that $nrows = 0 still has to work ==> no rows returned
return ADOConnection::SelectLimit($sql, $nrows, $offset, $inputarr, $arg3, $secs2cache);
} 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";
if (!($result = OCIParse($this->_connectionID, $q_fields))) {
return false;
}
if (!($success = OCIExecute($result, OCI_DEFAULT))) {
return false;
}
$ncols = OCINumCols($result);
for ($i = 1; $i <= $ncols; $i++) {
$cols[] = OCIColumnName($result, $i);
}
$result = false;
$fields = implode(',', $cols);
$nrows += $offset;
$offset += 1;
// in Oracle rownum starts at 1
$sql = "SELECT {$fields} FROM" . "(SELECT rownum as adodb_rownum, {$fields} FROM" . " ({$sql}) WHERE rownum <= {$nrows}" . ") WHERE adodb_rownum >= {$offset}";
if ($secs2cache > 0) {
return $this->CacheExecute($secs2cache, $sql, $inputarr, $arg3);
} else {
return $this->Execute($sql, $inputarr, $arg3);
}
}
}
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)
{
$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)
{
$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)
{
// 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;
}
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, $arg3 = false)
{
if ($offset <= 0) {
// could also use " OPTIMIZE FOR $nrows ROWS "
if ($nrows >= 0) {
$sql .= " FETCH FIRST {$nrows} ROWS ONLY ";
}
return $this->Execute($sql, false, $arg3);
} else {
if ($offset > 0 && $nrows < 0) {
} else {
$nrows += $offset;
$sql .= " FETCH FIRST {$nrows} ROWS ONLY ";
}
return ADOConnection::SelectLimit($sql, -1, $offset, $arg3);
}
}
public function &SelectLimit($sql,$nrows=-1,$offset=-1,$inputArr=false)
{
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;
}
/**
* This algorithm makes use of
*
* a. FIRST_ROWS hint
* The FIRST_ROWS hint explicitly chooses the approach to optimize response
* time, that is, minimum resource usage to return the first row. Results
* will be returned as soon as they are identified.
*
* b. Uses rownum tricks to obtain only the required rows from a given offset.
* As this uses complicated sql statements, we only use this if $offset >= 100.
* This idea by Tomas V V Cox.
*
* 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.
*/
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 ($nrows > 500 && $nrows < 1000) {
$hint = "FIRST_ROWS({$nrows})";
} else {
$hint = 'FIRST_ROWS';
}
if (strpos($sql, '/*+') !== false) {
$sql = str_replace('/*+ ', "/*+{$hint} ", $sql);
} else {
$sql = preg_replace('/^[ \\t\\n]*select/i', "SELECT /*+{$hint}*/", $sql);
}
$hint = "/*+ {$hint} */";
} else {
$hint = '';
}
if ($offset == -1 || $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";
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)) {
// suggested by g.giunta@libero.
if (sizeof($v) == 2) {
oci_bind_by_name($stmt, ":{$k}", $inputarr[$k][0], $v[1]);
} else {
oci_bind_by_name($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 oci_bind_by_name again
$bindarr[$k] = $v;
} else {
// dynamic sql, so rebind every time
oci_bind_by_name($stmt, ":{$k}", $inputarr[$k], $len);
}
}
}
}
if (!oci_execute($stmt, OCI_DEFAULT)) {
oci_free_statement($stmt);
return false;
}
$ncols = oci_num_fields($stmt);
for ($i = 1; $i <= $ncols; $i++) {
$cols[] = '"' . oci_field_name($stmt, $i) . '"';
}
$result = false;
oci_free_statement($stmt);
$fields = implode(',', $cols);
if ($nrows <= 0) {
$nrows = 999999999999;
} else {
$nrows += $offset;
}
$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;
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, $arg3 = false, $secs2cache = 0)
{
if ($secs2cache > 0) {
// we do not cache rowcount, so we have to load entire recordset
return ADOConnection::SelectLimit($sql, $nrows, $offset, $inputarr, $arg3, $secs2cache);
}
$cnt = $nrows > 0 ? $nrows : 0;
if ($offset > 0 && $cnt) {
$cnt += $offset;
}
$this->Execute("set rowcount {$cnt}");
$rs =& ADOConnection::SelectLimit($sql, $nrows, $offset, $inputarr, $arg3, $secs2cache);
$this->Execute("set rowcount 0");
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)) {
// If there are any numeric keys, all key must be in an integer sequence
// starting at 0 without any gaps for OCIBindByName() to work.
$arr4fields = $inputarr;
foreach ($inputarr as $k => $v) {
if (is_int($k)) {
// Reindex array
$arr4fields = array_values($inputarr);
break;
}
}
foreach ($arr4fields as $k => $v) {
if (is_array($v)) {
if (sizeof($v) == 2) {
// suggested by g.giunta@libero.
OCIBindByName($stmt, ":{$k}", $arr4fields[$k][0], $v[1]);
} else {
OCIBindByName($stmt, ":{$k}", $arr4fields[$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}", $arr4fields[$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_placeholder = $offset_placeholder = '?';
if ($this->databaseType != 'oci8po') {
$nrows_placeholder = ':adodb_nrows';
$offset_placeholder = ':adodb_offset';
}
if ($offset >= 0 && $nrows >= 0) {
$sql = "SELECT {$fields} FROM" . "(SELECT rownum as adodb_rownum, {$fields} FROM" . " ({$sql}) WHERE rownum <= {$nrows_placeholder}" . ") WHERE adodb_rownum >= {$offset_placeholder}";
$offset += 1;
// in Oracle rownum starts at 1
$nrows += $offset;
$inputarr['adodb_nrows'] = $nrows;
$inputarr['adodb_offset'] = $offset;
} else {
if ($offset >= 0) {
$sql = "SELECT {$fields} FROM" . "(SELECT rownum as adodb_rownum, {$fields} FROM" . " ({$sql})" . ") WHERE adodb_rownum >= {$offset_placeholder}";
$offset += 1;
// in Oracle rownum starts at 1
$inputarr['adodb_offset'] = $offset;
} else {
if ($nrows >= 0) {
$sql = "SELECT {$fields} FROM" . " ({$sql}) WHERE rownum <= {$nrows_placeholder}";
$inputarr['adodb_nrows'] = $nrows;
}
}
}
// else $sql is unchanged
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)
{
if ($nrows > 0 && $offset <= 0) {
$sql = preg_replace(
'/(^\s*select\s+(distinctrow|distinct)?)/i','\\1 '.$this->hasTop." $nrows ",$sql);
if ($secs2cache)
$rs = $this->CacheExecute($secs2cache, $sql, $inputarr);
else
$rs = $this->Execute($sql,$inputarr);
} else
$rs = ADOConnection::SelectLimit($sql,$nrows,$offset,$inputarr,$secs2cache);
return $rs;
}
