12 Ways to Use PLCs & SQL Databases Together

Speakers

Travis Cox

Director of Training

Inductive Automation

What if there was an easier way to move data between the plant floor and the rest of your enterprise, specifically between PLCs and SQL databases? It would open up many opportunities to improve efficiency, data storage, synchronization, uptime, data insights — and the list goes on.

The Ignition SQL Bridge Module is made for this exact purpose. As its name suggests, the SQL Bridge Module acts as a conduit between PLCs and any SQL database. For years, companies have used it as a “Swiss Army knife” for a variety of industrial data management needs.

Inductive Automation Co-Director of Sales Engineering Travis Cox discusses 12 of the many powerful uses of the SQL Bridge Module. You’ll not only learn a dozen ways to use this versatile tool, you’ll also be able to think up other exciting ways to apply it in your enterprise.

Learn how easy it is to:

• Add contextual data to historical data
• Synchronize PLCs through a SQL database
• Sequence products on a line
• Map PLC values to stored procedures in a database
• Manage recipes (demo included)
• Track production 
• And more

Related content:

• Blog post: 12 Powerful Ways to Use PLCs with SQL Databases - Part 1
• Blog post: 12 Powerful Ways to Use PLCs with SQL Databases - Part 2

Transcript:

(The moderator, Don Pearson, briefly introduces the topic, Inductive Automation and Ignition software, and then introduces the presenter, Travis Cox.)

Travis: Thank you very much, Don. Hello, everybody. I'm very happy to be here today to show you the power of the SQL Bridge Module for Ignition. And just a little of background myself, I've been with the company since the beginning as Don mentioned there, and I have seen the evolution of our software from the very first product, which happens to be the module we're gonna be talking about here today, to what we have here now with Ignition and the platform. So today I really wanna give you a good snapshot of just the power of one of the modules in particular which is the SQL Bridge Module. Before I get into that, a lot of people come to our website or look at Ignition as simply just HMI, SCADA, and MES applications. And in fact really, when you really discover Ignition and look more about it, there's so much more that you can do with it. If you think of an iceberg, at the top of the iceberg is what people think of as far as MES and SCADA and an IIoT. But if you look under the iceberg, Ignition can do so much more than that. It's been successfully used for other things like full alarming systems, reporting, MQTT server and clients, logic engines, dashboards, ERP middleware, web services, database front-ends, OPC UA servers and clients, and the list continues to go on and on.

Travis: And we've seen our customers use Ignition in a variety of different ways and some that are very innovative with what you're able to work with. So Ignition has vast possibilities beyond the scope of simply HMI, SCADA, MES. And what we're gonna do here today is talk about some of those possibilities in particular with our SQL Bridge Module. So we're gonna focus today's webinar on the SQL Bridge Module for Ignition and this is one of the modules that deals with moving data between the PLC and a database. A little bit of the origin of the SQL Bridge Module, it started out as a product called FactorySQL, that was our legacy product. In fact it was the very first product that we developed here in Inductive Automation. Our CEO, Steve Hechtman used to be a control systems integrator in Sacramento and was constantly dealing with problems with systems that didn't connect, that weren't able to put data into a SQL database, weren't able to get the results or the analysis that we're looking for from these systems with the software or the technology that was out at the time.

Travis: Steve wanted a better way to move data from the operation side to the IT side. So he came up with the idea of moving data between PLCs and SQL databases and to get to the PLC is where you standardize on OPC. So we're talking about SQL databases here. So it could be a MySQL, Microsoft SQL Server, Oracle, Postgres, and others where we keep the data in a very open format that can also be interoperable with other systems especially in the IT side.

Travis: So, Steve and a small team of software engineers developed the tool, FactorySQL that can move the data between the PLCs and the database. It really became Steve's Swiss Army knife as integrator and gave him a lot of freedom to solve his customers' problems. And we're gonna talk about some of the things that you can do with that module here today. So eventually, we started inductive automation, and we offered FactorySQL to other integrators and since then it has become an essential part of the Ignition platform as the SQL Bridge module today. So our legacy software had been moved into the platform as modules.

Travis: Now, before I get into the SQL Bridge module in particular, there's always a lot of questions about these two modules when you look at the software stack. And that is the SQL Bridge module and the Tag Historian module. And they're both very important modules for Ignition and they perform different functionalities and there's significant differences between these two modules.

Travis: So, I wanna clear those up before we actually get into SQL Bridge, in particular. The Tag Historian module, let's focus on that one first, that was created primarily for trending of your process values, is an easy way to log your tags into a SQL database, to then simply view a graph or to summarize data in table form, to bring it back. And we've done a lot of work to keep that very efficient, easy to configure, especially in the sense that we're using SQL databases. A lot of our customers don't know much about databases, so this makes it very easy for them to get up and running. By comparison, the SQL Bridge module, that module is our... We like to call it Transaction Manager, is a way of moving data between an OPC server or PLC and a SQL database. And we can move it any way that we want, either on a schedule, on a timer, or on triggers.

Travis: So they're really... A big difference between these two modules, the SQL Bridge module can perform a lot of functionality, whereas Tag Historian is focused on trending, it's focused on being easy to use in that regard. So if you look at the two sort of putting them side-by-side and doing some comparisons, maybe pros and cons, on the Tag Historian you're not in control of the database schema.

