Manu Chadha wrote:

Yes, you can create a server action so you can trigger it somewhere to Delete all Data.

Example:

Drag a SQL Widget and write a code something like this

Truncate table {TableName};

Hello Manu.

You can delete data from actions, but not from Service Studio. You must publish and call it from a Timer or a Button on Screen. This is to prevent accidents.

You can do a DELETE or a TRUNCATE. But if data is that different, maybe you could delete the entity and create a new one?

Hi Manu,

You may like to write an Advanced SQL query using Service Studio to truncate the table using command  

TRUNCATE TABLE {TABLENAME};

Keep in mind you need to grant the sufficient rights to the user on the database to be able to execute TRUNCATE statements. 

Regards,

Swatantra

What we do with all our applications is to always create a special support espace (application_SUP) in which we create a page for every entity we have using simple scaffolding and a couple of basic actions like search and delete all or truncate depending on if it is a multi tenant app or if there are referential links to other tables. This makes life so much easier for testing, maintenance and changing things when you develop and being in a separate espace we can simply delete it completely once going into production or not publish it through to the prod environment to prevent accidental deletion of prod data. 

One thing to note on the above answers is that you can't always do a truncate table command. In SQL a truncate cannot be used with some referential integrity link types such as Delete, if you get errors you can try a simple Delete from {TABLENAME} with no where filter.

Yes, Daniel Truncate works in the PAAS environment. And indeed as Jeanene mentioned if there are FK constraint on the table with data referencing to it, then Truncate won't work.

EDIT: Didn't check specifically for Truncate on the PaaS.

It looks like their may have been some role permission changes between some of the platform version upgrades.

I do not find any release notes about it, if I try in my PE I am not able to do a TRUNCATE TABLE due to permissions. I am still not convinced this is default possible in OutSystems PAAS environments and will see if I can get an answer from OutSystems.

It isn't possible NOW in new tables created in our O11 platform, but it is in older code that fully passed testing in all 4 of our environments back in 2016/17 so seems to have changed somewhere along the lines. Also tested in my PE and no it isn't possible in there, never tested that back on O9 or O10 though.

BTW it IS possible if you have a VPN and direct SQL credentials but that's a completely separate support issue.

But the points fairly mute, it's best to assume that truncate isn't possible to use on any new PaaS environment.

Manu Chadha wrote:

Hi Manu,

The answer to your question is spread over multiple replies, I try to summarize it here:

• If you have OutSystems on-premise and the proper permission (i believe ALTER is the minimum permission that you can get as table owner) then you can use TRUNCATE TABLE in your advanced SQL One thing to note on the above answers is that you can't always do a truncate table command. In SQL a truncate cannot be used with some referential integrity link types such as Delete, if you get errors you can try a simple Delete from {TABLENAME} with no WHERE filter.
• If you have an OutSystems PaaS environment then you cannot use the TRUNCATE table. Maybe this was possible in the past, for sure it is not possible for any new entity you would create now, and it has been like that already for some time. To remove all records from a table you can do:
  1. Delete from {TABLENAME} with no WHERE filter.
  2. Remove then entity from your module, and past it back. This effectively creates a new table in the database. This is also limited based on if there are foreign key referenced to the records in the table with Procect delete rule. Also important to understand, the old table and its data is still existing, this may cause unwanted side effects if the table has foreign key references to other tables that are still represented by OutSystems entities. You can use DBCleaner or DBCleaner on Steriods to effectively remove a deleted entity table from the database.

Regards,

Daniel