SQL Bridge Module: Bridging the Gap Between SQL and PLCs

Often called a “Swiss Army Knife” for efficiently moving data between PLCs and SQL databases, the SQL Bridge Module leverages Ignition’s native database connectivity to map data to any tables you want, in any format that you want. The SQL Bridge Module acts as a transaction manager for logging data with additional context, calling stored procedures, and synchronizing data bi-directionally.

Transcript:

00:06
Travis Cox: The SQL Bridge Module allows you to move data between PLCs and SQL databases with ease. Think of it as building mini-logic engines that control when and how data moves between the two. With it, you can store atomic structures or event data to the database, you can build a simple recipe or batch system, sequence an entire process, interface with stored procedures, and a lot more. There are a ton of possibilities with this module. The module adds transaction groups to Ignition. A lot of people think that you need SQL Bridge to connect to SQL databases, but in fact, the Ignition platform provides database connectivity. SQL Bridge leverages that so that we can move data between PLCs and databases.

00:42
Travis Cox: SQL Bridge is different from the Tag Historian Module in that it puts you in control of the format of your data. The Tag Historian Module was specifically designed for time series data. It automatically creates tables in your database and logs each tag independently. With SQL Bridge, you can map data to any table that you want and in any format that you want. SQL Bridge allows you to log data with additional context, and that's perfect for events that happen in your system.

01:05
Travis Cox: There are four types of transaction groups in Ignition. First is the Historical Group, and that allows you to log basic history to a database table, inserting one row at a time. Next is Standard Group, and that is the most flexible group and allows you to insert a row, update a row, or you can synchronize values between PLCs and databases. Next is the Block Data Group, and that allows you to log blocks of data in a tall format in your database. And lastly, is the Stored Procedure. With Stored Procedures, you can map data as inputs and outputs to your stored procedure in the database. Transaction groups are really simple, and when they execute, they only do one thing, and that could be to insert a row in a database, update a row, or synchronize values.

01:41
Travis Cox: SQL Bridge is extremely reliable and is often called the Swiss Army Knife for Ignition. With SQL Bridge, you could define your own schema or database tables, or you can even let SQL Bridge create them for you. Each transaction group allows you to map tags or other data points as inputs and outputs. So, for example, you can take 10 tags from a PLC and you can map them to individual columns of a wide table. Transaction groups execute either on a timer, cron schedule, or trigger. For example, you can run a group every five seconds performing some logic, on the hour, every hour, every day at midnight, once on the rising edge of a bit in the PLC, like when a machine goes down, every five seconds while a value in the PLC is high, like during a downtime event, every time a tag value changes, and when the temperature gets above a certain point. The possibilities are endless.

02:29
Travis Cox: Let's go to four examples of how to use SQL Bridge Module. First, we're going to show how to log basic history every 10 seconds. So we simply bring some tags in from the PLC, set the rate of the group to 10 seconds, specify our database, specify a database table we want to log to, like basic_history, enable the group, and now we're logging data every 10 seconds. The second example is showing how to log history off of a trigger. Same as before, we have tags in the PLC, but now we have a trigger. We want to execute this group when that trigger goes to one. So here we're running the group every second, going to the MySQL database, and a new table called event_history. Now on the trigger tab, we're at run this group when that trigger goes to one, we're only doing it once, and we're going to reset that trigger after execution. So if we enable the group and save it, we can see that when we turn that value to one, a row gets added to database.

03:14
Travis Cox: The next example shows how we can load recipe values from a database down to the PLC. So we bring in the tags. We want to write those recipe values too. We're going to use our MySQL database. We're going to go to a table called recipes, and we're going to select a row where the recipe matches the name that we want to load down. The group is going to run when that recipe name changes, it's going to go find that row in the database, and it's going to take all those values we've mapped to the tags and write them down to those tags. Every time the recipe changes, we're going to get those new set points being loaded.

03:44
Travis Cox: The last example shows how we can bidirectionally synchronize PLCs and SQL databases. Here we can bring in our tags. We want to synchronize to the database. We have our MySQL database selected and our bidirectional_sync table. When we execute this group, if a change happens in the PLC, we write that to the database. If a change happens in the database, we write that to the PLC,5 and it automatically synchronizes the two. Try the SQL Bridge Module today by downloading a free trial of Ignition.