PHP-错误时未引发PDOException，返回空数组(SQL Server) [英] PHP - PDOException is not thrown on error, an empty array returned (SQL Server)

问题描述

如果我理解正确，我正在使用以下堆栈:PHP<-> PDO<-> MS SQL DBLIB<-> freetds<-> MS SQL Server.

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

我将ERRMODE设置为ERRMODE_EXCEPTION，并且存储过程中发生错误 515 ，但是没有引发异常，为什么? $data正在存储空数组，我认为这是执行存储过程的正确结果.为了克服这个问题，我添加了检查$statement->errorInfo()[1]，通过这种方式我不确定是否应该通过以下方式检查错误:

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

我检查是否正确?

详细的$ statement-> errorInfo():

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

还根据文章错误 515 是将NULL插入NOT NULL列的错误.但是为什么在Microsoft SQL Server Management Studio或tsql(freetds-bin)中执行EXEC [dbo].[sp_MyStoredProcedure] 1, 2, 3时为什么看不到此错误?

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

推荐答案

我遇到了类似的问题.

解决方案-为存储过程中的临时表列明确指定空功能.

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

之前:

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

之后:

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.

这篇关于PHP-错误时未引发PDOException，返回空数组(SQL Server)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！