jeudi 17 mars 2016

No output from SQL Server 2005 T-SQL and PDO when using SET

I am connecting to a SQL Server 2005 database using PDO and don't get output when running certain types of queries using PHP, but I do when running the queries directly in SQL Server Management Studio.

When I run this:

$conn = new PDO(...);

$statement = $conn->prepare("
    DECLARE @testvar VARCHAR(10)
    SELECT 'hello world'
");

$result = $statement->execute();

echo $result ? "Success" : "Failure";
echo "<br>";

print_r($conn->errorInfo());
echo "<br>";

die(print_r($statement->fetchAll()));

I get output.

Success
Array ( [0] => 00000 [1] => 0 [2] => (null) [0] (severity 0) [] [3] => 0 [4] => 0 ) 
Array ( [0] => Array ( [] => hello world ) ) 1

However, if I change the query to set @testvar to something, either inline:

DECLARE @testvar VARCHAR(10) = 'test'
SELECT 'hello world'

Or by using SET:

DECLARE @testvar VARCHAR(10)
SET @testvar = 'test'
SELECT 'hello world'

I no longer get "hello world" when running $statement->fetchAll(), and there does not seem to be an error:

Success
Array ( [0] => 00000 [1] => 0 [2] => (null) [0] (severity 0) [] [3] => 0 [4] => 0 ) 
Array ( ) 1

When I try all three of the queries above directly in SQL Server Management Studio, all three of them work and return "hello world" correctly.

Aucun commentaire:

Enregistrer un commentaire