>[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
[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