When using a sql local database (the db api) I can use limit and offset with success, but when using my own service to a remote SQL Server database offset property doesn’t work. Limit works, but whatever offset I use I always get the first records .
Not sure why that’s happening. I’ve logged an issue on GitHub https://github.com/dreamfactorysoftware/dsp-core/issues/29. The engineering team will look into it and I’ll post an answer once I hear back.
Our service for SQL Server uses the older method for paging where SQL Server requires the ORDER BY clause, (not the OFFSET and FETCH method used in the 2012/2014 versions as documented [here] (http://msdn.microsoft.com/en-us/library/ms188385.aspx).
So, if you wanted to get the second set/page of records, limiting 5 at a time, your request would look like this… (Note, I chose the “id” field to order by but any sortable field would do.)
/rest/<sqlsrv_service>/<table_name>?limit=5&offset=2&order=id%20ASC
Or if you are more familiar with the Microsoft aliases like top and skip…
/rest/<sqlsrv_service>/<table_name>?top=5&skip=2&order=id%20ASC
We look to support the OFFSET/FETCH method in the future, but for now, most of our clients were running 2008R2 or earlier.
Anyone with an idea on how to bring down all the records? when I use a limit that is more then 1000, it still limits it to 1000.
Hello @glowingembersons , I’m not sure if there’s another way to change that, but you can change the default value for the maximum number of records per request.
In version 1.9.x look for the following line in the file: config/common.config.php
/ ** The default number of records to return at once for database queries * /
‘dsp.db_max_records returned’ => 1000,
In version 2.0.x procute the line below in the file: config/df.php
// The default number of records to return at once for database queries
’records returned max’ => env (‘DF_DB_MAX_RECORDS_RETURNED’, 1000)
Another alternative would be to create a stored procedure and there treat the limit of records, as well as other applicable filters. The downside here is that you can not count on them so comforting parameters and resources of the database API. On the other hand, precisely because there is a minimal treatment by the JSON generated in the execution, the performance is quite high.
I’ve used procedures to return JSON’s with 5000+ records, and in my tests, it began to complicate when exceeded the 12000+ records, but does not recommend doing so great requests.
Best regards.