Storing variable number of records in SQL DB

I have an array of a UDT in a Compact Logix PLC. It contains five variables per record, but a variable number of (non-zero) records, up to 120 each time. These are unique carton barcodes that have been stacked on a pallet and the array is updated for each completed pallet.

I need to send these barcodes, and the four other variables, to a MS SQL database each time a pallet is completed.

The customer has provided a stored procedure using table-valued parameters and I’m supposed to add as many INSERTS as required. They have given me this example of how to call their stored proc:

Is this possible in Ignition or is there a better way?

TL/DR: You can’t call a stored procedure like that.

Using a temp table is very unfriendly to generic JDBC environments, as the base JDBC interface doesn’t expose table-valued parameters. See this MS documentation for the instructions on doing this with their JDBC driver. However, you will be stymied by the fact that Ignition places JDBC drivers in their own class loaders, and goes to great length to ensure that DB connections are generic. You simply can’t get to those Microsoft datatypes (or array types or other brands’ user column types) in any reasonable way.

The supported way to do something like this is to define a view in your database that selects the same columns from the real table, and attach the DB-side code to the view as an INSERT trigger. You would construct a standard SQL multi-row insert, something like this:

INSERT INTO PNZ_Pallet_Insert_View VALUES (?, ?, ?, ?, ?), (?, ?, ?, ?, ?).....

Dynamically adjust the number of row-groups and supply all the row values to the .runPrepUpdate function. On the DB side, the insert trigger will get all of the rows in a batch, equivalent to the table-valued proc.

IMNSHO, Microsoft pushes these types of solutions to lock their customers into their toolsets.

1 Like

Thanks Phil, much appreciated.