Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EGL Development Tools » Open SQLResultSet(Can I get a record count?)
Open SQLResultSet [message #1059737] Tue, 21 May 2013 13:09 Go to next message
Richard Moulton is currently offline Richard Moulton
Messages: 92
Registered: August 2011
Location: Devon, UK
Member
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 20:58 Go to previous messageGo to next message
Richard Moulton is currently offline Richard Moulton
Messages: 92
Registered: August 2011
Location: Devon, UK
Member
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 06:37 Go to previous messageGo to next message
Trond Einar Nilsen is currently offline Trond Einar Nilsen
Messages: 33
Registered: December 2011
Member
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 13:08 Go to previous message
Richard Moulton is currently offline Richard Moulton
Messages: 92
Registered: August 2011
Location: Devon, UK
Member
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:
  


Current Time: Wed Oct 22 22:22:10 GMT 2014

Powered by FUDForum. Page generated in 0.01874 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software