How to delete more than 200 records in a db table

Questions
1

I have a table that contains more than 200 records matching a field (e.g. a year). Using postgresql pgAdmin query tool for count returns 555.

If I load a foundset by query (created using query builder) I will of course only get the first 200 records.

If I then do a foundset.deleteAllRecords this works on that 200 set correctly.

If I then attempt another load foundset by query the result is zero records.

Do I have to do something else to trigger a reload of the next 200 etc?

What is the best practice to delete records matching a field?

2

deleteAllRecords should do the job.

If you want to know exactly what is going on, log on to the servoy-admin page and have a look at the database performance page.

This will show you the exact queries that are executed on the PostgreSQL database.
Table filters or foundset filters could change the result (read: will add conditions to the where-clause) but this will be visible on this page.

Hope this helps to find out what is going on.

3

Thanks. I had assumed that since the foundset was based on a query that deleteAllRecords would only delete those 200 loaded in the foundset. The fact that a second query shows zero records must mean that that the function ignores the query that built the foundset. Indeed after testing a few times it is true that all the records (all 555) are deleted that match the query despite only loading the first 200.

4

Hi,

from performance point of view, the foundset will load data in chunks of 200 records.
If you iterate a foundset like the example below, Servoy continues to loop all records belonging to the query result set.
In this case the foundset will grow beyond the initial 200 record size.

Important: you should never store the foundset.getSize() in a variable prior to the iteration code and use that as your ‘max’ index. This will prevent Servoy from looping in chunks.

As explained deleteAllRecords() will build the delete-query according to your ‘select-query’ conditions.
Servoy admin database performance page is your best friend in order to see what is going on under the hood.

for(var i=1; i <= foundset.getSize(); i++) {
   // code 
}