AdvancedHMI Software
General Category => Support Questions => Topic started by: Justinb94 on August 10, 2020, 03:40:39 AM
-
Hi guys,
I am wanting to like a data subscriber to SQLite. Has anyone done this? Or know how to link a data subscriber to a SQLite data base.
Thanks.
-
Lots of examples on this site alone.
https://www.advancedhmi.com/forum/index.php?topic=2607.msg15649#msg15649
https://www.advancedhmi.com/forum/index.php?topic=2549.msg15388#msg15388
https://www.advancedhmi.com/forum/index.php?topic=2501.msg15079#msg15079
There are two parts to this, getting the data from the PLC via datasubscriber, then sending this to SQL. The code to send to SQL is similar across different database types. There are a ton of examples online.
-
I have managed to write the data from the data subscriber into a SQLite database. I found a very helpful video that explains this step.
https://youtu.be/QFT4cBXZ0kU
Now I wish to graph the two columns in the data base (one column for the x value and one column for the y value). How do I port the data from the SQLite database into a chart control?
-
Again, lots of info on the web, just need to spend some time searching.
https://www.youtube.com/watch?v=h3nHrZxDub8
https://stackoverflow.com/questions/22093979/vb-net-chart-controls-and-sql-server
https://stackoverflow.com/questions/17288682/how-to-plot-a-line-graph-by-reading-the-values-from-database-sql-in-vb-net
-
Essentially I have 52 data base entries week (x value) and pump run hours (y value) so I wish to have 52 columns on my graph. However when I load the graph I only have three columns.
This is my rough testing code. Can anyone see where I am going wrong?
Dim myconnection As New SQLiteConnection("Data Source=C:\test.db")
myconnection.Open()
Dim cmd As New SQLiteCommand
cmd.Connection = myconnection
cmd.CommandText = "Select * from test01"
Dim rdr As SQLiteDataReader = cmd.ExecuteReader()
Dim dt As New DataTable
dt.Load(rdr)
rdr.Close()
Dim dat As Integer
dt.Rows.Item(0)("date") = CInt(Dat)
Dim inte As Integer
dt.Rows.Item(0)("int") = CInt(inte)
Chart1.Series(0).XValueMember = Dat
Chart1.Series(0).YValueMembers = inte
Chart1.ChartAreas(0).AxisX.Minimum = 1
Chart1.ChartAreas(0).AxisX.Maximum = 52
Chart1.DataSource = dt
-
Download one of the free browsers for SQLite, here's one:
https://sqlitebrowser.org/
Open your database and run your query, Select * from test01. Post a screenshot of the results.
SQLite is a bit different as it doesn't really care about data types, you can pretty much throw any value into any data type. But, at some point you will want to get a datetime for X and value for Y.
If you are wanting a specific range you will need to have this part of the query. Something like "Select * from test01 WHERE t_stamp BETWEEN StartTime AND EndTime", where t_stamp is your datetime column.
Get the query correct first by testing in the browser, then simply load into chart. In this case, what you see is what you get. If you are going to miss data, filling and interpolation is going to be up to you
-
Do some research on how to setup your chart, here's some good info to help:
https://www.syncfusion.com/kb/71/how-to-work-with-date-in-chart-axes
https://help.syncfusion.com/windowsforms/chart/chart-axes?cs-save-lang=1&cs-lang=vb
-
I re-typed my code and fixed the issue. My chart is now doing working correctly. Thanks for you help!
-
I am using Datasubsciber2 to monitor 3 tags from a plc, I then write them three values to a SQLite db with accompanied buy a timestamp column. However when I check the db, instead of writing all three values into the same row it generates a new row for each value. Any clue on how I get it to write all the data required to a single row, have I made an error in my code? See attached a screenshot of the db.
Dim tstamp As String = DateAndTime.Now
Dim hrnow As Integer = DateAndTime.Now.TimeOfDay.Hours
Dim p1speed As String
Dim p1amps As Integer
Dim p1temp As Integer
If Not System.IO.File.Exists(fullname) Then
Dim ct As String = "CREATE TABLE Trends (tstamp Text,p1speed Integer,p1amps Integer, p1temp Integer);"
Using sqlconn As New SQLite.SQLiteConnection(cs)
Dim cmd As New SQLiteCommand(ct, sqlconn)
sqlconn.Open()
cmd.ExecuteNonQuery()
End Using
End If
If e.PlcAddress = "400201" Then
p1speed = e.Values(0)
End If
If e.PlcAddress = "400202" Then
p1amps = e.Values(0)
End If
If e.PlcAddress = "400103" Then
p1temp = CInt(e.Values(0))
End If
Dim myconnection As New SQLiteConnection(cs)
myconnection.Open()
Dim cmdd As New SQLiteCommand
cmdd.Connection = myconnection
cmdd.CommandText = "Insert into Trends (tstamp,p1speed,p1amps,p1temp) VALUES(@tstamp,@p1speed,@p1amps,@p1temp)"
cmdd.Parameters.AddWithValue("@tstamp", tstamp)
cmdd.Parameters.AddWithValue("@p1speed", p1speed)
cmdd.Parameters.AddWithValue("@p1amps", p1amps)
cmdd.Parameters.AddWithValue("@p1temp", p1temp)
cmdd.ExecuteNonQuery()
myconnection.Close()
-
Because your query is telling it to create a new row, that's the point of insert.
https://www.w3schools.com/sql/sql_insert.asp
You want to use update.
https://www.w3schools.com/sql/sql_update.asp
Notice on update you will need a where clause, which will probably be the primary key. So, you will need to know that. If that Id changes the query will not work.
Edit, oops, I read your post wrong. You want a new row, just not getting all values at the same time.
What is the trigger for insertion? You will need to read each of the tags before you try to insert.
-
Yes, essentially I would like to have one row per one second time stamp that contains 3 other columns of values. Where as currently there three rows being generated for each one second time stamp. I am un-sure what is causing this is happen. What are insertion trigger are you referring to?
This is the full code snippet under the data returned event handler
Private Sub DataSubscriber21_DataReturned(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber21.DataReturned
Dim tstamp As String = DateAndTime.Now
Dim hrnow As Integer = DateAndTime.Now.TimeOfDay.Hours
Dim p1speed As Integer
Dim p1amps As Integer
Dim p1temp As Integer
If Not System.IO.File.Exists(fullname) Then
Dim ct As String = "CREATE TABLE Trends (tstamp Datetime,p1speed Integer,p1amps Integer, p1temp Integer);"
Using sqlconn As New SQLite.SQLiteConnection(cs)
Dim cmd As New SQLiteCommand(ct, sqlconn)
sqlconn.Open()
cmd.ExecuteNonQuery()
End Using
End If
If e.PlcAddress = "400201" Then
p1speed = e.Values(0)
End If
If e.PlcAddress = "400202" Then
p1amps = e.Values(0)
End If
If e.PlcAddress = "400103" Then
p1temp = CInt(e.Values(0))
End If
Dim myconnection As New SQLiteConnection(cs)
myconnection.Open()
Dim cmdd As New SQLiteCommand
cmdd.Connection = myconnection
cmdd.Parameters.AddWithValue("@tstamp", tstamp)
cmdd.Parameters.AddWithValue("@p1speed", p1speed)
cmdd.Parameters.AddWithValue("@p1amps", p1amps)
cmdd.Parameters.AddWithValue("@p1temp", p1temp)
cmdd.CommandText = "Insert into Trends (tstamp,p1speed,p1amps,p1temp) VALUES(@tstamp,@p1speed,@p1amps,@p1temp)"
cmdd.ExecuteNonQuery()
myconnection.Close()
End Sub
End Class
Can you see anything wrong with this code?
-
This is the full code snippet under the data returned event handler
If e.PlcAddress = "400201" Then
p1speed = e.Values(0)
End If
If e.PlcAddress = "400202" Then
p1amps = e.Values(0)
End If
If e.PlcAddress = "400103" Then
p1temp = CInt(e.Values(0))
End If
That is your problem. The event returns one a time, you are then picking them out one at a time. You will need to loop through and get all of the values before you insert, or simply do a read from the PLC before you insert. I believe something like this would work:
For i = 0 To e.Values.Count - 1
If DataSubscriber21.PLCAddressValueItems(i).PLCAddress = "400201" Then
p1speed = e.Values(i)
End If
If DataSubscriber21.PLCAddressValueItems(i).PLCAddress = "400202" Then
p1amps = e.Values(i)
End If
If DataSubscriber21.PLCAddressValueItems(i).PLCAddress = "400103" Then
p1temp = e.Values(i)
End If
Next
-
What are insertion trigger are you referring to?
The trigger you are using to insert the data. I usually use data change, not returned, and the data change is a counter style trigger in the PLC. So, if I want once a second, I have a counter increment once a second. AHMI sees this change and I do something with it. If I want based on an event, I still increment the counter based on the event.
-
Yes my insertion trigger is data returned which is linked to a one second modbus driver. I tried your suggested code for no success. I still get three rows per time stamp with incorrect data being inserted
-
I would do what I suggested in my last post.
I'm not too familiar with how the data return event works. From your results, it looks like it fires for each address in the collection.
-
I put my insert statement under a one second timer tick event, which has resolved my issue.