Skip to main content
Version: 8.1

Named Query Workspace

Named Query Workspace Overview​

Named Queries have a dedicated workspace inside of the Project Section of the Designer. This workspace allows for the creation and testing of Named Queries. Once created, the Named Query may be called from another resource, such as a datasource in a report or a component using a Named Query binding.

Named Queries are created by right-clicking the Named Query item in the Project Browser. Like other resources in the Project Browser, Named Queries can be organized in folders, which creates a unique path to the query and helps keep your queries organized.

note

Named Queries are referenced by path, so renaming the Named Query or any parent folders will require you to update the path on any other resources that are using it.

Also like other resources, multiple Named Queries may be opened in the same Designer session. Tabs at the bottom of the Designer allow for easy swapping between Named Queries.

Workspace Sections​

The Named Query workspace contains three tabs: Settings, Authoring, and Testing. A description of each section follows.

Settings​

This Settings tab contains configuration properties and security for the selected Named Query.

The following properties are available:

Item NameDescription
EnabledDetermines if the Named Query is enabled or disabled. A disabled Named Query may not be executed.
SecuritySpecifies a combination of Security Zones and Roles that may call the Named Query. Only roles in the projects user source will be available in the Role dropdown. Multiple rows may be configured to account for granular access restrictions (i.e., requests from Administrator roles originating from the Office security zone area could be allowed, while requests from same users in the plant floor zone could be denied).

If the request does not match any of the specified zone and role combinations, then the query will not run. Additionally, if a ScalarQuery type has a Fallback value configured, that Fallback value will not be returned either: the query will not execute due to security settings, so there is never a chance for other errors to occur.

Either the Security Zone or the Role (but not both) may be left blank. This means it is available to all objects of that type (i.e., with a blank Role and the "office" Security Zone, all roles in the "office" zone are valid).
DescriptionAllows you to give the Named Query a description.
CachingAllows the Gateway to cache the results of the query. See the Named Query Caching page for more details.

Authoring​

The Authoring tab is where the query and parameters are created. There is also a Table Browser and Query Builder that can be used to help you to create your query.

note
New in 8.1.24

Starting in Ignition 8.1.24, switching to another tab from the Authoring tab will automatically commit any changes to the Named Query's parameters.

Item NameDescription
Database Connection

The database connection the Named Query should run against. In addition to a list of the database connections configured in the Gateway, this dropdown contains two unique values: <Default> and <Parameter>.

  • <Default>: The query will execute against the project's default database connection.
  • <Parameter>: The query expects the database connection name to be passed in as a parameter when called. This allows you to use a dynamic database connection.
Note: A parameter for the database connection does not need to be manually created in the Parameters section. Instead, the Named Query will have a special "Database" type parameter available when called. Below is an image of a Named Query binding that is utilizing the <Parameter> connection type.
Query Type

The type of query to execute. The following options are available:

  • Query: Allows SELECT Queries and returns a full dataset. This type should be selected when running a SELECT statement that returns multiple rows or columns.
  • ScalarQuery: Allows SELECT queries and returns a single value. This type should be used when running SELECT statements that only return a single value. The very first cell returned will be the only output.
    This type is special in that a Fallback value may be defined. The Fallback value will be returned if the Named Query would return an error. Note, the Fallback parameter will not be returned if the request does not meet the security requirements.
  • UpdateQuery: Allows all UPDATE types of queries (querys that mutate or otherwise modify rows on a table) and returns the number of rows affected by the query. This type should be selected when modifying the database in some way, such as when running an INSERT, UPDATE, or DELETE query.
ParametersA table of the parameter names and types that will be used in the query. These parameters have three types, Value, QueryString, and Database. Most commonly, the Value type is used and can be accessed by using the :paramName notation. More details on this field may be found on the Named Query Parameters page.
QueryThe query that will execute when the Named Query is called. You can type directly into this field or use the Table Browser on the right to get started. Right-Clicking inside this field will cause a popup menu to appear:

Most of the items on this menu are self-explanatory, but a few require special mention:

Parameterize: Contains two sub-menu items, which are detailed below. Note, that the sub-items will be disabled unless you right-click on some text that does not reference a parameter, table name, or column name in the query.
  • Make Value: Turns the selected text into a value-type parameter. The new value-type parameter will appear in the Parameter table above the query.
  • Make QueryString: Turns the selected text into a QueryString. The new QueryString will appear in the Parameter table above the query. Note that QueryStrings are susceptible to SQL injection attacks. Because of this, the Make Value option is recommend over this option.
Insert Parameter: Quickly creates a reference to the selected parameter. This menu is an alternative to dragging-and-dropping from the Parameters table or typing the name of the parameter.
Table BrowserProvides a list of the tables in the selected Database Connection. Tables may be dragged into the Query field to quickly insert the name of the table. Additionally, right-clicking on a table in the list will cause a popup menu to appear:

  • Create SELECT Statement will populate the Query field with a SELECT statement targeting the selected table or selected row of the table.
  • Refresh Tree will refresh the Table Browser.
Query BuilderOpens the Query Builder, which provides an easy way to create SQL queries using a drag-and-drop interface. This button will be disabled if the Database Connection property is set to <Parameter>. This is the same Query Builder used in other places like the reporting data page.
Builder SyntaxSpecifies the syntax the Query Builder should use. Contains syntax for many popular databases, and has a Universal selection that should work in most scenarios.

Testing​

In the Testing tab, you can test your query without leaving the workspace. Fill in your values and click the Execute Query button to see your results.

note
New in 8.1.28

If your Named Query is using a DateTime parameter, you can expand the DateTime picker to easily set a DateTime value.

Item NameDescription
Test ParametersAllows you to manually supply test values to the parameters to the queries. The table is populated by the Parameters field on the Authoring section.
Use Sample Size LimitWhen checked, allows you to set the maximum number of rows the query will return while testing the Named Query. In addition this setting also limits the results of that named query called elsewhere in the Designer. This property is only enabled when the Query Type on the Authoring section is set to Query.
Execute QueryRuns the Named Query using the parameter values listed above. The Results area will display any results returned by the query.
Export to CSVExports the results of the query to a CSV file. The button becomes available after results are returned. Note that this will only return the values shown, check the Sample Size Limit when using this button.
ResultsThe results returned by the Named Query when testing. Populated by the Execute Query button.