public function Query($query_string, $params = null)
{
++self::$query_count;
$this->qid = $this->prepareSQL($query_string, $params);
$result = oci_execute($this->qid);
$this->row = 0;
$this->selected_rows = null;
$err = oci_error($this->qid);
$this->error = $err['message'];
$this->errno = $err['code'];
if (oci_statement_type($this->qid) == 'SELECT') {
$this->records = array();
$this->selected_rows = 0;
$this->current_row = 0;
while ($row = @oci_fetch_assoc($this->qid)) {
$this->records[] = $row;
++$this->selected_rows;
}
}
if (!$result) {
throw new Exception('Invalid query: ' . $query_string . ': ' . $this->error, $this->errno);
}
//if
return $result;
}
public function exec(&$statement)
{
$result =& $this->temp_result;
if (($result = oci_parse($this->link, $statement)) && @oci_execute($result, $this->autocommit ? OCI_COMMIT_ON_SUCCESS : OCI_DEFAULT)) {
if ('SELECT' == oci_statement_type($result)) {
oci_free_statement($result);
$result = null;
return 0;
}
$rows = oci_num_rows($result);
$result = null;
return $rows;
}
return false;
}
protected function doQuery($sql)
{
wfDebug("SQL: [{$sql}]\n");
if (!mb_check_encoding($sql)) {
throw new MWException("SQL encoding is invalid\n{$sql}");
}
// handle some oracle specifics
// remove AS column/table/subquery namings
if (!$this->getFlag(DBO_DDLMODE)) {
$sql = preg_replace('/ as /i', ' ', $sql);
}
// Oracle has issues with UNION clause if the statement includes LOB fields
// So we do a UNION ALL and then filter the results array with array_unique
$union_unique = preg_match('/\\/\\* UNION_UNIQUE \\*\\/ /', $sql) != 0;
// EXPLAIN syntax in Oracle is EXPLAIN PLAN FOR and it return nothing
// you have to select data from plan table after explain
$explain_id = date('dmYHis');
$sql = preg_replace('/^EXPLAIN /', 'EXPLAIN PLAN SET STATEMENT_ID = \'' . $explain_id . '\' FOR', $sql, 1, $explain_count);
wfSuppressWarnings();
if (($this->mLastResult = $stmt = oci_parse($this->mConn, $sql)) === false) {
$e = oci_error($this->mConn);
$this->reportQueryError($e['message'], $e['code'], $sql, __METHOD__);
return false;
}
if (!oci_execute($stmt, $this->execFlags())) {
$e = oci_error($stmt);
if (!$this->ignore_DUP_VAL_ON_INDEX || $e['code'] != '1') {
$this->reportQueryError($e['message'], $e['code'], $sql, __METHOD__);
return false;
}
}
wfRestoreWarnings();
if ($explain_count > 0) {
return $this->doQuery('SELECT id, cardinality "ROWS" FROM plan_table WHERE statement_id = \'' . $explain_id . '\'');
} elseif (oci_statement_type($stmt) == 'SELECT') {
return new ORAResult($this, $stmt, $union_unique);
} else {
$this->mAffectedRows = oci_num_rows($stmt);
return true;
}
}
/**
* Set resource
*
* @param resource $oci8Statement
* @return Statement
*/
public function setResource($oci8Statement)
{
$type = oci_statement_type($oci8Statement);
if (false === $type || 'UNKNOWN' == $type) {
throw new Exception\InvalidArgumentException(sprintf('Invalid statement provided to %s', __METHOD__));
}
$this->resource = $oci8Statement;
$this->isPrepared = true;
return $this;
}
function doQuery($sql)
{
wfDebug("SQL: [{$sql}]\n");
if (!mb_check_encoding($sql)) {
throw new MWException("SQL encoding is invalid");
}
if (($this->mLastResult = $stmt = oci_parse($this->mConn, $sql)) === false) {
$e = oci_error($this->mConn);
$this->reportQueryError($e['message'], $e['code'], $sql, __FUNCTION__);
}
if (oci_execute($stmt, $this->execFlags()) == false) {
$e = oci_error($stmt);
$this->reportQueryError($e['message'], $e['code'], $sql, __FUNCTION__);
}
if (oci_statement_type($stmt) == "SELECT") {
return new ORAResult($this, $stmt);
} else {
$this->mAffectedRows = oci_num_rows($stmt);
return true;
}
}
/**
* returns query ID if successful, otherwise false
* this version supports:
*
* 1. $db->execute('select * from table');
*
* 2. $db->prepare('insert into table (a,b,c) values (:0,:1,:2)');
* $db->execute($prepared_statement, array(1,2,3));
*
* 3. $db->execute('insert into table (a,b,c) values (:a,:b,:c)',array('a'=>1,'b'=>2,'c'=>3));
*
* 4. $db->prepare('insert into table (a,b,c) values (:0,:1,:2)');
* $db->bind($stmt,1); $db->bind($stmt,2); $db->bind($stmt,3);
* $db->execute($stmt);
*/
function _query($sql, $inputarr = false)
{
if (is_array($sql)) {
// is prepared sql
$stmt = $sql[1];
// we try to bind to permanent array, so that oci_bind_by_name is persistent
// and carried out once only - note that max array element size is 4000 chars
if (is_array($inputarr)) {
$bindpos = $sql[3];
if (isset($this->_bind[$bindpos])) {
// all tied up already
$bindarr = $this->_bind[$bindpos];
} else {
// one statement to bind them all
$bindarr = array();
foreach ($inputarr as $k => $v) {
$bindarr[$k] = $v;
oci_bind_by_name($stmt, ":{$k}", $bindarr[$k], is_string($v) && strlen($v) > 4000 ? -1 : 4000);
}
$this->_bind[$bindpos] = $bindarr;
}
}
} else {
$stmt = oci_parse($this->_connectionID, $sql);
}
$this->_stmt = $stmt;
if (!$stmt) {
return false;
}
if (defined('ADODB_PREFETCH_ROWS')) {
@oci_set_prefetch($stmt, ADODB_PREFETCH_ROWS);
}
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]);
}
if ($this->debug == 99) {
if (is_object($v[0])) {
echo "name=:{$k}", ' len=' . $v[1], ' type=' . $v[2], '<br>';
} else {
echo "name=:{$k}", ' var=' . $inputarr[$k][0], ' len=' . $v[1], ' type=' . $v[2], '<br>';
}
}
} 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);
}
}
}
}
$this->_errorMsg = false;
$this->_errorCode = false;
if (oci_execute($stmt, $this->_commit)) {
if (count($this->_refLOBs) > 0) {
foreach ($this->_refLOBs as $key => $value) {
if ($this->_refLOBs[$key]['TYPE'] == true) {
$tmp = $this->_refLOBs[$key]['LOB']->load();
if ($this->debug) {
ADOConnection::outp("<b>OUT LOB</b>: LOB has been loaded. <br>");
}
//$_GLOBALS[$this -> _refLOBs[$key]['VAR']] = $tmp;
$this->_refLOBs[$key]['VAR'] = $tmp;
} else {
$this->_refLOBs[$key]['LOB']->save($this->_refLOBs[$key]['VAR']);
$this->_refLOBs[$key]['LOB']->free();
unset($this->_refLOBs[$key]);
if ($this->debug) {
ADOConnection::outp("<b>IN LOB</b>: LOB has been saved. <br>");
}
}
}
}
switch (@oci_statement_type($stmt)) {
case "SELECT":
return $stmt;
case 'DECLARE':
case "BEGIN":
if (is_array($sql) && !empty($sql[4])) {
$cursor = $sql[4];
if (is_resource($cursor)) {
$ok = oci_execute($cursor);
return $cursor;
}
return $stmt;
} else {
if (is_resource($stmt)) {
oci_free_statement($stmt);
return true;
}
return $stmt;
}
break;
default:
return true;
}
}
return false;
}
public function StatementType($statement)
{
return oci_statement_type($statement);
}
/**
* Executes given SQL statement. This is an overloaded method.
*
* @param string $sql SQL statement
* @return resource Result resource identifier or null
* @access protected
*/
function _execute($sql, $params = array(), $prepareOptions = array())
{
if (!$this->connection) {
$this->_statementId = false;
return false;
}
$this->_statementId = oci_parse($this->connection, $sql);
if (!$this->_statementId) {
$this->_setError($this->connection);
return false;
}
if ($this->__transactionStarted) {
$mode = OCI_DEFAULT;
} else {
$mode = OCI_COMMIT_ON_SUCCESS;
}
if (!oci_execute($this->_statementId, $mode)) {
$this->_setError($this->_statementId);
error_log($sql);
return false;
}
$this->_setError(null, true);
switch (oci_statement_type($this->_statementId)) {
case 'DESCRIBE':
case 'SELECT':
$this->_scrapeSQL($sql);
break;
default:
return $this->_statementId;
}
if ($this->_limit >= 1) {
oci_set_prefetch($this->_statementId, $this->_limit);
} else {
oci_set_prefetch($this->_statementId, 3000);
}
$this->_numRows = oci_fetch_all($this->_statementId, $this->_results, $this->_offset, $this->_limit, OCI_NUM | OCI_FETCHSTATEMENT_BY_ROW);
$this->_currentRow = 0;
$this->limit();
return $this->_statementId;
}
/**
* Processa a query no Oracle
*
* <code>
dbParse_OCI(&$dbHandle, $dbSQL)
* </code>
*
* @param handle $dbHandle HandleId da conexão
*
* @see dbOpen_OCI()
*
* @since Versão 1.0
*/
function dbParse_OCI(&$dbHandle, $dbSQL)
{
$debugBackTrace = debug_backtrace();
$debugFile = basename($debugBackTrace[1]["file"]);
$debugFunction = $debugBackTrace[1]["function"];
$dbDriver = $dbHandle[dbHandleDriver];
$dbHandleId = $dbHandle[dbHandleId];
if (!($ociParse = @oci_parse($dbHandleId, $dbSQL))) {
$e = oci_error($dbHandleId);
echo "<span style=\"text-align: left;\"><pre><b>{$dbDriver} - {$debugFile} - {$debugFunction}() - Parse</b>:" . "<br /><b>Connection</b>: " . $dbHost . "<br /><b>Code</b>: " . $e["code"] . "<br /><b>Message</b>: [" . htmlentities($e["message"]) . "]" . "<br /><b>Command</b>: " . oci_statement_type($dbHandleId);
echo "<hr />" . debugBackTrace();
echo "</pre></span>";
die;
}
return $ociParse;
}
/**
* Binds a parameter to the specified variable name
* @param string $parameter
* @param mixed $variable
* @param int $data_type
* @param int $length
* @param array $driver_options
* @return bool
*/
public function bindParam($parameter, &$variable, $data_type = PDO::PARAM_STR, $length = 4000, $driver_options = null)
{
if (strpos($parameter, ':') === false) {
$parameter = ':' . $parameter;
}
if (stripos($this->queryString, $parameter) === false) {
return true;
}
$isOutputParameter = $this->checkBitFlag($data_type, PDO::PARAM_INPUT_OUTPUT);
$data_type = $this->removeBitFlag($data_type, PDO::PARAM_INPUT_OUTPUT);
$ociParamType = $this->pdo2OciParamConst($data_type);
if ($ociParamType === SQLT_CHR) {
$variable = (string) $variable;
}
if (is_array($variable)) {
// TODO Не съм сигурен, дали ще се използва някога
$res = @oci_bind_array_by_name($this->stmt, $parameter, $variable, count($variable), $length, $ociParamType);
$this->checkError($res);
} else {
// Cursor
if ($ociParamType == OCI_B_CURSOR) {
$statementType = @oci_statement_type($this->stmt);
$this->checkError($statementType);
if (!in_array($statementType, array('BEGIN', 'DECLARE'))) {
throw new Exception('Bind cursor only in BEGIN or DECLARE statement');
}
$this->_cursor = @oci_new_cursor($this->ociPdoAdapter->getOciConnection());
$res = $this->_cursor;
$this->checkError($res);
$res = @oci_bind_by_name($this->stmt, $parameter, $this->_cursor, -1, $ociParamType);
$this->checkError($res);
} elseif ($lob_desc = $this->oci_lob_desc($ociParamType)) {
$this->_lobs[$this->_lobsCount]['type'] = $ociParamType;
$this->_lobs[$this->_lobsCount]['lob'] = @oci_new_descriptor($this->ociPdoAdapter->getOciConnection(), $lob_desc);
$res = $this->_lobs[$this->_lobsCount]['lob'];
$this->checkError($res);
$res = @oci_bind_by_name($this->stmt, $parameter, $this->_lobs[$this->_lobsCount]['lob'], -1, $ociParamType);
$this->checkError($res);
if (!$isOutputParameter) {
if (is_resource($variable) && get_resource_type($variable) === 'stream') {
$this->_lobs[$this->_lobsCount]['var'] = '';
$res = @$this->_lobs[$this->_lobsCount]['lob']->writeTemporary($this->_lobs[$this->_lobsCount]['var'], $ociParamType == SQLT_BLOB ? OCI_TEMP_BLOB : OCI_TEMP_CLOB);
$this->checkError($res);
$buffer = 8192;
while (!feof($variable)) {
$res = @$this->_lobs[$this->_lobsCount]['lob']->write(fread($variable, $buffer));
$this->checkError($res);
$res = @$this->_lobs[$this->_lobsCount]['lob']->flush();
$this->checkError($res);
}
} else {
$variable = (string) $variable;
$this->_lobs[$this->_lobsCount]['var'] =& $variable;
$res = @$this->_lobs[$this->_lobsCount]['lob']->writeTemporary($this->_lobs[$this->_lobsCount]['var'], $ociParamType == SQLT_BLOB ? OCI_TEMP_BLOB : OCI_TEMP_CLOB);
$this->checkError($res);
$res = @$this->_lobs[$this->_lobsCount]['lob']->flush();
$this->checkError($res);
}
} else {
$this->_lobs[$this->_lobsCount]['var'] =& $variable;
}
$this->_lobs[$this->_lobsCount]['input'] = !$isOutputParameter;
$this->_lobsCount++;
} else {
$res = @oci_bind_by_name($this->stmt, $parameter, $variable, $length, $ociParamType);
$this->checkError($res);
}
}
return $res;
}
public function getType()
{
set_error_handler(static::getErrorHandler());
$type = oci_statement_type($this->resource);
restore_error_handler();
return $type;
}
public function execute($sql, $bindParams = array(), $additionalParameters = array())
{
$connection = $this->connection;
$sql = $this->bind($sql, $bindParams);
// array $blobs Sabel_Db_Oci_Blob[]
$blobs = isset($additionalParameters["blob"]) ? $additionalParameters["blob"] : array();
if (empty($blobs)) {
$execMode = $this->autoCommit ? OCI_COMMIT_ON_SUCCESS : OCI_DEFAULT;
$ociStmt = oci_parse($connection, $sql);
$result = oci_execute($ociStmt, $execMode);
} else {
$ociStmt = oci_parse($connection, $sql);
foreach ($blobs as $column => $blob) {
$lob = $blob->getLob();
oci_bind_by_name($ociStmt, ":" . $column, $lob, -1, SQLT_BLOB);
}
$result = oci_execute($ociStmt, OCI_DEFAULT);
foreach ($blobs as $blob) {
$blob->save();
}
}
if (!$result) {
$this->executeError($ociStmt);
}
$rows = array();
if (oci_statement_type($ociStmt) === "SELECT") {
oci_fetch_all($ociStmt, $rows, $this->offset, $this->limit, OCI_ASSOC | OCI_FETCHSTATEMENT_BY_ROW);
$rows = array_map("array_change_key_case", $rows);
} else {
$this->affectedRows = oci_num_rows($ociStmt);
}
if (!empty($blobs) && $this->autoCommit) {
$this->commit();
}
oci_free_statement($ociStmt);
return empty($rows) ? null : $rows;
}
/**
* prepare
+-----------------------------------------
* @access public
* @param mixed $sql
* @return void
*/
public function prepare($sql)
{
$sql = str_replace(array(PHP_EOL, "\t"), ' ', trim((string) $sql));
$this->_statement = @oci_parse($this->_connect, $sql);
if (!$this->_statement) {
$e = oci_error($this->_connect);
error_handler($e['code'], $e['message'], __FILE__, __LINE__);
}
$this->_sql = $sql;
$this->_sql_method = oci_statement_type($this->_statement);
return $this;
}
/**
* Execute data manipulation statement, then roll it back
* @param $type Statement type
* @param $table Table name
* @param $query Query to validate
* @return string|bool String will be not empty if there's any
*/
protected function verifyGenericQueryRollback($type, $table, $query)
{
$this->log->debug("verifying {$type} statement");
$stmt = oci_parse($this->database, $query);
if (!$stmt) {
return 'Cannot parse statement';
}
if (oci_statement_type($stmt) != "SELECT") {
return 'Wrong statement type';
}
// try query, but don't generate result set and do not commit
$res = oci_execute($stmt, OCI_DESCRIBE_ONLY | OCI_DEFAULT);
// just in case, rollback all changes
$error = $this->lastError();
oci_rollback($this->database);
if (empty($res)) {
return 'Query failed to execute';
}
return $error;
}
/**
* @return string
*/
public function getStatementType()
{
return oci_statement_type($this->resource);
}
/**
* Se retornar el número de filas afectadas de las operaciones: INSERT, DELETE y UPDATE
*
* @author damanzano
* @since 22/10/10
*
* @param int $id_sql Identificador de la consulta.
*/
public function filasAfectadas($id_sql)
{
$numFilasAfectadas = oci_num_rows($id_sql);
$stringres = '';
switch (oci_statement_type($statement)) {
case 'INSERT':
$stringres = 'Se insertaron ' . $numFilasAfectadas . ' registros';
break;
case 'DELETE':
$stringres = 'Se eliminaron ' . $numFilasAfectadas . ' registros';
break;
case 'UPDATE':
$stringres = 'Se actualizaron ' . $numFilasAfectadas . ' registros';
break;
default:
$stringres = 'Se obtubieron ' . $numFilasAfectadas . ' registros';
}
return $numFilasAfectadas;
}
