How to call a stored procedure using hibernate?
I am having some problems with hibernate and MySQL. I've been reading but I am getting even more confused, so I imagine you could help me understand what I should do next.

I have a MySQL database in which I added this Stored Procedure (thanks to the Stack Overflow people)

    CREATE PROCEDURE BookBed (
    OUT oReservaOK boolean,
    pPaciente varchar(255),
    pHospital bigint(20))
    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

I've read somewhere that calling functions and procedures using hibernate would be very similar. Then I found (coincidently also [in StackOverflow][1]), the code to execute a function using from my java application using Hibernate:

    session.doWork(new Work() {
			@Override
			  public void execute(Connection connection) throws SQLException {
			    CallableStatement call = connection.prepareCall("{ ? = call " + functionName + "(?,?,?)  }");
			    call.registerOutParameter( 1, Types.BOOLEAN ); // or whatever it is
			    call.setString(2, param1);
			    call.setLong(3, param2);
			    call.registerOutParameter( 4, Types.BOOLEAN ); // or whatever it is
			    call.execute();
			    DatabaseManager.this.setResult(call.getBoolean(1)); // propagate this back to enclosing class
			  }
			});

I tried to use it but I get different kinds of errors (it depends how I setup the parameters). At the current point in time, I am getting a "No value specified for parameter 3" error because I registered an output parameter.
I've searched for more information but I am getting confused because the websites have different approaches and many links to the documentation are broken (links to JBoss web site).

I've read I have to return a cursor as the first out parameter. I am searching the way to do it in MySQL. (But I've read this is an indication that your design is bad)
What should I do? I am completely lost...
How can I solve it? Should I change the procedure to return my value in some other way?


Thanks,
Oscar

  [1]: http://stackoverflow.com/questions/1703351/how-to-call-a-oracle-function-from-hibernate-with-return-parameter
How to call a stored procedure using hibernate?
===

I am having some problems with hibernate and MySQL. I've been reading but I am getting even more confused, so I imagine you could help me understand what I should do next.

I have a MySQL database in which I added this Stored Procedure (thanks to the Stack Overflow people)

    CREATE PROCEDURE BookBed (
    OUT oReservaOK boolean,
    pPaciente varchar(255),
    pHospital bigint(20))
    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

I've read somewhere that calling functions and procedures using hibernate would be very similar. Then I found (coincidently also [in StackOverflow][1]), the code to execute a function using from my java application using Hibernate:

    session.doWork(new Work() {
			@Override
			  public void execute(Connection connection) throws SQLException {
			    CallableStatement call = connection.prepareCall("{ ? = call " + functionName + "(?,?,?)  }");
			    call.registerOutParameter( 1, Types.BOOLEAN ); // or whatever it is
			    call.setString(2, param1);
			    call.setLong(3, param2);
			    call.registerOutParameter( 4, Types.BOOLEAN ); // or whatever it is
			    call.execute();
			    DatabaseManager.this.setResult(call.getBoolean(1)); // propagate this back to enclosing class
			  }
			});

I tried to use it but I get different kinds of errors (it depends how I setup the parameters). At the current point in time, I am getting a "No value specified for parameter 3" error because I registered an output parameter.
I've searched for more information but I am getting confused because the websites have different approaches and many links to the documentation are broken (links to JBoss web site).

I've read I have to return a cursor as the first out parameter. I am searching the way to do it in MySQL. (But I've read this is an indication that your design is bad)
What should I do? I am completely lost...
How can I solve it? Should I change the procedure to return my value in some other way?


Thanks,
Oscar

  [1]: http://stackoverflow.com/questions/1703351/how-to-call-a-oracle-function-from-hibernate-with-return-parameter