Catalog · How the catalog works
S7-PLC_MySql_MariaDB
MySql and MariaDB communication FB for Siemens PLC S7-1200 and S7-1500
- ★ 62 on GitHub
Anyone may press Interesting, signed in or not. It helps others find this project.
README
S7-PLC_MySql_MariaDB
MySql and MariaDB communication FB for Siemens PLC S7-1200 and S7-1500
Example Video:
General Informations:
Server user settings: The user that you want to login to sql server needs to set up using "mysql_native_password" as authentication method. You should create a new user for connecting to plc by typing following into your server commandline:
CREATE USER 'your_new_username'@'%';
ALTER USER 'your_new_username'@'%' IDENTIFIED WITH 'mysql_native_password';
ALTER USER 'your_new_username'@'%' IDENTIFIED BY 'your_new_password';
GRANT ALL PRIVILEGES ON your_databasename.your_tablename TO 'your_new_username'@'%';
FLUSH PRIVILEGES;
-
Tested with:
- PLC:
- CPU 1214C DC/DC/DC FW: V4.3
- CPU 1214 DC/DC/DC FW: V4.6
- CPU 1515-2PN FW: V2.6
- CPU 1513-1PN FW: V2.9
- Short PLC cycletimes (like 1ms) Will work, but you might have problems manually setting "query" to TRUE while debugging live in TIA-Portal (Siemens Software Issue)
-
SQL Server:
- MySql Server: 8.0.26 and 8.0.40
- MariaDB Server: 5.5.5 - 10.6.4, 11.2, 11.5.2 and 12.3.2
-
Requirements:
- PLC: S7-1200 or S7-1500
- Server: must use "mysql_native_password" authentication plugin
-
Functionality:
- Connect with SQL-Server
- Send PING to SQL-Server
- Send QUERY String to SQL-Server
- This client works with client/server protocol
- Results are saved as Strings
-
Settings:
The following constants can be adjusted depending on the expected maximum number of:
- Colums | columns in a result message from Server
- Rows | rows in a result message from Server
- Stringsize | chars in a String of a result (column or row) from Server
- Buffersize | bytes send from Server in a message from Server
Call:
Parameter:
| Parameter | Declaration | Datatype | Example | Description |
|---|---|---|---|---|
| connect | Input | Bool | False | communication and connect |
| connectionID | Input | CONN_OUC | 16#1 | connection reference / identifier |
| hardwareID | Input | HW_ANY | 64 | HW-identifier of IE-interface submodule |
| localPort | Input | UInt | 2000 | plc portnumber |
| server | Input | Int | 1 | server (Mysql = 1 / MariaDB = 2) |
| ipaddress | Input | String[15] | '192.168.0.1' | server IPv4 address '192.168.0.1' |
| remotePort | Input | UInt | 3306 | server portnumber |
| username | Input | String | 'root' | server login username |
| password | Input | String | 'root' | server login password |
| database | Input | String | 'data' | server database name |
| status | Output | Word | 16#0 | infocode / errorcode |
| connected | Output | Bool | false | connection established |
| nColumns | Output | DInt | 0 | number of recieved columns |
| nRows | Output | DInt | 0 | number of recieved rows |
| resultData | Output | Array[1..#Columns, 0..#Rows] of String[#Stringsize] | resultdata: ResultData[1,0] = Columnname ResultData[1,1-X] = Rowdata | |
| ping | InOut | Bool | false | ping command, will automatically be reset |
| query | InOut | Bool | false | query command, will automatically be reset |
| querydata | InOut | Array[*] of String | query data / sql statement, multiple lines to make larger statements. Will be glued together as it is, remember to set your whitespaces as needed! | |
| Buffersize | Constant | Int | 4096 | size of send and recieve buffer in bytes |
| Columns | Constant | Int | 20 | max number of columns |
| Rows | Constant | Int | 10 | max number of rows |
| Stringsize | Constant | Int | 50 | max number of chars in resultstrings |
Status Codes:
Info:
W#16#0000: No Error
W#16#0002: TCP connection is not established
Error:
W#16#0010: Bad IP-Address, IP-Address should be in this format: '192.168.0.1'
W#16#0011: Client capability "CONNECT_ATTRS" is set, but it is not implemented
W#16#0012: SQL error, for further information see instancedatablock.errorPacket.ERROR_MESSAGE
W#16#0013: Client capability "SESSION_TRACK" is set, but it is not implemented
W#16#0014: Client capability "CLIENT_CACHE_METADATA" is set, but it is not implemented
W#16#0015: Client capability "CLIENT_EXTENDED_TYPE_INFO" is set, but it is not implemented
W#16#0016: Input "server" wrong, 1 = Mysql / 2 = MariaDB
W#16#0017: The user you want to login to sql Server is set up to authenticate with "caching_sha2_password",
change this to "mysql_native_password", or create a new user for connecting to plc by
typing following into your server commandline:
1. CREATE USER 'your_new_username'@'%';
2. ALTER USER 'your_new_username'@'%' IDENTIFIED WITH 'mysql_native_password';
3. ALTER USER 'your_new_username'@'%' IDENTIFIED BY 'your_new_password';
4. GRANT ALL PRIVILEGES ON your_databasename.your_tablename TO 'your_new_username'@'%';
5. FLUSH PRIVILEGES;
W#16#0020: Wrong packet type recieved after login request, expectet OK or ERROR
W#16#0021: Recieved LOCALINFILE packet, but it is not implemented
W#16#0023: Number of colums in result data is greater than the set value number of columns, adjust the constant "Columns"
W#16#0024: Number of recieved bytes is greater than the set value number of bytes, adjust the constant "Buffersize"
W#16#0025: Number of rows in result data is greater than the set value number of rows, adjust the constant "Rows"
W#16#0026: Timeout receiving data; missing correct footer that indicates the end of data transmissionServer status:
IN_TRANS 00000000 00000001
AUTO_COMMIT 00000000 00000010
MULTI_QUERY(unused) 00000000 00000100
MORE_RESULTS_EXISTS 00000000 00001000
BAD_INDEX_USED 00000000 00010000
NO_INDEX_USED 00000000 00100000
CURSOR_EXISTS 00000000 01000000
LAST_ROW_SENT 00000000 10000000
DB_DROPPED 00000001 00000000
NO_BACKSLASH_ESCAPES 00000010 00000000
METADATA_CHANGED 00000100 00000000
QUERY_WAS_SLOW 00001000 00000000
PS_OUT_PARAMS 00010000 00000000
IN_TRANS_READONLY 00100000 00000000
SESSION_STATE_CHANGED 01000000 00000000Capabilities standard:
CLIENT_MYSQL 00000000 00000001
FOUND_ROWS 00000000 00000010
LONG_FLAG 00000000 00000100
CONNECT_WITH_DB 00000000 00001000
NO_SCHEMA 00000000 00010000
COMPRESS 00000000 00100000
ODBC 00000000 01000000
LOCAL_FILES 00000000 10000000
IGNORE_SPACE 00000001 00000000
SPEAKS_PROTOCOL_41 00000010 00000000
CLIENT_INTERACTIVE 00000100 00000000
SSL 00001000 00000000
IGNORE_SIGPIPE 00010000 00000000
TRANSACTIONS 00100000 00000000
RESERVED 01000000 00000000
AUTH_PROTOCOL_41 10000000 00000000Capabilities extended:
MULTI_STATEMENTS 00000000 00000001
MULTI_RESULTS 00000000 00000010
PS_MULTI_RESULTS 00000000 00000100
PLUGIN_AUTH 00000000 00001000
CONNECT_ATTRS 00000000 00010000
PLUGIN_AUTH_LENENC 00000000 00100000
HANDLE_EXPIRED_PASSWORDS 00000000 01000000
SESSION_TRACK 00000000 10000000
DEPRECATE_EOF 00000001 00000000Capabilities mariaDB:
CLIENT_PROGRESS 00000001
CLIENT_COM_MULTI 00000010
CLIENT_STMT_BULK_OPERATIONS 00000100
CLIENT_EXTENDED_TYPE_INFO 00001000
CLIENT_CACHE_METADATA 00010000Field Details Flag:
NOT_NULL 00000000 00000001
PRIMARY_KEY 00000000 00000010
UNIQUE_KEY 00000000 00000100
MULTIPLE_KEY 00000000 00001000
BLOB 00000000 00010000
UNSIGNED 00000000 00100000
ZEROFILL_FLAG 00000000 01000000
BINARY_COLLATION 00000000 10000000
ENUM 00000001 00000000
AUTO_INCREMENT 00000010 00000000
TIMESTAMP 00000100 00000000
SET 00001000 00000000
NO_DEFAULT_VALUE_FLAG 00010000 00000000
ON_UPDATE_NOW_FLAG 00100000 00000000
NUM_FLAG 10000000 00000000Field Types:
00: DECIMAL
01: TINY
02: SHORT
03: LONG
04: FLOAT
05: DOUBLE
06: NULL
07: TIMESTAMP
08: LONGLONG
09: INT24
10: DATE
11: TIME
12: DATETIME
13: YEAR
14: NEWDATE
15: VARCHAR
16: BIT
17: TIMESTAMP2
18: DATETIME2
19: TIME2
245: JSON
246: NEWDECIMAL
247: ENUM
248: SET
249: TINY_BLOB
250: MEDIUM_BLOB
251: LONG_BLOB
252: BLOB
253: VAR_STRING
254: STRING
255: GEOMETRYCollation:
01: big5 | big5_chinese_ci
03: dec8 | dec8_swedish_ci
04: cp850 | cp850_general_ci
06: hp8 | hp8_english_ci
07: koi8r | koi8r_general_ci
08: latin1 | latin1_swedish_ci
09: latin2 | latin2_general_ci
10: swe7 | swe7_swedish_ci
11: ascii | ascii_general_ci
12: ujis | ujis_japanese_ci
13: sjis | sjis_japanese_ci
16: hebrew | hebrew_general_ci
18: tis620 | tis620_thai_ci
19: euckr | euckr_korean_ci
22: koi8u | koi8u_general_ci
24: gb2312 | gb2312_chinese_ci
25: greek | greek_general_ci
26: cp1250 | cp1250_general_ci
28: gbk | gbk_chinese_ci
30: latin5 | latin5_turkish_ci
32: armscii8 | armscii8_general_ci
33: utf8 | utf8_general_ci
35: ucs2 | ucs2_general_ci
36: cp866 | cp866_general_ci
37: keybcs2 | keybcs2_general_ci
38: macce | macce_general_ci
39: macroman | macroman_general_ci
40: cp852 | cp852_general_ci
41: latin7 | latin7_general_ci
51: cp1251 | cp1251_general_ci
54: utf16 | utf16_general_ci
56: utf16le | utf16le_general_ci
57: cp1256 | cp1256_general_ci
59: cp1257 | cp1257_general_ci
60: utf32 | utf32_general_ci
63: binary | binary
92: geostd8 | geostd8_general_ci
95: cp932 | cp932_japanese_ci
97: eucjpms | eucjpms_japanese_ci
248: gb18030 | gb18030_chinese_ci
255: utf8mb4 | utf8mb4_0900_ai_ci