0

I have a prepared query with 3 result sets. I used fetch() for the first two since they only have one row each and they are appearing correctly in the View. For the 3rd result set, it consists of several transaction rows and I used fetchAll(), but it is empty, nothing appears in the view.

I tried modifying the proc to only return this transaction list, so in my code, I only called fetchAll(), but it is still empty. I've also tried running the EXEC statement in MS SQL Management Studio, and I can confirm that the 3rd query was supposed to return rows. I also didn't use fetchColumn() anywhere in the code. rowCount() returns 0.

I am now stuck. Please help.

Code snippet below:

    try {
        $conn = new PDO($dsn,$un,$pw);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    catch (PDOException $e) {
        print("Error connecting to SQL Server.");
        die(print_r($e));
    }

    $stmt = $conn->prepare("EXEC SomeStoredProc ?,?,?");
    $stmt->execute([$id,$fromDate,$toDate]);
    $firstResult_ = $stmt->fetch(PDO::FETCH_ASSOC);
    $stmt->nextRowset();
    $secondResult = $stmt->fetch(PDO::FETCH_ASSOC);
    $stmt->nextRowset();
    $transactions = $stmt->fetchall(PDO::FETCH_ASSOC);

    $sample = $stmt->rowCount(); //returns 0
Zhorov
  • 28,486
  • 6
  • 27
  • 52
user2805659
  • 13
  • 2
  • 1
    You could [check for errors](https://www.php.net/manual/en/pdostatement.errorinfo.php). – KIKO Software Feb 09 '23 at 09:51
  • 1
    SQL Server transactions? As in your stored procedure is modifying data, not just selecting it? Does it have `SET NOCOUNT ON;` so that it's not sending rowcounts back to PDO for any delete/insert/update operations? – AlwaysLearning Feb 09 '23 at 12:34
  • @KIKOSoftware [It's already checked](https://www.php.net/manual/en/pdo.error-handling.php): When PDO::ERRMODE_EXCEPTION is set, *PDO will throw a PDOException and set its properties to reflect the error code and error information. This setting is useful during debugging, as it will effectively "blow up" the script at the point of the error, very quickly pointing a finger at potential problem areas in your code* – Your Common Sense Feb 09 '23 at 15:11
  • @KIKOSoftware - No errors are displayed. – user2805659 Feb 09 '23 at 23:05
  • @Zhorov - I assign value from the request: $fromDate = $request->input('req.fromDate'); $toDate = $request->input('req.toDate'); I'm using sql server – user2805659 Feb 09 '23 at 23:06
  • @AlwaysLearning - It's not modifying data, it just selects multiple tables and outputs them. And yes, it has SET NOCOUNT ON; As I mentioned, the SP is returning values for the first 2 tables. – user2805659 Feb 09 '23 at 23:10
  • It's not evident from your question that `SET NOCOUNT ON` was in effect. It's totally possible for SSMS to display three result sets on the Results tab but show eleventy-billion "(N row(s) affected)" lines on the Messages tab due to non-select operations. – AlwaysLearning Feb 10 '23 at 04:10

0 Answers0