Writing to a MySQL readable database with S7-300

[EirikV 1]

Hi, I was wondering if it is possible to write into a MySQL readable database file to send over ethernet to a possible SCADA system. I'm thinking alarm logging here with date and time, the alarm and the user that was logged in at the time.. I'll be using a CPU 315-2 DP and a CP 343-1 ethernet module. Thanks Eirik

[JesperMP 2]

Hei Eirik. In theory it is definitely possible. I know that Phoenix Contact has a library for their PLCs that will do exactly that. For Siemens, you will have to figure it out yourself. But it will take some knowledge about SQL commands (beyond my knowhow). The easy solution would be to use a panel as an intermediary step. Siemens panels have the possibility to log directly to an ODBC/SQL database.

[Nathan 7]

How do you know the user that was logged in? I can appreciate the desire to do it in the PLC. It's a more ideal location if no consideration were needed for implementation. If you're looking for: flexibility, simplicity, and more functionality in general you'll need to use a computer. I'd recommend considering FactorySQL for that purpose. Alarming is pretty trivial to set up in software and you get email (text message) notification capability in addition to SQL data logging. (I'm still pushing for a free version of FactorySQL that would do this). Alternatively, there's a whole slew of little OPC based utilities for data logging. Edited 15 Feb 2008 by Nathan

[EirikV 1]

Hi again Jesper. The solution with a panel, was my initial idea to accomplish this, but I'm under the impression that my panel (TP 177B) is not capable of doing such. I may very well be wrong, though, but I found the panel very limited with regards to scripting and alarm logging, and even basic functionality too, really. I don't know if you are familiar with those panels. SQL should be very well documented, though, so I think I should be able to figure that out. My biggest concern is the Siemens side, since I'm not experienced with that at all. I guess I'll be busy this weekend then

[EirikV 1]

Hi Nathan. I have not quite figured that out yet, but I guess I have to be sending some kind of user ID from the operator panel when a new user logs in. Regarding using a computer, the equipment is allready ordered and I'm just trying to implement the functionality given in the specs from the customer. We are on a tight budget and fairly late in the design stage now, too, so adding new hardware is probably out of the question. This is not a very big application, though, so I would think I should be able to get the functionality I want out if the current hardware. In the specs they say that it should write the alarm log to a database file that is readable for their SCADA system. I have no idea how their system works, my job is just to make sure there is a file with alarm logs that they can aquire from the system if they want to. I will look into the OPC utillities as you mentioned, though.

[EirikV 1]

It looks like I can use a delimited text file and just transfer that to the SCADA system via ethernet. My question then is: can I save a text file in the PLC memory?

[JesperMP 2]

I dont understand. If there is a SCADA, why cant it just do all the works ? Datalogging is one of the things that a SCADA does best. Anyway, you cannot store a file in the S7 PLC, but you can store for example a DB with many values that you have logged. The maximum size of the DB varies with the CPU. You can also use several DBs. You can then setup the SCADA to read all the values from the S7 PLC in one go. Maybe that is close to what you want to achieve. Try to explain what has to be achieved in terms of functionality.

[EirikV 1]

Hi Jesper. I can quote the specs: "The alarm system shall write alarms into a db file. This file shall be readable from MySQL applications. This file is to be accessible for <Company> distributed alarm system where <Company> uses a system developed using MySQL applications for distribution of alarm systems and for issuing of alarms through a pager system. File format and structure to be defined in cooperation with <Company>". To me this sounds like they want me to write some kind of file that they can aquire from my system. I could of course just use DBs.

[JesperMP 2]

If that is all that you have to go out from then you absolutely must investigate more before you decide anything. You have to contact the customer and ask them exactly what they want. For the connection between PLC and MySQL: It would only make sense if the connection between the PLC and the MySQL database is done from the customer side. It would be absolutely crazy to try and implement that the S7 PLC sends SQL commands or CSV files actively. However, maybe the customer do have such an idiotic idea. (*) In that case, you shall politely ask them to change their requirements, and if they do not want to do that ask them how you shall do it exactly. For the alarms: It could be that they just need to have access to the alarm bits that you use to show alarms on your local HMI panel. It could also be that you have to log alarms sequentally in a DB (rotating FIFO maybe). And it can be something completely different. If things go sour with the customer, you can stick to the text "The alarm system shall write alarms into a db file" and then do whatever you decide to do. And then you can argue that "db file" is a DB in the PLC and not a Database in MySQL. *: (edit) Maybe some time in the future, it will be possible to send SQL commands directly from the PLC. Such a thing exists for Wago and Beckhoff PLCs, but not for Siemens. edit again: If you cannot sneak out of having to send data to the MySQL database, you probably have to use a WinCC Flexible PC RT. With this you can log directly to any ODBC database. WinCC Flex RT is not so expensive for the 128 tags license + the necessary archives option. Of course a panel PC is a bit expensive compared to a TP177B. Edited 18 Feb 2008 by JesperMP

[EirikV 1]

Takk Jesper. I will investigate this further. I just wanted to make sure wether is was possible to do or not in Siemens. - Eirik

[aikona 0]

A readable file = CSV file .You could enjoy yourself to 'write' this using a DB but .best solution is to use a OP/TP that can send a CSV file to your MySQL server .A TP177 DP PN can do that ,Get an exact scope of supply from the <Company> We are using S7-412's in combination with OP270's , every alarm is logged into a text file ( CSV format) and then I wrote a little program in Delphi to analyze these text files and insert all the new alarms into a MySQL database .This program runs on the MySQL server.Six months of logging gives me about 4 million records Greetings Eric Edited 1 Apr 2008 by aikona