PHP sqlsrv PDOStatement::rowCount

Question

在这段代码中，rowCount()有一个问题：

$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()){

它将打印-1，但粪便正常工作，并返回行，因此代码的其余部分工作正常，只有rowCount()，我不明白为什么。

谢谢

Answer 1

我假设您正在为Server使用(基于问题的标题和注释中的链接)，而且您似乎需要在PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE => PDO::SQLSRV_CURSOR_BUFFERED调用中使用PDO::prepare()选项。

这在文档中有简要的解释:您可以通过使用PDO：：then，指定PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL游标类型，然后指定PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE => PDO::SQLSRV_CURSOR_BUFFERED来请求客户端游标。

我能够使用以下代码(基于问题中的代码)再现(并解决)这一意外行为：

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

一个重要的注意事项--始终在语句中使用参数以防止可能出现的SQL注入问题：

<?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();   
...

?>