Travis: The data's in an open format in a SQL database, but we control the schema, and we do data partitioning. So we actually separate data into separate tables, the database, and that is just to maximize performance of a SQL database. So Ignition is taking care of that fact, where customers don't have to worry about knowing much about databases. We log data as it changes, we use algorithms to sort of do some compression in the fact that we don't log data unless we actually need to, and when we do interpolation, we bring that data back. There's really no knowledge necessary of the SQL database or the SQL language, in particular. And we do lose a little bit of data context, we just simply log each of the tags individually to a database. Now, if you look at the SQL Bridge module, in particular for historical logging, you can control the database schema, you can put the data into any format that you choose, you can create your own tables, you can map data into store procedures, or any other format that you would like to.

Travis: Now, there's no compression or data partitioning so we're not gonna take advantage of some of those features but we're putting more of the power into your hands. So the more you know about databases, the more you can use the SQL language, the more that you can use database techniques to get more powerful analysis out of the information that you have, and to also provide more contextual data.

Travis: And like I said, the SQL Bridge module, they do things off of schedules and timers and triggers and so it really is a great way of doing event logging, which we're gonna see a lot about here today. So hopefully, that gives you a pretty good sense of the two modules. They are most certainly not mutually exclusive. A lot of customers will use them together in their applications, because there are scenarios where you would use each one of these.

Travis: So today, though, we're gonna focus on the SQL Bridge module and we're gonna show 12 use cases for the module and how we can move data between a PLC to database, and we're gonna go through each one of these one by one and at the end, we're actually gonna demonstrate one of them, which is the recipe management. So you can actually see how we can configure what we call a Transaction Group in Ignition to be able to perform this logic.

Travis: So we're gonna start with history with context, move in to how to synchronize two or more PLCs through a SQL database. We're gonna show how we can interface with barcode scanning applications, how we can do sequencing, mapping PLC values to database stored procedures, creating a scheduler, recipe management, like I said, OEE implementation and OEE downtime in a simple form, not doing crazy amount 'cause there's a module for that, but in a simple form of doing how to do downtime in OEE, production tracking, handshaking with PLCs, putting real-time data control with third-party applications and lastly, ETL tools.

Travis: So without further ado, let's go through each one of these and if there's any questions along the way, please feel free to put them into your Goto webinar console. We can try to get to them during, if we don't, we'll get to them at the end, of course, after the demonstration, we have plenty of time for questions. So in the first use case is history with context. So as I mentioned, the Tag Historian module is excellent in collecting historical data from each of your tags and the SQL Bridge module is perfect for collecting the contextual data. So here we can actually bring in not just the process values, but we can bring in external environment variables such as the operator that's running the machine, the work orders, the product codes, and many other things that we can log in the database in the same table, in the same row as other values.

Travis: So effectively putting the context to the database. We're storing more data typically to have that 'cause we're storing a row of data at a time, but we can store anything we want in that row. The Tag Historian can only log each of the process values by themselves or individual.

Travis: And so it's very common that when there are PLC events happening that we use a trigger to log some contextual data like when it started, maybe when it stopped, who is the operator writing the line, what was the work order, what was the product code? We put that in the database into a separate table, then when we want to query it, we can see all the events that have happened, and then we can relate that to the process information through the Tag Historian. We can get back to the graph of the data between those time periods. So it really allows us to bring in that context, to store other values in the same record as our process values. If you'll look at sort of a graphic of this, we have, of course, in the PLC, Ignition can be communicated to that, we can bring logging the historical data from the historian into a SQL database and we can be doing that in parallel.

Travis: And then with the SQL Bridge Monitor, with the transaction group, when certain events happen, we can log them separately and we can relate the two together when we bring it back on to a display. So what's important here is that you understand that we can bring in more when we're putting records into a database more than just the tags, we can bring other information and commonly that would come into the form of maybe memory tags in Ignition, things that people are entering in when they're using the application that we can put into the database and then bring that back along with the historical data that we're collecting. So this is a perfect example of being able to use the two modules together but the SQL Bridge is important for logging these events when they're actually happening, storing that into our special tables.

Travis: Okay, the second example that I wanna talk about here is synchronizing two or more PLCs through a SQL database. And in this case, Ignition can leverage the power of the database to perform the synchronization. And we can have many different PLCs, they can be different brands and protocols of PLCS and if we need to synchronize a value from one PLC to a value in another PLC and vice versa, a SQL Bridge module is perfect for doing that. Now if we look at a diagram here and how this really works, so here I've got PLC 1 and PLC 2, they could be different brands, they could be different parts of the factory, doesn't quite matter. They're not connected whatsoever, but Ignition can't communicate to them individually, so we have drivers to look at those. So we're communicating to them. We can use the database as a center of our model here. We can have a transaction group, that's what we use in the SQL Bridge module, we can have it looking at both of the PLCs, and if a value changes in PLC 1, that then gets synchronized to the database which the database will see that change happen which will synchronize it to PLC 2.

Travis: If a value changes in PLC 2, we'll synchronize it to the database. We see a value change in the database, it'll go down to the value in PLC 1. So we can effectively use two different transaction groups to synchronize two different PLC values bidirectionally here by putting the values in the database, using the database as middle of the model. And we have many different modes on this for who's gonna win, if it's the database is gonna win or PLCs are gonna win, if we restart the system, or if we lose communication, but we've done a lot of work to be able to make this possible where you could easily synchronize the two and there's a lot of applications out there where you have these islands and they're not connected in the larger control system and it could be costly to maybe put a control logics to connect all these things together in the middle and then do the programming with that where here in Ignition, we can use the single SQL Bridge module, the transaction groups to perform this, the synchronization or move data around at different times. Now here, we're just doing it when these values change.

