2
\$\begingroup\$

I would like to know if this is the correct way to use try/catch when dealing with multiple MySQL queries and PDO:

try {
    $create = $con[$_POST['user-selected-connection']]->query("...");
    KD::notice('success','table successfully created');
    try {
        $insert = $con[$_POST['user-selected-connection']]->query("...");
        KD::notice('success','table was populated successfully');
    } catch(PDOException $e) {
        KD::notice('error','was unabled to insert records'.$e->getMessage());
    }
} catch(PDOException $e) {
    KD::notice('error','The table was not created.'.$e->getMessage());
}

This is part of a get started wizard in my application, and this is the part where the user needs to create tables in order to proceed. That can be done in two ways; either copy/paste the provided SQL query manually into their MySQL handler (e.g. phpMyAdmin), or by just clicking a button if they have created a user with CREATE privileges in the previous step. (The previous step, which is "set up database, and add users into the application", tells them the benefits of having a super user for future tasks).

Anyway, I need to use try/catch since the user has the ability to chose which user they want to use when creating the tables. And if the selects user doesn't have the required privileges, a fatal error message is returned. That's how I figured I needed to use try/catch to handle the fatal error, and notify that the user didn't have access.

I do this task in two stages to make sure that the required records actually gets inserted after the table is created. (had a few test runs where the table was created, and the records wasn't inserted when in the same query).

Is this an appropriate way to do this?

If the create part fails, will it jump to catch right away, or will it still try to execute the insert query before throwing the error?

\$\endgroup\$
2
\$\begingroup\$

Exception handlers that don't either handle exceptions or rethrow them, generally shouldn't exist.

An exception signals that a breakage has occurred -- one serious enough that the thrower thinks that the app should die. Because you've asked to catch the exception, it's now your job to deal with the problem -- and that means more than just logging it or printing a message or whatever. That means cleaning up at least your part of the mess, making things right again, deciding what to return, etc.

If the code can't safely recover, then rethrow, or throw your own exception -- or, if there's nothing it can clean up, don't catch at all.

(If you were using a real database, i would have suggested creating the table in a transaction to make cleanup more feasible. Unfortunately, MySQL is severely gimped transactionwise; CREATE TABLE causes an implicit commit, so rolling back isn't really an option.)


Aside from that, since you don't do any cleanup, there's not much point to the nesting. You could have one block that has all the actions, and the first error will abort the process. (If you want different errors for each step, then you could have a "current step" variable you update as you step through the process. As an added bonus, i find it makes the code a bit more self-documenting.

In other words, i might suggest something like

# because i hate repeating this ugliness
$db = $con[$_POST['user-selected-connection']];

try {
    $state = 'creating table';
    $create = $db->query("CREATE TABLE...");
    KD::notice('success','table successfully created');

    $state = 'populating table';
    $insert = $db->query("INSERT...");
    KD::notice('success','table was populated successfully');
}
catch(PDOException $e) {
    KD::notice('error',"While $state: " . $e->getMessage());
    throw $e;
}
\$\endgroup\$
  • \$\begingroup\$ I thought innoDB could handle transactions. But that doesn't work when creating tables? \$\endgroup\$ – ThomasK Oct 4 '14 at 10:55
  • \$\begingroup\$ Right. See dev.mysql.com/doc/refman/5.7/en/implicit-commit.html : "The CREATE TABLE statement in InnoDB is processed as a single transaction. This means that a ROLLBACK from the user does not undo CREATE TABLE statements the user made during that transaction." \$\endgroup\$ – cHao Oct 4 '14 at 15:29
  • \$\begingroup\$ Sure MySQL has its issues, but creating a transaction for the INSERT statement, catching the exception, rolling back the insert and attempting a DROP TABLE is one way around the issue \$\endgroup\$ – Elias Van Ootegem Oct 6 '14 at 11:13
  • \$\begingroup\$ @EliasVanOotegem: Failures don't always happen due to broken SQL. If you lost the connection, for example, you can't exactly drop the table telepathically. :) So you still have to clean up by hand before you can try again. (Just up and dropping the table at the beginning of the process is trouble waiting to happen, IMO. Particularly since it can't be rolled back if things go wacky.) \$\endgroup\$ – cHao Oct 6 '14 at 13:55
  • \$\begingroup\$ And if you're going to drop the table as part of handling the exception, you don't really need a transaction. It doesn't matter how many rows are in the table when you drop it. \$\endgroup\$ – cHao Oct 6 '14 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.