mercredi 26 avril 2017

Issues with MySQL transactions and variables

I'm trying to set up a process where my team can do some routine DB changes using variables and a basic script. I'm only on the first of many operations and can't even get it to execute. The idea is you assign the variables at the top of the script and say if you want to run the script in test or prod mode.

test should just mock the changes, show you what would've happened and roll the transaction back while prod will actually commit the transaction and show you the updated rows.

When I run these lines individually they work, but altogether it keeps saying syntax error. I would love help debugging this but also if anyone has a better solution to this problem I'm wide open to hearing it.

Ok here's the starting code, thanks in advance!

#ADD A NEW QUESTION CATEGORY
SET @new_category_name = 'NEW CATEGORY NAME HERE';
SET @prod_or_test = 'PROD';

START TRANSACTION;
#SET IT'S SORT_ORDER TO BE AT THE END OF THE LIST
SET @last_sort_order = ((SELECT MAX(`sort_order`) FROM question_categories)+1);

#INSERT THE NEW ROW
INSERT INTO question_categories (name, sort_order) VALUES (@new_category_name, @last_sort_order);
IF (@prod_or_test = 'PROD') THEN
  COMMIT;
  SELECT * FROM question_categories WHERE name = @new_category_name;
ELSE
  ROLLBACK;
END IF;

Aucun commentaire:

Enregistrer un commentaire