Travis: Another form of synchronization that a lot of customers will do with each transaction group, is synchronizing the clocks across all the different PLCs. PLCs don't have... They're not communicated to time servers. And so when you're switching from daylight savings and to Standard time and other situations like that, the Ignition running on a machine can be communicating to that time server, and then it could easily synchronize that across all the PLCs that are there, maybe when the values get out of sync or just continue, on a continual basis, keeping those clocks synchronized is another really good use case for the transaction groups. And I did kind of forget to mention there a moment ago, transaction groups, we call that is the way of performing some logic, moving data between the PLC and database and so we might use one, or we might use many of these, but that is the method of being able to move the data around. So we would use a transaction group here for each of the PLCs, to synchronize the clock.

Travis: The third example here is a barcode scanning and there are many applications that will use barcodes to help identify products or items within a system, and when you scan that barcode, that is an event, that is a trigger that's going to happen, a value is gonna change and a transaction group can be monitoring that tag, when that tag changes, we can perform some logic and we could be checking back with the SQL database in particular to know what to do, maybe we wanna write a value back to the PLC after we've scanned the value, we also may want to log that record on the database saying that hey, we scanned it at this time from this particular machine. So it's a really good case for that. And in particular, if you think about an actual real life scenario is airport baggage handling systems. We have lots of bags moving around different conveyor belts and they have these 360 scanners that are gonna scan that bag.

Travis: When that scan happens, Ignition's gonna see that event happen, we're gonna go into the database and check where that bag needs to go and obviously log the record that, the fact that we've scanned that bag, and then knowing where it needs to go, we're gonna write a value back to the PLC to maybe divert the bag, one way or the other, on the conveyor line. And so these transaction groups are running behind the scenes, they're constantly checking for these different conditions and the interface with databases which is where a lot of the logic, a lot of the information is gonna come from, it could either be a database that Ignition is generating or it could be a database on the ERP side, it could be a database on the MES side, doesn't really matter. We're gonna be working with the databases to know what to do in this situation.

Travis: And in particular, the barcode scanning, there is a module for Ignition called a TCP/IP driver, and it allows us to capture packets that are ASCII packets that are sent over the network and as an event, as a value change and then do something with that. So in particular, we'd use that driver with the SQL Bridge module and the transaction group to be able to divert these bags, as they're being scanned in the system. And of course, we have to do these things really high speed. The SQL Bridge module can run things down to 10 milliseconds if you want to. It can be really, really fast on how we can see these events, and how we can load these values down. Now if we take that example kind of one step further, whereas last time we were talking about just simply barcode scanning, we may have to sequence something on assembly line or on multiple machines, where we're gonna be scanning that thing many, many times.

Travis: And every time we scan it, we're gonna be doing something different. And so we might be keeping track of the database of where that product is, what's happening to it as it moves along that assembly line. So in particular, if you think about an auto assembly line, there are different cars coming down with different options. Some could be cars, some could be SUVs, some could be for Europe, some could be for US. And there's differences in that and the robots that maybe the spot welders have to know where to do the welds is gonna be different for each of the machines. And the MES system typically is gonna have all of the... The cars are gonna be moving down the assembly line and Ignition with the SQL Bridge module can interface with database.

Travis: So when a car comes into a station, maybe there's an RFID tag, we scan what that tag is. We have a value change there. We know what's in there because we can check the MES database to see what that is. We give them based on that, load a value down the PLC as maybe what the robot should do, what the PLC should do in particular, and we can log the fact that once it's complete, we can say it's past this particular station. It goes on to the next one. So we can see the sequence of events happening and we can look at a queue. The database is where all the information is stored, but the transaction group is actually taking that and sending down to PLC automatically what's gonna happen in each of these stations based on what's in that station at that time. Assembly lines are perfect, could be cars, could be big combines, could be anything that we're moving down and performing different actions on it.

Travis: It could also be just different disparate machines that we have that we're doing different kinds of processing on. It doesn't matter. The SQL Bridge module is really gonna be in charge of all these events of loading down the proper value to a database, especially looking into a database to see what it needs to do. Okay, so using SQL databases and PLCs together gives you a lot of power because the PLC doesn't have all the knowledge as to what to do. There's environment variables, there's information in other systems we've gotta use together in order to get the full system. The fifth example here is mapping PLC values to database stored procedures. Now the past three examples I talked about with the recipe management or the scheduler or the sequencer, any of those we can interface with the stored procedure where we can actually map PLC values as inputs and outputs to a database stored procedure.

Travis: There are a lot of times that ERP systems or other databases already have these procedures defined or that you may want to define a procedure because there could be a lot of logic in that procedure that's on the database side of things. And if we have this procedure, it makes it very easy for us to map the R values into it. So you can imagine when a PLC event happens, a trigger happens, we can run a group that goes and sends values to a stored procedure in the database. It executes that procedure and the results of that procedure are then sent back to the PLC so that we know what to do, whereas you're sort of handshaking the PLC with the database, so we can go back and forth there. It's a very commonly done thing for a lot of applications, and it also puts a lot more to some of the logic in the database. So it keeps the Ignition cleaner. We're not having to put all the scripting and things in Ignition. In fact, the transaction groups are really eliminating the need to have to do scripting in Ignition.

Don: Can I throw in a question here, Travis?

Travis Cox: Absolutely.

Don: I think it applies to this one and the one before. But anyway, while synchronizing two PLCs, do you have to manually input which values to monitor for changes or does it monitor all tags or data tables at once?

