jQuery in Action, 2nd edition*
The moose likes JDBC and the fly likes Oracle JDBC and Cursor question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Oracle JDBC and Cursor question" Watch "Oracle JDBC and Cursor question" New topic
Author

Oracle JDBC and Cursor question

Henrik Engert
Ranch Hand

Joined: Apr 26, 2005
Posts: 70
We have a database class that handles building a callable statement (OracleCallableStatement). We register in and out parameters. We fetch the outparameter Cursor like this:



This cursor is then added to a List like this:



We then close the OracleCallableStatement before we access the Cursor. This works fine, but is this the correct way of doing things? Will there be memory complications since we do not access the cursor until after the CallableStatements has been closed?

I have to add that it works fine for our application that is a regular Java application, but it does not work if the code is run in a Servlet.

If we do not close the OracleCallableStatement like we do now, then I would have to call OracleCallableStatement.close every time we use the database class to access the database. That would be a pain to do, but now we call close() right away to generalize things.

Any suggestions?

Thanks.
[ December 04, 2006: Message edited by: Henrik Engert ]

SCJP 5.0, SCWCD
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30130
    
150

Henrik,
It's risky. Calling close on a Statement is allowed to implicitly close the ResultSet. Then the cursor is pointint to nothing.

Who creates the cStmt? Usually the code that creates an object is the one the closes it.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Henrik Engert
Ranch Hand

Joined: Apr 26, 2005
Posts: 70
Hi,

I suspected it was risky since when switching to Oracle 10.0.2.2 from 10.0.2.1 we encountered a problem with closed ResultSets. Before we could call close on the statement and use it (the statement) afterwards for some odd reason, but it seems Oracle has fixed this.

We have a single class that builds these statements and executes them, then we have a finally block that closes the statement. After this we access the resultset etc. So it is general solution that's not good at all.

This forces me to write a seperate method that closes the statement which needs to be called from wherever I use the class that build/executes the statemements. The only problem with that will be whenever we only fetch small amounts from the resultset at a time.

Anyway, I just wanted to confirm my thoughts on the problem.

Thanks.

[ December 05, 2006: Message edited by: Henrik Engert ]
[ December 05, 2006: Message edited by: Henrik Engert ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Oracle JDBC and Cursor question
 
Similar Threads
casting objects with parameterized types
How do I convert a list of list into array [] []
Bridge between REST & WS
string editor
Very urg help me