[DataDirect][ODBC OPENEDGE driver][OPENEDGE]Number of open cursors exceeds limit

[regulatre]

>[Error] Script lines: 1-1 --------------------------
[DataDirect][ODBC OPENEDGE driver][OPENEDGE]Number of open cursors exceeds limit (7612) 

[Executed: 5/26/06 2:17:25 PM EDT ] [Execution: 0/ms]


I get the above error upon running my stored procedure for the 17th time. (the first 16 executions work great!). Obviously the number of open cursors must be limited to 16, but how is it possible that a cursor is not being deleted after code execution?

Has anyone else had this problem?

I'm fumbling around in the java source for the procedure and nothing has jumped out at me yet as obviously neglegent on my part, but it must be something painfully simple...

Source follows:


CREATE PROCEDURE ItemOnHands
(
IN inItemNumber CHAR(12)
)
RESULT
(
ItemNum CHAR(20),
PkCode1 INTEGER,
PkCode2 INTEGER,
PkCode3 INTEGER,
PkCode4 INTEGER
)
IMPORT
import java.math.*;
BEGIN
String TheQuery;
TheQuery = "SELECT \"descrpt\",\"id\", \"rec-type\", pro_element(tot,8,8) FROM ";
TheQuery = TheQuery + "PUb.totals JOIN PUB.Item ON PUB.totals.\"id\" = PUb.item.\"item#\" ";
TheQuery = TheQuery + "AND PUB.item.\"pack-num\" = \"rec-type\" where \"type\"=9 and \"period\" = 'd' ";
/* append the item number constriction */
/* use the LIKE clause to compare part of the item# */
TheQuery = TheQuery + " AND \"Item#\" like '%" + inItemNumber + "'";
/* append the pack-code constriction thing, which we will append to during the loop of pack codes. */
TheQuery = TheQuery + " AND \"rec-type\" = ";
for (int i=1;i<=4;i++)
{
/* now constrict the query by the package code */
/* Query the DB to obtain the recordset */
String ThisQuery = TheQuery + i;
SQLCursor cur_totals = new SQLCursor (ThisQuery);
/* Open the cursor */
cur_totals.open();
/* Fetch the record */
cur_totals.fetch();
if (!cur_totals.found())
{
/* handle error here - no record found for that item! */
continue;
}
/* Define variables here */
Integer varPkCodeOne;// = null;

try
{
/* grab the on-hand value, which is stored in index 8 of the tot field. It is the fourth field in the result set. */
//varPkCodeOne = (Integer) cur_totals.getValue (4,INTEGER);
varPkCodeOne = (Integer) cur_totals.getValue (4,INTEGER);

}
catch (DhSQLException e)
{
/* do nothing. should we clear the error? */
continue;
//DhSQLException excep = new DhSQLException(777,new String("Error obtaining value in pack code " + i));
//throw excep;
}

switch (i)
{
case 1:
SQLResultSet.set (1,inItemNumber);
SQLResultSet.set (2,varPkCodeOne);
break;
case 2:
SQLResultSet.set (3,varPkCodeOne);
break;
case 3:
SQLResultSet.set (4,varPkCodeOne);
break;
case 4:
SQLResultSet.set (5,varPkCodeOne);
break;
}
/* were done with the resultset for this loop, close the cursor so we can create a new one for the next use. */
cur_totals.close();

/* free up unused variables. */
cur_totals = null;


}
/* end the for loop. Insert the single row that contains fields for each pack code. */
SQLResultSet.insert();


END

 

[Casper]

Hi,

I don't think I can help you but from where do you call your stored procedure (java, ado, sql explorer or....).

Regards,

Casper

 

[regulatre]

I'm calling it from my SQL query tool. I happen to be evaluating Aqua Data Studio (so far it's awesome).

Squirrel SQL 2.2 actually tries to interpret the JS and doesn't pass it to the DB for compilation so I can't use Squirrel to create stored procedures.

Hi,

I don't think I can help you but from where do you call your stored procedure (java, ado, sql explorer or....).

Regards,

Casper

 

[Lee Curzon]

Obviously the number of open cursors must be limited to 16

I don't know Java, but...

If the cursors are not being closed, then the default (50) would make more sense, as you are opening a cursor 4 times each pass (16 * 4 = 48). The 17th pass would therefore break the 50 limit as you are experiencing.

What happens if you put the cursor close in a Finally clause? That may not make sense with your code, I'm not sure, but check the close is always executed, regardless of exceptions.

 

[Casper]

I asked for the caling of the stored procedure because there is an isue with jdbc and java in combination with resultsets. The cursor isn't released until the resultset is closed:

KB P35592:

Status: Unverified

SYMPTOM(s):
Possible cause for cursor not being released using JDBC
Number of open cursors exceeds limit (7612)
Using java program accessing Progress database via JDBC
Java program uses result sets

CAUSE:
Cursor is not released until the result set is closed

FIX:
Make sure that the cursor is being released by using by adding a
rs.close()

But that doesn't seem to be the problem if I understand you correctly....

Casper

 

[Lee Curzon]

To narrow the error down, remove the non essential (ie. non opening/closing) code, and rerun, to make sure the bare bones cursor logic is correct.

There are several examples in the KB for opening a cursor - most of them use a

cursor.open ();
cursor.fetch ();
while (cursor.found())
{
    cursor.fetch();
}
cursor.close ();

construct which is different to your code.


If you run your code without the update logic, and it still breaks, it may be your cursor construct logic is incorrect, and rewriting it along the lines of the above may help.

If it works, then the error is probably in your switch/try clauses.

 

[Lee Curzon]

