I’m trying to execute three different prepared statements inside a function which basically does an insert in three different tables.
File registration.java
JavaScript
x
public String regPoints(Timestamp time2, String usersID2) throws PDHException{ String METHODNAME="regPoints"; String msg = "Failure"; Long lUserid,ldataid,lpointsid = 0L;Connection conn = null; PreparedStatement ps1=null,ps2=null,ps3 = null; try{lUserid = ECKeyManager.singleton().getNextKey("hp_loyalty_users"); ldataid = ECKeyManager.singleton().getNextKey("hp_loyalty_data"); lpointsid = ECKeyManager.singleton().getNextKey("hp_loyalty_points"); conn = createConnection(); ps1=conn.prepareStatement(INSERT_USERS_REGISTRATION); ps2=conn.prepareStatement(INSERT_DATA_REGISTRATION); ps3=conn.prepareStatement(INSERT_POINTS_REGISTRATION); conn.setAutoCommit(false); logMsg(Level.INFO, METHODNAME,"Registration Started for WCS userid: "+usersID2); logMsg(Level.INFO, METHODNAME,"Query started for users table to register"); ps1.setLong(1, lUserid); ps1.setLong(2, Long.valueOf(usersID2)); ps1.setInt(3, regPoints); ps1.setInt(4, Constants.LOYALTY_REG_DEFAULT); ps1.setTimestamp(5,time2); ps1.setTimestamp(6,time2); ps1.setString(7,Constants.LOYALTY_TRUE); ps1.addBatch(); logMsg(Level.INFO, METHODNAME,"Query started for data table to register"); ps2.setLong(1, ldataid); ps2.setLong(2, lUserid); ps2.setString(3, Constants.LOYALTY_NA); ps2.setString(4, Constants.LOYALTY_NA); ps2.setString(5, Constants.LOYALTY_NA); ps2.setFloat(6, Constants.LOYALTY_REG_DEFAULT); ps2.setInt(7, Constants.LOYALTY_REG_DEFAULT); ps2.setTimestamp(8,time2); ps2.setInt(9, regPoints); ps2.setInt(10, Constants.LOYALTY_REG_DEFAULT); ps2.setString(11, Constants.LOYALTY_REGISTRATION); ps2.setTimestamp(12,time2); ps2.setTimestamp(13,time2); ps2.setInt(14, Constants.LOYALTY_REG_DEFAULT); ps2.setString(15, Constants.LOYALTY_NA); ps2.addBatch(); logMsg(Level.INFO, METHODNAME,"Query started for points table to register"); ps3.setLong(1, lpointsid); ps3.setLong(2, lUserid); ps3.setInt(3, regPoints); ps3.setString(4, Constants.LOYALTY_FALSE); ps3.setLong(5, ldataid); ps3.setTimestamp(6,time2); ps3.setString(7, Constants.LOYALTY_FALSE); ps3.setTimestamp(8,time2); ps3.addBatch(); int[] users = ps1.executeBatch(); logMsg(Level.INFO, METHODNAME,"rows processed in users table: "+users.length); int[] data = ps2.executeBatch(); logMsg(Level.INFO, METHODNAME,"rows processed in data table: "+data.length); int[] points = ps3.executeBatch(); logMsg(Level.INFO, METHODNAME,"rows processed in data table: "+points.length); conn.commit(); msg = "Success"; logMsg(Level.INFO, METHODNAME,"Registration Completed for WCS userid: "+usersID2); }catch (Exception e) { if(null!=conn) { logMsg(Level.SEVERE, METHODNAME,"Registration Failed for WCS userid: "+usersID2); try { conn.rollback(); throw new PDHException(e); } catch (SQLException e1) { throw new PDHException(e1); } } }finally { JDBCUtils.closeStatement(ps1); JDBCUtils.closeStatement(ps2); JDBCUtils.closeStatement(ps3); JDBCUtils.closeConnection(conn); } return msg; }createConnection method – I’m using ojdbc6.jar
JavaScript
private Connection createConnection() throws SQLException, Exception { final String METHODNAME = "createConnection"; Connection dbConn = null; Class.forName("oracle.jdbc.driver.OracleDriver"); dbConn = DriverManager.getConnection(jdbcURL, CipherTextUtil.decodeHexString(userName) , CipherTextUtil.decodeHexString(pass)); dbConn.setAutoCommit(false); return dbConn; }When I call the regPoints function from a webpage in my local RAD or local environment its working perfectly fine. But the same doesn’t work when it’s running on a test environment. When I checked the logs I could see the below warning in the SystemOut.log and I had checked the database too. It never inserts any data because the initial insert is the point where it gets hung
JavaScript
[7/28/20 5:26:42:251 UTC] 00000096 ThreadMonitor W WSVR0605W: Thread "WebContainer : 2" (00000118) has been active for 324493 milliseconds and may be hung. There is/are 1 thread(s) in total in the server that may be hung. at java.net.SocketInputStream.socketRead0(Native Method) at java.net.SocketInputStream.read(SocketInputStream.java:165) at java.net.SocketInputStream.read(SocketInputStream.java:134) at oracle.net.ns.Packet.receive(Packet.java:282) at oracle.net.ns.DataPacket.receive(DataPacket.java:103) at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:230) at oracle.net.ns.NetInputStream.read(NetInputStream.java:175) at oracle.net.ns.NetInputStream.read(NetInputStream.java:100) at oracle.net.ns.NetInputStream.read(NetInputStream.java:85) at oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:122) at oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:78) at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1179) at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1155) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:279) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205) at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1008) at oracle.jdbc.driver.OraclePreparedStatement.executeForRowsWithTimeout(OraclePreparedStatement.java:9870) at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:9974) at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:213) at com.hp.commerce.hployalty.commands.HpLoyaltyTranscationCmdImpl.registrationFlow(HpLoyaltyTranscationCmdImpl.java:739) at com.hp.commerce.hployalty.commands.HpLoyaltyTranscationCmdImpl.performExecute(HpLoyaltyTranscationCmdImpl.java:168) at com.ibm.commerce.command.ECCommandTarget.executeCommand(ECCommandTarget.java:157) at com.ibm.ws.cache.command.CommandCache.executeCommand(CommandCache.java:332) at com.ibm.websphere.command.CacheableCommandImpl.execute(CacheableCommandImpl.java:167) at com.ibm.commerce.command.MeasuredCacheableCommandImpl.execute(MeasuredCacheableCommandImpl.java:68) at com.ibm.commerce.command.AbstractECTargetableCommand.execute(AbstractECTargetableCommand.java:199) at com.hp.commerce.usermanagement.commands.ExtUserRegistrationAddCmdImpl.performExecute(ExtUserRegistrationAddCmdImpl.java:251) at com.ibm.commerce.command.ECCommandTarget.executeCommand(ECCommandTarget.java:157) at com.ibm.ws.cache.command.CommandCache.executeCommand(CommandCache.java:332) at com.ibm.websphere.command.CacheableCommandImpl.execute(CacheableCommandImpl.java:167) at com.ibm.commerce.command.MeasuredCacheableCommandImpl.execute(MeasuredCacheableCommandImpl.java:68) at com.ibm.commerce.command.AbstractECTargetableCommand.execute(AbstractECTargetableCommand.java:199) at com.ibm.commerce.component.BaseComponentImpl.executeCommand(BaseComponentImpl.java:285) at com.ibm.commerce.component.WebAdapterComponentImpl.executeCommand(WebAdapterComponentImpl.java:46) at com.ibm.commerce.component.objimpl.WebAdapterServiceBeanBase.executeCommand(WebAdapterServiceBeanBase.java:58) at com.ibm.commerce.component.objects.EJSLocalStatelessWebAdapterService_ce749a4a.executeCommand(EJSLocalStatelessWebAdapterService_ce749a4a.java:31) at com.ibm.commerce.component.objects.WebAdapterServiceAccessBean.executeCommand(WebAdapterServiceAccessBean.java:160) at com.ibm.commerce.webcontroller.WebControllerHelper.executeCommand(WebControllerHelper.java:2781) at com.ibm.commerce.struts.BaseAction.invokeService(BaseAction.java:1600) at com.ibm.commerce.struts.LTPATokenGenerationEnabledBaseAction.invokeService(LTPATokenGenerationEnabledBaseAction.java:129) at com.ibm.commerce.struts.BaseAction.executeAction(BaseAction.java:663) at com.ibm.commerce.struts.BaseAction.execute(BaseAction.java:152) at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431) at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236) at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196) at com.ibm.commerce.struts.ECActionServlet.processRequest(ECActionServlet.java:229) at com.ibm.commerce.struts.ECActionServlet.doPost(ECActionServlet.java:184) at javax.servlet.http.HttpServlet.service(HttpServlet.java:595) at com.ibm.commerce.struts.ECActionServlet.service(ECActionServlet.java:718) at javax.servlet.http.HttpServlet.service(HttpServlet.java:668) at com.ibm.ws.cache.servlet.ServletWrapper.serviceProxied(ServletWrapper.java:307) at com.ibm.ws.cache.servlet.CacheHook.handleFragment(CacheHook.java:562) at com.ibm.ws.cache.servlet.CacheHook.handleServlet(CacheHook.java:255) at com.ibm.ws.cache.servlet.ServletWrapper.service(ServletWrapper.java:259) at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1233) at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:782) at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:481) at com.ibm.ws.webcontainer.servlet.ServletWrapperImpl.handleRequest(ServletWrapperImpl.java:178) at com.ibm.ws.webcontainer.filter.WebAppFilterChain.invokeTarget(WebAppFilterChain.java:136) at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:97) at com.hp.ecom.b2c.service.AuthenticationFilter.doFilter(AuthenticationFilter.java:132) at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195) at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91) at com.ibm.commerce.foundation.server.services.servlet.filter.HttpSecurityFilter.doFilter(HttpSecurityFilter.java:268) at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195) at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91) at com.ibm.commerce.dynacache.filter.CacheFilter$1.run(CacheFilter.java:390) at com.ibm.commerce.dynacache.filter.CacheFilter.doFilter(CacheFilter.java:553) at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195) at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91) at com.ibm.commerce.webcontroller.RuntimeServletFilter.doFilterAction(RuntimeServletFilter.java:831) at com.ibm.commerce.webcontroller.RuntimeServletFilter.access$0(RuntimeServletFilter.java:614) at com.ibm.commerce.webcontroller.RuntimeServletFilter$1.run(RuntimeServletFilter.java:458) at com.ibm.commerce.webcontroller.RuntimeServletFilter.doFilter(RuntimeServletFilter.java:500) at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195) at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91) at com.hp.filter.LogFilter.doFilter(LogFilter.java:37) at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195) at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91) at com.hp.seo.filter.SEOSecurityCheckFilter.doFilter(SEOSecurityCheckFilter.java:161) at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195) at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91) at com.hp.seo.filter.SEOPdpCLPRedirectFilter.doFilter(SEOPdpCLPRedirectFilter.java:360) at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195) at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91) at com.hp.seo.filter.SEOHPCoBrandDynamicToStaticFilter.doFilter(SEOHPCoBrandDynamicToStaticFilter.java:185) at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195) at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91) at com.hp.seo.filter.SEOContentViewRedirectFilter.doFilter(SEOContentViewRedirectFilter.java:218) at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195) at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91) at com.hp.seo.filter.SEOCaseConversionFilter.doFilter(SEOCaseConversionFilter.java:347) at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195) at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91) at com.hp.vanity.filter.SEOVanityURLFilter.doFilter(SEOVanityURLFilter.java:98) at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195) at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91) at com.ibm.ws.webcontainer.filter.WebAppFilterManager.doFilter(WebAppFilterManager.java:967) at com.ibm.ws.webcontainer.filter.WebAppFilterManager.invokeFilters(WebAppFilterManager.java:1107) at com.ibm.ws.webcontainer.webapp.WebApp.handleRequest(WebApp.java:4047) at com.ibm.ws.webcontainer.webapp.WebGroup.handleRequest(WebGroup.java:304) at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:1016) at com.ibm.ws.webcontainer.WSWebContainer.handleRequest(WSWebContainer.java:1817) at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:213) at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:463) at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewRequest(HttpInboundLink.java:530) at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.processRequest(HttpInboundLink.java:316) at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.ready(HttpInboundLink.java:287) at com.ibm.ws.ssl.channel.impl.SSLConnectionLink.determineNextChannel(SSLConnectionLink.java:1187) at com.ibm.ws.ssl.channel.impl.SSLConnectionLink.readyInboundPostHandshake(SSLConnectionLink.java:768) at com.ibm.ws.ssl.channel.impl.SSLConnectionLink$MyHandshakeCompletedCallback.complete(SSLConnectionLink.java:464) at com.ibm.ws.ssl.channel.impl.SSLUtils.handleHandshake(SSLUtils.java:1137) at com.ibm.ws.ssl.channel.impl.SSLHandshakeIOCallback.complete(SSLHandshakeIOCallback.java:87) at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:175) at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217) at com.ibm.io.async.AsyncChannelFuture.fireCompletionActions(AsyncChannelFuture.java:161) at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:138) at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:204) at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:775) at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:905) at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1892)Is this something to do with jdbc version? Or do we have any specific reasons for this issue?
Advertisement
Answer
I managed to take the connection from connection pool instead of creating one to avoid the above issue. By default WCS doesnt allow executing batch statements using serverjdbchelperaccessbean. So i took the datasource from basejdbchelper class and got the connection.
JavaScript
DataSource ds = BaseJDBCHelper.getDataSource(); Connection dbConn = ds.getConnection();