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.
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.
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.
"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 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.
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. )