blob: 5e920cbe8bf02827aea5fc3c912b03a4ec1addf3 [file] [log] [blame]
Alex Bilbie84445d02011-03-10 16:43:39 +00001<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
2/**
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
8 *
9 * Licensed under the Open Software License version 3.0
10 *
11 * 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
24 * @since Version 1.0
25 * @filesource
26 */
27
28// ------------------------------------------------------------------------
29
30/**
Alex Bilbie01ab0042011-03-18 19:24:30 +000031 * SQLSRV Database Adapter Class
Alex Bilbie84445d02011-03-10 16:43:39 +000032 *
33 * Note: _DB is an extender class that the app controller
34 * creates dynamically based on whether the active record
35 * class is being used or not.
36 *
37 * @package CodeIgniter
38 * @subpackage Drivers
39 * @category Database
Derek Jonesf4a4bd82011-10-20 12:18:42 -050040 * @author EllisLab Dev Team
Alex Bilbie84445d02011-03-10 16:43:39 +000041 * @link http://codeigniter.com/user_guide/database/
42 */
Alex Bilbie01ab0042011-03-18 19:24:30 +000043class CI_DB_sqlsrv_driver extends CI_DB {
Alex Bilbie84445d02011-03-10 16:43:39 +000044
45 var $dbdriver = 'sqlsrv';
46
47 // The character used for escaping
48 var $_escape_char = '';
49
50 // clause and character used for LIKE escape sequences
51 var $_like_escape_str = " ESCAPE '%s' ";
52 var $_like_escape_chr = '!';
53
54 /**
55 * The syntax to count rows is slightly different across different
56 * database engines, so this string appears in each driver and is
57 * used for the count_all() and count_all_results() functions.
58 */
59 var $_count_string = "SELECT COUNT(*) AS ";
60 var $_random_keyword = ' ASC'; // not currently supported
61
62 /**
63 * Non-persistent database connection
64 *
65 * @access private called by the base class
66 * @return resource
67 */
Alex Bilbie3a43c7a2011-03-18 19:48:04 +000068 function db_connect($pooling = false)
Alex Bilbie84445d02011-03-10 16:43:39 +000069 {
Alex Bilbie3a43c7a2011-03-18 19:48:04 +000070 // Check for a UTF-8 charset being passed as CI's default 'utf8'.
71 $character_set = (0 === strcasecmp('utf8', $this->char_set)) ? 'UTF-8' : $this->char_set;
72
73 $connection = array(
74 'UID' => empty($this->username) ? '' : $this->username,
75 'PWD' => empty($this->password) ? '' : $this->password,
76 'Database' => $this->database,
77 'ConnectionPooling' => $pooling ? 1 : 0,
78 'CharacterSet' => $character_set,
79 'ReturnDatesAsStrings' => 1
80 );
81
82 // If the username and password are both empty, assume this is a
83 // 'Windows Authentication Mode' connection.
84 if(empty($connection['UID']) && empty($connection['PWD'])) {
85 unset($connection['UID'], $connection['PWD']);
Alex Bilbie84445d02011-03-10 16:43:39 +000086 }
87
Alex Bilbie3a43c7a2011-03-18 19:48:04 +000088 return sqlsrv_connect($this->hostname, $connection);
Alex Bilbie84445d02011-03-10 16:43:39 +000089 }
90
91 // --------------------------------------------------------------------
92
93 /**
94 * Persistent database connection
95 *
96 * @access private called by the base class
97 * @return resource
98 */
99 function db_pconnect()
100 {
Kyle Farris37e351f2011-09-07 11:14:46 -0300101 return $this->db_connect(TRUE);
Alex Bilbie84445d02011-03-10 16:43:39 +0000102 }
103
104 // --------------------------------------------------------------------
105
106 /**
107 * Reconnect
108 *
109 * Keep / reestablish the db connection if no queries have been
110 * sent for a length of time exceeding the server's idle timeout
111 *
112 * @access public
113 * @return void
114 */
115 function reconnect()
116 {
117 // not implemented in MSSQL
118 }
119
120 // --------------------------------------------------------------------
121
122 /**
123 * Select the database
124 *
Andrey Andreev11454e02012-02-22 16:05:47 +0200125 * @param string database name
126 * @return bool
Alex Bilbie84445d02011-03-10 16:43:39 +0000127 */
Andrey Andreev11454e02012-02-22 16:05:47 +0200128 public function db_select($database = '')
Alex Bilbie84445d02011-03-10 16:43:39 +0000129 {
Andrey Andreev024ba2d2012-02-24 11:40:36 +0200130 if ($database === '')
131 {
132 $database = $this->database;
133 }
134
135 if ($this->_execute('USE '.$database))
136 {
137 $this->database = $database;
138 return TRUE;
139 }
140
141 return FALSE;
Alex Bilbie84445d02011-03-10 16:43:39 +0000142 }
143
144 // --------------------------------------------------------------------
145
146 /**
Alex Bilbie84445d02011-03-10 16:43:39 +0000147 * Execute the query
148 *
149 * @access private called by the base class
150 * @param string an SQL query
151 * @return resource
152 */
153 function _execute($sql)
154 {
155 $sql = $this->_prep_query($sql);
Alex Bilbie3a43c7a2011-03-18 19:48:04 +0000156 return sqlsrv_query($this->conn_id, $sql, null, array(
157 'Scrollable' => SQLSRV_CURSOR_STATIC,
158 'SendStreamParamsAtExec' => true
159 ));
Alex Bilbie84445d02011-03-10 16:43:39 +0000160 }
161
162 // --------------------------------------------------------------------
163
164 /**
165 * Prep the query
166 *
167 * If needed, each database adapter can prep the query string
168 *
169 * @access private called by execute()
170 * @param string an SQL query
171 * @return string
172 */
173 function _prep_query($sql)
174 {
175 return $sql;
176 }
177
178 // --------------------------------------------------------------------
179
180 /**
181 * Begin Transaction
182 *
183 * @access public
184 * @return bool
185 */
186 function trans_begin($test_mode = FALSE)
187 {
188 if ( ! $this->trans_enabled)
189 {
190 return TRUE;
191 }
192
193 // When transactions are nested we only begin/commit/rollback the outermost ones
194 if ($this->_trans_depth > 0)
195 {
196 return TRUE;
197 }
198
199 // Reset the transaction failure flag.
200 // If the $test_mode flag is set to TRUE transactions will be rolled back
201 // even if the queries produce a successful result.
202 $this->_trans_failure = ($test_mode === TRUE) ? TRUE : FALSE;
203
Alex Bilbie3a43c7a2011-03-18 19:48:04 +0000204 return sqlsrv_begin_transaction($this->conn_id);
Alex Bilbie84445d02011-03-10 16:43:39 +0000205 }
206
207 // --------------------------------------------------------------------
208
209 /**
210 * Commit Transaction
211 *
212 * @access public
213 * @return bool
214 */
215 function trans_commit()
216 {
217 if ( ! $this->trans_enabled)
218 {
219 return TRUE;
220 }
221
222 // When transactions are nested we only begin/commit/rollback the outermost ones
223 if ($this->_trans_depth > 0)
224 {
225 return TRUE;
226 }
227
Alex Bilbie3a43c7a2011-03-18 19:48:04 +0000228 return sqlsrv_commit($this->conn_id);
Alex Bilbie84445d02011-03-10 16:43:39 +0000229 }
230
231 // --------------------------------------------------------------------
232
233 /**
234 * Rollback Transaction
235 *
236 * @access public
237 * @return bool
238 */
239 function trans_rollback()
240 {
241 if ( ! $this->trans_enabled)
242 {
243 return TRUE;
244 }
245
246 // When transactions are nested we only begin/commit/rollback the outermost ones
247 if ($this->_trans_depth > 0)
248 {
249 return TRUE;
250 }
251
Alex Bilbie3a43c7a2011-03-18 19:48:04 +0000252 return sqlsrv_rollback($this->conn_id);
Alex Bilbie84445d02011-03-10 16:43:39 +0000253 }
254
255 // --------------------------------------------------------------------
256
257 /**
258 * Escape String
259 *
260 * @access public
261 * @param string
262 * @param bool whether or not the string will be used in a LIKE condition
263 * @return string
264 */
265 function escape_str($str, $like = FALSE)
266 {
Alex Bilbie84445d02011-03-10 16:43:39 +0000267 // Escape single quotes
Alex Bilbie3a43c7a2011-03-18 19:48:04 +0000268 return str_replace("'", "''", $str);
Alex Bilbie84445d02011-03-10 16:43:39 +0000269 }
270
271 // --------------------------------------------------------------------
272
273 /**
274 * Affected Rows
275 *
276 * @access public
277 * @return integer
278 */
279 function affected_rows()
280 {
Alex Bilbie56e20402011-03-12 23:43:54 +0000281 return @sqlrv_rows_affected($this->conn_id);
Alex Bilbie84445d02011-03-10 16:43:39 +0000282 }
283
284 // --------------------------------------------------------------------
285
286 /**
287 * Insert ID
288 *
289 * Returns the last id created in the Identity column.
290 *
291 * @access public
292 * @return integer
293 */
294 function insert_id()
295 {
Alex Bilbie3a43c7a2011-03-18 19:48:04 +0000296 return $this->query('select @@IDENTITY as insert_id')->row('insert_id');
Alex Bilbie84445d02011-03-10 16:43:39 +0000297 }
298
299 // --------------------------------------------------------------------
300
301 /**
302 * Parse major version
303 *
304 * Grabs the major version number from the
305 * database server version string passed in.
306 *
307 * @access private
308 * @param string $version
309 * @return int16 major version number
310 */
311 function _parse_major_version($version)
312 {
313 preg_match('/([0-9]+)\.([0-9]+)\.([0-9]+)/', $version, $ver_info);
314 return $ver_info[1]; // return the major version b/c that's all we're interested in.
315 }
316
317 // --------------------------------------------------------------------
318
319 /**
Andrey Andreev08856b82012-03-03 03:19:28 +0200320 * Database version number
321 *
322 * @return string
323 */
324 public function version()
Alex Bilbie84445d02011-03-10 16:43:39 +0000325 {
Andrey Andreev08856b82012-03-03 03:19:28 +0200326 if (isset($this->data_cache['version']))
327 {
328 return $this->data_cache['version'];
329 }
330
331 if (($info = sqlsrv_server_info($this->conn_id)) === FALSE)
332 {
333 return FALSE;
334 }
335
336 return $this->data_cache['version'] = $info['SQLServerVersion'];
Alex Bilbie84445d02011-03-10 16:43:39 +0000337 }
338
339 // --------------------------------------------------------------------
340
341 /**
342 * "Count All" query
343 *
344 * Generates a platform-specific query string that counts all records in
345 * the specified database
346 *
347 * @access public
348 * @param string
349 * @return string
350 */
351 function count_all($table = '')
352 {
353 if ($table == '')
Alex Bilbie3a43c7a2011-03-18 19:48:04 +0000354 return '0';
355
356 $query = $this->query("SELECT COUNT(*) AS numrows FROM " . $this->dbprefix . $table);
357
Alex Bilbie84445d02011-03-10 16:43:39 +0000358 if ($query->num_rows() == 0)
Alex Bilbie3a43c7a2011-03-18 19:48:04 +0000359 return '0';
Alex Bilbie84445d02011-03-10 16:43:39 +0000360
361 $row = $query->row();
Greg Aker90248ab2011-08-20 14:23:14 -0500362 $this->_reset_select();
Alex Bilbie3a43c7a2011-03-18 19:48:04 +0000363 return $row->numrows;
Alex Bilbie84445d02011-03-10 16:43:39 +0000364 }
365
366 // --------------------------------------------------------------------
367
368 /**
369 * List table query
370 *
371 * Generates a platform-specific query string so that the table names can be fetched
372 *
373 * @access private
374 * @param boolean
375 * @return string
376 */
377 function _list_tables($prefix_limit = FALSE)
378 {
Alex Bilbie3a43c7a2011-03-18 19:48:04 +0000379 return "SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name";
Alex Bilbie84445d02011-03-10 16:43:39 +0000380 }
381
382 // --------------------------------------------------------------------
383
384 /**
385 * List column query
386 *
387 * Generates a platform-specific query string so that the column names can be fetched
388 *
389 * @access private
390 * @param string the table name
391 * @return string
392 */
393 function _list_columns($table = '')
394 {
Alex Bilbie3a43c7a2011-03-18 19:48:04 +0000395 return "SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = '".$this->_escape_table($table)."'";
Alex Bilbie84445d02011-03-10 16:43:39 +0000396 }
397
398 // --------------------------------------------------------------------
399
400 /**
401 * Field data query
402 *
403 * Generates a platform-specific query so that the column data can be retrieved
404 *
405 * @access public
406 * @param string the table name
407 * @return object
408 */
409 function _field_data($table)
410 {
Alex Bilbie3a43c7a2011-03-18 19:48:04 +0000411 return "SELECT TOP 1 * FROM " . $this->_escape_table($table);
Alex Bilbie84445d02011-03-10 16:43:39 +0000412 }
413
414 // --------------------------------------------------------------------
415
416 /**
Andrey Andreev4be5de12012-03-02 15:45:41 +0200417 * Error
Alex Bilbie84445d02011-03-10 16:43:39 +0000418 *
Andrey Andreev4be5de12012-03-02 15:45:41 +0200419 * Returns an array containing code and message of the last
420 * database error that has occured.
Alex Bilbie84445d02011-03-10 16:43:39 +0000421 *
Andrey Andreev4be5de12012-03-02 15:45:41 +0200422 * @return array
Alex Bilbie84445d02011-03-10 16:43:39 +0000423 */
Andrey Andreev4be5de12012-03-02 15:45:41 +0200424 public function error()
Alex Bilbie84445d02011-03-10 16:43:39 +0000425 {
Andrey Andreev4be5de12012-03-02 15:45:41 +0200426 $error = array('code' => '00000', 'message' => '');
427 $sqlsrv_errors = sqlsrv_errors(SQLSRV_ERR_ERRORS);
428
429 if ( ! is_array($sqlsrv_errors))
Andrey Andreev850f6012012-03-01 15:58:25 +0200430 {
Andrey Andreev4be5de12012-03-02 15:45:41 +0200431 return $error;
Andrey Andreev850f6012012-03-01 15:58:25 +0200432 }
433
Andrey Andreev4be5de12012-03-02 15:45:41 +0200434 $sqlsrv_error = array_shift($sqlsrv_errors);
435 if (isset($sqlsrv_error['SQLSTATE']))
436 {
437 $error['code'] = isset($sqlsrv_error['code']) ? $sqlsrv_error['SQLSTATE'].'/'.$sqlsrv_error['code'] : $sqlsrv_error['SQLSTATE'];
438 }
439 elseif (isset($sqlsrv_error['code']))
440 {
441 $error['code'] = $sqlsrv_error['code'];
442 }
443
444 if (isset($sqlsrv_error['message']))
445 {
446 $error['message'] = $sqlsrv_error['message'];
447 }
448
449 return $error;
Alex Bilbie84445d02011-03-10 16:43:39 +0000450 }
451
452 // --------------------------------------------------------------------
453
454 /**
Alex Bilbie3a43c7a2011-03-18 19:48:04 +0000455 * Escape Table Name
456 *
457 * This function adds backticks if the table name has a period
458 * in it. Some DBs will get cranky unless periods are escaped
459 *
460 * @access private
461 * @param string the table name
462 * @return string
463 */
464 function _escape_table($table)
465 {
466 return $table;
467 }
468
469
470 /**
Alex Bilbie84445d02011-03-10 16:43:39 +0000471 * Escape the SQL Identifiers
472 *
473 * This function escapes column and table names
474 *
475 * @access private
476 * @param string
477 * @return string
478 */
479 function _escape_identifiers($item)
480 {
Alex Bilbie3a43c7a2011-03-18 19:48:04 +0000481 return $item;
Alex Bilbie84445d02011-03-10 16:43:39 +0000482 }
483
484 // --------------------------------------------------------------------
485
486 /**
487 * From Tables
488 *
489 * This function implicitly groups FROM tables so there is no confusion
490 * about operator precedence in harmony with SQL standards
491 *
492 * @access public
493 * @param type
494 * @return type
495 */
496 function _from_tables($tables)
497 {
498 if ( ! is_array($tables))
499 {
500 $tables = array($tables);
501 }
502
503 return implode(', ', $tables);
504 }
505
506 // --------------------------------------------------------------------
507
508 /**
509 * Insert statement
510 *
511 * Generates a platform-specific insert string from the supplied data
512 *
513 * @access public
514 * @param string the table name
515 * @param array the insert keys
516 * @param array the insert values
517 * @return string
518 */
519 function _insert($table, $keys, $values)
Alex Bilbie3a43c7a2011-03-18 19:48:04 +0000520 {
521 return "INSERT INTO ".$this->_escape_table($table)." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")";
Alex Bilbie84445d02011-03-10 16:43:39 +0000522 }
523
524 // --------------------------------------------------------------------
525
526 /**
527 * Update statement
528 *
529 * Generates a platform-specific update string from the supplied data
530 *
531 * @access public
532 * @param string the table name
533 * @param array the update data
534 * @param array the where clause
535 * @param array the orderby clause
536 * @param array the limit clause
537 * @return string
538 */
Alex Bilbie3a43c7a2011-03-18 19:48:04 +0000539 function _update($table, $values, $where)
Alex Bilbie84445d02011-03-10 16:43:39 +0000540 {
Alex Bilbie3a43c7a2011-03-18 19:48:04 +0000541 foreach($values as $key => $val)
Alex Bilbie84445d02011-03-10 16:43:39 +0000542 {
543 $valstr[] = $key." = ".$val;
544 }
Alex Bilbie3a43c7a2011-03-18 19:48:04 +0000545
546 return "UPDATE ".$this->_escape_table($table)." SET ".implode(', ', $valstr)." WHERE ".implode(" ", $where);
Alex Bilbie84445d02011-03-10 16:43:39 +0000547 }
Alex Bilbie3a43c7a2011-03-18 19:48:04 +0000548
Alex Bilbie84445d02011-03-10 16:43:39 +0000549 // --------------------------------------------------------------------
550
551 /**
552 * Truncate statement
553 *
554 * Generates a platform-specific truncate string from the supplied data
555 * If the database does not support the truncate() command
556 * This function maps to "DELETE FROM table"
557 *
558 * @access public
559 * @param string the table name
560 * @return string
561 */
562 function _truncate($table)
563 {
564 return "TRUNCATE ".$table;
565 }
566
567 // --------------------------------------------------------------------
568
569 /**
570 * Delete statement
571 *
572 * Generates a platform-specific delete string from the supplied data
573 *
574 * @access public
575 * @param string the table name
576 * @param array the where clause
577 * @param string the limit clause
578 * @return string
579 */
Alex Bilbie3a43c7a2011-03-18 19:48:04 +0000580 function _delete($table, $where)
Alex Bilbie84445d02011-03-10 16:43:39 +0000581 {
Alex Bilbie3a43c7a2011-03-18 19:48:04 +0000582 return "DELETE FROM ".$this->_escape_table($table)." WHERE ".implode(" ", $where);
Alex Bilbie84445d02011-03-10 16:43:39 +0000583 }
584
585 // --------------------------------------------------------------------
586
587 /**
588 * Limit string
589 *
590 * Generates a platform-specific LIMIT clause
591 *
592 * @access public
593 * @param string the sql query string
594 * @param integer the number of rows to limit the query to
595 * @param integer the offset value
596 * @return string
597 */
598 function _limit($sql, $limit, $offset)
599 {
600 $i = $limit + $offset;
Alex Bilbie3a43c7a2011-03-18 19:48:04 +0000601
602 return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$i.' ', $sql);
Alex Bilbie84445d02011-03-10 16:43:39 +0000603 }
604
605 // --------------------------------------------------------------------
606
607 /**
608 * Close DB Connection
609 *
610 * @access public
611 * @param resource
612 * @return void
613 */
614 function _close($conn_id)
615 {
616 @sqlsrv_close($conn_id);
617 }
618
619}
620
621
622
623/* End of file mssql_driver.php */
Andrey Andreev11454e02012-02-22 16:05:47 +0200624/* Location: ./system/database/drivers/mssql/mssql_driver.php */