Skip to main content
S
svisveswaraiya_2859881-Visitor
1-Visitor
May 21, 2019
Question

passing like operator in Sql query

  • May 21, 2019
  • 1 reply
  • 1460 views

Hi,

In my sql query I am trying to search words in column having letters that I search for. I am using LIKE operation to perform this but it doesn't work. It works fine in sql server but in TW it shows error.

 

TW:

select * from
(SELECT DISTINCT STUFF(WORK_ORDER.order_number,1,8,'') as JOBNum FROM WORK_ORDER) t1

WHERE t1.JOBNum LIKE '%'[[input]]'%'

 

OUTPUT ERROR: Unable to Invoke Service SequencedProductionOrder_JobNumMatch on NAVISTAR_DB_CONFIG : Execute Query failed: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'.

 

SQLSERVER:

select * from
(SELECT DISTINCT STUFF(WORK_ORDER.order_number,1,8,'') as JOBNum FROM WORK_ORDER) t1

WHERE t1.JOBNum LIKE '%234%'

**WORKS FINE**

 

How can I resolve this and use the operator in TW?

 

Thanks in advance,

Shalini V.

1 reply

T
tmisner16-Pearl
16-Pearl
May 21, 2019

Hello Shalini,


Thank you reaching out on the PTC Developer Community.


I proceeded to run some tests using a query similar to yours which includes a subquery along with a LIKE statement.


My initial tests worked correctly in that the expected information was returned from the database. This configuration however did not include a passed in parameter value.


When I appended a pass in value I was able to duplicate the "Execute Query failed: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'." error in tests where quotes were not used around the LIKE criteria.


When using quotes I get "The Index 1 is out of range" error. This is likely due to the double square brackets which are required around the parameter. At this time it seems this may be a limitation with the database connector.

 

Please create a case with Technical Support via the PTC Support Portal. So we can look into this further and confirm if this is indeed a limitation.


Regards,

Tyler Misner

ThingWorx Technical Support Engineer

    Terms & ConditionsAccessibility statement