Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.


New_in
Version7.9.4

 


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



Scroll HTML Exporter Ignore


Iulink
URLhttps://inductiveuniversity.com/video/named-query-conversion-example
NameNamed Query Conversion Example




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



Anchor
namedQueryUpdate
namedQueryUpdate

Converting with an Update Query

New_in
Version7.9.6



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