Read/write in Mysql DB

[condore89]

Hello everybody.
I'm new to AdvancedHMI, and so far I've done quite some fun stuff with it (Thank you Archi).

Now what I want to do is write in a Mysql table the value of a tag of the PLC....but automatically. What I mean by that is that when the value changed in the PLC, the program should be able to detect it and update it in the Mysql table. From what i read in the forum, the Datasubscriber may be helpful for it. But I have no idea how to use it.

Can anyone give me the steps to do this?

[Archie]

For writing to MySQL, you will probably want to use this:

https://dev.mysql.com/downloads/windows/visualstudio/
https://dev.mysql.com/doc/visual-studio/en/

-or-

https://dev.mysql.com/downloads/connector/net/1.0.html


For getting PLC data, the DataSubscriber is one way of doing it:

- Add a DataSubscriber to the form
- Set PLCAddressValue
- Double click the data subscriber to get back to the code for the Data Changed event
- Using e.Values(0) for the value, write the code to save to the DB

I never used MySQL so I don't have any code examples, but there are plenty on the internet, such as this:

http://stackoverflow.com/questions/20287696/how-to-retrieve-mysql-data-in-vb-net

[Vitor]

If helps you here is a litle piece of code working with DataSubscriber2.
I have a button only for testing the connection with the database for debuging purpose.

This code i placed in the main form.

Imports MySql.Data.MySqlClient

Public Class MainForm
    Dim MySqlConn As MySqlConnection
    Dim COMMAND As MySqlCommand

    '*******************************************************************************
    '* Stop polling when the form is not visible in order to reduce communications
    '* Copy this section of code to every new form created
    '*******************************************************************************
    Private NotFirstShow As Boolean

    Private Sub Form_VisibleChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.VisibleChanged
        '* Do not start comms on first show in case it was set to disable in design mode
        If NotFirstShow Then
            AdvancedHMIDrivers.Utilities.StopComsOnHidden(components, Me)
        Else
            NotFirstShow = True
        End If
    End Sub

    '***************************************************************
    '* .NET does not close hidden forms, so do it here
    '* to make sure forms are disposed and drivers close
    '***************************************************************
    Private Sub MainForm_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
        Dim index As Integer
        While index < My.Application.OpenForms.Count
            If My.Application.OpenForms(index) IsNot Me Then
                My.Application.OpenForms(index).Close()
            End If
            index += 1
        End While
    End Sub

    Private Sub DataSubscriber21_DataChanged(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber21.DataChanged
        Dim estacao, alarme As String
        Dim datahora As Date = "2007-01-02 09:10:00"
        datahora = Now
        'datahora = datahora1
        estacao = "Estação 1"

        MySqlConn = New MySqlConnection 'Create the object
        'Parameters to connect to the database
        MySqlConn.ConnectionString =
        "server=localhost;userid=vitor;password=mypassword1977;database=teste_db"
        Dim READER As MySqlDataReader

        If e.PlcAddress = "Inp_1" Then
            alarme = "Entrada 1" + " " + e.Values(0)
        End If

        If e.PlcAddress = "Inp_2" Then
            alarme = "Entrada 2" + " " + e.Values(0)
        End If

        If e.Values(0) = "True" Then
            Try
                MySqlConn.Open() 'Open the database
                Dim Query As String
                Query =
                "insert into teste_db.alarmes (data_hora,estacao,alarme) values ('" & datahora & "', '" & estacao & "', '" & alarme & "')"
                COMMAND = New MySqlCommand(Query, MySqlConn)
                READER = COMMAND.ExecuteReader
                'MessageBox.Show("Data Saved")
                MySqlConn.Close()

            Catch ex As MySqlException
                MessageBox.Show(ex.Message) 'In case of error show the error
            Finally
                MySqlConn.Dispose() 'Destroy the object
            End Try
        End If
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        MySqlConn = New MySqlConnection 'Create the object
        'Parameters to connect to the database
        MySqlConn.ConnectionString =
        "server=localhost;userid=vitor;password=mypassword1977;database=teste_db"

        Try
            MySqlConn.Open() 'Open the database
            MessageBox.Show("Connection Successeful")
            MySqlConn.Close() 'Close the database
        Catch ex As MySqlException
            MessageBox.Show(ex.Message) 'In case of error show the error
        Finally
            MySqlConn.Dispose() 'Destroy the object
        End Try
    End Sub
End Class

[condore89]

Hello Archi and Vitor.

Thanks guys, now I can write the value of my PLC tag in my DB. I just adapted Vitor's code to my needs.
Next step for me will be to do the opposit, change in the DB => change the tag value. I'll start working on that shortly and will sure come to give you the result.

[Phrog30]

Hello Archi and Vitor.

Thanks guys, now I can write the value of my PLC tag in my DB. I just adapted Vitor's code to my needs.
Next step for me will be to do the opposit, change in the DB => change the tag value. I'll start working on that shortly and will sure come to give you the result.

How would the value in the DB change?  If it's only by a form in your app, then I would simply write to the PLC at the same time you are changing the value in the DB.  If it changes other ways then I am curious how you do it.

James

[lunenburger]

Hi Condore,  Would you still have  a copy of the changes you made to Vitor's code? I am trying to write Controllogix PLC data to a MYSQL Database and it is not connecting.
Any help would be greatly appreciated....