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

Community Tip - Did you get called away in the middle of writing a post? Don't worry you can find your unfinished post later in the Drafts section of your profile page. X

service type of SQL(command) cannot handle INFOTABLE parameters.

ptc-6815479
1-Visitor

service type of SQL(command) cannot handle INFOTABLE parameters.

Hi,


I'm writing a Database connection thing to insert data into my local database. I have a service type of SQL(command) accepting type INFOTABLE parameter. But Thingworx complains when using fields of this parameter: 

Error executing SQL command. ERROR: ERROR ERROR: syntax error at or near "["; Error while executing the query


The way I'm inserting the record is like this: 

insert into myTable values ([[myInfotable.recordOne]], [[myInfotable.recordTwo]]);


When breaking the INFOTABLE into a list of parameters, this way of insertion works just fine.


Can you guys help? Thanks a lot.



7 REPLIES 7
paic
12-Amethyst
(To:ptc-6815479)

Can you try this using << >> vs. [[ ]] that does mean it becomes String substitution vs. parameter substitution and you have to be aware of possible SQL injection.

If that does not work, the other method, just about the same but you would create an SQL Command that just runs a SQLQuery (define input parameter of SQLQuery) and it would just read <<SQLQuery>>
and a second regular javascript service that forms the actual SQL Query you want to run that invokes the SQL Command service.

Thanks for your reply, I tried the <<>> method, but still get errors.


I guess SQL Command services cannot access infotable fields inside right now, is that right?



paic
12-Amethyst
(To:ptc-6815479)

That is probably the case, you can still use the second method that I proposed.

vranganathan
12-Amethyst
(To:paic)

At least with Postgress <<>>works. I am able to pass query/insert using parameters which I received as input from service. More detailed documentation around this will be nice since each database may be treating things differently.

Thanks,

Varathan

Can u write down the service of command please? i don't konw how to use the <<>> in the postgreSQL

Excuse me,have you solved the problem? I had the same problem~

I'm having the same problem as well, but with ORACLE. Parameters such as [[infotable.value]] are returning ORA-00936: missing expression. <<infotable.value>> is returning the same.

Announcements


Top Tags