Advanced SQL query for update is not updating the value in table

Hi

I am trying to update a boolean field in an entity and i used the below query which is not updating anything on entity. 

SyntaxEditor Code Snippet

update {NotificationDetails} set {NotificationDetails}.[is_read] = true where {NotificationDetails}.[userId] = @userId 

can anyone help me on this?

What version of outsystems are you using?

Are you using "Test Query"? Ou is this in run-time?

Bruno Gonçalves wrote:

What version of outsystems are you using?

Are you using "Test Query"? Ou is this in run-time?

Hi Bruno,

using 11.0 version and i am running on "Test Query"

I can't recall the exact version but from some version of 10 onwards trying something like


BEGIN

UPDATE ...;

COMMIT;

END;


Results in Service Studio showing the following error:



laxmiprasanna b wrote:

Hi

I am trying to update a boolean field in an entity and i used the below query which is not updating anything on entity. 

SyntaxEditor Code Snippet

update {NotificationDetails} set {NotificationDetails}.[is_read] = true where {NotificationDetails}.[userId] = @userId 

can anyone help me on this?

Hi,

Try write COMMIT in the end of query and execute Test Query.

In version 10, returns this error but execute the update.


Example to delete action

You'll have to execute that in run-time.

Just create some screen with an input field and pass that as a single parameter into an advanced query with expand inline = true.

Access the screen and run whatever you want.

Hi Lakshmi,


This Query is fine but after this sql you should put "Commit" action and its working

update {NotificationDetails} set {NotificationDetails}.[is_read] = true where {NotificationDetails}.[userId] = @userId 

Because some time it craete problem if you didnot committed action of update or delete action it not reflact in data.


Hope this will help you.

Thanks

Rahul Sahu

After the version 11.6 you can't do "COMMIT". To do an update or delete, you need to build a page.

but if the version is less than 11.6 then you just need to commit, and in the output parameters, add any variable, structure or entity, no matter what.

Regards,

Nuno Verdasca

Nuno Miguel Verdasca wrote:

After the version 11.6 you can't do "COMMIT". To do an update or delete, you need to build a page.

but if the version is less than 11.6 then you just need to commit, and in the output parameters, add any variable, structure or entity, no matter what.

Regards,

Nuno Verdasca

Not only on version 11, some versions of 10 have the same behavior (ex: 10.0.1013.0)


OK!

I'm using version 10.0.822.0.

Hi laxmiprasanna b,

Instead of using commit inside the update query use the CommitTransaction action immediately after the update query , you can find this action under  systems in add references dialog.

Regards,

-PJ-

BEGIN

UPDATE ...
EXECUTE IMMEDIATE 'COM'||'MIT';

END;


if MSSQL

Same thing just change the exec line.

EXEC ('COM'+'MIT'); /*can't recall if the parenthesis are required, just check documentation*/