Travis: That's a great question. I'm gonna show you here at the end when we configure one of the groups. All the tags that we bring into the group, we're gonna monitor any of those tags for changes. If we want to just do one of them we can, or it could be all of them or a subset of them. We don't really care. As far as database, we can monitor all the values in the database as well and in particular record to see if they've changed. And the group is doing all this work for us. It is checking these conditions, and it's gonna execute when we want it to execute. And it's gonna be based on these events, these values changing or it can be based on schedules or timers or other things as well, that is the beauty of it. I'm glad that question was asked because it's very important here that this is happening behind the scenes, the very drag and drop simple thing to configure but all of this complex sort of logic is being taken care of, especially if we're looking at stored procedures. There can be a lot of complex logic that we're putting on the database side, keeping what's between the database and the PLC very simple and but always running there for us.

Don: That's great. Thanks for jumping in on that one.

Travis: So in that regard, we talked about sequencing, we've talked about those kinds of things, but we could also use these transaction groups for a scheduler of being able to send values down the PLC on a schedule rather than being event-based, which we talked a lot about when the values changed in the PLC, when these events happen either in the database or the PLC. Well, that's great, that allows us to do a lot of things. But what if we just have something based on time or outside influences? Maybe it's based on a web service or based on something else. So a scheduler is gonna be really based on time, when certain times are there, we might wanna send certain values down to the PLC.

Travis: And the example that comes to my mind with this is like when you look at sprinkler systems or for obviously massive sprinkler systems where you have to control them and you need to look at the environment. You need to look at the schedule to know when to turn these things on or off, right? And that can very well change based on information we're getting from our instrument data for the weather or what the forecast is for the next few days, but the idea, if you look at this diagram here, is at the edge where we have our PLCs that are next to the sprinkler systems, they're gonna be pretty dumb. They don't really know when we should turn these things on or off. Of course, the system above that's gonna know. So Ignition's gonna know. We're gonna communicate to those PLCs, whether directly or whether you're going through MQTT like we're showing here. It's really a beautiful way to get those remote edge data into Ignition.

Travis: So Ignition will communicate to all these different sprinkler systems and the transaction group can be looking at the schedule that you put into a database, and with all the setpoints, so it's like a red big recipe but based on time and when these events happen, we're gonna load that down. But we can also look at the instrument data and the web services to see the forecast, and maybe know if we need to change that or not write these things down. If it's raining, we probably don't wanna do that, so we can use these other environment variables to determine when we're going to turn these things on or off in the PLC. So it's another case where we can use Ignition as, really, the control system in a sense, where it's looking at these different variables, and gonna load that down to the PLC at the right times.

Travis: Now, there's a couple of questions here on the... That we've entered in as far as list of PLC protocols that SQL bridge supports and the SQL version supported. Those are really good questions, and unfortunately, we don't have the time to go through every single detail here in this particular webinar, but Ignition can talk natively to Allen-Bradley Ethernet set of controllers, control logics, compact logics, PLC5, MicroLogix, SLCs, those kinds of things, DNP3, Modbus, TCP, Omron NJ, Siemens S7, those are all the native protocols built in. And we can also communicate with any third party OPC server, like Kepware, Matrikon or others. So it really gives us the ability to talk virtually to any PLCs that's out there, because of OPC and our native drivers that we have in Ignition. Now as far as SQL database, we support all versions of the database we use what's called JDBC, Java Database Connectivity Driver that can talk to any version of MySql, Microsoft SQL Server, and others that are out there.

Travis: And I introduced MQTT here. I didn't wanna throw something brand new at everybody but that is just the way of being able to let to... Rather than pull in the devices that are at remote locations we can publish it eyes up to a central server as a change, it's still a bidirectional model, but it's the more modern approach than having to do the whole pulling from a central system where Ignition has to connect down, we rather can have it connect up. Just so, I wanna just briefly say that 'cause it is in the diagram here and the PowerPoint will be available for everybody too, so if you have more questions, we can get to that.

Travis: Now that last example was very much on a recipe, but based on time, right? And recipe management is just another perfect example of what a SQL Bridge module transaction group can do for you. I'm sure a lot of you have probably built systems where we put the recipes in the PLC itself. We store them in a big array, the PLC, using the PLC's memory, and we have a finite amount of memory, so we can't store unlimited amounts of recipes. And that really isn't the domain of the PLC. One of the big things that Steve set out to do at the beginning when he developed the SQL Bridge module was to make sure that we make the PLC really good, at the automation of control of the line. And when we actually store things like recipes, it is not a great place to store it because of the PLC somebody could load down to the program, they could wipe out the whole recipes, they can change the values. If we had, if a PLC went down, then there goes our storage of all the recipes, it's not the best place for that. Databases, in contrast, are perfect in storing these recipes. Oh, we can have unlimited amounts of storage because now we have a hard drive, lots of space to store these different recipes.

Travis: Now, those are perfect domains, right? Databases are great for storing information, and PLCs are great automation systems. Well, now we needed something to connect the two together, and bring that information where we can load a recipe from the database to the PLC. And what better way than the SQL bridge module for Ignition. These transaction groups can be triggered when somebody wants it to be triggered, if they want to load a different recipe, if somebody presses a button on a screen, or even if the PLC requests, that I get a new recipe that I can load down. Either of those events can cause a transaction group to run where we can take values from a database and load it to the PLC.

Travis: We are gonna do an example of that last one at the end, so you guys can get a sense of how we can configure this. Number eight here, another example we could do with the SQL bridge module is simple OEE implementation and simple OEE downtime or downtime tracking. Certainly, we have a module in the MES space for more complex OEE and downtime systems, but there are a lot of companies who just need simple ways of collecting the data and simple calculations that we can bring on to screens and it tells us, get going. Keep in mind that the SQL bridge module here costs $1500. Every example that I'm talking about here today can be all done with the one license of $1500, and that's the power that we can provide to an integrator or to an end user with just this one module. So if I need to track downtime and do some simple calculations, well, a SQL Bridge module can do that.

