SQL Query Binding

The SQL Query binding is a polling binding type that will run a SQL Query against any of the database connections configured in the Gateway.

Dynamic Queries

Using the brace notation, you can include the values of component properties (within the same window) and tag values inside your query. This is a very common technique to make your query dynamic. The values of the property or tag represented are simply substituted into the query where the braces are.

Note that because the substitution is direct, you'll often need to quote literal strings and dates to make your query valid. If you're getting errors running your query complaining about syntax, it is important to realize that these errors are coming from the database, not from Ignition. Try copying and pasting your query into the Query Browser and replacing the braces with literal values.

Example

A common requirement is to have a query filter its results for a date range. You can use the Date Range component or a pair of Popup Calendar components to let the user choose a range of dates. Then you can use these dates in your query like this:

SELECT
   t_stamp, flow_rate, amps
FROM
   valve_history
WHERE
   t_stamp >= '{Root Container.DateRange.startDate}' AND
   t_stamp <= '{Root Container.DateRange.endDate}'

Notice the single quotes around the braces. This is because when the query is run, the dates will be replaced with their literal evaluations. For example, the actual query sent to the database might look like this:

SELECT
   t_stamp, flow_rate, amps
FROM
   valve_history
WHERE
   t_stamp >= '2010-03-20 08:00:00' AND
   t_stamp <= '2010-03-20 13:00:00'

Fallback Value

If the property that is being bound is a scalar datatype (i.e. not a Dataset), then the value in the first column in the first row of the query results is used. If no rows were returned, the binding will cause an error unless the Use Fallback Value option is selected. The value entered in the fallback value text box will be used when the query returns no rows.

When binding a Dataset to a SQL Query, no fallback value is needed, because a Dataset will happily contain zero rows.

See also:

Polling Options

Creating a Database Connection