捕获从 Postgresql 到 PHP 的错误

catching errors from Postgresql to PHP

提问by woryzower

I want to catch and show the error(in a way that i choose) of a query on the web page using php . So instead of the code below

我想使用 php 在网页上捕获并显示查询的错误（以我选择的方式）。所以而不是下面的代码

$result=pg_query($connection,$query);

if($result){
    //success

}
else{
    echo pg_last_error($connection);
}

can i use a method like error code matching or something else to achieve things like

我可以使用诸如错误代码匹配之类的方法或其他方法来实现诸如

if(error equals duplicate value error){
 echo "this value already exists";
}
else if(error equals different type error){
 echo "You should enter wrong type for column blabla"
}

Note I am using postgresql

注意我使用的是 postgresql

回答by Daniel Vérité

It's possible to retrieve the desirable standard SQLSTATEerrcode, but there's a trick: the query has to be sent through the asynchronous pg_send_query()instead of the synchronous pg_query(). This is because pg_query()returns falseon error instead of the resource that is required to peek at the error details.

可以检索所需的标准SQLSTATE 错误代码，但有一个技巧：必须通过异步pg_send_query()而不是同步发送查询pg_query()。这是因为pg_query()返回false错误而不是查看错误详细信息所需的资源。

When pg_get_result()is called after pg_send_query, it will block anyway until the query is done, so it does not really complicate things compared to the synchronous case. And it returns a result which can be fully exploited for precise error handling.

当pg_get_result()在 之后调用时pg_send_query，它无论如何都会阻塞，直到查询完成，因此与同步情况相比，它并没有真正使事情复杂化。它返回一个结果，可以充分利用该结果进行精确的错误处理。

Example:

例子：

if (pg_send_query($db, $query)) {
  $res=pg_get_result($db);
  if ($res) {
    $state = pg_result_error_field($res, PGSQL_DIAG_SQLSTATE);
    if ($state==0) {
      // success
    }
    else {
      // some error happened
      if ($state=="23505") { // unique_violation
        // process specific error
      }
      else {
       // process other errors
      }
    }
  }  
}

Also, if the argument passed to pg_querymight contain several SQL statements(separated by semicolons), the above example should be extended to retrieve all results in a loop, as mentioned in the comment by @user1760150. Compared to pg_querywhich returns only the last result, pg_get_resultin a loop gives access to the results of each statement of the combined query.

此外，如果传递给的参数pg_query可能包含多个 SQL 语句（以分号分隔），则应扩展上述示例以在循环中检索所有结果，如@user1760150 的评论中所述。与pg_query仅返回最后一个结果相比，pg_get_result在循环中可以访问组合查询的每个语句的结果。

回答by j0k

You should parse the return of pg_last_errorto know the type of error. So I would go to sth like this :

您应该解析返回的信息pg_last_error以了解错误类型。所以我会这样：

$result = pg_query($connection,$query);

if($result)
{
  //success
}
else
{
  $error = pg_last_error($connection);

  // you need to adapt this regex
  if (preg_match('/duplicate/i', $error))
  {
    echo "this value already exists";
  }
  // you need to adapt this regex
  elseif(preg_match('/different type/i', $error))
  {
    echo "You should enter wrong type for column blabla"
  }
  else
  {
    // different error
  }
}

回答by Richard Huxton

You canget access to SQLSTATE through the two main drivers.

您可以通过两个主要驱动程序访问 SQLSTATE。

http://uk3.php.net/manual/en/function.pg-result-error-field.php

http://uk3.php.net/manual/en/function.pg-result-error-field.php

http://www.php.net/manual/en/pdo.errorcode.php

http://www.php.net/manual/en/pdo.errorcode.php