sqlsrv_query对于长查询不返回任何行，也不返回任何错误

Question

我有一个异常长的查询(对于我们的系统来说是13秒以上(，在使用sqlsrv_query时没有返回任何结果，但当我在服务器上直接输入SQL查询时，会给我331行。sqlsrv_num_rows返回0，sqlsrv_errors为空。

<?php
$connInfo = array( "Database"=>"xxx", "UID"=>"xxx", "PWD"=>"xxx", "CharacterSet" => "UTF-8");
$conn = sqlsrv_connect('SQL2008', $connInfo);
if ($conn)
{
$sql_q = "SELECT TblOrder.FldJobNb, TblOrder.FldOrdCre As DateReception, TblOrder.FldReclamerDate As DateDebutPORev, TblOrder.FldPOReviewApprovedDate As DateFinPORev,
TblOrder.FldPrinted, capsule_order.temps_reception_planification As DateReceptionPLANIF, TblOrder.FldPriced,
CASE
WHEN ISNULL(TblOrder.FldContractReviewCompletedDate, 0) = 0
THEN capsule_order.temps_reception_planification
ELSE TblOrder.FldContractReviewCompletedDate
END As TempsFinRevue,
(SELECT TOP 1 TblOrderXFeredNotifications.FldDate FROM [TCS].[dbo].[TblOrderXFeredNotifications] WHERE TblOrderXFeredNotifications.FldOrdID = TblOrder.FldOrdID ORDER BY TblOrderXFeredNotifications.FldNoLigne) As DatePlanification,
TblOrder.FldXfer2Sched, TblOrder.FldOrdMod As DateDernierMod, TblOrder.FldOrdStatusDate As DateDernierStatut, TblOrder.FldOrdReq As DateBesoin
FROM [TCS].[dbo].[TblOrder] RIGHT JOIN [TCS].[dbo].[capsule_order] ON TblOrder.FldJobNB = capsule_order.FldJobNB
WHERE '" . $dateDebut->format('Y-m-d H:i:s.').'000' . "' <= TblOrder.FldOrdCre AND TblOrder.FldOrdCre <= '" . $dateFin->format('Y-m-d H:i:s.').'000' . "'";
// die($sql_q);  Query that I enter into SQL Server and gives me 331 results after 13secs
$query= sqlsrv_query($conn, $sql_q, array(), array( "Scrollable" => 'static' ));
if ($query)
{
if (sqlsrv_num_rows($query) > 0)
{
while ($result= sqlsrv_fetch_array($query))
{
// ...
}
}
else
{ die(var_dump(sqlsrv_num_rows($query))); }
}
else
{ die("query".$sql_q.'<br>'.print_r( sqlsrv_errors(), true)); }
}
else
{ die ("Connection défectueuse."); } ?>

非常感谢您的支持，

雷诺

Answer 1

您可以考虑以下内容：

• 使用明确的datetime格式(yyyy-mm-ddThh:mm:ss.zzz(将datetime值传递给SQL Server(此处总结了我的经验(
• 不要连接字符串来生成语句。始终使用准备好的语句和参数化查询来防止SQL注入。使用PHP Driver for SQL Server，函数sqlsrv_query()同时完成语句准备和语句执行，并可用于执行参数化查询
• 如果要检查当前语句的结果集是否有一行或多行，可以使用sqlsrv_has_rows()而不是sqlsrv_num_rows()，并使用默认的(forward(光标

以下示例基于您的代码，是解决问题的可能方案：

<?php
$connInfo = array( "Database"=>"xxx", "UID"=>"xxx", "PWD"=>"xxx", "CharacterSet" => "UTF-8");
$conn = sqlsrv_connect('SQL2008', $connInfo);
if ($conn)
{
$sql_q = "
SELECT 
TblOrder.FldJobNb, 
TblOrder.FldOrdCre As DateReception, 
TblOrder.FldReclamerDate As DateDebutPORev, 
TblOrder.FldPOReviewApprovedDate As DateFinPORev,
TblOrder.FldPrinted, capsule_order.temps_reception_planification As DateReceptionPLANIF, TblOrder.FldPriced,
CASE
WHEN ISNULL(TblOrder.FldContractReviewCompletedDate, 0) = 0 THEN capsule_order.temps_reception_planification
ELSE TblOrder.FldContractReviewCompletedDate
END As TempsFinRevue,
(
SELECT TOP 1 TblOrderXFeredNotifications.FldDate 
FROM [TCS].[dbo].[TblOrderXFeredNotifications] 
WHERE TblOrderXFeredNotifications.FldOrdID = TblOrder.FldOrdID 
ORDER BY TblOrderXFeredNotifications.FldNoLigne
) As DatePlanification,
TblOrder.FldXfer2Sched, 
TblOrder.FldOrdMod As DateDernierMod, 
TblOrder.FldOrdStatusDate As DateDernierStatut, 
TblOrder.FldOrdReq As DateBesoin
FROM [TCS].[dbo].[TblOrder] 
RIGHT JOIN [TCS].[dbo].[capsule_order] ON TblOrder.FldJobNB = capsule_order.FldJobNB
WHERE ? <= TblOrder.FldOrdCre AND TblOrder.FldOrdCre <= ?
";
$query = sqlsrv_query(
$conn, 
$sql_q, 
array($dateDebut->format('Y-m-dTH:i:s').'.000', $dateFin->format('Y-m-dTH:i:s').'.000')
);
if ($query)
{
if (sqlsrv_has_rows($query))
{
while ($result= sqlsrv_fetch_array($query))
{
// ...
}
}
else
{ die(var_dump(sqlsrv_num_rows($query))); }
}
else
{ die("query".$sql_q.'<br>'.print_r( sqlsrv_errors(), true)); }
}
else
{ die ("Connection défectueuse."); } 
?>