Travis: For downtime, in particular, when events happen to the PLC, we can put a record to the database at what machine that was from, what the code was for the downtime, when it started, and we can update it when it completes. So now we have a complete event log of all the downtime records. And we just simply can log in to our own table. The reason to log into our own table is we can utilize SQL language to bring back the information and bar charts or pie charts so we can do some simple analysis on that data. In fact, I could build something like this in a matter of five minutes, so it is how you get started, it really is a simple way of doing it.

Travis: Also, we have counters in the PLC, we know very simple information for OEE we can have the group calculate the OEE value on the fly and store that into the database either on a time or on a time basis or as values change, whatever we wanna do in that particular case. So what's really important here is that we can log this along with contextual data. What machine did it come from? Who is the operator? What was the manufacturer's raw material? What was the work order, the product code? Because when we do even simple downtime in OEE, we wanna be able to do a little bit of comparison on what is my downtime broken up by machine or what is our OEE for different production runs or different operators and we can have the information stored in that same record. Now, we are, of course, creating our own table, and we're managing that and we're querying it, but for simple situations, it can go a long way with just that one module.

Travis: Okay, number nine here. Another example we can do with the SQL Bridge module is production tracking. So kind of in the same breadth, we talked about OEE in downtime, but if we need to know what products are moving through different machines, typically the PLC has the information scanning barcodes or it has work orders or various things like that. We can take that data and log all these different events that have happened and put it into some tables that we've created that allow us to query it, to see what's moving through each of the machines. As well as we could be logging the OEE in downtime as well as we could be logging the historical data from the PLCs directly. And that gives us all the information we really need put into a SQL database with a little bit of knowledge of SQL queries, we can do quite a bit. So it's a simple way of being able to do that.

Travis: Now, there was a question that came in here from Dan is, how does database disconnect when it's unavailable, handle another systems database calls go away unanswered, the application may freeze. Well, in our case, that's a good question. Talking about production tracking to others. If the database goes down, we do not want this in the freeze. That's not what's gonna happen here. But one of two things could happen. If we're logging historical data or event-based data, we can continue to log it. It'll go through a store of four systems. They'll be crashed until the database comes to backup which case we'll forward over to the database.

Travis: Now, if we need the database to make a decision, as to what we set down to the PLC because it has information in there. And we're not connected at that point. We take advantage of the hand-shaking which I'm about to talk about, where we can send down the PLC a different tag that we failed. In which case you could try again or we can handle that failure in some way. Ignition will give you the tools to handle these particular failures on both sides with the transaction group, which again will show you at the end so that we don't have a system that freezes. That's the last thing that we want to have happened here.

Don: Let me interject while you're moving to the next thing. Just to answer a question, it happened a couple of times, Travis mentioned it, but yes, the sides will be available, the archive will be available, you can see it again, share it with others. You'll all have access to all of these slides after we finish the webinar later today or tomorrow morning at the latest.

Travis: Thank you Don. Alright, so handshake in the PLCs, perfect segue way into number 10, being able to do the handshake whether we're doing around success or failure of a particular action. And this is really simple. We are just going to look for an event that's happening on one side or the other. After that events happen, we're gonna set a handshake either way. Typically, of course, PLC's gonna say, "Hey, we need to log this record." Ignition does it. And then we send the value back to the PLC saying, "Okay we're completed, we're done." So we can advance and move on, and if we fail, we can send a value down to let the PLC know we failed. Maybe you wanna stop the line. Maybe you wanna do something else with that.

Travis: It's very important to keep these two in sync. When you're especially handling some logic, that's going on between the two. So that we don't get into any bad situations, where we don't know where we're at. Hand-shaking is a perfect way to do that. It's a built-in feature to these transaction groups.

Travis: Alright, number 11 here. This is pretty unknown... Not many people know about this one, that we can do it with the SQL Bridge module. That is putting real-time data and control in a SQL database that third party applications can work with. So using a SQL database, MySQL, Microsoft SQL Server, Oracle, we're not the only ones that communicate to this. On the IT side, many applications work with databases there.

Travis: And what Ignition can do, the SQL Bridge module can do is it can actually store values, real-time values, tags change, it updates a record of the database saying, "Here's the latest value, here's latest value." And then that way, a third party application maybe in the IT side can query that simple database table and see what the latest value is of our PLC, without having to know the PLC drivers themselves. 'Cause that's what Ignition does. They don't need to know that, but they need the data they display on their systems, their dashboards or some other application that has no idea what OPC is. We can put values into database, real-time values that we can easily see in these different systems.

Travis: Now this direction can also be bi-directional, in that we can have these systems write values to the database which we can then load back down to the PLC. If you look at this diagram here, this really shows the picture; database being in the middle of the model, Ignition of course working the database and the PLCs or putting records into their... Putting information into a database. A CRM, ERP, other IT system, can create the database, can see that value, we can look at web pages, digital signage, whatever it might be that we need to interface with, and it's seeing the values as they're changing the database.

Travis: If you want to allow certain tags where they can change via database, we can then see that change happen to database and load down to PLC. That's part of the synchronization that the SQL Bridge module does with the PLC database. And again, I'll show you a mode where we make that actually happen. So one common case that happens is simply they like to put this information into a webpage, a simple HTML webpage outside of Ignition. Ignition certainly, its web app, its web-launched clients, it's simple to get those clients into people's hands, but we may have a need for these third-party applications to get access to that data.

