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 training@inductiveautomation.com.
Version:
LESSON LIST
Autoplay
Off
-
2:47Property Binding
-
2:15Property Binding – Bidirectional
-
2:19Tag Binding
-
2:17Tag Binding – Drag and Drop
-
2:19Tag Binding – Bidirectional
-
1:40Tag and Component Overlays
-
3:20Indirect Tag Binding
-
2:17Tag Historian Binding
-
3:21Expression Binding
-
1:24Expression Binding – Concat Strings
-
1:01Expression Binding – Celsius to Fahrenheit
-
2:01Expression Binding – Format Date
-
2:46Expression Binding – Date Manipulations
-
1:23Expression Binding – Bit Functions
-
2:06Expression Binding – Switch
-
1:43Expression Binding – Checking Conditions
-
2:40DB Browse Binding
-
2:05DB Browse Binding – Dynamic Filters
-
1:16SQL Query Binding
-
2:26SQL Query Binding – Polling
-
1:47SQL Query Binding – Dynamic Filters
-
1:55SQL Query Binding – Scalar Query and Fallback
-
1:47SQL Query Binding – Scalar Query and Update
-
2:24Cell Update Binding
-
2:07Function Binding
-
4:22Component Styles
Take Topic Challenge
LESSON
SQL Query Binding – Scalar Query and Fallback
Description
Learn how to bind a scalar property to a SQL query and return the fallback value when no rows are returned.
Video recorded using: Ignition 7.7
Transcript
(open in window)[00:00] You can bind any property to a SQL query in Ignition. It is important that your query brings back the appropriate value based on the data type of the property that you're binding to. So for example, if you're binding your label's text property to a query, you better bring back a single string. If you're binding a numeric label to a query, you better bring a numeric value. So let's do a couple of examples. Let's say on my label that I want to bind it to a query to bring back a single string value. When I bind the text property here to a SQL query, and my query's going to select the name from my recipes table where the ID equals one. Of course I can specify which database connection to run that query through, which in my case is MySQL. I can specify the polling mode, which I want to just run once so I'm going to set polling off. And there's a fallback value here. The fallback value's very important in that if your query does not return a result, then you can enable this and specify what you want the value to actually be, otherwise we will error out. So I know that my query here will return results, so I'm going to go head and press OK, and I can see "Recipe 1" as the name of that recipe. I can do the same thing on my numeric label.
[01:04] I can go in here and bind the value to a SQL query, again specifying a query that brings back a single result. This time I'm going to bring back sp1 and I'm going to specify where ID equals ten. That recipe does not exist in my database, so of course there's nothing that'll be able to come back. So I'm going to go ahead and specify the database connection, MySQL, polling mode off, and I'm going to leave this fallback unchecked. As soon as I press OK, you're going to see that it errors out. It basically says the "Query returned no rows" since it doesn't exist in the database. So it's important to either leave that error in so the operator knows that something's wrong or in your query you can specify a fallback value, so I can say let's bring back a -1 if nothing exists. Soon as I press OK, you'll see that there's no error and the value here gets -1. So you can certainly bind individual properties to SQL queries returning a scalar result.