String sizes and long SQL commands

Standard limitations

The MySQL library works with arrays to create SQL statements

Strings have a fixed maximum length. By splitting into arrays, longer statements can be created.

Adjust global constants

You can change these values in the Global Constants :

Where do I find the settings?

// In the GLOBAL CONSTANTS of the library:
gc_MySQL_iStatementMax    : INT := 10;      // Array size (0..10 = 11 elements)
gc_MySQL_iStatementLength : INT := 100;     // Characters per element

Example: Allow larger statements

// Standard (default):
gc_MySQL_iStatementMax    := 10;      // 11 parts
gc_MySQL_iStatementLength := 100;     // 100 characters/part


// Extended for longer statements:
gc_MySQL_iStatementMax    := 20;      // 21 parts
gc_MySQL_iStatementLength := 200;     // 200 characters/part

Long SQL statements

Strategy 1: Split the statement (standard)

Example: Long INSERT with many values

VAR
    arrLongCmd : ARRAY[0..10] OF STRING(100);
END_VAR

// Part 1: INSERT beginning
arrLongCmd[0] := 'INSERT INTO machine_data (id, timestamp, temp, pressure, speed, status, operator) ';

// Part 2: VALUES beginning
arrLongCmd[1] := 'VALUES (';

// Parts 3-8: Individual values
arrLongCmd[2] := CONCAT(INT_TO_STRING(iMachineID), ', ');
arrLongCmd[3] := '$272024-01-15 10:30:00$27, ';
arrLongCmd[4] := CONCAT(REAL_TO_STRING(rTemp), ', ');
arrLongCmd[5] := CONCAT(REAL_TO_STRING(rPressure), ', ');
arrLongCmd[6] := CONCAT(REAL_TO_STRING(rSpeed), ', $27');
arrLongCmd[7] := CONCAT(sStatus, '$27, $27');
arrLongCmd[8] := CONCAT(sOperator, '$27)');

// Leave the rest empty
arrLongCmd[9] := '';
arrLongCmd[10] := '';

Important: Each element may have a maximum of 100 characters!

Strategy 2: Use intermediate variables

If a substring becomes too long:

VAR
    sValuesPart1 : STRING(100);
    sValuesPart2 : STRING(100);
END_VAR

// Build values in intermediate variables
sValuesPart1 := CONCAT(INT_TO_STRING(iValue1), ', ');
sValuesPart1 := CONCAT(sValuesPart1, INT_TO_STRING(iValue2));
sValuesPart1 := CONCAT(sValuesPart1, ', ');
sValuesPart1 := CONCAT(sValuesPart1, INT_TO_STRING(iValue3));

sValuesPart2 := CONCAT(', ', REAL_TO_STRING(rValue4));
sValuesPart2 := CONCAT(sValuesPart2, ', ');
sValuesPart2 := CONCAT(sValuesPart2, REAL_TO_STRING(rValue5));

// Then insert into the array
arrLongCmd[0] := 'INSERT INTO data (v1, v2, v3, v4, v5) VALUES (';
arrLongCmd[1] := sValuesPart1;
arrLongCmd[2] := sValuesPart2;
arrLongCmd[3] := ')';

Practical examples

Example 1: INSERT with many columns

Task: Write 15 values to the database

VAR
    // Data
    iID           : INT := 1;
    sName         : STRING(50) := 'Machine A';
    rTemp1        : REAL := 23.5;
    rTemp2        : REAL := 24.1;
    rTemp3        : REAL := 22.8;
    rPressure1    : REAL := 1.5;
    rPressure2    : REAL := 1.6;
    rSpeed1       : REAL := 1500.0;
    rSpeed2       : REAL := 1520.0;
    sOperator     : STRING(30) := 'Schmidt';
    sShift        : STRING(10) := 'Morning shift';
    iCounter      : INT := 1234;
    xAlarm        : BOOL := FALSE;
    sStatus       : STRING(20) := 'RUNNING';
    sLocation     : STRING(30) := 'Hall 3';
    
    // SQL statement
    arrInsertCmd  : ARRAY[0..10] OF STRING(100);
