This example shows how to logging OPC data in to MS SQL Server 2014 Express using COPC32 and Visual Studio 2015 Express.
Requirements
- MS SQL Server / MS SQL Server Express
- Visual Studio 2015 Express (download here)
- COPC32 (it is not free, download trial here). And you have to installing COPC32.
- OPC Server
My database name and table’s column shown below. “id” column is auto increment.
MS SQL Server, instance name shown. It is MS SQL Server on same PC of my Visual Studio. Then I can use “(local)” as reference name in script. If your instance name is something like “ACER\SQLEXPRESS”, then you could use “(local)\SQLEXPRESS”.
Download OPC Data logging example project (135kb)
Open downloaded project and make sure you have insert COPC32 control on Toolbox of Visual Studio.
Three label used to show OPC tags value with Timer2 every 1 sec. Timer1 has logging script working every 5 sec.
Specify OPC Server on COPC32 ‘s property page.
As well as OPC tags.
In Timer2’s code, getting OPC data into global variable v(0) to v(2) (see downloaded example code, will see global variable declared). And also show data on labels.
Private Sub Timer2_Tick(sender As Object, e As EventArgs) Handles Timer2.Tick
For i = 0 To 2
v(i) = Axcopc1.GetVl(i)
Next
Label1.Text = v(0).ToString()
Label2.Text = v(1).ToString()
Label3.Text = v(2).ToString()
End Sub
I have use shell to call SQLCMD.exe with SQL command to insert OPC data in to MS SQL table.
Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
Shell(“C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.exe -S (local) -d test -Q “”insert into t1 (v1,v2,v3,Time_Date) values (” & v(0) & “,” & v(1) & “,” & v(2) & “,getdate())”””)
End Sub
When argumment –S = Server Name (Please note that if your SQL Server has “\SQLEXPRESS” after computer name then you need to use (local)\SQLEXPRESS ) , –d = Database Name, –Q = SQL query/command. Example above use SQL Insert command to put v(0), v(1), v(2) and current date time into table t1 at related column.
You have to checking path of SQLCMD.exe in your system and replace to above script.
After running project, OPC data logged into MS sQL Server.
