problem in Tmyquery and MYSQL

[pashaa]

why get error?
delphi tokyo.

Server: localhost via TCP/IP
Server type: MariaDB
Server version: 10.1.21-MariaDB - mariadb.org binary distribution
Protocol version: 10
User: root@localhost
Server charset: UTF-8 Unicode (utf8)

table collation utf8mb4_general_ci

Code: Select all

Myquery2.SQL.Clear;
Myquery2.SQL.Add('UPDATE users SET  TempCommand = ' + QuotedStr('') + ' where UserID=' + QuotedStr(UserID));
Myquery2.SQL.Add('UPDATE address SET  address_state  = ' + QuotedStr(Title) + ' where UserID=' + QuotedStr(UserID));
Myquery2.Prepared := True;
Myquery2.Execute;

#42000You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE address SET address_state = 'الامارات' where UserID='90434024'' at line 2 > EMyError

but when use this command in direct phpmyadmin work.

[ViktorV]

This behavior is related to the specificity of MySQL functionality: when calling the TMyQuery.Prepare method (or setting the TMyquery.Prepared property to True), the query is prepared on the server, but MariaDB does not allow performing SQL preparing that contains multiple statements: https://mariadb.com/kb/en/mariadb/prepare-statement
Therefore, to solve the issue, you can use the following recommendations:
- do not prepare the query (delete from your sample the line Myquery2.Prepared := True;)
- when using the TMyquery.SQL.Add method at the end of the line add the character ";" to correctly execute SQL, which contains multiple statements in the future.
For example:

Code: Select all

Myquery2.SQL.Clear;
Myquery2.SQL.Add('UPDATE users SET  TempCommand = ' + QuotedStr('') + ' where UserID=' + QuotedStr(UserID) + ';');
Myquery2.SQL.Add('UPDATE address SET  address_state  = ' + QuotedStr(Title) + ' where UserID=' + QuotedStr(UserID) + ';');
Myquery2.Execute;

[pashaa]

This behavior is related to the specificity of MySQL functionality: when calling the TMyQuery.Prepare method (or setting the TMyquery.Prepared property to True), the query is prepared on the server, but MariaDB does not allow performing SQL preparing that contains multiple statements: https://mariadb.com/kb/en/mariadb/prepare-statement
Therefore, to solve the issue, you can use the following recommendations:
- do not prepare the query (delete from your sample the line Myquery2.Prepared := True;)
- when using the TMyquery.SQL.Add method at the end of the line add the character ";" to correctly execute SQL, which contains multiple statements in the future.
For example:

Code: Select all

Myquery2.SQL.Clear;
Myquery2.SQL.Add('UPDATE users SET  TempCommand = ' + QuotedStr('') + ' where UserID=' + QuotedStr(UserID) + ';');
Myquery2.SQL.Add('UPDATE address SET  address_state  = ' + QuotedStr(Title) + ' where UserID=' + QuotedStr(UserID) + ';');
Myquery2.Execute;

Thanks,SOLVED.

[ViktorV]

Glad to see that the issue was resolved.
Feel free to contact us if you have any further questions about our products.