I'm trying to execute three different prepared statements inside a function which basically does an insert in three different tables.
File registration.java
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
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
[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?
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.
DataSource ds = BaseJDBCHelper.getDataSource();
Connection dbConn = ds.getConnection();