END_VAR

// Build INSERT in small parts
arrInsertCmd[0] := 'INSERT INTO machines (id, name, temp1, temp2, temp3, pressure1, pressure2) ';
arrInsertCmd[1] := 'VALUES (';
arrInsertCmd[2] := CONCAT(INT_TO_STRING(iID), ', $27');
arrInsertCmd[3] := CONCAT(sName, '$27, ');
arrInsertCmd[4] := CONCAT(REAL_TO_STRING(rTemp1), ', ');
arrInsertCmd[5] := CONCAT(REAL_TO_STRING(rTemp2), ', ');
arrInsertCmd[6] := CONCAT(REAL_TO_STRING(rTemp3), ', ');
arrInsertCmd[7] := CONCAT(REAL_TO_STRING(rPressure1), ', ');
arrInsertCmd[8] := CONCAT(REAL_TO_STRING(rPressure2), ')');

But: Only 7 of 15 values fit! Solution: Second INSERT or UPDATE

// Second part as UPDATE
arrUpdateCmd[0] := 'UPDATE machines SET speed1 = ';
arrUpdateCmd[1] := CONCAT(REAL_TO_STRING(rSpeed1), ', speed2 = ');
arrUpdateCmd[2] := CONCAT(REAL_TO_STRING(rSpeed2), ', operator = $27');
arrUpdateCmd[3] := CONCAT(sOperator, '$27, shift = $27');
arrUpdateCmd[4] := CONCAT(sShift, '$27, counter = ');
arrUpdateCmd[5] := CONCAT(INT_TO_STRING(iCounter), ', status = $27');
arrUpdateCmd[6] := CONCAT(sStatus, '$27 WHERE id = ');
arrUpdateCmd[7] := INT_TO_STRING(iID);

---

Example 2: SELECT with long WHERE clause

VAR
    dtStart : STRING := '2024-01-01';
    dtEnd   : STRING := '2024-12-31';
    rMinTemp: REAL := 20.0;
    rMaxTemp: REAL := 30.0;
    iMachID : INT := 1;
END_VAR

// Split long WHERE clause
arrSelectCmd[0] := 'SELECT * FROM measurements WHERE ';
arrSelectCmd[1] := 'machine_id = ';
arrSelectCmd[2] := CONCAT(INT_TO_STRING(iMachID), ' AND ');
arrSelectCmd[3] := 'timestamp BETWEEN $27';
arrSelectCmd[4] := CONCAT(dtStart, '$27 AND $27');
arrSelectCmd[5] := CONCAT(dtEnd, '$27 AND ');
arrSelectCmd[6] := 'temperature BETWEEN ';
arrSelectCmd[7] := CONCAT(REAL_TO_STRING(rMinTemp), ' AND ');
arrSelectCmd[8] := REAL_TO_STRING(rMaxTemp);

---

Example 3: UPDATE with many fields

// Problem: update 10 fields
// Solution: split into sensible parts

arrUpdateCmd[0] := 'UPDATE production SET ';
arrUpdateCmd[1] := 'temp1=' + REAL_TO_STRING(rTemp1) + ', ';
arrUpdateCmd[2] := 'temp2=' + REAL_TO_STRING(rTemp2) + ', ';
arrUpdateCmd[3] := 'temp3=' + REAL_TO_STRING(rTemp3) + ', ';
arrUpdateCmd[4] := 'pressure1=' + REAL_TO_STRING(rPress1) + ', ';
arrUpdateCmd[5] := 'pressure2=' + REAL_TO_STRING(rPress2) + ', ';
arrUpdateCmd[6] := 'speed=' + REAL_TO_STRING(rSpeed) + ', ';
arrUpdateCmd[7] := 'status=$27' + sStatus + '$27 ';
arrUpdateCmd[8] := 'WHERE id = ' + INT_TO_STRING(iID);