StatementTest.php 10.3 KB
Newer Older
1 2
<?php

3
namespace Doctrine\DBAL\Tests\Functional;
4

5
use Doctrine\DBAL\Driver\PDOOracle\Driver as PDOOracleDriver;
6
use Doctrine\DBAL\Driver\Statement;
7 8
use Doctrine\DBAL\FetchMode;
use Doctrine\DBAL\ParameterType;
9
use Doctrine\DBAL\Schema\Table;
10
use Doctrine\DBAL\Tests\FunctionalTestCase;
11
use Doctrine\DBAL\Types\Type;
12 13
use function base64_decode;
use function stream_get_contents;
14

15
class StatementTest extends FunctionalTestCase
16
{
17
    protected function setUp() : void
18
    {
19 20 21
        parent::setUp();

        $table = new Table('stmt_test');
22
        $table->addColumn('id', 'integer');
Sergei Morozov's avatar
Sergei Morozov committed
23
        $table->addColumn('name', 'text', ['notnull' => false]);
Sergei Morozov's avatar
Sergei Morozov committed
24
        $this->connection->getSchemaManager()->dropAndCreateTable($table);
25
    }
26

27
    public function testStatementIsReusableAfterClosingCursor() : void
28
    {
29
        if ($this->connection->getDriver() instanceof PDOOracleDriver) {
30
            self::markTestIncomplete('See https://bugs.php.net/bug.php?id=77181');
31 32
        }

Sergei Morozov's avatar
Sergei Morozov committed
33 34
        $this->connection->insert('stmt_test', ['id' => 1]);
        $this->connection->insert('stmt_test', ['id' => 2]);
35

Sergei Morozov's avatar
Sergei Morozov committed
36
        $stmt = $this->connection->prepare('SELECT id FROM stmt_test ORDER BY id');
37 38 39 40

        $stmt->execute();

        $id = $stmt->fetchColumn();
41
        self::assertEquals(1, $id);
42 43 44 45 46

        $stmt->closeCursor();

        $stmt->execute();
        $id = $stmt->fetchColumn();
47
        self::assertEquals(1, $id);
48
        $id = $stmt->fetchColumn();
49
        self::assertEquals(2, $id);
50 51
    }

52
    public function testReuseStatementWithLongerResults() : void
53
    {
54
        if ($this->connection->getDriver() instanceof PDOOracleDriver) {
55
            self::markTestIncomplete('PDO_OCI doesn\'t support fetching blobs via PDOStatement::fetchAll()');
56 57
        }

Sergei Morozov's avatar
Sergei Morozov committed
58
        $sm    = $this->connection->getSchemaManager();
59
        $table = new Table('stmt_longer_results');
60 61 62 63
        $table->addColumn('param', 'string');
        $table->addColumn('val', 'text');
        $sm->createTable($table);

Sergei Morozov's avatar
Sergei Morozov committed
64
        $row1 = [
65 66
            'param' => 'param1',
            'val' => 'X',
Sergei Morozov's avatar
Sergei Morozov committed
67
        ];
Sergei Morozov's avatar
Sergei Morozov committed
68
        $this->connection->insert('stmt_longer_results', $row1);
69

Sergei Morozov's avatar
Sergei Morozov committed
70
        $stmt = $this->connection->prepare('SELECT param, val FROM stmt_longer_results ORDER BY param');
71
        $stmt->execute();
72
        self::assertEquals([
Sergei Morozov's avatar
Sergei Morozov committed
73 74
            ['param1', 'X'],
        ], $stmt->fetchAll(FetchMode::NUMERIC));
75

Sergei Morozov's avatar
Sergei Morozov committed
76
        $row2 = [
77 78
            'param' => 'param2',
            'val' => 'A bit longer value',
Sergei Morozov's avatar
Sergei Morozov committed
79
        ];
Sergei Morozov's avatar
Sergei Morozov committed
80
        $this->connection->insert('stmt_longer_results', $row2);
81 82

        $stmt->execute();
83
        self::assertEquals([
Sergei Morozov's avatar
Sergei Morozov committed
84 85 86
            ['param1', 'X'],
            ['param2', 'A bit longer value'],
        ], $stmt->fetchAll(FetchMode::NUMERIC));
87
    }
88

89
    public function testFetchLongBlob() : void
90
    {
91 92 93
        if ($this->connection->getDriver() instanceof PDOOracleDriver) {
            // inserting BLOBs as streams on Oracle requires Oracle-specific SQL syntax which is currently not supported
            // see http://php.net/manual/en/pdo.lobs.php#example-1035
94
            self::markTestSkipped('DBAL doesn\'t support storing LOBs represented as streams using PDO_OCI');
95 96
        }

97 98 99 100
        // make sure memory limit is large enough to not cause false positives,
        // but is still not enough to store a LONGBLOB of the max possible size
        $this->iniSet('memory_limit', '4G');

Sergei Morozov's avatar
Sergei Morozov committed
101
        $sm    = $this->connection->getSchemaManager();
102
        $table = new Table('stmt_long_blob');
Sergei Morozov's avatar
Sergei Morozov committed
103
        $table->addColumn('contents', 'blob', ['length' => 0xFFFFFFFF]);
104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120
        $sm->createTable($table);

        $contents = base64_decode(<<<EOF
H4sICJRACVgCA2RvY3RyaW5lLmljbwDtVNtLFHEU/ia1i9fVzVWxvJSrZmoXS6pd0zK7QhdNc03z
lrpppq1pWqJCFERZkUFEDybYBQqJhB6iUOqhh+whgl4qkF6MfGh+s87O7GVmO6OlBfUfdIZvznxn
fpzznW9gAI4unQ50XwirH2AAkEygEuIwU58ODnPBzXGv14sEq4BrwzKKL4sY++SGTz6PodcutN5x
IPvsFCa+K9CXMfS/cOL5OxesN0Wceygho0WAXVLwcUJBdDVDaqOAij4Rrz640XlXQmAxQ16PHU63
iqdvXbg4JOHLpILBUSdM7XZEVDDcfuZEbI2ASaYguUGAroSh97GMngcSeFFFerMdI+/dyGy1o+GW
Ax5FxfAbFwoviajuc+DCIwn+RTwGRmRIThXxdQJyu+z4/NUDYz2DKCsILuERWsoQfoQhqpLhyhMZ
XfcknBmU0NLvQArpTm0SsI5mqKqKuFoGc8cUcjrtqLohom1AgtujQnapmJJU+BbwCLIwhJXyiKlh
MB4TkFgvIK3JjrRmAefJm+77Eiqvi+SvCq/qJahQyWuVuEpcIa7QLh7Kbsourb9b66/pZdAd1voz
fCNfwsp46OnZQPojSX9UFcNy+mYJNDeJPHtJfqeR/nSaPTzmwlXar5dQ1adpd+B//I9/hi0xuCPQ
Nkvb5um37Wtc+auQXZsVxEVYD5hnCilxTaYYjsuxLlsxXUitzd2hs3GWHLM5UOM7Fy8t3xiat4fb
sneNxmNb/POO1pRXc7vnF2nc13Rq0cFWiyXkuHmzxuOtzUYfC7fEmK/3mx4QZd5u4E7XJWz6+dey
Za4tXHUiPyB8Vm781oaT+3fN6Y/eUFDfPkcNWetNxb+tlxEZsPqPdZMOzS4rxwJ8CDC+ABj1+Tu0
d+N0hqezcjblboJ3Bj8ARJilHX4FAAA=
EOF
121
        , true);
122

Sergei Morozov's avatar
Sergei Morozov committed
123
        $this->connection->insert('stmt_long_blob', ['contents' => $contents], [ParameterType::LARGE_OBJECT]);
124

Sergei Morozov's avatar
Sergei Morozov committed
125
        $stmt = $this->connection->prepare('SELECT contents FROM stmt_long_blob');
126 127 128 129 130
        $stmt->execute();

        $stream = Type::getType('blob')
            ->convertToPHPValue(
                $stmt->fetchColumn(),
Sergei Morozov's avatar
Sergei Morozov committed
131
                $this->connection->getDatabasePlatform()
132 133
            );

134
        self::assertSame($contents, stream_get_contents($stream));
135
    }
136

137
    public function testIncompletelyFetchedStatementDoesNotBlockConnection() : void
138
    {
Sergei Morozov's avatar
Sergei Morozov committed
139 140
        $this->connection->insert('stmt_test', ['id' => 1]);
        $this->connection->insert('stmt_test', ['id' => 2]);
141

Sergei Morozov's avatar
Sergei Morozov committed
142
        $stmt1 = $this->connection->prepare('SELECT id FROM stmt_test');
143 144 145 146 147 148
        $stmt1->execute();
        $stmt1->fetch();
        $stmt1->execute();
        // fetching only one record out of two
        $stmt1->fetch();

Sergei Morozov's avatar
Sergei Morozov committed
149
        $stmt2 = $this->connection->prepare('SELECT id FROM stmt_test WHERE id = ?');
Sergei Morozov's avatar
Sergei Morozov committed
150
        $stmt2->execute([1]);
151
        self::assertEquals(1, $stmt2->fetchColumn());
152 153
    }

154
    public function testReuseStatementAfterClosingCursor() : void
155
    {
156
        if ($this->connection->getDriver() instanceof PDOOracleDriver) {
157
            self::markTestIncomplete('See https://bugs.php.net/bug.php?id=77181');
158 159
        }

Sergei Morozov's avatar
Sergei Morozov committed
160 161
        $this->connection->insert('stmt_test', ['id' => 1]);
        $this->connection->insert('stmt_test', ['id' => 2]);
162

Sergei Morozov's avatar
Sergei Morozov committed
163
        $stmt = $this->connection->prepare('SELECT id FROM stmt_test WHERE id = ?');
164

Sergei Morozov's avatar
Sergei Morozov committed
165
        $stmt->execute([1]);
166
        $id = $stmt->fetchColumn();
167
        self::assertEquals(1, $id);
168 169 170

        $stmt->closeCursor();

Sergei Morozov's avatar
Sergei Morozov committed
171
        $stmt->execute([2]);
172
        $id = $stmt->fetchColumn();
173
        self::assertEquals(2, $id);
174
    }
175

176
    public function testReuseStatementWithParameterBoundByReference() : void
177
    {
Sergei Morozov's avatar
Sergei Morozov committed
178 179
        $this->connection->insert('stmt_test', ['id' => 1]);
        $this->connection->insert('stmt_test', ['id' => 2]);
180

Sergei Morozov's avatar
Sergei Morozov committed
181
        $stmt = $this->connection->prepare('SELECT id FROM stmt_test WHERE id = ?');
182 183 184 185
        $stmt->bindParam(1, $id);

        $id = 1;
        $stmt->execute();
186
        self::assertEquals(1, $stmt->fetchColumn());
187 188 189

        $id = 2;
        $stmt->execute();
190
        self::assertEquals(2, $stmt->fetchColumn());
191 192
    }

193
    public function testReuseStatementWithReboundValue() : void
194
    {
Sergei Morozov's avatar
Sergei Morozov committed
195 196
        $this->connection->insert('stmt_test', ['id' => 1]);
        $this->connection->insert('stmt_test', ['id' => 2]);
197

Sergei Morozov's avatar
Sergei Morozov committed
198
        $stmt = $this->connection->prepare('SELECT id FROM stmt_test WHERE id = ?');
199 200 201

        $stmt->bindValue(1, 1);
        $stmt->execute();
202
        self::assertEquals(1, $stmt->fetchColumn());
203 204 205

        $stmt->bindValue(1, 2);
        $stmt->execute();
206
        self::assertEquals(2, $stmt->fetchColumn());
207 208
    }

209
    public function testReuseStatementWithReboundParam() : void
210
    {
Sergei Morozov's avatar
Sergei Morozov committed
211 212
        $this->connection->insert('stmt_test', ['id' => 1]);
        $this->connection->insert('stmt_test', ['id' => 2]);
213

Sergei Morozov's avatar
Sergei Morozov committed
214
        $stmt = $this->connection->prepare('SELECT id FROM stmt_test WHERE id = ?');
215 216 217 218

        $x = 1;
        $stmt->bindParam(1, $x);
        $stmt->execute();
219
        self::assertEquals(1, $stmt->fetchColumn());
220 221 222 223

        $y = 2;
        $stmt->bindParam(1, $y);
        $stmt->execute();
224
        self::assertEquals(2, $stmt->fetchColumn());
225 226
    }

227
    /**
228 229
     * @param mixed $expected
     *
230 231
     * @dataProvider emptyFetchProvider
     */
232
    public function testFetchFromNonExecutedStatement(callable $fetch, $expected) : void
233
    {
Sergei Morozov's avatar
Sergei Morozov committed
234
        $stmt = $this->connection->prepare('SELECT id FROM stmt_test');
235

236
        self::assertSame($expected, $fetch($stmt));
237 238
    }

239
    public function testCloseCursorOnNonExecutedStatement() : void
240
    {
Sergei Morozov's avatar
Sergei Morozov committed
241
        $stmt = $this->connection->prepare('SELECT id FROM stmt_test');
242
        self::assertTrue($stmt->closeCursor());
243 244
    }

245 246 247
    /**
     * @group DBAL-2637
     */
248
    public function testCloseCursorAfterCursorEnd() : void
249
    {
Sergei Morozov's avatar
Sergei Morozov committed
250
        $stmt = $this->connection->prepare('SELECT name FROM stmt_test');
251 252 253 254

        $stmt->execute();
        $stmt->fetch();

255
        self::assertTrue($stmt->closeCursor());
256 257
    }

258
    /**
259 260
     * @param mixed $expected
     *
261 262
     * @dataProvider emptyFetchProvider
     */
263
    public function testFetchFromNonExecutedStatementWithClosedCursor(callable $fetch, $expected) : void
264
    {
Sergei Morozov's avatar
Sergei Morozov committed
265
        $stmt = $this->connection->prepare('SELECT id FROM stmt_test');
266 267
        $stmt->closeCursor();

268
        self::assertSame($expected, $fetch($stmt));
269 270 271
    }

    /**
272 273
     * @param mixed $expected
     *
274 275
     * @dataProvider emptyFetchProvider
     */
276
    public function testFetchFromExecutedStatementWithClosedCursor(callable $fetch, $expected) : void
277
    {
Sergei Morozov's avatar
Sergei Morozov committed
278
        $this->connection->insert('stmt_test', ['id' => 1]);
279

Sergei Morozov's avatar
Sergei Morozov committed
280
        $stmt = $this->connection->prepare('SELECT id FROM stmt_test');
281 282 283
        $stmt->execute();
        $stmt->closeCursor();

284
        self::assertSame($expected, $fetch($stmt));
285 286
    }

287 288 289 290
    /**
     * @return mixed[][]
     */
    public static function emptyFetchProvider() : iterable
291
    {
Sergei Morozov's avatar
Sergei Morozov committed
292 293 294
        return [
            'fetch' => [
                static function (Statement $stmt) {
295 296 297
                    return $stmt->fetch();
                },
                false,
Sergei Morozov's avatar
Sergei Morozov committed
298 299 300
            ],
            'fetch-column' => [
                static function (Statement $stmt) {
301 302 303
                    return $stmt->fetchColumn();
                },
                false,
Sergei Morozov's avatar
Sergei Morozov committed
304 305
            ],
            'fetch-all' => [
306
                static function (Statement $stmt) : array {
307 308
                    return $stmt->fetchAll();
                },
Sergei Morozov's avatar
Sergei Morozov committed
309 310 311
                [],
            ],
        ];
312
    }
313 314 315

    public function testFetchInColumnMode() : void
    {
Sergei Morozov's avatar
Sergei Morozov committed
316
        $platform = $this->connection->getDatabasePlatform();
317
        $query    = $platform->getDummySelectSQL();
Sergei Morozov's avatar
Sergei Morozov committed
318
        $result   = $this->connection->executeQuery($query)->fetch(FetchMode::COLUMN);
319 320 321

        self::assertEquals(1, $result);
    }
322
}