StoredProcedure caching call string

[roykachouh]

Hi,

I am using springs facility to manage a stored procedure. Recently, my dba added an additional parameter required for the stored procedure to execute. This makes a total of 9 parameters. For the life of me, I can't get Spring to recognize the additional parameter. Whenever, I print the getCallString(), it still shows up as 8. What am I doing wrong. I am pasting the entire code for your reference.

private class SelectCounterPartyStoredProc extends StoredProcedure{
		public static final String CURSOR_ID = "results";
		private static final String SQL = "SelCptyTactical";
		private static final String ENTITY_SHORT_NAME_PARAM = "EntityShortName";
		private static final String ENTITY_LONG_NAME_PARAM = "EntityLongName";
		private static final String ENTITY_LEGAL_NAME_PARAM = "EntityLegalName";
		private static final String ENTITY_STATUS_CODE_PARAM = "EntityStatusCode";
		private static final String ENTITY_EFF_DATE_PARAM = "EntityEffDate";
		private static final String ENTITY_TERM_DATE_PARAM = "EntityTermDate";
		private static final String COUNTRY_OF_DOM_PARAM = "CountryOfDomCode";
		private static final String COUNTRY_OF_INC_PARAM = "CountryOfIncCode";
		private static final String GUID_PARAM = "EntityGUID";
		
		public SelectCounterPartyStoredProc(){
			setDataSource(dataSource);
			setFunction(true);
			setSql(SQL);
			
			declareParameter(new SqlReturnResultSet(CURSOR_ID,new CounterPartyMapper()));
			declareParameter(new SqlParameter(GUID_PARAM,Types.NUMERIC));
			declareParameter(new SqlParameter(ENTITY_SHORT_NAME_PARAM,Types.VARCHAR));
			declareParameter(new SqlParameter(ENTITY_LONG_NAME_PARAM,Types.VARCHAR));
			declareParameter(new SqlParameter(ENTITY_LEGAL_NAME_PARAM,Types.VARCHAR));
			declareParameter(new SqlParameter(ENTITY_STATUS_CODE_PARAM,Types.VARCHAR));
			declareParameter(new SqlParameter(ENTITY_EFF_DATE_PARAM,Types.TIMESTAMP));
			declareParameter(new SqlParameter(ENTITY_TERM_DATE_PARAM,Types.TIMESTAMP));
			declareParameter(new SqlParameter(COUNTRY_OF_DOM_PARAM,Types.VARCHAR));
			declareParameter(new SqlParameter(COUNTRY_OF_INC_PARAM,Types.VARCHAR));
			
			
			compile();
		}
		
		public Map execute(CPMEntity entity) {
			
			LOG.info("About to execute stored procedure using call string: "+getCallString());
	        Map inputs = new HashMap();
	        inputs.put(GUID_PARAM, new Long(entity.getEntityGUID()));
	        inputs.put(ENTITY_SHORT_NAME_PARAM, entity.getEntityShortName());
	        inputs.put(ENTITY_LONG_NAME_PARAM, entity.getEntityLongName());
	        inputs.put(ENTITY_LEGAL_NAME_PARAM, entity.getEntityLegalName());
	        inputs.put(ENTITY_STATUS_CODE_PARAM, entity.getEntityStatusCode());
	        inputs.put(ENTITY_EFF_DATE_PARAM, entity.getEffectiveDate());
	        inputs.put(ENTITY_TERM_DATE_PARAM, entity.getTerminationDate());
	        inputs.put(COUNTRY_OF_DOM_PARAM, entity.getDomicileCountry());
	        inputs.put(COUNTRY_OF_INC_PARAM, entity.getIncorpCountry());
	        
	        LOG.debug("Using declared parameters: ");
			LOG.debug(getSql());
			for (int i=0;i<getDeclaredParameters().size();i++){
				SqlParameter param = (SqlParameter) getDeclaredParameters().get(i);
				LOG.debug(param.getName()+"-->");
			}
	        
	        return super.execute(inputs);
	    }

		protected boolean allowsUnusedParameters() {
			return false;
		}
		
		
		
	}

[trisberg]

You have one SqlReturnResultSet parameter that won't generate a place holder. There is one return parameter since it's declared as a function. That leaves 8 in parameters. Based on your declaration your call string should look like this

{? = call SelCptyTactical(?, ?, ?, ?, ?, ?, ?, ?)}

[roykachouh]

Ok, I got it...you were right, it has to do with me setting the function attribute to true. The compile method of the parent classes, was using the function format for creating the call string.

Thanks.

Roy