0

I'd like to execute some queries that doesn't return result set, and then execute a real query, and fetch its result. Here is an exemple 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 neither doesn't work : 

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());
}

?>

Thanks for your help

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Emmanuel Averty
  • 11
  • 2

3 Answers3

1

I know this is old, but for other people finding this from Google: you need to use PDOStatement::nextRowset to iterate over the result sets from your multiple queries.

However, it seems there are memory issues when using nextRowset with dblib in some versions (it tried to allocate 94Tb in my case...), so I ended up re-engineering to avoid multiple SQL queries altogether (instead duplicating the value of the DECLARE where it was being used).

Ben
  • 11
  • 2
0

PDO::query docs (http://php.net/manual/it/pdo.query.php) say

PDO::query() executes an SQL statement in a single function call, returning the result set (if any) returned by the statement as a PDOStatement object.

This could mean that you can execute with query() both queries with and without result

Phate01
  • 2,499
  • 2
  • 30
  • 55
  • Thank you for your quick answer, I've modified my question to be more specific : the query string : «declare @entier int = 1; select @entier;» doesn't return anything. – Emmanuel Averty Jan 29 '15 at 16:24
0

Seems that pdo_dblib query() only allow ONE sql statement which can be a query with or without result, but it does not support TWO sql statements.

River
  • 1
  • 1