PDO usage guide: Using multiple queries to operate Sql Server

Question

I want to execute some queries that don't return a result set, and then execute a real query and get its results. Here's an example that doesn't work:

<?php

try {
    $db = new PDO('dblib:host=myhost;dbname=master','user','password');


    $query = "declare @entier int = 1;";
    $db->exec($query);
    $query = "select @entier;";
    $stmt = $db->query($query);
    $rows = $stmt->fetchAll();
    print_r($rows);
}
catch (PDOException $e) {
    print ($e->getMessage());
}
catch (Exception $e) {
    print ($e->getMessage());
}

?>

This code doesn’t work either:

try {
    $db = new PDO('dblib:host=myhost;dbname=master','user','password');

    $query = "declare @entier int = 1; select @entier;";
    $stmt = $db->query($query);
    $rows = $stmt->fetchAll();
    print_r($rows);
}
catch (PDOException $e) {
    print ($e->getMessage());
}
catch (Exception $e) {
    print ($e->getMessage());
}

?>

But this code works:

<?php

try {
    $db = new PDO('dblib:host=myhost;dbname=master','user','password');

    $query = "select 1;";
    $stmt = $db->query($query);
    $rows = $stmt->fetchAll();
    print_r($rows);
}
catch (PDOException $e) {
    print ($e->getMessage());
}
catch (Exception $e) {
    print ($e->getMessage());
}

?>

Thank you for your help

Answer 1

I know this is old, but for anyone else who finds this question via Google: You need to use PDOStatement::nextRowset to iterate over result sets from multiple queries.

However, in some versions, there seemed to be memory issues when using nextRowset and dblib (in my case, 94Tb of memory was tried allocated...), so I ended up redesigning to avoid multiple SQL queries altogether (Instead, the value declared where it is used is copied).