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

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: screenshots updated to 8.0.2
 


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

Tip
titlePro Tip!

The query that gets generated by the DB Browse will transfer over to the SQL Query binding when you switch the binding type. 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.

Dataset Binding

The majority of SQL Query bindings will return a dataset. These will return many rows with multiple columns. For example, showing all customer details from a certain account, or all downtime events in the facility. This type of SQL binding is used on properties of type dataset like the Data property on a Table component


On_this_page



Scroll HTML Exporter Ignore


Iulink
Description
URLhttps://www.inductiveuniversity.com/video/sql-query-binding?r=/course/components-property-binding
NameSQL Query Binding

 



Image RemovedImage Added

 

Dynamic Filters

Using the curly 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 add quotes to 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.


Scroll HTML Exporter Ignore


Iulink
Description
URLhttps://www.inductiveuniversity.com/video/sql-query-binding-dynamic-filters?r=/course/components-property-binding
NameSQL Query Binding - Dynamic Filters

 



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:

Code Block
languagesql
titleSQL - SQL Query Binding with Parameter References
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:

Code Block
languagesql
titleSQL - SQL Query Binding with the Values Replaced
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'


Note

It is important to use single quotes and not double quotes (t_stamp = "2010-03-20 08:00:00") because these mean something different in certain databases like Microsoft SQL Server.



Scalar Query Update

You can bind a non dataset type property to a SQL query to allow a singular value to be returned from the database with a scalar query. Now instead of returning multiple rows and columns, the query returns a single value from the first row of the first column. These types of SQL Query bindings can also be used to update the database on input components like a Text Field. 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. If {this} is a string, it needs single quotes around it.

Image RemovedImage Added

Take a Text Field with a simple query on it.

Code Block
languagesql
titleSQL - Simple Select Query
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.

Code Block
languagesql
titleSQL - Using Ignition's 'this' Keyword
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 table.


Scroll HTML Exporter Ignore


Iulink
Description
URLhttps://www.inductiveuniversity.com/video/sql-query-binding-scalar-query-and-update?r=/course/components-property-binding
NameSQL Query Binding - Scalar Query and Update

 



 

 

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 contain zero rows.

Image RemovedImage Added


Scroll HTML Exporter Ignore


Iulink
Description
URLhttps://www.inductiveuniversity.com/video/sql-query-binding-scalar-query-and-fallback?r=/course/components-property-binding
NameSQL Query Binding - Scalar Query and Fallback

 



 

 

Stored Procedures

While queries can manually be written on a SQL Query binding, SQL Stored Procedures may also be called from a SQL Query Binding. Note that the exact syntax is highly dependent on the type of database you are using.

For example, calling a Stored Procedure from MySQL would involve using the CALL command, while SQL Server utilizes the EXEC command.

Code Block
languagesql
titleSQL - MySQL Stored Procedure Call
CALL retrieve_daily_total


Code Block
languagesql
titleSQL - SQL Server Stored Procedure Call
EXEC retrieve_daily_total



Named Query Conversions

You can convert the SQL Query created here to a Named Query. For more information, see Named Query Conversions.



Next_link