1<?php 2 3namespace Doctrine\DBAL\Driver\SQLSrv; 4 5use Doctrine\DBAL\Driver\FetchUtils; 6use Doctrine\DBAL\Driver\Result; 7use Doctrine\DBAL\Driver\SQLSrv\Exception\Error; 8use Doctrine\DBAL\Driver\Statement as StatementInterface; 9use Doctrine\DBAL\Driver\StatementIterator; 10use Doctrine\DBAL\FetchMode; 11use Doctrine\DBAL\ParameterType; 12use IteratorAggregate; 13use PDO; 14use ReturnTypeWillChange; 15 16use function array_key_exists; 17use function count; 18use function func_get_args; 19use function in_array; 20use function is_int; 21use function is_numeric; 22use function sqlsrv_errors; 23use function sqlsrv_execute; 24use function sqlsrv_fetch; 25use function sqlsrv_fetch_array; 26use function sqlsrv_fetch_object; 27use function sqlsrv_get_field; 28use function sqlsrv_next_result; 29use function sqlsrv_num_fields; 30use function SQLSRV_PHPTYPE_STREAM; 31use function SQLSRV_PHPTYPE_STRING; 32use function sqlsrv_prepare; 33use function sqlsrv_rows_affected; 34use function SQLSRV_SQLTYPE_VARBINARY; 35use function stripos; 36 37use const SQLSRV_ENC_BINARY; 38use const SQLSRV_ENC_CHAR; 39use const SQLSRV_ERR_ERRORS; 40use const SQLSRV_FETCH_ASSOC; 41use const SQLSRV_FETCH_BOTH; 42use const SQLSRV_FETCH_NUMERIC; 43use const SQLSRV_PARAM_IN; 44 45/** 46 * SQL Server Statement. 47 * 48 * @deprecated Use {@link Statement} instead 49 */ 50class SQLSrvStatement implements IteratorAggregate, StatementInterface, Result 51{ 52 /** 53 * The SQLSRV Resource. 54 * 55 * @var resource 56 */ 57 private $conn; 58 59 /** 60 * The SQL statement to execute. 61 * 62 * @var string 63 */ 64 private $sql; 65 66 /** 67 * The SQLSRV statement resource. 68 * 69 * @var resource|null 70 */ 71 private $stmt; 72 73 /** 74 * References to the variables bound as statement parameters. 75 * 76 * @var mixed 77 */ 78 private $variables = []; 79 80 /** 81 * Bound parameter types. 82 * 83 * @var int[] 84 */ 85 private $types = []; 86 87 /** 88 * Translations. 89 * 90 * @var int[] 91 */ 92 private static $fetchMap = [ 93 FetchMode::MIXED => SQLSRV_FETCH_BOTH, 94 FetchMode::ASSOCIATIVE => SQLSRV_FETCH_ASSOC, 95 FetchMode::NUMERIC => SQLSRV_FETCH_NUMERIC, 96 ]; 97 98 /** 99 * The name of the default class to instantiate when fetching class instances. 100 * 101 * @var string 102 */ 103 private $defaultFetchClass = '\stdClass'; 104 105 /** 106 * The constructor arguments for the default class to instantiate when fetching class instances. 107 * 108 * @var mixed[] 109 */ 110 private $defaultFetchClassCtorArgs = []; 111 112 /** 113 * The fetch style. 114 * 115 * @var int 116 */ 117 private $defaultFetchMode = FetchMode::MIXED; 118 119 /** 120 * The last insert ID. 121 * 122 * @var LastInsertId|null 123 */ 124 private $lastInsertId; 125 126 /** 127 * Indicates whether the statement is in the state when fetching results is possible 128 * 129 * @var bool 130 */ 131 private $result = false; 132 133 /** 134 * Append to any INSERT query to retrieve the last insert id. 135 * 136 * @deprecated This constant has been deprecated and will be made private in 3.0 137 */ 138 public const LAST_INSERT_ID_SQL = ';SELECT SCOPE_IDENTITY() AS LastInsertId;'; 139 140 /** 141 * @internal The statement can be only instantiated by its driver connection. 142 * 143 * @param resource $conn 144 * @param string $sql 145 */ 146 public function __construct($conn, $sql, ?LastInsertId $lastInsertId = null) 147 { 148 $this->conn = $conn; 149 $this->sql = $sql; 150 151 if (stripos($sql, 'INSERT INTO ') !== 0) { 152 return; 153 } 154 155 $this->sql .= self::LAST_INSERT_ID_SQL; 156 $this->lastInsertId = $lastInsertId; 157 } 158 159 /** 160 * {@inheritdoc} 161 */ 162 public function bindValue($param, $value, $type = ParameterType::STRING) 163 { 164 if (! is_numeric($param)) { 165 throw new SQLSrvException( 166 'sqlsrv does not support named parameters to queries, use question mark (?) placeholders instead.' 167 ); 168 } 169 170 $this->variables[$param] = $value; 171 $this->types[$param] = $type; 172 173 return true; 174 } 175 176 /** 177 * {@inheritdoc} 178 */ 179 public function bindParam($param, &$variable, $type = ParameterType::STRING, $length = null) 180 { 181 if (! is_numeric($param)) { 182 throw new SQLSrvException( 183 'sqlsrv does not support named parameters to queries, use question mark (?) placeholders instead.' 184 ); 185 } 186 187 $this->variables[$param] =& $variable; 188 $this->types[$param] = $type; 189 190 // unset the statement resource if it exists as the new one will need to be bound to the new variable 191 $this->stmt = null; 192 193 return true; 194 } 195 196 /** 197 * {@inheritdoc} 198 * 199 * @deprecated Use free() instead. 200 */ 201 public function closeCursor() 202 { 203 $this->free(); 204 205 return true; 206 } 207 208 /** 209 * {@inheritdoc} 210 */ 211 public function columnCount() 212 { 213 if ($this->stmt === null) { 214 return 0; 215 } 216 217 return sqlsrv_num_fields($this->stmt) ?: 0; 218 } 219 220 /** 221 * {@inheritdoc} 222 * 223 * @deprecated The error information is available via exceptions. 224 */ 225 public function errorCode() 226 { 227 $errors = sqlsrv_errors(SQLSRV_ERR_ERRORS); 228 if ($errors) { 229 return $errors[0]['code']; 230 } 231 232 return false; 233 } 234 235 /** 236 * {@inheritdoc} 237 * 238 * @deprecated The error information is available via exceptions. 239 */ 240 public function errorInfo() 241 { 242 return (array) sqlsrv_errors(SQLSRV_ERR_ERRORS); 243 } 244 245 /** 246 * {@inheritdoc} 247 */ 248 public function execute($params = null) 249 { 250 if ($params) { 251 $hasZeroIndex = array_key_exists(0, $params); 252 253 foreach ($params as $key => $val) { 254 if ($hasZeroIndex && is_int($key)) { 255 $this->bindValue($key + 1, $val); 256 } else { 257 $this->bindValue($key, $val); 258 } 259 } 260 } 261 262 if (! $this->stmt) { 263 $this->stmt = $this->prepare(); 264 } 265 266 if (! sqlsrv_execute($this->stmt)) { 267 throw Error::new(); 268 } 269 270 if ($this->lastInsertId) { 271 sqlsrv_next_result($this->stmt); 272 sqlsrv_fetch($this->stmt); 273 $this->lastInsertId->setId(sqlsrv_get_field($this->stmt, 0)); 274 } 275 276 $this->result = true; 277 278 return true; 279 } 280 281 /** 282 * Prepares SQL Server statement resource 283 * 284 * @return resource 285 * 286 * @throws SQLSrvException 287 */ 288 private function prepare() 289 { 290 $params = []; 291 292 foreach ($this->variables as $column => &$variable) { 293 switch ($this->types[$column]) { 294 case ParameterType::LARGE_OBJECT: 295 $params[$column - 1] = [ 296 &$variable, 297 SQLSRV_PARAM_IN, 298 SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY), 299 SQLSRV_SQLTYPE_VARBINARY('max'), 300 ]; 301 break; 302 303 case ParameterType::BINARY: 304 $params[$column - 1] = [ 305 &$variable, 306 SQLSRV_PARAM_IN, 307 SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_BINARY), 308 ]; 309 break; 310 311 case ParameterType::ASCII: 312 $params[$column - 1] = [ 313 &$variable, 314 SQLSRV_PARAM_IN, 315 SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR), 316 ]; 317 break; 318 319 default: 320 $params[$column - 1] =& $variable; 321 break; 322 } 323 } 324 325 $stmt = sqlsrv_prepare($this->conn, $this->sql, $params); 326 327 if (! $stmt) { 328 throw Error::new(); 329 } 330 331 return $stmt; 332 } 333 334 /** 335 * {@inheritdoc} 336 * 337 * @deprecated Use one of the fetch- or iterate-related methods. 338 */ 339 public function setFetchMode($fetchMode, $arg2 = null, $arg3 = null) 340 { 341 $this->defaultFetchMode = $fetchMode; 342 $this->defaultFetchClass = $arg2 ?: $this->defaultFetchClass; 343 $this->defaultFetchClassCtorArgs = $arg3 ? (array) $arg3 : $this->defaultFetchClassCtorArgs; 344 345 return true; 346 } 347 348 /** 349 * {@inheritdoc} 350 * 351 * @deprecated Use iterateNumeric(), iterateAssociative() or iterateColumn() instead. 352 */ 353 #[ReturnTypeWillChange] 354 public function getIterator() 355 { 356 return new StatementIterator($this); 357 } 358 359 /** 360 * {@inheritdoc} 361 * 362 * @deprecated Use fetchNumeric(), fetchAssociative() or fetchOne() instead. 363 * 364 * @throws SQLSrvException 365 */ 366 public function fetch($fetchMode = null, $cursorOrientation = PDO::FETCH_ORI_NEXT, $cursorOffset = 0) 367 { 368 // do not try fetching from the statement if it's not expected to contain result 369 // in order to prevent exceptional situation 370 if ($this->stmt === null || ! $this->result) { 371 return false; 372 } 373 374 $args = func_get_args(); 375 $fetchMode = $fetchMode ?: $this->defaultFetchMode; 376 377 if ($fetchMode === FetchMode::COLUMN) { 378 return $this->fetchColumn(); 379 } 380 381 if (isset(self::$fetchMap[$fetchMode])) { 382 return sqlsrv_fetch_array($this->stmt, self::$fetchMap[$fetchMode]) ?: false; 383 } 384 385 if (in_array($fetchMode, [FetchMode::STANDARD_OBJECT, FetchMode::CUSTOM_OBJECT], true)) { 386 $className = $this->defaultFetchClass; 387 $ctorArgs = $this->defaultFetchClassCtorArgs; 388 389 if (count($args) >= 2) { 390 $className = $args[1]; 391 $ctorArgs = $args[2] ?? []; 392 } 393 394 return sqlsrv_fetch_object($this->stmt, $className, $ctorArgs) ?: false; 395 } 396 397 throw new SQLSrvException('Fetch mode is not supported!'); 398 } 399 400 /** 401 * {@inheritdoc} 402 * 403 * @deprecated Use fetchAllNumeric(), fetchAllAssociative() or fetchFirstColumn() instead. 404 */ 405 public function fetchAll($fetchMode = null, $fetchArgument = null, $ctorArgs = null) 406 { 407 $rows = []; 408 409 switch ($fetchMode) { 410 case FetchMode::CUSTOM_OBJECT: 411 while (($row = $this->fetch(...func_get_args())) !== false) { 412 $rows[] = $row; 413 } 414 415 break; 416 417 case FetchMode::COLUMN: 418 while (($row = $this->fetchColumn()) !== false) { 419 $rows[] = $row; 420 } 421 422 break; 423 424 default: 425 while (($row = $this->fetch($fetchMode)) !== false) { 426 $rows[] = $row; 427 } 428 } 429 430 return $rows; 431 } 432 433 /** 434 * {@inheritdoc} 435 * 436 * @deprecated Use fetchOne() instead. 437 */ 438 public function fetchColumn($columnIndex = 0) 439 { 440 $row = $this->fetch(FetchMode::NUMERIC); 441 442 if ($row === false) { 443 return false; 444 } 445 446 return $row[$columnIndex] ?? null; 447 } 448 449 /** 450 * {@inheritdoc} 451 */ 452 public function fetchNumeric() 453 { 454 return $this->doFetch(SQLSRV_FETCH_NUMERIC); 455 } 456 457 /** 458 * {@inheritdoc} 459 */ 460 public function fetchAssociative() 461 { 462 return $this->doFetch(SQLSRV_FETCH_ASSOC); 463 } 464 465 /** 466 * {@inheritdoc} 467 */ 468 public function fetchOne() 469 { 470 return FetchUtils::fetchOne($this); 471 } 472 473 /** 474 * {@inheritdoc} 475 */ 476 public function fetchAllNumeric(): array 477 { 478 return FetchUtils::fetchAllNumeric($this); 479 } 480 481 /** 482 * {@inheritdoc} 483 */ 484 public function fetchAllAssociative(): array 485 { 486 return FetchUtils::fetchAllAssociative($this); 487 } 488 489 /** 490 * {@inheritdoc} 491 */ 492 public function fetchFirstColumn(): array 493 { 494 return FetchUtils::fetchFirstColumn($this); 495 } 496 497 /** 498 * {@inheritdoc} 499 */ 500 public function rowCount() 501 { 502 if ($this->stmt === null) { 503 return 0; 504 } 505 506 return sqlsrv_rows_affected($this->stmt) ?: 0; 507 } 508 509 public function free(): void 510 { 511 // not having the result means there's nothing to close 512 if ($this->stmt === null || ! $this->result) { 513 return; 514 } 515 516 // emulate it by fetching and discarding rows, similarly to what PDO does in this case 517 // @link http://php.net/manual/en/pdostatement.closecursor.php 518 // @link https://github.com/php/php-src/blob/php-7.0.11/ext/pdo/pdo_stmt.c#L2075 519 // deliberately do not consider multiple result sets, since doctrine/dbal doesn't support them 520 while (sqlsrv_fetch($this->stmt)) { 521 } 522 523 $this->result = false; 524 } 525 526 /** 527 * @return mixed|false 528 */ 529 private function doFetch(int $fetchType) 530 { 531 // do not try fetching from the statement if it's not expected to contain the result 532 // in order to prevent exceptional situation 533 if ($this->stmt === null || ! $this->result) { 534 return false; 535 } 536 537 return sqlsrv_fetch_array($this->stmt, $fetchType) ?? false; 538 } 539} 540