How to execute SQL UPDATE statement

Hi,

I'm data analyst, not developer. I'm not interested in developing the application. What I need to do is accessing the data by SQL directly like SQL Server Management Studio in MS SQLServer.

I'm testing SQL to get or update data. SELECT statement is working well.

Set Parameter and click "TEST". Then result is like below.


But when I run UPDATE statement, looks nothing happen. (I checked the data but not updated.)


I have very limited knowledge about Outsystems so probably I need to do something else.  Can you someone teach me how to execute UPDATE statement?

This is not permanent application or function. I need to run SQLs which is always different statement as needed. 

Thanks!!

Hiro

Hi Hiroshi,


You just need to add ;commit; to the end of you Update statement;

For example:

Where {Well}.[OutletID]=@OutletID; commit;


Then if you check the data it should have changed.

Be aware of the commit, you only should use this if you want to change DATA when not in runtime, you dont need "commit" to update records in runtime.


Best Regards


Hello.
The best way to execute an update / delete / insert statement on an advanced query is with a Boolean structure as a return.
Below example.

Best Regards

Hi Hirosh,

Did you take a look at the Service Center to see if there is some error message?

When you say "run", you are publishing and running your application?


to access Service Center:

your_enviroment/servicecenter -> Monitoring

Hello Hiroshi, 

2. The Test functionality executes the query, buts abort the transaction after receives the result. 

That's the reason why the update does not work. 

The use of commit at the end of the query used to work, but recently it was removed. No commits are allowed on a test anymore. 

So, you just can't use the SQL to chamge database using the test functionality. 

You will have to create a page, with a buton, that calls a screen action that executes your sql. 

Cheers

Hi Hiroshi

I don't know if you want to use this query in an action in order to bulk update your data dinamically. 

If so, you have a slight error on query syntax:

boolean values are either 0 or 1 (not 'true') in the editor, even though they appear as 'false' or 'true' on test result.

therefore it should be:

UPDATE Well

SET  Well.IsActive = 1 

WHERE Well.OutletId = @OutletID

Hi Helena, 

SQL Server accepts this syntax :) 

But using standard SQL is always better. 

Cheers. 

From some versions of Outsystems Service Studio 10 and 11 onward updating via test query is no longer possible. Enjoy it while you can...

How Eduardo and Bruno told you, 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.


Evidence attached