How read returned value from stored procedure in Spring Jdbc (without out parameters in procedure)

Question

How can I read returned value from stored procedure in Spring Jdbc (without out parameters) ?

I am use Sybase ASE database.

Procedure Example:

CREATE PROCEDURE dbo.procedureA (
 @a int
)
as
begin
    IF EXISTS (SELECT 1 FROM dbo.T WHERE a = @a)
        return 1
    ELSE
        return -1
end

score 3 · Answer 1 · answered Aug 19 '14 at 19:49

3

Reference: http://www.databaseskill.com/1270151/ Key point is: SimpleJdbcCall.withReturnValue() directs the SimpleJdbcCall object to put the return value in the result map with key "RETURN_VALUE". So you can access it like (Integer)simpleJdbcCall.execute().get("RETURN_VALUE")

For Sybase, it is not necessary to declare SqlOutParameter "RETURN_VALUE"

answered Aug 19 '14 at 19:49

Jeff

31
2

From my experience with PostgreSQL as database, the return key name is `"returnvalue"`, but [others](http://tothepointtalks.blogspot.ro/2009/12/execute-stored-procedure-in-spring-with.html) have mentioned another possibility, i.e. `"return"` (not sure which database). Could be that the key in the map for the return value of the stored procedure changes depending on which database is being used. – Alex Bitek Jun 23 '15 at 17:37

score 1 · Answer 2 · answered Oct 25 '13 at 07:51

The best approach for handle this is to use SimpleJdbcCall that it's in the Spring-Jdbc project.

With SimpleJdbcCall you can declare in and out parameters for this purpose.

You can do it something like this:

SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(dataSource);
simpleJdbcCall.withCatalogName("dbo");
simpleJdbcCall.withProcedureName("procedureA ");
simpleJdbcCall.setAccessCallParameterMetaData(false);
simpleJdbcCall.declareParameters(new new SqlOutParameter("a",Types.NUMERIC));
simpleJdbcCall.execute();

More info here

Hope it helps.

No, this is procedure. (This is just an example, in fact I have the completed procedure and it is very big, it will not be rewritted.) — Malahov, Oct 25 '13 at 12:08

score -2 · Answer 3 · edited Jul 09 '14 at 12:23

This might help, although I haven't tried it myself yet;

http://forum.spring.io/forum/spring-projects/data/114859-return-value-of-stored-procs-using-storedprocedure-class-and-not-simplejdbccall

public MyStoredProc(JdbcTemplate jdbcTemplate) {
    super(jdbcTemplate, "mystoredproc");
    setFunction(true);
    declareParameter(new SqlOutParameter("Result", Types.NUMERIC));
    declareParameter(new SqlInOutParameter("otherparam1", Types.INTEGER));
    declareParameter(new SqlParameter("otherparam2", Types.INTEGER));
    allowsUnusedParameters();
    compile();
}

How read returned value from stored procedure in Spring Jdbc (without out parameters in procedure)

3 Answers3