title: Getting a bad grammar error in a call to a Spring Stored Procedure with Java
body: <p>I'm having to debug some code written by someone else and I've run into a problem in one of the DAO implementations.  We're using Java 1.6 and Spring (I think there's some hibernate in some portions of the application but don't think they come into play here.)  The code when it runs is erroring on the "outMap = super.execute(inMap);" line of code.  The error it throws is</p>
  
  <blockquote>
    <p>SystemErr     R callHistorySP()
    org.springframework.jdbc.BadSqlGrammarException:
    CallableStatementCallback; bad SQL grammar [{call
    DB2ADMIN/QUOTEACCESSORIALS(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; nested exception is
    java.sql.SQLException: The number of parameter values set or
    registered does not match the number of parameters.</p>
  </blockquote>
  
  <p>Here's the object (at least the relevant parts).</p>
  
  <pre><code>private class RateQuoteHistoryStoredProc extends StoredProcedure {
      private final String SQL = getStoredProcName();
      private final String QUOTE_NUM = "rateQuoteNumber",
          ACCESSORIAL = "accessorial",
          ACCESSORIAL_AMT = "accessorialAmt",
                  FAKRATEHISTORY = "fakRateHistory",
                  HANDLING_UNITS = "numHandlingUnits",
                  PIECES = "numPieces",
                  CUBIC_FEET = "cubicFeet",
                  BUNKER_FUEL_SURCHARGE = "puertoRicoBunkerFuel",
                  CANADIAN_BORDER_FEE = "canadaCrossBorderFee" ,
                  TX_ARBITRARY_CHARGE = "texasArbitraryChg" ,
                  WASHINGTON_DC_CHARGE = "washingtonDCArbitraryChg",
                  NYC_CHARGE = "nycArbitraryChg" ,
                  NORTHERN_VIRGINIA_CHARGE = "northVirginiaArbitraryChg" ,
                  FLORIDA_ARBITRARY_CHARGE = "flKeysArbitraryChg" ,
                  FERRY_CHARGE ="ferryCharge";
  
  
      private Map outMap = new HashMap();
  
  
      public RateQuoteHistoryStoredProc(DataSource ds){
          try {
              setDataSource(ds);
              setSql(SQL);
              declareParameter(new SqlParameter(QUOTE_NUM, Types.DECIMAL));
              for (int i = 0; i &lt; getMAX_ACCESSORIALS(); i++) {
                  declareParameter(new SqlOutParameter(ACCESSORIAL + i, Types.CHAR));
              }
              for (int i = 0; i &lt; getMAX_ACCESSORIALS(); i++) {
                  declareParameter(new SqlOutParameter(ACCESSORIAL_AMT + i, Types.DECIMAL));
              }
                      declareParameter(new SqlOutParameter(FAKRATEHISTORY, Types.CHAR));
                      declareParameter(new SqlOutParameter(HANDLING_UNITS, Types.DECIMAL));
                      declareParameter(new SqlOutParameter(PIECES, Types.DECIMAL));
                      declareParameter(new SqlOutParameter(CUBIC_FEET, Types.DECIMAL));
                      declareParameter(new SqlOutParameter(BUNKER_FUEL_SURCHARGE, Types.DECIMAL));
                  declareParameter(new SqlOutParameter(CANADIAN_BORDER_FEE, Types.DECIMAL));
                      declareParameter(new SqlOutParameter(TX_ARBITRARY_CHARGE, Types.DECIMAL));
                      declareParameter(new SqlOutParameter(WASHINGTON_DC_CHARGE, Types.DECIMAL));
                      declareParameter(new SqlOutParameter(NYC_CHARGE, Types.DECIMAL));
                      declareParameter(new SqlOutParameter(NORTHERN_VIRGINIA_CHARGE, Types.DECIMAL));
                      declareParameter(new SqlOutParameter(FLORIDA_ARBITRARY_CHARGE, Types.DECIMAL));
                      declareParameter(new SqlOutParameter(FERRY_CHARGE, Types.DECIMAL));
              compile();
          }
          catch (Exception e) {
              log.error("RateQuoteStoredProc()",e);
          }
      }
  
      public void callHistorySP(LTLCustomRatesBean bean) throws Exception {
          HashMap inMap = new HashMap();
          inMap.put(QUOTE_NUM, bean.getRateQuoteNumber());
  
          for(int i = 0; i &lt; getMAX_ACCESSORIALS(); i++){
              inMap.put(ACCESSORIAL+i, " ");
          }
  
          for(int i = 0; i &lt; getMAX_ACCESSORIALS(); i++){
              inMap.put(ACCESSORIAL_AMT+i, "0");
          }
  
          try 
          {
              outMap = super.execute(inMap);
          }
          catch (NullPointerException npe){
              if(bean.getCustomer() == null){
                  log.error("callHistorySP() Customer is null");
              }else{
                  log.error("callHistorySP()",npe);
              }
          }
          catch (Exception e) {
              log.error("callSP()",e);
          }
          if (isDebugMode()) {
              log.debug("callSP(): outMap: "+outMap);
          }
          saveResults(outMap, bean);
      }
  
  }
  </code></pre>
  
  <p>Here's the Stored Procedure that it is calling.</p>
  
  <pre><code>CREATE PROCEDURE DB2ADMIN.QUOTEACCESSORIALS ( 
       IN QUOTENUM DECIMAL(7, 0) , 
       INOUT ACCCD1 CHAR(6) , 
       INOUT ACCCD2 CHAR(6) , 
       INOUT ACCCD3 CHAR(6) , 
       INOUT ACCCD4 CHAR(6) , 
       INOUT ACCCD5 CHAR(6) , 
       INOUT ACCCD6 CHAR(6) , 
       INOUT ACCCD7 CHAR(6) , 
       INOUT ACCCD8 CHAR(6) , 
       INOUT ACCCD9 CHAR(6) , 
       INOUT ACCCD10 CHAR(6) , 
       INOUT ACCCD11 CHAR(6) , 
       INOUT ACCCD12 CHAR(6) , 
       INOUT ACCCD13 CHAR(6) , 
       INOUT ACCCD14 CHAR(6) , 
       INOUT ACCCD15 CHAR(6) , 
       INOUT ACCCD16 CHAR(6) , 
       INOUT ACCCD17 CHAR(6) , 
       INOUT ACCCD18 CHAR(6) , 
       INOUT ACCCD19 CHAR(6) , 
       INOUT ACCCD20 CHAR(6) , 
       INOUT ACCAM1 DECIMAL(9, 2) , 
       INOUT ACCAM2 DECIMAL(9, 2) , 
       INOUT ACCAM3 DECIMAL(9, 2) , 
       INOUT ACCAM4 DECIMAL(9, 2) , 
       INOUT ACCAM5 DECIMAL(9, 2) , 
       INOUT ACCAM6 DECIMAL(9, 2) , 
       INOUT ACCAM7 DECIMAL(9, 2) , 
       INOUT ACCAM8 DECIMAL(9, 2) , 
       INOUT ACCAM9 DECIMAL(9, 2) , 
       INOUT ACCAM10 DECIMAL(9, 2) , 
       INOUT ACCAM11 DECIMAL(9, 2) , 
       INOUT ACCAM12 DECIMAL(9, 2) , 
       INOUT ACCAM13 DECIMAL(9, 2) , 
       INOUT ACCAM14 DECIMAL(9, 2) , 
       INOUT ACCAM15 DECIMAL(9, 2) , 
       INOUT ACCAM16 DECIMAL(9, 2) , 
       INOUT ACCAM17 DECIMAL(9, 2) , 
       INOUT ACCAM18 DECIMAL(9, 2) , 
       INOUT ACCAM19 DECIMAL(9, 2) , 
       INOUT ACCAM20 DECIMAL(9, 2) , 
       OUT FAKRATEHISTORY CHAR(20) , 
       OUT HANDLING_UNIT DECIMAL(5, 0) , 
       OUT PIECES DECIMAL(5, 0) , 
       OUT CUBIC_FEET DECIMAL(5, 0) , 
       OUT BUNKER_FUEL_SURCHARGE DECIMAL(9, 2) , 
       OUT CANADIAN_BORDER_FEE DECIMAL(9, 2) , 
       OUT TX_ARBITRARY_CHARGE DECIMAL(9, 2) , 
       OUT WASHINGTON_DC_CHARGE DECIMAL(9, 2) , 
       OUT NYC_CHARGE DECIMAL(9, 2) , 
       OUT NORTHERN_VIRGINIA_CHARGE DECIMAL(9, 2) , 
       OUT FLORIDA_ARBITRARY_CHARGE DECIMAL(9, 2) , 
       OUT FERRY_CHARGE DECIMAL(9, 2) ) 
       LANGUAGE RPG 
       SPECIFIC DB2ADMIN.QUOTEACCESSORIALS 
       NOT DETERMINISTIC 
       MODIFIES SQL DATA 
       CALLED ON NULL INPUT 
       EXTERNAL NAME 'LTL400AVX2/WW7R010' 
       PARAMETER STYLE GENERAL ;
  </code></pre>
  
  <p>You'll note that the SP has 53 parameters.  It has 1 input parameter, 40 in/out parameters, and 12 output parameters.  The call that I'm making seems to have the correct number of parameters and I can't figure out what is causing the error.  Any help would be greatly appreciated.  I'm sure that the problem is probably something simple but I just don't know Spring well enough to see what is wrong with this code.  I haven't been able to Google an answer up like normal either.  Thanks!</p>
  
  <p>Andrew</p>