PageRenderTime 25ms CodeModel.GetById 28ms RepoModel.GetById 0ms app.codeStats 0ms

/system/database/drivers/sqlsrv/sqlsrv_driver.php

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