makeshkumar_r
asked on
Exception while calling the execute function of CallableStatement
My application is running for a while. Usually 2 weeks once , we get the exception in JDBC call and we restart the application and it runs fine. Finally we found the source of the exception. It's from execute() method of callableStatement. The call returns exception. But there is no information in the exception object. The getMessage() function of Exception returns null and there is no stack trace.
The callableStatement execute() is being called continuously to get the record one by one. This functionality is coded in Servlet.
The callableStatement execute() is being called continuously to get the record one by one. This functionality is coded in Servlet.
statement = connection.prepareCall( "{ CALL procName(?) }" );
do
{
try
{
count = new BizDelegateDBUtility().getInprogressCount(tableName);
if(routerMsgCount>=0)
{
if(count > (routerMsgCount+1))
{
routerStatus = "stop";
break;
}
}
if(count ==0 )
{
introduceDelay = false;
}else
{
introduceDelay = true;
}
int currentCount = 0;
while(currentCount < ( routerMsgCount - count))
{
this.deQueue(connection,statement,tableName);
currentCount++;
}
if(introduceDelay)
{
Thread.sleep(waitTime);
}
}
catch(Exception exception)
{
// Logging error message
routerState++;
}
}
while(routerState==1 && !(routerStatus.equalsIgnoreCase("stop")));
private void deQueue(Connection connection, CallableStatement statement,String tableName)
{
long runSeq;
boolean retrun = false;
try
{
statement.registerOutParameter (1, OracleTypes.INTEGER);
retrun = statement.execute();
runSeq = statement.getLong(1);
if(!statement.wasNull())
{
new BizDelegateDBUtility().updateGroupFlagHistoryStatus(runSeq,"IN PROGRESS",tableName);
consultQManaggerNSend(Long.toString(runSeq));
waitCount = 0;
}
else
{
Thread.sleep(waitTime);
waitCount++;
if(waitCount < 3)
{
sLogMessage = "Waiting for Message from DC...";
// Logging message
}
}
}
catch(SQLException sqlE)
{
// Logging error message
}
catch(InterruptedException interruptedException)
{
// Logging error message
}catch(Exception exception)
{
// Logging error message
}
}
ASKER
Its logged in log4j file. The code snippet is below.Its in each catch block
sLogMessage = "ERROR in Router" + e.getMessage();
Logger.log(ERROR, sLogMessage);
Could you post the *actual* code please?
ASKER
Here you go.
private void startRouter(PrintWriter out)
{
DataStoreUtility dsUtil = new DataStoreUtility();
Connection connection = null;
CallableStatement statement = null;
routerState = 1;
count =0;
sLogMessage = "Starting Router..." + " (" + super.getSpaceLINKSite() + ") " + super.getEnv();
SpaceLINKLogger.log(INFO, sLogMessage);
super.sendSpaceLINKNotify(SPACELINK_PULSE_CHECK, SPACELINK_PULSE_CHECK_SUBJECT, sLogMessage);
try
{
connection = dsUtil.getDSConn(super.getGatewayRouterSPQDCDS());
if(connection==null)
{
SpaceLINKLogger.log(ERROR, "Error in SPQDC Connection");
return;
}
String procName = new BizDelegateDBUtility().getGlobalParm("ROUTER_PROC_NAME");
String tableName = new BizDelegateDBUtility().getGlobalParm("LOG_TABLE_NAME");
String strRouterMsgCount = new BizDelegateDBUtility().getGlobalParm("ROUTER_MSG_COUNT");
int routerMsgCount = -1;
try
{
if(strRouterMsgCount!=null)
{
routerMsgCount = Integer.parseInt(strRouterMsgCount);
}
}catch(NumberFormatException execption)
{
}
statement = connection.prepareCall( "{ CALL "+procName+"(?) }" );
if(statement==null)
{
SpaceLINKLogger.log(ERROR, "Error in SPQDC Connection");
return;
}
do
{
try
{
count = new BizDelegateDBUtility().getInprogressCount(tableName);
if(routerMsgCount>=0)
{
if(count > (routerMsgCount+1))
{
routerStatus = "stop";
break;
}
}
if(count ==0 )
{
introduceDelay = false;
}else
{
introduceDelay = true;
}
int currentCount = 0;
while(currentCount < ( routerMsgCount - count))
{
if(statement.getConnection()==null)
{
sLogMessage = "SPQDC connection failed...";
SpaceLINKLogger.log(ERROR, sLogMessage);
//~email
super.sendSpaceLINKNotify(SPACELINK_FATAL_ERROR, SPACELINK_FATAL_ERROR_SUBJECT, sLogMessage);
connection = dsUtil.getDSConn(super.getGatewayRouterSPQDCDS());
statement = connection.prepareCall( "{ CALL "+procName+"(?) }" );
}
this.deQueue(connection,statement,tableName);
currentCount++;
}
if(introduceDelay)
{
Thread.sleep(waitTime);
}
}
catch(SpaceLINKException spacelinkException)
{
sLogMessage = spacelinkException.getMessage();
SpaceLINKLogger.log(ERROR, sLogMessage);
StackTraceElement[] traceElements = spacelinkException.getStackTrace();
SpaceLINKLogger.log(ERROR, "Exception Stack trace...");
StackTraceElement tempElement = null;
for(int iCount=0; traceElements!=null && iCount<traceElements.length; iCount++)
{
tempElement = traceElements[iCount];
if(tempElement!=null)
{
SpaceLINKLogger.log(ERROR, tempElement.toString());
}
}
routerState++;
}
catch(Exception exception)
{
sLogMessage = SPACELINK_EXCEPTION_MSG + exception.getMessage();
SpaceLINKLogger.log(ERROR, sLogMessage);
StackTraceElement[] traceElements = exception.getStackTrace();
SpaceLINKLogger.log(ERROR, "Exception Stack trace...");
StackTraceElement tempElement = null;
for(int iCount=0; traceElements!=null && iCount<traceElements.length; iCount++)
{
tempElement = traceElements[iCount];
if(tempElement!=null)
{
SpaceLINKLogger.log(ERROR, tempElement.toString());
}
}
routerState++;
}
}
while(routerState==1 && !(routerStatus.equalsIgnoreCase("stop")));
}
catch(Exception exception)
{
sLogMessage = exception.getMessage();
SpaceLINKLogger.log(ERROR, sLogMessage);
//~email
super.sendSpaceLINKNotify(SPACELINK_FATAL_ERROR, SPACELINK_FATAL_ERROR_SUBJECT, sLogMessage);
return;
}
finally
{
handleShutdown(connection, statement);
printMessage(out, "Router stopped...");
}
}
private void deQueue(Connection connection, CallableStatement statement,String tableName) throws SpaceLINKException
{
long runSeq;
boolean retrun = false;
int lineNumber =0;
try
{
lineNumber++;
statement.registerOutParameter (1, OracleTypes.INTEGER);
lineNumber++;
retrun = statement.execute();
lineNumber++;
runSeq = statement.getLong(1);
lineNumber++;
if(!statement.wasNull())
{
SpaceLINKLogger.log(INFO, "Received Run sequence : "+runSeq);
lineNumber++;
new BizDelegateDBUtility().updateGroupFlagHistoryStatus(runSeq,"IN PROGRESS",tableName);
lineNumber++;
consultQManaggerNSend(Long.toString(runSeq));
waitCount = 0;
}
else
{
lineNumber++;
Thread.sleep(waitTime);
lineNumber++;
waitCount++;
if(waitCount < 3)
{
sLogMessage = "Waiting for Message from DC...";
SpaceLINKLogger.log(INFO, sLogMessage);
}
}
}
catch(SQLException sqlE)
{
sLogMessage = SPACELINK_FATAL_ERROR_MSG + sqlE.getMessage() + " occured in lineNumber: "+ lineNumber ;
SpaceLINKLogger.log(ERROR, sLogMessage);
//~email
super.sendSpaceLINKNotify(SPACELINK_FATAL_ERROR, SPACELINK_FATAL_ERROR_SUBJECT, sLogMessage);
sLogMessage = sqlE.getMessage() + " (check server availability/configuration)";
throw new SpaceLINKException(sLogMessage, true);
}
catch(InterruptedException interruptedException)
{
sLogMessage = SPACELINK_FATAL_ERROR_MSG + interruptedException.getMessage() + " occured in lineNumber: "+ lineNumber;
SpaceLINKLogger.log(ERROR, sLogMessage);
//~email
super.sendSpaceLINKNotify(SPACELINK_FATAL_ERROR, SPACELINK_FATAL_ERROR_SUBJECT, sLogMessage);
StackTraceElement[] traceElements = interruptedException.getStackTrace();
SpaceLINKLogger.log(ERROR, "Exception Stack trace...");
StackTraceElement tempElement = null;
for(int iCount=0; traceElements!=null && iCount<traceElements.length; iCount++)
{
tempElement = traceElements[iCount];
if(tempElement!=null)
{
SpaceLINKLogger.log(ERROR, tempElement.toString());
}
}
}catch(Exception exception)
{
sLogMessage = SPACELINK_FATAL_ERROR_MSG + exception.getMessage()+ " occured in lineNumber: "+ lineNumber;
SpaceLINKLogger.log(ERROR, sLogMessage);
//~email
super.sendSpaceLINKNotify(SPACELINK_FATAL_ERROR, SPACELINK_FATAL_ERROR_SUBJECT, sLogMessage);
StackTraceElement[] traceElements = exception.getStackTrace();
SpaceLINKLogger.log(ERROR, "Exception Stack trace...");
StackTraceElement tempElement = null;
for(int iCount=0; traceElements!=null && iCount<traceElements.length; iCount++)
{
tempElement = traceElements[iCount];
if(tempElement!=null)
{
SpaceLINKLogger.log(ERROR, tempElement.toString());
}
}
}
}
You have an unhandled NumberFormatException. The general logging pattern to show stack traces should be
catch(XException e) {
log.error("Some message", e);
}
catch(XException e) {
log.error("Some message", e);
}
ASKER
Thanks for your suggestion. I agree that NumberFormatException not handled.
This is the error message logged in .
SpaceLINK2 Fatal Error :: null occured in lineNumber: 2
This message gives the hint that the exception occured in line 147: retrun = statement.execute();
Do you think handling the number format exception would solve this problem?
This is the error message logged in .
SpaceLINK2 Fatal Error :: null occured in lineNumber: 2
This message gives the hint that the exception occured in line 147: retrun = statement.execute();
Do you think handling the number format exception would solve this problem?
>>Do you think handling the number format exception would solve this problem?
Possible. You need to get stack traces in all cases. Of course the line number is (can be) already given by log4j
Possible. You need to get stack traces in all cases. Of course the line number is (can be) already given by log4j
ASKER
But the value always going to be Number. Its a constant value read from configuration table and the value is entered manually once in a while(Never after deployment). That's why NumberFormatException is not handled.
I don't see any relationship between the problem and the NUmberFormatException. Moreover If i put the solution you suggested in production, it may take more than two weeks to check the solution. The problem is not easily replicated. It happens after long time production started.So I want to make sure we are putting the correct solution. The question is why its not happening all the time. Why Its failing after more than two weeks in production? Is the connection got from connection pooling expired? If its expired, why NullPointerException is not thrown when out parameter is registered? Why NullPinterException is not thrown When execute method() is called on the statement? Why the execute method is throwing exception that can't provide any hint?
I am sorry that I don't totally agree with you on your suggestion.
I don't see any relationship between the problem and the NUmberFormatException. Moreover If i put the solution you suggested in production, it may take more than two weeks to check the solution. The problem is not easily replicated. It happens after long time production started.So I want to make sure we are putting the correct solution. The question is why its not happening all the time. Why Its failing after more than two weeks in production? Is the connection got from connection pooling expired? If its expired, why NullPointerException is not thrown when out parameter is registered? Why NullPinterException is not thrown When execute method() is called on the statement? Why the execute method is throwing exception that can't provide any hint?
I am sorry that I don't totally agree with you on your suggestion.
Well, you must have thought that NFE could been thrown, otherwise, why are you catching it? Incidentally, it really shouldn't normally be caught as it's a runtime exception that indicates an irrecoverable error. You should leave it to be caught by the vm or possibly by your general handler in your case, since the stack trace should be logged.
There's really not much point in speculating what could have caused the error when the correct approach to handling and logging exceptions should reveal what caused it
There's really not much point in speculating what could have caused the error when the correct approach to handling and logging exceptions should reveal what caused it
ASKER
I appreciate your efforts to solve this. Let me explain my point . I agree that Its wrong to have unhandled exception.
If you investigate the code, this NumberFormatException is way before the actual problem. The NFE is a part of initialization phase. Technically speaking when I start the application these variables get initialized. After that control enters the do-while loop and keep on looping through it until the application stopped.
the execute method exception occurs within the do-while loop. One more thing, even NFE occured, the corresponding value is assigned with another value. If NumberFormatException occurs, then the application can't run normally for two weeks as this variable provides the number of messages should be processed at given time. During the normal running time, application runs fine as expected. That means application passed the NFE test :). After running for a while, application throws the exception which originates from do-while loop code. Thank you for your expertise. Expecting your opinion.
If you investigate the code, this NumberFormatException is way before the actual problem. The NFE is a part of initialization phase. Technically speaking when I start the application these variables get initialized. After that control enters the do-while loop and keep on looping through it until the application stopped.
the execute method exception occurs within the do-while loop. One more thing, even NFE occured, the corresponding value is assigned with another value. If NumberFormatException occurs, then the application can't run normally for two weeks as this variable provides the number of messages should be processed at given time. During the normal running time, application runs fine as expected. That means application passed the NFE test :). After running for a while, application throws the exception which originates from do-while loop code. Thank you for your expertise. Expecting your opinion.
I see what you're getting at - i was making a general point really. I wonder what you *have* seen if you've seen no exception message and no stack trace? As i said above, there's not much point in speculating, but it's conceivable that it's a driver bug or some other non-application code without debug information. Try it with another, debuggable driver
Another possibility is to turn up logging at the db so it shows the native error
Another possibility is to turn up logging at the db so it shows the native error
ASKER
I don't want to close this question as I am waiting to find out the result of implemented solution.
Moreover, CEHJ provided valuable input and his time. So I want to reward some points to him. But I can't accept the input as solution. Advice me what should do.
Moreover, CEHJ provided valuable input and his time. So I want to reward some points to him. But I can't accept the input as solution. Advice me what should do.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
catch(Exception exception)
{
// Logging error message
routerState++;
}
>>
So where *is* the error message logged?