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.
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.
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.
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!
Displaying and Analyzing Data
You can leverage existing data in the database to create displays and track data throughout your system.
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.
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.
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.
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 mytableSELECT users.id, users.firstname, users.lastname, roles.name as 'rolename'FROM usersINNER JOIN mapping ON users.id = mapping.useridINNER JOIN roles ON mapping.roleid = roles.idWHERE 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 ...