CODEWITHSUNDEEP
javamysqljpastored-procedureseclipselink
Gicu Mironica
Gicu Mironica

Reputation: 635

Stored procedures doesn't execute when stopping Java

I created a stored procedure in MySQL and called it from a Java application with JPA EclipseLink. Once the procedure is called, it has a "sleep(sec)" method inside, and then it executes something successfully unless the application is turned off, it seems like the procedure is canceled too which is not what I want. The same thing I tried using JDBC PreparedStatements, the same result. Is there any workaround to make the stored procedure work even if the app was shut down after the procedure call.

Stored Procedure

DELIMITER //
DROP PROCEDURE IF EXISTS session_procedure//
CREATE PROCEDURE session_procedure
(
IN userID INT
)
BEGIN
SELECT SLEEP(30);
UPDATE users 
SET users.Active = 0 
WHERE users.Id = userID;

END//
DELIMITER ;

Procedure call in Java

public static void destroySessionCountdown(EntityManager entityManager, Account akk){
        int accountId = akk.getId();

        StoredProcedureQuery storedProcedure = entityManager.createStoredProcedureQuery("session_procedure");
        storedProcedure.registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN);
        storedProcedure.setParameter(1, accountId);

        try {
            storedProcedure.execute();
        }catch(Exception e){
            e.printStackTrace();
        }
        // force logout
    }

Upvotes: 2

Views: 380

Answers (1)

mentallurg
mentallurg

Reputation: 5207

I suppose that when you closed connection to the DB, all processes related to it were cancelled including the running call of this stored procedure. I don't think you can avoid it.

What you are trying to implement is a kind of scheduled job. I would suggest to use cron instead. For the procedure you shown a simple SQL instead of stored procedure would be sufficient. The logic related to delays and to the execution time could be placed to a shell script and to the cron.

Upvotes: 1

Related Questions