SQLSrvStatement.php 10.1 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
<?php
/*
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * This software consists of voluntary contributions made by many individuals
Benjamin Eberlei's avatar
Benjamin Eberlei committed
16
 * and is licensed under the MIT license. For more information, see
17 18 19 20 21
 * <http://www.doctrine-project.org>.
 */

namespace Doctrine\DBAL\Driver\SQLSrv;

22
use Doctrine\DBAL\Driver\StatementIterator;
23 24
use Doctrine\DBAL\FetchMode;
use Doctrine\DBAL\ParameterType;
25 26 27
use IteratorAggregate;
use Doctrine\DBAL\Driver\Statement;

28
/**
Benjamin Morel's avatar
Benjamin Morel committed
29
 * SQL Server Statement.
30 31 32 33
 *
 * @since 2.3
 * @author Benjamin Eberlei <kontakt@beberlei.de>
 */
34 35 36
class SQLSrvStatement implements IteratorAggregate, Statement
{
    /**
Benjamin Morel's avatar
Benjamin Morel committed
37
     * The SQLSRV Resource.
38 39 40 41 42 43
     *
     * @var resource
     */
    private $conn;

    /**
Benjamin Morel's avatar
Benjamin Morel committed
44
     * The SQL statement to execute.
45 46 47 48 49 50
     *
     * @var string
     */
    private $sql;

    /**
Benjamin Morel's avatar
Benjamin Morel committed
51
     * The SQLSRV statement resource.
52 53 54 55 56 57
     *
     * @var resource
     */
    private $stmt;

    /**
58
     * References to the variables bound as statement parameters.
59 60 61
     *
     * @var array
     */
62
    private $variables = [];
63 64 65 66 67 68

    /**
     * Bound parameter types.
     *
     * @var array
     */
69
    private $types = [];
70 71

    /**
Benjamin Morel's avatar
Benjamin Morel committed
72
     * Translations.
73 74 75
     *
     * @var array
     */
76
    private static $fetchMap = [
Sergei Morozov's avatar
Sergei Morozov committed
77
        FetchMode::MIXED       => SQLSRV_FETCH_BOTH,
78
        FetchMode::ASSOCIATIVE => SQLSRV_FETCH_ASSOC,
Sergei Morozov's avatar
Sergei Morozov committed
79
        FetchMode::NUMERIC     => SQLSRV_FETCH_NUMERIC,
80
    ];
81

82
    /**
83
     * The name of the default class to instantiate when fetching class instances.
84 85 86 87 88 89
     *
     * @var string
     */
    private $defaultFetchClass = '\stdClass';

    /**
90
     * The constructor arguments for the default class to instantiate when fetching class instances.
91 92 93
     *
     * @var string
     */
94
    private $defaultFetchClassCtorArgs = [];
95

96
    /**
Benjamin Morel's avatar
Benjamin Morel committed
97
     * The fetch style.
98
     *
99
     * @param int
100
     */
101
    private $defaultFetchMode = FetchMode::MIXED;
102

103
    /**
Benjamin Morel's avatar
Benjamin Morel committed
104 105 106
     * The last insert ID.
     *
     * @var \Doctrine\DBAL\Driver\SQLSrv\LastInsertId|null
107 108 109
     */
    private $lastInsertId;

110 111 112 113 114 115 116
    /**
     * Indicates whether the statement is in the state when fetching results is possible
     *
     * @var bool
     */
    private $result = false;

117 118 119 120 121 122 123
    /**
     * Append to any INSERT query to retrieve the last insert id.
     *
     * @var string
     */
    const LAST_INSERT_ID_SQL = ';SELECT SCOPE_IDENTITY() AS LastInsertId;';

Benjamin Morel's avatar
Benjamin Morel committed
124
    /**
125 126 127
     * @param resource                                       $conn
     * @param string                                         $sql
     * @param \Doctrine\DBAL\Driver\SQLSrv\LastInsertId|null $lastInsertId
Benjamin Morel's avatar
Benjamin Morel committed
128
     */
129
    public function __construct($conn, $sql, LastInsertId $lastInsertId = null)
130 131 132
    {
        $this->conn = $conn;
        $this->sql = $sql;
133 134 135 136 137

        if (stripos($sql, 'INSERT INTO ') === 0) {
            $this->sql .= self::LAST_INSERT_ID_SQL;
            $this->lastInsertId = $lastInsertId;
        }
138 139
    }

Benjamin Morel's avatar
Benjamin Morel committed
140 141 142
    /**
     * {@inheritdoc}
     */
143
    public function bindValue($param, $value, $type = ParameterType::STRING)
144
    {
145 146 147 148 149 150 151 152
        if (!is_numeric($param)) {
            throw new SQLSrvException(
                'sqlsrv does not support named parameters to queries, use question mark (?) placeholders instead.'
            );
        }

        $this->variables[$param] = $value;
        $this->types[$param] = $type;
153 154 155 156 157
    }

