Eclipse Community Forums: EGL Development Tools

Home » Archived » EGL Development Tools » Open SQLResultSet(Can I get a record count?)

Tue, 21 May 2013 09:09

Eclipse User

Is it possible to obtain the record count from an SQLResultSet?

I've developed a simple search facility over an IBM i database table using EDT and it's working really well. I use the prepare statement and then the get statement.

However, I need to get more sophisticated as the get statement is returning a large number of records, which on occasion returns a CRRUI3660E (Could not convert from JSON format), which I believe is caused by the large amount of data being returned.

So, what I'd like to do instead is open an sql result set and then read a block of records, say 100 at a time, but it would be really helpful to display the record count based on the search criteria.

The only way I can think of doing this is to issue a an sql 'select count' statement first, but was hoping that the SQLResultSet included some information about the result set that is built.

Richard

Re: Open SQLResultSet [message #1060689 is a reply to message #1059737]

Mon, 27 May 2013 16:58

Eclipse User

OK, I've waded through numerous forums on the subject and it appears that this isn't a new problem and there isn't a simple answer. It doesn't seem possible to obtain an accurate record/row count from an result set without using a 'SELECT COUNT(*) ...' statement.

However, this has some major drawbacks. The first on performance, I would essentially be running the same sql statement twice; once to get the row count and then a second time to retrieve pages of data. The second is that the data can change between the two statements.

Looking at the SQLResultSet object I see there are setLast() and getPosition() functions, which I can use to obtain the row count, seems to work fine though whether this is more or less performant than a 'SELECT COUNT(*) ...' statement I don't know.

Now, I come to the next stumbling block. To avoid me having to return the whole result set is it possible to maintain a server side cursor from a RUI application?

Nothing I've tried so far seems to work though I'm guessing someone in the EGL community has already come across the problem, please put me out of my misery. Is it possible? If anyone can offer any advice or best practices on RUI data access I'd appreciate it.

Richard

Re: Open SQLResultSet [message #1060908 is a reply to message #1059737]

Wed, 29 May 2013 02:37

Eclipse User

Hi Richard,
About the count, maybe I am off the pitch here, but have you tried
total int = retResult.getSize();
/Trond

Re: Open SQLResultSet [message #1060992 is a reply to message #1060908]

Wed, 29 May 2013 09:08

Eclipse User

Hi Trond,

I think that the getSize() function applies to the array of records returned from a service but this causes me a problem as in my instance the result set can contain a large number of records, which blows the limit of data that can be handled by the client layer.

So, my solution is working at the server level only returning a small subset of the result set (say 50 records), though back at the client level I want to know how many records are in that result set to help with paging controls.

So, the following code does work quite well (in my service).

// Obtain the row count
resultSet.setLast();
listControl.rowCount = resultSet.getPosition();

However, what I don't believe I can do is to keep a result set open and make multiple calls to the server to obtain the next block of 50 records. I'd be interested to hear how are other people are handling large result sets in a RUI application?

Richard

Previous Topic:	How can i contact "yuvaraj g" ?
Next Topic:	Screen Navigation Using EDT Eclipse

Goto Forum:

-=] Back to Top [=-

Current Time: Wed May 21 00:36:47 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter