12/13/2011 4:47 PM | |
Joined: 9/8/2009 Last visit: 7/17/2023 Posts: 1410 Rating: (152) |
Yes, I agree that there are other possibilties, to implement a custom server-client protocol for bridging to SQL server, with also many different server protocols like oracle, mysql, mssql,.. |
Last edited by: Marko Bursic at: 12/13/2011 4:49 PM |
|
12/19/2011 11:46 PM | |
Joined: 9/8/2009 Last visit: 7/17/2023 Posts: 1410 Rating: (152) |
Hi, all I have been studing MySQL client written in C++ and now I made first steps. The first problem is enycription of password and this is done with SHA1 function, I have been watching many SHA1 algorithms and now I made a small version of it. The purpose is to authenticate a login to the server, so it is limited to 40 characters, only just what is needed for MySQL authentication. The server when detects a connection, sends a greeting message to client, which has a random pattern of characters. Both client and server encrypts this pattern with known password. Client sends this encrypted pattern to the server which compares with its result and then if they match youre logged in. After logon is much simpler, sql statements are sent to the server in string form, so implementing an tiny client is close. Is anybody interested to join? I am not expert from converting C++ programs into S7, and help would be appreciated. I have attached SCL source Step7 for function SHA1 Attachment00000003.zip (1988 Downloads) |
7/19/2012 12:40 PM | |
Posts: 4 Rating: (0) |
Hello! I am also interesting in about s7-1200 mysql connection. If you have any info please share. Do you have a working c /or c++ program? Bye.
|
12/12/2012 1:11 AM | |
Posts: 33 Rating: (7) |
I'm interested in helping out on this project too. let me know if your still actively working on it. Thanks! |
12/14/2012 5:00 PM | |
Joined: 9/8/2009 Last visit: 7/17/2023 Posts: 1410 Rating: (152) |
If you didn't see other post, this is what was done. I have tested this with plcsim, it has never run on real plc, but is very probable that is working. AttachmentMySQL_v3 -- MySQL_v1_SIMATIC 30.pdf (3315 Downloads) |
This contribution was helpful to1 thankful Users |
12/14/2012 6:02 PM | |
Joined: 9/8/2009 Last visit: 7/17/2023 Posts: 1410 Rating: (152) |
I will put the last source and pdf I have made near year ago.
Attachment0000000c.zip (1896 Downloads) |
This contribution was helpful to1 thankful Users |
12/14/2012 6:03 PM | |
Joined: 9/8/2009 Last visit: 7/17/2023 Posts: 1410 Rating: (152) |
pdf
AttachmentMySQL_v5 -- MySQL_v1_SIMATIC 30.pdf (2469 Downloads) |
This contribution was helpful to1 thankful Users |
6/14/2013 9:54 PM | |
Posts: 7 Rating: (0) |
i'm very interresting by this projet, i have real S7-400 with ethernet, and i search sample of program to send data directlyt from PLC to SQL server
|
6/15/2013 11:02 AM | |
Joined: 9/8/2009 Last visit: 7/17/2023 Posts: 1410 Rating: (152) |
I have moved this project, to TIA and S7-1200. |
6/15/2013 10:14 PM | |
Posts: 7 Rating: (0) |
ok, can you send the pdf, i can help you for the state machine ... i write many code for S7-400 S7-300 |
Last edited by: vinceducat at: 6/15/2013 11:18 PM |
|
6/16/2013 11:53 AM | |
Joined: 9/8/2009 Last visit: 7/17/2023 Posts: 1410 Rating: (152) |
This is a program for TIA, all FBs were built using standard access (S7/300-400, don't look at Set in IDBproperty, because it isn't). AttachmentMysql_s1200_v8.pdf (1687 Downloads) |
Last edited by: Marko Bursic at: 6/16/2013 12:26 PMLast edited by: Marko Bursic at: 6/16/2013 12:08 PM |
|
6/16/2013 2:11 PM | |
Joined: 9/8/2009 Last visit: 7/17/2023 Posts: 1410 Rating: (152) |
Sorry, I have uploaded only one FB, this now is all blocks.
AttachmentMySQL_v8_S7-1200_All.pdf (1727 Downloads) |
This contribution was helpful to1 thankful Users |
9/5/2013 11:23 AM | |
Joined: 9/2/2013 Last visit: 4/2/2024 Posts: 1 Rating: (0) |
Is it possible for you to zip the S1200 project and upload it somewhere, or email it to me? |
4/17/2014 12:59 PM | |
Joined: 9/8/2009 Last visit: 7/17/2023 Posts: 1410 Rating: (152) |
Send a PM with your e-mail, and I will send you. |
6/12/2015 2:08 PM | |
Posts: 6 Rating: (0) |
Does anybody has this example available. I am trying to find a similar solution and i need it. Thanks, |
6/15/2015 9:16 AM | |
Joined: 9/8/2009 Last visit: 7/17/2023 Posts: 1410 Rating: (152) |
I have send it over PM multiple times, now I will post it here: This is Zip of zap13, TIA13 archeive file. I have compiled it from v11, VAL_STRG, does not allow to be called directly in SCL, so there is one another function that calls VAL_STRG in LAD. Hi, I have tested this near year ago, I have used Wireshark, Heidi SQL, MySQL Workbench. First you will need to stetup MySql server, my project uses a database named scada, and it has a table named Hist consisting ten real numbers named T1, T2,....T10. Look at JPG attached. In STAT declaration of function block MySql, you will find username, password and database name, currently: username: root password: newuser database: scada Change to your wish, but don't exceed length of maximum characters (14), you may change also that if you wish, but not more than 54 chars. MySql server should use newer 4.1 authenticaion protocol, older are not implemented. Autentication is done via SHA1 function block, it time consuming so the entire autentication sequence is split in more steps, whenever SHA1 is executed means one step (thus one plc scan). The authentication loads CPU to approx 20ms, this is done anytime you connect to server, after that data are sent to server encoded with ascii characters. Before you say it doesn't work, try to send data wit some pc program (Heidi SQL), wit wireshark you will monitor traffic and see response from server or error) Best regards, Marko. I forgot to say that there are additional parameter in STAT area: net_settings of type TCON_Param is a place where you will have to set the IP addres of your server. net_settings.REM_STADDR - now you will see 192.168.1.127, change it to suit your demand net_setting.REM_TSAP_ID - port number , now 0CEA hex, means 3306 dec, this is the default port for mysql You will find meaning for others in manual for S7-1200, but I think it is nothing to change there. AttachmentMYSQL_v8_V13.zip (2060 Downloads) |
This contribution was helpful to15 thankful Users |
6/15/2015 9:18 AM | |
Joined: 9/8/2009 Last visit: 7/17/2023 Posts: 1410 Rating: (152) |
this is the sql table |
This contribution was helpful to1 thankful Users |
6/16/2015 11:03 AM | |
Posts: 6 Rating: (0) |
Dear Marko, thanks for sharing this app with us. I make the steps you described and i succesfull connect with Heidi to mySQL database. I make the same table as your and i check the telegrams with the wireshark. Everything its fine until now. I run the project on my CPU and when i set the connect bit to "1" after 2-3 seconds the Error bit get TRUE and the Status take value = 3. I checked the SQL code and i found in line 247 that i have "authentication failed" status. It seems strange because with Wireshark there is no telegram from CPU to mySQL server PC. The only telegrams are between CPU and TIA Portal PC (used for status program). Is there any details i can check?
Attachmentscreenshots.rar (902 Downloads) |
Last edited by: giorgos007 at: 6/16/2015 11:04:41 AM |
|
6/18/2015 6:02 PM | |
Joined: 9/8/2009 Last visit: 7/17/2023 Posts: 1410 Rating: (152) |
As stated, the disconnection, error behaviour isn't 100%. But if you have time you can improve all that. Anyway, you're the first one that has sent some feedback information about this. I have never used it latter, I did just some piece of code because some of my clienet wanted some cheap variant to store data on SQL, that latter changed his mind. There are some parts of program, that they don't need to be changed at all. SHA1 function, strictly you have nothing to change, because it is complicated and well optimized. Then handshaking, encoding,...its a huge work and it works, you are the whitness that has logged in. From line 276 to 328 you can change as it suits your demand, you can even delete SQLExe command if you like to send data for logging on the server. The lines I don't like are 1 to 85 and 365-> Also I have found that you can remove copy: array[0..19] of byte and rtu :int, this because a line MOVE_BLK(IN:=#Pwd.Bytes[0],COUNT:=#Pwd.Len,OUT=>#copy[0]); does nothing (it does: consumes plc time), it is a residual from earlier versions. AttachmentMySQL_ S71200_description.doc (1715 Downloads) |
This contribution was helpful to1 thankful Users |
6/24/2015 7:30 AM | |
Posts: 1 Rating: (0) |
Good day! great job, thank you.I would like to ask, can there be a project for MSSQL? |
11/25/2015 4:24 AM | |
Posts: 4 Rating: (0) |
Hello I download project and change PLC 1214C . I load to PLC. But i can not write Mysql. Please check me. |
4/8/2017 7:23 AM | |
Posts: 16 Rating: (1) |
photo 2: Compilation Error |
4/8/2017 7:24 AM | |
Posts: 16 Rating: (1) |
photo 3, Busy Error |
4/8/2017 7:25 AM | |
Posts: 16 Rating: (1) |
photo 4, the status... |
4/8/2017 7:29 AM | |
Posts: 16 Rating: (1) |
photo 5, PORT is closed |
10/3/2017 10:40 AM | |
Posts: 30 Rating: (1) |
Sir, I would like to make a FB to do the same, Any help would be appreciated. Till where has this been completed? |
9/27/2021 3:31 PM | |
Joined: 1/27/2021 Last visit: 4/22/2024 Posts: 1 Rating: (0) |
May I get the project too? Thank you |
7/14/2023 4:27 AM | |
Joined: 1/5/2023 Last visit: 5/15/2024 Posts: 4 Rating: (0) |
Maybe this document will help you...
Attachment109779336_SQL_DOC_en_V31.pdf (325 Downloads) |
Follow us on