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

asked 13 Nov '14, 08:58

Peter's gravatar image

Peter
16112
accept rate: 0%

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.

(14 Nov '14, 04:11) Vlad

yes it has to be a SP

(14 Nov '14, 05:38) Peter
Replies hidden

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

(14 Nov '14, 05:59) Volker Barth

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

(14 Nov '14, 06:01) Volker Barth
Replies hidden

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

(14 Nov '14, 07:09) Peter
Replies hidden

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.
(14 Nov '14, 07:28) Volker Barth

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

(17 Nov '14, 08:19) Peter

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
(17 Nov '14, 08:29) Peter
Replies hidden

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.

(17 Nov '14, 09:06) Volker Barth

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
(17 Nov '14, 09:16) Breck Carter

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

(17 Nov '14, 09:30) M G
More comments hidden
showing 5 of 11 show all flat view

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

permanent link

answered 02 Dec '14, 21:01

J%20Diaz's gravatar image

J Diaz
830243044
accept rate: 14%

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.

permanent link

answered 21 Nov '14, 04:16

Frank's gravatar image

Frank
1204612
accept rate: 0%

edited 21 Nov '14, 04:23

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×102
×15
×5

question asked: 13 Nov '14, 08:58

question was seen: 2,206 times

last updated: 02 Dec '14, 21:01