php PDO :: FETCH_ASSOC在存储过程中备份后未检测到选择 [英] php PDO::FETCH_ASSOC doesnt detect select after backup in stored procedure

问题描述

我有一个存储过程，该过程进行备份，然后在出现错误或类似这样的错误时返回1或0:

I have a stored procedure which make a backup and then return 1 or 0 if there is an error or not something like this:

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值.

In sql it works and return the correct value of @answer.

在php中，当我尝试获取查询的执行时，它什么都找不到.

In php when I try to fetch the execution of the query It doesnt find anything.

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]:查询的活动结果不包含任何字段."

r: "SQLSTATE[IMSSP]: The active result for the query contains no fields."

我希望:答案:1

推荐答案

说明:

发生错误的原因是BACKUP DATABASE返回参考消息，并且您的存储过程具有多个结果集.我用测试脚本重现了您的错误(尽管在您的问题中我看不到您如何准备存储过程).您可以尝试以下解决方案之一(我编写了简单的脚本，可以轻松地在函数中实现这些脚本.)

The reason for your error is that BACKUP DATABASE returns informational messages and your stored procedure has multiple result sets. I reproduced your error with a test script (although in your question I can't see how you prepare your stored procedure). You may try with one of the following solutions (I've made simple scripts, which you can easily implement in your functions).

• 致电 PDOStatement :: nextRowset 来获取每个结果集(在这种情况下为两次附加调用)

• call PDOStatement::nextRowset to fetch each result set (two additional calls in this case)

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

use an OUTPUT parameter in your stored procedure. In this case you need to fetch all result sets and after that to get the value of the output parameter.

解决方案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   = 'server\instance,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   = 'server\instance,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;
?>

这篇关于php PDO :: FETCH_ASSOC在存储过程中备份后未检测到选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！