SQLSrvStatement.php 10 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411
  1. <?php
  2. namespace Doctrine\DBAL\Driver\SQLSrv;
  3. use Doctrine\DBAL\Driver\Statement;
  4. use Doctrine\DBAL\Driver\StatementIterator;
  5. use Doctrine\DBAL\FetchMode;
  6. use Doctrine\DBAL\ParameterType;
  7. use IteratorAggregate;
  8. use PDO;
  9. use const SQLSRV_ENC_BINARY;
  10. use const SQLSRV_ERR_ERRORS;
  11. use const SQLSRV_FETCH_ASSOC;
  12. use const SQLSRV_FETCH_BOTH;
  13. use const SQLSRV_FETCH_NUMERIC;
  14. use const SQLSRV_PARAM_IN;
  15. use function array_key_exists;
  16. use function count;
  17. use function func_get_args;
  18. use function in_array;
  19. use function is_numeric;
  20. use function sqlsrv_errors;
  21. use function sqlsrv_execute;
  22. use function sqlsrv_fetch;
  23. use function sqlsrv_fetch_array;
  24. use function sqlsrv_fetch_object;
  25. use function sqlsrv_get_field;
  26. use function sqlsrv_next_result;
  27. use function sqlsrv_num_fields;
  28. use function SQLSRV_PHPTYPE_STREAM;
  29. use function SQLSRV_PHPTYPE_STRING;
  30. use function sqlsrv_prepare;
  31. use function sqlsrv_rows_affected;
  32. use function SQLSRV_SQLTYPE_VARBINARY;
  33. use function stripos;
  34. /**
  35. * SQL Server Statement.
  36. */
  37. class SQLSrvStatement implements IteratorAggregate, Statement
  38. {
  39. /**
  40. * The SQLSRV Resource.
  41. *
  42. * @var resource
  43. */
  44. private $conn;
  45. /**
  46. * The SQL statement to execute.
  47. *
  48. * @var string
  49. */
  50. private $sql;
  51. /**
  52. * The SQLSRV statement resource.
  53. *
  54. * @var resource|null
  55. */
  56. private $stmt;
  57. /**
  58. * References to the variables bound as statement parameters.
  59. *
  60. * @var mixed
  61. */
  62. private $variables = [];
  63. /**
  64. * Bound parameter types.
  65. *
  66. * @var int[]
  67. */
  68. private $types = [];
  69. /**
  70. * Translations.
  71. *
  72. * @var int[]
  73. */
  74. private static $fetchMap = [
  75. FetchMode::MIXED => SQLSRV_FETCH_BOTH,
  76. FetchMode::ASSOCIATIVE => SQLSRV_FETCH_ASSOC,
  77. FetchMode::NUMERIC => SQLSRV_FETCH_NUMERIC,
  78. ];
  79. /**
  80. * The name of the default class to instantiate when fetching class instances.
  81. *
  82. * @var string
  83. */
  84. private $defaultFetchClass = '\stdClass';
  85. /**
  86. * The constructor arguments for the default class to instantiate when fetching class instances.
  87. *
  88. * @var mixed[]
  89. */
  90. private $defaultFetchClassCtorArgs = [];
  91. /**
  92. * The fetch style.
  93. *
  94. * @var int
  95. */
  96. private $defaultFetchMode = FetchMode::MIXED;
  97. /**
  98. * The last insert ID.
  99. *
  100. * @var LastInsertId|null
  101. */
  102. private $lastInsertId;
  103. /**
  104. * Indicates whether the statement is in the state when fetching results is possible
  105. *
  106. * @var bool
  107. */
  108. private $result = false;
  109. /**
  110. * Append to any INSERT query to retrieve the last insert id.
  111. */
  112. public const LAST_INSERT_ID_SQL = ';SELECT SCOPE_IDENTITY() AS LastInsertId;';
  113. /**
  114. * @param resource $conn
  115. * @param string $sql
  116. */
  117. public function __construct($conn, $sql, ?LastInsertId $lastInsertId = null)
  118. {
  119. $this->conn = $conn;
  120. $this->sql = $sql;
  121. if (stripos($sql, 'INSERT INTO ') !== 0) {
  122. return;
  123. }
  124. $this->sql .= self::LAST_INSERT_ID_SQL;
  125. $this->lastInsertId = $lastInsertId;
  126. }
  127. /**
  128. * {@inheritdoc}
  129. */
  130. public function bindValue($param, $value, $type = ParameterType::STRING)
  131. {
  132. if (! is_numeric($param)) {
  133. throw new SQLSrvException(
  134. 'sqlsrv does not support named parameters to queries, use question mark (?) placeholders instead.'
  135. );
  136. }
  137. $this->variables[$param] = $value;
  138. $this->types[$param] = $type;
  139. }
  140. /**
  141. * {@inheritdoc}
  142. */
  143. public function bindParam($column, &$variable, $type = ParameterType::STRING, $length = null)
  144. {
  145. if (! is_numeric($column)) {
  146. throw new SQLSrvException('sqlsrv does not support named parameters to queries, use question mark (?) placeholders instead.');
  147. }
  148. $this->variables[$column] =& $variable;
  149. $this->types[$column] = $type;
  150. // unset the statement resource if it exists as the new one will need to be bound to the new variable
  151. $this->stmt = null;
  152. }
  153. /**
  154. * {@inheritdoc}
  155. */
  156. public function closeCursor()
  157. {
  158. // not having the result means there's nothing to close
  159. if (! $this->result) {
  160. return true;
  161. }
  162. // emulate it by fetching and discarding rows, similarly to what PDO does in this case
  163. // @link http://php.net/manual/en/pdostatement.closecursor.php
  164. // @link https://github.com/php/php-src/blob/php-7.0.11/ext/pdo/pdo_stmt.c#L2075
  165. // deliberately do not consider multiple result sets, since doctrine/dbal doesn't support them
  166. while (sqlsrv_fetch($this->stmt)) {
  167. }
  168. $this->result = false;
  169. return true;
  170. }
  171. /**
  172. * {@inheritdoc}
  173. */
  174. public function columnCount()
  175. {
  176. return sqlsrv_num_fields($this->stmt);
  177. }
  178. /**
  179. * {@inheritdoc}
  180. */
  181. public function errorCode()
  182. {
  183. $errors = sqlsrv_errors(SQLSRV_ERR_ERRORS);
  184. if ($errors) {
  185. return $errors[0]['code'];
  186. }
  187. return false;
  188. }
  189. /**
  190. * {@inheritdoc}
  191. */
  192. public function errorInfo()
  193. {
  194. return sqlsrv_errors(SQLSRV_ERR_ERRORS);
  195. }
  196. /**
  197. * {@inheritdoc}
  198. */
  199. public function execute($params = null)
  200. {
  201. if ($params) {
  202. $hasZeroIndex = array_key_exists(0, $params);
  203. foreach ($params as $key => $val) {
  204. $key = $hasZeroIndex && is_numeric($key) ? $key + 1 : $key;
  205. $this->bindValue($key, $val);
  206. }
  207. }
  208. if (! $this->stmt) {
  209. $this->stmt = $this->prepare();
  210. }
  211. if (! sqlsrv_execute($this->stmt)) {
  212. throw SQLSrvException::fromSqlSrvErrors();
  213. }
  214. if ($this->lastInsertId) {
  215. sqlsrv_next_result($this->stmt);
  216. sqlsrv_fetch($this->stmt);
  217. $this->lastInsertId->setId(sqlsrv_get_field($this->stmt, 0));
  218. }
  219. $this->result = true;
  220. }
  221. /**
  222. * Prepares SQL Server statement resource
  223. *
  224. * @return resource
  225. *
  226. * @throws SQLSrvException
  227. */
  228. private function prepare()
  229. {
  230. $params = [];
  231. foreach ($this->variables as $column => &$variable) {
  232. switch ($this->types[$column]) {
  233. case ParameterType::LARGE_OBJECT:
  234. $params[$column - 1] = [
  235. &$variable,
  236. SQLSRV_PARAM_IN,
  237. SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY),
  238. SQLSRV_SQLTYPE_VARBINARY('max'),
  239. ];
  240. break;
  241. case ParameterType::BINARY:
  242. $params[$column - 1] = [
  243. &$variable,
  244. SQLSRV_PARAM_IN,
  245. SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_BINARY),
  246. ];
  247. break;
  248. default:
  249. $params[$column - 1] =& $variable;
  250. break;
  251. }
  252. }
  253. $stmt = sqlsrv_prepare($this->conn, $this->sql, $params);
  254. if (! $stmt) {
  255. throw SQLSrvException::fromSqlSrvErrors();
  256. }
  257. return $stmt;
  258. }
  259. /**
  260. * {@inheritdoc}
  261. */
  262. public function setFetchMode($fetchMode, $arg2 = null, $arg3 = null)
  263. {
  264. $this->defaultFetchMode = $fetchMode;
  265. $this->defaultFetchClass = $arg2 ?: $this->defaultFetchClass;
  266. $this->defaultFetchClassCtorArgs = $arg3 ? (array) $arg3 : $this->defaultFetchClassCtorArgs;
  267. return true;
  268. }
  269. /**
  270. * {@inheritdoc}
  271. */
  272. public function getIterator()
  273. {
  274. return new StatementIterator($this);
  275. }
  276. /**
  277. * {@inheritdoc}
  278. *
  279. * @throws SQLSrvException
  280. */
  281. public function fetch($fetchMode = null, $cursorOrientation = PDO::FETCH_ORI_NEXT, $cursorOffset = 0)
  282. {
  283. // do not try fetching from the statement if it's not expected to contain result
  284. // in order to prevent exceptional situation
  285. if (! $this->result) {
  286. return false;
  287. }
  288. $args = func_get_args();
  289. $fetchMode = $fetchMode ?: $this->defaultFetchMode;
  290. if ($fetchMode === FetchMode::COLUMN) {
  291. return $this->fetchColumn();
  292. }
  293. if (isset(self::$fetchMap[$fetchMode])) {
  294. return sqlsrv_fetch_array($this->stmt, self::$fetchMap[$fetchMode]) ?: false;
  295. }
  296. if (in_array($fetchMode, [FetchMode::STANDARD_OBJECT, FetchMode::CUSTOM_OBJECT], true)) {
  297. $className = $this->defaultFetchClass;
  298. $ctorArgs = $this->defaultFetchClassCtorArgs;
  299. if (count($args) >= 2) {
  300. $className = $args[1];
  301. $ctorArgs = $args[2] ?? [];
  302. }
  303. return sqlsrv_fetch_object($this->stmt, $className, $ctorArgs) ?: false;
  304. }
  305. throw new SQLSrvException('Fetch mode is not supported!');
  306. }
  307. /**
  308. * {@inheritdoc}
  309. */
  310. public function fetchAll($fetchMode = null, $fetchArgument = null, $ctorArgs = null)
  311. {
  312. $rows = [];
  313. switch ($fetchMode) {
  314. case FetchMode::CUSTOM_OBJECT:
  315. while (($row = $this->fetch(...func_get_args())) !== false) {
  316. $rows[] = $row;
  317. }
  318. break;
  319. case FetchMode::COLUMN:
  320. while (($row = $this->fetchColumn()) !== false) {
  321. $rows[] = $row;
  322. }
  323. break;
  324. default:
  325. while (($row = $this->fetch($fetchMode)) !== false) {
  326. $rows[] = $row;
  327. }
  328. }
  329. return $rows;
  330. }
  331. /**
  332. * {@inheritdoc}
  333. */
  334. public function fetchColumn($columnIndex = 0)
  335. {
  336. $row = $this->fetch(FetchMode::NUMERIC);
  337. if ($row === false) {
  338. return false;
  339. }
  340. return $row[$columnIndex] ?? null;
  341. }
  342. /**
  343. * {@inheritdoc}
  344. */
  345. public function rowCount()
  346. {
  347. return sqlsrv_rows_affected($this->stmt);
  348. }
  349. }