Search code examples
mysqlstored-proceduresstored-functions

Trouble executing a string query in MySQL Stored Procedure

Question

i have a stored procedure that executes a string query from a function. but when executing the string i keep getting the error

Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE rm_sensor_combined SET subid = 0, rmip = '10.7.75.122', dname = 'ESD-' at line 4

Bellow is the content of the procedure.

if as_option = "updateData" then
SET ls_option := "UPDATE";
SET ls_query = (select fn_Sys_GenUpdateDetail(as_table, ls_option) );
end if;
SET @sql := ls_query ;
PREPARE stmt FROM @sql;
EXECUTE stmt;

stored procedure execution here is the result from the function, therefore the query to be executed

quoted

' UPDATE rm_sensor_combined \n SET subid = 0, rmip = '10.7.75.122', dname = 'ESD-001', dlocation = 'FFF-GGG-SS', warning_max = 2000, alert_max = 4000, ispaused = 0, isused = 1, update_by = ''\n where giruid =1 and rscuid = 1 and (subid = 0 or rmip = '10.7.75.122' or dname = 'ESD-001' or dlocation = 'FFF-GGG-SS' or warning_max = 2000 or alert_max = 4000 or ispaused = 0 or isused = 1); \n UPDATE rm_sensor_combined \n SET subid = 0, rmip = '10.7.75.122', dname = 'ESD-002', dlocation = ' - - ', warning_max = 2000, alert_max = 4000, ispaused = 0, isused = 0, update_by = ''\n where giruid =1 and rscuid = 2 and (subid = 0 or rmip = '10.7.75.122' or dname = 'ESD-002' or dlocation = ' - - ' or warning_max = 2000 or alert_max = 4000 or ispaused = 0 or isused = 0); \n '

Unquoted

UPDATE rm_sensor_combined SET subid = 0, rmip = '10.7.75.122', dname = 'ESD-001', dlocation = 'FFF-GGG-SS', warning_max = 2000, alert_max = 4000, ispaused = 0, isused = 1, update_by = '' where giruid =1 and rscuid = 1 and (subid = 0 or rmip = '10.7.75.122' or dname = 'ESD-001' or dlocation = 'FFF-GGG-SS' or warning_max = 2000 or alert_max = 4000 or ispaused = 0 or isused = 1);
UPDATE rm_sensor_combined SET subid = 0, rmip = '10.7.75.122', dname = 'ESD-002', dlocation = ' - - ', warning_max = 2000, alert_max = 4000, ispaused = 0, isused = 0, update_by = '' where giruid =1 and rscuid = 2 and (subid = 0 or rmip = '10.7.75.122' or dname = 'ESD-002' or dlocation = ' - - ' or warning_max = 2000 or alert_max = 4000 or ispaused = 0 or isused = 0);

any help on what is causing the error would help. thanks

Solution

  • https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html says:

    SQL syntax for prepared statements does not support multi-statements (that is, multiple statements within a single string separated by ; characters).

    You need to run your statements one at a time.