MySQL - How do i create a function atomic?

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?

mysql database

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.

Related