Getting an Openedge error: [DataDirect][OpenEdge JDBC Driver

Hello,

With OpenEdge database, in the past, this was observed due to maximum cursors (default 50) being exceeded.

One example where this error occurred was, with JDBC client code that fetched some data and the resultset returned was not closed at the right time ( as in the example at: www.progresstalk.com/.../datadirect-odbc-openedge-driver-openedge-number-of-open-cursors-exceeds-limit.100002).

Can you share the details of the trigger?

Regards,

Karthikeyan

The triggers are fairly complex, and fetching a large number of related record id's.

Not sure why this would be holding SQL cursors after the rbv_api.select/get commands have returned their arrays of values?

Is there any way to deallocate SQL cursors from within Rollbase???

There are several selectQuery commands and getrelatedid commands, and some of these are within for loops based on previous query results.

There may be some scope for reducing the number of calls, but not without complicating the logic more than it already is.

Simple solution would be increase the number of max SQL cursors per thread. (See note at bottom:)

cut down version of the code:

/* getting list of workgroups this user belongs to) */

var userswrkgrps=rbv_api.getRelatedIds ("R34095", {!R38066});

var appusers = new Array; /* This is a placeholder for final array of user ids's */

var isempty=-1;

/* getting details of users who can approve all timesheets. */

var appAll = rbv_api.selectQuery("SELECT R34022 FROM lxtt_tsAdminRole WHERE usrHasA1Access=true and a1AllUsers=true", 1000);

for(var i=0; i<appAll.length; ++i) {

 appusers.push(Number(appAll[0]));

}

rbv_api.println( appusers.length);

for(var i=0; i<appusers.length; ++i) { rbv_api.println( appusers); }

var appsomeusers = rbv_api.selectQuery("SELECT id, R34022  FROM lxtt_tsAdminRole WHERE usrHasA1Access=true and a1AllUsers=false and R34095<>?", 2000, isempty);

for(var i=0; i<appsomeusers.length; ++i) {

  var candousers=rbv_api.getRelatedIds ("R34095", Number(appsomeusers[0]));

  for(var j=0; j<candousers.length; ++j) {

     if ( candousers[j] == {!R38066} ) { appusers.push(Number(appsomeusers[1]));}

  }  

}

/* getting details of users who can approve some groups. */

if ( userswrkgrps.length > 0 ) {

  var appsomegroups = rbv_api.selectQuery("SELECT id, R34022 FROM lxtt_tsAdminRole WHERE usrHasA1Access=true and a1AllUsers=false and R34088<>?", 2000, isempty);

  for(var i=0; i<appsomegroups.length; ++i) {

     var candogrps=rbv_api.getRelatedIds ("R34088", Number(appsomegroups[0]));

     for(var j=0; j<candogrps.length; ++j) {

        for(var k=0; k<userswrkgrps.length; ++k) {

           if ( candogrps[j] == userswrkgrps[k] ) {

              appusers.push(Number(appsomegroups[1]));

           }

        }

     }

  }  

}

var y=appusers.sort();

/* remove duplicates */

for(var i=0; i<y.length; ++i) {

  for(var j=i+1; j<y.length; ++j) {

     if(y === y[j])

         y.splice(j--, 1);

  }

}

var canselfapprove=Boolean( 1 === rbv_api.selectNumber("SELECT count(1) FROM lxtt_tsAdminRole WHERE R34022=? and usrHasA1Access=true and canSelfApprove=true", {!R38066} ));

/* remove user if they cannot self approve */

for(var i=0; i<y.length; ++i) {

  rbv_api.println(y);

  if ( y === {!R38066} && canselfapprove !== true ) {

     y.splice(i,1);

  }

}

return y;

Note: (our DBA's have been investigating this as well.)

http://knowledgebase.progress.com/articles/Article/P89401

This includes details for increasing the max SQLCursors per thread. The mentioned Error code is different to the error we are getting, but it looks like this might fix the issue..

-SQLCursors corresponding to OPEN_CURSORS:

Maximum number of cursors per thread
Very little runtime overhead
Increase if -20125 error encountered
Valid range: 16 - 512, Default: 50

Did the suggestions from the Knowledge Base article regarding the -SQLCursors startup parameter help?

For a Private Cloud installation, setting a higher value for -SQLCursors (max 512) may help resolve the problem.