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
-
2:55Named Query Binding
-
4:04DB Browse Binding
-
3:10DB Browse Binding - Dynamic Filters
-
2:15SQL Query Binding
-
4:02SQL Query Binding - Polling
-
2:52SQL Query Binding - Dynamic Filters
-
3:45SQL Query Binding – Scalar Query and Fallback
-
3:09SQL Query Binding - Scalar Query and Update
Take Topic Challenge
LESSON
SQL Query Binding - Scalar Query and Update
Description
Learn how to bind an input component bidirectionally to a SQL query to allow data be retrieved and updated in the database.
Video recorded using: Ignition 8.1
Transcript
(open in window)[00:00] In this lesson, we'll show how to bind an input component to a database by directionally as we might also do for a tag. We'll show how to retrieve and display a database value and also how to write a value back into the database. So here's a numeric text field component and below is the database query browser with our recipes table. We'd like to view and update setpoint one for recipe one in this recipe's table. Let's get the needed binding on the input component set up for starters since sp1 is a floating point value. We're going to scroll down and change its number type to double next. We'll bind the value double property. To a SQL query in this binding on the top we can specify the select query that will bring back a value from the database but on the bottom we can also enable the update query to
[01:02] write a value back. Once the user changes it on the component. We're going to make use of both parts for starters in the top select query. Let's update this placeholder query to bring back sp1 from the recipes table for the first recipe that query is going to be select sp1 from recipes where ID equals 1 we'll also let this query pull at the relative default rate of five seconds, so that it always updates on the screen and we need to make sure to set the database to use. So for us we're going to use dbiu then here on the bottom update query we want to enable it so that when we change the value in the component, it will go in the other direction and update that same database value the SP one for recipe one.
[01:54] Let's update this placeholder query to write to SP one. So that will be update. recipes set Sp1 and for the unknown value, we'll use this here on the right as a component value placeholder. Where ID equals one now that both queries are set up. When we press OK we see the value coming back from the database 1.18 is the SP one for recipe one for the next part. Let's make sure we set up two little gotchas first. We'll set the bidirectional communication in the designer and also the auto Refresh on the database query browser then if we go into preview mode. when we modify the input value and hit enter we see the database value updated. So to review in this lesson, we've shown how to set up a bidirectional binding between a component and a database using both the select and update portions of a SQL query binding.