Custom service with insert into command

[Gucio]

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

[supandey]

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 :

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);
}