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

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

Newbie

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

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

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.

Highlighted

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

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?



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

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

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

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

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

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

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

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

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

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.