Fetch more than 1000 records in one step

Connecting to SQL
1

Hi,
I have dreamfactory 1.5.9 running on my debian machine. I attached a mysql database and use several tables via rest api.
Now I discovered one problem. I have one table which has 1097 records. I want to fetch them in one step, the problem is, that I only get 1000 records. It seems that there is somewhere configured, that I can only fetch that amount. Is it possible to override that?
Thanks,
Rainer

2

Hi Rainer,

Yes, that value is easy to override. In the config/common.config.php file, just change the value of ‘dsp.db_max_records_returned’.

// change the value here
'dsp.db_max_records_returned'   => 1000
3

Hi Ben,
thanks for the Info. After searching a while, I already found this setting and it works perfect.
Thanks,
Rainer

4

Hi Ben,

Just now I’m facing same issue to get more then 1000 Records. But I’m using Dreamfactory 2.1.0,
How can i Fix it??

5

Did you try changing db_max_records_returned?

6

Hi Ben,

Happy to See your Reply,

In Dreamfactory 2.1.0 there is no config/common.config.php file. That’s why I got Confused. After that I got solution from your clue.
(i.e)
Override code like below in bootstrap/cache/config.php

array (
‘max_records_returned’ => 10000,

Thanks for Your Support,

7

Hi Ben,

Also I found a blocker issue that is, I was successfully created the SQL DB in Dreamfactory. After that If I add new table or add a new column in the existing table in phpmyadmin that not reflect in created service(api_doc)

for your reference:

DB name: Test->add new table(user)
Service name:mydemo

In api_doc->mydemo I was check the list of table user table not display here??

8

DreamFactory caches the database schema, so if you change it in PhpMyAdmin you need to clear the cache to see the changes in DreamFactory.

Go to the Config tab in the admin console and select Cache then click the ‘Flush System-Wide Cache’ button.

After that you should see the changes reflected in the API Docs.

1 Like
9

If you’re doing this programmatically, you can also refresh the schema of a service by calling service_name/_schema?refresh=true

1 Like
10

Hi Todd & Drew

Thanks For Quick Response, It’s Really Working Fine…

Thanks for your Support!

11

Hi Ben,

I’m using DreamFactory Version: 2.1.1 and hit this issue while trying to fetch more than 1000 rows from an Oracle DB. I included the count parameter in the API call and it listed the total number of records (currently a little above 41,000 Records) as well as the actually number of fetched records 1000.

My question is two fold:

  1. Is the max_records_returned parameter the ONLY parameter to change this limit of 1000? I did this and still found that this was still fetched ONLY 1000 records? I restarted the instance and even cleared the cache but this has no impact on increasing the number to what was set (which in this case was 3000 rows)

  2. Assuming this default of 1000 - can i go around this by pagination or fetching subsequent records after the first 1000? If so how do i go about this?

Regards

12

hi @chinedukoggu,

On question one, yes. max_records_returned governs the max number of records, so this should work. It’s possible that there’s an issue with Oracle. We’ll take a look. If there’s an issue we’ll ship a fix in the next sprint.

On question two, you can use the limit parameter together with the offset parameter to fetch 1000 records at a time. It’s a good policy to batch the number of records coming back to the client application. There’s a short tutorial here https://wiki.dreamfactory.com/DreamFactory/Tutorials/Paginating_with_limits_and_offsets.

1 Like
13

Thank you Ben.
Grateful for the feedback and assistance.

14

I would seem that your configuration is cached, so the changes you made to the .env file had no effect.
See if you have the is file: /path/to/htdoc/boostrap/cache/config.php
If yes, execute the following command from the command line, in the htdocs folder
php artisan config:clear

Your new maximum records setting should be in effect now.

And just to be clear, since this thread was resurrected from one pertaining to the previous DF version (1.x), the .env directive is called DF_DB_MAX_RECORDS_RETURNED

1 Like
15

Hi guys,
I’m using DF Version 2.2.1, and I was stuck with this issue for two days, and the solution which worked out for me was:

  1. Change DF_DB_MAX_RECORDS_RETURNED in /dreamfactory-2.2.1-2/apps/dreamfactory/htdocs/.env to a higher value.
  2. Also change to
    ’db’ => [
    // The default number of records to return at once for database queries
    ’max_records_returned’ => env(‘DF_DB_MAX_RECORDS_RETURNED’, 100000),
    …
    ]
    in /dreamfactory-2.2.1-2/apps/dreamfactory/htdocs/config/df.php file.
    Hope this helps…
    Regards
1 Like
16

Glad to hear you figured it out @Veeru_Udoshi. Thanks for sharing what worked for you.

Thanks,
@AlexBowen

17

Just for the sake of those reading later we do not recommend editing config/df.php

All you should need to do is edit the .env file and then clear the config and application caches.

4 Likes
18

It is possible to fetch more that 1000 records at a time. The default setting (1,000) is in place so users do not accidentally call an exceedingly large number of records with a single call, and potentially crash a browser or other client.

You can change this setting on a case-by-case basis by navigating to the API configuration within your DreamFactory administration console, and changing the Maximum Records setting accordingly. To override the default globally open the .env file and modify the DB_MAX_RECORDS_RETURNED setting (be sure to additionally uncomment the line by removing the preceding hash mark (#). The .env file resides in your DreamFactory application’s root directory. To identify the root directory, click on your Config tab and consult the Install Directory path.

After saving the file be sure to clear your cache to ensure the changes are picked up. You can do so by executing the following command from within your DreamFactory root directory:
$ php artisan cache:clear && php artisan config:clear