Structured Query Language (SQL) is one of the most common languages for database management systems and can be an extremely powerful tool. If you’ve ever wanted an easy way to push data to an SQL server, this Tech Tip will show you how using Crimson 3.1 devices.
Step 1: Set up the data log and SQL Server
For this Tech Tip, we’ll use one Red Lion’s new DA30D Data stations. The first thing we need to do is set up the actual data we want to push to and existing SQL server. In this example, we’ll create a program which generates a random number from 0-100 very tick. The program uses the Random function and stores the result in the tag my_number. Next, we create a data log which contains this number.
Now that we have set up the data we are going to log, we need to set up the SQL server. Click on SQL Sync under the Services menu in the Navigation Pane of the Communications window.
Put in the IP address and credentials for the SQL Server, as well as the database name. It is important to note that the Database Name must match your existing SQL database, in this case SQLTip. Next, we need to add the log we made earlier to the server, by clicking Select Log Directories. You can set up automatic syncing but, in this case, we are going to turn on Manual Sync.
Step 2: Create a Sync button and Feedback
Now that the data and communication is set up, we’re going to need to make a button on our display page to manually sync data, and we’ll add some feedback as well.
There are 4 functions we are going to make use of:
IsSQLSyncRunning - Returns whether the SQL Sync Service is currently attempting to synchronize with an SQL Server.
GetLastSQLSyncStatus - Returns the status from the last time that the SQL Sync Service attempted to synchronize data logs with a SQL server.
GetLastSQLSyncTime - Returns the last time that the SQL Sync Service synchronized with a SQL server since the system started up.
ForceSQLSync - Forces the SQL Sync service to run immediately and transmit log data to the configured SQL Server.
Here we have a simple display page which has the number we are pushing to the server, if the SQLSync is running, whether the Sync was successful or not, and 3 last sync times (last start, last success, last failure). There is also a button which has the ForceSQLSync function as the action.
Once we press the button, the SQLSync is initiated, and we will see the Sync running, then if all goes well, a successful sync status and last sync time will be displayed.
After using the DA30D’s web server to press the sync button, the last sync status has been changed to 1, indicating a successful sync, and the last start and last successful sync times have been updated.
We can double check the data was pushed to the server by either checking directly on the server or running a query in excel.
And that’s it! Now you can push any data you like to your SQL server from your Crimson 3.1 devices.