DMC worked on a project where we wanted to log data to a local Microsoft SQL server to be able to save and display it using Microsoft Report Designer. However, the standard format of Siemens’ data log did not match up with the schema we wanted to use in our SQL table. Not all projects require a full PC Station, there is also the option of using a Comfort Panel and logging data as a CSV file to the onboard USB drive or SD card slot. For a walkthrough of that method, visit my colleague Caleb Stoll’s blog.
We first had to set up the SQL database so that it could communicate with the PLC following the handy Siemens guide here. Once the connection to the database was set up, it was time to design the code, which consisted of four main components.
1. Connecting to the Database
Dim conn, rst, SQL_Table
On Error Resume Next
Set conn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
'Open data source
conn.Open "Provider=MSDASQL;DSN="&Database_Name&";" & "Trusted_Connection=yes;"
'Error handling
If Err.Number <> 0 Then
ShowSystemAlarm "Error #" & Err.Number & " " & Err.Description
Err.Clear
Set conn = Nothing
Exit Sub
End If
ADO stands for ActiveX Data Objects, which is Microsoft’s standard way of interacting with relational databases. We created a Connection object to connect to the database as well as a Recordset object to handle the results from our command in part 3. Find more information about ADO objects here.
Once the objects are created, we can open the connection to establish our link to the database. Note that we have ‘trusted_connection=yes’ in the Open command as the SQL server is configured to use Windows Authentication, as described in the Siemens guide above. For other authentication methods, the Open command may need different fields and parameters. Note that the name of the database is parameterized in the code to provide flexibility for deployment.
2. Defining the Data Record
'Define data record
SQL_Table = "INSERT INTO "& Table_Name & " VALUES ('" &_
"dbDevices_AIs_Isolator_Inlet_HEPA_Pressure.rValue', '" & CStr(Date) & " " & CStr(Time) & "', " & SmartTags("dbDevices_AIs_Isolator_Inlet_HEPA_Pressure.rValue") & ")," &_
"('dbDevices_AIs_Isolator_Exhaust_HEPA_Pressure_1.rValue', '" & CStr(Date) & " " & CStr(Time) & "', " & SmartTags("dbDevices_AIs_Isolator_Exhaust_HEPA_Pressure_1.rValue") & ")," &_
"('dbDevices_AIs_ColdWFITemp.rValue', '" & CStr(Date) & " " & CStr(Time) & "', " & SmartTags("dbDevices_AIs_ColdWFITemp.rValue") & ")"
The data record is defined in standard MS SQL syntax, in this case using an INSERT statement as we want to log data in the database. We’ve parameterized the table name as well in case we want to log to different tables.
We’ve chosen several tags that we want to log each time the script is run and made sure to format the INSERT statement so that they each get logged as an individual row. Be wary about the date formatting; we found that the easiest option was to log it as a string and handle the conversion to the SQL TIME type in the backend.
3. Inserting the Data Record
'Insert data record into the table
Set rst = conn.Execute(SQL_Table)
With the connection set up and the data record defined, inserting it into the table is easy using the Execute command!
4. Closing the Connection
'Close data source
conn.close
Set rst = Nothing
Set conn = Nothing
Finally, we can close our connection and clear our connection variables.