php PDO::FETCH_ASSOC doesnt detect select after backup in stored procedure

Question

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

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

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;
    }

Attributes of 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;
        }
    }

And I get this error:

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

And I expect : Answer: 1

Answer 1

Explanations:

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

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

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

Solution 1:

Stored procedure:

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;
?>

Solution 2:

Stored procedure:

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;
?>