From 84445d08e31c4d538d5a25023b8e5861804a8f14 Mon Sep 17 00:00:00 2001 From: Alex Bilbie Date: Thu, 10 Mar 2011 16:43:39 +0000 Subject: Added sqlsrv folder, added necessary files, started altering queries in sqlsrv_driver.php --- system/database/drivers/sqlsrv/sqlsrv_driver.php | 664 +++++++++++++++++++++++ 1 file changed, 664 insertions(+) create mode 100644 system/database/drivers/sqlsrv/sqlsrv_driver.php (limited to 'system/database/drivers/sqlsrv/sqlsrv_driver.php') diff --git a/system/database/drivers/sqlsrv/sqlsrv_driver.php b/system/database/drivers/sqlsrv/sqlsrv_driver.php new file mode 100644 index 000000000..d64e98108 --- /dev/null +++ b/system/database/drivers/sqlsrv/sqlsrv_driver.php @@ -0,0 +1,664 @@ +port != '') + { + $this->hostname .= ','.$this->port; + } + + return @sqlsrv_connect($this->hostname, array('UID' => $this->username, 'PWD' => $this->password, 'Database' => $this->database, 'ConnectionPooling' => 0)); + } + + // -------------------------------------------------------------------- + + /** + * Persistent database connection + * + * @access private called by the base class + * @return resource + */ + function db_pconnect() + { + if ($this->port != '') + { + $this->hostname .= ','.$this->port; + } + + return @sqlsrv_connect($this->hostname, array('UID' => $this->username, 'PWD' => $this->password, 'Database' => $this->database, 'ConnectionPooling' => 1)); + } + + // -------------------------------------------------------------------- + + /** + * Reconnect + * + * Keep / reestablish the db connection if no queries have been + * sent for a length of time exceeding the server's idle timeout + * + * @access public + * @return void + */ + function reconnect() + { + // not implemented in MSSQL + } + + // -------------------------------------------------------------------- + + /** + * Select the database + * + * @access private called by the base class + * @return resource + */ + function db_select() + { + // Note: The brackets are required in the event that the DB name + // contains reserved characters + return @mssql_select_db('['.$this->database.']', $this->conn_id); + } + + // -------------------------------------------------------------------- + + /** + * Set client character set + * + * @access public + * @param string + * @param string + * @return resource + */ + function db_set_charset($charset, $collation) + { + // @todo - add support if needed + return TRUE; + } + + // -------------------------------------------------------------------- + + /** + * Execute the query + * + * @access private called by the base class + * @param string an SQL query + * @return resource + */ + function _execute($sql) + { + $sql = $this->_prep_query($sql); + return @sqlsrv_query($this->conn_id, $sql); + } + + // -------------------------------------------------------------------- + + /** + * Prep the query + * + * If needed, each database adapter can prep the query string + * + * @access private called by execute() + * @param string an SQL query + * @return string + */ + function _prep_query($sql) + { + return $sql; + } + + // -------------------------------------------------------------------- + + /** + * Begin Transaction + * + * @access public + * @return bool + */ + function trans_begin($test_mode = FALSE) + { + if ( ! $this->trans_enabled) + { + return TRUE; + } + + // When transactions are nested we only begin/commit/rollback the outermost ones + if ($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) ? TRUE : FALSE; + + $this->simple_query('BEGIN TRAN'); + return TRUE; + } + + // -------------------------------------------------------------------- + + /** + * Commit Transaction + * + * @access public + * @return bool + */ + function trans_commit() + { + if ( ! $this->trans_enabled) + { + return TRUE; + } + + // When transactions are nested we only begin/commit/rollback the outermost ones + if ($this->_trans_depth > 0) + { + return TRUE; + } + + $this->simple_query('COMMIT TRAN'); + return TRUE; + } + + // -------------------------------------------------------------------- + + /** + * Rollback Transaction + * + * @access public + * @return bool + */ + function trans_rollback() + { + if ( ! $this->trans_enabled) + { + return TRUE; + } + + // When transactions are nested we only begin/commit/rollback the outermost ones + if ($this->_trans_depth > 0) + { + return TRUE; + } + + $this->simple_query('ROLLBACK TRAN'); + return TRUE; + } + + // -------------------------------------------------------------------- + + /** + * Escape String + * + * @access public + * @param string + * @param bool whether or not the string will be used in a LIKE condition + * @return string + */ + function escape_str($str, $like = FALSE) + { + if (is_array($str)) + { + foreach ($str as $key => $val) + { + $str[$key] = $this->escape_str($val, $like); + } + + return $str; + } + + // Escape single quotes + $str = str_replace("'", "''", remove_invisible_characters($str)); + + // escape LIKE condition wildcards + if ($like === TRUE) + { + $str = str_replace( array('%', '_', $this->_like_escape_chr), + array($this->_like_escape_chr.'%', $this->_like_escape_chr.'_', $this->_like_escape_chr.$this->_like_escape_chr), + $str); + } + + return $str; + } + + // -------------------------------------------------------------------- + + /** + * Affected Rows + * + * @access public + * @return integer + */ + function affected_rows() + { + return @mssql_rows_affected($this->conn_id); + } + + // -------------------------------------------------------------------- + + /** + * Insert ID + * + * Returns the last id created in the Identity column. + * + * @access public + * @return integer + */ + function insert_id() + { + $ver = self::_parse_major_version($this->version()); + $sql = ($ver >= 8 ? "SELECT SCOPE_IDENTITY() AS last_id" : "SELECT @@IDENTITY AS last_id"); + $query = $this->query($sql); + $row = $query->row(); + return $row->last_id; + } + + // -------------------------------------------------------------------- + + /** + * Parse major version + * + * Grabs the major version number from the + * database server version string passed in. + * + * @access private + * @param string $version + * @return int16 major version number + */ + function _parse_major_version($version) + { + preg_match('/([0-9]+)\.([0-9]+)\.([0-9]+)/', $version, $ver_info); + return $ver_info[1]; // return the major version b/c that's all we're interested in. + } + + // -------------------------------------------------------------------- + + /** + * Version number query string + * + * @access public + * @return string + */ + function _version() + { + return "SELECT @@VERSION AS ver"; + } + + // -------------------------------------------------------------------- + + /** + * "Count All" query + * + * Generates a platform-specific query string that counts all records in + * the specified database + * + * @access public + * @param string + * @return string + */ + function count_all($table = '') + { + if ($table == '') + { + return 0; + } + + $query = $this->query($this->_count_string . $this->_protect_identifiers('numrows') . " FROM " . $this->_protect_identifiers($table, TRUE, NULL, FALSE)); + + if ($query->num_rows() == 0) + { + return 0; + } + + $row = $query->row(); + return (int) $row->numrows; + } + + // -------------------------------------------------------------------- + + /** + * List table query + * + * Generates a platform-specific query string so that the table names can be fetched + * + * @access private + * @param boolean + * @return string + */ + function _list_tables($prefix_limit = FALSE) + { + $sql = "SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name"; + + // for future compatibility + if ($prefix_limit !== FALSE AND $this->dbprefix != '') + { + //$sql .= " LIKE '".$this->escape_like_str($this->dbprefix)."%' ".sprintf($this->_like_escape_str, $this->_like_escape_chr); + return FALSE; // not currently supported + } + + return $sql; + } + + // -------------------------------------------------------------------- + + /** + * List column query + * + * Generates a platform-specific query string so that the column names can be fetched + * + * @access private + * @param string the table name + * @return string + */ + 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 + * + * @access public + * @param string the table name + * @return object + */ + function _field_data($table) + { + return "SELECT TOP 1 * FROM ".$table; + } + + // -------------------------------------------------------------------- + + /** + * The error message string + * + * @access private + * @return string + */ + function _error_message() + { + return sqlsrv_errors(); + } + + // -------------------------------------------------------------------- + + /** + * The error message number + * + * @access private + * @return integer + */ + function _error_number() + { + // Are error numbers supported? + return ''; + } + + // -------------------------------------------------------------------- + + /** + * Escape the SQL Identifiers + * + * This function escapes column and table names + * + * @access private + * @param string + * @return string + */ + function _escape_identifiers($item) + { + if ($this->_escape_char == '') + { + return $item; + } + + foreach ($this->_reserved_identifiers as $id) + { + if (strpos($item, '.'.$id) !== FALSE) + { + $str = $this->_escape_char. str_replace('.', $this->_escape_char.'.', $item); + + // remove duplicates if the user already included the escape + return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str); + } + } + + if (strpos($item, '.') !== FALSE) + { + $str = $this->_escape_char.str_replace('.', $this->_escape_char.'.'.$this->_escape_char, $item).$this->_escape_char; + } + else + { + $str = $this->_escape_char.$item.$this->_escape_char; + } + + // remove duplicates if the user already included the escape + return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str); + } + + // -------------------------------------------------------------------- + + /** + * From Tables + * + * This function implicitly groups FROM tables so there is no confusion + * about operator precedence in harmony with SQL standards + * + * @access public + * @param type + * @return type + */ + function _from_tables($tables) + { + if ( ! is_array($tables)) + { + $tables = array($tables); + } + + return implode(', ', $tables); + } + + // -------------------------------------------------------------------- + + /** + * Insert statement + * + * Generates a platform-specific insert string from the supplied data + * + * @access public + * @param string the table name + * @param array the insert keys + * @param array the insert values + * @return string + */ + function _insert($table, $keys, $values) + { + return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")"; + } + + // -------------------------------------------------------------------- + + /** + * Update statement + * + * Generates a platform-specific update string from the supplied data + * + * @access public + * @param string the table name + * @param array the update data + * @param array the where clause + * @param array the orderby clause + * @param array the limit clause + * @return string + */ + function _update($table, $values, $where, $orderby = array(), $limit = FALSE) + { + foreach ($values as $key => $val) + { + $valstr[] = $key." = ".$val; + } + + $limit = ( ! $limit) ? '' : ' LIMIT '.$limit; + + $orderby = (count($orderby) >= 1)?' ORDER BY '.implode(", ", $orderby):''; + + $sql = "UPDATE ".$table." SET ".implode(', ', $valstr); + + $sql .= ($where != '' AND count($where) >=1) ? " WHERE ".implode(" ", $where) : ''; + + $sql .= $orderby.$limit; + + return $sql; + } + + + // -------------------------------------------------------------------- + + /** + * Truncate statement + * + * Generates a platform-specific truncate string from the supplied data + * If the database does not support the truncate() command + * This function maps to "DELETE FROM table" + * + * @access public + * @param string the table name + * @return string + */ + function _truncate($table) + { + return "TRUNCATE ".$table; + } + + // -------------------------------------------------------------------- + + /** + * Delete statement + * + * Generates a platform-specific delete string from the supplied data + * + * @access public + * @param string the table name + * @param array the where clause + * @param string the limit clause + * @return string + */ + function _delete($table, $where = array(), $like = array(), $limit = FALSE) + { + $conditions = ''; + + if (count($where) > 0 OR count($like) > 0) + { + $conditions = "\nWHERE "; + $conditions .= implode("\n", $this->ar_where); + + if (count($where) > 0 && count($like) > 0) + { + $conditions .= " AND "; + } + $conditions .= implode("\n", $like); + } + + $limit = ( ! $limit) ? '' : ' LIMIT '.$limit; + + return "DELETE FROM ".$table.$conditions.$limit; + } + + // -------------------------------------------------------------------- + + /** + * Limit string + * + * Generates a platform-specific LIMIT clause + * + * @access public + * @param string the sql query string + * @param integer the number of rows to limit the query to + * @param integer the offset value + * @return string + */ + function _limit($sql, $limit, $offset) + { + $i = $limit + $offset; + + return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$i.' ', $sql); + } + + // -------------------------------------------------------------------- + + /** + * Close DB Connection + * + * @access public + * @param resource + * @return void + */ + function _close($conn_id) + { + @sqlsrv_close($conn_id); + } + +} + + + +/* End of file mssql_driver.php */ +/* Location: ./system/database/drivers/mssql/mssql_driver.php */ \ No newline at end of file -- cgit v1.2.3-24-g4f1b From 079069c2fa70b142fb4313f12f1fdf3f11713ff5 Mon Sep 17 00:00:00 2001 From: Alex Bilbie Date: Thu, 10 Mar 2011 19:36:58 +0000 Subject: Think I've finished converting mssql commands to sqlsrv where appropriate --- system/database/drivers/sqlsrv/sqlsrv_driver.php | 4 +--- 1 file changed, 1 insertion(+), 3 deletions(-) (limited to 'system/database/drivers/sqlsrv/sqlsrv_driver.php') diff --git a/system/database/drivers/sqlsrv/sqlsrv_driver.php b/system/database/drivers/sqlsrv/sqlsrv_driver.php index d64e98108..53d974837 100644 --- a/system/database/drivers/sqlsrv/sqlsrv_driver.php +++ b/system/database/drivers/sqlsrv/sqlsrv_driver.php @@ -107,9 +107,7 @@ class CI_DB_mssql_driver extends CI_DB { */ function db_select() { - // Note: The brackets are required in the event that the DB name - // contains reserved characters - return @mssql_select_db('['.$this->database.']', $this->conn_id); + // not implemented in sqlsrv } // -------------------------------------------------------------------- -- cgit v1.2.3-24-g4f1b From 5336ee283c764a8f3ef9baa746324d7f6a26499f Mon Sep 17 00:00:00 2001 From: Alex Bilbie Date: Sat, 12 Mar 2011 23:35:29 +0000 Subject: Added flag to return dates as strings instead of PHP date objects. Updated db_select --- system/database/drivers/sqlsrv/sqlsrv_driver.php | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'system/database/drivers/sqlsrv/sqlsrv_driver.php') diff --git a/system/database/drivers/sqlsrv/sqlsrv_driver.php b/system/database/drivers/sqlsrv/sqlsrv_driver.php index 53d974837..25cc628df 100644 --- a/system/database/drivers/sqlsrv/sqlsrv_driver.php +++ b/system/database/drivers/sqlsrv/sqlsrv_driver.php @@ -60,7 +60,7 @@ class CI_DB_mssql_driver extends CI_DB { $this->hostname .= ','.$this->port; } - return @sqlsrv_connect($this->hostname, array('UID' => $this->username, 'PWD' => $this->password, 'Database' => $this->database, 'ConnectionPooling' => 0)); + return @sqlsrv_connect($this->hostname, array('UID' => $this->username, 'PWD' => $this->password, 'Database' => $this->database, 'ConnectionPooling' => 0, 'ReturnDatesAsStrings' => 1)); } // -------------------------------------------------------------------- @@ -78,7 +78,7 @@ class CI_DB_mssql_driver extends CI_DB { $this->hostname .= ','.$this->port; } - return @sqlsrv_connect($this->hostname, array('UID' => $this->username, 'PWD' => $this->password, 'Database' => $this->database, 'ConnectionPooling' => 1)); + return @sqlsrv_connect($this->hostname, array('UID' => $this->username, 'PWD' => $this->password, 'Database' => $this->database, 'ConnectionPooling' => 1, 'ReturnDatesAsStrings' => 1)); } // -------------------------------------------------------------------- @@ -107,7 +107,7 @@ class CI_DB_mssql_driver extends CI_DB { */ function db_select() { - // not implemented in sqlsrv + return $this->_execute('USE ' . $this->database); } // -------------------------------------------------------------------- -- cgit v1.2.3-24-g4f1b From 56e204079b8b3951abca5165ca815b75d37bf03d Mon Sep 17 00:00:00 2001 From: Alex Bilbie Date: Sat, 12 Mar 2011 23:43:54 +0000 Subject: Updated affected_rows --- system/database/drivers/sqlsrv/sqlsrv_driver.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'system/database/drivers/sqlsrv/sqlsrv_driver.php') diff --git a/system/database/drivers/sqlsrv/sqlsrv_driver.php b/system/database/drivers/sqlsrv/sqlsrv_driver.php index 25cc628df..e9c391786 100644 --- a/system/database/drivers/sqlsrv/sqlsrv_driver.php +++ b/system/database/drivers/sqlsrv/sqlsrv_driver.php @@ -283,7 +283,7 @@ class CI_DB_mssql_driver extends CI_DB { */ function affected_rows() { - return @mssql_rows_affected($this->conn_id); + return @sqlrv_rows_affected($this->conn_id); } // -------------------------------------------------------------------- -- cgit v1.2.3-24-g4f1b From 01ab00469c80a26c22c61f896f0514dfcedccb67 Mon Sep 17 00:00:00 2001 From: Alex Bilbie Date: Fri, 18 Mar 2011 19:24:30 +0000 Subject: Renamed some classes in the driver --- system/database/drivers/sqlsrv/sqlsrv_driver.php | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'system/database/drivers/sqlsrv/sqlsrv_driver.php') diff --git a/system/database/drivers/sqlsrv/sqlsrv_driver.php b/system/database/drivers/sqlsrv/sqlsrv_driver.php index e9c391786..116907123 100644 --- a/system/database/drivers/sqlsrv/sqlsrv_driver.php +++ b/system/database/drivers/sqlsrv/sqlsrv_driver.php @@ -16,7 +16,7 @@ // ------------------------------------------------------------------------ /** - * MS SQL Database Adapter Class + * SQLSRV Database Adapter Class * * Note: _DB is an extender class that the app controller * creates dynamically based on whether the active record @@ -28,7 +28,7 @@ * @author ExpressionEngine Dev Team * @link http://codeigniter.com/user_guide/database/ */ -class CI_DB_mssql_driver extends CI_DB { +class CI_DB_sqlsrv_driver extends CI_DB { var $dbdriver = 'sqlsrv'; -- cgit v1.2.3-24-g4f1b From 3a43c7adae7737d68a0eeca663cc2dd3fc5b0cf3 Mon Sep 17 00:00:00 2001 From: Alex Bilbie Date: Fri, 18 Mar 2011 19:48:04 +0000 Subject: Updates --- system/database/drivers/sqlsrv/sqlsrv_driver.php | 206 ++++++++--------------- 1 file changed, 71 insertions(+), 135 deletions(-) (limited to 'system/database/drivers/sqlsrv/sqlsrv_driver.php') diff --git a/system/database/drivers/sqlsrv/sqlsrv_driver.php b/system/database/drivers/sqlsrv/sqlsrv_driver.php index 116907123..1d32792ce 100644 --- a/system/database/drivers/sqlsrv/sqlsrv_driver.php +++ b/system/database/drivers/sqlsrv/sqlsrv_driver.php @@ -53,14 +53,27 @@ class CI_DB_sqlsrv_driver extends CI_DB { * @access private called by the base class * @return resource */ - function db_connect() + function db_connect($pooling = false) { - if ($this->port != '') - { - $this->hostname .= ','.$this->port; + // 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']); } - return @sqlsrv_connect($this->hostname, array('UID' => $this->username, 'PWD' => $this->password, 'Database' => $this->database, 'ConnectionPooling' => 0, 'ReturnDatesAsStrings' => 1)); + return sqlsrv_connect($this->hostname, $connection); } // -------------------------------------------------------------------- @@ -73,12 +86,7 @@ class CI_DB_sqlsrv_driver extends CI_DB { */ function db_pconnect() { - if ($this->port != '') - { - $this->hostname .= ','.$this->port; - } - - return @sqlsrv_connect($this->hostname, array('UID' => $this->username, 'PWD' => $this->password, 'Database' => $this->database, 'ConnectionPooling' => 1, 'ReturnDatesAsStrings' => 1)); + $this->db_connect(TRUE); } // -------------------------------------------------------------------- @@ -138,7 +146,10 @@ class CI_DB_sqlsrv_driver extends CI_DB { function _execute($sql) { $sql = $this->_prep_query($sql); - return @sqlsrv_query($this->conn_id, $sql); + return sqlsrv_query($this->conn_id, $sql, null, array( + 'Scrollable' => SQLSRV_CURSOR_STATIC, + 'SendStreamParamsAtExec' => true + )); } // -------------------------------------------------------------------- @@ -183,8 +194,7 @@ class CI_DB_sqlsrv_driver extends CI_DB { // even if the queries produce a successful result. $this->_trans_failure = ($test_mode === TRUE) ? TRUE : FALSE; - $this->simple_query('BEGIN TRAN'); - return TRUE; + return sqlsrv_begin_transaction($this->conn_id); } // -------------------------------------------------------------------- @@ -208,8 +218,7 @@ class CI_DB_sqlsrv_driver extends CI_DB { return TRUE; } - $this->simple_query('COMMIT TRAN'); - return TRUE; + return sqlsrv_commit($this->conn_id); } // -------------------------------------------------------------------- @@ -233,8 +242,7 @@ class CI_DB_sqlsrv_driver extends CI_DB { return TRUE; } - $this->simple_query('ROLLBACK TRAN'); - return TRUE; + return sqlsrv_rollback($this->conn_id); } // -------------------------------------------------------------------- @@ -249,28 +257,8 @@ class CI_DB_sqlsrv_driver extends CI_DB { */ function escape_str($str, $like = FALSE) { - if (is_array($str)) - { - foreach ($str as $key => $val) - { - $str[$key] = $this->escape_str($val, $like); - } - - return $str; - } - // Escape single quotes - $str = str_replace("'", "''", remove_invisible_characters($str)); - - // escape LIKE condition wildcards - if ($like === TRUE) - { - $str = str_replace( array('%', '_', $this->_like_escape_chr), - array($this->_like_escape_chr.'%', $this->_like_escape_chr.'_', $this->_like_escape_chr.$this->_like_escape_chr), - $str); - } - - return $str; + return str_replace("'", "''", $str); } // -------------------------------------------------------------------- @@ -298,11 +286,7 @@ class CI_DB_sqlsrv_driver extends CI_DB { */ function insert_id() { - $ver = self::_parse_major_version($this->version()); - $sql = ($ver >= 8 ? "SELECT SCOPE_IDENTITY() AS last_id" : "SELECT @@IDENTITY AS last_id"); - $query = $this->query($sql); - $row = $query->row(); - return $row->last_id; + return $this->query('select @@IDENTITY as insert_id')->row('insert_id'); } // -------------------------------------------------------------------- @@ -333,7 +317,8 @@ class CI_DB_sqlsrv_driver extends CI_DB { */ function _version() { - return "SELECT @@VERSION AS ver"; + $info = sqlsrv_server_info($this->conn_id); + return sprintf("select '%s' as ver", $info['SQLServerVersion']); } // -------------------------------------------------------------------- @@ -351,19 +336,15 @@ class CI_DB_sqlsrv_driver extends CI_DB { function count_all($table = '') { if ($table == '') - { - return 0; - } - - $query = $this->query($this->_count_string . $this->_protect_identifiers('numrows') . " FROM " . $this->_protect_identifiers($table, TRUE, NULL, FALSE)); - + return '0'; + + $query = $this->query("SELECT COUNT(*) AS numrows FROM " . $this->dbprefix . $table); + if ($query->num_rows() == 0) - { - return 0; - } + return '0'; $row = $query->row(); - return (int) $row->numrows; + return $row->numrows; } // -------------------------------------------------------------------- @@ -379,16 +360,7 @@ class CI_DB_sqlsrv_driver extends CI_DB { */ function _list_tables($prefix_limit = FALSE) { - $sql = "SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name"; - - // for future compatibility - if ($prefix_limit !== FALSE AND $this->dbprefix != '') - { - //$sql .= " LIKE '".$this->escape_like_str($this->dbprefix)."%' ".sprintf($this->_like_escape_str, $this->_like_escape_chr); - return FALSE; // not currently supported - } - - return $sql; + return "SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name"; } // -------------------------------------------------------------------- @@ -404,7 +376,7 @@ class CI_DB_sqlsrv_driver extends CI_DB { */ function _list_columns($table = '') { - return "SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = '".$table."'"; + return "SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = '".$this->_escape_table($table)."'"; } // -------------------------------------------------------------------- @@ -420,7 +392,7 @@ class CI_DB_sqlsrv_driver extends CI_DB { */ function _field_data($table) { - return "SELECT TOP 1 * FROM ".$table; + return "SELECT TOP 1 * FROM " . $this->_escape_table($table); } // -------------------------------------------------------------------- @@ -433,7 +405,8 @@ class CI_DB_sqlsrv_driver extends CI_DB { */ function _error_message() { - return sqlsrv_errors(); + $error = array_shift(sqlsrv_errors()); + return !empty($error['message']) ? $error['message'] : null; } // -------------------------------------------------------------------- @@ -446,12 +419,28 @@ class CI_DB_sqlsrv_driver extends CI_DB { */ function _error_number() { - // Are error numbers supported? - return ''; + $error = array_shift(sqlsrv_errors()); + return isset($error['SQLSTATE']) ? $error['SQLSTATE'] : null; } // -------------------------------------------------------------------- + /** + * Escape Table Name + * + * This function adds backticks if the table name has a period + * in it. Some DBs will get cranky unless periods are escaped + * + * @access private + * @param string the table name + * @return string + */ + function _escape_table($table) + { + return $table; + } + + /** * Escape the SQL Identifiers * @@ -463,33 +452,7 @@ class CI_DB_sqlsrv_driver extends CI_DB { */ function _escape_identifiers($item) { - if ($this->_escape_char == '') - { - return $item; - } - - foreach ($this->_reserved_identifiers as $id) - { - if (strpos($item, '.'.$id) !== FALSE) - { - $str = $this->_escape_char. str_replace('.', $this->_escape_char.'.', $item); - - // remove duplicates if the user already included the escape - return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str); - } - } - - if (strpos($item, '.') !== FALSE) - { - $str = $this->_escape_char.str_replace('.', $this->_escape_char.'.'.$this->_escape_char, $item).$this->_escape_char; - } - else - { - $str = $this->_escape_char.$item.$this->_escape_char; - } - - // remove duplicates if the user already included the escape - return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str); + return $item; } // -------------------------------------------------------------------- @@ -528,8 +491,8 @@ class CI_DB_sqlsrv_driver extends CI_DB { * @return string */ function _insert($table, $keys, $values) - { - return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")"; + { + return "INSERT INTO ".$this->_escape_table($table)." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")"; } // -------------------------------------------------------------------- @@ -547,27 +510,16 @@ class CI_DB_sqlsrv_driver extends CI_DB { * @param array the limit clause * @return string */ - function _update($table, $values, $where, $orderby = array(), $limit = FALSE) + function _update($table, $values, $where) { - foreach ($values as $key => $val) + foreach($values as $key => $val) { $valstr[] = $key." = ".$val; } - - $limit = ( ! $limit) ? '' : ' LIMIT '.$limit; - - $orderby = (count($orderby) >= 1)?' ORDER BY '.implode(", ", $orderby):''; - - $sql = "UPDATE ".$table." SET ".implode(', ', $valstr); - - $sql .= ($where != '' AND count($where) >=1) ? " WHERE ".implode(" ", $where) : ''; - - $sql .= $orderby.$limit; - - return $sql; + + return "UPDATE ".$this->_escape_table($table)." SET ".implode(', ', $valstr)." WHERE ".implode(" ", $where); } - - + // -------------------------------------------------------------------- /** @@ -599,25 +551,9 @@ class CI_DB_sqlsrv_driver extends CI_DB { * @param string the limit clause * @return string */ - function _delete($table, $where = array(), $like = array(), $limit = FALSE) + function _delete($table, $where) { - $conditions = ''; - - if (count($where) > 0 OR count($like) > 0) - { - $conditions = "\nWHERE "; - $conditions .= implode("\n", $this->ar_where); - - if (count($where) > 0 && count($like) > 0) - { - $conditions .= " AND "; - } - $conditions .= implode("\n", $like); - } - - $limit = ( ! $limit) ? '' : ' LIMIT '.$limit; - - return "DELETE FROM ".$table.$conditions.$limit; + return "DELETE FROM ".$this->_escape_table($table)." WHERE ".implode(" ", $where); } // -------------------------------------------------------------------- @@ -636,8 +572,8 @@ class CI_DB_sqlsrv_driver extends CI_DB { function _limit($sql, $limit, $offset) { $i = $limit + $offset; - - return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$i.' ', $sql); + + return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$i.' ', $sql); } // -------------------------------------------------------------------- -- cgit v1.2.3-24-g4f1b