Skip to main content
Z
ZbigniewK12-Amethyst
12-Amethyst
July 22, 2024
Solved

SQL: passing part of query as input parameter

  • July 22, 2024
  • 1 reply
  • 4720 views

I have simple SQL query:

SELECT [column 1], [column 2], [column3] ...

  FROM [table] WHERE [column1]= 31.

In my case, conditions are chaning, sometimes I have no WHERE part of querry, sometimes I have 1 column, sometimes 3, depending on operators behaviour.
So in query, I added variable filter as parameter, and make a querry like:

SELECT [column 1], [column 2], [column3] ...

  FROM [table] [[filter]]

and filter would generated by external service and can be empty if all data needed, but can be also WHERE [column1]=31, or any other combination of WHERE.

Unfortunatelly, no matter if I keep filter value empty, or I do put there my WHERE conditions, I receive 

 "Unable to Invoke Service ... on ... : Execute Query failed. Please check Database logs for more details."
Have tried to call this service manually, have tried to call by other service, changed name of variable to be sure it's not forbidden word... no changes. 

Have tried with filter as both, String and Text type. 
Any body has idea how to cope with this issue, without generating extra querries for every possible case?

 

TWX version: ThingWorx 9.5.1-b359

 

Best answer by Velkumar

Hi @ZbigniewK 

 

Please use below query to pass condition as Input

 

select * from public."testtable" <<condition>>

 

Output 

 

Velkumar_1-1721642844458.png

 

/VR

 

 

 

 

1 reply

V
19-Tanzanite
Velkumar19-TanzaniteAnswer
19-Tanzanite
July 22, 2024

Hi @ZbigniewK 

 

Please use below query to pass condition as Input

 

select * from public."testtable" <<condition>>

 

Output 

 

Velkumar_1-1721642844458.png

 

/VR

 

 

 

 

    Z
    ZbigniewK12-AmethystAuthor
    12-Amethyst
    July 22, 2024

    So, the solution was to put my condition in << >> instead of [[ ]]?

    Do I assume well, that we use [[ ]] when we want to put our variable as value which we want to compare with datas from SQL, and << >> when we want to add part of code/query? 

    Rocko
    Rocko19-Tanzanite
    19-Tanzanite
    July 22, 2024

    Correct. see also articles https://www.ptc.com/en/support/article/CS289219 and https://www.ptc.com/en/support/article/CS261346

     

    The box bracket notation can use a Prepared Statement, i.e. the syntax is fixed and you fill in values only.

    The angle bracket notation allows for syntactically different statements, which is why one needs to be extra careful to avoid SQL injection.

     

      Terms & ConditionsCookie settingsAccessibility statement