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 8.1


(open in window)

[00:00] In this lesson, we'll show how to set up a safe default or fallback value for SQL query bindings where there would otherwise be no result returned. You can bind any component property to a SQL query in ignition, but it's important that your query returns an appropriate value based on the data type of the property that you're binding to. For example, if you're binding a label's text property your query needs to return a single string. If you're binding a numeric text field's value property your query needs to return some single numerical value. Let's look at a simple database table using the database query browser to do some examples. We've selected all data from our recipes table and we have a recipe name which is text and two numerical parameters SP1 and SP2 for a total of three records. So going back to our designer. Let's say that on our label.

[01:01] We want to bind its text property to a SQL query which will bring back a single string value. So we'll replace this simple query with one that reads select name from recipes where ID equals 1. Of course, we'll need to specify our database connection which for us will be db_iu and we'll leave the polling mode is off. So it will run only once finally there's also a fallback value here which can have an important role if your query does not return any result you can enable this parameter and specify some safe default to return as opposed to Simply erroring out. In our example query. We know that there's a record with ID equal one. So the query will return a value and so there's really no need to make use of the fallback value. When we press Okay, we see that recipe 1 was returned to the label as the name of the recipe with ID equal one. Now, let's consider an example where the query doesn't return a result. Let's select the numeric text field.

[02:09] Then select its value double binding. And finally a new SQL query. We need a query which returns a single floating Point result. So let's do this. select sp1 from recipes where ID equals 10. Obviously that record with ID equal 10 does not exist in the database. So no result will be returned and we expect some sort of error. Once more we will specify the database connection as db_iu leaving the polling mode off. And for now, we will leave the fallback value disabled when we click ok, we see that our query returns an error message about no rows returned since there is no 10th record and our component is displayed with an error overlay. It's important that we either notify the operator that something's wrong as we've done here or provide some fallback value.

[03:11] So let's return to the value binding once again, but this time let's enable the fallback value and return a safe default of minus one. If our query returns no result when we click. Ok. Now we see that no errors are displayed in the fallback value of minus 1 is returned since there is no ID equal 10 recipe. So to summarize this lesson. We've seen how to use a fallback value on a SQL query binding to avoid errors when the query returns no scalar result.

You are editing this transcript.

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