'MySQL transaction FOR COMMITING ON TRUE IF

i need to make an sql transaction, that only commits the insert if the one date is greater than the other, and rolls back if else..

this is what i have:

START TRANSACTION;

INSERT INTO Cene_Prenocevanja VALUES (6, 2, 10.00, 22.39, str_to_date('01-01-1990', '%d-%m-%Y'), str_to_date('01-01-1989', '%d-%m-%Y'));

CREATE VIEW zacasni AS
SELECT Zacetek_Veljavnosti FROM Cene_Prenocevanja WHERE IDCene_Prenocevanja=6
INTO @zacetek;
SELECT Konec_Veljavnosti FROM Cene_Prenocevanja WHERE IDCene_Prenocevanja=6
INTO @konec;

IF @zacetek < @konec THEN
    COMMIT;
ELSE
    ROLLBACK;
END IF;

THESE ARE MY RESPONSES...

IF is not valid at this position

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF @zacetek < @konec THEN COMMIT' at line 1

mysqlsql


Solution 1:[1]

Like this? (Comment from krokodilko)

START TRANSACTION;

CREATE VIEW zacasni AS
SELECT Zacetek_Veljavnosti FROM Cene_Prenocevanja WHERE IDCene_Prenocevanja=6
INTO @zacetek;
SELECT Konec_Veljavnosti FROM Cene_Prenocevanja WHERE IDCene_Prenocevanja=6
INTO @konec;

IF @zacetek < @konec THEN
    INSERT INTO Cene_Prenocevanja VALUES (6, 2, 10.00, 22.39, str_to_date('01-01-1990', '%d-%m-%Y'), str_to_date('01-01-1989', '%d-%m-%Y'));
END IF;

COMMIT;

Solution 2:[2]

The MySQL IF statement is only allowed in the store procedures. To rollback the transaction you can try to insert an invalid data if your condition is FALSE. Let's suppose your IDCene_Prenocevanja is defined as NOT NULL. Then you can do it this way:

START TRANSACTION;
# Use IF function to set proper value to @id
SELECT @id := (
  SELECT IF(str_to_date('01-01-1990', '%d-%m-%Y') > str_to_date('01-01-1989', '%d-%m-%Y'), 6, NULL)
);
# Use @id in insertion
INSERT INTO Cene_Prenocevanja VALUES (@id, 2, 10.00, 22.39, str_to_date('01-01-1990', '%d-%m-%Y'), str_to_date('01-01-1989', '%d-%m-%Y'));
COMMIT;

So if the condition is FALSE it tries to insert NULL into NOT NULL field which rolls the transaction back.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 StanislavL
Solution 2 shev.vadim.net

Related Questions