LESSON

Named Query Overview

Description

Learn about how to create a Named Query and how they work. Named Queries are predefined queries that are called from the client but executed in the Gateway. This offers increased security over the traditional way of pulling querying such as SQL Query Binding or the query scripting functions.

Video recorded using: Ignition 7.9

Transcript

(open in window)

[00:00] New to Ignition 7.9.4 is named queries. Named queries are pre-configured queries that may be invoked elsewhere in a project. Named queries are conceptually similar to the project in shared scripts, except instead of scripting, it uses queries to pull data from a database. This allows you to define a query once, and then reference it many times throughout the project. You can even pass in parameters to make the queries dynamic. Named queries have their own section in the project browser, located here in the project section. You can right-click on the named queries object to add as many new queries as you would like. In addition, you can also add folders to help keep your queries organized. Each named query has three basic tabs. A settings tab, where you can configure some basic settings and security for your named query. An authoring tab, where we can set up our query and add any parameters that the query will use. And a testing tab, where we can test our named query before we use it out in the rest of our project. One of the major benefits of using named queries is the added security that they provide over a standard query, such as a SQL query binding. Named queries will all be executed from the gateway, with the client simply specifying what query to use, as well as passing in the required parameters. This help prevent malicious SQL attacks from unwanted parties. In addition, you can also restrict access to the query right here in the settings tab under security. You can specify a security zone, a user role, or both. All of this helps to keep your queries and your data secure. Let's take a look at the authoring page to get an idea of how a named query is created. First we can specify a database connection. We can either choose the default database connection for this project, a specific database connection that we have set up in our gateway, or we can pass in a parameter that will be used as the database connection instead. This allows us to dynamically change which database connection this query is going to be run against. We also need to specify what type of query we're going to be running, whether it's a standard select query, a scaler query, or an update query where we're going to be passing information into the database. Next, we can specify what type of parameters we want to use in our query. There are two options for parameters here, value and query string. The value type parameters act like values passed into a prepared statement. They work well at making a where clause dynamic and are very resilient to SQL injection attacks. However, they cannot be used to parametrize column or table names, so you'll have to use a query string parameter type for those instead. The query string parameter type works much like parameter references do in SQL query bindings, by directly referencing the value. This is what allows them to be used to parametrize column and table names. However, because of this, they are more prone to SQL injection attacks and we here at Inductive Automation caution you against using them. As you can see, selecting the query string parameter type will also have a warning symbol warning you away from using that parameter type. In addition to the differences I already mentioned, parameters are also referenced differently within the query. Let's take a look at the query area. Here we can see a pseudo query written in and we can see our two different parameters being referenced here. Notice that the value parameter type first has a colon and then the name of the parameter, while the query string has the name of the parameter enclosed in curly braces. This helps differentiate between the two different types of parameters. In addition to manually typing in a parameter, you can also right-click on the query area to add in one of your parameters or you can drag and drop your parameters into your query. One important thing to note here is that the way I use these parameters also differs. With the value type parameter, because its acting like a value passed to a prepared statement, it'll automatically add quotation marks around the ends of it because its a string. So I can leave that parameter reference alone and not add anything to it. However, the query string parameter type works a little bit differently. Because it's a direct reference, it's going to directly pass in the string that I specify for that parameter. This means that either my parameter value will need to have quotation marks built into it or I will need to add quotation marks around the parameter reference within my query. This should look familiar, as this is what's used in most of the SQL query bindings. Finally, once we're done with our query, we can go to the testing tab to test it. The first thing that you may notice is that even though I only have two parameters listed in my parameters table, there's actually three parameters listed here. This is because I had my database connection set to parameter. It won't show up here on the parameter's table but in testing or when using it out in the project, it will show up as a usable parameter that I can enter in a value for. I can then specify values for my other two parameters and then finally hit the execute query button to see the results of my query. If all goes well, I will be able to see what the executed query will return and can even export those results by clicking the export to csv button. Let's talk a quick look at how this works. I have a query already set up here. It's a simple select query with only one parameter that filters on my bay number. If I go up to the testing tab, I can try typing in a value of three for bay three and then clicking execute query. I can then compare these results against the results that I expected to see. Once you are finished setting up your query, you're then ready to use it throughout your project, such as in a binding, reporting, or even scripting.

You are editing this transcript.

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

close

Share this video