SQL Error using SimpleJDBC Call

[DaveScotiaBank]

I am trying to call a stored procedure with Spring. The stored procedure has been tested and is callable both from a DB2 Client and from as standalone Java Jdbc application. My problem is getting the same thing to work with a SimpleJdbcCall.

The error I get is

3/26/13 11:49:19:081 EDT] 00000040 SystemErr R org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call GETBUSINESSDATE()}]; nested exception is com.ibm.db2.jcc.am.ro: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=GETBUSINESSDATE;PROCEDURE, DRIVER=3.58.81

Here is the portion of the code that makes the call. The problem occurs with the SimpleJdbCall execute statement.
Is there any way to find out the actual SQL call that SimpleJdbcCall is making including the passed parameter?

Map<String, Object> callBusinessDateMap = null;
Map<String, String> getBusinessDateMap = null;

try {

String outRESULT = "";
String outERROR_TEXT = "";

callGetBusinessDate.declareParameters(new SqlParameter( "inDATE", Types.VARCHAR ),
new SqlParameter( "inISO_COUNTRY_CODE", Types.VARCHAR ),
new SqlParameter( "inISO_PROVINCE_CODE", Types.VARCHAR),
new SqlParameter( "inOFFSET", Types.INTEGER),
new SqlOutParameter("outRESULT", Types.VARCHAR),
new SqlOutParameter("outERROR_TEXT", Types.VARCHAR)
);


MapSqlParameterSource inSqlParms = new MapSqlParameterSource();
inSqlParms.addValue("inDATE", inDATE);
inSqlParms.addValue("inISO_COUNTRY_CODE", inISO_COUNTRY_CODE);
inSqlParms.addValue("inISO_PROVINCE_CODE", inISO_PROVINCE_CODE);
inSqlParms.addValue("inOFFSET", inOFFSET);
callBusinessDateMap = callGetBusinessDate.execute(inSqlParms);

[DaveScotiaBank]

More information about this problem.

cstmt = conn.prepareCall("call BUSINESSDATE.GetBusinessdate(?, ?, ?, ?, ?, ?)");

I tried my SimpleJDBC call again with this.callGetBusinessDate.withProcedureName("BUSINE SSDATE.GetBusinessdate");
and got an error

Unable to determine the correct call signature for BUSINESSDATE.GETBUSINESSDATE - package name should be specified separately using '.withCatalogName("BUSINESSDATE")'

I tried that as well and got the same result.

[Marten Deinum]

Please use [ code][/code ] tags when posting code, that way it remains readable...

Post your code instead of snippets hard to determine what is wrong.

SimpleJdbcCall callGetBusinessDate = new SimpleJdbcCall(dataSource).withCatalogName("BUSINESSDATE").declareParameters(new SqlParameter( "inDATE", Types.VARCHAR ),
new SqlParameter( "inISO_COUNTRY_CODE", Types.VARCHAR ),
new SqlParameter( "inISO_PROVINCE_CODE", Types.VARCHAR),
new SqlParameter( "inOFFSET", Types.INTEGER),
new SqlOutParameter("outRESULT", Types.VARCHAR),
new SqlOutParameter("outERROR_TEXT", Types.VARCHAR)
);

MapSqlParameterSource inSqlParms = new MapSqlParameterSource();
inSqlParms.addValue("inDATE", inDATE);
inSqlParms.addValue("inISO_COUNTRY_CODE", inISO_COUNTRY_CODE);
inSqlParms.addValue("inISO_PROVINCE_CODE", inISO_PROVINCE_CODE);
inSqlParms.addValue("inOFFSET", inOFFSET);
callBusinessDateMap = callGetBusinessDate.execute(inSqlParms)

Something like that should work... The SimpleJdbcCall methods (most of them) return a modified SimpleJdbcCall you need to assign it to a variable else it gets lots.

Instead of withCatalogName you could try withSchemaName instead.