aspose file tools*
The moose likes JDBC and the fly likes Can we divide a ResultSet Object to multiple result set objects? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Can we divide a ResultSet Object to multiple result set objects?" Watch "Can we divide a ResultSet Object to multiple result set objects?" New topic
Author

Can we divide a ResultSet Object to multiple result set objects?

Shankara Sharma
Ranch Hand

Joined: Feb 06, 2012
Posts: 41
I want to divide a result set object to multiple result set objects? Is that possible? If so.. How?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

I don't think there is a practical way to do so using an existing ResultSet. But you could run several queries, each of which would return a subset of the original query.

What are you trying to achieve?
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39885
    
  28
You can put the individual results into Lists, Maps, etc, using some sort of filtering.
Shankara Sharma
Ranch Hand

Joined: Feb 06, 2012
Posts: 41
I have a result set object which contains 23000 records in it. Using prepared statements and batch insertion i am inserting this data into database. But i got a
' ora-01000 maximum open cursors exceeded ' exception.

I tried using Connection.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT) method to close cursors on commit...but i found out that Oracle Database only supports HOLD_CURSORS_OVER_COMMIT. Can you suggest me any alternative?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

The ORA-01000 error generally means that you're leaking database resources. Splitting the ResultSet will result in more cursors to be opened and, if anything, will make things worse.

I don't think ResultSet.CLOSE_CURSORS_AT_COMMIT is going to help you either. You generally don't need to commit frequently in Oracle, and certainly you should not commit while reading from a ResultSet. But even if you do, you risk the ORA-01555 error, not ORA-01000.

It looks like you're not properly closing statements or resultsets in your code. Perhaps we could help more if you post your full code.
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1851
    
  16

Shankara Sharma wrote:I have a result set object which contains 23000 records in it. Using prepared statements and batch insertion i am inserting this data into database.

Are you fetching data (23000 records!) out of the database then trying to write the same data back to a different table? If so, why not leave the data in the database and do it all in a single SQL INSERT instead?


No more Blub for me, thank you, Vicar.
Shankara Sharma
Ranch Hand

Joined: Feb 06, 2012
Posts: 41
I need to perform some business logic on each record and then i need to insert back to another table in database..
Shankara Sharma
Ranch Hand

Joined: Feb 06, 2012
Posts: 41
@ Martin Vajsar : That's not the case because... the same business logic is working in SQL Server DB. SQL server supports Connection.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT) parameter.
Shankara Sharma
Ranch Hand

Joined: Feb 06, 2012
Posts: 41
My businerss logic is.

try
{
PreparedSattement statement = ...

While(resultSet.next())
{
Batch insertion for every 1000 records...
}
}
Catch()
{
Exception handling
}
finally()
{
Close result set.
Close prepared statement.
}

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Shankara Sharma wrote:@ Martin Vajsar : That's not the case because... the same business logic is working in SQL Server DB. SQL server supports Connection.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT) parameter.

Well, SQL Server and Oracle are different databases. Their processing differs dramatically in some aspects. So having a code working well on one of them doesn't mean it will work as well on the other. I'm just guessing here, but if you're (knowingly or unknowingly) relying on commits to close resources, it might make your code usable on SQL Server, but unusable on Oracle.

You should still consider Chris's advice. SQL is quite a powerful language, it is possible that you could incorporate your business logic into it.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Shankara Sharma wrote:My businerss logic is.

...

That looks innocuous (I have lots of code along similar lines and it works well). But the devil could lie in the detail. Could you post the full code? The business logic needn't be there, but every database operation must be included.

By the way, why did you try to use ResultSet.CLOSE_CURSORS_AT_COMMIT, if there are no commits inside the loop?
Shankara Sharma
Ranch Hand

Joined: Feb 06, 2012
Posts: 41
@ Martin Vajsar : Actually, I retrieve data from the result set, send it to a third party application and store the response in a temporary table. After that i am merging both main and temporary table. We use Oracle, Derby and SQL server databases. So the merging mechanism changes base on the database configured..
Shankara Sharma
Ranch Hand

Joined: Feb 06, 2012
Posts: 41
Hmmm... I can not post the code Martin . Sorry for that! But after every batch insertion i commit database.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

No need to apologize for not being able to post the code. But it makes it harder for someone to help you, of course.

The description of your wider process seems reasonable to me. But I don't understand the details. The pseudocode you've posted is the insertion phase, or the merge phase?

In any case, I'd advice to remove the commits, at least when using Oracle database. They are really not necessary. In truth, I've never tried to carry a ResultSet through a commit, so I actually don't know what happens. I'd also suggest to make sure you're using the latest Oracle JDBC drivers.
Shankara Sharma
Ranch Hand

Joined: Feb 06, 2012
Posts: 41
@Martin : The code snippet i posted is at insertion part.. Merge part is working fine...
 
jQuery in Action, 2nd edition
 
subject: Can we divide a ResultSet Object to multiple result set objects?