Announcement Announcement Module
Collapse
No announcement yet.
StoredProcedure caching call string Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • StoredProcedure caching call string

    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.

    Code:
    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;
    		}
    		
    		
    		
    	}

  • #2
    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(?, ?, ?, ?, ?, ?, ?, ?)}

    Comment


    • #3
      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

      Comment

      Working...
      X