Binding Properties to a SQL Query

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. They are very similar to the DB Browse binding type in that they both query a database to return data. The difference is the SQL Query Binding is written completely by hand. This is useful for more complex queries where you will use more advanced functions of the SQL language that can not be accomplished with the DB Browse binding.

Pro Tip!

The query that gets generated by the DB Browse will transfer over to the SQL Query binding. It may be useful to build the basic query structure with DB Browse first, then switch to SQL Query binding to modify the query to fit your needs.

Polling Mode

The Polling Mode dictates how often the query will execute. All Polling Modes exist on all the database binding types. The three Polling Modes are as follows:

Off – The query will run once when the window is opened, and again whenever the query changes (if the query is dynamic, see below)
Relative – The query will poll at the project's Base Polling Rate, which is 5 seconds by default, plus or minus the given Polling Rate.
Absolute – The query will poll at the given Polling Rate.

Dataset Binding

The majority of SQL Query bindings will return a dataset (and are usually Dynamic). These will usually return many rows with multiple columns. For example, showing all customer details from a certain account, or all downtime events in the facility. The only important distinction here is that if you are returning multiple rows and/or columns, you need to put that binding on a property of type dataset like the Data property on a Table component. Basic data types like integers or strings can only show the first value of a query result, not the whole set. You can easily see the difference in the binding window because only basic data types will show two query entry fields, SELECT and UPDATE. Dataset properties only show SELECT.

images/download/attachments/6047961/SQL_Query_Binding.png

Dynamic Filters

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'

Scalar Query Fallback

If the property that is being bound is a scalar datatype (that is, not a Dataset), 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.

images/download/attachments/6047961/Scalar_Fallback.PNG

Scalar Query Update

You can bind an input component bidirectionally to a SQL query to allow data be retrieved and updated in the database. This can only be done with a Scalar Query on a non Dataset property. Essentially, we mimic the bidirectionality of Tag and property bindings by adding in an update query to run whenever a value gets entered into the property with the binding. In our update query, we use the special parameter '{this}' to denote the new value from the bound property.

images/download/attachments/6047961/scalar_update.PNG

Take a Text Field with a simple query on it.

SELECT Name FROM area WHERE ID = 1

This will return a single value that can populate our text field. We then enable the Update Query at the bottom of the Property Binding window, and add in the update query.

UPDATE area SET Name = '{this}' WHERE ID = 1

After confirming the binding, we can see that our text field contains the value from the database and will update the database cell if we enter in a new value into the text field. This is a good way to alter very specific cells in a database record.

It works for DB Browse too!

The DB Browse Binding also features a database writeback that provides the same functionality as the SQL Query binding update query, and allows you to make your DB Browse Queries bidirectional as well, as long as your binding is a scalar query on a non dataset property. The update query on the DB Browse binding will also transfer to the SQL Query binding much like the select query, to help you get started with building your query!