Is there a way to truncate a table from ServiceStudio

I am in very early stages of my application development. I added a few entries in a table and later added an index to the table which made the previously added entries invalid/incompatible. Is there a way to delete the data in the Entity from Service Studio so that I can publish the new table?

Manu Chadha wrote:

I am in very early stages of my application development. I added a few entries in a table and later added an index to the table which made the previously added entries invalid/incompatible. Is there a way to delete the data in the Entity from Service Studio so that I can publish the new table?

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.


Swatantra Kumar wrote:

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

This is NOT in PAAS infrastructures.


Daniël Kuhlmann wrote:

Swatantra Kumar wrote:

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

This is NOT in PAAS infrastructures.


Yes it is, we are PaaS and do it all the time. Just can't use truncate if there are conflicting referential links


Edit, the standard user has permission to do truncates in PaaS environment, or in our at least anyway


Jeanene Williams wrote:

Daniël Kuhlmann wrote:

Swatantra Kumar wrote:

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

This is NOT in PAAS infrastructures.


Yes it is, we are PaaS and do it all the time. Just can't use truncate if there are conflicting referential links


Edit, the standard user has permission to do truncates in PaaS environment, or in our at least anyway


That is new information for me, sofar in my experience, this has never been possible.



Daniël Kuhlmann wrote:

Jeanene Williams wrote:

Daniël Kuhlmann wrote:

Swatantra Kumar wrote:

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

This is NOT in PAAS infrastructures.


Yes it is, we are PaaS and do it all the time. Just can't use truncate if there are conflicting referential links


Edit, the standard user has permission to do truncates in PaaS environment, or in our at least anyway


That is new information for me, sofar in my experience, this has never been possible.



We have been doing it since early Outsystems 10 version. Although I must admit that most of the time we use a simple delete because most of our entities are multi tenant


Daniël Kuhlmann wrote:

Jeanene Williams wrote:

Daniël Kuhlmann wrote:

Swatantra Kumar wrote:

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

This is NOT in PAAS infrastructures.


Yes it is, we are PaaS and do it all the time. Just can't use truncate if there are conflicting referential links


Edit, the standard user has permission to do truncates in PaaS environment, or in our at least anyway


That is new information for me, sofar in my experience, this has never been possible.



Ok sorry I stand corrected. It looks like there has been a change in roles. I went back to some old code where we had the truncates and they are actually failing now. We hadn't noticed this as most of our stuff we do these days is multi tenant and for those you can't do a truncate for as it tries to apply the multi tenant filter. I'll need to get our old code updated it actually came from our first Outsystems 9 install, thanks for the heads-up.


Jeanene Williams wrote:

Daniël Kuhlmann wrote:

We have been doing it since early Outsystems 10 version. Although I must admit that most of the time we use a simple delete because most of our entities are multi tenant


I check with some fellow MVP and we are pretty sure this is not possible by the standard PAAS offering from OutSystems. Could you check if in the past you asked for extra permissions on the database to be able to do the TRUNCATE?

Regards,

Daniel


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.

Swatantra Kumar wrote:

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.

Did you request extra permission from OutSystems to enable this?


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.

Jeanene Williams wrote:

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.


TRUNCATE TABLE was possible in the past by default. But the permission on the database that allows you to do has been retracted. So that is why in old PAAS infrastructures it seems to be possible to do this. But not for any entity created after OutSystems removed the database permission to do so.


Daniël Kuhlmann wrote:

Swatantra Kumar wrote:

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.

Did you request extra permission from OutSystems to enable this?


No, I didn't. We had one app working on-premise which was migrated on the cloud later. Didn't check specifically for the truncate statement, but had no error in the log complaining about it. 

There could be the possibility that after migration, no one triggered the Truncate, which was used during development on on-prem infra. I'll check it if it stopped working after migration.


Daniël Kuhlmann wrote:

Jeanene Williams wrote:

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.


TRUNCATE TABLE was possible in the past by default. But the permission on the database that allows you to do has been retracted. So that is why in old PAAS infrastructures it seems to be possible to do this. But not for any entity created after OutSystems removed the database permission to do so.


That explains it then. Great to know


Solution

Manu Chadha wrote:

I am in very early stages of my application development. I added a few entries in a table and later added an index to the table which made the previously added entries invalid/incompatible. Is there a way to delete the data in the Entity from Service Studio so that I can publish the new table?

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


Solution