cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Query: Assignment of value of an input variable in to internal temporary variable

Regular Member

SQL Query: Assignment of value of an input variable in to internal temporary variable

Spoiler
SQL Query: Assignment variable

Hello all !!!!

I have problems with assignment of value of an input variable to temporary variable. I create service with SQL Query. Added an input variable [[container]] with type of data a string. In assignment attempt: set @Container=[[container]] - the compiler ceases to highlight syntax and execution happens to an error. At execution of any command as soon as I insert an input variable, [[.....]] check of syntax shows that it is not right.

image.png

Whether somebody knows in what an error? In SQL Managment studio everything works.

6 REPLIES 6

Re: SQL Query: Assignment of value of an input variable in to internal temporary variable

My best guess is that the compiler within composer isn't set up to recognize that syntax as a possible parameter substitution.

You can try the more dangerous and more powerful << >> which is a string substitution and shouldn't cause any complaints but exposes you to potential SQL Injection

Re: SQL Query: Assignment of value of an input variable in to internal temporary variable

You suggest to write set @container=<<container>>; instead of
set @container=[[Container]] ?

Re: SQL Query: Assignment of value of an input variable in to internal temporary variable

Correct!

For input parameters into a service standard when you bring it in it will be [[ ]] which is Parameter substitution but << >> is String substitution, so potentially the service is more 'forgiving'

If it still doesn't accept that, then you may have to go the full 'build your query as a string' route and then execute it with <<YourFullSQLStringHere>> approach

 

Here is an article on it: https://community.ptc.com/t5/ThingWorx-Developers/How-can-I-execute-a-sql-query-constructed-dynamica...

Re: SQL Query: Assignment of value of an input variable in to internal temporary variable

I tried to do so 

IF  <<Container>> is not null
The compiler returned the message "Unable to Invoke Service GetEquipment MSSQL : null"
Tried so:
<<IF  [[Container]] is not null>>
The compiler returned the message"Unable to Invoke Service GetEquipment on MSSQL : Execute Query failed: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '<'."
 
Also I tried to conclude all query code in <<>> - the compiler return the same message

Re: SQL Query: Assignment of value of an input variable in to internal temporary variable

Did you try 

set @Container=<<container>>

 

Else you need to build the whole SQL as a STRING first in a separate JavaScript service

and then supply that full string as an input parameter to your SQL Query/Command

and then do <<MyInputParameterSQLString>>

Re: SQL Query: Assignment of value of an input variable in to internal temporary variable

Hi @Eforenkow_PMI.

 

If one of the solutions recommended by @PaiChung helped to solve your issue, please mark it as the Accepted Solution for the benefit of others who may have the same question.

 

Regards.

 

--Sharon