Named Query Parameters
Parameters allow you to make Named Queries dynamic. They act as placeholders you can pass values into when requesting the query to execute. Other resources in Ignition can then pass arguments into the parameters. The exact implementation depends on what resource is requesting the Named Query, such as a Named Query binding, a Named Query Data Source, or the system.db.runNamedQuery() function. Check out the Named Query Example page for a complete example on passing parameters into a named query.
Types of Named Query Parameters
There are three types of Parameters in Named queries. Each varies in usage.
Value
The Value type should be used whenever a Named Query needs a dynamic WHERE clause. These act like values passed to a prepared statement, meaning they can never be used to parameterize column or table names. However, they are resilient to SQL injection attacks.
SELECT * FROM mytable
WHERE name = :myParam
QueryString
QueryStrings are more flexible than the Value type in that they can be used to parameterize column and table names. However, their values are never sanitized, which causes them to be more susceptible to SQL injection attacks. When using QueryStrings, it is best to avoid situations where the user can manually type in the value that will be passed to the Name Query. Additionally, if you are using a QueryString for a string in the where clause, you would need to provide quotation marks.
SELECT {myColumnName} FROM mytable
WHERE name = '{myName}'
Database
Database type parameters cannot be created manually. Instead, it is automatically created when the Database Connection dropdown on the Authoring section is set to <Parameter>. Additionally, this parameter is not used in the body of the query. This type allows you to parameterize the database connection when the Named Query is called. This way the Named Query can run against multiple database connections specified by the resource that made the request.
Parameters while Authoring a Named Query
Creating Parameters
New parameters can be created in the Authoring section of a Named Query by clicking the add button (
) next to the Parameters table.
In Named Queries, Parameters are referenced by their name, so renaming the Parameter will require you to update it on any other resources that are using it.
Each parameter has three properties represented by different columns in the table. These may be edited by double-clicking on the cell you wish to modify:
- Type: Changes the type between Value and QueryString.
- Name: Determines the name of the parameter, and how it will appear in the query. Names are not case-sensitive and must be unique. Additionally, they may only use letters, numbers, dashes and underscores.
- Data Type: Specifies the datatype of the parameter. The Type of the parameter determines which data types are available. Note, that QueryStrings may only be configured as strings, where as Value-type parameters have more types available.
Byte Arrays
Using Parameters in the Query
Once created, parameters can be inserted into the Query field by drag-and-drop from the Parameter table onto the Query field, or by using the right-click menu in the Query field.
Additionally, the parameters may be typed in manually, but the correct syntax must be used.