I am using JdbcTemplate to make a call to a stored procedure in as400. I know that the schema and procedure exist and I'm using the correct spelling etc.
The first time the application starts the call works as expected and gives no error. Subsequent calls fail with this error:
org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call PGM999.STOREDPROCNAME(?, ?, ?, ?, ?, ?, ?)}]; nested exception is com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException: [SQL0204] NEWSP in RGRPGM type *N not found.
Here is some snippets of relevant code in an @Service
class:
private ConcurrentMap<String, SimpleJdbcCall> storedProcedureCallsMap = new ConcurrentHashMap<String, SimpleJdbcCall>();
public Map<String, Object> executeStoredProcedure(String procedureName, MapSqlParameterSource paramSource, String libraryName) {
return executeStoredProcedureCall(procedureName, paramSource, createCallForStoredProcedure(procedureName, libraryName));
}
private CallCreator createCallForStoredProcedure(String procedureName, String libraryName) {
return new CallCreator() {
@Override
public SimpleJdbcCall createCall() {
return new SimpleJdbcCall(as400JdbcTemplate).withSchemaName(libraryName).withProcedureName(procedureName);
}
};
}
//
SimpleJdbcCall call = storedProcedureCallsMap.get(procedureName);
call = callCreator.createCall();
call.compile();
Map<String, Object> returnMap = call.execute(paramsSource);
What's odd is that when I call one of: Connection.commit()
or Connection.endRequest()
or Connection.setAutoCommit(true)
it works every time, however, in all of these it locks up the connections in the thread pool, and even calling Connection.close()
doesn't close them, and they stay active until there's no idle connections left to use and it locks the whole thing up.
So I either need to fix the 1st issue with it working every time without telling it to commit etc. or the second issue where I do call one of these but successfully force it to release the connections.
It turns out the the stored procedure in question was calling another stored procedure that hadn't been copied over to the schema that the main procedure was using. I still don't know why it would work the first time or when I told it to commit it would work every time. Either way copying over the missing procedure to the same schema seemed to solve the issue.