Version:

This lesson is part of the Tag Historian in Ignition course. You can browse the rest of the lessons below.

LESSON LIST

Autoplay Off
Take topic challenge

Supplemental Videos

LESSON

DB Table Historian

Description

The DB Table Historian provider can process tables created by third-party systems and attempt to interpret the data, making it available to Tag History queries.

Video recorded using: Ignition 8.1

Transcript

(open in window)

[00:00] In this lesson, we'll take a look at the DB Table Historian Provider, which is a unique provider that takes the content of tables created outside of the Historian system and exposes their contents to things like Tag History queries. Now, to begin with, we first need a database table. I have this DB Database Connection Provider here. And inside of it, I have not one, but two tables. So, I created two tables, and using MySQL Workbench here, create a table_a and table_b. So, table_a has a couple of rows, and table_b, also has a couple of rows. Both tables also have at least one DATETIME column. If you're curious how I configured these, here are the settings I use. You can pause the video if you wanted to look a little bit closer at them. But that's not super important. What's important is that, they do have some rows, and we're going to go ahead and take the contents of both tables and we're going to expose them to different bindings and components within our project. So, we have a Database Connection with some tables. To use this provider, first, we need to create an instance of the provider. So, on my gateway here, under Config, we can scroll on down, and under Tags, I can go to History. And, let's go ahead and create an instance of this provider. So, I'll click the Create New Historical Tag Provider link. And from the list of options, we'll select the DB Table Historian, or in this case, the first one in the list. I'll click Next. And let's give this provider a name here, so, how about just "DB_TH". Now, aside from the name here, the only other property you really need to worry about, is this Data source property. So, you need to effectively tell this provider which of your database connections it should be looking at. Now, my case it is in fact, the DB database I want to look at. So, I'll click the Create New Historical Tag Provider button. Okay, we're actually done configuring our provider. Now, we get to try to use it. So, let's go ahead and switch over to our Designer. Now, to start with, I think I'll focus on perspective, so, you can see I'm looking at a view here, and I have a PowerChart selected. Now, what we can do with this new provider is, we can, I'm going to put my Designer to Preview Mode here. And I'll go ahead and start browsing for Tags here on the PowerChart. If you don't see your Historian on there, you can always go ahead and refresh. But looks like I have mine here, anyways. We can go into DB_TH, we can see our two tables. I can go to table_a, and I do want to show the value, I want to trend the value. So, I'll go ahead and select value, I'll click the Add Selected Tags button. And there's our trend. And it was that easy. Although, I did cheat. There is one little thing I did that I should talk about. And that would be the t_stamp column here. The DB Table Historian Provider has limited ability to interpret the meaning behind any given table. To give this feature better interplay with transaction groups, The provider will recognize any column named t_stamp, as the column it should use as the DATETIME for each row. Which is why that first example from table_a worked right away. However, your tables likely won't always have a t_stamp columns. So, let's look at what we can do in those scenarios. Let's go ahead and get rid of this pen here. And, let's take a look at table_b. Now, if you recall table_b actually had a couple of DATETIME columns. So, I had one called time, and then I had more_time. Time is showing 13:00 or 1:00 p.m. times, and more_time is showing 15:00 or three o'clock. So, if we try to add value from table_b here, we actually get a whole lot of nothing. You'll get some errors in the console, but we do see that the PowerChart here does have a new pen. So, we're actually getting an error just because in this case, it doesn't really know which of those timestamp columns to use. Now, let's take a closer look here. Now, this part's kind of specific to the PowerChart here, but really, the key part here is being familiar with how the various features that Ignition that can query Tag History, configure the path to the tag in the History system. So, in this case, the PowerChart will list its pens on the Pen's tab here. If we scroll down here, we do have a little path that leads to the values that are coming from my table. Now, to make this a little bit easier to talk about, I'm actually just going to copy this out, and I'm going to move that text over to something that's a little bit easier for you to see. Now, again, if you've seen these historical paths with the Tag Historian Module, you may be familiar with some of the ideas or the notation here. But in our specific case here, the : and /, sort of delimit different units or different components of this path here. So, if I were to try to make this a little bit more readable. So, the History Provider name is, DB_TH, the table in that provider is, table_b, and then the column from that table that we want to use on this pen here is the column, "value". There's actually more stuff we can add here, it just doesn't add a timestamp by default, but you can actually specify which column is the TIMESTAMP or the DATETIME. So, in this case, I'm going to add a new line, we'll put a :/ down, and if you check the User Manual on the DB Table Historian Provider page. You can search for it, it's also alongside all of the other Tag Historian Provider stuff. There's actually a timestamp component. And, this allows you to just type in the name of the column that's going to be the timestamp for this particular point of data. So, I do have a time column, and we did say time was the 1:00 p.m. times. So, let's try to use that. I'll go ahead and I'll get rid of all those new lines here, and I'll select everything, we'll copy that out, and I will replace that down below. We could have just typed it down here, but easier for me to just copy-paste. I'll click Done, and look at that, we do have a trend that's actually showing again. If I were to try to look at the timestamp here, we can see that it's showing about one o'clock, I was off a little bit with my click there. We can also, of course, go back to those Pen Settings. And we could actually change the end of this little path here, instead of specifying time, that we can change it to more_time. Which should give us the 3:00 p.m. times. And look at that. Great, so really, you just need to be aware that the additional components for these paths exist. Now, let's also just take a look at Vision, just to give you a different interface to kind of show you the same sort of routine. Over here, in Vision, I do have a Power table. If I select it, and head down the Property Editor, I can take a look at the Data property, which we know is where the Power Table gets all the content, or data that's supposed to show inside of it. Let's configure a binding, we'll do a Tag History Binding, and I'll go ahead and refresh this from an earlier take here. But you can see, that we do have our table being basically the same kind of interface we saw earlier with the Power table. I'll go ahead and try to add value over here, but I'll go ahead and just double click, and I will paste in that path we just copy-pasted from earlier, where we're pointing at time, and play around with the real time settings. We'll set it for four hours, I suppose. And there we go. We've got our two, 1:00 p.m. times here. All right, that's going to about wrap it up for this video. Again, I'd highly advise you take a look at the User Manual page just because it does have more information on the various components, but really just modifying that Historical Tag Path is the key piece of information you need to be aware of when using this provider.

You are editing this transcript.

Make any corrections to improve this transcript. We'll review any changes before posting them.

close

Share this video