NotionCommotion Posted August 5, 2016 Share Posted August 5, 2016 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'=>'???']; } Quote Link to comment Share on other sites More sharing options...
Jacques1 Posted August 5, 2016 Share Posted August 5, 2016 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. Quote Link to comment Share on other sites More sharing options...
NotionCommotion Posted August 5, 2016 Author Share Posted August 5, 2016 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 Quote Link to comment Share on other sites More sharing options...
Jacques1 Posted August 5, 2016 Share Posted August 5, 2016 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. Quote Link to comment Share on other sites More sharing options...
NotionCommotion Posted August 5, 2016 Author Share Posted August 5, 2016 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? Quote Link to comment Share on other sites More sharing options...
Jacques1 Posted August 5, 2016 Share Posted August 5, 2016 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). Quote Link to comment Share on other sites More sharing options...
benanamen Posted August 5, 2016 Share Posted August 5, 2016 set_exception_handler is your friend here. @Jaques1 schooled me on this one. http://php.net/manual/en/function.set-exception-handler.php Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.