system/database/drivers/sqlsrv/sqlsrv_driver.php

   1 <?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');
   2 /**
   3  * CodeIgniter
   4  *
   5  * An open source application development framework for PHP 5.1.6 or newer
   6  *
   7  * @package             CodeIgniter
   8  * @author              ExpressionEngine Dev Team
   9  * @copyright   Copyright (c) 2008 - 2011, EllisLab, Inc.
  10  * @license             http://codeigniter.com/user_guide/license.html
  11  * @link                http://codeigniter.com
  12  * @since               Version 1.0
  13  * @filesource
  14  */
  15
  16 // ------------------------------------------------------------------------
  17
  18 /**
  19  * SQLSRV Database Adapter Class
  20  *
  21  * Note: _DB is an extender class that the app controller
  22  * creates dynamically based on whether the active record
  23  * class is being used or not.
  24  *
  25  * @package             CodeIgniter
  26  * @subpackage  Drivers
  27  * @category    Database
  28  * @author              ExpressionEngine Dev Team
  29  * @link                http://codeigniter.com/user_guide/database/
  30  */
  31 class CI_DB_sqlsrv_driver extends CI_DB {
  32
  33         var $dbdriver = 'sqlsrv';
  34
  35         // The character used for escaping
  36         var $_escape_char = '';
  37
  38         // clause and character used for LIKE escape sequences
  39         var $_like_escape_str = " ESCAPE '%s' ";
  40         var $_like_escape_chr = '!';
  41
  42         /**
  43          * The syntax to count rows is slightly different across different
  44          * database engines, so this string appears in each driver and is
  45          * used for the count_all() and count_all_results() functions.
  46          */
  47         var $_count_string = "SELECT COUNT(*) AS ";
  48         var $_random_keyword = ' ASC'; // not currently supported
  49
  50         /**
  51          * Non-persistent database connection
  52          *
  53          * @access      private called by the base class
  54          * @return      resource
  55          */
  56         function db_connect($pooling = false)
  57         {
  58                 // Check for a UTF-8 charset being passed as CI's default 'utf8'.
  59                 $character_set = (0 === strcasecmp('utf8', $this->char_set)) ? 'UTF-8' : $this->char_set;
  60
  61                 $connection = array(
  62                         'UID'                           => empty($this->username) ? '' : $this->username,
  63                         'PWD'                           => empty($this->password) ? '' : $this->password,
  64                         'Database'                      => $this->database,
  65                         'ConnectionPooling' => $pooling ? 1 : 0,
  66                         'CharacterSet'          => $character_set,
  67                         'ReturnDatesAsStrings' => 1
  68                 );
  69
  70                 // If the username and password are both empty, assume this is a
  71                 // 'Windows Authentication Mode' connection.
  72                 if(empty($connection['UID']) && empty($connection['PWD'])) {
  73                         unset($connection['UID'], $connection['PWD']);
  74                 }
  75
  76                 return sqlsrv_connect($this->hostname, $connection);
  77         }
  78
  79         // --------------------------------------------------------------------
  80
  81         /**
  82          * Persistent database connection
  83          *
  84          * @access      private called by the base class
  85          * @return      resource
  86          */
  87         function db_pconnect()
  88         {
  89                 $this->db_connect(TRUE);
  90         }
  91
  92         // --------------------------------------------------------------------
  93
  94         /**
  95          * Reconnect
  96          *
  97          * Keep / reestablish the db connection if no queries have been
  98          * sent for a length of time exceeding the server's idle timeout
  99          *
 100          * @access      public
 101          * @return      void
 102          */
 103         function reconnect()
 104         {
 105                 // not implemented in MSSQL
 106         }
 107
 108         // --------------------------------------------------------------------
 109
 110         /**
 111          * Select the database
 112          *
 113          * @access      private called by the base class
 114          * @return      resource
 115          */
 116         function db_select()
 117         {
 118                 return $this->_execute('USE ' . $this->database);
 119         }
 120
 121         // --------------------------------------------------------------------
 122
 123         /**
 124          * Set client character set
 125          *
 126          * @access      public
 127          * @param       string
 128          * @param       string
 129          * @return      resource
 130          */
 131         function db_set_charset($charset, $collation)
 132         {
 133                 // @todo - add support if needed
 134                 return TRUE;
 135         }
 136
 137         // --------------------------------------------------------------------
 138
 139         /**
 140          * Execute the query
 141          *
 142          * @access      private called by the base class
 143          * @param       string  an SQL query
 144          * @return      resource
 145          */
 146         function _execute($sql)
 147         {
 148                 $sql = $this->_prep_query($sql);
 149                 return sqlsrv_query($this->conn_id, $sql, null, array(
 150                         'Scrollable'                            => SQLSRV_CURSOR_STATIC,
 151                         'SendStreamParamsAtExec'        => true
 152                 ));
 153         }
 154
 155         // --------------------------------------------------------------------
 156
 157         /**
 158          * Prep the query
 159          *
 160          * If needed, each database adapter can prep the query string
 161          *
 162          * @access      private called by execute()
 163          * @param       string  an SQL query
 164          * @return      string
 165          */
 166         function _prep_query($sql)
 167         {
 168                 return $sql;
 169         }
 170
 171         // --------------------------------------------------------------------
 172
 173         /**
 174          * Begin Transaction
 175          *
 176          * @access      public
 177          * @return      bool
 178          */
 179         function trans_begin($test_mode = FALSE)
 180         {
 181                 if ( ! $this->trans_enabled)
 182                 {
 183                         return TRUE;
 184                 }
 185
 186                 // When transactions are nested we only begin/commit/rollback the outermost ones
 187                 if ($this->_trans_depth > 0)
 188                 {
 189                         return TRUE;
 190                 }
 191
 192                 // Reset the transaction failure flag.
 193                 // If the $test_mode flag is set to TRUE transactions will be rolled back
 194                 // even if the queries produce a successful result.
 195                 $this->_trans_failure = ($test_mode === TRUE) ? TRUE : FALSE;
 196
 197                 return sqlsrv_begin_transaction($this->conn_id);
 198         }
 199
 200         // --------------------------------------------------------------------
 201
 202         /**
 203          * Commit Transaction
 204          *
 205          * @access      public
 206          * @return      bool
 207          */
 208         function trans_commit()
 209         {
 210                 if ( ! $this->trans_enabled)
 211                 {
 212                         return TRUE;
 213                 }
 214
 215                 // When transactions are nested we only begin/commit/rollback the outermost ones
 216                 if ($this->_trans_depth > 0)
 217                 {
 218                         return TRUE;
 219                 }
 220
 221                 return sqlsrv_commit($this->conn_id);
 222         }
 223
 224         // --------------------------------------------------------------------
 225
 226         /**
 227          * Rollback Transaction
 228          *
 229          * @access      public
 230          * @return      bool
 231          */
 232         function trans_rollback()
 233         {
 234                 if ( ! $this->trans_enabled)
 235                 {
 236                         return TRUE;
 237                 }
 238
 239                 // When transactions are nested we only begin/commit/rollback the outermost ones
 240                 if ($this->_trans_depth > 0)
 241                 {
 242                         return TRUE;
 243                 }
 244
 245                 return sqlsrv_rollback($this->conn_id);
 246         }
 247
 248         // --------------------------------------------------------------------
 249
 250         /**
 251          * Escape String
 252          *
 253          * @access      public
 254          * @param       string
 255          * @param       bool    whether or not the string will be used in a LIKE condition
 256          * @return      string
 257          */
 258         function escape_str($str, $like = FALSE)
 259         {
 260                 // Escape single quotes
 261                 return str_replace("'", "''", $str);
 262         }
 263
 264         // --------------------------------------------------------------------
 265
 266         /**
 267          * Affected Rows
 268          *
 269          * @access      public
 270          * @return      integer
 271          */
 272         function affected_rows()
 273         {
 274                 return @sqlrv_rows_affected($this->conn_id);
 275         }
 276
 277         // --------------------------------------------------------------------
 278
 279         /**
 280         * Insert ID
 281         *
 282         * Returns the last id created in the Identity column.
 283         *
 284         * @access public
 285         * @return integer
 286         */
 287         function insert_id()
 288         {
 289                 return $this->query('select @@IDENTITY as insert_id')->row('insert_id');
 290         }
 291
 292         // --------------------------------------------------------------------
 293
 294         /**
 295         * Parse major version
 296         *
 297         * Grabs the major version number from the
 298         * database server version string passed in.
 299         *
 300         * @access private
 301         * @param string $version
 302         * @return int16 major version number
 303         */
 304         function _parse_major_version($version)
 305         {
 306                 preg_match('/([0-9]+)\.([0-9]+)\.([0-9]+)/', $version, $ver_info);
 307                 return $ver_info[1]; // return the major version b/c that's all we're interested in.
 308         }
 309
 310         // --------------------------------------------------------------------
 311
 312         /**
 313         * Version number query string
 314         *
 315         * @access public
 316         * @return string
 317         */
 318         function _version()
 319         {
 320                 $info = sqlsrv_server_info($this->conn_id);
 321                 return sprintf("select '%s' as ver", $info['SQLServerVersion']);
 322         }
 323
 324         // --------------------------------------------------------------------
 325
 326         /**
 327          * "Count All" query
 328          *
 329          * Generates a platform-specific query string that counts all records in
 330          * the specified database
 331          *
 332          * @access      public
 333          * @param       string
 334          * @return      string
 335          */
 336         function count_all($table = '')
 337         {
 338                 if ($table == '')
 339                         return '0';
 340
 341                 $query = $this->query("SELECT COUNT(*) AS numrows FROM " . $this->dbprefix . $table);
 342
 343                 if ($query->num_rows() == 0)
 344                         return '0';
 345
 346                 $row = $query->row();
 347                 $this->_reset_select();
 348                 return $row->numrows;
 349         }
 350
 351         // --------------------------------------------------------------------
 352
 353         /**
 354          * List table query
 355          *
 356          * Generates a platform-specific query string so that the table names can be fetched
 357          *
 358          * @access      private
 359          * @param       boolean
 360          * @return      string
 361          */
 362         function _list_tables($prefix_limit = FALSE)
 363         {
 364                 return "SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name";
 365         }
 366
 367         // --------------------------------------------------------------------
 368
 369         /**
 370          * List column query
 371          *
 372          * Generates a platform-specific query string so that the column names can be fetched
 373          *
 374          * @access      private
 375          * @param       string  the table name
 376          * @return      string
 377          */
 378         function _list_columns($table = '')
 379         {
 380                 return "SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = '".$this->_escape_table($table)."'";
 381         }
 382
 383         // --------------------------------------------------------------------
 384
 385         /**
 386          * Field data query
 387          *
 388          * Generates a platform-specific query so that the column data can be retrieved
 389          *
 390          * @access      public
 391          * @param       string  the table name
 392          * @return      object
 393          */
 394         function _field_data($table)
 395         {
 396                 return "SELECT TOP 1 * FROM " . $this->_escape_table($table);
 397         }
 398
 399         // --------------------------------------------------------------------
 400
 401         /**
 402          * The error message string
 403          *
 404          * @access      private
 405          * @return      string
 406          */
 407         function _error_message()
 408         {
 409                 $error = array_shift(sqlsrv_errors());
 410                 return !empty($error['message']) ? $error['message'] : null;
 411         }
 412
 413         // --------------------------------------------------------------------
 414
 415         /**
 416          * The error message number
 417          *
 418          * @access      private
 419          * @return      integer
 420          */
 421         function _error_number()
 422         {
 423                 $error = array_shift(sqlsrv_errors());
 424                 return isset($error['SQLSTATE']) ? $error['SQLSTATE'] : null;
 425         }
 426
 427         // --------------------------------------------------------------------
 428
 429         /**
 430          * Escape Table Name
 431          *
 432          * This function adds backticks if the table name has a period
 433          * in it. Some DBs will get cranky unless periods are escaped
 434          *
 435          * @access      private
 436          * @param       string  the table name
 437          * @return      string
 438          */
 439         function _escape_table($table)
 440         {
 441                 return $table;
 442         }
 443
 444
 445         /**
 446          * Escape the SQL Identifiers
 447          *
 448          * This function escapes column and table names
 449          *
 450          * @access      private
 451          * @param       string
 452          * @return      string
 453          */
 454         function _escape_identifiers($item)
 455         {
 456                 return $item;
 457         }
 458
 459         // --------------------------------------------------------------------
 460
 461         /**
 462          * From Tables
 463          *
 464          * This function implicitly groups FROM tables so there is no confusion
 465          * about operator precedence in harmony with SQL standards
 466          *
 467          * @access      public
 468          * @param       type
 469          * @return      type
 470          */
 471         function _from_tables($tables)
 472         {
 473                 if ( ! is_array($tables))
 474                 {
 475                         $tables = array($tables);
 476                 }
 477
 478                 return implode(', ', $tables);
 479         }
 480
 481         // --------------------------------------------------------------------
 482
 483         /**
 484          * Insert statement
 485          *
 486          * Generates a platform-specific insert string from the supplied data
 487          *
 488          * @access      public
 489          * @param       string  the table name
 490          * @param       array   the insert keys
 491          * @param       array   the insert values
 492          * @return      string
 493          */
 494         function _insert($table, $keys, $values)
 495         {
 496                 return "INSERT INTO ".$this->_escape_table($table)." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")";
 497         }
 498
 499         // --------------------------------------------------------------------
 500
 501         /**
 502          * Update statement
 503          *
 504          * Generates a platform-specific update string from the supplied data
 505          *
 506          * @access      public
 507          * @param       string  the table name
 508          * @param       array   the update data
 509          * @param       array   the where clause
 510          * @param       array   the orderby clause
 511          * @param       array   the limit clause
 512          * @return      string
 513          */
 514         function _update($table, $values, $where)
 515         {
 516                 foreach($values as $key => $val)
 517                 {
 518                         $valstr[] = $key." = ".$val;
 519                 }
 520
 521                 return "UPDATE ".$this->_escape_table($table)." SET ".implode(', ', $valstr)." WHERE ".implode(" ", $where);
 522         }
 523
 524         // --------------------------------------------------------------------
 525
 526         /**
 527          * Truncate statement
 528          *
 529          * Generates a platform-specific truncate string from the supplied data
 530          * If the database does not support the truncate() command
 531          * This function maps to "DELETE FROM table"
 532          *
 533          * @access      public
 534          * @param       string  the table name
 535          * @return      string
 536          */
 537         function _truncate($table)
 538         {
 539                 return "TRUNCATE ".$table;
 540         }
 541
 542         // --------------------------------------------------------------------
 543
 544         /**
 545          * Delete statement
 546          *
 547          * Generates a platform-specific delete string from the supplied data
 548          *
 549          * @access      public
 550          * @param       string  the table name
 551          * @param       array   the where clause
 552          * @param       string  the limit clause
 553          * @return      string
 554          */
 555         function _delete($table, $where)
 556         {
 557                 return "DELETE FROM ".$this->_escape_table($table)." WHERE ".implode(" ", $where);
 558         }
 559
 560         // --------------------------------------------------------------------
 561
 562         /**
 563          * Limit string
 564          *
 565          * Generates a platform-specific LIMIT clause
 566          *
 567          * @access      public
 568          * @param       string  the sql query string
 569          * @param       integer the number of rows to limit the query to
 570          * @param       integer the offset value
 571          * @return      string
 572          */
 573         function _limit($sql, $limit, $offset)
 574         {
 575                 $i = $limit + $offset;
 576
 577                 return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$i.' ', $sql);
 578         }
 579
 580         // --------------------------------------------------------------------
 581
 582         /**
 583          * Close DB Connection
 584          *
 585          * @access      public
 586          * @param       resource
 587          * @return      void
 588          */
 589         function _close($conn_id)
 590         {
 591                 @sqlsrv_close($conn_id);
 592         }
 593
 594 }
 595
 596
 597
 598 /* End of file mssql_driver.php */
 599 /* Location: ./system/database/drivers/mssql/mssql_driver.php */