Travis: Alright, and the last example here before we go through a real example of configuring Ignition is ETL: Extract, transfer, load tool. We see a lot of customers use the SQL Bridge module in data warehousing and that we can run these things on schedules or timers or in various events happen. And when... In particular, we could actually collect data from different systems, we can parse it, put it into different databases, we can put it into the right format, map it into the right format, we can load values to the PLC if we need to. We can use store procedures to perform a variety of different functions, whether that is the actual parsing of a file if we need to.

Travis: But it makes the logic simpler in Ignition where you're gonna use a lot less scripting, wanna keep it simple. Again, we do not want to use scripting with a SQL Bridge module. That is not the goal. It's very much a tool that anybody comes into and sees how it was configured. So let's go into an example here of how we can configure these things in Ignition. And I figured out I would spend a couple of minutes because the SQL Bridge module might be pretty brand new to a lot of you here, and that you've probably never... Maybe you've never seen it or you don't even know that it can be used for some of these different scenarios.

Travis: So when you install a SQL Bridge module, in the designer for Ignition, you get an area called transaction groups, and this the groups that can perform the logic of moving data between a PLC and a database. We can determine what the flow of the data is going to be. If I right-click there are four types of groups in Ignition. There's a standard group where we can do a lot of different things. It's a very flexible group. There's a block data group where we can deal with blocks of data, so rather than deal with one row, we could deal with multiple rows and multiple columns.

Travis: We have a historical group, to do basic logging of history, so event-based or whatever might be, and a stored procedure group where we can map values of inputs and outputs to a stored procedure. Those are the four that we're dealing with here. So if I look at any one of these groups, for example, if I look at the standard group, I'm gonna call this recipe load, since we're gonna do an example of loading a recipe. But on the right-hand side over here, these are the configuration of that. On the action tab, we can run a group on a timer or a schedule, and the schedule we could do things like I wanna run 8:00 AM and 9:00 AM or I wanna run from 8:00 AM to 11:00 AM, and at a particular rate in-between that, we can really determine what we want that to be, again, based on the time of our schedule.

Travis: And if we want to do triggers, there's a trigger tab here which we're gonna get into, and so how we can do things based on these events that are going to occur versus just time. And a lot of configuration here, but ultimately, we can configure what database we wanna go to and the flow of the data, the mode, PLC to database, database to PLC or bi-directional, those are the modes that we're dealing with. In the middle area here we can bring in all of our tags, and we can have them be inputs or outputs. We can also do expression items which are calculations here. We can actually utilize... And if I do an expression item here, the Ignition's functions set what we have to do calculations and we can bring those values into databases very easily or map them back to the PLC. So all these items can be easily mapped as inputs and outputs to the database, or inputs and outputs to the PLC. That's really what we're dealing with here.

Travis: So we're talking about a simple recipe example. What I have in my database, let me go up here to tools, database, I've already configured a table in database called recipes. And I have, right now, three different recipes with the name, it has five set points. Here's one, two, three, four and five of different data types there. Those are my recipes that I'm dealing with there. Okay? So with these five recipes, we wanna be able to take some of these values and load them down to the PLC. So I have some tags over here. I have a recipe name, and set point one, two, three, four and five. So all I gotta do is simply take these tags and drag them into the transaction group. Now I have items that can either be inputs or outputs. Before I do anything more of those, I wanna go over here to the right-hand side, and I want to say I wanna go from the database to the PLC. So I'm gonna go that direction, so we can load values down to PLC.

Travis: I'm gonna go from the recipes table in the database, and I'm going to simply select, since I'm going from the database to the PLC, I'm gonna select the row where the name, the recipe name is equal to the recipe name tag. So when this tag over here, recipe name changes, when I change the name that means I'm gonna load a different recipe down, we're gonna go grab the recipe from the database and send down the values to those five tags, okay? So it's gonna go and grab the record where that name matches. If you wanna do the ID of the recipe instead, you could do any of those, that doesn't matter. So that's the first configuration here, we're just simply taking the tags from, that we're gonna load down to, go from the recipes table, database to the PLC. Now we're not quite done 'cause I don't want this to happen every one second. I want this to happen only when the recipe name changes. So I'm gonna run the group every second, but it's gonna be triggered.

Travis: The trigger is gonna be either a single item where we can look at a value or we can see any other values change. So if I can look for all tags changing or a custom set of tags, I'm looking for just a recipe name, when that tag changes, it's going to run this group, and send values down to the PLC. At the bottom here, you can see that I can write handshake on success or on failure. So if I had to write to the PLC saying that it's worked correctly, I can do that to a different tag, or if it failed, I can write that to a different tag as well. Keep it very, very simple here. Okay, so I've got the configuration done. Last thing though is each of these tags has gotta be mapped to the right column in the database.

Travis: So I'm gonna map the recipe name to the recipe name column and then SP1, this is already mapped to the proper SP1, 2, 3, 4 and 5 in the database. So these are gonna be outputs writing things down from the database here to the PLC. In fact, actually, the name, I can actually make this read-only since it's just used to determine which recipe to get the information from. So either way you wanna run that, we can.