    /**
     * {@inheritdoc}
     */
158
    public function bindParam($column, &$variable, $type = ParameterType::STRING, $length = null)
159 160 161 162 163
    {
        if (!is_numeric($column)) {
            throw new SQLSrvException("sqlsrv does not support named parameters to queries, use question mark (?) placeholders instead.");
        }

164 165
        $this->variables[$column] =& $variable;
        $this->types[$column] = $type;
166 167 168

        // unset the statement resource if it exists as the new one will need to be bound to the new variable
        $this->stmt = null;
169 170
    }

Benjamin Morel's avatar
Benjamin Morel committed
171 172 173
    /**
     * {@inheritdoc}
     */
174 175
    public function closeCursor()
    {
176 177 178 179 180
        // not having the result means there's nothing to close
        if (!$this->result) {
            return true;
        }

181 182 183 184 185 186
        // emulate it by fetching and discarding rows, similarly to what PDO does in this case
        // @link http://php.net/manual/en/pdostatement.closecursor.php
        // @link https://github.com/php/php-src/blob/php-7.0.11/ext/pdo/pdo_stmt.c#L2075
        // deliberately do not consider multiple result sets, since doctrine/dbal doesn't support them
        while (sqlsrv_fetch($this->stmt));

187 188
        $this->result = false;

189
        return true;
190 191
    }

Benjamin Morel's avatar
Benjamin Morel committed
192 193 194
    /**
     * {@inheritdoc}
     */
195 196 197 198 199 200
    public function columnCount()
    {
        return sqlsrv_num_fields($this->stmt);
    }

    /**
Benjamin Morel's avatar
Benjamin Morel committed
201
     * {@inheritdoc}
202 203 204 205 206 207 208
     */
    public function errorCode()
    {
        $errors = sqlsrv_errors(SQLSRV_ERR_ERRORS);
        if ($errors) {
            return $errors[0]['code'];
        }
Benjamin Morel's avatar
Benjamin Morel committed
209

210 211 212 213
        return false;
    }

    /**
Benjamin Morel's avatar
Benjamin Morel committed
214
     * {@inheritdoc}
215 216 217 218 219 220
     */
    public function errorInfo()
    {
        return sqlsrv_errors(SQLSRV_ERR_ERRORS);
    }

Benjamin Morel's avatar
Benjamin Morel committed
221 222 223
    /**
     * {@inheritdoc}
     */
224 225 226 227 228
    public function execute($params = null)
    {
        if ($params) {
            $hasZeroIndex = array_key_exists(0, $params);
            foreach ($params as $key => $val) {
229
                $key = ($hasZeroIndex && is_numeric($key)) ? $key + 1 : $key;
230
                $this->bindValue($key, $val);
231 232 233
            }
        }

234
        if ( ! $this->stmt) {
235
            $this->stmt = $this->prepare();
236 237 238
        }

        if (!sqlsrv_execute($this->stmt)) {
239 240
            throw SQLSrvException::fromSqlSrvErrors();
        }
241 242 243 244

        if ($this->lastInsertId) {
            sqlsrv_next_result($this->stmt);
            sqlsrv_fetch($this->stmt);
Steve Müller's avatar
Steve Müller committed
245
            $this->lastInsertId->setId(sqlsrv_get_field($this->stmt, 0));
246
        }
247 248

        $this->result = true;
249 250
    }

251 252 253 254 255 256 257 258
    /**
     * Prepares SQL Server statement resource
     *
     * @return resource
     * @throws SQLSrvException
     */
    private function prepare()
    {
259
        $params = [];
260 261

        foreach ($this->variables as $column => &$variable) {
262
            if ($this->types[$column] === ParameterType::LARGE_OBJECT) {
263
                $params[$column - 1] = [
264 265 266 267
                    &$variable,
                    SQLSRV_PARAM_IN,
                    SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY),
                    SQLSRV_SQLTYPE_VARBINARY('max'),
268
                ];
269 270 271 272 273 274 275 276 277 278 279 280 281 282
            } else {
                $params[$column - 1] =& $variable;
            }
        }

