SQL Transactions and Error Handling: COMMIT, ROLLBACK

# What is error handling? Error handling decides what happens when a statement fails: undo the half-done work, save what went wrong, and tell the caller. Why you need it: without it, a failed procedure can leave half an order in the database, and nobody knows what happened or why. - `transaction` keeps a group of changes all or nothing - `handler` the block that runs when an error happens - `error log` a table where the real error is saved for you to read later This level builds all three, starting with what goes wrong without them. > In an Appian project, procedures report errors through an OUT parameter, so the interface can show a friendly message.

# When a procedure fails halfway Without error handling, MySQL stops at the statement that fails, and everything before it stays done. Here the order is inserted, then the payment fails: half an order is left behind. - `LAST_INSERT_ID()` the id the database just gave the new row - `--` a comment, ignored by the database > Any procedure with more than one INSERT, UPDATE or DELETE needs a plan for this. The next slides build one.

CREATE PROCEDURE place_order(
  IN p_customer INT,
  IN p_pizza INT,
  IN p_qty INT)
BEGIN
  -- this runs:
  INSERT INTO orders
    (customer_id, pizza_id,
     qty, status)
  VALUES (p_customer, p_pizza,
    p_qty, 'new');
  -- this fails:
  INSERT INTO payments
    (order_id, amount)
  VALUES (LAST_INSERT_ID(), NULL);
END

# Transactions: all or nothing Emma wants to swap her pizza. That takes two changes: cancel her old order 8, then add the new order. The second change fails, because pizza 99 isn't on the menu. Without a transaction, the first change stays done: her old order is cancelled and there is no new one, so Emma gets nothing. - `START TRANSACTION` the changes from here on belong together - `COMMIT` they all worked: keep them - `ROLLBACK` one of them failed: undo all of them, as if none had happened So order 8 is still on the way, and Emma still gets her pizza. > Change ROLLBACK to COMMIT and run it: order 8 stays cancelled. Use a transaction whenever two changes only make sense together.

START TRANSACTION;
-- change 1 works:
UPDATE orders
SET status = 'cancelled'
WHERE id = 8;
-- change 2 fails, no pizza 99:
-- INSERT INTO orders
--   (customer_id, pizza_id,
--    qty, status)
-- VALUES (5, 99, 1, 'new');
-- so undo change 1:
ROLLBACK;
SELECT id, customer_id, status
FROM orders WHERE id = 8

# Catch errors: EXIT HANDLER In an Appian project, a procedure reports problems through OUT parameters instead of failing. - `OUT p_error_message` stays NULL when everything worked - `DECLARE EXIT HANDLER FOR SQLEXCEPTION` if any statement fails, run this block, then leave the procedure - `ROLLBACK` inside the handler, undoes the half-done work Run the CALL: there's no customer 99, so the INSERT fails and the handler takes over. Then change 99 to 1 and run it again. > Set the result to NULL in the handler, so the caller can never mistake a failed call for a successful one.

CREATE PROCEDURE place_order(
  IN p_customer INT,
  IN p_pizza INT,
  IN p_qty INT,
  OUT p_order_id INT,
  OUT p_error_message VARCHAR(255))
BEGIN
  DECLARE EXIT HANDLER
    FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SET p_order_id = NULL;
    SET p_error_message =
      'Something went wrong';
  END;

  START TRANSACTION;
  INSERT INTO orders
    (customer_id, pizza_id,
     qty, status)
  VALUES (p_customer, p_pizza,
    p_qty, 'new');
  SET p_order_id = LAST_INSERT_ID();
  COMMIT;
  SET p_error_message = NULL;
END

# Business rules: IF … THEN … ELSE A business rule isn't a database error: the data is fine, but the app should say no. - `IF p_qty < 1 THEN` when the rule is broken - `ELSE` otherwise - `END IF` the end of the IF A quantity under 1 sends back a message. Otherwise the order is inserted, COMMIT keeps it, and the message stays NULL. > Check the rules before START TRANSACTION. Then there's nothing to undo when a rule fails.

IF p_qty < 1 THEN
  SET p_order_id = NULL;
  SET p_error_message =
    'Quantity must be at least 1';
ELSE
  START TRANSACTION;
  INSERT INTO orders (customer_id,
    pizza_id, qty, status)
  VALUES (p_customer, p_pizza,
    p_qty, 'new');
  SET p_order_id = LAST_INSERT_ID();
  COMMIT;
  SET p_error_message = NULL;
END IF;

# Log the real error The friendly message is for the user. For you, save the real error. - `GET DIAGNOSTICS CONDITION 1` reads the error that just happened - `MYSQL_ERRNO` `MESSAGE_TEXT` its number and its text - `v_code` `v_msg` local variables that hold them - `NOW()` the current date and time ROLLBACK first, then INSERT into error_log, then COMMIT. Run the CALL and look at the newest rows in error_log. > If you log before the ROLLBACK, the rollback deletes your log row too.

CREATE PROCEDURE place_order(
  IN p_customer INT,
  IN p_pizza INT,
  IN p_qty INT,
  OUT p_order_id INT,
  OUT p_error_message VARCHAR(255))
BEGIN
  DECLARE v_code INT;
  DECLARE v_msg VARCHAR(255);
  DECLARE EXIT HANDLER
    FOR SQLEXCEPTION
  BEGIN
    GET DIAGNOSTICS CONDITION 1
      v_code = MYSQL_ERRNO,
      v_msg = MESSAGE_TEXT;
    ROLLBACK;
    INSERT INTO error_log
      (procedure_name, error_code,
       error_message, logged_at)
    VALUES ('place_order', v_code,
      v_msg, NOW());
    COMMIT;
    SET p_order_id = NULL;
    SET p_error_message =
      'Something went wrong';
  END;

  START TRANSACTION;
  INSERT INTO orders
    (customer_id, pizza_id,
     qty, status)
  VALUES (p_customer, p_pizza,
    p_qty, 'new');
  SET p_order_id = LAST_INSERT_ID();
  COMMIT;
  SET p_error_message = NULL;
