|
Hi I would like to implement "infinite scroll" in a web client library called ExtJS.(The backend is SQLA,JAVA,DBUtils,REST,JSON) This means each DB fetch only returns a fraction of the list from the DB defined by parameters Start and Limit. This divides the list into "pages" (of size Limit) and only "visible" pages are fetched. ExtJS also wants to know the total size of the list. How do I go about returning a "page" of rows plus a scalar that is the total size of the list, using a stored procedure Or am I forced to call 2 stored procedures? Peter |
|
If you don't mind the total count returned with each row you can use select top x start at y column names, COUNT(*) OVER() AS TotalRows from tablename order by xyz |
|
It would be nice if we could have a similar setup for json as we have for XML: If we could replace XML with JSON then you would get what you need. Maybe something for the future? I tried to achieve something like this with a work around but I couldn't succeed. |
Do you need stored procedures, or OData can suit you? There will be two calls ($count + getEntitySet), but the usage is simpler than writing of customer SPs.
yes it has to be a SP
Can ExtJS consume a single SP with multiple result sets? If so, you could use two SELECTs in your SP, one to return the count, one to return the according subset of rows of the desired result set.
Note: While a SP can return both a result set and a return value, AFAIK, with SQL Anywhere you can usually only read the return value after you have consumed the full result set, so it may be not helpful here to return the count via the SP's optional return value.
As to "paging" a result set: That can be done with "SELECT TOP n START AT m ..." (or the MySQLish variants with a LIMIT/OFFSET clause).
With respect to concurrency:
Will the number of rows in the whole result set be constant during several SP calls to return subset of the rows, or will you need to recalculate the count dynamically (as other users may have added/modified/deleted rows in-between)?
We are using DBUtils in the Java REST server to turn the resultset into JSON. I have not seen anything in the DBUtils documentation about being able to handle multiple result sets. We did a quick test and only saw one result set. However we are new to SQLA and DBUtils so the test may well have been invalid
Perhaps someone can post what the SQLCA side would look like returning 2 result sets where 1 is a bunch of rows and the other is a scalar count
Without further details it's not possible to tell how these stored procedures might have to look, so here are only very general hints from the docs:
I dont think is is so important. The count is whatever it happens to be each time a page of data is fetched. there might be an occasional inconsistency at the end of the list. But I think in a lot of scenarios this s acceptable
Below is the original SP to test returning 2 resultsets (some irrelevant stuff removed).
The test failed but the problem could well be in DBUtils. Is the general format correct for the SP? It would be nice to know this before debugging DBUtils to try to find where the 2nd resultset went.
Peter
ALTER PROCEDURE "DBA_app"."spw_GetTrafficTwoResultSets"( IN in_StartAt integer, IN in_nmbrOfPosts integer) BEGIN SELECT TOP in_nmbrOfPosts START AT in_StartAt Traffic_id, description, name FROM Traffic ORDER BY Traffic_id DESC; SELECT COUNT(*) numberOfPostsInTable FROM Traffic; ENDThe SP looks OK to me.
With JDBC, you would usually use stmt.getMoreResults() to check for further result sets from one SP. I don't know for DBUtils.
Suggestion: Write and test a trivial multiple-result-set test procedure (like the following), then post ALL of your code including the calling code (because that is where the problem might lie)...
Test the procedure in ISQL to prove the procedure itself works OK...
CALL p(); first_result ------------ 1 second_result ------------- 2 Execution time: 0.033 seconds Procedure completedSimilar tests have been done in the environment with positive results, so Peter you should probably start digging in DBUtils, though you probably would have liked to find the errors on the database side :)