Travis: Now I'm gonna go ahead and start this group up and save my project. And group is running. So there's nothing to load at this point because we haven't changed the recipe name tag. So notice that when I go in here and say, I want recipe AAA and press Enter, it's going to go and grab recipe AAA from the database and you can see that SP1, got 15.640 true code AAA and 19.6 and execution here went to one. You see, it's not executing anymore because it's waiting for the value to change. If I go here and set this to recipe BBB, Enter, I'm gonna see now BBB you gotta load it down to these tags here, and I can see the execution count went to three. Very simple in how it's moving the data from the PLC database. Now, what we typically do is have a screen where they can interface with this. Maybe here I want a drop-down list and I'll keep it real simple where I can select the name from the recipes table. I'm just gonna bring them back, all the names.

Travis: And so if I look at this I can see recipe AAA, whatever. In fact, I will actually then take the recipe name tag, and I will make it, I'll link it to the selected string value, if I drag it down bi-directional to that. Now, if I go in here I can select CCC and you'll notice that it loaded that down, tags changed here. So in fact, we can take these tags, drag them in here. We'll drag them one by one as some numeric label, as a numeric label, as a multi-state indicator, as a string, and lastly here as a numeric label, and we'll just put them into a vertical alignment there so I see the tags. Okay, so now I'm gonna go back up here and set this to AAA. You're gonna notice that's going to write the tag. This group's gonna change all the values down to PLC along with the handshake on success or failure.

Travis: So, very simple to start working with these. The group is gonna perform that logic behind the scenes and we can do a lot more than... And everything, every example we talked about here can be done using the same idea. We might use one or more groups to perform that logic, that allows us to move that data around very effectively. So, to reiterate, Ignition is an incredibly powerful platform that allows you to do a lot with your data, and these 12 examples are just things of just this one module, SQL Bridge, on just the versatility of that module in particular. And I'm happy to show anybody who has questions or want more deeper dives and demonstrations on what we can do with this module in particular.

Travis: So, I want you to just keep in mind that this module only costs $1,500. You can do a complete scheduling system for your sprinklers for $1,500. You can get a complete rescue management system for $1,500. It's very versatile with that. Now you might want a frontend, which is the Vision module, but you get a lot for just that one very low price, and it really is that Swiss Army knife that Steve was talking about. And we're really happy here to be able to demonstrate this for you guys here today. Hopefully, you got a lot out of it. Like I said, we're gonna go into Q&A, and when we could do more demonstration of these at a later point. Don, back over to you.

Don: Thanks, I know you covered a lot of material there. I'm gonna just say a couple of things while you get a chance to look at the queue of questions here if you want to see what's going on and what you might wanna do. The first thing is that, hopefully, we've piqued your interest to give Ignition a try. If you're new to Ignition, you can certainly try it out by just going to inductiveautomation.com and download the full version of Ignition, it's free. You got the full version. And as to our time out, just reset it. Designer never times out. So actually, you have the opportunity to be up and running in three minutes and design a full system without ever having to buy it. And if you... And also, just one other thing I wanna say before we do, is I know Travis covered a lot of material, but we have Inductive University and it has a tremendous amount of information, not just on SQL Bridge or Tag Historian, but on the entire utilization of Ignition.

Don: You can earn a full credential in it. There's 600-and-some videos there. We did it because we really wanted to emphasize on knowledge transfer. As I mentioned at the outset, we got folks doing the projects in 100 countries. We want the data made available so you can learn. So please go there, take a look. It's easy to sign up and start your own learning.

Don: So with that, I think we're gonna go to the Q&A. And as Travis mentioned at the outset, we'll get to as many questions as we can, if we don't get to all of them 'cause you guys have quite a queue of them, then we'll answer them afterwards and get back to you on that. "How many transaction groups can be created? How many tags of each transaction can... How many tags can it have?"

Travis: So that's a great question. You could effectively have thousands and thousands of transaction groups in Ignition. The key is really when they're gonna execute. If you're running all of your groups every second and they're executing every second doing some action, then you might not be able to have as many as if they were triggered, meaning they're only gonna happen when certain events happen out there. And that's a more common case for transaction groups anyways, where we have these things on events, and there's systems out there with 5,000-10,000 of these transaction groups and they can all be running quite fast.

Travis: You can have all of them set to 50 milliseconds, but of course, when they're triggered on the event to actually happen. So, the amount of tags we can use inside of them... An Ignition server can handle roughly around 500,000 tags before you wanna think about having dedicated IO or other servers. So you can have a lot of tags on a single Ignition server for sure.

Don: Great, thanks. Next question: "Any limitation in the number of PLC devices or PLC variables?"

Travis: That's a great question. I mean, like I said, we can have lots of tags in Ignition, but there's no license limitation on how many tags or transaction groups or anything that we can have. That's really gonna be based off of the hardware that you're running it on. Ignition is an unlimited licensing model. We can have as much as we want. So it's just about what we could actually push through on that machine.

Don: Great. Another question here, "Can the Bridge use both SQL and integrated security AD accounts to connect to the database? If integrated, can you define a service account or the service process, service/process to run under that and pass through in the connections?"

Travis: So, yes and yes. We can certainly interface the connection, the database connection you configure can be set up to use the AD accounts. Typically, what you do is set the Ignition servers to run under that account and it will be able to connect automatically. You don't have to really do anything. So, a couple different ways you could make that happen there, for sure.

Don: Alright. I know we don't have too much time. We got a couple more minutes. So why don't we see if there's a couple more questions you may wanna take a look at and try and get to them if we possibly can? So, here's one: "I have a UDT structure of about 600 devices. Each one has two tags inside UDT. They're dedicated to alarming. How can I import all the tags at once?"

