SQL Query return no result while trying to set data for column.

I tried to execute following query using OutSystems SQL functionality and the test shows that the query return no result thus unable to set the data for the column

Can somebody tell me why this SQL Query return no result? 


SyntaxEditor Code Snippet

UPDATE {Users}
SET Token = cast([id] as varchar(10)) + cast(cast(round((rand() * 500000000.0), 0) as int) as varchar(10))
WHERE Id = @Id 

Hello Daniel.

Putting it simply, UPDATE does not return data. it modifies it. SELECT returns data.

To do an Update you can use the Update action in the Entity. No need to do SQL code.

Nuno Reis wrote:

Hello Daniel.

Putting it simply, UPDATE does not return data. it modifies it. SELECT returns data.

To do an Update you can use the Update action in the Entity. No need to do SQL code.

Exactly, I want to change the data, but the statement syntax is correct for other languages that I used in the past, so why, when I execute this statement on OutSystem SQL, it doesn’t change anything?

Solution

Are you testing on a page or from Service Studio? The SQL debug doesn't change the DB anymore. Only published code. Safety issues.

PS: There are better ways to generate a token than all those random and casting. For instance, the Generate Password function.

Solution

Hi,

A little note to complement this posts:

After the version 11.6  and some versions of 10 have the same behavior (ex: 10.0.1013.0)... you can't execute a COMMITto 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.


The alternatives are:

  1. Create a timer with your query and start this timer in ServiceCenter. (It will run and Commit just after the timer runs)
  2. Use an external component to do that, for example: 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.

Cheers,

Nuno Verdasca


Hi Daniel,

If you're getting this problem when testing in Service Studio, you already have the answer from Nuno Reis and Nuno Verdasca.

If the problem is in runtime, please check if you have an error in Service Center. Either way, a good practice is to always fully qualify the attributes of the entities, as in certain circumstances the physical names of the columns may not match the actual attribute name.

UPDATE  {Users}
SET     {Users}.[Token] = cast({Users}.[Id] as varchar(10)) + cast(cast(round((rand() * 500000000.0), 0) as int) as varchar(10))
WHERE   {Users}.[Id] = @Id 


 Cheers,

Tiago.

Hi Daniel,

Your syntax is not following OutSystems naming rules for SQL.

Entities need to be written as {entityname} 

Columns as {entityname}.[columnname]

UPDATE {Users}
SET Token = cast([id] as varchar(10)) + cast(cast(round((rand() * 500000000.0), 0) as int) as varchar(10))
WHERE Id = @Id 

Should be:

UPDATE {Users}
SET {Users}.[Token] = cast({Users}.[id] as varchar(10)) + cast(cast(round((rand() * 500000000.0), 0) as int) as varchar(10))
WHERE {Users}.[Id] = @Id 


For more information about Advanced SQL please read:

https://success.outsystems.com/Documentation/11/Developing_an_Application/Use_Data/Query_Data/SQL_Queries

Regards,

Daniel

Nuno Reis wrote:

Are you testing on a page or from Service Studio? The SQL debug doesn't change the DB anymore. Only published code. Safety issues.

PS: There are better ways to generate a token than all those random and casting. For instance, the Generate Password function.

I was using SQL debug. Now tried with production and it works. Thank you. You saved my time!

Daniël Kuhlmann wrote:

Hi Daniel,

Your syntax is not following OutSystems naming rules for SQL.

Entities need to be written as {entityname} 

Columns as {entityname}.[columnname]

UPDATE {Users}
SET Token = cast([id] as varchar(10)) + cast(cast(round((rand() * 500000000.0), 0) as int) as varchar(10))
WHERE Id = @Id 

Should be:

UPDATE {Users}
SET {Users}.[Token] = cast({Users}.[id] as varchar(10)) + cast(cast(round((rand() * 500000000.0), 0) as int) as varchar(10))
WHERE {Users}.[Id] = @Id 


For more information about Advanced SQL please read:

https://success.outsystems.com/Documentation/11/Developing_an_Application/Use_Data/Query_Data/SQL_Queries

Regards,

Daniel


Even though it still works now without following this rule.