Querying, Adding, and Editing Data

Where Can Queries Be Used?

Ignition takes full advantage of the flexibility and extra functionality that databases offer. It allows you execute queries in a variety of places throughout your project, you can get data from your database into just about anything you want in your project. Most notably, you can run queries as part of a binding for any property on any window, or you can use queries in any script you write.

Bindings

Ignition's binding system allows you to query and edit data through a graphical DB Browse builder or by writing a Query directly in the SQL Query option. This means you can easily display any information you'd like on your windows without needing to learn the SQL language. The queries are generated and show to you so you can easily get familiar with the syntax or edit the queries directly by clicking over to the SQL Query option. When binding to a basic (non-dataset) data type, you can use the Writeback feature directly to send any changes back to the database.

images/download/attachments/6035087/DB_Browse.png

Scripting Functions

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 your 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.

Scripting Function
Description

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.

system.db.runScalarPrepQuery()

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.

Other Places

There are a variety of other places that SQL Queries can be used like inside Tags, Transaction Group Expression Items, and more. There are many other places that scripts can be used which means SQL Queries are available just about everywhere you can imagine!

On this page ...

SQL Tables

For information about databases and how to get connected, see the Database Connections section.

The foundation of every database system is a table. Every database consists of one or more tables, which store the database’s data/information. Each table is identified by a name (for example Customers or Orders), and consists of column definitions and rows of data.

The database table columns have their own unique names and have a pre-defined data types. Table columns can have various attributes defining the column functionality (such as the primary key, index, default value, and so on).

While table columns describe the data types, the table rows contain the actual data for the columns.

images/download/attachments/6035085/image2015-6-8_11_23_22.png

Primary Key

A primary key is a way to uniquely identify each row in a table. Every table must have a primary key. A primary key is comprised of either a single column, or set of columns. No two distinct rows in a table can have the same value (or combination of values) in those columns.

The Primary Key can be:

  • Auto-incrementing: an integer value that counts up as rows are added

  • Statically defined: any row that is inserted must fill in these value(s) without creating duplicates

Index

Indexes speed up the querying process by providing swift access to rows in the data tables, similarly to the way a book’s index helps you find information quickly within that book. Indexes are extremely important when querying large sets of data. You should create an index for the set of columns you use commonly in a WHERE clause. For example, you should add an index on the timestamp column of a historical table when querying the table by a start and end date. Ignition does this automatically when it creates tables for Tag History or Transaction Groups.

Foreign Key

A Foreign Key is a referential constraint between two tables. The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. The columns in the referencing table must be the primary key in the referenced table. For example, you might have a list of suppliers with an integer ID column. Then your invoices would use the supplier's ID instead of the name. These linked tables can save a lot of space because you don't have to include everything about the supplier in each invoice.

Example

Supplier ( SupplierNumber, Name, Address, Type )
Invoices ( InvoiceNumber, SupplierNumber, Text, Cost )

NULL Value

NULL is a special marker used in SQL to indicate that a data value does not exist in the database. This way it is clear that there is no data, you don't have to guess if a value of 0 is correct or just missing data. By definition, NULL is not equal to anything, even other NULL values. Every database has a function to identify if a value is NULL, usually called isNULL() or something similar.

SQL Queries

SQL or Structured Query Language is a standard computer language for accessing and manipulating database systems. SQL statements are used to create, maintain, and query relational databases like MySQL, SQLServer, Oracle, etc.

You might notice a lot of CAPITALIZED words in SQL queries. While these key words are not case sensitive, it is still common practice for people to capitalize them in a query. Things like SELECT, FROM, WHERE (and a few others) are almost always capitalized and you will see it in examples throughout this user manual and other online references.

Examples

SELECT Firstname FROM Contacts WHERE Lastname = 'Smith'
INSERT INTO Contacts VALUES ('Joe', 'Smith')
UPDATE Contacts SET Lastname = 'Howard' WHERE ID = 1
DELETE FROM Contacts WHERE Lastname = 'Smith'

SQL Statement Examples

SELECT Command

The SELECT statement is used to select data from a database. The result is returned as a data set, called the result set. This is true even if there is only one value returned.

Examples

SELECT * FROM Customers
SELECT Name FROM Customers
SELECT Name, Address FROM Customers

UPDATE Command

The UPDATE statement is used to update existing records in a table. If a WHERE clause is not used, all rows in the table will be updated.

Examples

UPDATE Customers SET Name = 'Inductive Automation'
UPDATE Customers SET Address = '2110 21st Street' WHERE ID = 1

INSERT Command

The INSERT statement is used to insert a new row in a table. If any columns have default values or are auto-incrementing, they can be omitted from the INSERT query.

Examples

INSERT INTO Customers (Name, Address, City, State, Zip, Country, Phone)
VALUES ('Inductive Automation', '2110 21st Street', 'Sacramento', 'CA', '95818', NULL, '800-266-7798')
INSERT INTO Customers SELECT * FROM OtherCustomers

DELETE Command

The DELETE statement is used to delete records in a table. DO NOT EVER run a delete command without a WHERE clause. It will delete ALL records from that table.

Examples

DELETE FROM Customers
DELETE FROM Customers WHERE Name = 'Inductive Automation'

WHERE Clause

The WHERE clause is used to extract only those records that fulfill a specified criterion. Operators Allowed in the WHERE Clause are as follows:

= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN If you know the exact value you want to return for at least one of the columns

The AND & OR operators are used to filter records based on more than one condition. If you don't use parentheses to group clauses, AND takes precedence.

Examples

SELECT * FROM Customers WHERE State = 'CA'
SELECT Name FROM Customers WHERE Address LIKE '%St%' AND State = 'CA'
SELECT Name FROM Customers WHERE Address LIKE '%St%' AND (State = 'CA' OR State = 'NV')

ORDER BY Clause

The ORDER BY keyword is used to sort the result-set by a specified column. The ORDER BY keyword sorts the records in ascending (ASC) order by default. If you want to sort the records in a descending order, you can use the DESC keyword.

Examples

SELECT * FROM Customers ORDER BY Name ASC
SELECT * FROM Customers ORDER BY State ASC, Name DESC

Joins

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables. Tables in a database are often related to each other with keys. A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.

JOIN: Return rows when there is at least one match in both tables. Also called INNER JOIN.
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table.
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table.
FULL JOIN: Return all rows from both tables, even if there isn't a match. Also called OUTER JOIN.

Example

SELECT * FROM Contacts JOIN Customers ON Customers.ID = Contacts.CustomerID

GROUP BY Clause

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns. This way you can find the MIN, MAX, Average, COUNT, etc. for each group of rows.

Example

SELECT SUM(Duration) FROM Downtime GROUP BY CauseID

In This Section ...