        $stmt = sqlsrv_prepare($this->conn, $this->sql, $params);

        if (!$stmt) {
            throw SQLSrvException::fromSqlSrvErrors();
        }

        return $stmt;
    }

Benjamin Morel's avatar
Benjamin Morel committed
283 284 285
    /**
     * {@inheritdoc}
     */
286
    public function setFetchMode($fetchMode, $arg2 = null, $arg3 = null)
287
    {
288 289 290
        $this->defaultFetchMode          = $fetchMode;
        $this->defaultFetchClass         = $arg2 ?: $this->defaultFetchClass;
        $this->defaultFetchClassCtorArgs = $arg3 ? (array) $arg3 : $this->defaultFetchClassCtorArgs;
Benjamin Morel's avatar
Benjamin Morel committed
291 292

        return true;
293 294 295 296 297 298 299
    }

    /**
     * {@inheritdoc}
     */
    public function getIterator()
    {
300
        return new StatementIterator($this);
301 302 303 304
    }

    /**
     * {@inheritdoc}
305 306
     *
     * @throws SQLSrvException
307
     */
308
    public function fetch($fetchMode = null, $cursorOrientation = PDO::FETCH_ORI_NEXT, $cursorOffset = 0)
309
    {
310 311 312 313 314 315
        // do not try fetching from the statement if it's not expected to contain result
        // in order to prevent exceptional situation
        if (!$this->result) {
            return false;
        }

Steve Müller's avatar
Steve Müller committed
316
        $args      = func_get_args();
317
        $fetchMode = $fetchMode ?: $this->defaultFetchMode;
Steve Müller's avatar
Steve Müller committed
318

319
        if ($fetchMode === FetchMode::COLUMN) {
320 321 322
            return $this->fetchColumn();
        }

323
        if (isset(self::$fetchMap[$fetchMode])) {
Steve Müller's avatar
Steve Müller committed
324 325
            return sqlsrv_fetch_array($this->stmt, self::$fetchMap[$fetchMode]) ?: false;
        }
326

327
        if (in_array($fetchMode, [FetchMode::STANDARD_OBJECT, FetchMode::CUSTOM_OBJECT], true)) {
328 329
            $className = $this->defaultFetchClass;
            $ctorArgs  = $this->defaultFetchClassCtorArgs;
Steve Müller's avatar
Steve Müller committed
330

331
            if (count($args) >= 2) {
332
                $className = $args[1];
333
                $ctorArgs  = $args[2] ?? [];
334
            }
335

Steve Müller's avatar
Steve Müller committed
336
            return sqlsrv_fetch_object($this->stmt, $className, $ctorArgs) ?: false;
337
        }
338

339
        throw new SQLSrvException('Fetch mode is not supported!');
340 341 342 343 344
    }

    /**
     * {@inheritdoc}
     */
345
    public function fetchAll($fetchMode = null, $fetchArgument = null, $ctorArgs = null)
346
    {
347
        $rows = [];
348 349

        switch ($fetchMode) {
350
            case FetchMode::CUSTOM_OBJECT:
351
                while ($row = call_user_func_array([$this, 'fetch'], func_get_args())) {
352 353 354
                    $rows[] = $row;
                }
                break;
355 356

            case FetchMode::COLUMN:
357 358 359 360
                while ($row = $this->fetchColumn()) {
                    $rows[] = $row;
                }
                break;
361

362 363 364 365
            default:
                while ($row = $this->fetch($fetchMode)) {
                    $rows[] = $row;
                }
366
        }
Benjamin Morel's avatar
Benjamin Morel committed
367

368 369 370 371 372 373 374 375
        return $rows;
    }

    /**
     * {@inheritdoc}
     */
    public function fetchColumn($columnIndex = 0)
    {
376
        $row = $this->fetch(FetchMode::NUMERIC);
Benjamin Morel's avatar
Benjamin Morel committed
377

378 379
        if (false === $row) {
            return false;
380 381
        }

382
        return $row[$columnIndex] ?? null;
383 384 385 386 387 388 389 390 391 392
    }

    /**
     * {@inheritdoc}
     */
    public function rowCount()
    {
        return sqlsrv_rows_affected($this->stmt);
    }
}