Pagination
This mode allows retrieving data in smaller chunks over multiple HTTP requests. On the first request, instead of only fetching the first batch of rows, CData Virtuality fetches the whole result set and stores it in a cache. When the next batch is requested from the HTTP client, data is served from the cache instead of recalculating the result set of the actual query. To identify subsequent batches, the REST API returns an additional HTTP header requestid as well as pointers to the previous and next pages.
Please note that for the first request, the pagination mode can be activated only when providing the pagination=TRUE parameter. For subsequent requests, it is enough to provide only the requestId parameter with a particular cursor identifier.
Example 1
POST http://<cdatavirtuality-server-address>:8080/rest/api/query?pagination=true&limit=3&offset=3Body (application/json):
{ "sql": "SELECT * FROM SYS.Tables" }
STATUS 200
[
["datavirtuality","SYS","Keys","Table",null,true,false,"tid:2cb59cfd55db-0023a214-00000040",-1,null,true,false,4],
["datavirtuality","SYS","ProcedureParams","Table",null,true,false,"tid:2cb59cfd55db-ab347619-0000004c",-1,null,true,false,5],
["datavirtuality","SYS","Procedures","Table",null,true,false,"tid:2cb59cfd55db-8f29b420-0000005d",-1,null,true,false,6]
]Headers in response:
limit = 3
offset = 3
nextPage = http://localhost:8080/rest/api/query?requestId=767913&limit=3&offset=6
prevPage = http://localhost:8080/rest/api/query?requestId=767913&limit=3&offset=0
requestId = 767913
Example 2
POST http://<cdatavirtuality-server-address>:8080/rest/api/query?requestId=767913&limit=3&offset=6Body (application/json):
{ "sql": "SELECT * FROM SYS.Tables" }
STATUS 200
[
["datavirtuality","SYS","Properties","Table",null,true,false,"tid:2cb59cfd55db-3f9f6953-00000066",-1,null,true,false,7],
["datavirtuality","SYS","ReferenceKeyColumns","Table",null,true,false,"tid:2cb59cfd55db-aa92fa09-0000006c",-1,null,true,false,8],
["datavirtuality","SYS","Schemas","Table",null,true,false,"tid:2cb59cfd55db-d57e16d2-0000007b",-1,null,true,false,9]
]Headers in response:
limit = 3
offset = 6
nextPage = http://localhost:8080/rest/api/query?requestId=767913&limit=3&offset=9
prevPage = http://localhost:8080/rest/api/query?requestId=767913&limit=3&offset=3
requestId = 767913
Example 3
In case of an illegal query, status 400 with an explanatory message will be returned, as illustrated by this final example:
POST http://<cdatavirtuality-server-address>:8080/rest/api/query?pagination=trueBody (application/json):
{ "sql": "NOT SQL" }
STATUS 400
{
"title": "Something is wrong",
"description": "Error while parsing query: Encountered \"NOT\" at line 1, column 1.\rWas expecting one of:\r \"begin\" ...\r \"{\" ...\r \"create\" ...\r \"alter\" ...\r \"with\" ...\r \"select\" ...\r \"values\" ...\r \"table\" ...\r \"(\" ...",
"hint": null
}