php pdo mssql 声明插入选择

Question

我正在尝试使用 PHP PDO:SQLSRV 或 PHP SQLSRV 访问 MS SQL 服务器。

对于 PHP PDO:SQLSRV，我使用以下代码:

<?php

    try {
        $db = new PDO("sqlsrv:Server=sql_server;Database=database", "username", "password");
        $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

        $test = $db->prepare('declare @t2 code.instancereferencestable;
                              insert into @t2 select top 10 [Instance Reference] from imported.instance where [Instance Type] = \'CENAS\' and [Instance Reference] = \'CENAS.8000\' order by [Instance (Id)] desc;
                              select * from transformed.attribute_instance_extended_from_references(@t2)');
        $test->execute();
        $test->fetch(PDO::FETCH_OBJ);

        echo "Connected!". "\n";
    }
    catch(Exception $e) {
            echo "Error: " . $e->getMessage() . "\n";
    }

?>

对于 PHP SQLSRV，我使用以下代码:

<?php

    $serverName = "sql_server";
    $connectionOptions = array(
        "Database" => "database",
        "Uid" => "username",
        "PWD" => "password"
    );

    $conn = sqlsrv_connect($serverName, $connectionOptions);
    if ( $conn ) {
        echo "Connection established.".PHP_EOL;

        $sql = ("declare @t2 code.instancereferencestable;
                insert into @t2 select top 10 [Instance Reference] from imported.instance where [Instance Type] = 'CENAS' and [Instance Reference] = 'CENAS.8000' order by [Instance (Id)] desc;
                select * from transformed.attribute_instance_extended_from_references(@t2);");

        $stmt = sqlsrv_query($conn, $sql);
        if ( $stmt === false ) {
               die( print_r( sqlsrv_errors(), true));
        } else {
            $row = sqlsrv_fetch_array($stmt); 
            print_r ($row);
        }

    } else {
            echo "Connection could not be established.".PHP_EOL;
            die( print_r( sqlsrv_errors(), true)).PHP_EOL;
    }

?>  

我从 PHP PDO:SQLSRV 得到的输出是:错误:SQLSTATE[IMSSP]:查询的事件结果不包含任何字段。

并且从 PHP SQLSRV 我没有输出。

我直接在 SQL Server Management Studio 中运行相同的 SQL 代码，并且有输出。

谁能帮忙!？提前致谢!

Answer 1

一个简单的解决方案可能是创建一个存储过程并调用它。

您也可以尝试使用 php_mssql.dll 模块。此处信息:http://php.net/manual/es/book.mssql.php