Skip to main content
Oct 4, 2024 7:04 AM UTC
Solved

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

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

RockoAnswer
Oct 4, 2024 8:00 AM UTC

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

3 replies

RockoAnswer
Oct 4, 2024 8:00 AM UTC

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

Oct 4, 2024 8:02 AM UTC

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

Oct 4, 2024 8:04 AM UTC

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.