Parameters duplicating while calling SimpleJdbcCall.execute() method

[spice.us]

Hi,
I'm getting org.springframework.jdbc.BadSqlGrammarException when i try to fetch the records from the table.

Below is the code which i'm using to query the table. To the simpleJdbcCall object, i'm declaring 9 parameters (1 IN, 8 OUT). But when i execute using

simpleJdbcCall.execute(fiscalYearCalendarInputMap);

I'm getting the below exception.

error in downloadcom.netapp.smart.framework.spreadsheet.SpreadSheetException: CallableStatementCallback; bad SQL grammar [{call SMRT_WORKFLOW_PKG.SP_GETFISCALYEARDETAILSBYFY(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SP_GETFISCALYEARDETAILSBYFY'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

 
public class SystemEntityDaoImpl extends SimpleJdbcDaoSupport implements SystemEntityDao {

public FiscalYearCalendar getFiscalYearDetailsByFiscalYear(String fiscalYear) throws DataAccessException {

        // Name of the database package
        final String packageName = storedProcedureMessageSource.getMessage(PKG_SMART_WORKFLOW, null, Locale.getDefault());

        // Name of the stored procedure, which fetches all other information of
        // FiscalYearCalendar having the specified fiscalYear
        final String storedProcedureName = storedProcedureMessageSource.getMessage(SP_GET_FISCAL_YEAR_DETAILS_BY_FISCAL_YEAR, null, Locale
                .getDefault());

        logger.debug(" Inside getFiscalYearDetailsByFiscalYear() - Stored Procedure name:" + storedProcedureName);

        SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(getDataSource()).withCatalogName(packageName).withProcedureName(storedProcedureName)
                .declareParameters(
                        // Specifying the sql data types of all the
                        // parameters(both IN-OUT parameters) of
                        // the stored-procedure.
                        new SqlOutParameter("fiscalyearid", Types.BIGINT), new SqlOutParameter("effectivestartdate", Types.DATE),
                        new SqlOutParameter("effectiveendtdate", Types.DATE), new SqlOutParameter("description", Types.VARCHAR),
                        new SqlOutParameter("systemcreateduserid", Types.BIGINT), new SqlOutParameter("systemlastmodifieduserid", Types.BIGINT),
                        new SqlOutParameter("systemcreatedtimestamp", Types.DATE), new SqlOutParameter("systemlastmodifiedtimestamp", Types.DATE),
                        new SqlParameter("fiscalyear", Types.VARCHAR));

        // A MapSqlParameterSource object, specifying the parameter of
        // the stored-procedure.
        MapSqlParameterSource fiscalYearCalendarInputMap = new MapSqlParameterSource();
        fiscalYearCalendarInputMap.addValue("fiscalyear", fiscalYear);

        // Executing the stored procedure
        Map fiscalYearCalendarResultMap = simpleJdbcCall.execute(fiscalYearCalendarInputMap);

        FiscalYearCalendar fiscalYearCalendar = new FiscalYearCalendar();

        fiscalYearCalendar.setFiscalYear(fiscalYear);
        fiscalYearCalendar.setFiscalYearId((Long) fiscalYearCalendarResultMap.get("fiscalyearid"));
        fiscalYearCalendar.setEffectiveStartDate((Date) fiscalYearCalendarResultMap.get("effectivestartdate"));
        fiscalYearCalendar.setEffectiveEndDate((Date) fiscalYearCalendarResultMap.get("effectiveendtdate"));
        fiscalYearCalendar.setDescription((String) fiscalYearCalendarResultMap.get("description"));
        fiscalYearCalendar.setSystemCreatedUserId((Long) fiscalYearCalendarResultMap.get("systemcreateduserid"));
        fiscalYearCalendar.setSystemCreatedTimeStamp((Date) fiscalYearCalendarResultMap.get("systemcreatedtimestamp"));
        fiscalYearCalendar.setSystemLastModifiedUserId((Long) fiscalYearCalendarResultMap.get("systemlastmodifieduserid"));
        fiscalYearCalendar.setSystemLastModifiedTimeStamp((Date) fiscalYearCalendarResultMap.get("systemlastmodifiedtimestamp"));

        return fiscalYearCalendar;
    } // End of getFiscalYearDetailsByFiscalYear() method.
}

Please let me know why the parameters are getting duplicated while i use spring dao?

FYI: I tried accessing the same SP with simple java code, its working fine.

--Velmurugan.S--

[wpoitras]

Looking at the Javadoc for SimpleJdbcCall.declareParameters I noticed:

Specify one or more parameters if desired. These parameters will be supplemented with any parameter information retrieved from the database meta data.

My guess is that the code is adding the same parameters from the metadata. You can either not declare parameters and use the names of the parameters from the database or call withoutProcedureColumnMetaDataAccess.

[spice.us]

Thanks Bill.. After i added withoutProcedureColumnMetaDataAccess() to the declareParameters(), now it is running fine without any exceptions.

But what my concern is until yesterday the same code was running fine. We didn't change our source code as well as database guys also not updated any metadata related stuffs. Then why should i today suddenly got this exception?

If this is the case, how do i believe the Spring JDBC api's will behave consistently through out the application in production ?

If Spring JDBC dao api does not behave consistently, Do i need to use withoutProcedureColumnMetaDataAccess() through out my code?

regards,
Velmurugan.S

[wpoitras]

Something may have changed, even if it wasn't obvious. From reading the code and using Spring JDBC classes, I have found Spring to be very consistent and stable. It strives to be very spec compliant, however things like uncompliant or buggy drivers make it look like Spring has a problem. If there is an inconsistency in the reporting on meta data and there wasn't any changes, it may be a driver bug.

All that being said, its possible there is a bug. No software is bug free.

Looking at the javadoc, SimpleJdbcCall is designed to rely on meta data, unless you explitly choose to ignore it. You have a few options:
- Add withoutProcedureColumnMetaDataAccess to each call
- Rename your parameters to match what the stored procedure uses. Your map keys would also have to change.
- Don't declare your parameters, instead use the names the stored procedure uses. Again, your map keys would have to change.
- Subclass the StoredProcedure class. Its more verbose, but doesn't rely on meta data.