Common SQL Tasks
This section contains examples for items we identified as "common" tasks. When first starting out with SQL in Ignition, many users are looking for examples in order to grasp concepts, or examples to possibly use in their projects. Additionally, this section aims to demystify some of the more complex or abstract tasks that our users may encounter when working with SQL in Ignition.
The examples in this section document several types of SQL tasks that may also touch many other areas of Ignition. While these examples are typically focused on a single concept or end result, they can easily be expanded or modified. In essence, they serve as a great starting point for users new to Ignition, as well as experienced users that need to get acquainted with a new or unfamiliar feature.
Below is a list of common SQL tasks related to using SQL in Ignition.
Filter Data in a Table​
The Filter Data in a Table section explains how using a simple Dropdown box and making some modifications to the SQL query can allow users to dynamically filter data coming into the table to only show a specific subset of data, or all of the data. This section also includes variations of the SQL query, thus returning different filtered results to the table.
Add Data to a Database​
A common method of adding data to a database from within a client is using a Button component that executes an SQL query. This section provides an SQL script showing how to collect relevant properties and insert them into a database, as well as how to setup your components on a window.
Edit Data in a Database​
Editing data in a database is very similar to how we add data to a database. Users can edit data within a table on the screen in realtime, and the changes are then pushed back to the database table. This section goes over the different ways to edit data in the database depending if you're using a Table component or Power Table component.
Refreshing SQL Data on a Component​
Sometimes, it may be unnecessary to have a table constantly updating and requerying the database for data. By using the system.db.refresh function, we can turn Polling "Off" on our query, and have a button that allows us to manually update the table with new data when the Button is pressed. Alternatively, we can add the system.db.refresh function at the end of a script to refresh the newly entered data automatically without pressing a button.
Editing Multi-Selected Rows from Table​
Tables have the ability to select and edit multiple rows at a time. Oftentimes, this can be used to manipulate multiple rows simultaneously, such as deleting all of the selected rows at once.
Storing PDF Files in a Database​
The database is a powerful tool and can allow you to store files such as PDFs in a database table. The Storing PDF Files in a Database section explores how we can take a PDF file and store it in the database table, as well as how to pull it back out and display it in the Vision - PDF Viewer component.
Simple Database Editor​
It may be beneficial to have a frontend to a database table built right into an Ignition project that allows you to control the data in the table without having to go into the database's built-in frontend, such as MySQL workbench. In the Simple Database Editor section, you will learn how to build a simple database table editor on a window that can add, edit, and delete data from the database.