> For the complete documentation index, see [llms.txt](https://support.powerio.com/hub/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://support.powerio.com/hub/codesys-hvac/en/mysql-library/beispiele/string-grossen-und-lange-sql-befehle.md).

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

```iecst
// 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

```iecst
// 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

```iecst
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:

```iecst
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

```iecst
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

```iecst
// 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

```iecst
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

```iecst
// 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);
```


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://support.powerio.com/hub/codesys-hvac/en/mysql-library/beispiele/string-grossen-und-lange-sql-befehle.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.