The SQL language is used for selecting information from a database. It can be used in a variety of ways, but most of them will either make modifications to the database or return a set of values (with a few notable exceptions like Stored Procedures). The majority of users will be returning large chunks of data into a Table or Report in Ignition. This means a complete dataset will be returned based on a user selection, a time range, or any combination of factors.
How Can I Tell If I Should Be Using SQL Syntax?
Typically, the words Query and Database appear in the interface somewhere. Additionally, there is usually a way to specify a Database Connection.
Where Is SQL Used in Ignition?
Below is a reference of the most common areas in Ignition where SQL queries may be used.
SQL in Python
SQL queries can be called from a Python script. There are several system functions in Ignition that allow a script to run a query against the database, such as system.db.runPrepQuery. This is a more advanced technique, as you need to adhere to both language's syntax. Furthermore, when typing a SQL query in a Python script interface, the syntax highlighting can not help with the SQL portions. The syntax highlighting in a Scripting Window is only looking for Python syntax, not SQL.
In cases where you plan on calling a SQL query from a Python script, it is highly recommended to write the query in the Database Query Browser first (substituting parameters with static values for testing purposes), and then move the query over to the script once the query executes successfully on its own. This approach can save you some time troubleshooting, as there will be less ambiguity when an error occurs since you know the query runs.
Below we see an example of calling a SQL query in a script. Line 5 creates a variable called "query", and assigns it a string consisting of a prepared statement (using SQL). The query is then executed with the system.db.runPrepUpdate function.
For more examples of using a query in a Python Script, check out the system.db.* functions.