SQL Query to Block Item

Ignition
1

I'm trying use a transaction group to query the SQL database for the last 50 values and write those values to an array in the PLC (Siemens) using a block item. For testing purposes, if I limit the query to 1 value and set the target of the expression item to a basic item it will write the value to the PLC no problem. So my query is working fine. However, when I set the target to a block item nothing get written to the PLC. I don't see any errors and the execution is successful. I can see the live source value for the array in the block item so I believe I have the block item set up correctly. But it must have something to do with the block item because writing to a basic item worked. Any suggestions?

2

Hi Steven,

I think this would be better served as a script.

In the meantime, we can't quite see your screen from here, so you may have to show us what you've got. :wink:

3

I haven't done scripting, I might look into that.

In this image you are able to see the query value is 65 however the block item Data value stays at 99.

image
image1457×785 82.9 KB

If I set up a basic item it writes the query value of 65 to the item.

image
image1058×763 43.3 KB

This is how I have the block item set up

image
image770×669 30 KB

image
image769×669 27.3 KB

The basic item is set up using the same path as the block item

image
image769×667 32.4 KB

4

So, a couple of things here. :

  • The data type of the query is set to Int4, so it will only return the first value. It would need to be a dataset to get all 50.
  • Unfortunately, you can't write to a single element of the array, so you would need to convert the dataset to a list, then write the list to the array, which the transaction group won't do for you.

A Gateway Tag Change script would be a lot less painful for you.

I'm not a Siemens guy by any means, but I imagine it would go something like:

if not initialChange and event.newValue.value:
	baseTag = '[LC2]DB31,I{}'
	
	dataIn = system.db.runNamedQuery('NamedQueryToGet50Values')
	
	dataOut = dataIn.getColumnAsList(1) #The 1 assumes that dataIn is in a ['t_stamp', 'value'] format.
	
	tagList = [baseTag.format(i) for i in range(50)]
	
	system.tag.writeAsync(tagList, dataOut)

You'll likely (read: almost certainly) need to modify this to fit your needs.

5

Thanks for your help Jordan. I was able to use a Gateway Tag Change Script to write my query results to the PLC. I'm going to mark your answer as the solution.
The script does not like commas in the tag as in [LC2]DB32,I0.0 however if I create tags in the tag browser, I can reference those tags in the script. I have a little more work to finish, but you got me past my hurdle. Thanks again.

6

In Ignition, that is an OPC Item Path, not a tag. OPC drivers present tags (in the device) as OPC items/nodes (in the OPC server) to be subscribed in OPC tags (in Ignition). There's no automatic availability of named device "tags" as Ignition tags, and no requirement that Ignition tag names match device "tag" names (in the device).

1 Like