How to get system.db.runPrepUpdate into a script variable

Ignition
1

I’m trying to get the SQL index ID (for use in another INSERT statement).

system.db.runPrepUpdate("INSERT INTO Shipping_Form ([Requestor Badge Number], [Purchase Order Number], [RMA Number], [Ledger Code Number]) Output Inserted.ID VALUES (?,?,?,?)", [empID, poNum, rmaNum, ledgerNum])

When I use the query builder (lets say on a text field) I get the returned index value.

When trying to duplicate this in script, how to do i get the OUTPUT Inserted.ID into a variable?

Edit:
So ultimately I’m trying to insert a record into a (MS) SQL table, get the unique index (PK) and use that index value for another insert into a daughter (FK) table. Maybe my approach is totally off…

2 
pk=system.db.runPrepUpdate("INSERT INTO Shipping_Form ([Requestor Badge Number], [Purchase Order Number], [RMA Number], [Ledger Code Number]) Output Inserted.ID VALUES (?,?,?,?)", [empID, poNum, rmaNum, ledgerNum],getKey=1) 
print pk

You need to have the getKey=1 in the call

3

Ok, getting this error..

ava.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO Shipping_Form ([Requestor Badge Number], [Purchase Order Number], [RMA Number], [Ledger Code Number]) Output Inserted.ID VALUES (?,?,?,?), [55425, 123, 123, 123], , , true, false)

caused by Exception: Error executing system.db.runPrepUpdate(INSERT INTO Shipping_Form ([Requestor Badge Number], [Purchase Order Number], [RMA Number], [Ledger Code Number]) Output Inserted.ID VALUES (?,?,?,?), [55425, 123, 123, 123], , , true, false)
caused by GatewayException: SQL error for "INSERT INTO Shipping_Form ([Requestor Badge Number], [Purchase Order Number], [RMA Number], [Ledger Code Number]) Output Inserted.ID VALUES (?,?,?,?)": A result set was generated for update.
caused by SQLServerException: A result set was generated for update.

May I ask where you found that? Seems pretty obscure.

4

Get rid of the OUTPUT Inserted.ID.

5

Perfect, thanks guys!

6

It is shown in the users manual.

For Ignition 8 here

For Ignition 7.9 here

The fourth code snipit on the page shows and example of this funtionality.

Just for future reference for a passer by

1 Like