Debugging dynamic SQL errors and warnings of MySQL
create table log_trace(row_id int auto_increment primary key, msg text, utc timestamp, tag varchar(200)); DELIMITER ;; CREATE PROCEDURE `sp_logger`(in p_msg text,in p_tag varchar(200)) BEGIN insert into log_trace (`msg`, `utc`, `tag`) values(p_msg, now(), p_tag); END ;; DELIMITER ; DROP PROCEDURE IF EXISTS `sp_run_sql`; DELIMITER ;; CREATE PROCEDURE `sp_run_sql`( in p_sql text, in p_for varchar(50), out p_cnt int ) BEGIN declare exit handler for sqlexception call sp_error_logger(p_sql, p_for); declare continue handler for sqlwarning call sp_error_logger(p_sql, p_for); set p_cnt = 0; set @txt = concat('begin sql: ',p_sql); call sp_logger(@txt, p_for); set @txt = p_sql; prepare stm from @txt; execute stm; set p_cnt = row_count(); deallocate prepare stm; set @txt = concat(p_cnt,' row(s) affected/returned by sql: ',p_sql); call sp_logger(@txt, p_for); END ;; DELIMITER ; show procedure status where db=database(); show
