Problem with org.springframework.jdbc.object.StoredProcedure

[wcheng]

i wrote a Procedure in my oracle db :
create or replace package body test is
procedure main_p(a in varchar2, b in varchar2) is
begin
//do something;
end;
end test;

then invoked it in my class:
public class TestSpringStoredProcedure extends StoredProcedure{
public TestSpringStoredProcedure(DataSource ds) {
setDataSource(ds);
setSql("test.main_p");
declareParameter(new SqlParameter("a", Types.VARCHAR));
declareParameter(new SqlParameter("b", Types.VARCHAR));
compile();
}

public Map executeCalculation() {
Map inParameters = new HashMap();
inParameters.put("a", new String("w"));
inParameters.put("b", new String("y"));

Map out = execute(inParameters); // Call on parent class
return out;
}
}

it works

but when i changed Procedure to Function:
create or replace package body test is
function main_p(a in varchar2, b in varchar2) return varchar2 is
begin
return a;
end;
end test;

i met a exception:
org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call test.main_p(?, ?)}];

i know when i invoke Function use jdbc i should write like this
?=call test.main_p(?,?)
CallableStatement cstmt = con.prepareCall("{?=call test.main_p(?,?)}");
cstmt.registerOutParameter(1,Types.FLOAT);
cstmt.setString(2,"x");
cstmt.setFloat(3,"y");



so i try this in my TestSpringStoredProcedure class:

......
declareParameter(new SqlOutParameter("c", Types.FLOAT));
declareParameter(new SqlParameter("a", Types.VARCHAR));
declareParameter(new SqlParameter("b", Types.VARCHAR));
......

another exception thrown :org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call test.main_p(?, ?, ?)}];
it was handled as call test.main_p(?, ?, ?)

can somebody help me?

[Andreas Senft]

Mabe you could try SqlReturnResultSet instead of SqlOutParameter.
In any case you have to set the "function" property of your StoredProcedure to "true".

Regards,
Andreas

[wcheng]

thank you
Andreas.
i try like this
setDataSource(ds);
setSql(PROC_NAME);
setFunction(true);
declareParameter(new SqlReturnResultSet("rs", new RowMapper(){
public Object mapRow(ResultSet rs, int rowNum) throws SQLException{
return new Integer(1);
}
} ));
declareParameter(new SqlParameter("a", Types.VARCHAR));
declareParameter(new SqlParameter("b", Types.VARCHAR));


Map inParameters = new HashMap();
inParameters.put("a", new String("w"));
inParameters.put("b", new String("y"));

Map out = execute(inParameters); // Call on parent class


it ends in a exception :
org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{? = call test.main_p(?)}];

where is my second in param?

[Andreas Senft]

I'm at a loss.
Does it work with SqlOutParameter (with "function" property set to "true")?


Regards,
Andreas

[wcheng]

ok,it works!
thank you very much!