以下代码按预期工作(假设变量存在):
$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) 表它可以工作,但这也不是一个选项。
我正在运行PHP 5.4.12,并在SQL Server 11.0.3(2012 SP1)和10.50.4000(2008 SP2)上尝试了代码。
我对为什么在使用参数的SELECT INTO
查询后看不到临时表的解释。
请考虑以下 T-SQL 代码(使用如下所示创建和填充MyTable
):
DECLARE @stmt nvarchar(max) = 'SELECT * INTO #mytable_temp FROM mytable WHERE myfield = @P1';
EXECUTE sp_executesql @stmt, N'@P1 varchar(50)', @P1 = 'Value1';
如果在 SSMS 中运行它,它运行良好,"消息"窗口中的输出显示:
(2 row(s) affected)
尝试在同一 SSMS 窗口中向上述代码添加一行并运行整个批处理:
DECLARE @stmt nvarchar(max) = 'SELECT * INTO #mytable_temp FROM mytable WHERE myfield = @P1';
EXECUTE sp_executesql @stmt, N'@P1 varchar(50)', @P1 = 'Value1';
SELECT * FROM #mytable_temp;
输出为:
(2 row(s) affected)
Msg 208, Level 16, State 0, Line 3
Invalid object name '#mytable_temp'.
原因是带有参数的语句由嵌套存储过程范围内的sp_executesql
执行,并且在存储过程中创建的临时表对此存储过程的调用方不可见。
执行sp_executeSql以选择...#table 但无法选择临时表数据
https://msdn.microsoft.com/en-us/library/ms174979.aspx
删除在存储过程中创建的本地临时表 存储过程完成时自动执行。该表可以是 由存储执行的任何嵌套存储过程引用 创建表的过程。该表不能由 引用 调用创建表的存储过程的进程。
当你准备一个带有参数的SQL语句时,PHP最终会调用sp_executesql
(很可能,尽管我没有跟踪它)。你会得到这个记录的行为 - 一个临时表作为查询的一部分在此存储过程中创建,并在sp_executesql
返回时立即删除。当您运行不带参数的 SQL 语句时,PHP 会按原样将其发送到服务器,而不使用 sp_executesql
。
想到
的解决方法很少。
将多个 SQL 语句放入一个长字符串中,并使用一次调用来运行它
sqlsrv_query
。创建一个带有参数的存储过程,并在其中放置几个 SQL 语句,然后通过一次调用
sqlsrv_query
来调用您的过程。(我个人更喜欢这种方法)。显式创建(并选择性地删除)临时表。
这是我用来验证最后一个解决方法是否有效的代码。已通过 PHP 5.4.28、SQL Server Express 2014 Microsoft PHP SQLSRV 3.2 驱动程序进行验证。它使用额外的CREATE TABLE
语句显式创建临时表,然后使用INSERT INTO
而不是单个SELECT INTO
语句。
创建测试表并填充一些数据
CREATE TABLE [dbo].[MyTable](
[ID] [int] NOT NULL,
[MyField] [varchar](50) NOT NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[ID] ASC
))
INSERT INTO [dbo].[MyTable] ([ID],[MyField]) VALUES
(1, 'Value1'),
(2, 'Value2'),
(3, 'Value3'),
(4, 'Value1')
运行 php 脚本
$connectionInfo = array("Database" => "tempdb");
$conn = sqlsrv_connect($serverName, $connectionInfo);
if ($conn)
{
echo "Connection established.'n";
}
else
{
echo "Connection could not be established.'n";
die( print_r( sqlsrv_errors(), true));
}
echo "Running CREATE TABLE ...'n";
$sql_create = "CREATE TABLE #mytable_temp([ID] [int] NOT NULL, [MyField] [varchar](50) NOT NULL)";
$stmt_create = sqlsrv_query($conn, $sql_create);
if( $stmt_create === false )
{
echo "CREATE TABLE failed'n";
die( print_r( sqlsrv_errors(), true));
}
else
{
echo "CREATE TABLE result set:'n";
while ($row = sqlsrv_fetch_array($stmt_create))
{
var_dump($row);
}
}
sqlsrv_free_stmt($stmt_create);
echo "Running INSERT INTO with param ...'n";
$select_into = "INSERT INTO #mytable_temp(ID, MyField) SELECT ID, MyField FROM MyTable WHERE MyField = ?";
$search = "Value1";
$params = array
(
array(&$search, SQLSRV_PARAM_IN)
);
$stmt_into = sqlsrv_query($conn, $select_into, $params);
if( $stmt_into === false )
{
echo "INSERT INTO failed'n";
die( print_r( sqlsrv_errors(), true));
}
else
{
echo "INSERT INTO result set:'n";
while ($row = sqlsrv_fetch_array($stmt_into))
{
var_dump($row);
}
}
sqlsrv_free_stmt($stmt_into);
echo "Running SELECT FROM ...'n";
$select_from = "SELECT * FROM #mytable_temp";
$stmt_from = sqlsrv_query($conn, $select_from);
if( $stmt_from === false )
{
echo "SELECT FROM failed'n";
die( print_r( sqlsrv_errors(), true));
}
else
{
echo "SELECT FROM result set:'n";
while ($row = sqlsrv_fetch_array($stmt_from))
{
var_dump($row);
}
}
echo "end'n";
脚本的输出
Connection established.
Running CREATE TABLE ...
CREATE TABLE result set:
Running INSERT INTO with param ...
INSERT INTO result set:
Running SELECT FROM ...
SELECT FROM result set:
array(4) {
[0]=>
int(1)
["ID"]=>
int(1)
[1]=>
string(6) "Value1"
["MyField"]=>
string(6) "Value1"
}
array(4) {
[0]=>
int(4)
["ID"]=>
int(4)
[1]=>
string(6) "Value1"
["MyField"]=>
string(6) "Value1"
}
end
为了补充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