SQLSrvStatement.php 6.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278
  1. <?php
  2. /*
  3. * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
  4. * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
  5. * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
  6. * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
  7. * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
  8. * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
  9. * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
  10. * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
  11. * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
  12. * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
  13. * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  14. *
  15. * This software consists of voluntary contributions made by many individuals
  16. * and is licensed under the MIT license. For more information, see
  17. * <http://www.doctrine-project.org>.
  18. */
  19. namespace Doctrine\DBAL\Driver\SQLSrv;
  20. use PDO;
  21. use IteratorAggregate;
  22. use Doctrine\DBAL\Driver\Statement;
  23. /**
  24. * SQL Server Statement.
  25. *
  26. * @since 2.3
  27. * @author Benjamin Eberlei <kontakt@beberlei.de>
  28. */
  29. class SQLSrvStatement implements IteratorAggregate, Statement
  30. {
  31. /**
  32. * The SQLSRV Resource.
  33. *
  34. * @var resource
  35. */
  36. private $conn;
  37. /**
  38. * The SQL statement to execute.
  39. *
  40. * @var string
  41. */
  42. private $sql;
  43. /**
  44. * The SQLSRV statement resource.
  45. *
  46. * @var resource
  47. */
  48. private $stmt;
  49. /**
  50. * Parameters to bind.
  51. *
  52. * @var array
  53. */
  54. private $params = array();
  55. /**
  56. * Translations.
  57. *
  58. * @var array
  59. */
  60. private static $fetchMap = array(
  61. PDO::FETCH_BOTH => SQLSRV_FETCH_BOTH,
  62. PDO::FETCH_ASSOC => SQLSRV_FETCH_ASSOC,
  63. PDO::FETCH_NUM => SQLSRV_FETCH_NUMERIC,
  64. );
  65. /**
  66. * The fetch style.
  67. *
  68. * @param integer
  69. */
  70. private $defaultFetchMode = PDO::FETCH_BOTH;
  71. /**
  72. * The last insert ID.
  73. *
  74. * @var \Doctrine\DBAL\Driver\SQLSrv\LastInsertId|null
  75. */
  76. private $lastInsertId;
  77. /**
  78. * Append to any INSERT query to retrieve the last insert id.
  79. *
  80. * @var string
  81. */
  82. const LAST_INSERT_ID_SQL = ';SELECT SCOPE_IDENTITY() AS LastInsertId;';
  83. /**
  84. * @param resource $conn
  85. * @param string $sql
  86. * @param integer|null $lastInsertId
  87. */
  88. public function __construct($conn, $sql, $lastInsertId = null)
  89. {
  90. $this->conn = $conn;
  91. $this->sql = $sql;
  92. if (stripos($sql, 'INSERT INTO ') === 0) {
  93. $this->sql .= self::LAST_INSERT_ID_SQL;
  94. $this->lastInsertId = $lastInsertId;
  95. }
  96. }
  97. /**
  98. * {@inheritdoc}
  99. */
  100. public function bindValue($param, $value, $type = null)
  101. {
  102. return $this->bindParam($param, $value, $type,null);
  103. }
  104. /**
  105. * {@inheritdoc}
  106. */
  107. public function bindParam($column, &$variable, $type = null, $length = null)
  108. {
  109. if (!is_numeric($column)) {
  110. throw new SQLSrvException("sqlsrv does not support named parameters to queries, use question mark (?) placeholders instead.");
  111. }
  112. if ($type === \PDO::PARAM_LOB) {
  113. $this->params[$column-1] = array($variable, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY), SQLSRV_SQLTYPE_VARBINARY('max'));
  114. } else {
  115. $this->params[$column-1] = $variable;
  116. }
  117. }
  118. /**
  119. * {@inheritdoc}
  120. */
  121. public function closeCursor()
  122. {
  123. if ($this->stmt) {
  124. sqlsrv_free_stmt($this->stmt);
  125. }
  126. }
  127. /**
  128. * {@inheritdoc}
  129. */
  130. public function columnCount()
  131. {
  132. return sqlsrv_num_fields($this->stmt);
  133. }
  134. /**
  135. * {@inheritdoc}
  136. */
  137. public function errorCode()
  138. {
  139. $errors = sqlsrv_errors(SQLSRV_ERR_ERRORS);
  140. if ($errors) {
  141. return $errors[0]['code'];
  142. }
  143. return false;
  144. }
  145. /**
  146. * {@inheritdoc}
  147. */
  148. public function errorInfo()
  149. {
  150. return sqlsrv_errors(SQLSRV_ERR_ERRORS);
  151. }
  152. /**
  153. * {@inheritdoc}
  154. */
  155. public function execute($params = null)
  156. {
  157. if ($params) {
  158. $hasZeroIndex = array_key_exists(0, $params);
  159. foreach ($params as $key => $val) {
  160. $key = ($hasZeroIndex && is_numeric($key)) ? $key + 1 : $key;
  161. $this->bindValue($key, $val);
  162. }
  163. }
  164. $this->stmt = sqlsrv_query($this->conn, $this->sql, $this->params);
  165. if ( ! $this->stmt) {
  166. throw SQLSrvException::fromSqlSrvErrors();
  167. }
  168. if ($this->lastInsertId) {
  169. sqlsrv_next_result($this->stmt);
  170. sqlsrv_fetch($this->stmt);
  171. $this->lastInsertId->setId( sqlsrv_get_field($this->stmt, 0) );
  172. }
  173. }
  174. /**
  175. * {@inheritdoc}
  176. */
  177. public function setFetchMode($fetchMode, $arg2 = null, $arg3 = null)
  178. {
  179. $this->defaultFetchMode = $fetchMode;
  180. return true;
  181. }
  182. /**
  183. * {@inheritdoc}
  184. */
  185. public function getIterator()
  186. {
  187. $data = $this->fetchAll();
  188. return new \ArrayIterator($data);
  189. }
  190. /**
  191. * {@inheritdoc}
  192. */
  193. public function fetch($fetchMode = null)
  194. {
  195. $fetchMode = $fetchMode ?: $this->defaultFetchMode;
  196. if (isset(self::$fetchMap[$fetchMode])) {
  197. return sqlsrv_fetch_array($this->stmt, self::$fetchMap[$fetchMode]);
  198. } else if ($fetchMode == PDO::FETCH_OBJ || $fetchMode == PDO::FETCH_CLASS) {
  199. $className = null;
  200. $ctorArgs = null;
  201. if (func_num_args() >= 2) {
  202. $args = func_get_args();
  203. $className = $args[1];
  204. $ctorArgs = (isset($args[2])) ? $args[2] : array();
  205. }
  206. return sqlsrv_fetch_object($this->stmt, $className, $ctorArgs);
  207. }
  208. throw new SQLSrvException("Fetch mode is not supported!");
  209. }
  210. /**
  211. * {@inheritdoc}
  212. */
  213. public function fetchAll($fetchMode = null)
  214. {
  215. $className = null;
  216. $ctorArgs = null;
  217. if (func_num_args() >= 2) {
  218. $args = func_get_args();
  219. $className = $args[1];
  220. $ctorArgs = (isset($args[2])) ? $args[2] : array();
  221. }
  222. $rows = array();
  223. while ($row = $this->fetch($fetchMode, $className, $ctorArgs)) {
  224. $rows[] = $row;
  225. }
  226. return $rows;
  227. }
  228. /**
  229. * {@inheritdoc}
  230. */
  231. public function fetchColumn($columnIndex = 0)
  232. {
  233. $row = $this->fetch(PDO::FETCH_NUM);
  234. return $row[$columnIndex];
  235. }
  236. /**
  237. * {@inheritdoc}
  238. */
  239. public function rowCount()
  240. {
  241. return sqlsrv_rows_affected($this->stmt);
  242. }
  243. }