I asked for the caling of the stored procedure because there is an isue with jdbc and java in combination with resultsets. The cursor isn't released until the resultset is closed:

KB P35592:

I also saw this, and took the resultset closing to mean the

cur_totals.close

statement regulatre is using.

But perhaps (as I think you may be implying) a SQLResultSet.close() command is needed, but I don't have Java knowledge to know if this is what the KB entry means.

 

[regulatre]

It appears that SQLResultSet does not offer a close method. I get the following errors when I try to use the close method.

>[Error] Script lines: 1-99 -------------------------
[DataDirect][ODBC OPENEDGE driver][OPENEDGE]Error from Java compiler. Compiler messages follow.(10727)
C:\OpenEdge\WRK\SQL_ITEMONHANDS_SP.java:101: cannot resolve symbol
symbol : method close ()
location: class com.progress.sql.sp.DhSQLResultSet
SQLResultSet.close();
^
1 erro 

I also saw this, and took the resultset closing to mean the

cur_totals.close

statement regulatre is using.

But perhaps (as I think you may be implying) a SQLResultSet.close() command is needed, but I don't have Java knowledge to know if this is what the KB entry means.

 

[Lee Curzon]

One last try:

Again, bearing in mind my Java ignorance,

shouldn't

 /* Insert the single row that contains fields for each pack code. */
SQLResultSet.insert();

be closer to the .set code? eg. inside the loop?

the fact that it's scope contains the 4 open/close cursor statements may have some bearing on your problem.

 

[regulatre]

Yesssssssss!!!!!!

GOT IT!

While I was in trying your various recommendations, I realized that, when no record is found, I'm doing a "continue". Which skips all the rest of the commands in the braces and starts the next loop. Well the problem with that is that I'm releasing the cursor at the bottom of the block, so by continuing, I was skipping the close() and release (setting it to null).

I just recompiled it, tested it, and I can run it as many times as I want now!


Thanks guys.


New Code Follows:

CREATE PROCEDURE ItemOnHands
(
    IN inItemNumber CHAR(12)
)

RESULT
(
    ItemNum CHAR(20),
    PkCode1 INTEGER,
    PkCode2 INTEGER,
    PkCode3 INTEGER,
    PkCode4 INTEGER
)
IMPORT
import java.math.*;
BEGIN
String TheQuery;
TheQuery = "SELECT \"descrpt\",\"id\", \"rec-type\", pro_element(tot,8,8) FROM ";
TheQuery = TheQuery + "PUb.totals JOIN PUB.Item ON PUB.totals.\"id\" = PUb.item.\"item#\" ";
TheQuery = TheQuery + "AND PUB.item.\"pack-num\" = \"rec-type\" where \"type\"=9 and \"period\" = 'd' ";
/* append the item number constriction */
/* use the LIKE clause to compare part of the item# */
TheQuery = TheQuery + " AND \"Item#\" like '%" + inItemNumber + "'";
/* append the pack-code constriction thing, which we will append to during the loop of pack codes.  */
TheQuery = TheQuery + " AND \"rec-type\" = ";
for (int i=1;i<=4;i++)
{
/* now constrict the query by the package code */
/* Query the DB to obtain the recordset */
String ThisQuery = TheQuery + i;
SQLCursor cur_totals = new SQLCursor (ThisQuery);
/* Open the cursor */
cur_totals.open();
/* Fetch the record */
cur_totals.fetch();
if (!cur_totals.found())
{
    /* handle error here - no record found for that item!  */

    /* if we jump to a new loop then make sure this cursor gets closed and released. */
    cur_totals.close();
    cur_totals = null; 
    continue; /* Skip past pack codes with no on-hand counts. */
}
/* Define variables here */
Integer varPkCodeOne;// = null;

try
{
    /* grab the on-hand value, which is stored in index 8 of the tot field. It is the fourth field in the result set. */
    //varPkCodeOne = (Integer) cur_totals.getValue (4,INTEGER);
    varPkCodeOne = (Integer) cur_totals.getValue (4,INTEGER);

}
catch  (DhSQLException e)
{
    /* do nothing. should we clear the error? */
    continue;
    //DhSQLException excep = new DhSQLException(777,new String("Error obtaining value in pack code " + i));
    //throw excep;
}

switch (i)
{
case 1:
  SQLResultSet.set (1,inItemNumber);
  SQLResultSet.set (2,varPkCodeOne);
  break;
case 2:
  SQLResultSet.set (3,varPkCodeOne);
  break;
case 3:
  SQLResultSet.set (4,varPkCodeOne);
  break;
case 4:
  SQLResultSet.set (5,varPkCodeOne);
  break;
}
/* were done with the resultset for this loop, close the cursor so we can create a new one for the next use. */
cur_totals.close();

/* free up unused memory by setting variable(s) to null. */
cur_totals = null;


}

/* end the for loop. Insert the single row that contains fields for each pack code.  */
SQLResultSet.insert();

/* Close the result set. */
//SQLResultSet.close();

/* delete the resultSet variable (free up memory). */
SQLResultSet = null;



END

To narrow the error down, remove the non essential (ie. non opening/closing) code, and rerun, to make sure the bare bones cursor logic is correct.

There are several examples in the KB for opening a cursor - most of them use a

cursor.open ();
cursor.fetch ();
while (cursor.found())
{
    cursor.fetch();
}
cursor.close ();

construct which is different to your code.


If you run your code without the update logic, and it still breaks, it may be your cursor construct logic is incorrect, and rewriting it along the lines of the above may help.

If it works, then the error is probably in your switch/try clauses.

 

[Lee Curzon]

Good work fella