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

[ptc-6815479]

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.

[paic]

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.

[ptc-6815479]

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]

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

[vranganathan]

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

[lshihao]

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

[kqian-2]

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

[dking1]

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.