AdvancedHMI Software
General Category => Support Questions => Topic started by: Stumpy on March 12, 2019, 06:11:23 PM
-
I have been logging data to a text file using AHMI v3.99y Beta28 using the Basicdatalogger2. It works perfectly! But I now want summarize the daily logs.
In the Basicdatalogger or ChartWithLogging or Datasubscriber would there be an easy way to record just that days Min, Average & Max Values to a daily Log file for Each PLCAddressItem. I have about 10 monitored items.
I am currently testing and recording using BasicDataLogger2 writing a daily log file but then having to post process to summarize the data for min, Avg & Max Values separately for each PLCAddressItem using spreadsheet.
Any Help much appreciated!
Thank You in advance!
Hugh
-
I am not the best person to answer that regarding the HMI portion of this, Archie is, but I do what you are asking for in the PLC, pretty easy to store all the data and then use plc instructions for Averaging and finding min / max values. You don't need the chart for that but you could chart the results of the the PLC instructions .
What PLC are you using?
-
Hi DadvidSr
Thank you for your reply, sadly in this instance I do not have access to the PLC for programming, its located remotely.
Also the internet link is very slow, so realistically I can compile and then upload an AHMI project (taking about 3 or 4Hrs to upload). Note this not a mission critical app, just something to take the chore out of post processing data! Although the daily log details give great insight of workings, I now really only want the Min, Avg & Max readings cutting the current daily log points down from 2400 to 30. This over the years should save disk space!
I am using opcda driver and its an ABB 800xA. I can read the OPC data no problem and my current append to daily log file gives some robustness regarding history for previous values that day should a power outage occur. A ups is in place to cover brown outs but some times longer unplanned outages occur (ie storms affecting power grid) and a loss of log data until grid power is restored.
Cheers
Hugh
-
Hugh,
read carefully this part of DavidSr's post: "... store all the data and then use plc instructions for Averaging and finding min / max values ..."
How would a BasicDataLogger2 go about doing all the post processing without storing all the required data first?
You might be able to create a code to do this for you by reading the values from the log file and then processing them, similar to this:
https://docs.microsoft.com/en-us/dotnet/visual-basic/programming-guide/concepts/linq/aggregation-operations
or this:
https://www.dotnetperls.com/math-max-min-vbnet
This is just my view of this.
-
Use a database. There are plenty of free ones, with tutorials. Search MySQL, or SQLite, my two favorites.
The apps I have referenced in the past have examples. If you want more info let me know.
-
Thank you one and all for your time and the feedback... It opens ones eyes to options!
Godra: Great links to coded examples I will try these out on my saved data file(s)...
Phrog30: I do also like the option going forward using a database. So Yes More Info PLEASE! ....
I will attempt both options
Thanks Again
Regards
Hugh
-
Always glad to help Hugh .
While the other solutions / options suggested will probably do what you want, and were offered because you requested them, and both of those guys are offering you good options but you are headed down a path that is far more complicated then just using the PLC to store and process the data you want. It's already there, all you have to do is copy each change in what ever interval you want to other word addresses and let the PLC do the processing. Then it is easy to display it on the HMI and chart the processed results if you want.
MySql server is a great server, I have several running but setting that up to do what you want seems like an overkill.
I understand you have a slow internet speed but once it is programmed I think it will be far more reliable then the SQL or any other PC based solution.
If you want to try to do it in the PLC, Perhaps you can get someone local to the PLC to set it up for you. If you need PLC programming help check out https://plctalk.net
Whatever you decide in the end there are people willing to help you especially since you are trying to help yourself. Let us know how it goes. I wish you all the best.
-
Actually David,
You have a very valid point. I had not thought of your solution because the system is remotely located. However your last comment sparked a light bulb moment. I do have the option of running a "Soft Controller" (simulation on PC/Laptop) that I could load up the existing PLC Program and then develop to cater for my data recording requirements. Then carry out the necessary proving tests before loading up during a future site visit. I think that would work :)
So I guess I should update my previous statement... I will attempt ALL Three options. It should keep me busy for a while.
Thanks Again for the input!
Hugh
-
Here is a sample app with database stuff,
https://www.advancedhmi.com/forum/index.php?topic=2323.msg13814#msg13814
I don't think it's overkill to use a database. I'm no expert, but I've dabbled enough that's it's the quickest and easiest way to store and retrieve data. If you are new, then it might be a challenge. The biggest benefit to me is the easy way to query data.
-
Here is a sample app with database stuff,
https://www.advancedhmi.com/forum/index.php?topic=2323.msg13814#msg13814 (https://www.advancedhmi.com/forum/index.php?topic=2323.msg13814#msg13814)
I don't think it's overkill to use a database. I'm no expert, but I've dabbled enough that's it's the quickest and easiest way to store and retrieve data. If you are new, then it might be a challenge. The biggest benefit to me is the easy way to query data.
James I would agree if he wanted to store and retrieve a lot of data, I have done a lot of MySql, including running several websites running on it with PHP front ends, it is really fast and if it's run on Linux very stable but he only wants 3 items. That is why I said it seems to be an overkill. Doing math with Sql is more complicated then doing it in the PLC also.
You cannot out-run a PLC with Sql, you have to retrieve, process, store and then retrieve the processed data then interface it with the HMI with some code. The PLC can do it in milliseconds with every scan and display it on the HMI as fast as the comm link is, No Queries required. I am currently reading, several thousand data points, sorting, averaging and getting min / max and displaying it on the AVHMI, longest scan is only 40 ms when I let it all run. I am charting them and could put the results on one of my MySQL servers but I don't have a need for that. Another advantage of doing it in the PLC is once you have that information you can use it for process control if you need it.
If you really want to put it in a SQL database, do it after the PLC has processed it. Anyway that is how I see it, Many ways to skin it none are wrong, some are more efficient, really depends on what he wants to do with the results.
-
Forgive my lack of clarity moving forward just had to pause for thought. For me all three options present valid solutions with slightly different learning opportunities. Initially I did want the easiest/quickest solution to summarize my data but as with most things once you get a glimpse at the possibilities:- ideas form, requirements evolve/change, and so I now also want to use this to learn and gain experience allowing me to better understand and implement future system applications as they are needed.
I do find great comfort in the fact that as you have all demonstrated here that help is at hand when needed... So I hope its OK to pester you for more info, as I would like to learn/explore more regarding solutions using Databases and PHP with website front ends, (kind of an ultimate longer term goal in the back of my mind). Perhaps first I need to digest and solve the 3 solutions to the original query, helping bring me up to speed and use it as a stepping stone moving forward ie eventual goal of database solutions with web based front end.... of which I can only dream about from my starting point now! As the saying goes on this side of the pond "Rome was not built in one day", but with continued help/support from this excellent forum success seems to be within my grasp which I see as a great starting point! :)
Kind Regards
Hugh
-
Hugh I won't say what you "Should Do", that would be presumptuous of me, and There are guys that know a whole lot more than I do but I will say how "I" would do it based on what I know and understand if I wanted to go down the path you laid out.
Start with storing the data in words in the PLC
Process the data in the PLC
You said you wanted Avg and Min Max so the processed data is all you need, 3 values at what ever interval you want to record them.
Now you can use the Advanced HMI to Chart/Log just those 3 values. Since you only want the min max and avg you don't need to grab all the values you stored in the PLC to get those. If you post process in a DB you will need to grab all of it.
Now you can display the 3 different words ( For each of the 10 items you want) on the HMI, if you want, You can chart with out displaying it but it sounds like you wanted to see it in real time also. The word that has the MIN., The word that has the Max and The word that has the Average value. You can use a digital or analog Panel meter for that .
One project I am using this method for is storing wind speed every second for 15 minutes using a first in first out then averaging the 900 words to get the Avg Wind Speed for the previous 15 minuets. It runs continuously so it is updating the avg every second. I show real time wind speed on the HMI and the Avg. I also Grab the Temp from a RTD connected to an IR module. I continually read the Temp on the HMI.
I chart the values of both avg wind speed and temp with a time interval in the HMI of 900,000 ( That's 15 minutes.) (15 x 60 ) / .001
Now I could put that data in a db but I am not interested it doing that. I am running 12 different set ups like that on one obsolete PLC 5 with varying file lengths. Several of them storing, averaging, and totaling data over a 6 month period but running continuously. That's a lot of Data and the PLC does it in less than a Second every scan.
So That is the simplest way to get and see the data you want using the same method.
For your project, you can set up a chart in the HMI, for the 3 values you want, 1 chart for each item with the 3 values. Depends on what you want.
Now you have the data you want - and you can use Open Office, Libre Office or Excell to view the spread sheets and create other charts .
You can also export the sheets to A database in one of those office programs. No SQL needed, Open Office and Libre Office both have database programs and they are open source. see https://openoffice.org (https://openoffice.org)
This way you have a feel for it and altogether it would not take much time at to get all that with the database in your office program.That is not the best Database solution and I wouldn't use it if I wanted a long term solution but it will get you started and It is very easy to set up a simple db in those office programs but it is not a good long term solution for a DB doing what you want.
Now if you want to continue, with a robust DB then do as James suggested and set up A SQL, DB for it. You can set it up on the same machine you are developing AHMI on, And depending on where you are running the run-time of ADVHMI you can run it on that as well.
The great thing about MySql is it is super fast and you can run a lot of separate DBs on it on the same box and set up relational DBs so you can get data from more then one DB to analyze together, That also saves on the number of records needed because you can share a set of records across databases. You need to know a lot more about setting up a SQL DB then you do in an office Program DB though.
Have Fun!
-
Wind speed capture, store and average. Max is maximum ever and is not computed over a time period, You can do the same with minimum but that is always going to be a zero in this case so no need. If you want to get MIN/MAX for a period you have to write logic for that and you can read it from the same file and place the results in another word.
There maybe better, more compact ways to do this but this is what I am doing. The real time value is placed in the last word that is not seen in this graphic. All the data is moving from the bottom towards the top. The oldest data is to the right of where I show the average in the graphic. You can use a FLL (File fill instruction) instead of copy for integers but I started doing this with Floating point values for other things so I just use the same method for clarity. You cannot use the FLL instruction for floating point values in PLC 5.
All the other numbers you see are the captured wind speed values each second.
It is usually better to use a higher resolution for the timer time base such as .01 or .001 but I did not need it and to get 15 minutes the data file would have had to be much larger.
-
Can I just say "WOW" A lot of hard graft (work) and effort has gone in to that project, especially with attention to detail. Its fantastic! I think it would take me a few years to code something similar! :( After seeing that I am just jealous ;) but inspired at what is possible.
I can see how you have collated the 900 words of data, smart move for the hi resolution over the 15mins. Also I am very impressed with the Weather Station Graphics! Not to digress too far from the main topic but what I am interested in what kit is used in particular the wind direction and its granularity/resolution? plus is it analogue input?
It looks like the PLC I will be using has a built in "resource/overhead" graph as part of its download so I can hopefully avoid over burdening the processor or memory which is nice since I know my coding initially wont be efficient!
Hugh
-
You are giving me way too much credit.
It would take you all of 30 minutes to do all of it. That is why I said use a PLC for the data processing, not a database. The graphics are just stock Advanced HMI stuff.
It's late for me, I will answer more fully how I get the data next week. It's simple if you have the right stuff. The weather station is really a very small part of my HMI screen. I have it on a 25 inch monitor so there is a lot more I did not show.
Can I just say "WOW" A lot of hard graft (work) and effort has gone in to that project, especially with attention to detail. Its fantastic! I think it would take me a few years to code something similar! :( After seeing that I am just jealous ;) but inspired at what is possible.
I can see how you have collated the 900 words of data, smart move for the hi resolution over the 15mins. Also I am very impressed with the Weather Station Graphics! Not to digress too far from the main topic but what I am interested in what kit is used in particular the wind direction and its granularity/resolution? plus is it analogue input?
It looks like the PLC I will be using has a built in "resource/overhead" graph as part of its download so I can hopefully avoid over burdening the processor or memory which is nice since I know my coding initially wont be efficient!
Hugh
-
Here is a little more info on using a database. My go to is MySQL, but for something as simple as what you are suggesting I would use SQLite instead, https://www.sqlite.org/index.html (https://www.sqlite.org/index.html).
I think it's the best choice because there is nothing to install, it just works. I have included a test database that I manually entered data in to. Here are some simple queries:
1. To get all data
select * from testtable
2. To get min value between two dates
select id, t_stamp, min(value) from testtable
where t_stamp between '2019-03-17 1500' and '2019-03-17 1507'
3. To get max value between two dates
select id, t_stamp, max(value) from testtable
where t_stamp between '2019-03-17 1500' and '2019-03-17 1507'
4. To get avg between two dates
select avg(value) from testtable
where t_stamp between '2019-03-17 1500' and '2019-03-17 1507'
To me, it really isn't much simpler than that. One thing to note, you don't need a block of data in a PLC for this , you just need to log data on change. My point, to log data every second, or minute, etc. is wasteful. Only log when it changes outside of a deadband you think is necessary. So, for temp, maybe 1 degree. The nice thing about using a database is it's really easy to query data from points of time. Using a log file each day is fine, but what happens if you want to know weekly or monthly data? It's tedious with a log file saved each day. The database is very simple, just query a start and end date.
What I have done in the past is use a FIFO for sending data to the database, this acts as a store and forward, so any interruption in connectivity and you are somewhat covered.
Here are the SQLite files. Install the browser, then open and point to the sample project and datbase.
https://drive.google.com/file/d/1d1BJe-akbPeI1HORQvMjfYLUQMRbSuv2/view?usp=sharing (https://drive.google.com/file/d/1d1BJe-akbPeI1HORQvMjfYLUQMRbSuv2/view?usp=sharing)
You can't really mess this up, just open it up and play around with. If you don't use it, no big deal, but you might learn something and think it's fun.
If you have any specific questions let me know. What's nice, there is a lot more help online for database than there is for PLC. So, if you get stuck, open google and search and you will probably find the answer easily.
-
I think your idea is good. James I downloaded and looked at the file you created, and I would encourage Hugh to do so.
I actually agree with you if a person wants to keep the data so it is not overwritten and do whatever with it. But I do not agree with not storing it in the PLC. It does not tax the PLC a bit to keep it there, not at all waist-full and it is the most efficient way to make it available to the HMI for display and for use by the PLC if it is needed for any type of process control.
Everything he said he wants can be done with a few instructions and displayed on the HMI. If he wants to keep it indefinitely then I agree with you to store it in a DB but still keep the data file in the PLC for all new data for whatever time period he wants to display on the HMI. There is no need to take it from the PLC and put it in a DB and put it back in the HMI.
Also, any part of the data file can be averaged, you don't have to average the whole file, you can start and stop anywhere in the file you want. You can also get the min/max from any part of the file you want and with many PLCs it only takes one instruction each to do that.
We have 6 months of data for two furnaces stored in the PLC
Run Time, Total Fuel Used, Total Cost to operate per furnace and for a dryer and water heater. Computations for fuel used is based on BTU of appliance. Everything is calculated in the PLC. (We will be adding CTs to electrical equipment as we go forward with our Total Energy Monitoring Plan to monitor costs and plan expenditures. )
We take only parts of those 6 months long files to display Average fuel used per day last 30 Days and last 7 Days, and real time per day for each of the two furnaces. For now, we only get a running total for the other appliances because they don't use much. Cost per day real time is also displayed.
We also take total fuel usage of all equipment and project when we will need a re-fill updated every time an appliance cycles. All the data is displayed on the HMI Updated every scan less then a second, Now that's six months of data and we have enough memory in the PLC we could do it longer but we don't need it any longer than that so why put it in a DB? We don't know what more we would do with it unless we wanted to keep it year to year.
If the HMI is not running- you get no data, That is the week link in using a SQL solution to store the new data. In an Industrial setting where the PLC is controlling the equipment you are monitoring, If there is data to be had, it will be in the PLC, if the PLC is not running, there is no production data to be acquired anyway until it is back in run mode, nothing lost.
Another advantage is if you connect a different HMI, the data is still in the PLC, all you have to do is load the config file into the HMI. And it is not HMI type dependent, You can put it on whatever HMI that is compatible with the PLC. Can't do that if the data is stored in the HMI -
I think you have probably done PLC work long enough to know the PLC is far more efficient at directly displaying the information on the HMI without putting a PC in the middle, /processing and putting the processed info on the HMI. There is no way a PC based solution for that is more efficient or even close to the speed the plc does it on it's own.
Where I agree with you is keeping the data long term but for everything else he wants, he does not need an external db for it. As long as the PLC has enough memory to store what he wants.
I am grateful for your presenting this alternative and I may use it myself if I want to keep the data to compare from year to year but I will still store the new data in the PLC which I back up daily to a network drive.
On a side note, I am amused at the windows people that hated Novell Netware for the command-line interface - no GUI and yet rave about SQL. ( I like NetWare)
I would say the best of both worlds here is both solutions together, not one or the other. So thanks for that Idea.
(They only keep me alive because I know DOS)
-
...not at all waist-full and it is the most efficient way to make it available...
Was referring to the database side. It really isn't best practice to continually send the same data over and over again. Sending on data change is really more efficient. If you need to do interpolation you can, but generally you wouldn't need to.
On a side note, not all PLCs allow users to create big blocks of memory. Example, a MicroLogix has a 256 element long restriction. So that could be a deal breaker for some. Or, some brands might not even have instructions or means to do averaging, etc.
-
Was referring to the database side. It really isn't best practice to continually send the same data over and over again. Sending on data change is really more efficient. If you need to do interpolation you can, but generally you wouldn't need to.
On a side note, not all PLCs allow users to create big blocks of memory. Example, a MicroLogix has a 256 element long restriction. So that could be a deal breaker for some. Or, some brands might not even have instructions or means to do averaging, etc.
I agree....
-
Phrog30, Sorry to bother you....
Thank you for the demo download. I have had a play with the table and supplied data seemed to work great. It looks a really simple easy tool for slicing & dicing loaded data! To this end I eagerly wanted to try my own sample data from my BasicDataLogger2 daily log file (ie raw data text file but I renamed the file extention from txt to csv allowing me to easily import into both excel and DB Browser for comparison). Note daily log file is simply 240 data samples for a few PLC items (temperatures, and other bits n bobs) ie recording every 6mins over 24hr period.
For the Min, Avg & Max aggregate function I am only applying the calculation to the 2nd column (field2) which is ambient air temperature.
I expected all result to be identical but was surprised by my findings and can only conclude I am doing something wrong?
MS excel results (which to me seem to be correct)
Min -11.99817 04/03/2019 23:47
Avg -7.186637
Max -3.794922 04/03/2019 12:53 and 12:59
DB Browser results.... (Version 3.9.1, Qt Version 5.7.0,SQLCipher Version 3.11.0)
1. The Average function in DB Browser for SQLite worked as expected in both giving -7.186637 (GREAT RESULT)
2. The Minimum function in DB Browser result = -10.09155 (which seems wrong)
3 The Maximum function in DB Browser result = -9.979401 (also seems wrong)
Using the select statement to include date & time (field1) I get
select field1, min(field2) from PLCDataLog_2019_03_04
"04/03/2019 00:17" "-10.09155" <---Row 3
select field1, max(field2) from PLCDataLog_2019_03_04
"04/03/2019 00:23" "-9.979401" <---Row 4
Oddly DB Browser seems to repeatedly point to the 3rd & 4th row of field2 to return its version Min & Max.
Great that its pointing to something that exist in my table but again oddly 1st temperature is the min out of the first four so why did it not return the 1st row result then stop??
In addition the Avg function must use the correct values for this average value to be the same as the excel calculated answer. This adds to the intrigue!
So far they are my findings regarding DB Browser Min & Max aggregation and in conclusion I am left scratching my head!
I have attached my sample data csv file for "Fun" filled problem solving (just to get an impartial view that is before contemplating a bug report).
Also I tried to use the plot window which looks very useful (unless I use select!). All the data "is" available on initial file import, but then as expected vanishes as the select command serves up only those items returned from the select request. All good so far eg select max(field2) from PLCDataLog_2019_03_04
However when select * ie "select * from PLCDataLog_2019_03_04" is used I would expect field1 (my Date & time) results to return on the plot screen but magically field1 remains missing from the plot window but is in plain view in the results table located below the select command window. It would appear that I am unable to plot anything against time without field1 being present in the plot window.
Not sure now wether to continue scratching my head at this point or close my gaping mouth... seems a bit baffling to me....
I did try to remove the "_" from the table/imported name in case that was some sort of undocumented qualifier to stop at row 03 & 04. It would seem not as it make no difference to my results.
Can you shed any light on the above teething problems...
Regards
Hugh
-
For the Min, Avg & Max aggregate function I am only applying the calculation to the 2nd column (field2) which is ambient air temperature.
I'm not following, what calculation to field2?
Also, I have never imported data, how exactly did you do it? What is the table structure, there is a tab Database Structure, can you show the columns, the types? I recommend creating an ID column and have it primary key and auto increment.
For testing I created another column and entered random values, I also used all negative values, my queries work fine.
-
I took your CSV file, added a new column "A", made this be an index 1-240, then imported into DB Browser, I used your queries, but offset by one since I added a column and the results were:
select field2, min(field3) from testMe
4/3/2019 23:47 -11.99817
select field2, max(field3) from testMe
4/3/2019 12:29 -3.794922
Looks like the results you were expecting, correct?
-
Yes your results are what I was expecting.
-
So I guess would each csv file needs modification to have its own unique index column added, perhaps my assumption that date & time column not suffice.
Also I noticed there are some options in DB Browser to create index. I can try it out.
Thank you for your prompt response!
Hugh
-
So I guess would each csv file needs modification to have its own unique index column added, perhaps my assumption that date & time column not suffice.
Not really. You really should have at least one column that is indexed. When you make it a primary key it is automatically indexed. To me, it's just easier with an id column.
The queries should have worked bringing in the data as is. Perhaps something else got messed up.
-
Ok thanks for Feedback... No pain no gain as they say! I will try DB Browser update and play with it abit....
Cheers
Hugh
-
Update from 3.9 to 3.11.1 on DB Browser for SQLite seems to be better on the plots but not perfect... Work in progress on their part I assume, time will tell!
-
Glad to see you are trying James solution Hugh, and you seem to be making good progress. I downloaded it but since have seen there is a 64 bit version. I have not tried it though, I will wait to see James comment on that.
James is exactly right on having a field as an indexed field and although it does not have to be an "ID" , it can be any, but you should do it that way because it most likely will be the only field in your application with unique contents.
-
Hello David
I am sure James soon will think I am very "trying!" but where there is a will there is a way!
Just for background info...
I note what you both are saying about the index etc... The "time and date" field1 in my sample csv file is unique and the DB Browser has an auto index option (tick box) under "Edit Pragmas" Tab, which is adjacent to Execute SQL tab) which results in row/ID from 1 to 240 which is automated and seems correct. So I am assuming the unique index should all be taken care of as the raw csv file is imported. ie Import option under File Menu and selecting the csv and accepting default options.
On the graph Plot (DB Browser ver 3.9) initially everything worked, but as I used the Execute SQL Tab to create some select commands (ie filter my data), now graph plot literally lost the plot! Downloading DB Browser ver 3.11.1 improved the situation but it is not perfect yet. I think a few more "Gremlins" are in their code...
This is no show stopper for me just part of my learning curve. Think I will continue to use DB Browser as engineers tool. The plot window is really quick and easy to use but keep in mind there may be a few undocumented features you may come across!
Hugh
-
Hello David
I am sure James soon will think I am very "trying!" ..............................
Hugh
James will be very helpful and as long as you are trying to work through things he won't grow weary. It's the ones that expect people here to do it all "FOR" them with little or no effort on their part that will loose support from those here on this or for that matter pretty much any support forum. James has been here a long time and has always demonstrated his willingness to help.
I won't comment on your posts regarding how to handle the issues you raised as long as James is helping you. He is probably more capable then me in this particular application and all I will do is get in the way.
You never did tell us what PLC you are using unless I missed it. Sharing that information is critical for knowing how to help you on the PLC side of things.
-
Hi David
I mentioned PLC was ABB 800xA and I am using the opcda1 driver for Advanced HMI comms.
I am looking to solve this min/max/average problem all 3 ways using;
1. Logging to file
2. Logging Using database(s)
3. Using the PLC to Log in the PLC ie you solution!
It the learning opportunity/curve for me so enjoy the Silence I am just mulling things over... rest assured it won’t be too long before my queries will be heading in your direction :)
Longer term I am wanting to be able share some of the data from the PLC with other persons but not at the risk of the PLC (hence log files or database) but like wise, what I would consider mission critical data I would want to store in the PLC (ie your robust PLC Log solution).
I also see the need to take a data snapshot of how things (system/sub system level) perform when commissioned. That way when after years of operation you can pull the data & compare. You can “SEE†degradation in performance has cause over time. Then maintain it back to an acceptable level. I think a really useful tool to look at that log data is DB Browser for SQLite.
Any way in summary I still need assistance to crack 2 & 3 above so remain on standby “Please†I find it of great help to be able to obtain assistance by asking. I do appreaciate time is precious. So thank you all once again!
Hugh
-
You really should have an ID field, make it not null, the primary key, and auto incrementing. If you want to insert into a table from AHMI, you will need this arrangement. A few things to note, SQLite doesn't have an IDE, like other DBs do. The DB browser you are using is 3rd party, so very possible it has bugs. I almost never use a browser, I do everything from code within .NET. There are several other browsers out there, if you want to try them out. DB browser has worked for me. Here are some others:
https://github.com/sqlitebrowser/sqlitebrowser (https://github.com/sqlitebrowser/sqlitebrowser)
https://sqliteonline.com/ (https://sqliteonline.com/)
https://www.sqliteviewer.com/ (https://www.sqliteviewer.com/)
I'm in no way an expert, but here are code snippets to help you get started if you want to do some of this via code:
To create a table...
You will need to import, Imports System.Data.SQLite
Public dt_Active_Alarms As DataTable
Public dt_All_Alarms As DataTable
Public Alarm_Quantity As Integer
Public AlarmActivePath As String = (Application.StartupPath & "\MyHMI\Data\Alarms.ini")
Private connBuilder As SQLiteConnectionStringBuilder
Private Alarm_Table_Initialize_Complete As Boolean = False
Private AlarmActiveini As IniFile
Private AlarmDescini As IniFile
Private Alarm_DescQty As Integer
Private AlarmDescPath As String = (Application.StartupPath & "\MyHMI\Data\Alarm_Desc.ini")
Private AlarmDescArrayName As String
Private AlarmHistoryDBPath As String = Application.StartupPath & "\MyHMI\Data\Alarm_History.s3db"
Private Binary_Length As Integer = Globals.Alarm_Binary_Length
Private Sub Alarm_Initialize()
connBuilder = New SQLiteConnectionStringBuilder()
connBuilder.JournalMode = SQLiteJournalModeEnum.Wal
connBuilder.SyncMode = SynchronizationModes.Normal
connBuilder.Pooling = True
connBuilder.LegacyFormat = False
connBuilder.ConnectionString = "Data Source=" & AlarmHistoryDBPath & ""
If Not System.IO.File.Exists(AlarmHistoryDBPath) Then
SQLiteConnection.CreateFile(AlarmHistoryDBPath)
End If
Using cnn As New SQLiteConnection(connBuilder.ToString())
Using Comm As New SQLiteCommand()
With Comm
.Connection = cnn
.CommandText = "CREATE TABLE IF NOT EXISTS [Alarm_History] ( [Id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,[Num] INTEGER NULL, [Name] TEXT NULL, [FaultTime] TIMESTAMP NULL, [ResetTime] TIMESTAMP NULL )"
.CommandType = CommandType.Text
End With
Try
cnn.Open()
Comm.ExecuteNonQuery()
cnn.Close()
Catch
End Try
End Using
End Using
Using cnn As New SQLiteConnection(connBuilder.ToString())
Using Comm As New SQLiteCommand()
With Comm
.Connection = cnn
.CommandText = "CREATE Index IF NOT EXISTS Alarm_History_ndx On Alarm_History (Id);"
.CommandType = CommandType.Text
End With
Try
cnn.Open()
Comm.ExecuteNonQuery()
cnn.Close()
Catch
End Try
End Using
End Using
Using cnn As New SQLiteConnection(connBuilder.ToString())
Using Comm As New SQLiteCommand()
With Comm
.Connection = cnn
.CommandText = "PRAGMA journal_mode = WAL"
.CommandType = CommandType.Text
End With
Try
cnn.Open()
Comm.ExecuteNonQuery()
cnn.Close()
Catch
End Try
End Using
End Using
Using cnn As New SQLiteConnection(connBuilder.ToString())
Using Comm As New SQLiteCommand()
With Comm
.Connection = cnn
.CommandText = "PRAGMA synchronous = NORMAL"
.CommandType = CommandType.Text
End With
Try
cnn.Open()
Comm.ExecuteNonQuery()
cnn.Close()
Catch
End Try
End Using
End Using
Alarm_Table_Initialize_Complete = True
End Sub
Insert stuff into the table you just created...
Private Sub Alarm_History_Data(ByVal alarmID As Integer)
If Alarm_Table_Initialize_Complete Then
Dim cnn As New SQLiteConnection
cnn.ConnectionString = "Data Source=" & AlarmHistoryDBPath & ""
If AlarmActiveini Is Nothing Then
AlarmActiveini = New IniFile(AlarmActivePath)
End If
Dim alarmText = AlarmActiveini.ReadValue("Alarm " & alarmID & "", 2)
Dim alarmActiveTime = AlarmActiveini.ReadValue("Alarm " & alarmID & "", 4)
Dim dt = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
If alarmText <> "" AndAlso alarmActiveTime <> "" Then
Using Comm As New SQLiteCommand()
With Comm
.Connection = cnn
.CommandText = "INSERT INTO Alarm_History (Num, Name, FaultTime, ResetTime) VALUES (@Num, @Name, @FaultTime, @ResetTime)"
.CommandType = CommandType.Text
.Parameters.AddWithValue("@Num", alarmID)
.Parameters.AddWithValue("@Name", alarmText)
.Parameters.AddWithValue("@FaultTime", alarmActiveTime)
.Parameters.AddWithValue("@ResetTime", dt)
End With
Try
cnn.Open()
Comm.ExecuteNonQuery()
cnn.Close()
Catch ex As SQLiteException
MessageBox.Show(ex.Message + System.Environment.NewLine, "SQL Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Using
End If
Alarm_History_Table()
End If
End Sub
Some of the code I included isn't necessary for you, but I'm pressed for time so I just copied and pasted. You will need to strip out what you don't need.
Continue to "play" around and ask all the questions you need.
-
James much obliged! Thanks!
-
Hi David
I mentioned PLC was ABB 800xA ..............................................
.........
Hugh
That processor has some nice features, but a limited instruction set so it is not as simple to do the functions that are available in PLCs with advanced instructions. A lot more needs to be done to deal with math on files. Where one function in advanced PLCs will average a group of words, unless I am missing something, that is not available in the ABB 800
One of its strengths however is that you can use several programming languages which gives you some flexibility to decide which one is best for your application.
I did a large Job for American Family Insurance Headquarters in Madison Wisconsin several years ago - We put together a SCADA for their DATA Center. They have 7 large generators and we used a Modicon PLC to handle the switching. I used FB programming for that. It was very complex, I never programmed anything that had so many conditions controlling one output.
I would have hated to have had to use Ladder logic for that job. FB made it much easier to see the flow. So If you are wanting to get into Function Block programming, although it has some drawbacks it would be worth a try just to get familiar.
One suggestion to you is to break up your program into separate files of associated control parts as sub-routines. Such as.. if you are controlling say, the doors on a parts washer, put all the door control in one file by its self. It is then easy to copy that logic to another program that uses the same logic and much easier to trouble shoot then scattering it through your program with unrelated logic.
There are many ways to break up a program in the order that makes the most sense for a given application. I put all lighting control in a subroutine for example and Alarm Monitoring in another, and so on.
Other programmers have their preferences and there is no real wrong way but you need to understand how the I/O and other data tables are updated regardless what you do or you can be scratching your head trying to figure out why something looks like it should be turning on or off is not doing what you think it should when you look at the logic. The Obvious is not always so obvious.
-
Hi David
Yes I think the kit should be able to handle what demands I throw at it with relative ease. I am spoilt for choice regarding plc programming language options, but I think I will try to tackle it in Structured Text Language. The interface is geared up for modular development kind of how your suggesting. It pays to keep logic well laid out easy to follow and logical! I am awaiting the latest firmware release. It should arrive any time soon, then at least I can upgrade my plc simulator and start developing and testing some code.
Cheers
Hugh
PS I am still interested in the meteorological equipment/data (Wind Speed/Direction). In particular the resolution and interfacing of direction. Longer term I am wanting to implement Met data on the HMI so will need to spec up I/O to suit.
-
Hi David
...................
PS I am still interested in the meteorological equipment/data (Wind Speed/Direction). In particular the resolution and interfacing of direction. Longer term I am wanting to implement Met data on the HMI so will need to spec up I/O to suit.
I built my own and wind speed anemometer very simple - and it is still a work in progress, I am using a small DC Motor connected to an Allen Bradley 1771-IFE analog input module. I scale the output of the motor in the module to have it read the proper value in the PLC and on the HMI. It is not the best solution for the sensor but whatever I settle with it will still be connected to the Analog Input Module.
Still working on options for the Wind direction but will probably use an encoder connected to an encoder module. This is where the PLCs with advanced instructions / features are nicer to use then PLCs without them. All the scaling is done in the module and is just a matter of entering values. No need for math instructions in the program