0

Trying to craft a transaction from the code to send to MySQL.

START TRANSACTION

INSERT INTO EMPLOYEE (`firstname`,`lastname`)
 VALUES('john','Doe');

SET EMP_ID = last_insert_id();
 //do some inserts here that fail

COMMIT;

Now, when the other inserts into child tables fail I see that just the first insert being committed. TO avoid that I need to capture error and set rollback on error being triggered. When I include the declare statements for error it just fails saying

"Declare is not valid at this position. expecting EOF ;" Any pointers would be greatly appreciated

START TRANSACTION

DECLARE error BOOL DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;

....other code
2
  • "If some statment fails should not commit this", I need help figuring out how to implement this. Lets assume I made an insert to an employee detail table that violated null constraint. It still inserts john doe into the employee table. Was under the impression that this is when you declare an error and continue handler. But that declare statement gives a syntax error as well.
    – user275157
    Dec 1 '19 at 11:38
  • 1
    Declarations must prefer START TRANSACTION statement. If you need shown order you must enclose them into a block: START TRANSACTION; BEGIN DECLARE ...; other code; END; COMMIT;
    – Akina
    Dec 1 '19 at 18:44
2

DECLAREs must be placed before all executable code in a BEGIN...END block. `START TRANSACTION is 'executable'.

So...

DELIMITER //    
CREATE ...
BEGIN
    DECLARE _rollback DEFAULT 0;
    START TRANSACTION;
    ... -- other executables
    IF _rollback = 0
    THEN  COMMIT;
    ELSE  ROLLBACK;
    END IF;
END;
//
DELIMITER ;

You can also have nested BEGIN..END blocks. But the declarations in your example should probably go at the beginning of the stored routine. This is because BEGIN..END blocks provide "scoping" for variables such as _rollback.

6
  • 1
    Nesting does not produce scoping problems: START TRANSACTION; BEGIN DECLARE _rollback DEFAULT 0; executables; IF _rollback = 0 THEN COMMIT; ELSE ROLLBACK; END IF; END; - transaction start and finalize do not have to be in the same scope block.
    – Akina
    Dec 1 '19 at 20:02
  • Thanks. So does the create statement need to create a stored procedure? What if I do not want to create a stored proc.
    – user275157
    Dec 2 '19 at 3:21
  • 1
    @user275157 - Outside of a stored routine, you cannot use DECLARE and IF..THEN..ELSE..END IF. Those things must be done in your client programming language.
    – Rick James
    Dec 2 '19 at 3:27
  • So I send a separate query for begin transaction, end transaction setting the variables outside in my programming language. That sounds doable. I was under the assumption that there would be a way to send a script without creating a stored proc. Thanks for the clarification.
    – user275157
    Dec 2 '19 at 3:31
  • @user275157 - What is your client language?
    – Rick James
    Dec 2 '19 at 3:48

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.