SQL Query Binding – Scalar Query and Fallback


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


(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. 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 outputer 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.

You are editing this transcript.

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


Share this video