commit within a "while(result.next())"-loop
1 posts in topic
Flat View  Flat View
Older Post First |   Reply to Topic
TOPIC ACTIONS:
 

Posted By:   Anonymous
Posted On:   Monday, March 26, 2001 05:30 AM

I want to use transactions within a resultset but trying to invoke the con.commit() method within a "while(result.next())"-loop gives me a "Function sequence error" (JDataConnect-Driver) or the loop ends after 1 row (Sun-JdbcOdbcDriver). What´s wrong and how can I solve the problem ? sample code: ... con.setAutoCommit(false); Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); ResultSet rs = stmt.executeQuery("SELECT * FROM Test"); while (rs.next()) { // do something ... con.commit();    More>>

I want to use transactions within a resultset but trying to invoke the con.commit() method within a "while(result.next())"-loop gives me a "Function sequence error" (JDataConnect-Driver) or the loop ends after 1 row (Sun-JdbcOdbcDriver).

What´s wrong and how can I solve the problem ?




sample code:



...



con.setAutoCommit(false);



Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

ResultSet rs = stmt.executeQuery("SELECT * FROM Test");



while (rs.next())

{

// do something

...



con.commit();

}



...

   <<Less
Report | Quote This | Send private message | Reply | Print

Re: commit within a &quot;while(result.next())&quot;-loop

Posted By:   Anonymous  
Posted On:   Friday, March 30, 2001 02:43 PM

Okay, you are starting a transaction before the loop and you are comitting the transaction within the body of the loop. Fine. This code should work for atleast one time. When the body of the loop is executed second time, you again try to commit the transaction. But which transaction? The trasaction that you have started before the loop is not open anymore. It's commited in first go of the loop. So what you can do is to open a fresh transaction every time the body of the loop is executed. Something similar to the code follows


Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); 
ResultSet rs = stmt.executeQuery("SELECT * FROM Test"); 

while (rs.next()) 
{
//start transaction here
con.setAutoCommit(false); 
 
// do something 
... 

con.commit(); 
}

Hope this will help.


regards


Yasir
Report | Quote This | Send private message | Reply | Print
About | Sitemap | Contact