User Manual

GETTING STARTED


OTHER EDITIONS


LAUNCHERS


MODULES


PLATFORM


APPENDIX


TUTORIALS & HELPFUL TRICKS


STRATEGIC PARTNER LINKS

Sepasoft - MES Modules
Cirrus Link - MQTT Modules

RESOURCES

Inductive University
Ignition Demo Project
Knowledge Base Articles
Forum
IA Support

ALL USER MANUAL VERSIONS

Ignition 8.1. Offline Version (04/2021)
Ignition 8 Online Version
Ignition 7.9 Online Version
Ignition 7.8 Online Version

Deprecated Pages

SDK Documentation

Employees

Sign In

Skip to end of metadata
Go to start of metadata


Using the SQL Query Datasource

The SQL Query Data Source allows you to craft parameterized queries that run as a prepared statement.  As Prepared Statements, these queries are more resistant to SQL injection offering additional security over basic queries.

The SQL Query type looks very similar to the Basic SQL Query type. It has a large text area where you can enter in a SQL select query. On the right, you have the option to rename the query, choose what database to run this query against, and add in a Nested Query.

On this page ...

IULocgo


SQL Query


Parameters in SQL Query

Because the SQL Query Data Source runs as a prepared statement, passing parameters into this query type works a little differently.

Instead of placing a parameter within { } characters like the Basic SQL Query, we place a ? where we would like to pass in a parameter. Doing this will actually create a new text area below the query area for your parameter. This smaller text area is where you can pass a parameter into the query. You can pass in Tag values or Report Parameters, and since the parameter area allows expressions, you can use expressions to create any value you like from a combination of Tags and Report Parameters.

This is the preferred way to pass dates into your queries. You can use the {StartDate} and {EndDate} default Parameters directly in the Parameter fields below.

You can add as many of these to a query as needed, with new parameter areas popping up underneath as they get added. To help keep track of what parameter corresponds to which "?", when entering in a value into the parameter area, its associated "?" will highlight to show you which parameter you are currently working on.





Crafting Queries with the Query Builder

The SQL Query data source includes the powerful SQL Query Builder tool. The Query Builder is a powerful Drag-and-Drop query building GUI that allows you to make complex queries from your connected databases.  While a basic understanding of SQL helps make the most of the Query Builder tool, most people will have no problem creating effective queries after a brief tutorial. The Query Builder is a third party tool that we brought into the Reporting Module. We go into detail on how to use it on the Query Builder page, but you can also check out the Active Query Builder's documentation for additional information.

Using the Builder

To activate the Query Builder in your SQL Query datasource type, start by selecting the SQL Syntax version from the drop down menu beneath the SQL Query Builder button.  If your Database type isn't available (or you aren't sure), you can get most of the general functionality by selecting the Universal option. Then push the SQL Query Builder button to show the Query Builder.




  • No labels