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
