Skip to main content
G
Gucio16-Pearl
16-Pearl
July 4, 2018
Question

Custom service with insert into command

  • July 4, 2018
  • 1 reply
  • 2290 views

hi all,

 

is it possible to insert multiple rows  into Oracle table via service?

I have simple sql command:

INSERT INTO thingworxTestTable (PROPERTY1) VALUES ('02'), ('03')

but it doesn't work. Thingworx ends with error:

"Unable to Invoke Service FillTable on ReservationSystem_Oracle : Execute Update failed: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended".

Sql script seems to work ok only for one line, because having two lines of code:

INSERT INTO thingworxTestTable (PROPERTY1) VALUES '02'
INSERT INTO thingworxTestTable (PROPERTY1) VALUES '03'

also ends with this same error message.

Any idea how to type multiple command rows in one service??

 

thanks in advance

Krzysiek

 

1 reply

S
supandey5-Regular Member
5-Regular Member
July 9, 2018

Hi @Gucio if you are looking to run Insert statements from ThingWorx SQL Service, you could create an SQLCommand service with Insert statement and then call this under a wrapper service something like

 

1. Create SQL  Insert  Service

 

Insert into table1 (sNO, name) values ([[sNO]], [[name]])

here's how it'd look in the Service section, with two inputs :

 

09-07-2018 11-18-13.jpg

 

2. Wrapping  my SQL Command service 

 

for (var x = 5; x < 15; x++) {
 var params = {
 sNO: x /* INTEGER */,
 name: "Insert from wrapper services" /* STRING */
 	};

 // result: NUMBER
 var result = me.InsertService(params);
}

09-07-2018 11-19-27.jpg

Terms & ConditionsAccessibility statement