Named Query Workspace

New in 7.9.4

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.

Named Query Icons

Named Queries in the Project Browser use the following Icons.

Icon	Description
	Disabled
	Enabled and open in the current Designer session
	Enabled, and closed in the current Designer session

Workspace Sections

The Named Query workspace contains three sections (or tabs). A description of each section is listed below.

Settings

This section contains configuration properties and security for the selected Named Query. The following properties are available:

Item Name	Description
Enabled	Determines if the Named Query is enabled or disabled. A disabled Named Query may not be executed.
Security	Specifies 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).
Description	Allows you to give the Named Query a description.
Caching	Allows the Gateway to cache the results of the query. See the Named Query Caching page for more details.

Authoring

This section 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.

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.
  New in 7.9.10
  The Fallback value will now also be returned if there is no value returned from the query. Note, that this differs from a null value being returned in that the null value is deliberate.

• 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. |

Parameters

A 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.

Query

The 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 Browser

Provides 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 Builder

Opens 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 Syntax

Specifies 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

This section allows you to test your query without leaving the workspace. Fill in your values and click the Execute Query button to see your results.

Item Name	Description
Test Parameters	Allows 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 Limit	When checked, allows you to set the maximum number of rows the query will return while testing. This will not effect anything outside of this tab. This property is only enabled when the Query Type on the Authoring section is set to Query.
Execute Query	Runs the Named Query using the parameter values listed above. The Results area will display any results returned by the query.
Export to CSV	Exports 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.
Results	The results returned by the Named Query when testing. Populated by the Execute Query button.