Configuring an S7-1200/S7-1500 to communicate with Microsoft SQL Database using Tabular Data Stream

[Music] hi welcome back it's me how again and this time I'm going to show you how to configure your s7 1200 or s7 1500 to talk to your SQL database okay so we our partner VM let's fire up Chrome and go to the support site at download the sequel library taker there's an application example called connecting an s7 1500 to an SQL database this is exactly what we need so it comprises of documentation and the project the PDF will tell us exactly how to use the blocks in the project and the project should be just a project ready to use nice the project's not downloaded let's go to the Downloads folder let's extract our project and let's open it in Ti so I already had the I running I usually have t.i running let's close it for now is this is gonna fire up as a threatening instance there we go at the opening project for us now let's see what's in the project okay so in our project we already have an SQL POC this is the name of the PLC it's a 1511 or to Heaven window here actually as a 50 11 F so I'm gonna right click change the voice and select a 15 11 F so faced with variant 50 11 F the latest and greatest on the latest firmware as well now all these things I'm going to be showing you I'm going to be using an s-1 1,500 for it but if you go back here and read the documentation it actually tells us somewhere here it tells us that the same blots the same example can be used when s 7 1200 if we do not use archiving and we comment out the line and later on I will show you exactly a word this line is still defined it and how does that this project or 1200 as well just so that you know okay so we have a 1511 F now with the latest firmware I'm going to make sure that the prophetís configuration is the same as the config of my PLC so my PLC sets on a zero to two leaven the POS name that's fine either mind let's see what's inside the code so good program blocks and we have main archive SQL config and we have the library the l SQL library here so in main we have a block of the imagine yet from Desa library and this block uses flight data and white bus route internally as well we are not read that interested even but the block does inside as long as it works but again the noise being with the recent publication examples as they are not never protected so we can actually go in and see the coding soil you know maybe this block is missing some functionality that you would like to add so because it is not know how protected you could go in here and change the functionality okay so as you can see the block is already tagged up for us and it's using an interface DB that's called SQL counting that's just a global DB if you are going to creating your project or you have your project in which you don't want to integrate this library now you could fire up another resource of DIA just drag and drop this project and create your your own interface block or maybe you already have an interface block so if you look at the interface block itself it's using some EDT s so unity time log information and type Diagnostics decd T's can be found here in the project so again if you want to take this and use it in your project make sure you also copy the UD T's not just a blocks themselves so as I said the block is already timed up enable is just a boolean that we have connection settings the connection settings this would be a bet like in the MTV TV videos they will mean exactly same level information so hard to ID again where can we find a hard ROI D if you go to device in networks we click on the PROFINET board the properties and system constants you can find the harder ID of your profit interface here so I'm using forum to base one it's the only one that I have with my and 15:11 and the hard drive this is 64 another place where you can find it hard to ID as you go PLC tags show all times and to go system constants then you can scroll down and find your through this local profit interface one hard ROI be 64 yeah same some volume so if I now go back to my interface Levy well as you can say my name and hardware ID is already set to 64 so this okay I don't you change it next thing next thing is my connection already so this needs to be a unique ID for the open user communication so my default code program there's selected ten if you are already using attending hacks you might just change it to 15 or anything like this so that it is a unique ID connection type zero B stands for 11 so it's a tcp/ip that's fine an active connection establishment that's what we want remote address so this is where we need to type in the address of our SQL database if you watch my previous video on how to set up SQL Express at the VM that the MySQL Express is running isn't 0 13 and I am using the default port for SQL terms of 1 4 3 3 then I have login information so this is where I need to define some extra things on the right hand side here you can see which ones are optional which ones are mandatory so hostname is optional mandatory username yeah so again if watch my previous video we said the username to be SQL underscore s7 1500 AD the password it is my top secret password I love somatic the app name this is just optional this is their the name of let's say your FSM 1500 connection to the database itself the head we have the server name so for the server name I think we should be perfectly fine if we just put it as this so this is the name of my SQL instance our chili library name local optional optional not interested database to read and write from so again whether we were serving up it up with SQL Express we added ourselves a new database and called it s7 1500 SQL DB so this phone as well danny has some more optional fields that's all give the next things our command so the sequel command they actually want to send to my database and then execute boolean so the breast shouldn't be really that important theoretically and you're having just these should be find for me and should allow me to establish comes to my SQL database well let's see if it does so like my PLC and downloads device okay so we found over 50 Levin F this will get on 0 1 1 1 let's load it password must not be empty yeah so by default most of the application examples they come with this pre-selected for luxe is no protection and this means that you need to define the password for fjl safe and full fail-safe cpus you wanted to make changes to to fail saved and this password would be needed I'm just gonna flip this today well basically no protection I do not recommend it for production setups for production setups I would actually recommend setting this to no access and setting all these passwords for different levels of access so that your CDs actually protected from over writing okay so we changed this now let's just rebuild or faith will ensure full consistency of the project we will have any surprise when you start downloading and it tells us that the safety is not consistent for example although in our case there is no safety so we should still be fine continue on synchronization hello let's go take a moment okay download it start the module and let's see what happens you know let's go online let's close this window and let's put the glasses here as well yeah I'm good okay so let's not try to establish the connection to our SQL database the valid through means that the settings seem to be valid Baeza means that the block is doing something 7002 means that it did send me for queries in this trying to and to come back to your server if something goes wrong this will change to 86 or 2 for example but if it stays at 7000 - it means it's all good if it does change to something else I just go here in Diagnostics and check what is the error of sub function and then just go to the help of TI and look for t con block hey so it go for example for a TS and C or a t-con itself you scroll down you find the code here and this will tell you what I can't establish the connection and she'll be all good as you can see it's the same positive which means it's good now if you remember our database to which we are connected with the management studio just to go for the process connect your SQL Express I'm going to be using tip in my account here my top secret password we have our database and in the database your table called PLC data of columns flow pressure and temperature so now what I'm going to try to do I'm going to try to insert some values here from the PLC first let's just make sure yeah the table is empty so we can go back to a block and by default in this project we already have a command and service here that says insert into PLC data values 5 6 7 the only difference is I guess that ours is called change this PLC data but I will still insert the same values in 5 6 7 8 you deserve them in this subsequent a column so 500 flow 6 into pressure at 7 into temperature so now let's try to flip the execute secret command secret command god soon let's see if it updated here so again yeah well now we run this command press f5 and there you go from 6-7 in your sequel database and this is exactly how you can establish communication and send data from your s7 1200 or 1500 to the SQL database I didn't mention that I'll show you how to do it with s1 1239 so if you wanted to use these blocks with s7 1200 and we need to follow the manual and comment out program line 386 of LM l SQL Microsoft so this will be here and you would then go to line three eight six well it's three eight six this but basically 1501 support this command this one a week we wouldn't be able to use this block with 1200 if we comment out this and don't use the enable archive or even you know read the entire nebula archive as well at this entire region we should be perfectly fine and this block would work with s7 1200 again if you haven't seen the video on how to set up the SQL express itself and you don't have SQL Express yet not sure how to set it up please go check our other video that will tell you exactly how to set up SQL Express for communication with s7 1200 tourists so in 1500 CPU banks and speak to you soon bye [Music]

Channel: AutomationStation

Views: 8,155

Keywords:

Id: 7uFjfIb2fXE

Length: 13min 22sec (802 seconds)

Published: Wed Jun 10 2020