Sign in to follow this  
Followers 0
HandledException

Logging Data from a CompactLogix via FactorySQL & KepserverEx

6 posts in this topic

Hi Guys, In our quest to get rid of our RSSQL installs, our shop has been looking for data logging alternatives. I've heard FactorySQL mentioned here on the forums a few times so I thought I would give it a try. The problem is though, that I'm brand new to both FactorySQL and Kepware (We usually use RSLinx, but I haven't been overly impressed with it, or Rockwell in general, so I thought I'd give Kepware a try as well). My problem is that in the current program, the plc tracks various date times retrieved from the controller (as an array of 7 Ints) that I need to read in and log to the database. I can't figure out how to create that date and time reference with this system? How do you go about doing that? Thanks in advance for any help.

Share this post


Link to post
Share on other sites
I'm going to go out on a limb here and assume that first you've managed to start logging SOMETHING. It's just that how to put it together gets a little confusing. I'm also going to highly recommend that you add a counter along with your date stamp. By default, the data logger is always set up great for processes. It automatically logs data on a time interval, say once a minute. This is great for continuous processes but lousy if you aren't measuring "stuff" like gallons per minute or tons per hour. If you are measuring "things" like in a discrete manufacturing process, if you simply start logging date stamps on a regular basis, you'll get a huge table but your interest is only in logging when things change. You can attempt to use the DISTINCT SQL command to try to eliminate duplicates, but...let me just say good luck with that. I spent days trying to do it that way and eventually out of desperation came up with a much easier way. If you simply add a simple counter and increment it every time your datestamp (or whatever you want to log) changes, you get two things. By monitoring the counter for missing or duplicate entries, you can easily detect problems in your data tables when problems occur. Second, you can trigger the data logger to only log an entry whenever the counter changes. Purists will have you construct a big expression that uses your data as input possibly with a hashing function since the counter is extraneous and unnecessary but after doing this for several years, I find that a simple counter is a life saver and helps troubleshoot numerous communication problems. Add the counter. You can thank me later when you realize what a blessing it is. Or curse me when you figure out that it reveals problems you didn't know you had. If your plant likes to stare at the counter, you can always reset it at the end of the shift on the PLC or something like that and simply delete all entries with a "0" in the counter in your SQL queries when you need to see the real entries. You won't be able to "create a date and time reference", but you will create an array of 7 ints. Right here, there's a problem. See, there is currently no standard syntax in OPC for "arrays". There is sort of a de facto standard but it's not always 100% guaranteed to work right. So chances are that you will end up with 7 columns, each representing one of your ints. You can even name them year, month, day, hour, minute, second, and whatever the last one is. So try to get it to store an array. It might work, or you might have to code each item individually. Sorry but that's OPC for you. Supposedly OPC-UA will fix this. So far I haven't seen any products made with OPC-UA yet. Assuming you've made it this far then you are logging your dates. But the formatting is awful. There are all these commands in your front end HMI thing that will let you format the dates but all you have is numbers. Was June a 6 or a 7? Well, the problem is that at this point, none of the programs know that those 7 integers you are logging are actually dates. We've got to fix that. Everyone seems to know "what time it is" but nobody can agree on just how to encode or store that information. It seems that although the Unix world pretty much had a de facto standard for handling them (number of seconds since January 1, 1970 UTC), and since the most popular TCP/IP protocol stack is the BSD Unix one, most folks adopted the Unix way. However, everyone else either tried to improve on this idea or came up with their own. For instance, PLC-5's broke out the individual elements because it was easier for PLC programmers but ControlLogix adopted an even higher resolution counter than the Unix one because it was intended to be used for motion control and very accurate time stamps. SQL on the other hand uses the number of days (fractions represent part of a day) since January 1, 1900 UTC. Internally, Microsoft SQL specifically stores it as two 32-bit integers. The first is the number of days since January 1, 1900 UTC. The second is the number of milliseconds since midnight. Excel uses a floating point number which represents the number of days since January 1, 1900 UTC. I could go on but you should be getting the point by now. Now, I'm not going to hammer out a bunch of SQL code right now partly because I don't even have any idea what your 7 integers represent (I expected 6). But I'll give you some pointers along the way. The way that I normally handle this is to convert the data into the native database format using a view so that I can access and treat it just like any other table, just with some upgraded formatting. It seems that most interface software that talks to the database knows how to understand SQL-based dates. So if we convert the data into that format, we can let the "automatic" things do our bidding. And that's when you can use all the date/time formatting stuff that's in your front end system since it should be able to understand SQL. Now for some SQL magic functions: You can create views which are nothing more than essentially preprogrammed queries in the SQL database. Your front end software should be able to see them and will think it's talking to just another table in the SQL database. The SQL query syntax is like this: CREATE VIEW ViewName AS (and then just type in your normal query here) The reason for the view in this case is that it's like a preloaded function. Once you get your view working to your satisfaction, you can simply treat it almost like any other table. Second, I'm going to make an assumption here, and that's that you are working with a PLC-5 (your description sounds like it's either that or something close). So we'll proceed under the assumption that you will have "column 1=year, column 2=month..." So, there's the DATEADD function. Google it. Now, what your query is going to look like will be DATEADD(yy, column-name-1900, DATEADD( dd, column-name-1, DATEADD( mm, column-name-1, .... And in the innermost value will start at 0 which is the SQL magic start date of midnight, January 1, 1900. If for some reason your SQL version doesn't like this, you might have to do something like "CAST(0) AS datetime" to essentially say, "Yes I know it's a number but just this once, treat it as a date". This is all standard SQL functions so unless your SQL version is broken, it shouldn't complain too much about the commands you are using. Finally, there is a good chance that whatever date format you have in your front end SQL data reading program, it's horrendous and you don't like it, no sir, not one bit. There's still hope. The SQL CONVERT command (again, google it) has all kinds of date formats and one of them at least is sure to be close enough to what you want. As to putting this all together, it's best to get a decent SQL book but I have a hard time recommending one. Most of them are not very good. For a free reference, try www.w3schools.com. They have a SQL tutorial that will at least get you through the basics. While you are reading on it, keep this in mind. SQL is completely and utterly unlike pretty much any other programming language. It is as alien to the "typical" programming languages as ladder logic is. The best way to get things done in SQL is to think "in the large". In most programming languages, you start with little things. Bits, bytes, words, strings. Maybe structures. Then you do things with the individual pieces using loops or array/block/file commands. There are loops in SQL but the compiler isn't very efficient at handling them (google SQL cursors and you'll get an ear full). In SQL, it's best to think in terms of sets or groups. You have a bag of stuff. You sift out the part you want, format it, sort it, and maybe even count it, totalize it, or average it. It takes a while to "get" SQL because you have stop thinking in ladder logic, C, Java, BASIC, or whatever you would normally use as a programming language and instead think "in SQL". Once you do, you'll do all the data manipulation in SQL and just use your usual tools for displaying the results because SQL is lightning fast when it comes to handling huge piles of data with hundreds of thousands of entries.

Share this post


Link to post
Share on other sites
I don't follow why the datestamp logged by the system, which gets its time from the database, doesn't work. But I'll assume you have some good reason to construct a datetime based on the PLC. The question has some answers here and here that address your question. I'd go with the FactorySQL toDate approach that was suggested. Let me know how it goes. I'll try it on my setup if you can't get it.

Share this post


Link to post
Share on other sites
Thanks for the help guys. The posts that Nathan mentioned were actually ones that I posted over on IA's Forums fishing for help over there. I haven't been able to get things to work yet, but I think I am getting close. --HandledException

Share this post


Link to post
Share on other sites
I finally got it sorted out with the help of IA's support. It turns out that the values I was calculating were never making it into the insert statement which was causing me all of my problems. All part of learning a new software package I guess! --HandledException

Share this post


Link to post
Share on other sites
No worries - hit me up if you have any more questions.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0