Please give me some advice on how to make a transaction work as I have visualized.
I have a database which is vertically oriented, with 4 columns that together form a unique combination
(Station, PartNumber,PartFamily, and ID)., in addition to the values associated with the unique combination
I have an array in my PLC, with the PLC array having ID, values.
I’d like to be able to trigger a transaction that matches the 3 tags of station, part number, part family, for all rows, then matches id for specific array elements; ie array[0].id=1, so give me the value where select part number=part number tag and part family=part family tag and station=station tag and id =id tag.
I’ve tried as a block and as a standard transaction, but the select on ID value is the part I can’t figure out.
Is this a possible thing or do I need to look at a different approach?
When trying as a standard transaction, I do update/select on the partfamily= part family tag, partnumber=part number tag, and station=station tag.
I think a block is the better way to do what I want, but I haven’t had luck with that either.
I’m using 8.1.5, but I can update if needed, this is a test box.
When I execute the transaction I can get the first matching row to work, but not the next ones, which gives me a write target does not exist message.
Thanks!
Transaction groups, IMO, work best in a wide format vs a tall one. I’d probably script this one, if it stays in the db as a block.
1 Like
This may or may not be the “best” solution, but it worked for what I was trying to do; based on what Jordan said.
I defined a named query to do a select based on station and part number.
I then created a library script that gets passed a station, part number, and a base tag reference to my structure of values, which is 20x UDT instances named Value00-Value19.
It would work for a larger set, and it doesn’t do bounds checking to see if you have too many values in the dataset vs the number of tags you have. Finally, it does a blocking tag write.
The library script:
def RecipeDownload(BasePath,Station,PartNumber):
#This will do a select on the recipe table for the given station and part number.
#It will then write the Station Recipe Value structures.
#Nothing will be written in the event of an empty set except for the handshake
params = {“Station”:Station, “PartNumber”:PartNumber}
data = system.dataset.toPyDataSet(system.db.runNamedQuery(“GetRecipeValues”, params))
i=0
paths=[]
values=[]
for row in data:
newpaths=[BasePath+’/Value’+str(i).zfill(2)+"/ID" #i is index, zfill pads to 2 digits
,BasePath+’/Value’+str(i).zfill(2)+"/Type"
,BasePath+’/Value’+str(i).zfill(2)+"/Name"
,BasePath+’/Value’+str(i).zfill(2)+"/ValueString"
,BasePath+’/Value’+str(i).zfill(2)+"/ValueReal"
,BasePath+’/Value’+str(i).zfill(2)+"/ValueDint"
,BasePath+’/Value’+str(i).zfill(2)+"/Unit"
]
newvalues=[row[0],row[1],row[2],row[3],row[4],row[5],row[6]]
i=i+1
paths=paths+newpaths
values=values+newvalues
#end of the dataset
handshakepath=[BasePath+"/Handshake"]
paths=paths+handshakepath
values=values+[‘20’]
#Write Handshake of 20 at end of script
system.tag.writeBlocking(paths,values)
return(i)
1 Like