Search

User Manual

GETTING STARTED


MODULES AND PLATFORM


APPENDIX


TUTORIALS & HELPFUL TRICKS


GLOSSARY


STRATEGIC PARTNER LINKS

Sepasoft - MES Modules
Cirrus Link - MQTT Modules

RESOURCES

Inductive University
Ignition Demo Project
Knowledge Base Articles
Forum
IA Support
SDK Documentation
SDK Examples

ALL MANUAL VERSIONS

Ignition 8
Ignition 7.9
Ignition 7.8

Deprecated Pages

Skip to end of metadata
Go to start of metadata


Converting SQL Queries to Named Queries

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

A popup appears where you can enter a path to the new Named Query.

On this page ...

IULocgo


Named Query Conversion Example

Converting with an Update Query

If your SQL Query binding is on an component property whose value can be updated and written back to the database (for example, input components and button components), you have the option to create two Named Queries: one for the Select Query in the binding and a second for the Update Query. 

  1. Enter the Select Query.
  2. Click the Enabled checkbox for the Update Query.
  3. Enter the Update Query.
  4. Click OK.



  5. In the popup, enter the path for the Select Query.
  6. Click the Yes button next to Create Update Query,
  7. Enter the path for the Update Query,
  8. Click Save. 


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