<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed'); /** * CodeIgniter * * An open source application development framework for PHP 5.2.4 or newer * * NOTICE OF LICENSE * * Licensed under the Open Software License version 3.0 * * This source file is subject to the Open Software License (OSL 3.0) that is * bundled with this package in the files license.txt / license.rst. It is * also available through the world wide web at this URL: * http://opensource.org/licenses/OSL-3.0 * If you did not receive a copy of the license and are unable to obtain it * through the world wide web, please send an email to * licensing@ellislab.com so we can send you a copy immediately. * * @package CodeIgniter * @author EllisLab Dev Team * @copyright Copyright (c) 2008 - 2012, EllisLab, Inc. (http://ellislab.com/) * @license http://opensource.org/licenses/OSL-3.0 Open Software License (OSL 3.0) * @link http://codeigniter.com * @since Version 2.0.3 * @filesource */ /** * SQLSRV Database Adapter Class * * Note: _DB is an extender class that the app controller * creates dynamically based on whether the query builder * class is being used or not. * * @package CodeIgniter * @subpackage Drivers * @category Database * @author EllisLab Dev Team * @link http://codeigniter.com/user_guide/database/ */ class CI_DB_sqlsrv_driver extends CI_DB { public $dbdriver = 'sqlsrv'; // The character used for escaping protected $_escape_char = '"'; // clause and character used for LIKE escape sequences protected $_like_escape_str = " ESCAPE '%s' "; protected $_like_escape_chr = '!'; /** * The syntax to count rows is slightly different across different * database engines, so this string appears in each driver and is * used for the count_all() and count_all_results() functions. */ protected $_count_string = 'SELECT COUNT(*) AS '; protected $_random_keyword = ' NEWID()'; // SQLSRV-specific properties protected $_quoted_identifier = TRUE; /** * Non-persistent database connection * * @return resource */ public function db_connect($pooling = FALSE) { // Check for a UTF-8 charset being passed as CI's default 'utf8'. $character_set = (0 === strcasecmp('utf8', $this->char_set)) ? 'UTF-8' : $this->char_set; $connection = array( 'UID' => empty($this->username) ? '' : $this->username, 'PWD' => empty($this->password) ? '' : $this->password, 'Database' => $this->database, 'ConnectionPooling' => $pooling ? 1 : 0, 'CharacterSet' => $character_set, 'ReturnDatesAsStrings' => 1 ); // If the username and password are both empty, assume this is a // 'Windows Authentication Mode' connection. if (empty($connection['UID']) && empty($connection['PWD'])) { unset($connection['UID'], $connection['PWD']); } $conn_id = sqlsrv_connect($this->hostname, $connection); // Determine how identifiers are escaped $query = $this->query('SELECT CASE WHEN (@@OPTIONS | 256) = @@OPTIONS THEN 1 ELSE 0 END AS qi'); $query = $query->row_array(); $this->_quoted_identifier = empty($query) ? FALSE : (bool) $query->qi; $this->_escape_char = ($this->_quoted_identifier) ? '"' : array('[', ']'); return $conn_id; } // -------------------------------------------------------------------- /** * Persistent database connection * * @return resource */ public function db_pconnect() { return $this->db_connect(TRUE); } // -------------------------------------------------------------------- /** * Select the database * * @param string database name * @return bool */ public function db_select($database = '') { if ($database === '') { $database = $this->database; } if ($this->_execute('USE '.$database)) { $this->database = $database; return TRUE; } return FALSE; } // -------------------------------------------------------------------- /** * Execute the query * * @param string an SQL query * @return resource */ protected function _execute($sql) { return (is_write_type($sql) && stripos($sql, 'INSERT') === FALSE) ? sqlsrv_query($this->conn_id, $sql) : sqlsrv_query($this->conn_id, $sql, NULL, array('Scrollable' => SQLSRV_CURSOR_STATIC)); } // -------------------------------------------------------------------- /** * Begin Transaction * * @return bool */ public function trans_begin($test_mode = FALSE) { // When transactions are nested we only begin/commit/rollback the outermost ones if ( ! $this->trans_enabled OR $this->_trans_depth > 0) { return TRUE; } // Reset the transaction failure flag. // If the $test_mode flag is set to TRUE transactions will be rolled back // even if the queries produce a successful result. $this->_trans_failure = ($test_mode === TRUE); return sqlsrv_begin_transaction($this->conn_id); } // -------------------------------------------------------------------- /** * Commit Transaction * * @return bool */ public function trans_commit() { // When transactions are nested we only begin/commit/rollback the outermost ones if ( ! $this->trans_enabled OR $this->_trans_depth > 0) { return TRUE; } return sqlsrv_commit($this->conn_id); } // -------------------------------------------------------------------- /** * Rollback Transaction * * @return bool */ public function trans_rollback() { // When transactions are nested we only begin/commit/rollback the outermost ones if ( ! $this->trans_enabled OR $this->_trans_depth > 0) { return TRUE; } return sqlsrv_rollback($this->conn_id); } // -------------------------------------------------------------------- /** * Escape String * * @param string * @param bool whether or not the string will be used in a LIKE condition * @return string */ public function escape_str($str, $like = FALSE) { // Escape single quotes return str_replace("'", "''", $str); } // -------------------------------------------------------------------- /** * Affected Rows * * @return int */ public function affected_rows() { return sqlrv_rows_affected($this->result_id); } // -------------------------------------------------------------------- /** * Insert ID * * Returns the last id created in the Identity column. * * @return string */ public function insert_id() { $query = $this->query('SELECT @@IDENTITY AS insert_id'); $query = $query->row(); return $query->insert_id; } // -------------------------------------------------------------------- /** * Database version number * * @return string */ public function version() { if (isset($this->data_cache['version'])) { return $this->data_cache['version']; } if (($info = sqlsrv_server_info($this->conn_id)) === FALSE) { return FALSE; } return $this->data_cache['version'] = $info['SQLServerVersion']; } // -------------------------------------------------------------------- /** * List table query * * Generates a platform-specific query string so that the table names can be fetched * * @param bool * @return string */ protected function _list_tables($prefix_limit = FALSE) { return "SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name"; } // -------------------------------------------------------------------- /** * List column query * * Generates a platform-specific query string so that the column names can be fetched * * @param string the table name * @return string */ protected function _list_columns($table = '') { return "SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = '".$table."'"; } // -------------------------------------------------------------------- /** * Field data query * * Generates a platform-specific query so that the column data can be retrieved * * @param string the table name * @return string */ protected function _field_data($table) { return 'SELECT TOP 1 * FROM '.$table; } // -------------------------------------------------------------------- /** * Error * * Returns an array containing code and message of the last * database error that has occured. * * @return array */ public function error() { $error = array('code' => '00000', 'message' => ''); $sqlsrv_errors = sqlsrv_errors(SQLSRV_ERR_ERRORS); if ( ! is_array($sqlsrv_errors)) { return $error; } $sqlsrv_error = array_shift($sqlsrv_errors); if (isset($sqlsrv_error['SQLSTATE'])) { $error['code'] = isset($sqlsrv_error['code']) ? $sqlsrv_error['SQLSTATE'].'/'.$sqlsrv_error['code'] : $sqlsrv_error['SQLSTATE']; } elseif (isset($sqlsrv_error['code'])) { $error['code'] = $sqlsrv_error['code']; } if (isset($sqlsrv_error['message'])) { $error['message'] = $sqlsrv_error['message']; } return $error; } // -------------------------------------------------------------------- /** * From Tables * * This function implicitly groups FROM tables so there is no confusion * about operator precedence in harmony with SQL standards * * @param array * @return string */ protected function _from_tables($tables) { return is_array($tables) ? implode(', ', $tables) : $tables; } // -------------------------------------------------------------------- /** * Update statement * * Generates a platform-specific update string from the supplied data * * @param string the table name * @param array the update data * @param array the where clause * @param array the orderby clause (ignored) * @param array the limit clause (ignored) * @param array the like clause * @return string */ protected function _update($table, $values, $where, $orderby = array(), $limit = FALSE, $like = array()) { foreach ($values as $key => $val) { $valstr[] = $key.' = '.$val; } $where = empty($where) ? '' : ' WHERE '.implode(' ', $where); if ( ! empty($like)) { $where .= ($where === '' ? ' WHERE ' : ' AND ').implode(' ', $like); } return 'UPDATE '.$table.' SET '.implode(', ', $valstr).$where; } // -------------------------------------------------------------------- /** * Truncate statement * * Generates a platform-specific truncate string from the supplied data * * If the database does not support the truncate() command, * then this method maps to 'DELETE FROM table' * * @param string the table name * @return string */ protected function _truncate($table) { return 'TRUNCATE TABLE '.$table; } // -------------------------------------------------------------------- /** * Delete statement * * Generates a platform-specific delete string from the supplied data * * @param string the table name * @param array the where clause * @param array the like clause * @param string the limit clause * @return string */ protected function _delete($table, $where = array(), $like = array(), $limit = FALSE) { $conditions = array(); empty($where) OR $conditions[] = implode(' ', $where); empty($like) OR $conditions[] = implode(' ', $like); $conditions = (count($conditions) > 0) ? ' WHERE '.implode(' AND ', $conditions) : ''; return ($limit) ? 'WITH ci_delete AS (SELECT TOP '.$limit.' * FROM '.$table.$conditions.') DELETE FROM ci_delete' : 'DELETE FROM '.$table.$conditions; } // -------------------------------------------------------------------- /** * Limit string * * Generates a platform-specific LIMIT clause * * @param string the sql query string * @param int the number of rows to limit the query to * @param int the offset value * @return string */ protected function _limit($sql, $limit, $offset) { // As of SQL Server 2012 (11.0.*) OFFSET is supported return version_compare($this->version(), '11', '>=') ? $sql.' OFFSET '.(int) $offset.' ROWS FETCH NEXT '.(int) $limit.' ROWS ONLY' : preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.($limit + $offset).' ', $sql); } // -------------------------------------------------------------------- /** * Close DB Connection * * @return void */ protected function _close() { @sqlsrv_close($this->conn_id); } } /* End of file sqlsrv_driver.php */ /* Location: ./system/database/drivers/sqlsrv/sqlsrv_driver.php */