aspose file tools*
The moose likes Programmer Certification (SCJP/OCPJP) and the fly likes Does CallableStatement have to be closed? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Certification » Programmer Certification (SCJP/OCPJP)
Bookmark "Does CallableStatement have to be closed?" Watch "Does CallableStatement have to be closed?" New topic
Author

Does CallableStatement have to be closed?

Jian Yi
Ranch Hand

Joined: Feb 01, 2002
Posts: 127
I need to modify the following code in a way that the upper level will handle the SQLException. The original code is:
<CODE>
public boolean deleteALP(UserBean user,
String ALP_ALW_IDENT,
String ALP_PROD_PRD
)
{

int val = 0;
CallableStatement cstmt = null;
boolean status = false;
try{
cstmt = user.getConnection().prepareCall("{CALL CP_DBA.del_ALP_gen(?,?)}");
...
val = cstmt.executeUpdate();
if(val==0) status = true;
else status = false;
}
catch (java.sql.SQLException ex){
ex.printStackTrace();
status = false;
}finally{
try{
if(cstmt!=null) cstmt.close();
cstmt = null;
}catch(java.sql.SQLException ex1){}
}
return status;
}
</CODE>
I understand that I need to move the catch block to the upper level and declare the methods with throws SQLExeption. My question is, if SQLException is thrown at the line of "val = cstmt.executeUpdate();" then it got thrown to upper level. So the cstmt never gets closed (that's what finally block is doing). I wonder if it is ok for cstmt without being closed. What could happen?
Thanks a lot,
Jenny
Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
Originally posted by Jenny Yin:
...My question is, if SQLException is thrown at the line of "val = cstmt.executeUpdate();" then it got thrown to upper level. So the cstmt never gets closed (that's what finally block is doing)...

Jenny,
One option you have would be to keep the code in a try block, just remove the catch. This will cause the exception to be thrown to the calling method (this method will have to declare that it throws the exception). Meanwhile, keep the finally block. This way, if an exception is thrown, the finally block will execute (performing your close operation) and then the exception will be thrown. I think this is the behavior you're after.
Something like this:

I think that will work, but I haven't tested it.
Corey
[ March 06, 2002: Message edited by: Corey McGlone ]

SCJP Tipline, etc.
Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
However, as Val pointed out (before deleting his post ), JDBC is not part of the SCJP exam. If you really want to know what bad things can happen if you don't close a CallableStatement, post something in the JDBC forum. As the "solution" seems to be based around exception handling rather than some other JDBC topic, it looks like Val has decided to let this post remain where it is.
Corey
Jian Yi
Ranch Hand

Joined: Feb 01, 2002
Posts: 127
Hi Corey,
Thanks a lot for your prompt reply. But I'm still confused. If cstmt.executeUpdate() throws an exception, the control will be handed over to the caller of deleteALP(). When does the control come back to the finally block in deleteALP?
Thanks,
Jenny
Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
Originally posted by Jenny Yin:
...If cstmt.executeUpdate() throws an exception, the control will be handed over to the caller of deleteALP()...

This is not true. When an exception is thrown in a try block, control is transferred to the nearest catch block that can handle that exception. If none can be found, control is transferred to the calling method but not until all finally blocks have been executed. Basically, this means that the finally block will always execute before the exception is actually thrown to the calling method. This is what will allow you to close the CallableStatement prior to execution being handed over to the method that called this one.
Here's a snippet from the 11.3 Handling of an Exception section of the JLS:
"If no catch clause handling an exception can be found, then the current thread (the thread that encountered the exception) is terminated, but only after all finally clauses have been executed and the method uncaughtException has been invoked for the ThreadGroup that is the parent of the current thread."
I hope that helps,
Corey
Jian Yi
Ranch Hand

Joined: Feb 01, 2002
Posts: 127
Thanks a lot, Corey! That does help! Can I confirm from you one last thing? Is it true that, in your code, if executeUpdate() throws an Exception, the flow jumps to finally block? So that if else won't be executed?
Thanks,
Jenny
Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
Originally posted by Jenny Yin:
...Is it true that, in your code, if executeUpdate() throws an Exception, the flow jumps to finally block? So that if else won't be executed?

That's right. When an exception is thrown, execution immediately flows to an associated catch or finally block. Once again, from the JLS:

The control transfer that occurs when an exception is thrown causes abrupt completion of expressions (�15.6) and statements (�14.1) until a catch clause is encountered that can handle the exception; execution then continues by executing the block of that catch clause. The code that caused the exception is never resumed.

Therefore, if an exception is thrown by the executeUpdate method, control is immediately transferred to the finally block (as there is no catch block to handle the exception locally) and then, as the exception has not yet been caught, it will be thrown to the calling method.
Corey
Jian Yi
Ranch Hand

Joined: Feb 01, 2002
Posts: 127
That helps, Corey!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Does CallableStatement have to be closed?