stored procedure return result set and total row count

[Former Member]

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

[Vlad]

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.

[Former Member]

yes it has to be a SP

[VolkerBarth]

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).

[VolkerBarth]

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)?

[Former Member]

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

[VolkerBarth]

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:

• How to return a result set from a procedure
• In the same fashion, you can generate a "scalar" result set by simply using a "SELECT COUNT(*) from <whatever>"
• In case a simple return value is sufficient and you do not want to return a result set but a return value, you would usually use a user-defined function instead of a procedure in SQL Anywhere.

[Former Member]

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

[Former Member]

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;      
END

[VolkerBarth]

The 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.

[Breck_Carter]

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)...

CREATE PROCEDURE p()
BEGIN
   SELECT 1 AS first_result;
   SELECT 2 AS second_result;
END;

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 completed

[Former Member]

Similar 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 🙂

[JimDiaz]

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

[fvestjens]

It would be nice if we could have a similar setup for json as we have for XML:

select
   (select count() from address) as NumberOfRecords,
   (select Id, Code from address order by Id for xml auto,elements) as Address
for xml auto, elements


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.