带有MSSQL的PHP​​ PDO不会重用预准备语句

I have PHP 5.3 with the pdo_sqlsrv extension and a MSSQL2008 database. The following code retrieves data from the table with prepared statement, everything works fine. However, when I repeat the statement several times, in the SQL server profile I can see that the prepared statement is not reused. On every SQL request, the statement is prepared again.

我有PHP 5.3与pdo_sqlsrv扩展和MSSQL2008数据库。以下代码使用预准备语句从表中检索数据,一切正常。但是,当我多次重复该语句时,在SQL服务器配置文件中,我可以看到准备好的语句不会被重用。在每个SQL请求上,都会再次准备该语句。

    $this->statement = $connection->prepare("SELECT Lang,MemoryArea,ID,TextType,TextValue FROM CoreLanguage WHERE TextType = :textType;");
    $this->statement->bindValue(":textType", "URL");
    $this->statement->execute();
    $data = $this->statement->fetchAll(PDO::FETCH_ASSOC) or array();
    $this->statement->closeCursor();


    $this->statement->bindValue(":textType", "SYSTEM");
    $this->statement->execute();
    $data = $this->statement->fetchAll(PDO::FETCH_ASSOC) or array();
    $this->statement->closeCursor();

The reason for this behaviour could be in different string lengths of the variable $textType. As you can see in the profiler list, the same statement is prepared twice with different input parameter data type (nvarchar(6) and nvarchar(3)).

这种行为的原因可能是变量$ textType的不同字符串长度。正如您在分析器列表中看到的那样,使用不同的输入参数数据类型(nvarchar(6)和nvarchar(3))准备两次相同的语句。

SQL Server Profiler log entry of those data requests:

这些数据请求的SQL Server Profiler日志条目:

declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,N'@P1 nvarchar(3)',N'SELECT Lang,MemoryArea,ID,TextType,TextValue FROM CoreLanguage WHERE TextType = @P1;',N'URL'
select @p1

declare @p1 int
set @p1=3
exec sp_prepexec @p1 output,N'@P1 nvarchar(6)',N'SELECT Lang,MemoryArea,ID,TextType,TextValue FROM CoreLanguage WHERE TextType = @P1;',N'SYSTEM'
select @p1

If the input string data is always the same length, the statement is reused by the SQL server. However, that will rarely be the case...

如果输入字符串数据的长度始终相同,则SQL Server将重用该语句。但是,很少会出现这种情况......

Does anyone know why the SQL server does not reuse the already prepared statement? Or how I could manipulate the input parameter @P1 to a suitable data type, for example nvarchar(40) (same as the TextType column definition)?

有谁知道SQL服务器为什么不重用已经准备好的语句?或者我如何将输入参数@ P1操作为合适的数据类型,例如nvarchar(40)(与TextType列定义相同)?

Maybe there's a driver specific option that I missed?

也许我错过了一个特定于驱动程序的选项?

1 个解决方案

#1

Did you tried with bindParam() instead of bindValue()? Maybe you can try with that.

你尝试过bindParam()而不是bindValue()吗?也许你可以试试。