dataset with max 1000 records??

Classic Servoy
21

Hello Robert,

sorry, but now I think you confuse things. In think what happens is this:

  1. You can use ANY query to create a JSDataset of ANY size using getDataSetByQuery. There are not limits whatsoever regarding the statement. Any valid SQL statement will be fine
  2. Now you want to use controller.loadRecords(JSDataSet). When you do that, Servoy creates an IN statement to query the primary keys that you have in your dataset

Example:

var query = 'SELECT pk FROM table WHERE pk < 500'
var Dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query , null, -1);

Suppose the dataset contains this: 10, 20, 30, 35, 37, 42

Now when you use controller.loadRecords(dataset), Servoy will fire this statement to the database

SELECT ... FROM table WHERE pk IN (10, 20, 30, 35, 37, 42)

So Servoy DOES NOT use your original query. So your statement is wrong on that part: it doesn’t make any difference if you original statement was an in statement or not, Servoy will always create an IN statement anyway. That’s why there is this limit!!

22

I have done this with over 20,000 records.

This is the proof that Servoy 2.x warned you to load too many records this way, but did not enforce a limit. Obviously in Servoy 3, the limit is hard coded to be 1000.

23

Maybe Jan Blok can enlighten us with how Servoy really works under the hood.
He should know for sure…he has the source ;)

24

Before I leaned myself out of the window, I turned on the stacktrace to see if Servoy really does what I thought and it does.:wink: It creates an IN-statement from whatever dataset.

If not, why should anyone mess to comply to Servoy’s restrictions when using controller.loadRecords(query) if you can use any query with a JSDataSet and then controller.loadRecords(dataset)? The difference is, that when using the query approach, your query will not be changed and used directly. If you use a dataset, the dataset will be taken apart to figure out primary keys and then the IN-statement is constructed and fired. This also means, that the dataset approach needs two queries, while controller.loadRecords(query) only fires one query.

25

patrick:
Before I leaned myself out of the window, I turned on the stacktrace to see if Servoy really does what I thought and it does.:wink: It creates an IN-statement from whatever dataset.

:shock: You’ve got me on that one. ;)

patrick:
If not, why should anyone mess to comply to Servoy’s restrictions when using controller.loadRecords(query) if you can use any query with a JSDataSet and then controller.loadRecords(dataset)? The difference is, that when using the query approach, your query will not be changed and used directly. If you use a dataset, the dataset will be taken apart to figure out primary keys and then the IN-statement is constructed and fired. This also means, that the dataset approach needs two queries, while controller.loadRecords(query) only fires one query.

Well I did some fact checking now as well…a dataset doesn’t know the SQL it used nor the parameters. So my remark on that was false.
The controller does however know it’s SQL and parameters (via the databaseManager). I guess I mixed those two up.
Makes sense however since you can add/remove rows from a dataset or create an empty one.