SimpleJdbcCall without parameter declaration fails

[davison]

hi,

we're trying to write a generic DAO impl. to wrap calls to several dozen stored procs that all have varying IN parameters, but the same OUT parameters. We keep getting failures based on missing parameter declarations. Using Spring 2.5.1

The DAO impl is as follows:

public class GenericStoredProcedureDAOImpl implements GenericStoredProcedureDAO {
    private Map<String, SimpleJdbcCall> spMap = new HashMap<String, SimpleJdbcCall>();

    @Autowired
    private DataSource dataSource;

    public StoredProcReturn call(String spName, Map<String, Object> parameters) throws DataAccessException {
        
        // is the compiled call already in the map?
        SimpleJdbcCall sp = spMap.get(spName);
        if (sp == null) {
            // create and add to the map
            sp = new SimpleJdbcCall(dataSource)
                .withProcedureName(spName);
//                .withoutProcedureColumnMetaDataAccess()
//                .useInParameterNames("j_user_id")
//                .declareParameters(
//                        new SqlParameter("j_user_id", Types.NUMERIC),
//                        new SqlOutParameter("j_employee_id", Types.NUMERIC),
//                        new SqlOutParameter(J_RET_VAL, Types.INTEGER),
//                        new SqlOutParameter(J_INTERNAL_MESSAGE, Types.VARCHAR),
//                        new SqlOutParameter(J_EXTERNAL_MESSAGE, Types.VARCHAR)
//                );
            sp.compile();
            spMap.put(spName, sp);
        }
        
        // execute sp
        Map<String, Object> out = sp.execute(parameters);
        
        // set return vals
        StoredProcReturn spReturn = new StoredProcReturn();
        spReturn.setRetVal((Integer) out.get(J_RET_VAL));
        spReturn.setInternalMessage((String) out.get(J_INTERNAL_MESSAGE));
        spReturn.setExternalMessage((String) out.get(J_EXTERNAL_MESSAGE));
        
        return spReturn;
    }

}

which is being exercised with the following test:

public void testCallNotFailing() {
    Map<String, Object> p = new HashMap<String, Object>();
    p.put("j_user_id", 1151l);
    
    StoredProcReturn spr = dao.call("employee_pkg.get_employee_id", p);
    assertFalse(spr.hasFailed());
    assertNull(spr.getInternalMessage());
    assertNull(spr.getExternalMessage());
}

The relevant SP declaration in Oracle for the above test is as follows:

PROCEDURE GET_EMPLOYEE_ID (
    j_user_id                   IN ACCOUNT_USER.USER_ID%TYPE,
    j_employee_id               OUT VARCHAR2,
    j_ret_val                   OUT NUMBER,
    j_internal_message          OUT VARCHAR2,
    j_external_message          OUT VARCHAR2);

and the log output is this:

[22 Feb 13:41:37,534] DEBUG core.metadata.CallMetaDataProviderFactory - Using org.springframework.jdbc.core.metadata.OracleCallMetaDataProvider
[22 Feb 13:41:37,534] DEBUG core.metadata.CallMetaDataProvider - Retrieving metadata for /MYSCHEMA/EMPLOYEE_PKG.GET_EMPLOYEE_ID
[22 Feb 13:41:37,953] DEBUG core.simple.SimpleJdbcCall - Compiled stored procedure. Call string is [{call EMPLOYEE_PKG.GET_EMPLOYEE_ID()}]
[22 Feb 13:41:37,955] DEBUG core.simple.SimpleJdbcCall - SqlCall for procedure [employee_pkg.get_employee_id] compiled
[22 Feb 13:41:37,956] WARN  core.metadata.CallMetaDataContext - Unable to locate the corresponding parameter for "j_user_id" in the parameters used: []
[22 Feb 13:41:37,956] DEBUG core.metadata.CallMetaDataContext - Matching {j_user_id=1151} with {}
[22 Feb 13:41:37,956] DEBUG core.metadata.CallMetaDataContext - Found []

If I uncomment the param declarations in the DAO, it works, but of course this makes the DAO method applicable to only a single SP and not the dozens we want it to be used for.

Anything I'm missing here?

Cheers,

[davison]

hmm..

.withCatalogName("NAME_OF_PKG")

sorry for the noise.

[trisberg]

Yes, the package name must be provided separately for the metadata lookup to work. Looks like you are creating a new instance for each call which would result in some metadata lookup each time. If the same procedure is called several times you mighty want to create a cache of already compiled instances.