sql to be eecuted contains a COMMIT

Hello!

I was trying to do a advanced query with a commit in the personal cloud environment but the query does not work anymore. Is there a work around, I want to empty a table?

Hi,

You can use a CommitTransaction node after the SQL node.

Hi Freek,

If your idea/goal is to delete data without publishing the functionality itself., you just need to create an SQL node and execute "delete from table". That can be accomplished this way:


Note 1: Although you get an error, this will delete the date anyway.
Note 2: Make sure you don't have dependencies (data from this table being referenced in other tables), otherwise it won't work.

Can you share more details about the error you are getting?

Vera

freek wrote:

Hello!

I was trying to do a advanced query with a commit in the personal cloud environment but the query does not work anymore. Is there a work around, I want to empty a table?

Hi,


You can create some logic and run it in a Webscreen for developers only, for example.


Kind regards,

Hugo


Vera Tiago wrote:

Hi Freek,

If your idea/goal is to delete data without publishing the functionality itself., you just need to create an SQL node and execute "delete from table". That can be accomplished this way:


Note 1: Although you get an error, this will delete the date anyway.
Note 2: Make sure you don't have dependencies (data from this table being referenced in other tables), otherwise it won't work.

Can you share more details about the error you are getting?

Vera

Vera,


Using the commit at the end of the query used to work but this workaround was removed and it is not allowed anymore. 

In order to perform that you need to create some logic (screen, action, etc).


Kind regards,

Hugo



A little note to complement what Hugo said:

After the version 11.6  and some versions of 10 have the same behavior (ex: 10.0.1013.0)... you can't execute a COMMIT.

To be able to do Update or Delete, you need to build a page for this purpose, with a button to perform this action.

In versions other than those mentioned above, you just need to commit, and in the output parameters, add any variable, structure or entity, no matter what.

Cheers,

Nuno Verdasca

Hi guys,

In fact, the latest version of Studio Beta already blocks the Commit inside Advanced Queries. The alternatives are:

1. Create a timer with your query and start this timer in SC. (It will run and Commit just after the timer runs)

2. Use an external component to do that. I personally like this one: https://www.outsystems.com/forge/component-overview/5900/sql-sandbox, but be careful, this component could be dangerous and should be used wisely. You will be able to run any query on the database side using it.


Best regards

Fabio


HI @freek

They removed that functionality, because in order avoid changes directly into the database, but we can achieve the same thing in action for example you create action, inside the action use standard SQL some thing like this 

In the SQL you can add your query with commit like this 


for testing purpose you can run this action on-click button 

It will update your database,


Its applies same thing for the Delete, please try in this way.

I hope this helps for you

Thank you


Regards,

Srinivas

Hi guys a lot of reactions on my question that's nice!!

Of course we could build a screen or a timer but it is a bit silly.

They removed it on purpose so I have to have peace with it.

OS = GREAT

:)

It could have easily been made an environment setting, so the customer can decide themselves to allow it in dev and test and not in acc and prd for example. It is annoying having to publish to change/remove testdata instead of directly in Service studio.   With any alternative for this removed functionality (either timer or screen or ...) the risk of changing directly in the database stays the same...

Alternative:

Create screen with inputfield + button. Button calls action with advanced query passing input value as inline sql.  Question is: why would this be any safer?