Integration

First, I know that the best solution is to not use stored procedures with Toplink. But some teams I am working with have a special requirement that DB access is only through SPs. So with that out of the way.... :)

I am trying to do queries with stored procedures. My goal is to define a stored procedure with a full set of query parameters, but only execute with a subset of these parameters defined. The reset need to be passed to the SP as null input parameter, which will indicate to the SP that it should not use these parameters as part of the query.

To complicate things further, I am using the Toplink JDO APIs, though I do not understand how to pass in null parameters for the Toplink base APIs either.

I have no problem setting up the oracle.toplink.queryframework.StoredProcedureCall when all parameters are defined. I use the addNamedArgument(spParamName, argName) API to set up each input parameter on the SP, and use the JDOQuery addArgument(argValue) API to set each value prior to execute (which calls DatabaseQuery.addArgument()). This allows me to correctly execute the query by calling execute with a map of all parameter values.

The problem I am having is that if omit one or more of the parameter values in the execute map, I get a null pointer error within the translate method of the StoredProcedureCall object. The error is on line 709 in version 9.0.4 of Toplink.

I have tried the following variations:

- Not calling addNamedArgument() on the StoredProcedureCall object for omitted values. This causes an error because the SP signature no longer matches.

- Not calling addArgument on the JDOQuery/DatabaseQuery object. This results in a null pointer with the translate method.

- Not setting omitted argument within the map. This results in a null pointer with the translate method.

Any ideas out there? I know when working directly with JDBC I could use the setNull() method explicitly pass the value as a null. But can't figure out how to do this with Toplink.

Thanks!

-John