How to connect a Siemens S7 1200 PLC to a SQL Server Database

hi i'm chris from SQL for automation in this tutorial video i'm going to show you how you can connect the Siemens PLC to an SQL database using our SQL for automation connector and with the connection we will be able to send and receive data execute stored procedures and do basically everything you could also do using a normal community to the database the only additional thing you'll need is our connector service which you can run on the computer weather server is running or on a different device in the same network so yeah let's start if you want to follow along I recommend you install the connector and server first you can watch your first video to see how I install the connector just make sure to select the correct PLC type and our fifth video to see how I install the server to show you how the connection works I'm going to use an example project which you can download on our website or from a link in the description to download the project from the website you can go to SQL for automation comm click on download select your PLC fill out the form click on this link and you will receive the download link via email and alongside the example you'll also get our connector software now the project is written in t of 13 but I'll use the version 15 instead so I'll start the portal first and then open the project from there portal should now automatically detect that we are using a newer version and upgrade project and then after it's open we can go to our project view and we should find this layout my setup is a little bit different than the one in the example project so as a first step I want to change the device configuration to do it I can open this folder and go to device configuration to see the setup now I don't have this object here so I can delete it and since I'm using a different PLC I select this one click on change your eyes then search the one I'm using and click ok next I'm going to assign an IP address to my device there are a few different ways in which you can do this one way is by going to online access and selecting the network interface for which you are connected to the PLC then we double click on update accessible devices and there we go here's our PLC next we can double click here go to functions and assign IP address then in here I can enter the IP address I want the PLC to have enter the default subnet mask and assign it now I also need to change the IP address of my computer and that's because we need to be in the same subnet as the PLC in order to connect to it one way to do this is by going to our network settings change adapter options select the correct network device go to properties Internet Protocol version 4 properties and changing these numbers to be the same as in our PLC except the last one now let's also change the IP address in the project go to the vise configuration double-click the Ethernet port and change these values to the address that shows before then I can right-click on my CPU go to compile and rebuild the hardware then we click here again select download download hardware and then here I select my interface type the interface and the slot I'm using and start to search for my device there we go here's not PLC so I can load check the settings and click load again and just like that we've configured our device in our program structure we do have a few different elements some of them are necessary for the connection to work and shouldn't be changed and some of them are examples to show you how can set up the program now at the base we have our main organization block which continuously gets called by our operating system for example every millisecond and then this cyclic interrupt lock which gets called every 10 milliseconds so the way it basically works is that in our main block or let's say our user program we put together a request string and store it in a global data block and then every 10 milliseconds this cyclic interrupt block will process the data and depending on the data communicate with the connector and the only thing we have to provide for that are some parameters like the IP and ports and so on so the way you put together your user program is up to you you can directly call these sfra functions from the main block if you want now we don't recommend that and we've put together a few example programs which you can use as templates or to test the functions so let's take a look at them if we open our main block we will find well but much as you can see it's just calling these four functions now each of these functions corresponds to one example we've prepared and we do have two examples each written in SCL and ladder so in total for functions they are being called one after the other now let's just look at the first function if you open up this first group here we will find our function and alongside it for function box and for instance data blocks now each of these function box corresponds to one of our basic SQL queries to select insert delete and update and each function block has an instance data block assigned to it in which they can store values so our main block is calling this function so let's open it up now in here we have four main sections and we can see that each section corresponds to one of these queries now in each of these sections we call an instance of our function block and by that assign data to its corresponding data block and if we take a closer look we can see that it's the same data every time so what is this data the data is coming from this global data block which you can find in here and it's stored in this custom data structure and if we go to plc data types SQL for Siemens we can find the definition of the structure and the variables it contains like for example the execute interface and the request and response data so each of our function blocks is accessing the same data you can just read but also change it so just like before I call in the instance of the function block we are executing the code of that function block so let's open one up now normally every time this function block gets called nothing much happens it's just going to go through this idle state and then exit but as soon as you set this execute variable here to true and we'll go to the next state and then depending on the transitions for each of these states one cycle at a time or maybe longer if it's waiting for something now by calling the SQL for automation functions this block will put together a select string wait for the response and then parse it and finally reset and get back to its idle State now our main goal is to connect our PLC to the database and we're going to do that with the SQL for automation connector now the connector will take the request from the PLC and send them over to the database so as a first step I'm going to connect the connector to the database to do that I open the configurator connected to the collector create a new link enter a name and very important because I'm using Siemens make sure that I select Siemens as my target type next I'll select the IP of the connector the port and then finally the data source I want to use if you don't know how to set up one you can watch our fifth video then we click OK activate the test license and we should have our connection so now the only thing missing is the connection from the PSC to the connector so in order to do that we need to tell our PLC where the connector is running and which link we want to use so let's go back to our PLC and open the cyclic interrupt block now in here I can tell my PC everything it needs to know I can enter the connector IP and connector port gonna check if the hardware ID is correct and you can find it by going to device configuration selecting the device you're connecting to system constants and it should be listed here now in some cases you might need to change some of these other settings as well but most of the time it should work like this just one setting I want to point out is this one here if you remember from before our PLC is continuously cycling through the main OB but every 10 milliseconds it gets interrupted by the cyclic interrupt block that's where as for a function block is and it won't return until after it's finished with that one so if you don't have any requests at the moment it won't take long and switch between these two blocks is regular intervals but now every time we do send a request the PLC will stay longer at the S for a block for example 22 milliseconds and during that time our main program won't be able to process anything so if it's for example waiting for a sensor and that sensor gets triggered directly after we go to errors for a function block it won't be able to detect that sensor until after we're finished so we can have a delay of 21 milliseconds in here and this is where our setting comes into play to minimize the delay we can set this parameter to the 5 milliseconds for example and with that error as for a block we'll only be allowed to work for 5 milliseconds at a time so this delay will then never be above 5 milliseconds but on the other hand it will of course also take longer to process the request so yeah you might have to find a compromise between these two so let's right-click our CPU compile software rebuild all and then again download the device software all then load tell at the start to CPU and we're done to test the connection we can use the examples in our project and an easy way to do it is by opening one of these watch tables we prepared so I'm just gonna open the Select example and then to send the command we go online and monitor the values and as you see our program says it's ready so that's good now we can start it by setting the execute variable here to true and you can type in one to prepare the value and modify it afterwards or you can use right click and modify value to one or you could use this shortcut which is the fastest way and here we go here's the data from our database now to verify that we really are accessing our database I'm going to start managing studio and then I connect to the server open the sample database table and should see this data now using my normal typing speed I can type in a request which will edit the text of the last entry and we can see if that works by refreshing list it did and then when we send our select request again we should see this change which we do now what else can we do we could also change the data using your PLC but maybe first let's create a backup table to do this I'm gonna right-click our table and create a new one based on it as you can see I can't with this logon so I'll connect to the server using another logon then that just do the same thing again execute it and after refreshing I should see my new table now this table won't have any data in it yet so I'll also have to copy the data from our old table to do this I'm going to use the insert query which looks like this and change it so it inserts into the new table all the values from the old one then we can execute and we should have our backup table okay now we're prepared to make changes to the database from the plc we can use an update example here we can change the parameters and we could execute it but we don't really know what it's going to do now right so before that we should maybe first take a look at what it's going to send now if you go to our function block we can see how our request will be put together and you see it's gonna update the table one and set the parameter one to parameter 1 for M 2 2 to parameter 2 and so on where the ID is below the ID we give it so yeah if I have it set at 290 it's gonna update every entry below 290 and that's probably not what we want instead we update all the entries and see what happens okay maybe there was an our best idea we kind of destroyed our database but since we do have a backup we can get our data back from there I just have to delete this stuff first and guess what we can do this with our PLC just open the delete example modify this parameter and as soon as we execute it our data should be gone but first let's check the syntax really quick we can see in here that it's going to delete everything from a table one where the ID is below the ID we give it and because I've set this parameter way above 300 it should delete everything so let's execute it there we go now to get the day back we just do the same thing we did before with the backup right-click our backup table and then we want to insert into our old table all the data from a backup table so now I could just execute this and be done but what if this happens again in the future another way to do it would be by creating a store procedure out of this and these can make our program a lot more flexible because after setting them up you can easily make changes to the queries without changing any of the PLC code but I'll leave that for another video so if you don't want to miss that you can subscribe to our channel and if you have a question about this video or something else you can just write it in the comments below anyway I hope this was helpful thanks for watching and I'll see you in the next tutorial [Music]

Channel: SQL4automation

Views: 67,601

Keywords: PLC, SPS, SQL, Database, Automation, SQL4automation, Inasoft, Microsoft SQL Server, Siemens, S7, S7-1200, Datenbank, Datenbankanbindung, Database connection, IoT, I4.0

Id: 52Rf5HGVZQk

Length: 14min 55sec (895 seconds)

Published: Wed Oct 02 2019