Skip to Content (Press Enter)
UserImage.jpg
Greg Adams
108Views
9Comments
Solved
SQL Execute (@string) error. Passing a SELECT as a string
Question
SQL
Reactive
Database
Application Type
Reactive
Service Studio Version
11.54.4 (Build 62290)

I am testing a simple SELECT. It runs as expected, but when passed as a string it fails on syntax.

This is the query

SELECT {Table2}.[Id], {Table2}.CauseofDeath, {Table2}.[Year],{Table2}.[Month],{Table2}.[Agegroup],{Table2}.[Vaccinationstatus] FROM {Table2} WHERE {Table2}.[Id]=3

I am testing putting that into a string variable called @StringQuery 

and then using this command

EXEC (@StringQuery)

To get the expression editor to accept the string it is formulated with double quotes

"SELECT {Table2}.[Id], {Table2}.CauseofDeath, {Table2}.[Year],{Table2}.[Month],{Table2}.[Agegroup],{Table2}.[Vaccinationstatus] FROM {Table2} WHERE {Table2}.[Id]=3"

On test this produces

Error in advanced query SQLQueryTest: Incorrect syntax near '}' 

I have tried using single quotes, but the expression editor points to the first {

Syntax error caused by unexpected '{' element in expression. 


I have read many pages about using EXEC and @parameters, but not seen anything that seems relevant.

The query works, but for some reason it isn't accepted as a string variable when using EXEC 


 


1
0
16 Apr 2023
Copy Link
2021-09-06 15-09-53
Dorine Boudry
 
MVP
Solution

@Caldeira81, @Ramesh Subramanian , @Prince Kumar,

your 3 answers point to the same thing, and this is not the cause of that error.  

Although it is not advisable to use attribute names without square brackets, they don't necessarily lead to sql errors, but rather to incorrect data being returned in case attributes start being renamed in Outsystems.

@Greg Adams, you should not use the EXEC construct, just 

  • build up a correct sql string
  • pass that into your SQL widget
  • set the property to 'expand inline'
  • think carefully and extensively about the warning you get about SQL injection
  • put that parameter as the only thing in your SQL, without a EXEC(...) around it

Dorine

See solution in context
3
0
17 Apr 2023
Copy Link
UserImage.jpg
Greg Adams

Yes, that worked. 

Thank you.


I will recap for anyone else who has the same problem:

If building an SQL Query in a variable:

  1. Use " double quotes around the string.
  2. Set the input variable to Expand inline (see later)
  3. Inside the SQL exprssion editor just put the identifier which begins with @ as the expression. (Don't use EXEC() )

There are screenshots below.


To switch to Expand Inline:

Left double click the SQL widget you are using.
Left click the Input Paramter

To the right hand there is a place to turn on Expand Inline

 Here is proof that it worked

  

Here is more detail for anyone else who has the same problem:

The Query had worked directly even with the missing [ ], but those who pointed it out were worth pointing out that I had missed them. 

I have tested that & it made no difference adding them.

I had used EXEC(@queryString) because that seemed to be what was listed in the few places I could find about this, but that was wrong.

Outsystems seems to have almost nothing on the subject in documentation. Obviously they recommend aggregates.

When I tested using @StringQuery on its own the following error was generated, but that was for a different reason. I had, at some stage turned Expand inline to No. 

I assume that this has to be Yes  so that the system allows the text to be treated as instructions.

The expression in the SQL widget is now just @StringQuery, but without expanding inline it produced  this error

Error in advanced query SQLQueryTest: The name 'SELECT {Table2}.[Id], {Table2}.CauseofDeath, {Table2}.[Year],{Table2}.[Month],{Table2}.[Agegroup],{Table2}.[Vaccinationstatus] FROM {Table2} WHERE {Table2}.[Id]=3' is not a valid identifier. 

This is the default value which is in double quotes

"SELECT{Table2}.[Id], {Table2}.[CauseofDeath], {Table2}.[Year],{Table2}.[Month],{Table2}.[Agegroup],{Table2}.[Vaccinationstatus] FROM {Table2} WHERE {Table2}.[Id]=3"


Trying the @QueryString on its own without Expand Inline, the SQL regards this as an identifier, not as a query.

2
0
17 Apr 2023
Copy Link
2019-02-27 17-48-20
Caldeira81

Hi, @Greg Adams 

It seems that is missing a [ ] "SELECT {Table2}.[Id], {Table2}.CauseofDeath, " in the column CauseofDeath.

"SELECT {Table2}.[Id], {Table2}.[CauseofDeath], {Table2}.[Year],{Table2}.[Month],{Table2}.[Agegroup],{Table2}.[Vaccinationstatus] FROM {Table2} WHERE {Table2}.[Id]=3" 


Hope that could helped, please let me know if it was solved.

Regards

0
0
16 Apr 2023
Copy Link
UserImage.jpg
Greg Adams

Thanks for the reply. I had not spotted the missing [ ], but the query worked without them.

It was only when running it inside a variable that there was a problem.

Dorine Boudry solved it.

But thank you for answering.

1
0
17 Apr 2023
Copy Link
2025-10-18 11-13-53
Ramesh subramanian

Hi Greg Adams,

The error is here.


2
0
16 Apr 2023
Copy Link
UserImage.jpg
Greg Adams

Thanks for the reply. I had not spotted the missing [ ], but the query worked without them.

It was only when running it inside a variable that there was a problem.

Dorine Boudry solved it.

But thank you for answering.

0
0
17 Apr 2023
Copy Link
2026-04-23 05-29-20
Prince Kumar

Hi Greg Adams, 

Please make this entity variable {Table2}.[CauseofDeath] in brackets. 

That's the only syntax is missing in your query.



0
0
17 Apr 2023
Copy Link
UserImage.jpg
Greg Adams

Thanks for the reply. I had not spotted the missing [ ], but the query worked without them.

It was only when running it inside a variable that there was a problem.

Dorine Boudry solved it.

But thank you for answering.

0
0
17 Apr 2023
Copy Link
2021-09-06 15-09-53
Dorine Boudry
 
MVP
Solution

@Caldeira81, @Ramesh Subramanian , @Prince Kumar,

your 3 answers point to the same thing, and this is not the cause of that error.  

Although it is not advisable to use attribute names without square brackets, they don't necessarily lead to sql errors, but rather to incorrect data being returned in case attributes start being renamed in Outsystems.

@Greg Adams, you should not use the EXEC construct, just 

  • build up a correct sql string
  • pass that into your SQL widget
  • set the property to 'expand inline'
  • think carefully and extensively about the warning you get about SQL injection
  • put that parameter as the only thing in your SQL, without a EXEC(...) around it

Dorine

3
0
17 Apr 2023
Copy Link
UserImage.jpg
Greg Adams

Yes, that worked. 

Thank you.


I will recap for anyone else who has the same problem:

If building an SQL Query in a variable:

  1. Use " double quotes around the string.
  2. Set the input variable to Expand inline (see later)
  3. Inside the SQL exprssion editor just put the identifier which begins with @ as the expression. (Don't use EXEC() )

There are screenshots below.


To switch to Expand Inline:

Left double click the SQL widget you are using.
Left click the Input Paramter

To the right hand there is a place to turn on Expand Inline

 Here is proof that it worked

  

Here is more detail for anyone else who has the same problem:

The Query had worked directly even with the missing [ ], but those who pointed it out were worth pointing out that I had missed them. 

I have tested that & it made no difference adding them.

I had used EXEC(@queryString) because that seemed to be what was listed in the few places I could find about this, but that was wrong.

Outsystems seems to have almost nothing on the subject in documentation. Obviously they recommend aggregates.

When I tested using @StringQuery on its own the following error was generated, but that was for a different reason. I had, at some stage turned Expand inline to No. 

I assume that this has to be Yes  so that the system allows the text to be treated as instructions.

The expression in the SQL widget is now just @StringQuery, but without expanding inline it produced  this error

Error in advanced query SQLQueryTest: The name 'SELECT {Table2}.[Id], {Table2}.CauseofDeath, {Table2}.[Year],{Table2}.[Month],{Table2}.[Agegroup],{Table2}.[Vaccinationstatus] FROM {Table2} WHERE {Table2}.[Id]=3' is not a valid identifier. 

This is the default value which is in double quotes

"SELECT{Table2}.[Id], {Table2}.[CauseofDeath], {Table2}.[Year],{Table2}.[Month],{Table2}.[Agegroup],{Table2}.[Vaccinationstatus] FROM {Table2} WHERE {Table2}.[Id]=3"


Trying the @QueryString on its own without Expand Inline, the SQL regards this as an identifier, not as a query.

2
0
17 Apr 2023
Copy Link
2019-02-27 17-48-20
Caldeira81

Glad that the community helped this. Sorry for not understanding the sql advance way that you need.

Also check the SQL SANDBOX forge component that is a great and helpful tool

SQL Sandbox - Overview | OutSystems  https://www.outsystems.com/forge/component-overview/5900/sql-sandbox


REgards,

1
0
17 Apr 2023
Copy Link
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.