I've this function during my DB
CREATE FUNCTION BookBed (pPaciente varchar(255),
pHospital bigint(20)) RETURNS BOOLEAN
BEGIN
DECLARE NumLeitosDisponiveis INT;
DECLARE vReservaOK BOOLEAN;
DECLARE dt TIMESTAMP;
SET dt = (Select now());
SET NumLeitosDisponiveis = (SELECT AVAILABLEBEDCOUNT FROM HOSPITAL WHERE ID = pHospital);
IF((SELECT NumLeitosDisponiveis) > 0) THEN
BEGIN
START TRANSACTION;
INSERT INTO RESERVATION(PERSON, HOSPITAL, DATE) VALUES (pPaciente, pHospital, dt);
UPDATE HOSPITAL
SET AVAILABLEBEDCOUNT = AVAILABLEBEDCOUNT - 1
WHERE ID = pHospital;
SET vReservaOk = true;
commit;
END;
ELSE
SET vReservaOk = false;
END IF;
RETURN vReservaOK;
END;
Within the if a part of my if-else statement, I must perform all of the procedures inside a atomic way. I needed to make use of the beginning TRANSACTION command, but they're disallowed in functions and that i could not find every other command to do it.
Are functions atomic automatically? Otherwise, can there be in whatever way I'm able to implement it?
Thanks, Oscar
EDIT: And when I must make use of a function, can you really have transactions?
Make use of a saved procedure by having an output parameter for coming back the operation status.
DELIMITER //
CREATE PROCEDURE BookBed (
pPaciente varchar(255),
pHospital bigint(20),
OUT oReservaOK boolean)
BEGIN
DECLARE NumLeitosDisponiveis INT;
DECLARE dt TIMESTAMP;
SET dt = (Select now());
SET NumLeitosDisponiveis =
SELECT AVAILABLEBEDCOUNT FROM HOSPITAL WHERE ID = pHospital;
IF((SELECT NumLeitosDisponiveis) > 0) THEN
BEGIN
START TRANSACTION;
INSERT INTO RESERVATION(PERSON, HOSPITAL, DATE)
VALUES (pPaciente, pHospital, dt);
UPDATE HOSPITAL
SET AVAILABLEBEDCOUNT = AVAILABLEBEDCOUNT - 1
WHERE ID = pHospital;
SET oReservaOk = true;
commit;
END;
ELSE
SET oReservaOk = false;
END IF;
END//
If you're getting trouble calling the saved procedure using Hibernate, then slowly move the transaction logic to Hibernate. That's, start, commit or rollback the transaction using Hibernate constructs.