One of our customers are sending out email newsletters. We have recently discovered that not everyone is getting the newsletters. We have spent several days, checking logs, ruleing out spam filters, email server limits etc.
We create a dataset of around 1250 records using getDataSetByQuery.
The dataset contains the same number of records we find quering the database using a third-party tool like Aqua Data Studio, or PGAdmin3.
Then we load the records using controller.loadRecords(dataset);
This loads the 200 first records, and as we loop down the list, the next 200 records load as normal when we pass 200,400,600 records.
However, at 1,000 records we don’t get any more records!!!
This is easy to verify using a simple Application.output() on the loop counter.
There is no error message.
Is this a bug or a feature?
We have a lot of code to rewrite if this is not a bug.
This is a limitation, not a bug as you can read in the post you mentioned. Use controller.loadRecords(query) instead and you don’t suffer from this limitation (but maybe from others, depending on your query).
OK we have added a field called ‘load_safe’ to our databases of queries and will route load_safe queries through to controller.loadData(). This will allow us to leave the majority of the queries as they are.
It seems that
controller.loadData(sql,params)
is much faster than
controller.loadData(dataset)
is that obeservation correct? or is just that Servoy has cached all the records by now…
Yes, it is a lot faster. Creating the dataset needs one query, getting the records using loadRecords() fires a second, very slow query (the IN statement). Using loadRecords(query) requires only one query. But there is a downside: you cannot use any query. For example you cannot use outer joins.
BTW: everybody seems to be very surprised about this matter. I just had a look at the reference guide and found this:
loadRecords(dataset)
Loads the specified form with the primary key’s column data as the foundset. Requires a dataset with only the primary key’s column data from the table the form is based on.
This function will work best when the dataset that is returned is approximately 200 rows (records). This function should not be used if the returned dataset is too large (as in thousands of rows).
patrick:
Yes, it is a lot faster. Creating the dataset needs one query, getting the records using loadRecords() fires a second, very slow query (the IN statement). Using loadRecords(query) requires only one query.
Didn’t know that
patrick:
But there is a downside: you cannot use any query. For example you cannot use outer joins.
That is the main reason why I use
var dataset = databaseManager.getDataSetByQuery(...);
forms.tabel_list_land.controller.loadRecords(dataset);
But ran into problems now becoz it didn’t show more than 1000 rows.
Any chance of Servoy beeing able to use outer joins in controller.loadRecords(sqlstring,parameters)
Or else this will be major rebuild of our solution
Workaround: if you’re printing statements or sending emails or something like that, and it is a solution only supporting one database, you could do you own paging by adding a OFFSET LIMIT clause at the end of your SQL, and run the search several times returning 1,000 or less records each time.
I’m saying one database, since different DBs have different syntax for this.