April 21, 2016 at 5:16 pm
I have a walking freezer controller that logs temp and other variables like door open,etc. In all it tracks 17-20 variables and stores them in an .xml file. this file is updated every 2 seconds. I would like to store this info, by reading the xml and storing it into a database for tracking freezer performance.
in the future i would like to show these variables on a graph. i am not a complete newbie but am definitely over my head. Help? Where do i start?
Jeff
April 21, 2016 at 5:49 pm
First, welcome to SQLServerCentral. This sounds like fun.
What you want to do sounds simple and easy. A few questions:
1. Do you have an MS SQL Server you can use? If so what version?
2. How are your T-SQL skills?
3. Excel is the simplest solution for creating charts/graphs with the data. Would Excel work?
4. Would this be a personal instance of SQL or a Production server somewhere?
-- Itzik Ben-Gan 2001
April 21, 2016 at 9:06 pm
Further on Alan's questions, does it save a new file every 2 seconds or does it append to the existing file? Like Alan mentioned, this seems very straight forward with quite few ways of solving the problem without any heavy weight programming needed. You could i.e. use the likes of PowerBI for the visual reporting.
😎
April 22, 2016 at 6:49 am
Answers:
1. I have a godaddy account with a MySQL server, and this is my limited experience with SQL.
2. Not sure what t-sql is with out googling it.
3. Have MS Excel but for the final version of this project i envision having a website i can go to and pull up data, by date/time, probably in a graph form.
4. Probably answered by question 1, but i can install a LAMP solution on a computer or Raspberry Pi.
I am a newbie to this, and am willing to learn, but not finding great simple tutorials on the subject. I am not even sure this is the best solution. but it is one i am somewhat familiar with.
this controller can be viewed at the following link
and the xml is at
http://68.52.248.181/status.xml
This xml is updated/refreshed not appended.
If you visit the link, it is the embedded webserver in a controller for a walkin freezer. the controller has a graph button, but the controller graph is only showing a data snapshot of every 10 minutes, so it doesn't catch the door opening unless it is open exactly that 10 min interval.
by datalogging via the xml, i can gain every 2 sec intervals and catch the door opening, such as when they are loading in or out product, and thus explain why the box's temp spikes.
the controllers manufacturer says that folks are datalogging the xml via saving the data from the xml into MS Access, but will possible this is not a OEM supported function, so it has become a DIY project for me. I would like to save the data to my websites SQL database and be able to pull it up from anywhere i am, not just at my desk. At this point it is not feasible financially to pay a web developer to do this for me, so i am attempting to learn to do this in my spare time, as i also enjoying the learning experience. thanks for your patience..
Jeff
April 23, 2016 at 2:13 pm
We're not really a MySQL site, so we might not be great at helping here.
What's I'd suggest is that you do a few things.
1. learn to retrieve the XML from the URL and shred it. By this, I mean you should be able to grab the time, temp, etc, and get those in variables.
2. Build a table in your MySQL database that has fields for these values.
3. build a method in your app that grabs the variables and stores them in MySQL (via an INSERT statement.
Once you have that, you're recording data.
The pull from MySQL into Excel or with some other system is easy from this point. You're querying data and displaying it.
April 23, 2016 at 8:16 pm
I would add:
You'll want to learn first how to import the XML data which should be pretty easy. Then how to save it as relational data - meaning that it's the data is extracted and stored in MySQL tables. Here's an example of what I'm talking about using the XML from the link you posted and updating it every couple minutes.
DataCollectionDate Smode rTemp cTemp comp evapfan
----------------------- ------------ -------- ------- ---------- ----------
2016-04-23 16:09:48.023 Refrigerate -4.3 F -8.1 F Relay On Relay On
2016-04-23 16:11:48.023 Refrigerate -2.3 F -8.3 F Relay Off Relay On
2016-04-23 16:13:48.023 Refrigerate -5.8 F -2.1 F Relay On Relay Off
2016-04-23 16:15:48.023 Refrigerate -1.3 F -8.1 F Relay On Relay Off
2016-04-23 16:17:48.023 Refrigerate 4.9 F -7.4 F Relay Off Relay Off
2016-04-23 16:19:48.023 Refrigerate 0.3 F -8.6 F Relay On Relay On
I noticed that there's widgets out there that allow you to use mySQL data to create a graph, one example is below. For shredding XML the key is to understand XPath - it's not hard at all. Here's some good sites to get you started:
XPath Basics (key for working with XML)
-- Itzik Ben-Gan 2001
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply