File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Maintaining an open resultset after commit Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Maintaining an open resultset after commit" Watch "Maintaining an open resultset after commit" New topic
Author

Maintaining an open resultset after commit

EdwinTay
Greenhorn

Joined: Jun 14, 2001
Posts: 1
I'm using the default JDBC-ODBC driver to connect to SQL2K. I'm trying to open a result set ... and then do a couple of INSERT/UPDATE operations based on the data and commit after each record in the result set.
The problem is the resultset will close if I commit (seems to be the default behaviour of the JDBC-ODBC driver?). How can I get around this problem?
Thanks.

<<Edwin>>
Shaunak Desai
Greenhorn

Joined: Jun 14, 2001
Posts: 7

Hi Edwin,
Yes, the resultset is bound to close when you do Commit.Because commit is a function which tells the database that all the insert/update operations are over now.
Everything you want to do with the resultset, you'll have to do it before doing commit or closing the connection.
Anil Vupputuri
Ranch Hand

Joined: Oct 31, 2000
Posts: 527


I'm trying to open a result set ... and then do a couple of INSERT/UPDATE operations based on the data and commit after each record in the result set.

IMHO..
Since ur using JDBC1.x, so u dont have many options. If I understand u correctly, U maintain a copy of resultset in ur local memory, initially when u get ResultSet dump it in a some collection classes and before commiting DB let changes be reflected in ur local memory as well. It reduces the network traffic to DB.


SCJP 1.5, SCEA, ICED (287,484,486)
Frank Carver
Sheriff

Joined: Jan 07, 1999
Posts: 6920
"EdwinTay",
The Java Ranch has thousands of visitors every week, many with surprisingly similar names. To avoid confusion we have a naming convention, described at http://www.javaranch.com/name.jsp . We require names to have at least two words, separated by a space, and strongly recommend that you use your full real name. Posts which don't meet our naming standards are not eligible to win books! Please log in with a new name which meets the requirements.

Thanks.


Read about me at frankcarver.me ~ Raspberry Alpha Omega ~ Frank's Punchbarrel Blog
Edwin Tay
Greenhorn

Joined: Jun 16, 2001
Posts: 1
Thanks for the reply all.
I've managed to find a workaround the problem. I think the default Sun JDBC-ODBC driver (there are other drivers that have an option to not close the cursor on commit) will close the resultset (cursor) automatically once u issue .commit().
This is what I've done. I leave autocommit to true. Open the resultset .. and then within the loop of fetching the resultset call a stored procedure. In the stored procedure I can have another level of transaction that may either commit or rollback.
If the stored procedure completes successfully, it is immediately committed , but the resultset stays open.

<<Edwin>>
Monty Ireland
Ranch Hand

Joined: Oct 03, 2000
Posts: 161
This is normal ... for come DBMS and ODBMS's.
Although JDBC might fix a bad design... look at your DB engine and how you want you application to work before you spend alot of time more tha a few hours with JDBC....
Pls review your DB engine DOC
Pls review your driver DOC
Pls review the design of you application/applet
I you have questions.... find a firend who is a DB developer sn buy him/her a few beer's...
I have been busy... if you still have question... we are all here to help one another...
See you in a few days.

------------------
Multi-Platform Database Developer ( on E.S.T. )


Multi Platform Database Developer & DBA on E.S.T.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Maintaining an open resultset after commit