How to call a stored procedure synchronously with SimpleJdbcCall

Question

I was running a stored procedure with Springs SimpleJdbcCall like this:

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate).withProcedureName("example_proc");
jdbcCall.execute();
// do other stuff on other subsystems
// the sysout below is just an example - the real scenario was somewhat different
System.out.println("Supposedly after the end of the stored procedure call");

The stored procedure was running for a long time, and it was overlapped with the stuff that was supposed to happen after that.

The stored procedure was written in Microsoft's SQL Server dialect, and looked like this:

CREATE PROCEDURE example_proc
AS
BEGIN
    INSERT INTO example_table_1 SELECT * FROM example_table_2
    UPDATE example_table_1 SET col1 = 'a' WHERE ...
END

The question is: how to make sure the SimpleJdbcCall waits until the stored procedure finishes?

Answer 1

There is a hack for this: make the stored procedure return something, and then retrieve it in the jdbc call.

The is the stored procedure:

CREATE PROCEDURE example_proc
AS
BEGIN
    INSERT INTO example_table_1 SELECT * FROM example_table_2
    UPDATE example_table_1 SET col1 = 'a' WHERE ...

    -- this is just a hack for running it synchronously:
    SELECT 1 AS success
END

Now that it returns something, the jdbc call can wait for that:

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
        .withProcedureName("example_proc").
        returningResultSet("success", new SingleColumnRowMapper<Integer>());
Map<String, Object> map = jdbcCall.execute();
@SuppressWarnings("unchecked")
List<Integer> storedProcedureResults = (List<Integer>) map.get(success);
int result = storedProcedureResults.get(0);
// I did something to the result. I am not sure if this is really necessary.
// But I was worried if the jvm or javac would optimize the dead code.
// I returned the value from a method. Printing it should also be enough.
System.out.println(result);