Determining which query caused exception in try block?

[NotionCommotion]

I have the following queries where foo.x, bar.x, and foobar.bla are foreign keys.  The JavaScript client "should" only pass valid data, so I expect it is rare that an exception will occur.

 

How best to determine which query caused the exception in the try block?

try {
    $this->db->beginTransaction();
    $stmt=$this->db->prepare('INSERT INTO foo(id,x,y,z) VALUES(0,?,?,?)');
    $stmt->execute($foo);
    $foo_id=$this->db->lastInsertId();

    $stmt=$this->db->prepare('INSERT INTO bar(id,x,y,z) VALUES(0,?,?,?)');
    $stmt->execute($bar);
    $bar_id=$this->db->lastInsertId();

    $stmt=$this->db->prepare('INSERT INTO foobar(foo_id,bar_id,bla) VALUES(?,?,?)');
    $stmt->execute([$foo_id,$bar_id,$bla]);

    $this->db->commit();
    $rs=['status'=>true,'foo_id'=>$foo_id,'bar_id'=>$bar_id];
}
catch(PDOException $e){
    $this->db->rollBack();
    $rs=['status'=>false,'error'=>'???'];
}

[Jacques1]

Why do you need to know that? Are you using the database system to validate the input? Don't do that. The input should be validated before you use it for any queries.

 

And to give you a literal answer: By not putting everything into one try statement.

[NotionCommotion]

Why do you need to know that? Are you using the database system to validate the input? Don't do that. The input should be validated before you use it for any queries.

 

And to give you a literal answer: By not putting everything into one try statement.

 

Yes, I am using the database system to validate the input.  Why not?  It is validated client side, and as such I expect only exceptions due to malicious behavior.

 

I recognize that one can use a separate try statement for each query, but it doesn't help for code conciseness and readability.  I was thinking of just placing a string $cause_of_error='Foo caused the error!'; before each statement.

 

Thanks

[Jacques1]

Yes, I am using the database system to validate the input.

 

That's a bad idea. First off, you'll have a hard time figuring out if the error was actually caused by wrong input (as opposed to a server-side error), and it will be even more difficult to tell what exactly is wrong. Secondly, that's just not the job of the database system. Validation happens in the application, right after receiving the input.

 

The statement “The input might be malicious, so I send it straight to the database system to see if anything blows up” also worries me. Sure, you appearently use prepared statements (hopefully not emulated ones), but don't push your luck.

[NotionCommotion]

I agree with your point about making it more difficult to identify errors.  Also, no emulated prepared statements here (learned that from you).

 

If not as I showed, what would possible be a good use of PDO exceptions?

[Jacques1]

If not as I showed, what would possible be a good use of PDO exceptions?

 

99% of the time, you shouldn't do anything with exceptions.

 

Catching exceptions is only useful in edge cases like testing the uniqueness of a value with a UNIQUE constraint or manually rolling back a failed transaction in a database system that doesn't do it automatically (MySQL does).

[benanamen]

set_exception_handler is your friend here. @Jaques1 schooled me on this one.

 

http://php.net/manual/en/function.set-exception-handler.php