Delete all records

Classic Servoy
1

What’s the quickest way to delete a table’s entire contents?

I have a simple loop:

controller.loadAllRecords()
var maxi = controller.getMaxRecordIndex()
for (i=1; i <= maxi; i++)
{
controller.deleteRecord()
controller.saveData()
}

This loop doesn’t seem to work every time though.. not sure why!

I tried deleteAllRecords(), but that seems to only affect the foundset, not the actual records in the DB. Maybe I am using it wrong?

2

THIS SHOULD DO THE TRICK:

controller.loadAllRecords() 
controller.deleteAllRecords()

or this: (much slower! not preferable!)

controller.loadAllRecords() 
for (i=1; i <= controller.getMaxRecordIndex(); i++) 
{ 
controller.deleteRecord() 
}
controller.saveData()

you must set the controller.getMaxRecordIndex() inside the loop and not outside. Than it goes behond the limit of every 200 records![/code]

3

I saw some wierd behavior when putting get max records inside the loop, Every iteration that number changes and you end up only deleting half the records, when i meets the max record index.

I tried the first suggestion It works now… wierd

I added a controller.saveData() to be safe.

4

I maybe misunderstanding things, but personally ,when working with sybase at the backend, it seems to me that an executesql command that says “truncate table xxxx” would delete all records of the table, without the overhead of every delete added to the log (as stated in the sybase docs) and eliminating the need for a potentially heavy loop

HTH

5

You can’t execute such a command directly from within Servoy.

I imagine that deleteAllRecords() does something to that effect for you.

6

This procedure (using Sybase central to build) in your database (obviously changing the table name to the correct one)

ALTER PROCEDURE “DBA”.“deleteAllClients”( /* [IN | OUT | INOUT] parameter_name parameter_type [DEFAULT default_value], … / )
/ RESULT( column_name column_type, … ) */
BEGIN
truncate table dummy
END

can be called and functions from servoy with this very basic code

var array1 = new Array();
var array2 = new Array();

databaseManager.executeStoredProcedure(‘ready2learn’, “call deleteAllClients()”,array1,array2,0);

I have to say that on my dummy table it took surprisingly long (for 3 records , a few seconds) on the other hand I believe that for truncate it does really matter if its 3 or 500000 records; anyway this is a way for executing sql on your db backend. The executesql statement came from another language :roll:

Of course both the procedure and the method can be enhanced i.e. passing the table name as a variable,…

HTH

7

I may be mistaken about the functionality of databaseManager.executeStoredProcedure(), but in my understanding:

You should be careful using stored procedures to modify records. Servoy has gone to great lengths to ensure data consistency across simultaneous connected clients, and changing anything on the database level without servoy knowing about it can result in errors corrupted datasets and other undesired behavior.

8

this code:

controller.loadAllRecords() 
controller.deleteAllRecords()

should work fine. Doesn’t it delete all records in youre case??

9

I must have been doing something wierd at first, it works fine now.