If you find you are hitting this SQL error when using Integration Bus with JDBC you are probably not closing off everything properly.
As documented in the Knowledge Centre, IIB maintains a connection for a given application until it idles out (default 60 seconds). As a result:
- DO NOT close the actual connection. If you do this then IIB will have to create new connections every time which is very time and resource consuming.
- DO close off all statements or result sets etc.
i.e.
//Connect to Stock database
Connection conn = getJDBCType4Connection(“DB_2”,JDBC_TransactionType.MB_TRANSACTION_AUTO);
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);//Check current stock level
myQuery = “SELECT STOCK FROM MQPERF.STOCK WHERE ITEM = ‘” + Description + “‘”;
resultSet = stmt.executeQuery(myQuery);
resultSet.next();
int itemStock = Integer.parseInt(resultSet.getString(1));xp = new MbXPath(“/Body/wss:SaleRequest/SaleEnvelope/SaleList/Invoice/Item/Quantity”);
xp.addNamespacePrefix(“wss”, “http://WssSale.miwssoap.broker.mqst.ibm.com”);
int orderQuantity= Integer.parseInt(((MbElement) ((List<?>)inAssembly.getMessage().evaluateXPath(xp)).get(0)).getValueAsString());
int newStock = itemStock – orderQuantity;
CallableStatement callableStatement = conn.prepareCall(“{call update_stock(?,?)}”);
callableStatement.setInt(1, newStock);
callableStatement.setString(2, Description);
callableStatement.execute();
callableStatement.close();
resultSet.close();
stmt.close();
progress = “CompletedUpdate”;