SQL table to UDT

Ignition
1

Hi,
I have a table in a SQL server DB with multiple columns and multiple records. I need to write the entire table to an array in a CompactLogix PLC. The array is an array of UDTs. The UDT members match the table columns.
The frequency of data changing in the database, and hence needing to be updated in the PLC is low.

Is it possible to do this with a transaction group?
If not, is there a recommended way to achieve this?

2

I don't use transactions groups but I doubt you can do this with it. It seems rather specialised.

I would do this in a script library and most likely write to the tags directly to the OPC-UA tags via system.opc.writeBlocking instead of creating a large number of useless Ignition tags for them.

Something like this:

ds = system.db.runPrepQuery("SELECT C1, C2 FROM TABLE")
pds = system.dataset.toPyDataSet(ds)

headers = system.dataset.getColumnHeaders(pds)

opcItemPathTemplate = 'ns=1;s=[PLCNAME]Recipe[{}].{}'

opcItemPaths = []
vals = []
for i, row in enumerate(pds):
    for header in headers:
        opcItemPath = opcItemPathTemplate.format(i, header)
        val = row[header]
        opcItemPaths.append(opcItemPath)
        vals.append(val)
        print opcItemPath,val

# comment out when the prints are correct
#system.opc.writeBlocking(opcItemPaths, vals)
2 Likes
3

Sure it can be done with a transaction group.

I think I used a block group, not at my laptop to confirm though, Block Group - Ignition User Manual 8.0 - Ignition Documentation

4

Hi Nick,

You’re a legend, thanks for that cobber! I see you made a couple of edits to your post, so thanks for all the effort, very much appreciated!

Here’s my working version. The only differences is there’s no system.opc.writeBlocking, so I used .writeValues and one of the values was a date time stamp, so I had to cast the val to a string.

params = {}
MotherBatchDataset = system.db.runNamedQuery("qryGetAllMotherBatchDetail",params)
pyMotherBatchDataset = system.dataset.toPyDataSet(MotherBatchDataset)
	
headers = system.dataset.getColumnHeaders(pyMotherBatchDataset)

# OPC Item Path example: ns=1;s=[MainPLC]IF_MotherBatchArray[0].MotherBatch
opcItemPathTemplate = 'ns=1;s=[MainPLC]IF_MotherBatchArray[{}].{}'
	
opcItemPaths = []
vals = []

for i, row in enumerate(pyMotherBatchDataset):
	for header in headers:	
		opcItemPath=opcItemPathTemplate.format(i,header)
		val = row[header]
		opcItemPaths.append(opcItemPath)
		vals.append(str(val))
		print opcItemPath,val
		
quality=system.opc.writeValues("Ignition OPC UA Server",opcItemPaths,vals)

print quality
1 Like