Query Error Handling in MySQL Transaction Statement

Question

I am taking a Database Design & Development course, and I just learned about SQL transactions. The textbook (Learning SQL 3rd Edition by Alan Beaulieu) is really good at explaining how things work, but isn't very good at giving examples of the actual code that we would need to use.

In the textbook, pretty much the only place where it talks about the error handling in a transaction, is an example of a transaction statement, but it is only in pseudocode:

START TRANSACTION;

/* withdraw money from first account, making sure balance is sufficient */
UPDATE account SET avail_balance = avail_balance - 500
WHERE account_id = 9988
  AND avail_balance > 500;

IF <exactly one row was updated by the previous statement> THEN
  /* deposit money into second account */
  UPDATE account SET avail_balance = avail_balance + 500
    WHERE account_id = 9989;

  IF <exactly one row was updated by the previous statement> THEN
    /* everything worked, make the changes permanent */
    COMMIT;
  ELSE
    /* something went wrong, undo all changes in this transaction */
    ROLLBACK;
  END IF;
ELSE
  /* insufficient funds, or error encountered during update */
  ROLLBACK;
END IF;

In MySQL workbench (the software we use to test our queries), I tried to use a CASE statement instead of the IF (since MySQL uses CASE instead of IF), but it would give me an error that says:

"CASE" is not valid at this position, expecting EOF, ALTER...

I tried looking up how to do it, but everything I found was either not for MySQL (Oracle Database or Microsoft SQL Server), or it was just as (not) helpful as the textbook was...

Any help would be appreciated!

Answer 1

The way you control the flow will depend on the client, which is why the textbook examples you have are in pseudocode. I do not think there is any way to control flow in SQL scripts in query tabs of MySQL Workbench, but I may be wrong.

You could certainly create a stored procedure to encapsulate the example transaction listed in your original post -

CREATE PROCEDURE `sp_TransferFunds`(
    IN amount DECIMAL(12,2),
    IN sendingAccount INTEGER,
    IN receivingAccount INTEGER
)
sp:BEGIN
    /* do some validation of the input parameters */
    IF amount <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Amount to be transferred must be greater than zero';
        LEAVE sp;
    ELSEIF sendingAccount = receivingAccount THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Sending and receiving accounts must be different';
        LEAVE sp;
    END IF;

    START TRANSACTION;

    /* withdraw money from first account, making sure balance is sufficient */
    UPDATE account
        SET avail_balance = avail_balance - amount
    WHERE account_id = sendingAccount
    AND avail_balance >= amount;

    SET @tx1 = ROW_COUNT();

    IF (@tx1 = 1) THEN
        /* deposit money into second account */
        UPDATE account
            SET avail_balance = avail_balance + amount
        WHERE account_id = receivingAccount;

        SET @tx2 = ROW_COUNT();
    
        IF (@tx2 = 1) THEN
            /* everything worked, make the changes permanent */
            COMMIT;
        ELSE
            /* something went wrong, undo all changes in this transaction */
            ROLLBACK;
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Something went wrong!';
        END IF;
    ELSE
        /* insufficient funds, or error encountered during update */
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds in sendingAccount!';
    END IF;
END

If this is not helpful and you have a more specific example of what you are trying to achieve, please update your original post.