4

I am trying to get up and running with stored procedures in Zend Framework 2. I tried to return an error code with an out parameter in the stored procedure, but I have been unable to make that work. Then I thought that when an error occurred, I could just catch an exception in PHP. The problem is that I can't seem to get access to the specific error code - only a general one (e.g. 23000 - integrity constraint violation). Here is an example of what I want to do (or similar):

try {
    $result = $this->dbAdapter->query('CALL sp_register_user(?, ?)', array('username', 'password'));
}

catch (\Exception $e) {
    switch ($e->getCode()) {
        case 1062:
            // Duplicate entry
            break;

        case 1452:
            // Cannot add or update a child row
            break;
    }
}

That is, I would like to be able to check exactly which error occurred. The problem, though, is that the exception that is thrown has an error code of 23000 and not one of the above.

An InvalidQueryException is thrown in Zend\Db\Adapter\Driver\Pdo\Statement on line 220:

try {
    $this->resource->execute();
} catch (\PDOException $e) {
    throw new Exception\InvalidQueryException('Statement could not be executed', null, $e);
}

The PDOException here contains an error code of 23000 in my case. The null parameter is the error code. So in my catch block, I will actually be catching an InvalidQueryException with an error code of 0, which is not all that useful. This exception does provide me access to previous exceptions (the last parameter above), which would be the PDOException, like this:

// try block omitted
catch (InvalidQueryException $e) {
    $previous_exception_error_code = $e->getPrevious()->getCode();
}

On duplicate entry (1062), the error code is 23000. For a "cannot add or update a child row" (1452), it would also be 23000. Therefore I am not sure how I can distinguish between them in my application. What if I wanted to present different error messages to the user for the two errors? The exception message is: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'user' for key 'username'. This is a string, though, so searching for the error code would just be too hacky. I am hoping to be able to still abstract away from the fact that I am using PDO as the concrete implementation.

In the end, this seems more about PDO and MySQL than ZF2. I am not really sure where to go from here, so any ideas on how I can distinguish between the error codes would be much appreciated. That is, to know when an error 1062 or 1452 occurred rather than just 23000.

Community
  • 1
  • 1
ba0708
  • 10,180
  • 13
  • 67
  • 99

2 Answers2

6

You want to switch on $e->errorInfo[1], which is the driver-specific error code (instead of the standardised SQLSTATE value).

eggyal
  • 122,705
  • 18
  • 212
  • 237
  • 1
    D'oh! I saw that while debugging, but I got fooled because it doesn't seem to have a getter method. Thanks a lot - it works. :-) – ba0708 Jan 21 '13 at 22:52
  • 1
    @andy124 there are methods to get at that data, but on the relevant PDO and PDOStatement classes, not on the exception itself. – cmbuckley Jan 21 '13 at 23:00
  • @cbuckley Yes, I was actually looking at those. What I meant was that I didn't find a getter method on the exception. Getting to those classes through ZF2 seemed a bit confusing at first, so this approach seemed easier. Thank you for the heads up - I appreciate it. – ba0708 Jan 22 '13 at 00:16
0

may be it will help some body-> sometimes the try catch block is not efficient read here the comments

and here the solution.

Eran Or
  • 1,252
  • 15
  • 22
  • While this link may answer the question, it is better to include the essential parts of the answer [here](http://meta.stackexchange.com/a/8259) and provide the link for reference. Link-only answers can become invalid if the linked page changes. – bummi Dec 01 '13 at 12:15