Description

Learn how SQL query-based expression items can be used to pull in data from the database for use in groups. These Expression Items can be used for calculations or as a trigger for the Group.

Video recorded using: Ignition 7.7

Transcript

(open in window)

[00:00] Expression items in a group can represent three different types of values. They can be static values, an expression, or they can run SQL queries. In SQL Query mode, they can execute select statements to retrieve values that can then be used by other items in the group, or written to the database, or they can be data modification statements such as update, insert or delete, where they might reference items in the group in order to write those values to other places in the database. In the Item Configuration window, besides the text field for writing the query, we have the ability to set the Data source that we will target, which can be the same or different than that of the group, and we have a setting for the Query Type. The item has to know whether the query is a Select or an Update, and usually can Auto Detect. But for more complicated multi-line queries, or call stored procedures, it may be necessary to set it directly. Our example group here updates the first row of a table, and has a column called trigger column. So as an example, we'll write a query that will select that value from the table, and we'll use that to trigger the group, in other words, we're going to trigger the execution of the group, off of a column in the table, and the group will write back to that with a new value. So I've written a simple select statement and I'll rename the item and set it to run always. Now I'll set the group trigger, set it to greater than zero and enable the group. If we go to the database, and IP the column, on the next execution, the select statement will see the value and the group will begin to trigger. SQL query expression items can do all of the things that other expression items can do, for example, they can write to database columns or they can write to other items, and they can reference items in the group inside of their query's. As an example of this, imagine that we had a different table that we wanted to insert a value into each time this group ran, perhaps it would be better to use another transaction group, but for the purposes of this example, we'll write a SQL query that does just that. Set the name, and we'll leave it set to run on the trigger, we'll come over and create a SQL query, and set it to an insert statement. At this point, we'll reference a different tag in our group that has the value that we want. The reference will get injected directly into the query, as if the value had been typed by hand. If we save the item, that's all we need. Now each time the group executes and is triggered, that insert statement will be run with the current value of our ramp tag. SQL query expression items provide a great way to interact with data from different parts of the database. The ability to query from different database connections than the group is set to use, is a great way to get data from other systems. However, it's very important to keep in mind the impact that running query's can have on a system. Creating many SQL items inside a large number of groups can really drive up the average query count against your database, and so it's important to be mindful of the number of queries that you create and when they get executed. Running queries on triggers can be a way to improve this situation and avoid problems.

You are editing this transcript.

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