Values and special characters

Values and special characters

In CODESYS special characters in strings must be encoded with $ encoded.

Most important $ codes

Character	Code	Usage	Example
' (Apostrophe)	$27	Text in SQL	$27Max Mustermann$27
" (Quotation marks)	$22	Rarely needed	$22Text$22
$ (Dollar)	$$	Dollar sign	$$100

Practical examples

Insert text values into SQL

// Wrong - does not work:
MySQL_Command[0] := 'INSERT INTO users (name) VALUES ('Max')';

// Correct - with $27:
MySQL_Command[0] := 'INSERT INTO users (name) VALUES ($27Max$27)';

Dynamic text values

VAR
    sName : STRING := 'Max Mustermann';
    sCity : STRING := 'Berlin';
END_VAR

// Enclose text with $27
MySQL_Command[0] := 'INSERT INTO users (name, city) VALUES ($27';
MySQL_Command[1] := CONCAT(sName, '$27, $27');
MySQL_Command[2] := CONCAT(sCity, '$27)');

// Result: INSERT INTO users (name, city) VALUES ('Max Mustermann', 'Berlin')

WHERE condition with text

VAR
    sSearchName : STRING := 'Schmidt';
END_VAR

MySQL_Command[0] := 'SELECT * FROM users WHERE name = $27';
MySQL_Command[1] := CONCAT(sSearchName, '$27');

// Result: SELECT * FROM users WHERE name = 'Schmidt'

Date and time

// Date
MySQL_Command[0] := 'SELECT * FROM logs WHERE date = $272024-01-15$27';

// Date and time
MySQL_Command[0] := 'SELECT * FROM logs WHERE timestamp = $272024-01-15 10:30:00$27';

Numbers do not need $27

// Correct - numbers without $27:
MySQL_Command[0] := 'INSERT INTO data (value) VALUES (123)';

// Wrong - not for numbers:
MySQL_Command[0] := 'INSERT INTO data (value) VALUES ($27123$27)';  // WRONG!

NULL values

// Correct - NULL without $27:
MySQL_Command[0] := 'INSERT INTO data (value) VALUES (NULL)';

// Wrong:
MySQL_Command[0] := 'INSERT INTO data (value) VALUES ($27NULL$27)';  // WRONG!

Reference

Data type -> SQL format

CoDeSys type	SQL format	Example code
STRING	$27text$27	$27Berlin$27
INT	Direct	123
REAL	Direct (with dot)	23.5
BOOL	As 0/1 or text	1 or $27TRUE$27
DATE	$27YYYY-MM-DD$27	$272024-01-15$27
TIME	$27HH:MM:SS$27	$2710:30:00$27
DATETIME	$27YYYY-MM-DD HH:MM:SS$27	$272024-01-15 10:30:00$27

Avoid common mistakes

Wrong

MySQL_Command[0] := 'INSERT INTO users (name) VALUES ('Max')';  
// Syntax error!

Correct

MySQL_Command[0] := 'INSERT INTO users (name) VALUES ($27Max$27)';

Wrong

MySQL_Command[0] := 'INSERT INTO data (value) VALUES ($27100$27)';  
// Number as text!

Correct

MySQL_Command[0] := 'INSERT INTO data (value) VALUES (100)';