OPC UA to SQL with FactoryTalk Optix

Contents

1. OPC UA Client

2. Writing to an SQL database

3. Creating a OPC UA to SQL converter

1. OPC UA Client

Let’s create a new OPC Client

https://www.rockwellautomation.com/docs/en/factorytalk-optix/1-00/contents-ditamap/using-the-software/opc-ua/manage-the-opc-ua-client.html

Change the address and port of the OPC UA server

For instance with FT Kepserver Enterprise, port number is 49370

Right click to add a new OPCUA Tag Importer

Then double click on it

Click on the Button to go online with the OPC server

Click on apply

On Objects you will see this

Now let’s create a texbox that points to the data

That’s all

Let’s add a label

2. Writing to an SQL database

Let’s write to an SQL database from Optix

First just read from a Database

https://www.rockwellautomation.com/docs/en/factorytalk-optix/1-00/contents-ditamap/using-the-software/datastore-database.html

Add a Database object

Be sure to have an SQL working database, with user, password, access thru TCP/IP, etc.

Complete database properties click on Table + and Column + to match our Database

If you do not add a column here you will have errors when drag and drop to the Table1 to the data grid (Unable to insert a empty table to the datagrid)

Display Database data

https://www.rockwellautomation.com/docs/en/factorytalk-optix/1-00/contents-ditamap/using-the-software/datastore-database/display-database-table-data.html

Just click on the + on Tables to create a new table (even thought it already exists on the MS database)

Do not forget to add a column minimum

Drag and Drop

It works!!

Now let’s try to write a variable on the database

Using this example

https://www.rockwellautomation.com/docs/en/factorytalk-optix/1-00/contents-ditamap/developing-solutions/application-examples/logger-tutorial/develop-a-data-logger-with-odbc-data-store.html

Download a sample project: [DataLoggerODBC.zip](C:\Program Files\Rockwell Automation\FactoryTalk Optix\Studio\Help\en\downloads\DataLoggerODBC.zip)

https://www.rockwellautomation.com/content/dam/dita/en/factorytalk-optix/1-00/downloads/DataLoggerODBC.zip

To see how it is made.

But let’s start creating a complete new project

For instance sql_datalogger_optix_v2

But first let’s create a database without a Table (Optix and The Datalogger function will create the Table)

For instance let’s create a new database Optix2

Just give a name, that’s all

There are no user tables

Now Let’s go to Optix

Add a variable to work with

Add a Slider to enter values to such variable

Next create the Database instance on Optix

Point to the existing database, but do not create any Table on Optix

Then create a new DataLogger

And select the variable to log and the destination, just the database

If we now open the ODBC object, surprise, a new Table an columns have been created for us

If we take a look at the database:

You have to disconnect and connect again to see the changes

Still nothing

Until you run the Optix Runtime

Give some values to the gauge dropping the index

Then go to the Database, and …voilà

Now let’s look at the data, this is working fine

Now let’s add a Datagrid on the Optix Project

Drag and drop the table to the Datagrid

Let’s test it. It works! That’s all

3. Creating a OPC UA to SQL converter

Take last SQL project and delete the gauge.

Then add the OPC UA client like in previous sections.

Now we have access to IoT_data Tag

First of all test that we are reading OPC UA data

Yes

Then let’s erase the data on the database from previous chapter.

Verify that the data is erased from Optix

Now just change the source of data on the datalogger Object

Let’s test The Optix Runtime

It takes a while until the values are logged. I have also restarted the OPC UA server.

Clear the Table again

First time we see nothing on the Optix runtime datagrid window

Let’s close the runtime and open it again

Now it works

Let’s put an autorefresh to the datagrid and sort ascending, descending to see it live!

As you can see on this video

https://www.youtube.com/watch?v=irL_CHUZaHY