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?


Solution

Hello,

If you replace commit  for Exec('com'+'mit') still working :-)

All the best

Solution

Hi freek,

Since outsystems does not allow to execute delete statement anymore. Simple solutions would as follow that I used.

1. Create a test screen with a button and call your SQL using advance SQL.

2. Create a timer and pass your query as a parameter to the advance SQL and make the parameter configurable from the service center . You can run your timer from the service center as well manually . Make sure to make the input parameter as Expand Inline as true.

I hope this can be of use to you.

Thanks.

pranav pandey wrote:

Hi freek,

Since outsystems does not allow to execute delete statement anymore. Simple solutions would as follow that I used.

1. Create a test screen with a button and call your SQL using advance SQL.

2. Create a timer and pass your query as a parameter to the advance SQL and make the parameter configurable from the service center . You can run your timer from the service center as well manually . Make sure to make the input parameter as Expand Inline as true.

I hope this can be of use to you.

Thanks.

That doesn't make sense to me. OutSystems DOES allow delete statements. It doesn't allow commit executed in a Test execution of advanced SQL.


Ricardo Cruz wrote:

Hello,

If you replace commit  for Exec('com'+'mit') still working :-)

All the best

Nice your the best!


freek wrote:

Ricardo Cruz wrote:

Hello,

If you replace commit  for Exec('com'+'mit') still working :-)

All the best

Nice your the best!


Well this trick is known by many, but posting this in the forum will most likely result that OutSystems will take measures in future update to not allow this anymore.



@daniel What are you trying to say?

freek wrote:

@daniel What are you trying to say?

The trick described by Ricardo overcomes the recent change that one cannot add the commit statement in after an SQL statement. No that it everybody can read and try this it is no longer under the radar. So I expect OutSystems to also block this trick in a future update.

Daniël Kuhlmann wrote:

pranav pandey wrote:

Hi freek,

Since outsystems does not allow to execute delete statement anymore. Simple solutions would as follow that I used.

1. Create a test screen with a button and call your SQL using advance SQL.

2. Create a timer and pass your query as a parameter to the advance SQL and make the parameter configurable from the service center . You can run your timer from the service center as well manually . Make sure to make the input parameter as Expand Inline as true.

I hope this can be of use to you.

Thanks.

That doesn't make sense to me. OutSystems DOES allow delete statements. It doesn't allow commit executed in a Test execution of advanced SQL.


Hi Daniël Kuhlmann,

Sorry I meant the commit statement no the delete statement. Since we are not allowed to execute commit statements in test execution , so I  have suggested some methods that I use to clear up my data during application development.


Hi guys!

Can anyone offer some insight into why the commit statement is being blocked by Outsystems? Is this discussed somewhere?

Understanding the reasoning from Outsystems for doing this might make it less of an annoyance...