KO | EN

GitLite

Minimalist Open Source Repository Search Engine

gitlite — search

#developer-tools #typescript #claude-code #codex #dsh #dsh-plugin #ai-agent #ai-agents #deepseek-harness #agent-skills #macos #cli

S7-PLC_MySql_MariaDB

★ 61 Open GitHub ↗

MySql and MariaDB communication FB for Siemens PLC S7-1200 and S7-1500

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

# S7-PLC_MySql_MariaDB
## MySql and MariaDB communication FB for Siemens PLC S7-1200 and S7-1500

### Example Video:
https://user-images.githubusercontent.com/10088323/134990531-046f66e1-0961-44b2-ace2-500b7aa3dfe5.mp4

### 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;

![S7-PLC_MySql_MariaDB_Bild03](https://user-images.githubusercontent.com/10088323/134990587-494fd77e-f111-4fd2-b32c-3f1e69d1b739.JPG)

* 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:<br>
  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:

![Screenshot 2025-12-04 090530](https://github.com/user-attachments/assets/230097a5-fa3a-44de-85ea-b88567cc70d4)

### 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"

### Server 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 00000000

### Capabilities 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 00000000
       
### Capabilities 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 00000000
        
### Capabilities mariaDB:
        CLIENT_PROGRESS                      00000001
        CLIENT_COM_MULTI                     00000010
        CLIENT_STMT_BULK_OPERATIONS          00000100
        CLIENT_EXTENDED_TYPE_INFO            00001000
        CLIENT_CACHE_METADATA                00010000

### Field 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 00000000

### Field 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: GEOMETRY

S7-PLC_MySql_MariaDB

Explore Similar Repositories

Was this content helpful?

Recent Feedback

Download README