SQL Queries on Crimson 3.1 Devices

Structured Query Language (SQL) is one of the most common languages for database management systems and can be an extremely powerful tool. A Query is the process of extracting information from an SQL database, and in this Tech Tip we’ll show you how to do it using Crimson 3.1 devices. 

STEP ONE: SET UP THE CONNECTION TO THE SQL SERVER

The first thing we need to do is tell our Red Lion device which SQL database we want to query data from. We’ll use the database created for a previous Tech Tip on Syncing data to an SQL server which involved generating a random number and storing it in the database.

In the SQL Queries menu, click on SQL Queries to bring up the options.



Set Enable SQL Queries to yes and 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.

STEP TWO: CREATE A QUERY

Next, we need to create the query to extract specific data from the server. Right click SQL Queries and create a new query, which will also create a column and a row below it. In this case, we’ll query the first 10 rows of the database which has a column called number in the table LOG1_Log1.



The database rows have been mapped to internal tags 1-10 so they can be used within Crimson easier. We’ll set the Query Mode to On Demand for this example, since we’ll be manually querying, but you can set it to Periodic as well.

In the column configuration, we need to specify which column we are looking at, and what data type the variables are. It is important that the SQL Type matches what is in the database, otherwise the query will fail.



STEP 3: CREATE A QUERY BUTTON AND FEEDBACK

The last thing we need to do is display the queried data and add a bit of feedback. Set up a display page which has:
  • A GetSQLConnectionStatus instance – Tells us the status of the connection to the SQL Server. 0 meaning pending, 1 meaning successful, and 2 meaning failed;
  • A GetQueryStatus instance – Tells us the status of the query being run. Returns 0 if pending, 1 if successful, 2 for partial data retrieved, and 3 for a failure;
  • The data tags which have been mapped to the queried rows, and
  • A button which activates the RunAllQueries


As a note, the GetQueryStatus function needs to be passed the query name as an argument. In this case the function would be GetQueryStatus(“Query1”);

Once this has been set up, all that is left to do is to test it.



The picture on the left/top (depending on your screen) shows the data in the SQL Server, having run a query within SQL. The picture on the right is our Red Lion device which is showing a successful connection to the server, a successful query, and the data tags. We can see that the correct data has been queried by simply comparing the two.

That’s it! Now you’ll be able to set up and run SQL queries from your Crimson 3.1 devices. Once you have the data within Crimson, you can do any number of things such as incorporating it into programs and more.

For more information please contact us! Call 1800 557 705 or email sales@controllogic.com.au

Contributors: