php PDO::FETCH_ASSOC 在存储过程中备份后不会检测选择

Question

>我有一个存储过程，它可以进行备份，如果出现错误或不是这样的错误，则返回 1 或 0：

Create procedure [dbo].[sp_IWBackup]
as
begin
declare @route varchar(500),  @answer int = 0
set nocount on
set @route = 'I:route'+(replace((replace(convert(varchar, getdate(), 21),':',';')),'.',';'))+'Full.bak'
set @answer = 1
begin try
backup database databasename to disk = @route
end try
begin catch
set @answer = 0
end catch
select @answer as answer
end

在 sql 中，它可以工作并返回正确的 @answer 值。

在 php 中，当我尝试获取查询的执行时，它没有找到任何东西。

public function ExecuteSelectAssoc($sth)
    {
        $r=array('data'=>false,
        'error'=>false,
        'r'=>array());
        try {
            $sth->execute();
            while ($row=$sth->fetch(PDO::FETCH_ASSOC)) {  //error here
                $r['data'] = true;
                $keys = array_keys($row);
                $tmp = array();
                foreach($keys as $key)
                {
                    $tmp[$key] = $row[$key];
                }
                array_push($r['r'], $tmp);
            }
        } catch (PDOException $e) {
            $r['error']=true;
            $r['r'] = $e->getMessage();
        }
        return $r;
    }

PDO的属性

public function connectSqlSrv(){
        try{
            $dbCnx = new PDO("sqlsrv:Server=$this->server;Database=$this->db", $this->usr, $this->psw);
            $dbCnx->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            return $dbCnx;
        }
        catch(PDOException $e){
            echo $e;
            die();
            return null;
        }
    }

我收到此错误：

r："SQLSTATE[IMSSP]：查询的活动结果不包含任何字段。

我期望 ： 答案： 1

Answer 1

解释：

出错的原因是BACKUP DATABASE返回信息性消息，并且存储过程具有多个结果集。我用测试脚本重现了您的错误(尽管在您的问题中我看不到您如何准备存储过程(。您可以尝试使用以下解决方案之一(我制作了简单的脚本，您可以在函数中轻松实现(。

• 调用 PDOStatement：：nextRowset 以获取每个结果集(在本例中为两个额外的调用(

• 在存储过程中使用 OUTPUT 参数。在这种情况下，您需要获取所有结果集，然后获取输出参数的值。

解决方案 1：

存储过程：

create procedure [dbo].[sp_IWBackup]
as
begin
    declare 
       @route varchar(500),  
       @answer int = 0
    set nocount on
    set @route = 'I:route'+(replace((replace(convert(varchar, getdate(), 21),':',';')),'.',';'))+'Full.bak'
    set @answer = 1
    begin try
        backup database databasename to disk = @route
    end try
    begin catch
        set @answer = 0
    end catch
    select @answer as answer
end

.PHP：

<?php
$server   = 'serverinstance,port';
$database = 'database';
$uid      = 'uid';
$pwd      = 'pww';
try {
    $dbh = new PDO("sqlsrv:server=$server;Database=$database", $uid, $pwd);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch( PDOException $e ) {
    die("Error connecting to SQL Server. ".$e->getMessage());
}
try {
    $sql = "{CALL sp_IWBackup}";
    $stmt = $dbh->prepare($sql);
    $stmt->execute();
    $stmt->nextRowset();
    $stmt->nextRowset();
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        foreach ($row as $key => $value) {
            echo $key.": ".$value."<br>";
        };
    }
} catch( PDOException $e ) {
    die( "Error executing stored procedure: ".$e->getMessage());
}
$stmt = null;
$dbh = null;
?>

解决方案 2：

存储过程：

create procedure [dbo].[sp_IWBackupOut]
    @answer int OUTPUT
as
begin
    declare 
        @route varchar(500)
    set nocount on
    set @route = 'I:route'+(replace((replace(convert(varchar, getdate(), 21),':',';')),'.',';'))+'Full.bak'
    set @answer = 1
    begin try
        backup database databasename to disk = @route
    end try
    begin catch
        set @answer = 0
    end catch
end

.PHP：

<?php
$server   = 'serverinstance,port';
$database = 'database';
$uid      = 'uid';
$pwd      = 'pww';
try {
    $dbh = new PDO("sqlsrv:server=$server;Database=$database", $uid, $pwd);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch( PDOException $e ) {
    die("Error connecting to SQL Server. ".$e->getMessage());
}
try {
    $sql = "{CALL sp_IWBackupOut (?)}";
    $stmt = $dbh->prepare($sql);
    $answer = -1;
    $stmt->bindParam(1, $answer, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, PDO::SQLSRV_PARAM_OUT_DEFAULT_SIZE);
    $stmt->execute();
    do {
    } while ($stmt->nextRowset());
    echo "answer: ".$answer;
} catch( PDOException $e ) {
    die( "Error executing stored procedure: ".$e->getMessage());
}
$stmt = null;
$dbh = null;
?>