END

# Read the error log error_log is an ordinary table, so a plain SELECT works on it. - `DATE_SUB(NOW(), INTERVAL 1 DAY)` this time yesterday > When users report a problem, check the error log first. The real error message is usually there.

SELECT procedure_name,
  error_code, error_message,
  logged_at
FROM error_log
WHERE logged_at >= DATE_SUB(NOW(),
  INTERVAL 1 DAY)

# Watch a CALL fail CALL place_order(99, 1, 2, @order_id, @error), and there is no customer 99. Step by step: - `START TRANSACTION` the work begins - `INSERT INTO orders` fails: error 1452, the foreign key - `EXIT HANDLER` takes over: GET DIAGNOSTICS, ROLLBACK, log the error, COMMIT, set the OUT values - `@order_id` `@error` come back NULL and 'Something went wrong' > After a failed CALL, orders is unchanged and error_log has one new row. Check both when you test a handler.

CREATE PROCEDURE place_order(
  IN p_customer INT,
  IN p_pizza INT,
  IN p_qty INT,
  OUT p_order_id INT,
  OUT p_error_message VARCHAR(255))
BEGIN
  DECLARE v_code INT;
  DECLARE v_msg VARCHAR(255);
  DECLARE EXIT HANDLER
    FOR SQLEXCEPTION
  BEGIN
    GET DIAGNOSTICS CONDITION 1
      v_code = MYSQL_ERRNO,
      v_msg = MESSAGE_TEXT;
    ROLLBACK;
    INSERT INTO error_log
      (procedure_name, error_code,
       error_message, logged_at)
    VALUES ('place_order', v_code,
      v_msg, NOW());
    COMMIT;
    SET p_order_id = NULL;
    SET p_error_message =
      'Something went wrong';
  END;

  START TRANSACTION;
  INSERT INTO orders
    (customer_id, pizza_id,
     qty, status)
  VALUES (p_customer, p_pizza,
    p_qty, 'new');
  SET p_order_id = LAST_INSERT_ID();
  COMMIT;
  SET p_error_message = NULL;
END

# Log like a real project Real projects often log every failure to a process log, with the start time and the end time, so you can also see how long it ran. - `DECLARE v_start DATETIME DEFAULT NOW()` remembers when the procedure started - `v_` local variables, not `@` ones: an @ variable lasts for the whole connection, and Appian reuses connections - `RETURNED_SQLSTATE` the five-character error code, next to MYSQL_ERRNO - `CONCAT(…)` glues the pieces into one line of text - `FOR SQLEXCEPTION, SQLWARNING` one handler for both, no copy-pasted block - `RESIGNAL` after logging, pass the original error on to the caller > With SQLWARNING in an EXIT handler, even a harmless warning stops the procedure. Without RESIGNAL, Appian sees a successful call with no rows.

CREATE PROCEDURE order_report()
BEGIN
  DECLARE v_state CHAR(5)
    DEFAULT '00000';
  DECLARE v_errno INT;
  DECLARE v_msg VARCHAR(1000);
  DECLARE v_proc VARCHAR(100)
    DEFAULT 'order_report';
  DECLARE v_start DATETIME
    DEFAULT NOW();

  DECLARE EXIT HANDLER
    FOR SQLEXCEPTION, SQLWARNING
  BEGIN
    GET DIAGNOSTICS CONDITION 1
      v_state = RETURNED_SQLSTATE,
      v_errno = MYSQL_ERRNO,
      v_msg = MESSAGE_TEXT;
    INSERT INTO process_log
      (process_name, start_time,
       end_time, description)
    VALUES (v_proc, v_start, NOW(),
      CONCAT('Error ', v_errno,
        ', SQL state ', v_state,
        ': ', v_msg));
    COMMIT;
    RESIGNAL;
  END;

  -- the report query goes here
END

# Calling it from Appian The OUT parameters come back in fv!parameters. The procedure finished, even when it refused the order, so onSuccess runs: check p_error_message there. - `onSuccess` the call finished; read the OUT values - `onError` the call itself broke, like a timeout or a missing procedure > A refused order is still a successful call. Always check p_error_message in onSuccess, not only onError.

a!executeStoredProcedureOnSave(
  dataSource: cons!PIZZA_DB,
  procedureName: "place_order",
  inputs: {
    a!storedProcedureInput(
      name: "p_customer",
      value: local!customerId),
    a!storedProcedureInput(
      name: "p_pizza",
      value: local!pizzaId),
    a!storedProcedureInput(
      name: "p_qty",
      value: local!qty)
  },
  onSuccess: if(
    a!isNullOrEmpty(
      fv!parameters.p_error_message
    ),
    a!save(local!orderId,
      fv!parameters.p_order_id),
    a!save(local!error,
      fv!parameters.p_error_message)
  ),
  onError: a!save(local!error,
    "Could not save. Try again.")
)

# Debugging a procedure - `CALL place_order(99, 1, 2, @order_id, @error)` run it again with the values that failed - `SELECT @order_id, @error` read the OUT values - `SELECT 'after insert', …` print values between steps while you debug - `SHOW CREATE PROCEDURE` see the version the database is really running > Check SHOW CREATE PROCEDURE first. Very often the database is running an older version than the one you're editing.

CALL place_order(99, 1, 2,
  @order_id, @error);
SELECT @order_id, @error;

-- inside the procedure, while you
-- debug:
SELECT 'after insert',
  LAST_INSERT_ID();

SHOW CREATE PROCEDURE place_order;