This lesson is part of the Tags in Ignition course. You can browse the rest of the lessons below.

LESSON LIST

Autoplay Off

Description

Query Tags execute an SQL statement, allowing the Tags system to directly interact with records in a database.

Video recorded using: Ignition 8.1

Transcript

(open in window)

[00:00] In this lesson, we'll take a look at query tags. Query tags, as their name implies, allow you to run a SQL query, and then return the result of that query to the tags value. Query tags are a standard tag, so if we come over and right click anywhere in our tag browser, I can go down to new tag to be able to create a new query tag. I will name this guy, query one. I will set this data type to dataset. The query setting here will allow me to configure the query that will be used to populate this tag's value. So I will click on the pencil icon here to bring up the query editor. I will create a query here that returns all of the data in my database's inventory table which contains ice cream inventory data. I'll commit my change, and now I have to tell my query tag which database to query for its data. If you leave this setting blank, ignition will try and use the default database for the Tag Provider as this tag's data source. I do not mean the default database for this project, I mean the default database for the stock provider, which is very different. My inventory table lives in my DB database so I'll select it and finish my configuration. My tag is now created. From the tag browser, I can drill down to it's value property and I should see all of my inventory tables now. This data is not very easy to visualize in this tag browser, but I can easily drag and drop my tag onto my visual window here, and select table, to display my tag's data on a table. This data is coming directly from my database onto my query tag which is not bound to this table's data property. Since my tag now contains this table's data, it can be used in reference by vision clients, perspective sessions, reports, pretty much any admission subsystem that has access to tags. This becomes useful because now, instead of querying the database, which can sometimes be expensive, I can simply read this tag's value to retrieve the same data. Let's go to tools here, and select to open up our database query browser. Here, we can execute queries against any database or gateway it's connected to. Instead of querying for all the data in my inventory table, I just want to return the inventory count for strawberry ice cream, so I will need to write a query like so. I can use the same query to have my query tag hold the inventory account for strawberry. Let's do that. I will copy this value and head down to my tag browser, double click on my query tag to modify its query, and paste my query here and commit. My query will no longer return an entire table. It instead will return a single integer value, so it's data type no longer needs to be a dataset, but it must be an integer now. I will then hit okay, and you will notice that my tag's value changed and that not only has my strawberry ice cream count. So we just saw how a query tag can be used to retrieve a whole table's data and also just a single value within a table. Now let's go back to our tags configuration. As far as determining how often our query time will execute, that's what the execution mode is for. So this is very similar to the expression tags you may have seen earlier. You can either do an event driven, which means your query has some sort of tag reference inside of it, and whenever that reference updates, we want to fire the query again. You can use a fixed rate, which means specify the execution rate here, and we'll run the query at whatever rate you specify, or you can always set it to a tag group, and then the query's execution will be dictated by the tag group above. You always want to be mindful of a query tag's execution rate, like with any rate of query execution. Say the query tied to my tag here takes five seconds to return a value. It probably would not be wise to set this tag to a fixed one second execution rate, for instance. So hopefully this gives you a good idea of what query tags can do.

You are editing this transcript.

Make any corrections to improve this transcript. We'll review any changes before posting them.