SQL in Ignition

Leveraging Databases

In addition to all the normal HMI functionality you would expect, Ignition has the ability to connect to databases, and this can greatly increase the functionality available to you! You can use databases to store history, create easy to search lists and configurations, and retrieve data from ERP or other systems. When you start using SQL, you can expand your project from a simple HMI to a project that brings your whole process together. The best part is that Ignition connects to as many databases as you want, and from one central location.

Ignition is so familiar with using databases that in many cases you just need to provide a database connection and Ignition will do the rest! Of course if you want to manually control your data, you can. The database lives outside of Ignition and we don't maintain any control or rules over what you can do with your data.

Displaying Data

You can easily display information from your databases on a window along with anything else in Ignition. You can show parts lists, step sequences, realtime or historical charts, or add the ability to search for inventory.

images/download/attachments/6035085/SQL_history.PNG

Storing and Updating Data

Ignition has a many tools to add information to your databases, or interact and modify existing data directly from a client. You can make tables interactive, or set up anything from storing tag history, to storing files in the database, to complete inventory tracking systems.

images/download/attachments/6035085/SQL_storing_data.PNG

On this page ...

What is SQL?

SQL stands for "Structured Query Language" and is the backbone of most modern relational databases. It's often referred to as "S.Q.L." or "Sequel" but both are correct and widely recognized. This language (similar to, but different than the Python Scripting Language) allows you to write out requests or "queries" against the existing data to view, add, edit, or remove the information you want in a simple format.

Everything in a relational database is based around tables. Tables store the basic information for any system and can be combined together to make very efficient queries to retrieve your data.

images/download/attachments/6035085/SQL_table.PNG images/download/attachments/6035085/SQL_query.PNG

Using SQL in Ignition

There are many types of queries, and many ways to use them in Ignition. Some provide an easy to use builder to automatically store or fetch data, and some allow you to completely customize your queries. Ignition allows you to use SQL to bind components, store Tag History, interact automatically with the database, create reports, and more!

Storing Tag History

There's a simple user interface to start data logging on Tags you've created. Just check a few settings and Ignition will take car of the rest!

images/download/attachments/6035085/SQL_tag_history.PNG

Displaying and Analyzing Data

You can leverage existing data in the database to create displays and track data throughout your system.

images/download/attachments/6035085/SQL_analyzing.PNG

Creating Forms

You can easily mix tables and components to set up your own forms and entry screens. Modify data on the fly and provide instant updates to all clients.

images/download/attachments/6035085/SQL_Forms.PNG

Creating Rich Reports

You can leverage any data across all database connections to create rich reports. From simple data logging to complex reports with grouped charts and datasets.

images/download/attachments/6035085/SQL_report.PNG

Storing and Updating with Transaction Groups

Transaction Groups are simple data storage at their finest! Just drag a few tags over and start logging data right away. This is especially useful when you have a distributed system and want to store a lot of data to a central location.

images/download/attachments/6035085/SQL_transaction_group.PNG

Queries

SQL queries are crucial to Ignition's database-centric model. Queries can show what is available or alter data in the databases, and some companies have staff dedicated to running databases and creating queries. Click here to learn more about Querying, Adding, and Editing Data. Anywhere Ignition is fetching data, you can choose to use your own custom queries to get exactly what you want out of the database. You can make your queries as simple or complex as you like. If your database is large, you might have a whole team dedicated to creating these queries for you and Ignition will happily accept them.

SELECT * FROM mytable
SELECT users.id, users.firstname, users.lastname, roles.name as 'rolename'
FROM users
INNER JOIN mapping ON users.id = mapping.userid
INNER JOIN roles ON mapping.roleid = roles.id
WHERE roles.name = 'Administrator'

Database Connections

Any SQL query you use needs a Database connection, but Ignition simplifies all that by creating database connections in the Gateway instead of in the clients. This means from one central location, you can manage all your database connections, and you don't have to worry about planning around adding clients in the future. Any special rules or connection restrictions are taken care of in the Gateway.

Other Resources for Learning SQL

The following are unrelated to Inductive Automation and in no way affiliated, but provide good tutorials and basic SQL querying and syntax.

Similar Topics ...

In This Section ...