You're currently browsing the Ignition 7.9 docs. Click here to view the latest docs.

 

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.

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.

Using a Parameter

Multiple Parameters

 

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