You can help by commenting or suggesting your edit directly into the transcript. We'll review any changes before posting them. All comments are completely anonymous. For any comments that need a reply, consider emailing firstname.lastname@example.org.
We are experiencing playback issues from our video hosting provider. Please check back shortly.
Take topic challenge
Query Tags execute an SQL statement, allowing the tags system to directly interact with records in a database.
Video recorded using: Ignition 8.0
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 results of that query to the tag's value. Query Tags are a Standard Tag, so if we come over to our Tag Browser here, I'll select my Tags folder, and we'll place a Query Tag at the root here. I'll hit the Add Tag dropdown, I'll go down to New Standard Tag, and Query Tag. Let's go ahead and change the name here. Let's change the name here, so I'll just call it My Query Tag. And Query Tags require a query. So, for the Query property over here, we'll hit the little edit button on the right. We need to type an SQL query that will run against our database. So, to start with, I could type Select or Return 100. We'll hit Commit, we'll hit OK, and I'll refresh my Tag Browser over here, and we have our Query Tag, and it has a fixed value of 100. So, because you're writing a query, you do need to be somewhat familiar with any tables you're trying to return values from, or otherwise interact with. Now, let's try something a little more interesting. So, if I go up to Tools at the top of my Designer, if we take a look at the Database Query Browser, we can actually browse any of the available database tables that my gateway has access to. I have this little Equipment table, double-click on it to populate this field with a query, we'll hit Execute, and I do have some data that looks like this. So, we could actually have our Query Tag run this query. Which means the results from the query will be available everywhere else throughout Ignition. So, I'm going to copy this with control and C. We'll close the Database Query Browser. I'll head back to my Query Tag here, we'll give it a little double-click. I'm going to make a couple of changes to the Query property. I'll hit the edit button, and lets go ahead and delete that old query. I'll right-click and paste. Now, when you're doing this, you want to be real careful that you don't actually query for way too much data. I am using MySQL. In MySQL, we can use the "limit" keyword to restrict the number of rows that are returned by this query, but of course, you'll want to consult your database's documentation to figure out the appropriate syntax. So, at maximum, I'll only get 100 rows. Now, if I hit commit here, and if I change the data type on this tag from an Integer, we can actually scroll on down and we can see that there's a Dataset type. Now, if I hit okay here, and I'll refresh my Tag Browser, even though it looks like it's kicking in, just because I made a pretty large change to my tag there. If I start expanding here, you can see that the value property is something I can expand and browse through. You'll see each of those individual columns as well as any values that were returned in the query. So, just like any other tag, we can use the results of this Query Tag in findings and in scripts throughout our various projects. Now, if I go back to my Query Tag, let's edit this again. There's a data source field down below, which I left blank. If you leave this blank, this tag will try to use the default database connection for that tag provider. So, not the project setting, but the tag provider setting. Otherwise, you could always just click the dropdown and explicitly state which one of your connections you want to use. Now, as far as determining how often this actually updates, 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 "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 here. Or, you could always set it to Tag Group, and then only use whatever Tag Group you have up above. So, when the tag group updates, we'll run the query again. So, hopefully that gives you a good idea about what these Query Tags can do.