Daily Log file - Is it possible to just record Min, Average and Max Values?

[Stumpy]

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

[Phrog30]

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://sqliteonline.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

Code: [Select]

    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...

Code: [Select]

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.

[Stumpy]

James much obliged! Thanks!

[DavidSr]

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.

[Stumpy]

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. 

[DavidSr]

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