Robs Blog

Blog containing all sorts of info and help from my day to day life

Categories

Integration Bus – JDBC – SQLCODE=-805, SQLSTATE=51002

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”;

November 7th, 2014 by robconvery
Posted in Uncategorized

Leave a Reply