Travis: So, if you're talking about importing into a transaction group, groups can actually be imported and exported through an XML format, which allows you to actually utilize outside to convert, to basically make that XML, we can easily then just right-click imports. You don't have to define that by dragging and dropping every single one of those. There's also the same idea for tags, we can import/export through an XML format as well. So we can make it pretty easy. We can use scripting to kinda easily just generate the file that we're gonna bring in. It's a common case that we do, we have a lot of these... They are similar, just with different variables being changed.

Don: José can't believe you. I see this question, "How much again was that cost? You wanna repeat that, Travis?"

Travis Cox: It's $1,500.

Don: Jose, $1,500. That's SQL Bridge. That's the answer to it. "Can the product directly read Modbus TCP/IP databases from Schneider Controllers?"

Travis: So Modbus TCP, Modbus RT over TCP, those drivers are Ignitions. So, if it conforms to those, that there's a lot of settings with each Modbus device can be somewhat different. We have a lot of settings in there, but absolutely, it's a Modbus driver, it's a pretty generic driver, it allows you to connect to a variety of different PLCs.

Don: I'm just gonna read this one. "How can I take a number from the PLC and average it over a day or hour and output to a database, also looking to output minimums and maximums to a database?"

Travis: So the transaction group can be running all day long. And then we're not actually gonna execute, meaning store the records or database until maybe the end of the day, or whatever it might be. In fact, there are special modes of items in there to calculate how long it’s been one, number of seconds or how many transitions it's happened, and we can, for averages in particular, we can constantly be taking the value that we have, the previous value, the new value, getting the new average, just holding that in memory until we write that to database, or we can have it continually update the database throughout the day as the average is being changed, which is the method that I would prefer to use.

Travis: So it's continually getting the average putting in there so we can see a live one, and then at the end of the day we have our record. Then the next day, a new record gets added, we do the same thing to that one. So, commonly in that case, you have two groups, one inserts records every day at a particular time, the second one that actually updates that with the values as it goes along that day.

Don: That's great. Here's a question that has to do with SQL Bridges working with other Legacy HMI platforms. "If I have customers who are currently on a different HMI platform, can I run SQL Bridge as a middleware module?"

Travis: Absolutely. In fact, we have a lot of times, a lot of customers where they have other HMIs where they have panel views online. And panel views won't interface with the PLC, but they wanna be able to use the recipes in the database, the write example that I used earlier. And we could do that. Ignition can talk to a database. It'll be working with that. Imagine with panel view, if you wrote…

Travis: If you said, "I wanna write this recipe," specify a name in the panel view which writes to a tag, Ignition sees a tag change, we go to database, we get the other values on that to the PLC. Now we're using the existing system along with Ignition and the full power of both. It's very commonly put in parallel on the side, where we could add the value there, but work with or integrate with other systems that happened to be around, either on the operations or IT side.

Don: That's great, Travis. I wanna make sure I at least get this question and this is about a particular industry, in this case, water/wastewater. "How can this system be used in a water or wastewater plant? Has it been used there?"

Travis: It most certainly has been used. The historian logs the values as data changes, it has some compression, and there are a lot of regulations in particular that say you have to have a value logged on the database at a particular time for us to be able to do compliance. And water/waste water, it's very much the case, but we only need to do it every hour or every day. We don't need to do a long, long the time. So a transaction group can be scheduled on these particular times and we can log a guarantee or records get printed to a database with those values. We're not doing rescue management or anything like that, but we can guarantee that we have these points so we can easily generate reports of that data later on.

Don: Okay, good. "Just what is the recommended current version of SQL, MySQL, Full SQL, what version? Any thoughts on the SQL side of it?"

Travis: It's a great question. All the databases are really good these days. They compare pretty equally in performance. It really comes down to the tools that I think you're most comfortable with. If you know MySQL over SQL Server, I would prefer to use that tool. If you're better at SQL Server, then heck, why would we wanna change that? So it's really about what you're most comfortable with. If you've never used databases before, I would certainly recommend something simple like MySQL. It certainly can be very robust, it can handle large amounts of systems, but it's easy to get going with, easy to install just like Ignition. But we have customers who have IT departments that do Oracle, any of these will work. It's just a matter of I think what's easiest for you to manage and work with yourself.

Don: Great. Thanks, Travis. Just time for maybe one or two more quick questions here. "Can you configure a transact group without a Vision module license, or you're talking about the SQL Bridge module?" So what's this answer?

Travis: Man, okay, yeah, I apologize for using the Vision module in my last example, I was just showing how we could put that onto a screen interface with it. You do not need a Vision module to do everything that I talked about here today. You just simply need the SQL Bridge module. It's a behind-the-scenes module. It could do all of that stuff, moving data between the PLC database. The Vision module is only if you ever wanted to create a screen or client. I just want you to see it on the screen here, it's easier to see. But no, you do not need that module to use to do everything we talked about with SQL Bridge.

Travis: Alright, great. I do know we're at our hour. There is still a queue of questions, so I just wanna assure all of you who still have questions in the queue, that they are going to get answered. We are... Travis talked as fast as he could to try and get to as many as he could, and we will get your questions answered. As you can tell, pretty excited about the SQL Bridge and what it's capable of doing, and to be able to show you a dozen different views on how you could use it today was certainly a pleasure from our side to have Travis do that. If you have other questions, you can certainly ask him also. You all get a copy of this, as I mentioned. On the screen here, you have our director of sales and our account executives. If you have any questions where you wanna demo, you wanna see and go deeper dive, I already mentioned the university for your own self-education. So we really appreciate everyone's interest today, we had an incredible turnout. So, I thank all of you for your time and interest. And thank you, Travis.