Skip to main content
P
ptc-68154791-Visitor
1-Visitor
March 10, 2015
Question

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

  • March 10, 2015
  • 3 replies
  • 4412 views

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.



    3 replies

    P
    paic1-Visitor
    1-Visitor
    March 10, 2015

    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.

      P
      ptc-68154791-VisitorAuthor
      1-Visitor
      March 12, 2015

      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?



      P
      paic1-Visitor
      1-Visitor
      March 12, 2015

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

      K
      kqian-21-Visitor
      1-Visitor
      July 12, 2016

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

      D
      dking11-Visitor
      1-Visitor
      December 7, 2017

      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.

      Terms & ConditionsAccessibility statement