Skip to main content
R
RL_120559233-Newcomer
3-Newcomer
October 4, 2024
Solved

Make the table you insert data into as an variable input in a sql command service

  • October 4, 2024
  • 1 reply
  • 1072 views

I am working with thingworx 9.5.

I want to use the insert into sql database command. I want to have the database I want to insert the data into as a variable input, but this doesn't seem to be possible. I made an example of how I am trying it but I keep receiving errors:

 

insert into [[Table]] (logid,message,source,category,servicename,level,country) 
values ([[LogID]],[[Message]],[[Source]],[[Category]],[[ServiceName]],[[Level]],[[Country]]);

 

When I change the [[Table]] parameter by the name of the db, all is fine.

error returned is: 

Execute Update failed: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1" Position: 13

 

Does anyone have a workaround for this?

Kind regards

Best answer by Rocko

try to replace [[]] with <<>>, at least for the table part.

1 reply

Rocko
Rocko19-TanzaniteAnswer
19-Tanzanite
October 4, 2024

try to replace [[]] with <<>>, at least for the table part.

R
RL_120559233-NewcomerAuthor
3-Newcomer
October 4, 2024

That made it work! I just can't find much documentation about these things. Thank you

Rocko
Rocko19-Tanzanite
19-Tanzanite
October 4, 2024

Check on https://www.ptc.com/en/support/article/CS289219 and

https://community.ptc.com/t5/ThingWorx-Developers/SQL-passing-part-of-query-as-input-parameter/m-p/962499

 

JDBC is trying to make a Prepared Statement from it with the box brackets, but that only works with the values in the statement, because then it can be parsed ahead of time. This won't work with the table name being dynamic.

The <<>> is more flexible, but also allows you to send ANYTHING to the database (think SQL injection attack). So be careful.

Terms & ConditionsCookie settingsAccessibility statement