Hi,

I am a newbie to outsystems and am looking to find a way to truncate tables for an application I have created.

Reading the related "Truncate table" posts there is reference to changing OSRUNTIME permissions to allow elevated priviledges which would solve my problem of not being able to truncate tables from an advancedSQL widget.

My question is how do I go about doing this as I dont have a direct connection to the SQL db.

Any help is greatly appreciated.

Hi Vince


If I am getting you correctly, you need a way to clear all data from a table of your application. You can do that with an advanced SQL Query, even with tables created on Outsystems.


Just add the SQL query and do an

  DELETE FROM {Table_Name}


You can add the where clause if needed for other use cases as well, though if I understand correctly the TRUNCATE is just clearing up a table.

That should take care of the problem. BTW ctrl+space works on the SQL editor and shows you the tables and fields available for each table.


Hope this helps!

   CLSJ


Thanks Carlos,


I am wanting to do a truncate as this will reset the table identifiers too, a delete will only remove the data from the tables and the autonumber columns will continue to increment from where they left off.

Ah interesting. Afraid I can't be of any help in that case, as I have never had to reset table identifiers. If you don't mind me asking, for what use case do you need a table reset at that level?


Regards,

   CLSJ 

In my case it is for verifying initial data load scripts that I have written, I want to ensure that they are rerunnable and will consistently return the same data. This is for data migration purposes.

Solution

Hi Vince,

To use TRUNCATE you have to extend the rights of your user on the database with enough right to execute TRUNCATE / DROP.

If you are on premise, that  should not be difficult. If you have a cloud infrastructure you probably need to request to get access with a database management tool to your OutSystems database via a VPN connection.

Note that this has a security risk that data might get lost when you grant TRUNCATE rights on all tables, because TRUNCATE doesn't keep track of changes in the transaction log.

Regards,

Daniel

Solution

Thanks Daniel, I am on the cloud infrastructure so will investigate how to get a VPN connection setup.