3.0: after an SQL query cannot load more than 1000 records

Classic Servoy
1

I am not sure if this is a bug or else, but I noticed this behaviour in 3.0 that is different from 2.0.

In Servoy 3.0, after an Sql query I get a dataset containing 8101 rows. When I load the dataset using controller.loadRecords the resulting foundset is limited to 1000 record, no matter how many rows I have retrieved using databaseManager.getDataSetByQuery. In Servoy 2.0 there was no limit and all records in the resulting set where loaded so that I could do something on each one (in my specific case I wanted to retrieve data from a remote database to update the records).

var sqlQue = "SELECT tb_test.tb_testid FROM tb_test WHERE tb_test.fd_check = '1'";
var sqlSet = databaseManager.getDataSetByQuery(controller.getServerName(),sqlQue,null,10000);

var resSet = sqlSet.getMaxRowIndex()
controller.loadRecords(sqlSet);
var fndSet = databaseManager.getFoundSetCount(foundset);

in Servoy 2.0 resSet and fndSet gave the same result, 8101. In Servoy 3.0 resSet = 8101 and fndSet = 1000.

If I have more than 1000 rows in the dataset and I need to loop through all of them, how can I do that? foundset.getSize or controller.getMaxRecordIndex don’t seem to be of any help in this case.

MacOS 10.4.8
Servoy Developer
Version R2 2.2.5-build 337
Java version 1.5.0_06-68 (Mac OS X)

Version 3.0-build 371
Java version 1.5.0_06-68 (Mac OS X)

MySql 5.0.24a

2

Hello,

What happens if you substitute the SQL query itself, and forget about the getDataSetByQuery line? That is, try:

controller.loadRecords(sqlQue);

instead of:

controller.loadRecords(sqlSet);

According to the developer reference guide, you need to include an ORDER BY clause. Look up “loadRecords(sqlstring)” in the documentation.

3

Hi, chartpacs

thank you for your reply and…it works. I don’t know why, but it works.

4

Glad to hear everything is working now. Maybe someone else can elaborate on why loadRecords(sqlstring) seems to work better than loadRecords(dataset).

5

Interesting problem.

When you use loadRecords(yourSQL) then Servoy will handle the fetching of the rows. In fact it will only fetch the first 200 records like it always does. And so on.
When you get a dataset of PK’s and load that with loadRecords(yourDataset) then it normally would load the WHOLE set of data into the controller.
Apparently this has changed and is maximized to 1000 records in version 3.0.

6

Hi,

I am now with he himself problem. If now only it gives back 1000 records of 11754. DatabaseManager.getDataSetByQuery that utility has?, if you do not know first whatever it will give back.

Carles Barbal