Looking for an efficient way to script from a name query to build a string from a row using all the columns in the row separated by columns. I want to write it to a tag. I am starting off using the db.runNamedQuery to run the query then system. tag.writeblocking to the tag. I just can't find much information on how to build the data collection part in the middle to build the string. Appreciate any help I can get!
There are many ways to accomplish this, the question I have is why? Generally speaking you want to leave values as there base types and do any conversion to string as a last chance operation.
So, why not keep the data as a dataset or JSON object? Why write it to a tag? Does it truly need to be gateway global information?
Where are you executing this script? Vision, perspective, Gateway event, etc....
2 Likes
I was looking to to grab an order from the data base which i send to my PLC so i can use to tell my machine what parts to run. I was going to have to PLC send tag to run the query then move the order in string and send it back to my plc. I am very green so i am looking for the best direction to go.
Do you have the SQL Bridge module? Because transaction groups are made for this process.
If not, then consider using Gateway Tag Events. You can then use system.db.runNamedQuery() to execute the query. Then process the returned data and use system.opc.writeValues() to write the values out directly to the PLC.
Keep the values in the data type that they are.
Is the PLC expecting a concatenated string? Or multiple values?
Yes, i will be using the PLC to pull values out of the string to run certain parts of the machine. When I run the name query i have 1 row and 10 column so i am hoping i can do the system.db.runNamequery(). then pull all the columns together out of the row and then send it to tag. I have the SQL bridge module but i can only seem to bring the columns in separate not together.
Is this your choice or something you are stuck with? IMO, PLC's aren't the greatest at string manipulation, if you can keep the parts separate from the start, that would be my recommendation.
If not then you will have to build the string up and then write it to the tag.
dsReturn = system.db.runNamedQuery('PathToYourQuery',{'param1':param1})
#getColumnNames from dataset and pull values from query results to build string
#I have chosen to create a dictionary and then encode that as JSON before writing to the tag.
#This should give you a consitent model for manipulating the string.
jsonOut = system.util.jsonEncode({col:dsReturn.getValueAt(col,0) for col in dsReturn.columnNames})
#if you truely need this information elsewhere on the gateway.
system.tag.writeBlocking(['[~provider~]tagPath',[jsonOut])
#if you only need the information in the device then you can use
#system.opc.writeValue('OPC Server', 'OPC Item Path',jsonOut)
1 Like
I am stuck with this, and i walked into this by pervous contorls guy that promised it so now i am trying to figure out how to make it all work