以下代码按预期工作(假设变量存在):
$connectionInfo = ['Database'=>$dbName, 'UID'=>$username, 'PWD'=>$pwd, 'ReturnDatesAsStrings'=>true, 'CharacterSet'=>'UTF-8'];
$conn = sqlsrv_connect($server, $connectionInfo);
$select = sqlsrv_query($conn, 'SELECT * INTO #mytable_temp FROM mytable WHERE myfield = \'myvalue\'', []);
$select2 = sqlsrv_query($conn, 'SELECT * FROM #mytable_temp ', []);
if (!$select2) {
$errors = sqlsrv_errors();
var_dump($errors);
} else {
$res = sqlsrv_fetch_array($select2, SQLSRV_FETCH_ASSOC);
var_dump($res);
}
但是,如果我将$select更改为以下内容,它将不起作用:
$select = sqlsrv_query($conn, 'SELECT * INTO #mytable_temp FROM mytable WHERE myfield = ?', ['myvalue']);
在运行第二个语句时,我得到了一个错误,该语句显示“无效的对象名'#mytable_temp”。为什么使用参数绑定会导致临时表不可见?
我知道,如果在同一个sqlsrv_query()语句中包含这两个语句,就可以让它工作,但对于我的用例来说,这不是一个选项。我也知道如果使用全局(##mytable_temp)表,它也可以工作,但这也不是一个选择。
我正在运行PHP5.4.12,并在SQL Server11.0.3 (2012 SP1)和10.50.4000 (2008 SP2)上尝试了代码。
发布于 2015-06-30 10:40:26
为了补充Vladimir的答案,以下是跟踪结果(PHP 5.6.9,MSSQL 2014 Express)。
当您不添加任何参数时
$select = sqlsrv_query($conn, 'SELECT * INTO #mytable_temp FROM mytable WHERE myfield = \'myvalue\'', []);
$select2 = sqlsrv_query($conn, 'SELECT * FROM #mytable_temp ', []);
PHP将向MSSQL发送普通命令:
SELECT * INTO #mytable_temp FROM mytable WHERE myfield = 'myvalue'
SELECT * FROM #mytable_temp
当您添加参数时
$select = sqlsrv_query($conn, 'SELECT * INTO #mytable_temp FROM mytable WHERE myfield = ?', ['myvalue']);
$select2 = sqlsrv_query($conn, 'SELECT * FROM #mytable_temp ', []);
然后,PHP将使用sp_executesql
exec sp_executesql N'SELECT * INTO #mytable_temp FROM mytable WHERE myfield = @P1',N'@P1 nvarchar(6)',N'myvalue'
SELECT * FROM #mytable_temp
https://stackoverflow.com/questions/30893902
复制相似问题