/**
* Ejecuta una query update.
*
* @param resource $conn
* @param string $tabla
* La tabla a la que queremos hacerle la query.
* @param array $set
* Los campos que irán en la cláusula <b><i>SET</i></b>. <br>Donde
* <b><i>key</i></b> el el nombre del campo y <b><i>value</i></b> es
* el valor a comparar.
* @param aray $where
* Nombre de los campos que queremos mostrar en la cláusula
* <b><i>WHERE</i></b>.
* @param string $SQLserverName
* @return mixed array si hay un error. String columnas afectadas.
*/
function queryUpdate($conn, $tabla, $set, $where, $SQLserverName)
{
$updateString = "UPDATE {$tabla} SET ";
$primerElemento = true;
foreach ($set as $key => $value) {
if (strlen($value) == 0) {
unset($set[$key]);
continue;
}
if ($primerElemento) {
$updateString .= " {$key} = ?";
$primerElemento = false;
} else {
$updateString .= ", {$key} = ?";
}
}
$whereString = " WHERE ";
$primerElemento = true;
foreach ($where as $key => $value) {
if ($primerElemento) {
$whereString .= " ({$key} = ?)";
$primerElemento = false;
} else {
$whereString .= " and ({$key} = ?)";
}
}
$sql = $updateString . $whereString;
$parametros = array_merge($set, $where);
$parametros = array_values($parametros);
// Ejecutar la query
//////////////////////////////////////////////////////
if ($farmacia == FARMACIA_DEBUG or strlen(FARMACIA_DEBUG) == 0) {
if (DEBUG & DEBUG_QUERY) {
$mensaje = "--[" . date("c") . "] Farmacia: {$farmacia} \n";
$mensaje .= "--Query hecha en el fichero: " . __FILE__ . ", en la línea: " . __LINE__ . "\n";
error_log($mensaje . verQuery($sql, $parametros) . "\r\n", 3, DIRECTORIO_LOG . __FUNCTION__ . "_" . date("YmdH") . ".log");
}
}
$stmt = sqlsrv_query($conn, $sql, $parametros);
if ($stmt === false) {
if (($errors = sqlsrv_errors()) != null) {
$Merror = $errors[0]["message"];
/* Guardamos en un fichero el error y la query que lo ha generado para poder procesarlo después.
* Sólo en este caso no nos fijamos en el valor de la constante DEBUG ya que nos interesa guardarlo.*/
$mensaje = "--[" . date("c") . "] farmacia: {$farmacia} mensaje: {$Merror} \n";
$mensaje .= "--Error en el fichero: " . __FILE__ . ", en la línea: " . __LINE__;
error_log($mensaje . verQuery($sql, $parametros) . "\r\n", 3, DIRECTORIO_LOG . $tabla . "_" . $SQLserverName . "_" . date("YmdH") . ".log");
return $errors;
}
}
// Desplegar la información del resultado
//////////////////////////////////////////////////////
return sqlsrv_rows_affected($stmt);
}
function BatchUpdate($itemNameQuery, $expenses, $durability, $useInstructions)
{
// Alterar apenas campos de usuário aqui (começados com U_ ), é proibido alterar campos do SAP B1
$query = "UPDATE OITM SET U_Expenses = " . $expenses . ",U_Durability = " . $durability . ", U_UseInstructions = '" . $useInstructions . "' WHERE ItemName LIKE '%" . $itemNameQuery . "%'";
$result = sqlsrv_query($this->sqlserverConnection, $query);
if ($result) {
return sqlsrv_rows_affected($result);
}
if (!$result && $this->showErrors) {
print_r(sqlsrv_errors());
echo '<br/>';
}
return null;
}
/**
* Ejecuta una query insert.
*
* @param resource $conn
* @param string $tabla
* La tabla a la que queremos hacerle el insert.
* @param array $parametros
* Los campos que se van a insertar. <br>Donde
* <b><i>key</i></b> el el nombre del campo y <b><i>value</i></b> es
* el valor del mismo.
* @param string $SQLserverName
* @return mixed Array si hay errores de SQL.
* Int con el número de filas afectadas (0..n).
*/
function queryInsert($conn, $tabla, $parametros, $SQLserverName)
{
global $farmacia;
$respuestaError = new respuestaError();
if (isset($parametros["farmacia"])) {
$farmacia = $parametros["farmacia"];
}
$insertString = "INSERT INTO {$tabla} (";
$values = "VALUES (";
$primerElemento = true;
foreach ($parametros as $key => $value) {
if ($primerElemento) {
$insertString .= "{$key}";
$values .= "?";
$primerElemento = false;
} else {
$insertString .= ", {$key}";
$values .= ", ?";
}
}
$insertString .= ") ";
$values .= ")";
$sql = $insertString . $values;
$parametros = array_values($parametros);
// Ejecutar la query
//////////////////////////////////////////////////////
if ($farmacia == FARMACIA_DEBUG or strlen(FARMACIA_DEBUG) == 0) {
if (DEBUG & DEBUG_QUERY) {
$mensaje = "--[" . date("c") . "] Farmacia: {$farmacia} \n";
$mensaje .= "--Query hecha en el fichero: " . __FILE__ . ", en la línea: " . __LINE__ . "\n";
error_log($mensaje . verQuery($sql, $parametros) . "\r\n", 3, DIRECTORIO_LOG . __FUNCTION__ . "_" . date("YmdH") . ".log");
}
}
$stmt = sqlsrv_query($conn, $sql, $parametros);
if ($stmt === false) {
if (($errors = sqlsrv_errors()) != null) {
$Merror = $errors[0]["message"];
/* Guardamos en un fichero el error y la query que lo ha generado para poder procesarlo después.
* Sólo en este caso no filtramos el valor de la constante DEBUG ya que en cualquier caso nos interesa guardar el error.*/
$mensaje = "--[" . date("c") . "] farmacia: {$farmacia} mensaje: {$Merror} \n";
$mensaje .= "--Error en el fichero: " . __FILE__ . ", en la línea: " . __LINE__ . "\n";
error_log($mensaje . verQuery($sql, $parametros) . "\r\n", 3, DIRECTORIO_LOG . $tabla . "_" . $SQLserverName . "_" . date("YmdH") . ".log");
return $errors;
}
}
// Desplegar la información del resultado
//////////////////////////////////////////////////////
return sqlsrv_rows_affected($stmt);
}
public function execute($sql, array $data = null)
{
$this->connect();
if (!is_array($data)) {
$data = array();
}
$temp = sqlsrv_query($this->lnk, $sql, $data);
if (!$temp) {
throw new DatabaseException('Could not execute query : ' . json_encode(sqlsrv_errors()) . ' <' . $sql . '>');
}
if (preg_match('@^\\s*(INSERT|REPLACE)\\s+INTO@i', $sql)) {
$this->iid = sqlsrv_query($this->lnk, 'SELECT SCOPE_IDENTITY()');
if ($this->iid) {
$this->iid = sqlsrv_fetch_array($this->iid, \SQLSRV_FETCH_NUMERIC);
$this->iid = $this->iid[0];
}
$this->aff = sqlsrv_rows_affected($temp);
}
return $temp;
}
/**
* Returns the number of rows affected by the execution of the
* last INSERT, DELETE, or UPDATE statement executed by this
* statement object.
*
* @return int The number of rows affected.
* @throws Zend_Db_Statement_Exception
*/
public function rowCount()
{
if (!$this->_stmt) {
return false;
}
if (!$this->_executed) {
return 0;
}
$num_rows = sqlsrv_rows_affected($this->_stmt);
// Strict check is necessary; 0 is a valid return value
if ($num_rows === false) {
require_once 'include/Zend/Db/Statement/Sqlsrv/Exception.php';
throw new Zend_Db_Statement_Sqlsrv_Exception(sqlsrv_errors());
}
return $num_rows;
}
/**
* (non-PHPdoc)
* @see classes/connectionengine/Result_STH#setNbRow()
*/
protected function setNbRow()
{
$this->nbRow = sqlsrv_rows_affected($this->result);
if ($this->nbRow === false) {
throw new ConnectionengineException('Error in calling sqlsrv_rows_affected: ' . $this->query, __FILE__, __LINE__, sqlsrv_errors());
}
if ($this->nbRow == -1) {
$this->nbRow = 0;
}
}
function isAffected($stmt)
{
$result = sqlsrv_rows_affected($stmt);
if ($result > 0) {
return $result;
} else {
return 0;
}
}
/**
* Affected Rows
*
* @return int
*/
public function affected_rows()
{
return sqlsrv_rows_affected($this->result_id);
}
public function query($sql, $errorLevel = E_USER_ERROR)
{
if (isset($_REQUEST['previewwrite']) && in_array(strtolower(substr($sql, 0, strpos($sql, ' '))), array('insert', 'update', 'delete', 'replace'))) {
Debug::message("Will execute: {$sql}");
return;
}
if (isset($_REQUEST['showqueries'])) {
$starttime = microtime(true);
}
$error = '';
if ($this->mssql) {
$handle = mssql_query($sql, $this->dbConn);
$error = mssql_get_last_message();
} else {
$handle = sqlsrv_query($this->dbConn, $sql);
if ($handle) {
$this->lastAffectedRows = sqlsrv_rows_affected($handle);
}
if (function_exists('sqlsrv_errors')) {
$errInfo = sqlsrv_errors();
if ($errInfo) {
foreach ($errInfo as $info) {
$error .= implode(', ', array($info['SQLSTATE'], $info['code'], $info['message']));
}
}
}
}
if (isset($_REQUEST['showqueries'])) {
$endtime = round(microtime(true) - $starttime, 4);
Debug::message("\n{$sql}\n{$endtime}ms\n", false);
}
if (!$handle && $errorLevel) {
$this->databaseError("Couldn't run query ({$error}): {$sql}", $errorLevel);
}
return new MSSQLQuery($this, $handle, $this->mssql);
}
/**
* INSERT SELECT wrapper
* $varMap must be an associative array of the form array( 'dest1' => 'source1', ...)
* Source items may be literals rather than field names, but strings should be quoted with Database::addQuotes()
* $conds may be "*" to copy the whole table
* srcTable may be an array of tables.
*/
function insertSelect($destTable, $srcTable, $varMap, $conds, $fname = 'DatabaseMssql::insertSelect', $insertOptions = array(), $selectOptions = array())
{
$ret = parent::insertSelect($destTable, $srcTable, $varMap, $conds, $fname, $insertOptions, $selectOptions);
if ($ret === false) {
throw new DBQueryError($this, $this->getErrors(), $this->lastErrno(), '', $fname);
} elseif ($ret != NULL) {
// remember number of rows affected
$this->mAffectedRows = sqlsrv_rows_affected($ret);
return $ret;
}
return NULL;
}
/**
* Retrieve number of affected rows for last query
*
* @return int
*/
protected function affectedRows()
{
return sqlsrv_rows_affected($this->result);
}
/**
* Perform a MySQL database query, using current database connection.
*
* More information can be found on the codex page.
*
* @since 0.71
*
* @param string $query Database query
* @return int|false Number of rows affected/selected or false on error
*/
public function query($query)
{
if (!$this->ready) {
$this->check_current_query = true;
return false;
}
/**
* Filter the database query.
*
* Some queries are made before the plugins have been loaded,
* and thus cannot be filtered with this method.
*
* @since 2.1.0
*
* @param string $query Database query.
*/
$query = apply_filters('query', $query);
$this->flush();
// Log how the function was called
$this->func_call = "\$db->query(\"{$query}\")";
// If we're writing to the database, make sure the query will write safely.
if ($this->check_current_query && !$this->check_ascii($query)) {
$stripped_query = $this->strip_invalid_text_from_query($query);
// strip_invalid_text_from_query() can perform queries, so we need
// to flush again, just to make sure everything is clear.
$this->flush();
if ($stripped_query !== $query) {
$this->insert_id = 0;
return false;
}
}
$this->check_current_query = true;
// Keep track of the last query for debug..
$this->last_query = $query;
$this->_do_query($query);
// MySQL server has gone away, try to reconnect
/*
$mysql_errno = 0;
if ( ! empty( $this->dbh ) ) {
if ( $this->use_mysqli ) {
$mysql_errno = mysqli_errno( $this->dbh );
} else {
$mysql_errno = mysql_errno( $this->dbh );
}
}
if ( empty( $this->dbh ) || 2006 == $mysql_errno ) {
if ( $this->check_connection() ) {
$this->_do_query( $query );
} else {
$this->insert_id = 0;
return false;
}
}
*/
// If there is an error, first attempt to translate
$errors = sqlsrv_errors();
if (!empty($errors) && is_array($errors)) {
switch ($errors[0]['code']) {
case 102:
case 145:
case 156:
case 195:
case 207:
case 241:
case 261:
case 321:
case 1018:
case 8120:
case 8127:
if (getenv('ProjectNamiLogTranslate')) {
$begintransmsg = date("Y-m-d H:i:s") . " -- Begin translation attempt: {$query} \n";
error_log($begintransmsg, 3, 'D:\\home\\LogFiles\\translate.log');
}
$sqltranslate = new SQL_Translations(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);
$query = $sqltranslate->translate($query);
if (getenv('ProjectNamiLogTranslate')) {
$endtransmsg = date("Y-m-d H:i:s") . " -- Translation result: {$query} \n";
error_log($endtransmsg, 3, 'D:\\home\\LogFiles\\translate.log');
}
$this->last_query = $query;
$this->_do_query($query);
// If there is an error then take note of it..
$errors = sqlsrv_errors();
}
}
if (!empty($errors) && is_array($errors)) {
$this->last_error = $errors[0]['message'];
// Clear insert_id on a subsequent failed insert.
if ($this->insert_id && preg_match('/^\\s*(insert|replace)\\s/i', $query)) {
$this->insert_id = 0;
}
$this->print_error();
return false;
}
if (preg_match('/^\\s*(create|alter|truncate|drop)\\s/i', $query)) {
$return_val = $this->result;
} elseif (preg_match('/^\\s*(insert|delete|update|replace)\\s/i', $query) && $this->query_statement_resource != false) {
$this->rows_affected = sqlsrv_rows_affected($this->query_statement_resource);
// Take note of the insert_id
if (preg_match('/^\\s*(insert|replace)\\s/i', $query)) {
$this->insert_id = sqlsrv_query($this->dbh, 'SELECT isnull(scope_identity(), 0)');
$row = sqlsrv_fetch_array($this->insert_id);
$this->insert_id = $row[0];
}
// Return number of rows affected
$return_val = $this->rows_affected;
} else {
$num_rows = 0;
while ($row = @sqlsrv_fetch_object($this->query_statement_resource)) {
$this->last_result[$num_rows] = $row;
$num_rows++;
}
// Log number of rows the query returned
// and return number of rows selected
$this->num_rows = $num_rows;
$return_val = $num_rows;
}
if (isset($this->last_result[0]) && is_object($this->last_result[0]) && isset($this->last_result[0]->found_rows)) {
$this->last_query_total_rows = $this->last_result[0]->found_rows;
}
return $return_val;
}
public function query($type, $sql, $as_object = FALSE, array $params = NULL)
{
// Make sure the database is connected
$this->_connection or $this->connect();
if (!empty($this->_config['profiling'])) {
// Benchmark this query for the current instance
$benchmark = Profiler::start("Database ({$this->_instance})", $sql);
}
// Detect type Database::INSERT and ensure last id is captured
if ($type === Database::INSERT) {
// We need to do some magic here to get the insert ID
// this is a glorious hack!
$sql_statement = (string) $sql;
// Locate VALUES
$values = strpos($sql, 'VALUES');
// Insert the lastInsertId logic
$sql = substr($sql_statement, 0, $values) . 'output inserted.identitycol AS lastInsertId ' . substr($sql_statement, $values);
}
// Execute the query
if (($result = sqlsrv_query($this->_connection, $sql, $params, array('Scrollable' => SQLSRV_CURSOR_KEYSET))) === FALSE) {
// If something went wrong
if (isset($benchmark)) {
// This benchmark is worthless
Profiler::delete($benchmark);
}
// Get the errors
$error = sqlsrv_errors(SQLSRV_ERR_ERRORS);
// Throw an exception
throw new Database_Sqlsrv_Exception(':error [ :query ]', array(':error' => $error[0]['message'], ':query' => $sql), $error[0]['code']);
}
if (isset($benchmark)) {
Profiler::stop($benchmark);
}
// Set the last query
$this->last_query = $sql;
if ($type === Database::SELECT) {
// Return an iterator of results
return new Database_Sqlsrv_Result($result, $sql, $as_object);
} elseif ($type === Database::INSERT) {
// Get the last insert id
if (($insert_id = sqlsrv_fetch_array($result)) === FALSE) {
// Get the errors
$error = sqlsrv_errors(SQLSRV_ERR_ERRORS);
// Throw an exception
throw new Database_Sqlsrv_Exception(':error [ :query ]', array(':error' => 'Unable to get the last inserted row ID from driver', ':query' => $sql), $error[0]['code']);
}
return array($insert_id['lastInsertId'], sqlsrv_rows_affected($result));
} else {
// Return the number of rows affected
return sqlsrv_rows_affected($result);
}
}
public function execute($bind_params = false)
{
$ret_val = false;
$arg_list = func_get_args();
$lazy = isset($arg_list[1]) && $arg_list[1] ? true : false;
//----------------------------------------------
// Prepare SQL Statement
//----------------------------------------------
$prepare_status = $this->_prepare($this->curr_query, $bind_params, $lazy);
if (!$prepare_status) {
if ($this->check_and_print_error()) {
return false;
}
$this->print_error('Query prepare failed.');
return false;
}
if (!$this->stmt) {
return false;
}
//----------------------------------------------
// Execute Query
//----------------------------------------------
$exec_status = @sqlsrv_execute($this->stmt);
if (!$exec_status) {
if ($this->check_and_print_error()) {
return false;
}
$this->print_error('Query execution failed.');
return false;
}
//----------------------------------------------
// Create Data Result Object if Necessary
//----------------------------------------------
if ($this->stmt && gettype($this->stmt) != 'boolean') {
//----------------------------------------------
// Affected Rows
//----------------------------------------------
$this->affected_rows = sqlsrv_rows_affected($this->stmt);
$ret_val = $this->affected_rows;
//----------------------------------------------
// Create Data Result Object
//----------------------------------------------
$has_rows = sqlsrv_has_rows($this->stmt);
$this->data_result = new data_result($this->stmt, $this->data_src);
//----------------------------------------------
// Last Insert ID
//----------------------------------------------
$this->last_id = null;
}
//----------------------------------------------
// Return Data Result Object if it exists
//----------------------------------------------
if ($this->data_result) {
$this->num_rows = $this->data_result->num_rows();
$this->num_fields = $this->data_result->num_fields();
$ret_val = $this->data_result;
}
//----------------------------------------------
// Check for Errors
//----------------------------------------------
if ($this->check_and_print_error()) {
return false;
}
return $ret_val;
}
/**
* @param string $sql
* @return bool|MssqlResult
* @throws DBUnexpectedError
*/
protected function doQuery($sql)
{
global $wgDebugDumpSql;
if ($wgDebugDumpSql) {
wfDebug("SQL: [{$sql}]\n");
}
$this->offset = 0;
// several extensions seem to think that all databases support limits
// via LIMIT N after the WHERE clause well, MSSQL uses SELECT TOP N,
// so to catch any of those extensions we'll do a quick check for a
// LIMIT clause and pass $sql through $this->LimitToTopN() which parses
// the limit clause and passes the result to $this->limitResult();
if (preg_match('/\\bLIMIT\\s*/i', $sql)) {
// massage LIMIT -> TopN
$sql = $this->LimitToTopN($sql);
}
// MSSQL doesn't have EXTRACT(epoch FROM XXX)
if (preg_match('#\\bEXTRACT\\s*?\\(\\s*?EPOCH\\s+FROM\\b#i', $sql, $matches)) {
// This is same as UNIX_TIMESTAMP, we need to calc # of seconds from 1970
$sql = str_replace($matches[0], "DATEDIFF(s,CONVERT(datetime,'1/1/1970'),", $sql);
}
// perform query
// SQLSRV_CURSOR_STATIC is slower than SQLSRV_CURSOR_CLIENT_BUFFERED (one of the two is
// needed if we want to be able to seek around the result set), however CLIENT_BUFFERED
// has a bug in the sqlsrv driver where wchar_t types (such as nvarchar) that are empty
// strings make php throw a fatal error "Severe error translating Unicode"
if ($this->mScrollableCursor) {
$scrollArr = array('Scrollable' => SQLSRV_CURSOR_STATIC);
} else {
$scrollArr = array();
}
if ($this->mPrepareStatements) {
// we do prepare + execute so we can get its field metadata for later usage if desired
$stmt = sqlsrv_prepare($this->mConn, $sql, array(), $scrollArr);
$success = sqlsrv_execute($stmt);
} else {
$stmt = sqlsrv_query($this->mConn, $sql, array(), $scrollArr);
$success = (bool) $stmt;
}
if ($this->mIgnoreDupKeyErrors) {
// ignore duplicate key errors, but nothing else
// this emulates INSERT IGNORE in MySQL
if ($success === false) {
$errors = sqlsrv_errors(SQLSRV_ERR_ERRORS);
$success = true;
foreach ($errors as $err) {
if ($err['SQLSTATE'] == '23000' && $err['code'] == '2601') {
continue;
// duplicate key error
} elseif ($err['SQLSTATE'] == '01000' && $err['code'] == '3621') {
continue;
// generic "the statement has been terminated" error
}
$success = false;
// getting here means we got an error we weren't expecting
break;
}
if ($success) {
$this->mAffectedRows = 0;
return true;
}
}
}
if ($success === false) {
return false;
}
// remember number of rows affected
$this->mAffectedRows = sqlsrv_rows_affected($stmt);
return $stmt;
}
/**
* Sets the number of rows affected by the query
*
* @param fResult $result The result object for the query
* @param mixed $resource Only applicable for `ibm_db2`, `pdo`, `oci8` and `sqlsrv` extentions or `mysqli` prepared statements - this is either the `PDOStatement` object, `mysqli_stmt` object or the `oci8` or `sqlsrv` resource
* @return void
*/
private function setAffectedRows($result, $resource = NULL)
{
if ($this->extension == 'ibm_db2') {
$insert_update_delete = preg_match('#^\\s*(INSERT|UPDATE|DELETE)\\b#i', $result->getSQL());
$result->setAffectedRows(!$insert_update_delete ? 0 : db2_num_rows($resource));
} elseif ($this->extension == 'mssql') {
$affected_rows_result = mssql_query('SELECT @@ROWCOUNT AS rows', $this->connection);
$result->setAffectedRows((int) mssql_result($affected_rows_result, 0, 'rows'));
} elseif ($this->extension == 'mysql') {
$result->setAffectedRows(mysql_affected_rows($this->connection));
} elseif ($this->extension == 'mysqli') {
if (is_object($resource)) {
$result->setAffectedRows($resource->affected_rows);
} else {
$result->setAffectedRows(mysqli_affected_rows($this->connection));
}
} elseif ($this->extension == 'oci8') {
$result->setAffectedRows(oci_num_rows($resource));
} elseif ($this->extension == 'pgsql') {
$result->setAffectedRows(pg_affected_rows($result->getResult()));
} elseif ($this->extension == 'sqlite') {
$result->setAffectedRows(sqlite_changes($this->connection));
} elseif ($this->extension == 'sqlsrv') {
$result->setAffectedRows(sqlsrv_rows_affected($resource));
} elseif ($this->extension == 'pdo') {
// This fixes the fact that rowCount is not reset for non INSERT/UPDATE/DELETE statements
try {
if (!$resource || !$resource->fetch()) {
throw new PDOException();
}
$result->setAffectedRows(0);
} catch (PDOException $e) {
// The SQLite PDO driver seems to return 1 when no rows are returned from a SELECT statement
if ($this->type == 'sqlite' && $this->extension == 'pdo' && preg_match('#^\\s*SELECT#i', $result->getSQL())) {
$result->setAffectedRows(0);
} elseif (!$resource) {
$result->setAffectedRows(0);
} else {
$result->setAffectedRows($resource->rowCount());
}
}
}
}
/**
* Get affected rows
*
* @return integer
*/
public function getAffectedRows()
{
return sqlsrv_rows_affected($this->resource);
}
/**
* Returns the number of rows affected by the execution of the
* last INSERT, DELETE, or UPDATE statement executed by this
* statement object.
*
* @return int The number of rows affected.
* @throws \Zend\DB\Statement\Exception
*/
public function rowCount()
{
if (!$this->_stmt) {
return false;
}
if (!$this->_executed) {
return 0;
}
$num_rows = sqlsrv_rows_affected($this->_stmt);
// Strict check is necessary; 0 is a valid return value
if ($num_rows === false) {
throw new Exception(sqlsrv_errors());
}
return $num_rows;
}
function _affectedrows()
{
return sqlsrv_rows_affected($this->_queryID);
}
/**
* 执行语句
* @access public
* @param string $str sql指令
* @param array $bind 参数绑定
* @return integer
*/
public function execute($str, $bind = array())
{
$this->initConnect(true);
if (!$this->_linkID) {
return false;
}
//释放前次的查询结果
if ($this->queryID) {
$this->free();
}
N('db_write', 1);
// 记录开始执行时间
G('queryStartTime');
$str = str_replace(array_keys($bind), '?', $str);
$bind = array_values($bind);
$this->queryStr = $str;
$this->queryID = sqlsrv_query($this->_linkID, $str, $bind);
$this->debug();
if (false === $this->queryID) {
$this->error();
return false;
} else {
$this->numRows = sqlsrv_rows_affected($this->queryID);
$this->lastInsID = $this->mssql_insert_id();
return $this->numRows;
}
}
/**
* Move the internal result pointer to the next available result
*
* @return true on success, false if there is no more result set or an error object on failure
* @access public
*/
function nextResult()
{
if (false === $this->result) {
return $this->db->raiseError(MDB2_ERROR_NEED_MORE_DATA, null, null, 'resultset has already been freed', __FUNCTION__);
}
if (null === $this->result) {
return false;
}
$ret = sqlsrv_next_result($this->result);
if ($ret) {
$this->cursor = 0;
$this->rows = array();
$this->numFields = sqlsrv_num_fields($this->result);
$this->fieldMeta = sqlsrv_field_metadata($this->result);
$this->numRowsAffected = sqlsrv_rows_affected($this->result);
while ($row = sqlsrv_fetch_array($this->result, SQLSRV_FETCH_ASSOC)) {
if ($row !== null) {
if ($this->offset && $this->offset_count < $this->offset) {
$this->offset_count++;
continue;
}
foreach ($row as $k => $v) {
if (is_object($v) && method_exists($v, 'format')) {
//DateTime Object
//$v->setTimezone(new DateTimeZone('GMT'));//TS_ISO_8601 with a trailing 'Z' is GMT
$row[$k] = $v->format("Y-m-d H:i:s");
}
}
$this->rows[] = $row;
//read results into memory, cursors are not supported
}
}
$this->rowcnt = count($this->rows);
}
return $ret;
}
/**
* {@inheritdoc}
*/
public function rowCount()
{
return sqlsrv_rows_affected($this->stmt);
}
/**
* Returns number of affected rows in previous database operation. If no previous operation exists,
* this returns false.
*
* @return integer Number of affected rows
*/
function lastAffected()
{
if ($this->_result) {
return sqlsrv_rows_affected($this->_result);
}
return null;
}
/**
* Get the number of affected rows for the previous executed SQL statement.
*
* @return integer The number of affected rows.
*
* @since 12.1
*/
public function getAffectedRows()
{
$this->connect();
return sqlsrv_rows_affected($this->cursor);
}
protected function affected_rows($db, $result)
{
return sqlsrv_rows_affected($result);
}
function query($query)
{
//if flag to convert query from MySql syntax to MS-Sql syntax is true
//convert the query
if ($this->convertMySqlToMSSqlQuery == true) {
$query = $this->ConvertMySqlToMSSql($query);
}
// Initialise return
$return_val = 0;
// Flush cached values..
$this->flush();
// For reg expressions
$query = trim($query);
// Log how the function was called
$this->func_call = "\$db->query(\"{$query}\")";
// Keep track of the last query for debug..
$this->last_query = $query;
// Count how many queries there have been
$this->num_queries++;
// Use core file cache function
if ($cache = $this->get_cache($query)) {
return $cache;
}
// If there is no existing database connection then try to connect
if (!isset($this->dbh) || !$this->dbh) {
$this->connect($this->dbuser, $this->dbpassword, $this->dbname, $this->dbhost);
}
// Perform the query via std mssql_query function..
$this->result = @sqlsrv_query($this->dbh, $query);
// If there is an error then take note of it..
if ($this->result === false) {
$errors = sqlsrv_errors();
if (!empty($errors)) {
$is_insert = true;
foreach ($errors as $error) {
$sqlError = "ErrorCode: " . $error['code'] . " ### State: " . $error['SQLSTATE'] . " ### Error Message: " . $error['message'] . " ### Query: " . $query;
$this->register_error($sqlError);
$this->show_errors ? trigger_error($sqlError, E_USER_WARNING) : null;
}
}
return false;
}
// Query was an insert, delete, update, replace
$is_insert = false;
if (preg_match("/^(insert|delete|update|replace)\\s+/i", $query)) {
$this->rows_affected = @sqlsrv_rows_affected($this->dbh);
// Take note of the insert_id
if (preg_match("/^(insert|replace)\\s+/i", $query)) {
$identityresultset = @sqlsrv_query($this->dbh, "select SCOPE_IDENTITY()");
if ($identityresultset != false) {
$identityrow = @sqlsrv_fetch($identityresultset);
$this->insert_id = $identityrow[0];
}
}
// Return number of rows affected
$return_val = $this->rows_affected;
} else {
// Take note of column info
$i = 0;
foreach (@sqlsrv_field_metadata($this->result) as $field) {
foreach ($field as $name => $value) {
$name = strtolower($name);
if ($name == "size") {
$name = "max_length";
} else {
if ($name == "type") {
$name = "typeid";
}
}
$col->{$name} = $value;
}
$col->type = $this->get_datatype($col);
$this->col_info[$i++] = $col;
unset($col);
}
// Store Query Results
$num_rows = 0;
while ($row = @sqlsrv_fetch_object($this->result)) {
// Store relults as an objects within main array
$this->last_result[$num_rows] = $row;
$num_rows++;
}
@sqlsrv_free_stmt($this->result);
// Log number of rows the query returned
$this->num_rows = $num_rows;
// Return number of rows selected
$return_val = $this->num_rows;
}
// disk caching of queries
$this->store_cache($query, $is_insert);
// If debug ALL queries
$this->trace || $this->debug_all ? $this->debug() : null;
return $return_val;
}
public function saveDeviceInCache($userAgent, &$device)
{
$tablename = TeraWurflConfig::$TABLE_PREFIX . 'Cache';
$ua = $this->SQLPrep($userAgent);
$packed_device = $this->SQLPrep(serialize($device));
$this->numQueries++;
$res = sqlsrv_query($this->dbcon, "INSERT INTO {$tablename} (user_agent,cache_data) VALUES ({$ua},{$packed_device})");
if (sqlsrv_rows_affected($res) > 0) {
sqlsrv_free_stmt($res);
return true;
}
sqlsrv_free_stmt($res);
return false;
}
/**
* Returns affected rows count from last executed query.
*
* @return integer
*/
public function getAffectedRowsCount()
{
return sqlsrv_rows_affected($this->lastQueryResult);
}
public function execute()
{
if ($this->usedPrepare) {
if (!sqlsrv_execute($this->stmt)) {
throw new SqlException('Failed to execute prepared statement', sqlsrv_errors(), $this->query, $this->params);
}
}
$next = null;
$selectedRows = false;
$this->affected = 0;
// get affected row count from each result (triggers could cause multiple inserts)
do {
$affectedRows = sqlsrv_rows_affected($this->stmt);
if ($affectedRows === false) {
throw new SqlException('Failed to get affected row count', sqlsrv_errors(), $this->query, $this->params);
} elseif ($affectedRows === -1) {
$selectedRows = true;
// reached SELECT result
break;
// so that getIterator will be able to select the rows
} else {
$this->affected += $affectedRows;
}
} while ($next = sqlsrv_next_result($this->stmt));
if ($next === false) {
throw new SqlException('Failed to get next result', sqlsrv_errors(), $this->query, $this->params);
}
if ($selectedRows === false && !$this->usedPrepare) {
$this->close();
// no results, so statement can be closed
}
}
private function executeQueryWithParameters()
{
$return = false;
if (count($this->parameters) != 0) {
// Prepato los parametros
$params = array();
foreach ($this->parameters as $param => $paramContent) {
$params[] = $paramContent['value'];
}
// Preparo la consulta
$stmt = sqlsrv_prepare($this->link, $this->query, $params);
// Se procede con la ejecucion de la consulta
if ($this->queryType == 'other') {
if (sqlsrv_execute($stmt) === true) {
$return = true;
$this->error = sqlsrv_errors();
}
} else {
if (sqlsrv_execute($stmt) === true) {
// Conteo de registros
if ($this->queryType == 'insert' || $this->queryType == 'update' || $this->queryType == 'delete') {
$this->numRows = sqlsrv_rows_affected($stmt);
$return = true;
} else {
// Se obtiene el numero de filas obtenidas de los metadatos de la consulta
$this->numRows = sqlsrv_num_rows($stmt);
$fetchType = SQLSRV_FETCH_NUMERIC;
if ($this->queryReturn == 'assoc') {
$fetchType = SQLSRV_FETCH_ASSOC;
} elseif ($this->queryReturn == 'both') {
$fetchType = SQLSRV_FETCH_BOTH;
}
$return = array();
while ($row = sqlsrv_fetch_array($stmt, $fetchType)) {
array_push($return, $row);
}
}
$this->error = sqlsrv_errors();
sqlsrv_free_stmt($stmt);
} else {
$this->error = sqlsrv_errors();
}
}
}
return $return;
}
