CODEWITHSUNDEEP
sqlsql-serversymfonydoctrine-ormdoctrine
Ben Shoemaker
Ben Shoemaker

Reputation: 143

Issue With Doctrine DBAL and SQL Variables

I'm attempting to run the following query on MSSQL Server, using Doctrine DBAL:

$sql = "declare @new_id_num int;
        set @new_id_num = 111111;
        select @new_id_num as [ID_NUM];";

$statement = $conn->prepare($sql);
$statement->execute();
$newIDNum = $statement->fetchAll();

However, all I'm getting back in $newIDNum is an empty array.
I'm not sure what to make of that - the query runs fine in a normal SQL console. Trace seems fine - you can see the correct query being run..

I imagine I'm missing something simple?

Upvotes: 1

Views: 617

Answers (2)

Arnaud D
Arnaud D

Reputation: 81

Thank you Ben!

I share an example with a procedure

$myquery = "DECLARE @return_value int,
            @param1 varchar(32),
            @out1 varchar(32),
            @out2 varchar(32)

            EXEC @return_value = MY_PROC
                                   @param1 = N'foo',
                                   @out1= @out1 OUTPUT,
                                   @out2 = @out2 OUTPUT

            SELECT @out1 AS out1, @out2 AS out2";

$stmt = $conn->prepare($myquery);
$stmt->execute();
$stmt->getWrappedStatement()->nextRowset();
$myOutputs = $stmt->fetchAll();

Upvotes: 0

Ben Shoemaker
Ben Shoemaker

Reputation: 143

ZLK was correct in that it was returning multiple rowsets.
However, you cannot call ->nextRowset() directly from the DBAL statement. You have to get the underlying PDO statement, and call nextRowset() on that

$statement->getWrappedStatement()->nextRowset();
$newIDNum = $statement->fetchAll();

Upvotes: 1

Related Questions