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

Custom service with insert into command

Garnet

Custom service with insert into command

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

 

Krzysztof
1 REPLY 1

Re: Custom service with insert into command

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