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:34Querying Data from Database
-
2:56Filter Data in Table
-
2:17Add Data to Database
-
4:21Edit Data in Database
-
2:18Refreshing SQL Data on Component
-
3:00Deleting Multi-Selected Rows from Table
Take Topic Challenge
Supplemental Videos
LESSON
Named Query Conversion Example
Description
Learn how to convert a Sql Query Binding into a Named Query Binding.
Video recorded using: Ignition 7.9
Transcript
(open in window)[00:00] With the introduction of named queries in Ignition version 7.9.4, you may want to convert some of your older SQL query bindings to use the new name query binding for the added security features. Let's take a look at an example of how we would convert a SQL query binding into a named query binding. Here on this window, I have a table component which is using a query to pull in storage bay information from a database table, and that query is being filtered by a bay number that I pass in using the text field below it. If I go ahead and open up the binding on the data property of the table we can see what it looks like. It's a pretty simple query, but it works as a good example. Query bindings now have a button down at the bottom of them that says convert to named query. We want to go ahead and click that button. A popup window will then come up that asks us to enter in a path for the new named query. I'm just going to call mine test. Then we need to click OK. Now what has happened is that it has automatically created a named query for us called test, and changed this binding to be a named query binding looking at that test named query. You can see that my query remained the same with the only difference being the parameter. Now it looks a little bit different. The converter has automatically made a query string parameter for us and called it text but we can see that it's still bound to the text property of the text field component on our same window. I can then simply hit the OK button and my table works just the same as before. I can put it into preview mode and change the storage bay number here on the text field, and it will automatically update just as it would with the SQL query binding. The only difference is, now it's using a named query binding. One important thing to note though, is that when doing conversions from SQL query bindings to named query bindings, because of the way parameters are referenced in a SQL query binding, for it to work after the conversion all parameters are automatically converted into query string type parameters. While the query string type parameters are flexible enough to work with the conversion, they are less secure. So because of this, we recommend that you go back to the named query and modify it to use all value-type parameters. Let's go ahead and do that right now. I'm going to go ahead and close out of my binding, and I'm going to find my named query that I just created called test. If I go to the authoring tab, you'll see that my parameter is a query string. I'm going to instead change that to use a value-type parameter. In addition to changing the parameter type, I also need to change the way it's referenced down here in the query. On top of modifying the parameter reference in the query, I also need to move the quotation marks that surround the parameter. This is because the value-type parameter works like inserting a value into a prepared statement, and doesn't need the quotation marks. So I can just delete all of this. And then I would want to grab my parameter and drag it down there into the query. I would want to make sure I modify all of the parameters to be value-type parameters. Once I finish modifying my named query, I can then go back to my window, and look at my table's binding again. You'll notice that now it's using the value-type parameter and it still works just as well as it did before. If I can change the bay number here, you'll see the data in the table changes.