Allow additional SQL syntax in Advanced SQL Widget

Aggregates & Queries
On our radar
This is stricttly a database permission issue for those that do not have direct access to the database environment, primarily people using the Personal Edition in the cloud.   There are several statements that would be useful if they could be performed in an Advanced SQL widget.  I will document two of them.

Most of my work involves converting from legacy mainframe systems to a new platform.  I'm trying, where possible, to make Outsystems that new platform.  I've been very succesful on my most recent work automating the data conversion process. 

One issue I will run into soon is that some of the links between IDs are created external to the Outsystems platform.  I would like to preserve those values but I cannot as I do not have the permission needed to use SET IDENTITY_INSERT {tablename} ON.

The second issue is mostly for testing.  I currently use DELETE FROM {tablename} to delete all records from a table when I want to test the conversion again.  The issue is that now all my ID values are higher than expected since it starts from where it left off, not at 1, as it did the first time.  Since ID values are all just links this is mostly a cosmectic issue but as I do this many times with millions of records this could become a bigger issue.  Using a simple TRUNCATE TABLE {tablename} would solve this problem but the current database permissions do not allow that statement to be executed.

What's interesting about this second issue is that if I have the ability to reload all the data in the database, which I do because of my conversion code, I can actually delete the eSpace with the tables and republish it.  This deletes all the records and resets all the identity values.  It's too drastic for most people but works for me right now.

So its not an issue of whether someone can do these things, it's changing the permissions so we don't have to go through hoops to do it.

I'm sure there are other statements that should be added to the list.  I would love to see these two added right away.


Created on 25 Jan 2016
Comments (0)