Forum: Building VoltDB Applications

Post: dificult implementation of web table paging with LIMIT clause

dificult implementation of web table paging with LIMIT clause
aris_sety
Jan 19, 2011
according to the documentation, the SELECT possible syntax of voltDB are:
SELECT { * | [ ALL | DISTINCT ] { column-name | selection-expression } [AS alias] [,...] }
FROM { table-name | view-name } [AS alias] [,...]
[WHERE boolean-expression [AND boolean-expression...]]
[clause...]
clause:
ORDER BY { column-name | alias } [ ASC | DESC ] [,...]
GROUP BY { column-name | alias } [,...]
LIMIT { integer-value | ALL }
It is difficult for me for implementing web table paging with voltDB LIMIT clause. How can I get the page 3 (eg: 20th row to 29th row) for my web table? I can implement table paging with with "LIMIT offset, count" clause, in mySQL (I'm not say that voltDB worse than mySQL. VolDB is great!!).
If I use voltDB I must fetch all 30 record to get record 20th to 29th row. Or I misinterpret the documentation?
re: dificult implementation of web table paging with LIMIT claus
tcallaghan
Jan 20, 2011
VoltDB does not currently support the offset component of a LIMIT clause, just the count.
There are two ways you can accomplish your need right now:
(1) Do LIMIT 30 in your SQL and return all 30 rows, skipping the first 20 in your client application. or
(2) Do LIMIT 30 in your SQL, skip the first 20 in your stored procedure, and build a new VoltTable with rows 21-30, returning just those rows from your stored procedure.
We already have this request as http://issues.voltdb.com/browse/ENG-704 in our Jira ticket system. Feel free to add a vote to this item as votes help us prioritize the effort toward additional SQL support.
-Tim
re: dificult implementation of web table paging with LIMIT claus
aris_sety
Jan 20, 2011
VoltDB does not currently support the offset component of a LIMIT clause, just the count.
There are two ways you can accomplish your need right now:
(1) Do LIMIT 30 in your SQL and return all 30 rows, skipping the first 20 in your client application. or
(2) Do LIMIT 30 in your SQL, skip the first 20 in your stored procedure, and build a new VoltTable with rows 21-30, returning just those rows from your stored procedure.
We already have this request as http://issues.voltdb.com/browse/ENG-704 in our Jira ticket system. Feel free to add a vote to this item as votes help us prioritize the effort toward additional SQL support.
-Tim


thank's, the second way looks cleaner to my application.
but, I still worry if my user click "last page button".