Retrieving Multiple DB Tags from one SQL Query Execution?

Ignition
1

Is it possible to run a SQL QUERY once and populate multiple DB TAGS vs running a separate SQL QUERY multiple times with slight variations to pull the individual values?

Basically wanting to assign the data retrieved based on the columns of the table assign those values to specific DB TAGS.

Thank you,
Casey

2

This seems like a good use of a gateway script or maybe a transaction group.

In scripting it would look something like this (assuming column 1 has a path, and column 2 is a value):

rs = system.db.runQuery("SELECT * FROM Sometable;")
for row in rs:
    system.tag.writeToTag(row[0], row[1])
3

To go a step further, if you take your query results (still assuming it’s in a path/value kind of format) and put them into lists , you can use a single system.tag.writeToTags (note the ‘s’ on the end):

rs = system.db.runQuery("SELECT * FROM Sometable;")
tagPath=[]
values=[]
for row in rs:
    tagPath.append(row[0])
    values.append(row[1])
system.tag.writeToTags(tagPath,values)

The documentation hasn’t quite caught up to this one, yet… :wink:

4

Thanks for the tips…

In my particular scenario I’m working with data from a query result such as this returning the most recent record from the DB:

QUERY
SELECT TOP 1 *
FROM LABELERS
WHERE line_id = ‘5’
ORDER by 1 DESC

RESULT
id prod_id line_id lot_code timestamp
28084 1605 5 2341257 2-17-2012 13:17:09

What I’m trying to do is I’m wanting to take which even prod_id, line_id, and lot_code that returns from the query and use those 3 items from the query result to fill in 3 TAGS for use elsewhere.

I have been trying to use transation groups, tried a couple different types but can never seem to get the group to return any values on the items I add to the group. I will continue to read up on that along with the gateway scripting ideas you both shared.

Thanks Again,
Casey

5

Can you give an example on how the query result would translate to a tag path? May be able to come up with an idea or two…

6

That’s where I’m a bit foggy to begin with.

I know that I want to take the values from 4 columns within one row of the database. I have the query written which gives me the values I want to use. However I’m uncertain the rules of how the data must be handled in order to get those values into OPC tags, must they be written to OPC tags?

I’m essentially wanting to turn the values read from the database into Database Tags. Which is what I’m doing now, but it requires sending 32 queries to the database everytime I update the tags. 4 values from each row, for 8 different peices of equipment. 4x8

I was thinking that perhaps there would be a simple way to run the query once per peice of equipment to pull the 4 values I need, taking those 4 values and storing them as DB Tags. In return taking me from 32 queries to only 8.

I don’t have any OPC tag addresses setup as of yet to utilize for this so I am completely open to any suggestions. I do have DB Tags setup in the current project for pulling the values I need resulting in the 32 queries on every update.

Thanks again,
Casey

7

The main reason I’m having to do this is that the OPC-COM driver isn’t working correctly on the x64 version of Ignition yet.

The machines I’m reading from have OPC servers on them but unfortunately I can’t run the ignition opc-com driver to gather the data without crashing the gateway.

So instead I’m writting the values to MSSQL and using ignition to read the values from MSSQL.

8

Have you thought about using a opc-ua wrapper?

I use the kepware version and it is rock solid.

9

A few things:

  1. In regards to writing tags to the database, and then populating them in a different Ignition, that’s exactly what external SQLTags are made to do. You would create a “database driving provider” to write the data to the database, and then a “database provider” to read the data. The driving provider is part of the SQL Bridge, however, so there is some cost there. If you are already writing data to the db with your own program, you might look at simply writing to the external sqltags table format instead, creating your own sqltags driver (this thread has more info)

  2. I believe we might have our 64-bit OPC-COM issues worked out. I can send you a dev build of the module, if you want to give it a shot. However…

  3. An OPC-UA wrapper isn’t a bad idea, because setting up remote connections over DCOM is annoying and somewhat fragile.

Regards,

10

Colby,

Thanks for the reply. That sounds good, I will take a closer look at the sqltags driver and give that a shot.

Also, for the 64-bit OPC-COM module… I would like to try that out and give you feedback. I’ll load up a trial and see how it works if you can provide with the dev module.

Thanks again,
Casey