PHP sqlsrv PDOStatement::rowCount

I have a problem with the rowCount() in this code:

$sqlLoc= "
          DECLARE @Data2 AS DATE; 
          SET @Data2 = CONVERT(DATE,CONVERT(date, '$dataFine'), 102);
          
          DECLARE @Data1 AS DATE;
          SET @Data1 = CONVERT(DATE,CONVERT(date, '$dataInizio'), 102);
          
          SELECT noteincassi.CodLocale,Insegna,Citta
          FROM [Edera].[dbo].[NoteIncassi]
          JOIN edera.dbo.AnagraficaLocali ON AnagraficaLocali.CodLocale=NoteIncassi.CodLocale
          WHERE DataIncasso >= @Data1  AND DataIncasso <= @Data2 AND tipoincasso = '6' AND sospeso = 0
          GROUP BY noteincassi.CodLocale,insegna,Citta
          ORDER BY Insegna";
$queryLoc = $conn->prepare($sqlLoc,array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$queryLoc->execute();

echo $numero=$queryLoc->rowCount();

echo "<h1>MEDIA INCASSI DAL ".date('d-m-Y',strtotime($dataInizio))." AL ".date('d-m-Y',strtotime($dataFine))."</h1>";

$id=0;
while($resultLoc=$queryLoc->fetch()){

It will print -1, but the fecth is working and rows are returned, so the rest of the code is working fine, it’s only the rowCount() and I don’t understand why.

Thank you

Answer

I assume that you are using PHP Driver for SQL Server (based on the question’s title and the link in the comments) and it seems that you need the PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE => PDO::SQLSRV_CURSOR_BUFFERED option in the PDO::prepare() call.

This is briefly explained in the documentation: You can request a client-side cursor by using PDO::prepare, specifying the PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL cursor type, and then specifying PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE => PDO::SQLSRV_CURSOR_BUFFERED.

I’m able to reproduce (and solve) this unexpected behaviour using the following code (based on the code in the question):

$sql = "
    DECLARE @Data2 AS DATE; 
    SET @Data2 = GETDATE();
    
    DECLARE @Data1 AS DATE;
    SET @Data1 = GETDATE();

    SELECT A, B
    FROM (VALUES (11, 22), (33, 44)) v (A, B)
";
$statement = $conn->prepare(
    $sql, 
    array(
        PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL, 
        PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE => PDO::SQLSRV_CURSOR_BUFFERED
    )
);
$statement->execute();
echo $numero = $statement->rowCount();

One important note – always use parameters in your statement to prevent possible SQL injection issues:

<?php

... 
$sqlLoc = "
    DECLARE @Data2 AS DATE; 
    SET @Data2 = CONVERT(DATE, CONVERT(date, :dataFine), 102);
    
    DECLARE @Data1 AS DATE;
    SET @Data1 = CONVERT(DATE, CONVERT(date, :dataInizio), 102);
    
    SELECT noteincassi.CodLocale, Insegna,Citta
    FROM [Edera].[dbo].[NoteIncassi]
    JOIN edera.dbo.AnagraficaLocali ON AnagraficaLocali.CodLocale=NoteIncassi.CodLocale
    WHERE DataIncasso >= @Data1  AND DataIncasso <= @Data2 AND tipoincasso = '6' AND sospeso = 0
    GROUP BY noteincassi.CodLocale,insegna,Citta
    ORDER BY Insegna
";
$statement = $conn->prepare(
   $sql, 
   array(
      PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL, 
      PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE => PDO::SQLSRV_CURSOR_BUFFERED
   )
);
$queryLoc->bindParam(':dataFine', $dataFine);
$queryLoc->bindParam(':dataInizio', $dataInizio);
$queryLoc->execute();   
...

?>