Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Maintaining an open resultset after commit

 
EdwinTay
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Shaunak Desai
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 527
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


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.
 
Frank Carver
Sheriff
Posts: 6920
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"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.
 
Edwin Tay
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Monty Ireland
Ranch Hand
Posts: 161
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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. )
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic