[FIXED] Out params not clearing on stored procedure call

Issue

My problem is as follows:

My service loops over items that are passed as args. For each item, i make two calls. My first price call gives me 9999 which means nothing was returned. That's fine - that's possible. My stock call works perfectly and i get the correct stock value returned. On the second iteration, my price call returns the same value as the previous stock call.

So, i get 9999 for price, then 150 for stock, then 150 for price. What's throwing me is that the price out parm is 6th, whereas the stock out parm is 8th. No idea how it would retain that value in a different position.

It seems that my jdbctemplate isn't being cleared or it's storing previous out params. Here's the code involved:

MyService.java

@Service
public class MyService extends BaseService implements MyInterface{
    protected static final Logger logger = LoggerFactory.getLogger(MyService.class);

    @Autowired
    private MyDAO myDAO;
    public myResponse checkOrder(args...)  
    {  
        for(something in args){
                // PRICE
                // Grab price data
                LinkedHashMap<String, Object> priceCallInParams = new LinkedHashMap<String, Object>();
                priceCallInParams .put("param1", "param1val");
                priceCallInParams .put("param2", "param2val");
                priceCallInParams .put("param3", "param3val");
                priceCallInParams .put("param4", "param4val");
                priceCallInParams .put("param5", "param5val");

                LinkedHashMap<String, Object> priceCallOutParams = new LinkedHashMap<String, Object>();
                priceCallOutParams .put("price", "0");

                
                logger.debug("Getting data...");
                Map<String, Object> priceData = new HashMap<String, Object>();

            
                priceData = myDAO.checkPrice(priceCallInParams , priceCallOutParams );
                
                BigDecimal unitPrice = new BigDecimal(9999);
                if (!priceData .get("PRCE").toString().trim().equals("")){
                    unitPrice = new BigDecimal(priceData.get("PRCE").toString().trim());
                }
                System.out.println("PRC - "+unitPrice);
                
                
                // AVAILABLE STOCK
                // Grab check stock data
                LinkedHashMap<String, Object> checkStockInParms = new LinkedHashMap<String, Object>();
                checkStockInParms.put("param1", "param1val");
                checkStockInParms.put("param2", "param2val");
                checkStockInParms.put("param3", "param3val");
                checkStockInParms.put("param4", "param4val");
                checkStockInParms.put("param5", "param5val");
                checkStockInParms.put("param6", "param6val");
                checkStockInParms.put("REQQTY", "123");

                LinkedHashMap<String, Object> checkStockOutParms = new LinkedHashMap<String, Object>();
                checkStockOutParms .put("AVAILQTY", "0");
                checkStockOutParms .put("NEXTDUE"," ");
                checkStockOutParms .put("NEXTQTY","0");

                
                logger.debug("Getting data...");
                Map<String, Object> checkStockDat = new HashMap<String, Object>();

                checkStockDat = myDAO.checkStock(checkStockInParms , checkStockOutParms );


                
                // Output quantity
                int AvailQTY = Integer.valueOf(checkStockDat.get("AVAILQTY").toString().trim());
                if (reqBIT.getRequestedQuantity()>AvailQTY) {
                    resBIT.setConfirmedQuantity(AvailQTY);
                }
                else {
                    resBIT.setConfirmedQuantity(reqBIT.getRequestedQuantity());
                }
            }

    }


}

MyDAO.java

@Component
public class MyDAO extends BaseDAO{
    protected static final Logger logger = LoggerFactory.getLogger(OrderDAO.class);

    
    public Map<String, Object> checkStock(LinkedHashMap<String, Object> inparms, LinkedHashMap<String, Object> outparms){
        StringBuilder builtSQL = new StringBuilder();
        builtSQL.append("CALL ");
        builtSQL.append("checkstock ");
        
// just generates our param string (?,?,?...) 
builtSQL.append(DataUtilities.genParmPlaceholderStringFromTotal(inparms.size()+outparms.size()));
        
        return executeStoredProcedure(builtSQL.toString(), inparms, outparms);
    }
    
    public Map<String, Object> checkPrice(LinkedHashMap<String, Object> inparms, LinkedHashMap<String, Object> outparms){
        logger.debug("CheckPrcc Initiated");
        StringBuilder builtSQL = new StringBuilder();
        builtSQL.append("CALL ");
        builtSQL.append("checkprice ");

// just generates our param string (?,?,?...) 
builtSQL.append(DataUtilities.genParmPlaceholderStringFromTotal(inparms.size()+outparms.size()));

        return executeStoredProcedure(builtSQL.toString(), inparms, outparms);
    }

}

BaseDAO.java

public class BaseDAO{
    @Autowired
    protected JdbcTemplate jdbcTemplate;
        
    protected static final Logger logger = LoggerFactory.getLogger(BaseDAO.class);

    protected Map<String, Object> executeStoredProcedure(String SQL, LinkedHashMap<String, Object> inParams, LinkedHashMap<String, Object> outParams){
        
        Map<String, Object> result = new HashMap<String, Object>();
            List<SqlParameter> declaredParameters = new ArrayList<SqlParameter>();
            for (Map.Entry<String, Object> entry : inParams.entrySet()) {
                declaredParameters.add(new SqlParameter(entry.getKey().toString(), Types.CHAR));
            }
            for (Map.Entry<String, Object> entry : outParams.entrySet()) {
                declaredParameters.add(new SqlOutParameter(entry.getKey().toString(), Types.CHAR));
            }
            
           result = jdbcTemplate.call(new CallableStatementCreator() {
                public CallableStatement createCallableStatement(Connection connection)
                throws SQLException {
                    CallableStatement callableStatement = connection.prepareCall(SQL);

                    int index = 0;
                    for (Map.Entry<String, Object> entry : inParams.entrySet()) {
                        index++;
                        callableStatement.setString(index, entry.getValue().toString());
                    }

                    for (Map.Entry<String, Object> entry : outParams.entrySet()) {
                        index++;
                        callableStatement.registerOutParameter(index, Types.CHAR);  
                    }
                    return callableStatement;
    
                    }
                }, declaredParameters);

        return result;
    }
    
}

My service is invoked from my rest controller, which pass the args (if that matters).

I've been racking my brain and can't find any information regarding this issue. I'm new to spring boot and Java. I don't believe i'm doing something too egregious.

Solution

In our situation, this was being caused our i-series. If no data is present to return, the system still returns 10 chars from memory - being the last value it just returned. The solution is to always populate the return value to clear the memory.

Not spring-boot after all!

Answered By - Codesidian
Answer Checked By - Willingham (JavaFixing Volunteer)