Magento 2 Documentation  2.3
Documentation for Magento 2 CMS v2.3 (December 2018)
Sqlsrv.php
Go to the documentation of this file.
1 <?php
26 #require_once 'Zend/Db/Adapter/Abstract.php';
27 
31 #require_once 'Zend/Db/Statement/Sqlsrv.php';
32 
41 {
53  protected $_config = array(
54  'dbname' => null,
55  'username' => null,
56  'password' => null,
57  );
58 
64  protected $_lastInsertId;
65 
71  protected $_lastInsertSQL = 'SELECT SCOPE_IDENTITY() as Current_Identity';
72 
84  protected $_numericDataTypes = array(
88  'INT' => Zend_Db::INT_TYPE,
89  'SMALLINT' => Zend_Db::INT_TYPE,
90  'TINYINT' => Zend_Db::INT_TYPE,
91  'BIGINT' => Zend_Db::BIGINT_TYPE,
92  'DECIMAL' => Zend_Db::FLOAT_TYPE,
93  'FLOAT' => Zend_Db::FLOAT_TYPE,
94  'MONEY' => Zend_Db::FLOAT_TYPE,
95  'NUMERIC' => Zend_Db::FLOAT_TYPE,
96  'REAL' => Zend_Db::FLOAT_TYPE,
97  'SMALLMONEY' => Zend_Db::FLOAT_TYPE,
98  );
99 
105  protected $_defaultStmtClass = 'Zend_Db_Statement_Sqlsrv';
106 
113  protected function _connect()
114  {
115  if (is_resource($this->_connection)) {
116  // connection already exists
117  return;
118  }
119 
120  if (!extension_loaded('sqlsrv')) {
124  #require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php';
125  throw new Zend_Db_Adapter_Sqlsrv_Exception('The Sqlsrv extension is required for this adapter but the extension is not loaded');
126  }
127 
128  $serverName = $this->_config['host'];
129  if (isset($this->_config['port'])) {
130  $port = (integer) $this->_config['port'];
131  $serverName .= ', ' . $port;
132  }
133 
134  $connectionInfo = array(
135  'Database' => $this->_config['dbname'],
136  );
137 
138  if (isset($this->_config['username']) && isset($this->_config['password']))
139  {
140  $connectionInfo += array(
141  'UID' => $this->_config['username'],
142  'PWD' => $this->_config['password'],
143  );
144  }
145  // else - windows authentication
146 
147  if (!empty($this->_config['driver_options'])) {
148  foreach ($this->_config['driver_options'] as $option => $value) {
149  // A value may be a constant.
150  if (is_string($value)) {
151  $constantName = strtoupper($value);
152  if (defined($constantName)) {
153  $connectionInfo[$option] = constant($constantName);
154  } else {
155  $connectionInfo[$option] = $value;
156  }
157  }
158  }
159  }
160 
161  $this->_connection = sqlsrv_connect($serverName, $connectionInfo);
162 
163  if (!$this->_connection) {
167  #require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php';
168  throw new Zend_Db_Adapter_Sqlsrv_Exception(sqlsrv_errors());
169  }
170  }
171 
179  protected function _checkRequiredOptions(array $config)
180  {
181  // we need at least a dbname
182  if (! array_key_exists('dbname', $config)) {
184  #require_once 'Zend/Db/Adapter/Exception.php';
185  throw new Zend_Db_Adapter_Exception("Configuration array must have a key for 'dbname' that names the database instance");
186  }
187 
188  if (! array_key_exists('password', $config) && array_key_exists('username', $config)) {
192  #require_once 'Zend/Db/Adapter/Exception.php';
193  throw new Zend_Db_Adapter_Exception("Configuration array must have a key for 'password' for login credentials.
194  If Windows Authentication is desired, both keys 'username' and 'password' should be ommited from config.");
195  }
196 
197  if (array_key_exists('password', $config) && !array_key_exists('username', $config)) {
201  #require_once 'Zend/Db/Adapter/Exception.php';
202  throw new Zend_Db_Adapter_Exception("Configuration array must have a key for 'username' for login credentials.
203  If Windows Authentication is desired, both keys 'username' and 'password' should be ommited from config.");
204  }
205  }
206 
214  public function setTransactionIsolationLevel($level = null)
215  {
216  $this->_connect();
217  $sql = null;
218 
219  // Default transaction level in sql server
220  if ($level === null)
221  {
222  $level = SQLSRV_TXN_READ_COMMITTED;
223  }
224 
225  switch ($level) {
226  case SQLSRV_TXN_READ_UNCOMMITTED:
227  $sql = "READ UNCOMMITTED";
228  break;
229  case SQLSRV_TXN_READ_COMMITTED:
230  $sql = "READ COMMITTED";
231  break;
232  case SQLSRV_TXN_REPEATABLE_READ:
233  $sql = "REPEATABLE READ";
234  break;
235  case SQLSRV_TXN_SNAPSHOT:
236  $sql = "SNAPSHOT";
237  break;
238  case SQLSRV_TXN_SERIALIZABLE:
239  $sql = "SERIALIZABLE";
240  break;
241  default:
242  #require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php';
243  throw new Zend_Db_Adapter_Sqlsrv_Exception("Invalid transaction isolation level mode '$level' specified");
244  }
245 
246  if (!sqlsrv_query($this->_connection, "SET TRANSACTION ISOLATION LEVEL $sql;")) {
247  #require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php';
248  throw new Zend_Db_Adapter_Sqlsrv_Exception("Transaction cannot be changed to '$level'");
249  }
250 
251  return true;
252  }
253 
259  public function isConnected()
260  {
261  return (is_resource($this->_connection)
262  && (get_resource_type($this->_connection) == 'SQL Server Connection')
263  );
264  }
265 
271  public function closeConnection()
272  {
273  if ($this->isConnected()) {
274  sqlsrv_close($this->_connection);
275  }
276  $this->_connection = null;
277  }
278 
285  public function prepare($sql)
286  {
287  $this->_connect();
288  $stmtClass = $this->_defaultStmtClass;
289 
290  if (!class_exists($stmtClass)) {
294  #require_once 'Zend/Loader.php';
295  Zend_Loader::loadClass($stmtClass);
296  }
297 
298  $stmt = new $stmtClass($this, $sql);
299  $stmt->setFetchMode($this->_fetchMode);
300  return $stmt;
301  }
302 
309  protected function _quote($value)
310  {
311  if (is_int($value)) {
312  return $value;
313  } elseif (is_float($value)) {
314  return sprintf('%F', $value);
315  }
316 
317  $value = addcslashes($value, "\000\032");
318  return "'" . str_replace("'", "''", $value) . "'";
319  }
320 
335  public function lastInsertId($tableName = null, $primaryKey = null)
336  {
337  if ($tableName) {
338  $tableName = $this->quote($tableName);
339  $sql = 'SELECT IDENT_CURRENT (' . $tableName . ') as Current_Identity';
340  return (string) $this->fetchOne($sql);
341  }
342 
343  if ($this->_lastInsertId > 0) {
344  return (string) $this->_lastInsertId;
345  }
346 
347  $sql = $this->_lastInsertSQL;
348  return (string) $this->fetchOne($sql);
349  }
350 
358  public function insert($table, array $bind)
359  {
360  // extract and quote col names from the array keys
361  $cols = array();
362  $vals = array();
363  foreach ($bind as $col => $val) {
364  $cols[] = $this->quoteIdentifier($col, true);
365  if ($val instanceof Zend_Db_Expr) {
366  $vals[] = $val->__toString();
367  unset($bind[$col]);
368  } else {
369  $vals[] = '?';
370  }
371  }
372 
373  // build the statement
374  $sql = "INSERT INTO "
375  . $this->quoteIdentifier($table, true)
376  . ' (' . implode(', ', $cols) . ') '
377  . 'VALUES (' . implode(', ', $vals) . ')'
378  . ' ' . $this->_lastInsertSQL;
379 
380  // execute the statement and return the number of affected rows
381  $stmt = $this->query($sql, array_values($bind));
382  $result = $stmt->rowCount();
383 
384  $stmt->nextRowset();
385 
386  $this->_lastInsertId = $stmt->fetchColumn();
387 
388  return $result;
389  }
390 
396  public function listTables()
397  {
398  $this->_connect();
399  $sql = "SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name";
400  return $this->fetchCol($sql);
401  }
402 
433  public function describeTable($tableName, $schemaName = null)
434  {
438  $sql = "exec sp_columns @table_name = " . $this->quoteIdentifier($tableName, true);
439  $stmt = $this->query($sql);
440  $result = $stmt->fetchAll(Zend_Db::FETCH_NUM);
441 
442  // ZF-7698
443  $stmt->closeCursor();
444 
445  if (count($result) == 0) {
446  return array();
447  }
448 
449  $owner = 1;
450  $table_name = 2;
451  $column_name = 3;
452  $type_name = 5;
453  $precision = 6;
454  $length = 7;
455  $scale = 8;
456  $nullable = 10;
457  $column_def = 12;
458  $column_position = 16;
459 
463  $tableOwner = $result[0][$owner];
464  $sql = "exec sp_pkeys @table_owner = " . $tableOwner
465  . ", @table_name = " . $this->quoteIdentifier($tableName, true);
466  $stmt = $this->query($sql);
467 
468  $primaryKeysResult = $stmt->fetchAll(Zend_Db::FETCH_NUM);
469  $primaryKeyColumn = array();
470 
471  // Per http://msdn.microsoft.com/en-us/library/ms189813.aspx,
472  // results from sp_keys stored procedure are:
473  // 0=TABLE_QUALIFIER 1=TABLE_OWNER 2=TABLE_NAME 3=COLUMN_NAME 4=KEY_SEQ 5=PK_NAME
474 
475  $pkey_column_name = 3;
476  $pkey_key_seq = 4;
477  foreach ($primaryKeysResult as $pkeysRow) {
478  $primaryKeyColumn[$pkeysRow[$pkey_column_name]] = $pkeysRow[$pkey_key_seq];
479  }
480 
481  $desc = array();
482  $p = 1;
483  foreach ($result as $key => $row) {
484  $identity = false;
485  $words = explode(' ', $row[$type_name], 2);
486  if (isset($words[0])) {
487  $type = $words[0];
488  if (isset($words[1])) {
489  $identity = (bool) preg_match('/identity/', $words[1]);
490  }
491  }
492 
493  $isPrimary = array_key_exists($row[$column_name], $primaryKeyColumn);
494  if ($isPrimary) {
495  $primaryPosition = $primaryKeyColumn[$row[$column_name]];
496  } else {
497  $primaryPosition = null;
498  }
499 
500  $desc[$this->foldCase($row[$column_name])] = array(
501  'SCHEMA_NAME' => null, // @todo
502  'TABLE_NAME' => $this->foldCase($row[$table_name]),
503  'COLUMN_NAME' => $this->foldCase($row[$column_name]),
504  'COLUMN_POSITION' => (int) $row[$column_position],
505  'DATA_TYPE' => $type,
506  'DEFAULT' => $row[$column_def],
507  'NULLABLE' => (bool) $row[$nullable],
508  'LENGTH' => $row[$length],
509  'SCALE' => $row[$scale],
510  'PRECISION' => $row[$precision],
511  'UNSIGNED' => null, // @todo
512  'PRIMARY' => $isPrimary,
513  'PRIMARY_POSITION' => $primaryPosition,
514  'IDENTITY' => $identity,
515  );
516  }
517 
518  return $desc;
519  }
520 
527  protected function _beginTransaction()
528  {
529  if (!sqlsrv_begin_transaction($this->_connection)) {
530  #require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php';
531  throw new Zend_Db_Adapter_Sqlsrv_Exception(sqlsrv_errors());
532  }
533  }
534 
541  protected function _commit()
542  {
543  if (!sqlsrv_commit($this->_connection)) {
544  #require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php';
545  throw new Zend_Db_Adapter_Sqlsrv_Exception(sqlsrv_errors());
546  }
547  }
548 
555  protected function _rollBack()
556  {
557  if (!sqlsrv_rollback($this->_connection)) {
558  #require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php';
559  throw new Zend_Db_Adapter_Sqlsrv_Exception(sqlsrv_errors());
560  }
561  }
562 
572  public function setFetchMode($mode)
573  {
574  switch ($mode) {
575  case Zend_Db::FETCH_NUM: // seq array
576  case Zend_Db::FETCH_ASSOC: // assoc array
577  case Zend_Db::FETCH_BOTH: // seq+assoc array
578  case Zend_Db::FETCH_OBJ: // object
579  $this->_fetchMode = $mode;
580  break;
581  case Zend_Db::FETCH_BOUND: // bound to PHP variable
582  #require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php';
583  throw new Zend_Db_Adapter_Sqlsrv_Exception('FETCH_BOUND is not supported yet');
584  break;
585  default:
586  #require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php';
587  throw new Zend_Db_Adapter_Sqlsrv_Exception("Invalid fetch mode '$mode' specified");
588  break;
589  }
590  }
591 
601  public function limit($sql, $count, $offset = 0)
602  {
603  $count = intval($count);
604  if ($count <= 0) {
605  #require_once 'Zend/Db/Adapter/Exception.php';
606  throw new Zend_Db_Adapter_Exception("LIMIT argument count=$count is not valid");
607  }
608 
609  $offset = intval($offset);
610  if ($offset < 0) {
612  #require_once 'Zend/Db/Adapter/Exception.php';
613  throw new Zend_Db_Adapter_Exception("LIMIT argument offset=$offset is not valid");
614  }
615 
616  if ($offset == 0) {
617  $sql = preg_replace('/^SELECT\s/i', 'SELECT TOP ' . $count . ' ', $sql);
618  } else {
619  $orderby = stristr($sql, 'ORDER BY');
620 
621  if (!$orderby) {
622  $over = 'ORDER BY (SELECT 0)';
623  } else {
624  $over = preg_replace('/\"[^,]*\".\"([^,]*)\"/i', '"inner_tbl"."$1"', $orderby);
625  }
626 
627  // Remove ORDER BY clause from $sql
628  $sql = preg_replace('/\s+ORDER BY(.*)/', '', $sql);
629 
630  // Add ORDER BY clause as an argument for ROW_NUMBER()
631  $sql = "SELECT ROW_NUMBER() OVER ($over) AS \"ZEND_DB_ROWNUM\", * FROM ($sql) AS inner_tbl";
632 
633  $start = $offset + 1;
634 
635  if ($count == PHP_INT_MAX) {
636  $sql = "WITH outer_tbl AS ($sql) SELECT * FROM outer_tbl WHERE \"ZEND_DB_ROWNUM\" >= $start";
637  }
638  else {
639  $end = $offset + $count;
640  $sql = "WITH outer_tbl AS ($sql) SELECT * FROM outer_tbl WHERE \"ZEND_DB_ROWNUM\" BETWEEN $start AND $end";
641  }
642  }
643 
644  return $sql;
645  }
646 
653  public function supportsParameters($type)
654  {
655  if ($type == 'positional') {
656  return true;
657  }
658 
659  // if its 'named' or anything else
660  return false;
661  }
662 
668  public function getServerVersion()
669  {
670  $this->_connect();
671  $serverInfo = sqlsrv_server_info($this->_connection);
672 
673  if ($serverInfo !== false) {
674  return $serverInfo['SQLServerVersion'];
675  }
676 
677  return null;
678  }
679 }
$tableName
Definition: trigger.php:13
fetchOne($sql, $bind=array())
Definition: Abstract.php:826
elseif(isset( $params[ 'redirect_parent']))
Definition: iframe.phtml:17
static loadClass($class, $dirs=null)
Definition: Loader.php:52
fetchCol($sql, $bind=array())
Definition: Abstract.php:792
$config
Definition: fraud_order.php:17
const BIGINT_TYPE
Definition: Db.php:69
quote($value, $type=null)
Definition: Abstract.php:859
const INT_TYPE
Definition: Db.php:68
$count
Definition: recent.phtml:13
const FETCH_BOUND
Definition: Db.php:144
const FETCH_ASSOC
Definition: Db.php:142
$start
Definition: listing.phtml:18
const FLOAT_TYPE
Definition: Db.php:70
insert($table, array $bind)
Definition: Sqlsrv.php:358
$type
Definition: item.phtml:13
const FETCH_BOTH
Definition: Db.php:143
$value
Definition: gender.phtml:16
const FETCH_NUM
Definition: Db.php:153
lastInsertId($tableName=null, $primaryKey=null)
Definition: Sqlsrv.php:335
if($exist=($block->getProductCollection() && $block->getProductCollection() ->getSize())) $mode
Definition: grid.phtml:15
const FETCH_OBJ
Definition: Db.php:154
_checkRequiredOptions(array $config)
Definition: Sqlsrv.php:179
query($sql, $bind=array())
Definition: Abstract.php:457
limit($sql, $count, $offset=0)
Definition: Sqlsrv.php:601
$table
Definition: trigger.php:14
quoteIdentifier($ident, $auto=false)
Definition: Abstract.php:959
supportsParameters($type)
Definition: Sqlsrv.php:653
setTransactionIsolationLevel($level=null)
Definition: Sqlsrv.php:214
describeTable($tableName, $schemaName=null)
Definition: Sqlsrv.php:433