Skip to end of metadata
Go to start of metadata


This feature is new in Ignition version 7.9.4
Click here to check out the other new features

Converting SQL Queries to Named Queries

If you have a SQL Query binding on a component, it is actually really easy to convert it to a Named Query. Simply open up the binding and click the Convert to Named Query button.

On this page ...

IULocgo


Named Query Conversion Example

A popup will then appear, and have you put in a Path to the new Named Query.



Converting with an Update Query

This feature is new in Ignition version 7.9.6
Click here to check out the other new features

In 7.9.6 and above, converting a SQL Query Binding to a Named Query will allow the option to create two Named Queries: one for the Select Query in the binding, and another for the Update Query. 


Warning for 7.9.5 and Prior

When converting a SQL Query Binding that contains an UPDATE Query in 7.9.5 and prior, an Update query will not be generated. Thus, it was possible to lose the Update Query upon conversion.
If using these versions of Ignition, it is highly recommended to manually create the Update Named Query before pressing the Conversion button. As of 7.9.6, this is no longer an issue, and the Convert to Named Query button may freely be used.

After confirming the conversion, the Window will show the query/queries. Click the OK button to save this change.

When converting in this manner, all parameters will be created at QueryStrings. It is highly recommended that you modify your new Named Queries so that these values become Value Parameters instead, as mentioned later on this page.

Modifying Converted Query to Use Parameters

When converting a query to a Named Query, it is strongly recommended that you go back into the query and convert the parameter from a QueryString type to a Parameter type. To do this, you first need to change all QueryString type parameters to the Parameter type. This is as simple as selecting Parameter from the dropdown under Type.

Now that your parameter(s) types have been modified, the second thing you need to do is modify syntax in the query to use the parameter(s). The Parameter and QueryString types are referenced differently in the query, so you will need to ensure that you modify how the parameter is referenced. In addition because the Parameter type works like a prepared statement, it does not need any quotation marks around any string type parameters like a QueryString would, so all quotation marks around parameters should be removed from the query. See the images below for an example.

From this: 

To this:

Once the syntax in the query has been modified, test it out in the Testing section at the top to make sure everything works correctly. If so, then you have successfully converted to a Named Query. 



 

  • No labels