blob: 1e182958cbeb0bc7f44b5ea48b7a5702a2407de8 [file] [log] [blame]
Andrey Andreevc5536aa2012-11-01 17:33:58 +02001<?php
Alex Bilbie84445d02011-03-10 16:43:39 +00002/**
3 * CodeIgniter
4 *
Phil Sturgeon07c1ac82012-03-09 17:03:37 +00005 * An open source application development framework for PHP 5.2.4 or newer
Alex Bilbie84445d02011-03-10 16:43:39 +00006 *
Derek Jonesf4a4bd82011-10-20 12:18:42 -05007 * NOTICE OF LICENSE
Andrey Andreev0e8968a2012-01-26 02:04:37 +02008 *
Derek Jonesf4a4bd82011-10-20 12:18:42 -05009 * Licensed under the Open Software License version 3.0
Andrey Andreev0e8968a2012-01-26 02:04:37 +020010 *
Derek Jonesf4a4bd82011-10-20 12:18:42 -050011 * This source file is subject to the Open Software License (OSL 3.0) that is
12 * bundled with this package in the files license.txt / license.rst. It is
13 * also available through the world wide web at this URL:
14 * http://opensource.org/licenses/OSL-3.0
15 * If you did not receive a copy of the license and are unable to obtain it
16 * through the world wide web, please send an email to
17 * licensing@ellislab.com so we can send you a copy immediately.
18 *
Alex Bilbie84445d02011-03-10 16:43:39 +000019 * @package CodeIgniter
Derek Jonesf4a4bd82011-10-20 12:18:42 -050020 * @author EllisLab Dev Team
Greg Aker0defe5d2012-01-01 18:46:41 -060021 * @copyright Copyright (c) 2008 - 2012, EllisLab, Inc. (http://ellislab.com/)
Derek Jonesf4a4bd82011-10-20 12:18:42 -050022 * @license http://opensource.org/licenses/OSL-3.0 Open Software License (OSL 3.0)
Alex Bilbie84445d02011-03-10 16:43:39 +000023 * @link http://codeigniter.com
Andrey Andreevbf940582012-06-10 07:05:05 +030024 * @since Version 2.0.3
Alex Bilbie84445d02011-03-10 16:43:39 +000025 * @filesource
26 */
Andrey Andreevc5536aa2012-11-01 17:33:58 +020027defined('BASEPATH') OR exit('No direct script access allowed');
Alex Bilbie84445d02011-03-10 16:43:39 +000028
Alex Bilbie84445d02011-03-10 16:43:39 +000029/**
Alex Bilbie01ab0042011-03-18 19:24:30 +000030 * SQLSRV Database Adapter Class
Alex Bilbie84445d02011-03-10 16:43:39 +000031 *
32 * Note: _DB is an extender class that the app controller
Jamie Rumbelow7efad202012-02-19 12:37:00 +000033 * creates dynamically based on whether the query builder
Alex Bilbie84445d02011-03-10 16:43:39 +000034 * class is being used or not.
35 *
36 * @package CodeIgniter
37 * @subpackage Drivers
38 * @category Database
Derek Jonesf4a4bd82011-10-20 12:18:42 -050039 * @author EllisLab Dev Team
Alex Bilbie84445d02011-03-10 16:43:39 +000040 * @link http://codeigniter.com/user_guide/database/
41 */
Alex Bilbie01ab0042011-03-18 19:24:30 +000042class CI_DB_sqlsrv_driver extends CI_DB {
Alex Bilbie84445d02011-03-10 16:43:39 +000043
Andrey Andreeva24e52e2012-11-02 03:54:12 +020044 /**
45 * Database driver
46 *
47 * @var string
48 */
Andrey Andreev0e8968a2012-01-26 02:04:37 +020049 public $dbdriver = 'sqlsrv';
Alex Bilbie84445d02011-03-10 16:43:39 +000050
Andrey Andreeva24e52e2012-11-02 03:54:12 +020051 // --------------------------------------------------------------------
Andrey Andreev082ee2b2012-06-08 15:26:34 +030052
Alex Bilbie84445d02011-03-10 16:43:39 +000053 /**
Andrey Andreeva24e52e2012-11-02 03:54:12 +020054 * ORDER BY random keyword
Alex Bilbie84445d02011-03-10 16:43:39 +000055 *
Andrey Andreev98e46cf2012-11-13 03:01:42 +020056 * @var array
Andrey Andreeva24e52e2012-11-02 03:54:12 +020057 */
Andrey Andreev98e46cf2012-11-13 03:01:42 +020058 protected $_random_keyword = array('NEWID()', 'RAND(%d)');
Andrey Andreeva24e52e2012-11-02 03:54:12 +020059
60 /**
61 * Quoted identifier flag
62 *
63 * Whether to use SQL-92 standard quoted identifier
64 * (double quotes) or brackets for identifier escaping.
65 *
66 * @var bool
67 */
68 protected $_quoted_identifier = TRUE;
69
70 // --------------------------------------------------------------------
71
72 /**
73 * Database connection
74 *
75 * @param bool $pooling
Alex Bilbie84445d02011-03-10 16:43:39 +000076 * @return resource
77 */
Andrey Andreeve6297342012-03-20 16:25:07 +020078 public function db_connect($pooling = FALSE)
Alex Bilbie84445d02011-03-10 16:43:39 +000079 {
Andrey Andreev2f8bf9b2012-10-12 20:37:52 +030080 $charset = in_array(strtolower($this->char_set), array('utf-8', 'utf8'), TRUE)
81 ? 'UTF-8' : SQLSRV_ENC_CHAR;
Alex Bilbie3a43c7a2011-03-18 19:48:04 +000082
83 $connection = array(
Andrey Andreev0e8968a2012-01-26 02:04:37 +020084 'UID' => empty($this->username) ? '' : $this->username,
85 'PWD' => empty($this->password) ? '' : $this->password,
86 'Database' => $this->database,
Andrey Andreev2f8bf9b2012-10-12 20:37:52 +030087 'ConnectionPooling' => ($pooling === TRUE) ? 1 : 0,
88 'CharacterSet' => $charset,
89 'Encrypt' => ($this->encrypt === TRUE) ? 1 : 0,
Andrey Andreev0e8968a2012-01-26 02:04:37 +020090 'ReturnDatesAsStrings' => 1
Alex Bilbie3a43c7a2011-03-18 19:48:04 +000091 );
Andrey Andreev0e8968a2012-01-26 02:04:37 +020092
93 // If the username and password are both empty, assume this is a
Alex Bilbie3a43c7a2011-03-18 19:48:04 +000094 // 'Windows Authentication Mode' connection.
Andrey Andreev0e8968a2012-01-26 02:04:37 +020095 if (empty($connection['UID']) && empty($connection['PWD']))
96 {
Alex Bilbie3a43c7a2011-03-18 19:48:04 +000097 unset($connection['UID'], $connection['PWD']);
Alex Bilbie84445d02011-03-10 16:43:39 +000098 }
99
Andrey Andreevfac37612012-07-02 14:56:20 +0300100 $this->conn_id = sqlsrv_connect($this->hostname, $connection);
Andrey Andreev082ee2b2012-06-08 15:26:34 +0300101
102 // Determine how identifiers are escaped
103 $query = $this->query('SELECT CASE WHEN (@@OPTIONS | 256) = @@OPTIONS THEN 1 ELSE 0 END AS qi');
104 $query = $query->row_array();
Andrey Andreev70c72c92012-06-25 00:04:51 +0300105 $this->_quoted_identifier = empty($query) ? FALSE : (bool) $query['qi'];
Andrey Andreev082ee2b2012-06-08 15:26:34 +0300106 $this->_escape_char = ($this->_quoted_identifier) ? '"' : array('[', ']');
107
Andrey Andreevfac37612012-07-02 14:56:20 +0300108 return $this->conn_id;
Alex Bilbie84445d02011-03-10 16:43:39 +0000109 }
110
111 // --------------------------------------------------------------------
112
113 /**
114 * Persistent database connection
115 *
Alex Bilbie84445d02011-03-10 16:43:39 +0000116 * @return resource
117 */
Andrey Andreev0e8968a2012-01-26 02:04:37 +0200118 public function db_pconnect()
Alex Bilbie84445d02011-03-10 16:43:39 +0000119 {
Kyle Farris37e351f2011-09-07 11:14:46 -0300120 return $this->db_connect(TRUE);
Alex Bilbie84445d02011-03-10 16:43:39 +0000121 }
122
123 // --------------------------------------------------------------------
124
125 /**
Alex Bilbie84445d02011-03-10 16:43:39 +0000126 * Select the database
127 *
Andrey Andreeva24e52e2012-11-02 03:54:12 +0200128 * @param string $database
Andrey Andreev11454e02012-02-22 16:05:47 +0200129 * @return bool
Alex Bilbie84445d02011-03-10 16:43:39 +0000130 */
Andrey Andreev11454e02012-02-22 16:05:47 +0200131 public function db_select($database = '')
Alex Bilbie84445d02011-03-10 16:43:39 +0000132 {
Andrey Andreev024ba2d2012-02-24 11:40:36 +0200133 if ($database === '')
134 {
135 $database = $this->database;
136 }
137
138 if ($this->_execute('USE '.$database))
139 {
140 $this->database = $database;
141 return TRUE;
142 }
143
144 return FALSE;
Alex Bilbie84445d02011-03-10 16:43:39 +0000145 }
146
147 // --------------------------------------------------------------------
148
149 /**
Alex Bilbie84445d02011-03-10 16:43:39 +0000150 * Execute the query
151 *
Andrey Andreeva24e52e2012-11-02 03:54:12 +0200152 * @param string $sql an SQL query
Alex Bilbie84445d02011-03-10 16:43:39 +0000153 * @return resource
154 */
Andrey Andreev0e8968a2012-01-26 02:04:37 +0200155 protected function _execute($sql)
Alex Bilbie84445d02011-03-10 16:43:39 +0000156 {
Andrey Andreevb2f60332012-07-03 14:45:00 +0300157 return ($this->is_write_type($sql) && stripos($sql, 'INSERT') === FALSE)
Andrey Andreev846acc72012-05-24 23:27:46 +0300158 ? sqlsrv_query($this->conn_id, $sql)
159 : sqlsrv_query($this->conn_id, $sql, NULL, array('Scrollable' => SQLSRV_CURSOR_STATIC));
Alex Bilbie84445d02011-03-10 16:43:39 +0000160 }
161
162 // --------------------------------------------------------------------
163
164 /**
165 * Begin Transaction
166 *
Andrey Andreeva24e52e2012-11-02 03:54:12 +0200167 * @param bool $test_mode
Alex Bilbie84445d02011-03-10 16:43:39 +0000168 * @return bool
169 */
Andrey Andreev0e8968a2012-01-26 02:04:37 +0200170 public function trans_begin($test_mode = FALSE)
Alex Bilbie84445d02011-03-10 16:43:39 +0000171 {
Alex Bilbie84445d02011-03-10 16:43:39 +0000172 // When transactions are nested we only begin/commit/rollback the outermost ones
Andrey Andreev0e8968a2012-01-26 02:04:37 +0200173 if ( ! $this->trans_enabled OR $this->_trans_depth > 0)
Alex Bilbie84445d02011-03-10 16:43:39 +0000174 {
175 return TRUE;
176 }
177
178 // Reset the transaction failure flag.
179 // If the $test_mode flag is set to TRUE transactions will be rolled back
180 // even if the queries produce a successful result.
Andrey Andreev0e8968a2012-01-26 02:04:37 +0200181 $this->_trans_failure = ($test_mode === TRUE);
Alex Bilbie84445d02011-03-10 16:43:39 +0000182
Alex Bilbie3a43c7a2011-03-18 19:48:04 +0000183 return sqlsrv_begin_transaction($this->conn_id);
Alex Bilbie84445d02011-03-10 16:43:39 +0000184 }
185
186 // --------------------------------------------------------------------
187
188 /**
189 * Commit Transaction
190 *
Alex Bilbie84445d02011-03-10 16:43:39 +0000191 * @return bool
192 */
Andrey Andreev0e8968a2012-01-26 02:04:37 +0200193 public function trans_commit()
Alex Bilbie84445d02011-03-10 16:43:39 +0000194 {
Alex Bilbie84445d02011-03-10 16:43:39 +0000195 // When transactions are nested we only begin/commit/rollback the outermost ones
Andrey Andreev0e8968a2012-01-26 02:04:37 +0200196 if ( ! $this->trans_enabled OR $this->_trans_depth > 0)
Alex Bilbie84445d02011-03-10 16:43:39 +0000197 {
198 return TRUE;
199 }
200
Alex Bilbie3a43c7a2011-03-18 19:48:04 +0000201 return sqlsrv_commit($this->conn_id);
Alex Bilbie84445d02011-03-10 16:43:39 +0000202 }
203
204 // --------------------------------------------------------------------
205
206 /**
207 * Rollback Transaction
208 *
Alex Bilbie84445d02011-03-10 16:43:39 +0000209 * @return bool
210 */
Andrey Andreev0e8968a2012-01-26 02:04:37 +0200211 public function trans_rollback()
Alex Bilbie84445d02011-03-10 16:43:39 +0000212 {
Alex Bilbie84445d02011-03-10 16:43:39 +0000213 // When transactions are nested we only begin/commit/rollback the outermost ones
Andrey Andreev0e8968a2012-01-26 02:04:37 +0200214 if ( ! $this->trans_enabled OR $this->_trans_depth > 0)
Alex Bilbie84445d02011-03-10 16:43:39 +0000215 {
216 return TRUE;
217 }
218
Alex Bilbie3a43c7a2011-03-18 19:48:04 +0000219 return sqlsrv_rollback($this->conn_id);
Alex Bilbie84445d02011-03-10 16:43:39 +0000220 }
221
222 // --------------------------------------------------------------------
223
224 /**
225 * Escape String
226 *
Andrey Andreeva24e52e2012-11-02 03:54:12 +0200227 * @param string $str
228 * @param bool $like Whether or not the string will be used in a LIKE condition
Alex Bilbie84445d02011-03-10 16:43:39 +0000229 * @return string
230 */
Andrey Andreev0e8968a2012-01-26 02:04:37 +0200231 public function escape_str($str, $like = FALSE)
Alex Bilbie84445d02011-03-10 16:43:39 +0000232 {
Alex Bilbie84445d02011-03-10 16:43:39 +0000233 // Escape single quotes
Alex Bilbie3a43c7a2011-03-18 19:48:04 +0000234 return str_replace("'", "''", $str);
Alex Bilbie84445d02011-03-10 16:43:39 +0000235 }
236
237 // --------------------------------------------------------------------
238
239 /**
240 * Affected Rows
241 *
Andrey Andreev0e8968a2012-01-26 02:04:37 +0200242 * @return int
Alex Bilbie84445d02011-03-10 16:43:39 +0000243 */
Andrey Andreev0e8968a2012-01-26 02:04:37 +0200244 public function affected_rows()
Alex Bilbie84445d02011-03-10 16:43:39 +0000245 {
Andrey Andreevede49ba2012-07-23 16:06:36 +0300246 return sqlsrv_rows_affected($this->result_id);
Alex Bilbie84445d02011-03-10 16:43:39 +0000247 }
248
249 // --------------------------------------------------------------------
250
251 /**
Andrey Andreeve6297342012-03-20 16:25:07 +0200252 * Insert ID
253 *
254 * Returns the last id created in the Identity column.
255 *
256 * @return string
257 */
Andrey Andreev0e8968a2012-01-26 02:04:37 +0200258 public function insert_id()
Alex Bilbie84445d02011-03-10 16:43:39 +0000259 {
Andrey Andreev0e8968a2012-01-26 02:04:37 +0200260 $query = $this->query('SELECT @@IDENTITY AS insert_id');
261 $query = $query->row();
262 return $query->insert_id;
Alex Bilbie84445d02011-03-10 16:43:39 +0000263 }
264
265 // --------------------------------------------------------------------
266
267 /**
Andrey Andreev08856b82012-03-03 03:19:28 +0200268 * Database version number
269 *
270 * @return string
271 */
272 public function version()
Alex Bilbie84445d02011-03-10 16:43:39 +0000273 {
Andrey Andreev08856b82012-03-03 03:19:28 +0200274 if (isset($this->data_cache['version']))
275 {
276 return $this->data_cache['version'];
277 }
Andrey Andreev2b730372012-11-05 17:01:11 +0200278 elseif ( ! $this->conn_id)
279 {
280 $this->initialize();
281 }
Andrey Andreev08856b82012-03-03 03:19:28 +0200282
Andrey Andreev2b730372012-11-05 17:01:11 +0200283 if ( ! $this->conn_id OR ($info = sqlsrv_server_info($this->conn_id)) === FALSE)
Andrey Andreev08856b82012-03-03 03:19:28 +0200284 {
285 return FALSE;
286 }
287
288 return $this->data_cache['version'] = $info['SQLServerVersion'];
Alex Bilbie84445d02011-03-10 16:43:39 +0000289 }
290
291 // --------------------------------------------------------------------
292
293 /**
Alex Bilbie84445d02011-03-10 16:43:39 +0000294 * List table query
295 *
296 * Generates a platform-specific query string so that the table names can be fetched
297 *
Andrey Andreev0e8968a2012-01-26 02:04:37 +0200298 * @param bool
Andrey Andreeva24e52e2012-11-02 03:54:12 +0200299 * @return string $prefix_limit
Alex Bilbie84445d02011-03-10 16:43:39 +0000300 */
Andrey Andreev0e8968a2012-01-26 02:04:37 +0200301 protected function _list_tables($prefix_limit = FALSE)
Alex Bilbie84445d02011-03-10 16:43:39 +0000302 {
Andrey Andreev70c72c92012-06-25 00:04:51 +0300303 $sql = 'SELECT '.$this->escape_identifiers('name')
304 .' FROM '.$this->escape_identifiers('sysobjects')
305 .' WHERE '.$this->escape_identifiers('type')." = 'U'";
306
307 if ($prefix_limit === TRUE && $this->dbprefix !== '')
308 {
309 $sql .= ' AND '.$this->escape_identifiers('name')." LIKE '".$this->escape_like_str($this->dbprefix)."%' "
310 .sprintf($this->_escape_like_str, $this->_escape_like_chr);
311 }
312
313 return $sql.' ORDER BY '.$this->escape_identifiers('name');
Alex Bilbie84445d02011-03-10 16:43:39 +0000314 }
315
316 // --------------------------------------------------------------------
317
318 /**
319 * List column query
320 *
321 * Generates a platform-specific query string so that the column names can be fetched
322 *
Andrey Andreeva24e52e2012-11-02 03:54:12 +0200323 * @param string $table
Alex Bilbie84445d02011-03-10 16:43:39 +0000324 * @return string
325 */
Andrey Andreev0e8968a2012-01-26 02:04:37 +0200326 protected function _list_columns($table = '')
Alex Bilbie84445d02011-03-10 16:43:39 +0000327 {
Andrey Andreevf1e1b772012-11-16 01:27:00 +0200328 return "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = '".$table."'";
Alex Bilbie84445d02011-03-10 16:43:39 +0000329 }
330
331 // --------------------------------------------------------------------
332
333 /**
Andrey Andreevf1e1b772012-11-16 01:27:00 +0200334 * Returns an object with field data
Alex Bilbie84445d02011-03-10 16:43:39 +0000335 *
Andrey Andreeva24e52e2012-11-02 03:54:12 +0200336 * @param string $table
Andrey Andreevf1e1b772012-11-16 01:27:00 +0200337 * @return array
Alex Bilbie84445d02011-03-10 16:43:39 +0000338 */
Andrey Andreevf1e1b772012-11-16 01:27:00 +0200339 public function field_data($table = '')
Alex Bilbie84445d02011-03-10 16:43:39 +0000340 {
Andrey Andreevf1e1b772012-11-16 01:27:00 +0200341 if ($table === '')
342 {
343 return ($this->db_debug) ? $this->display_error('db_field_param_missing') : FALSE;
344 }
345
346 $sql = 'SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, COLUMN_DEFAULT
347 FROM INFORMATION_SCHEMA.Columns
348 WHERE TABLE_NAME = '.$this->escape($table);
349
350 if (($query = $this->query($sql)) === FALSE)
351 {
352 return FALSE;
353 }
354 $query = $query->result_object();
355
356 $retval = array();
357 for ($i = 0, $c = count($query); $i < $c; $i++)
358 {
359 $retval[$i] = new stdClass();
360 $retval[$i]->name = $query[$i]->COLUMN_NAME;
361 $retval[$i]->type = $query[$i]->DATA_TYPE;
362 $retval[$i]->max_length = ($query[$i]->CHARACTER_MAXIMUM_LENGTH > 0) ? $query[$i]->NUMERIC_PRECISION : $query[$i]->CHARACTER_MAXIMUM_LENGTH;
363 $retval[$i]->default = $query[$i]->COLUMN_DEFAULT;
364 }
365
366 return $retval;
Alex Bilbie84445d02011-03-10 16:43:39 +0000367 }
368
369 // --------------------------------------------------------------------
370
371 /**
Andrey Andreev4be5de12012-03-02 15:45:41 +0200372 * Error
Alex Bilbie84445d02011-03-10 16:43:39 +0000373 *
Andrey Andreev4be5de12012-03-02 15:45:41 +0200374 * Returns an array containing code and message of the last
375 * database error that has occured.
Alex Bilbie84445d02011-03-10 16:43:39 +0000376 *
Andrey Andreev4be5de12012-03-02 15:45:41 +0200377 * @return array
Alex Bilbie84445d02011-03-10 16:43:39 +0000378 */
Andrey Andreev4be5de12012-03-02 15:45:41 +0200379 public function error()
Alex Bilbie84445d02011-03-10 16:43:39 +0000380 {
Andrey Andreev4be5de12012-03-02 15:45:41 +0200381 $error = array('code' => '00000', 'message' => '');
382 $sqlsrv_errors = sqlsrv_errors(SQLSRV_ERR_ERRORS);
383
384 if ( ! is_array($sqlsrv_errors))
Andrey Andreev0e8968a2012-01-26 02:04:37 +0200385 {
Andrey Andreev4be5de12012-03-02 15:45:41 +0200386 return $error;
Andrey Andreev0e8968a2012-01-26 02:04:37 +0200387 }
388
Andrey Andreev4be5de12012-03-02 15:45:41 +0200389 $sqlsrv_error = array_shift($sqlsrv_errors);
390 if (isset($sqlsrv_error['SQLSTATE']))
391 {
392 $error['code'] = isset($sqlsrv_error['code']) ? $sqlsrv_error['SQLSTATE'].'/'.$sqlsrv_error['code'] : $sqlsrv_error['SQLSTATE'];
393 }
394 elseif (isset($sqlsrv_error['code']))
395 {
396 $error['code'] = $sqlsrv_error['code'];
397 }
398
399 if (isset($sqlsrv_error['message']))
400 {
401 $error['message'] = $sqlsrv_error['message'];
402 }
403
404 return $error;
Alex Bilbie84445d02011-03-10 16:43:39 +0000405 }
406
407 // --------------------------------------------------------------------
408
409 /**
Alex Bilbie84445d02011-03-10 16:43:39 +0000410 * Update statement
411 *
412 * Generates a platform-specific update string from the supplied data
413 *
Andrey Andreeva24e52e2012-11-02 03:54:12 +0200414 * @param string $table
415 * @param array $values
Alex Bilbie84445d02011-03-10 16:43:39 +0000416 * @return string
417 */
Andrey Andreevb0478652012-07-18 15:34:46 +0300418 protected function _update($table, $values)
Alex Bilbie84445d02011-03-10 16:43:39 +0000419 {
Andrey Andreevb0478652012-07-18 15:34:46 +0300420 $this->qb_limit = FALSE;
421 $this->qb_orderby = array();
422 return parent::_update($table, $values);
Alex Bilbie84445d02011-03-10 16:43:39 +0000423 }
Andrey Andreev0e8968a2012-01-26 02:04:37 +0200424
Alex Bilbie84445d02011-03-10 16:43:39 +0000425 // --------------------------------------------------------------------
426
427 /**
428 * Truncate statement
429 *
430 * Generates a platform-specific truncate string from the supplied data
Andrey Andreev6d83cde2012-04-05 16:20:50 +0300431 *
Andrey Andreeva24e52e2012-11-02 03:54:12 +0200432 * If the database does not support the TRUNCATE statement,
Andrey Andreev6d83cde2012-04-05 16:20:50 +0300433 * then this method maps to 'DELETE FROM table'
Alex Bilbie84445d02011-03-10 16:43:39 +0000434 *
Andrey Andreeva24e52e2012-11-02 03:54:12 +0200435 * @param string $table
Alex Bilbie84445d02011-03-10 16:43:39 +0000436 * @return string
437 */
Andrey Andreev0e8968a2012-01-26 02:04:37 +0200438 protected function _truncate($table)
Alex Bilbie84445d02011-03-10 16:43:39 +0000439 {
Andrey Andreev6d83cde2012-04-05 16:20:50 +0300440 return 'TRUNCATE TABLE '.$table;
Alex Bilbie84445d02011-03-10 16:43:39 +0000441 }
442
443 // --------------------------------------------------------------------
444
445 /**
446 * Delete statement
447 *
448 * Generates a platform-specific delete string from the supplied data
449 *
Andrey Andreeva24e52e2012-11-02 03:54:12 +0200450 * @param string $table
Alex Bilbie84445d02011-03-10 16:43:39 +0000451 * @return string
452 */
Andrey Andreevb0478652012-07-18 15:34:46 +0300453 protected function _delete($table)
Alex Bilbie84445d02011-03-10 16:43:39 +0000454 {
Andrey Andreevb0478652012-07-18 15:34:46 +0300455 if ($this->qb_limit)
456 {
Andrey Andreevc9b924c2012-07-19 13:06:02 +0300457 return 'WITH ci_delete AS (SELECT TOP '.$this->qb_limit.' * FROM '.$table.$this->_compile_wh('qb_where').') DELETE FROM ci_delete';
Andrey Andreevb0478652012-07-18 15:34:46 +0300458 }
Andrey Andreev5c0e9fe2012-04-09 12:28:11 +0300459
Andrey Andreevb0478652012-07-18 15:34:46 +0300460 return parent::_delete($table);
Alex Bilbie84445d02011-03-10 16:43:39 +0000461 }
462
463 // --------------------------------------------------------------------
464
465 /**
Andrey Andreeva24e52e2012-11-02 03:54:12 +0200466 * LIMIT
Alex Bilbie84445d02011-03-10 16:43:39 +0000467 *
468 * Generates a platform-specific LIMIT clause
469 *
Andrey Andreeva24e52e2012-11-02 03:54:12 +0200470 * @param string $sql SQL Query
Alex Bilbie84445d02011-03-10 16:43:39 +0000471 * @return string
472 */
Andrey Andreevc9b924c2012-07-19 13:06:02 +0300473 protected function _limit($sql)
Alex Bilbie84445d02011-03-10 16:43:39 +0000474 {
Andrey Andreevd25c5892012-06-08 16:23:01 +0300475 // As of SQL Server 2012 (11.0.*) OFFSET is supported
Andrey Andreev71379ca2012-06-11 16:12:43 +0300476 if (version_compare($this->version(), '11', '>='))
477 {
Andrey Andreevc9b924c2012-07-19 13:06:02 +0300478 return $sql.' OFFSET '.(int) $this->qb_offset.' ROWS FETCH NEXT '.$this->qb_limit.' ROWS ONLY';
Andrey Andreev71379ca2012-06-11 16:12:43 +0300479 }
480
Andrey Andreevc9b924c2012-07-19 13:06:02 +0300481 $limit = $this->qb_offset + $this->qb_limit;
Andrey Andreev71379ca2012-06-11 16:12:43 +0300482
483 // An ORDER BY clause is required for ROW_NUMBER() to work
Andrey Andreevc9b924c2012-07-19 13:06:02 +0300484 if ($this->qb_offset && ! empty($this->qb_orderby))
Andrey Andreev71379ca2012-06-11 16:12:43 +0300485 {
Andrey Andreev2d486232012-07-19 14:46:51 +0300486 $orderby = $this->_compile_order_by();
Andrey Andreev71379ca2012-06-11 16:12:43 +0300487
488 // We have to strip the ORDER BY clause
Andrey Andreev2d486232012-07-19 14:46:51 +0300489 $sql = trim(substr($sql, 0, strrpos($sql, $orderby)));
Andrey Andreev71379ca2012-06-11 16:12:43 +0300490
Andrey Andreev44514542012-10-23 15:35:09 +0300491 // Get the fields to select from our subquery, so that we can avoid CI_rownum appearing in the actual results
492 if (count($this->qb_select) === 0)
493 {
494 $select = '*'; // Inevitable
495 }
496 else
497 {
498 // Use only field names and their aliases, everything else is out of our scope.
499 $select = array();
500 $field_regexp = ($this->_quoted_identifier)
501 ? '("[^\"]+")' : '(\[[^\]]+\])';
502 for ($i = 0, $c = count($this->qb_select); $i < $c; $i++)
503 {
504 $select[] = preg_match('/(?:\s|\.)'.$field_regexp.'$/i', $this->qb_select[$i], $m)
505 ? $m[1] : $this->qb_select[$i];
506 }
507 $select = implode(', ', $select);
508 }
509
510 return 'SELECT '.$select." FROM (\n\n"
Andrey Andreev2d486232012-07-19 14:46:51 +0300511 .preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER('.trim($orderby).') AS '.$this->escape_identifiers('CI_rownum').', ', $sql)
Andrey Andreev44514542012-10-23 15:35:09 +0300512 ."\n\n) ".$this->escape_identifiers('CI_subquery')
Andrey Andreevc9b924c2012-07-19 13:06:02 +0300513 ."\nWHERE ".$this->escape_identifiers('CI_rownum').' BETWEEN '.($this->qb_offset + 1).' AND '.$limit;
Andrey Andreev71379ca2012-06-11 16:12:43 +0300514 }
515
516 return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$limit.' ', $sql);
Alex Bilbie84445d02011-03-10 16:43:39 +0000517 }
518
519 // --------------------------------------------------------------------
520
521 /**
Andrey Andreev083e3c82012-11-06 12:48:32 +0200522 * Insert batch statement
523 *
524 * Generates a platform-specific insert string from the supplied data.
525 *
526 * @param string $table Table name
527 * @param array $keys INSERT keys
528 * @param array $values INSERT values
529 * @return string|bool
530 */
531 protected function _insert_batch($table, $keys, $values)
532 {
533 // Multiple-value inserts are only supported as of SQL Server 2008
534 if (version_compare($this->version(), '10', '>='))
535 {
536 return parent::_insert_batch($table, $keys, $values);
537 }
538
Andrey Andreev8d3afde2012-11-06 12:53:47 +0200539 return ($this->db->db_debug) ? $this->db->display_error('db_unsupported_feature') : FALSE;
Andrey Andreev083e3c82012-11-06 12:48:32 +0200540 }
541
542 // --------------------------------------------------------------------
543
544 /**
Alex Bilbie84445d02011-03-10 16:43:39 +0000545 * Close DB Connection
546 *
Alex Bilbie84445d02011-03-10 16:43:39 +0000547 * @return void
548 */
Andrey Andreev79922c02012-05-23 12:27:17 +0300549 protected function _close()
Alex Bilbie84445d02011-03-10 16:43:39 +0000550 {
Andrey Andreev79922c02012-05-23 12:27:17 +0300551 @sqlsrv_close($this->conn_id);
Alex Bilbie84445d02011-03-10 16:43:39 +0000552 }
553
554}
555
Andrey Andreev0e8968a2012-01-26 02:04:37 +0200556/* End of file sqlsrv_driver.php */
Andrey Andreev79922c02012-05-23 12:27:17 +0300557/* Location: ./system/database/drivers/sqlsrv/sqlsrv_driver.php */