How To Set Up SQL Function Blocks of Omron NX102-1020 with Microsoft SQL Express 2019

Hello everyone, today I will illustrate how to use the function blocks of Machine Automation Controller. NX102-1020 to connect to the Microsoft SQL Express, and create a mapping, and also insert the data from the PLC to the database. I am Tom Trinh, Senior Application Engineer for Valin Corporation. Let's get started.

So what I have in front of us right now is the program that already created in the Sysmac Studio, and also I already created the database connection for this. And I have previous video that shows how to set this up. But just real quickly, if I click on DB connection one, and I'm going to go online. I can test my connection for the database and make sure it's connected OK by clicking communication test. And it's said test OK. That means I am connecting to the database.

So I'm going to show you the program now. So in this program, we have these function blocks that we use. The first one is DB_Connect and that basically allowed the database to connect to the SQL. And you notice for the DB_Connect, the variable the tag that used to connect to the DB Connection name is this DB Connection 01 that I created over here. Also on this one I have a connectionname as a tag that tight to that DB connection. So once the database is connected, this is what we're going to use to create a mapping. In this example, this program I have two tables that I created here. So that's why I have these two function blocks. You can have more than one table. This table I name it cookies. This table I name it Process. In this function block, you notice also I use the tag, connectionname, and that  tight into the DBconnection here. The cookies is the name of the table. The Mapvar is the Tag. It is actually a global variable that has a structure in it. In this structure, this is where you specify what variable that you created in the table in the database.

So let's take a look at one of them which is SQLinsert. So if you look at the global variable, SQLinsert tight to the Myinsert structure.  So how is this structure look like? I'm going to go to data type. And under the data type, you see that my structure is Myinsert. And I have the first element is the name. That is basically a string. And in next 3 elements are flour, water and eggs, and the data type is integer. As I mentioned before, I can create another table so I have my second structure here and it's Myinsert2 and I have the Process as the name and it is a string and then I have a temperature and the pressure that I want to log as well and those are integer.

That's how the structures are created. I'm going to go back to the program. So we discuss about the SQLinsert for the Mapvar. For the DB create mapping you also notice here the SQLType. So we use the _eDBC_SQLTYPE#_DBC_SQLTYPE_Insert. This is a system variable and because I am creating a mapping so that's the system variable I need to use. So that's how you set up the DB create mapping. Now this DB insert function block is basically allow you to insert the data from the PLC into the database for the SQL in this example every time you trigger this contact here. So how do we set it up.

For DB insert function block, you again using the tag name from the DBconnection which is the connectionname. And then, for the MapVar, you basically using the structure that I mentioned above which is SQLinsert for this one. The second table is SQLinsert2. So that's how you set it up. When you finish the database, you want to close it, then this DB_Close function block basically closed the connection.

Let's start putting some data in here and then see how it appear on the Database. At this point, let me launch the DataBase, the SQL  Management Studio. So let me do that.

What I'm going to do next is I'm going to launch the Microsoft SQL Server management as an administrator. Also, one thing to note is that you want to make sure your firewall is disabled so that it will work correctly.

Here is the SQL Server Management Studio. Alright, so this is the NJSQLExpress. I'm going to go ahead and click Connect. And I'm connecting. So I'm going to go to my database. That's my NJ database. As I mentioned, I have two tables in here, Cookies and Process. As I showed you earlier. So I'm going to launch the Cookies first. I’m going to right mouse click and edit top 200 rows. This is what I have in there already. What I'm going to do is to go to my PLC. And for my Cookies, I'm going to say Bagel. I'm going to give it a value 123. This one I'm gonna say 456. I need to hit enter. Make sure it's there. And this one is 789. So that's for my Cookies and then for my Process I'm going to say Test 1. Temperature would be. 109. Pressure would be 45. Alright, so these are the data that I have. OK, So what I want to do next is I'm going to select this input. Turn it on. It's connected to database, and if it's successfully connected you will see a value 1 here under the Connectionname. So I've done that. So the next thing I want to do is create a map.
So I'm going to do this. Turn it on. I'm going to do the same thing for my Process as well. OK. At this point, I'm going to go ahead and click insert. As soon as I click insert the data from the Cookies and the Process will be transferred to the database. I'm going to go ahead and do that.

Alright, now if I go to my database and if I right mouse click here and Execute SQL, you will see the data will appear down here you will see Bagel with water 123, Flower 456, and  Eggs 789. If I go to my Process. And select the edit top 200 row. There you go. It's already appeared because I just opened it up. It will show test1 of temperature of 109 pressure of 45. I'm going to do one more. I'm going to go back here and change it to Process 2. And this time I'm going to run it temperature at 99. And this one is at 35, let's say. I'm going to click this insert. I go to my Database. There you go. Process 2 has a temperature of 99 and pressure of 35.

That is how you program the Omron Machine Automation Controller NX102-1020 that has a database feature with these function blocks ready to go to connect to the database and insert the data. If you have any questions, please call (855) 737-4716 or fill out our online form.