GeeCON Prague 2014*
The moose likes JDBC and the fly likes Max Open Cursor! Please Help. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Max Open Cursor! Please Help." Watch "Max Open Cursor! Please Help." New topic
Author

Max Open Cursor! Please Help.

rohan prakrit
Greenhorn

Joined: Nov 18, 2004
Posts: 4
Hi !

I have a problem with MAX Open Cursors !

My requirement is like this, i need to query 50,000 odd records from one database and then query depending on primary key to the same table in another database. If the data exists in db2 then i need to update else i need to insert the record from db1 into db2.

****************************************************************************My psuedo code looks like this :

connection object 1 for db1
connection object 2 for db2

preparedstatement1(query on db1)
iterate through resultset1 - this iterates 50,000 times.
{

preparedstatement2(query if data for this particular record exists in db2)
get the count
close the preparedstatement2 and it's corresponding resultset.
explicitly set this as null too

if(count>0)
{
preparedstatement3(form the update query on db2 with values from resultset1)
resultset3

add the preparedstatement3 to the batch

if batchsize exceeds the limit then [the batch limit is set as 50]
{
execute the batch in one shot
close preparedstatement3 and resultset3
also set them as null
}
}
else
{
preparedstatement4(form the insert query on db2 with values from resultset1)
resultset4

add the preparedstatement4 to the batch

if batchsize exceeds the limit then [the batch limit is set as 50]
{
execute the batch in one shot
close preparedstatement4 and resultset4
also set them as null
}
}
}

close preparedstatement1 and resultset1
also set them as null

close connection object 1 and 2
also set them as null

******************************************************************************************************************

My open_cursor limit is 450 and diligently my code fails when updating/inserting 460th odd record !!! why does this happen when i closed my prepared statement and resultset as frequently as i could ?

Please help me with this issue ! am at my wits end !

Thanks !
-Rohan
[ November 18, 2004: Message edited by: Bear Bibeault ]
rohan prakrit
Greenhorn

Joined: Nov 18, 2004
Posts: 4
Gosh !

How could i be soo stupid. I was defeating the very purpose of Prepared Statement and that was the reason why i encountered this Max Open Cursor Problem.

Now am creating all the prepared statements before iterating through the resultset1. Inside the iteration i just bind the variables to prepared statement 2,3 and 4 and that solved the issue for me

Anyways, Thanks Guys !
-Rohan
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30586
    
154

Rohan,
Thanks for posting the solution for people who read this in the future.

And welcome to JavaRanch!


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
GeeCON Prague 2014
 
subject: Max Open Cursor! Please Help.