PHP - PDOException is not thrown on error, an empty array returned (SQL Server)

Question

If I understand right I'm using following stack: PHP <-> PDO <-> MS SQL DBLIB <-> freetds <-> MS SQL Server.

$pdo = new \PDO('dblib:host=192.168.0.10:1433;dbname=MyDb;charset=UTF-8', 'mydb', 'secret', [
    \PDO::ATTR_CASE => \PDO::CASE_NATURAL,
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_ORACLE_NULLS => \PDO::NULL_NATURAL,
    \PDO::ATTR_STRINGIFY_FETCHES => false,
]);
$statement = $pdo->prepare("EXEC [dbo].[sp_MyStoredProcedure] 1, 2, 3");
if ( ! $statement->execute()) {
    throw new \ErrorException('Error executing sp_MyStoredProcedure');
}
$data = $statement->fetchAll(\PDO::FETCH_ASSOC);

I'm setting ERRMODE to ERRMODE_EXCEPTION, and there is an error 515 happens in stored procedure, but no exception is thrown, why? $data is storing empty array and I was considering this as proper result of executing stored procedure. To overcome this issue I've added check $statement->errorInfo()[1], through I'm not sure if it proper to check error in this way:

if ( ! $statement->execute() || $statement->errorInfo()[1]) {
    throw new \ErrorException('Error executing sp_MyStoredProcedure');
}

Do I'm doing this check properly?

Detailed $statement->errorInfo():

array (
  0 => '00000',
  1 => 515,
  2 => 'General SQL Server error: Check messages from the SQL Server [515]  (severity 16) [(null)]',
  3 => -1,
  4 => 16,
)

Also according to article error 515 is an error of inserting NULL into column which is NOT NULL. But why I don't see this error when executing EXEC [dbo].[sp_MyStoredProcedure] 1, 2, 3 in Microsoft SQL Server Management Studio or tsql (freetds-bin)?

You may need to iterate over multiple resultsets. Try to run a loop from [here](https://phpdelusions.net/pdo#call) and see whether it throws a PDOException? — Your Common Sense, Sep 13 '16 at 10:39
I have tried, but `$statement->nextRowset()` always returns `false`, so I'm assuming I have only single rowset. Maybe the behavior he describes only exists in MySQL, I'm using stored procedures of SQL Server. — happy_marmoset, Sep 13 '16 at 10:57

score 1 · Accepted Answer · answered Nov 25 '16 at 13:19

I had a similar problem.

Solution - explicitly specify null-ability for temporary tables columns in your stored procedure.

Before:

create table #resultsTable(
    paging_id int,
    pt_key bigint,
    pt_ctkeyfrom int
)

After:

create table #resultsTable(
    paging_id int NULL,
    pt_key bigint NULL,
    pt_ctkeyfrom int NULL
)

For me the problem was resolved.

Unfortunately I do not know how to solve without changing the procedure code.

score -1 · Answer 2 · answered Sep 13 '16 at 10:07

To see your exceptions, you need to catch them when they are thrown. Your code does not listen those PDOException because you miss the try-catch statement.

Here is an example of error checking when dealing with methods throwing exceptions :

$pdo = null;

try {
    $pdo = new \PDO('dblib:host=192.168.0.10:1433;dbname=MyDb;charset=UTF-8', 'mydb', 'secret', [
        \PDO::ATTR_CASE => \PDO::CASE_NATURAL,
        \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
        \PDO::ATTR_ORACLE_NULLS => \PDO::NULL_NATURAL,
        \PDO::ATTR_STRINGIFY_FETCHES => false,
    ]);

    $statement = $pdo->prepare("EXEC [dbo].[sp_MyStoredProcedure] 1, 2, 3");

    $data = $statement->fetchAll(\PDO::FETCH_ASSOC);
}
catch( PDOException $e ) {
    print_r( $pdo->errorInfo() );   //  PDO::errorInfo() returns an array
                                    //  [0] : sql error code
                                    //  [1] : driver error code
                                    //  [2] : error message
}

You see that you do not have to throw any custom exceptions, you have to catch them relative to their nature (PDO throws PDOException).

You may eventually throw your own exceptions, but you will need to catch them on another block of code. Here is an example that fits your original code :

$pdo = null;

try {
    $pdo = new \PDO('dblib:host=192.168.0.10:1433;dbname=MyDb;charset=UTF-8', 'mydb', 'secret', [
        \PDO::ATTR_CASE => \PDO::CASE_NATURAL,
        \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
        \PDO::ATTR_ORACLE_NULLS => \PDO::NULL_NATURAL,
        \PDO::ATTR_STRINGIFY_FETCHES => false,
    ]);

    $statement = $pdo->prepare("EXEC [dbo].[sp_MyStoredProcedure] 1, 2, 3");

    if ( ! $statement->execute()) {
        throw new \ErrorException('Error executing sp_MyStoredProcedure');
    }

    $data = $statement->fetchAll(\PDO::FETCH_ASSOC);
}
catch( PDOException $e ) {
    print_r( $pdo->errorInfo() );   //  PDO::errorInfo() returns an array
                                    //  [0] : sql error code
                                    //  [1] : driver error code
                                    //  [2] : error message
}
catch( ErrorException $e ) {
    print_r( $e );
}

I'm using debugger and executing my code in step by step, so I would see if it is thrown, but it is not. — happy_marmoset, Sep 13 '16 at 10:10
You are wrong. You don't need to catch exceptions when they are thrown. — Your Common Sense, Sep 13 '16 at 10:33
@YourCommonSense php throws an error if a `try` is not followed by a `catch` statement. — Anwar, Sep 13 '16 at 11:14

PHP - PDOException is not thrown on error, an empty array returned (SQL Server)

2 Answers2

Linked