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 (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.
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 positions just dedicated to running databases and creating queries. 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 execute 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'
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.
Queries in Scripting
Ignition offers a number of built-in scripting functions for you to use to query your databases. This makes it very simple to view data, create dynamic scripts that use real data, and more. You can pull individual pieces of information, return whole tables of data, or update your database directly. Depending on the type of query and the sort of results you want, you will use different functions. The following functions are the ones you will use most, and all of them can use a special placeholder (?) to allow for dynamic query building.
|system.db.runNamedQuery()||Used to run a previously setup Named Query from within a script.|
|system.db.runPrepQuery()||Used to run basic SELECT queries to fetch whole datasets. This can be used to populate tables, or to sift through the data to do your own calculations.|
|system.db.runPrepUpdate()||Used to run queries that change the data in the database. Usually used on input form windows to update your database records.|
Used when you want only one value from your results. Perfect for fetching a single value like the highest ID, or first timestamp of a result set.
Each of the different functions takes in different arguments (values) and provides slightly different options and functionality. For example, the runPrepUpdate() can return the auto-generated key from insert queries. This can be extremely helpful and eliminate the need to hit the database multiple times if you are using linked tables.
You can find examples of each of these and all the other database functions in the system.db section of the appendix.
Queries in Tags
Ignition offers Query Tags, which can run queries and return the result as a Tag value, giving all of the projects in the Gateway access to the same Database values.
Queries in Reports
You can leverage queries to access data from all database connections to create rich reports, from simple data logging to complex reports with grouped charts and datasets.
Queries in Transaction Groups
Database Query Browser
The Database Query Browser offers an easy to use environment to run queries in for testing. Here, queries can be tested to figure out what values get returned, or data can be updated through raw queries, or the Database Query Browsers easy to use GUI editor.
Auto Generated Queries
Many systems within Ignition utilize a database connection, but the queries that are executed are constructed automatically by the system and do not require you to build the queries manually. These systems such as the Tag Historian, the Alarm Journal, or the Database User Source are very easy to setup and use since each system will automatically generate the necessary tables in the database, insert the relevant data, and even has prebuilt tools to extract the data. However, it is important to note that while these systems can automatically generate queries for you using the various components, these systems are simply storing data in a database which you can manually query out by building your own SQL queries.