CODEWITHSUNDEEP
mysqlsqlexceptiontransactions
heisenberg
heisenberg

Reputation: 1954

MySQL Catching Exceptions in Transactions

What is the best way to return the cause of an error within a transaction in mysql stored procedure?

Here's one stored procedure I created. But I noticed that if I commit an error on the syntax, say for instance I forget to put the WHERE clause in one of the update statements, it would only return the SELECT 'error' but not the actual cause why the error occurred.

Is there any better way to catch errors just like in Java where you just put

catch(SQLException e){
JOptionPane.showMessageDialog(null,e.getError()+"\n"+e.getMessage() );
}

I'd appreciate any example or best practices. 

CREATE DEFINER=`root`@`localhost` PROCEDURE `updateCurriculumDetails`(p_curriculumId INT, p_editedByUserId INT, p_curriculumName varchar(50), p_description VARCHAR(200), p_gradeLevelId INT, p_schoolYearId INT)
BEGIN

    DECLARE hasError BOOLEAN DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR sqlexception SET hasError = 1;


    START TRANSACTION;

        UPDATE curriculum_dt c
            SET     c.`name`        = p_curriculumName, 
                    c.description   = p_description,
                    c.gradeLevelId  = p_gradeLevelId 
            WHERE   c.id            = p_curriculumId;

        UPDATE curriculum_schoolyears_dt cs
            SET     cs.schoolYearId = p_schoolYearId
            WHERE   cs.curriculumId = p_curriculumId;

        UPDATE curriculum_hd SET editedBy_UserId = p_editedByUserId,
        dateLastEdited = NOW()
            WHERE curriculumId = p_curriculumId;


    IF hasError THEN
        ROLLBACK;
        SELECT 'error';
    ELSE
        COMMIT;
        SELECT 'successfully updated' AS result;

    END IF;

END

Thanks in advance.

Upvotes: 2

Views: 2869

Answers (1)

Michael - sqlbot
Michael - sqlbot

Reputation: 179394

Use of a CONTINUE HANDLER is entirely inappropriate here. You're allowing the rest of the queries to run after an error occurs, which is, at best, a waste of time and resources, as you are executing queries you already know will be rolled back. Instead, something like this seems like a better plan:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  ROLLBACK;
  RESIGNAL;
END;

The best practice, however, is not to allow the procedure to control the transaction. If the calling session already holds a transaction, START TRANSACTION; inside the procedure will silently and implicitly commit it, leaving the caller in the unenviable position of being unable to rollback and unaware of the condition.

Upvotes: 1

Related Questions