MySql StoredProcedure Logger

MySql-StoredProcedure-Logger.sql

DELIMITER $$
DROP PROCEDURE IF EXISTS setupLogging $$
CREATE PROCEDURE setupLogging() 
BEGIN
	CREATE TABLE IF NOT EXISTS sp_logger(ts timestamp DEFAULT current_timestamp, thingID bigint, msg varchar(512)) ENGINE = MyISAM; 
END $$

CALL setupLogging() $$

DROP PROCEDURE IF EXISTS setupTmpLog $$
CREATE PROCEDURE setupTmpLog()
BEGIN
	CREATE TEMPORARY TABLE IF NOT EXISTS tmplog (msg varchar(512)) ENGINE = MEMORY;
END $$

DROP PROCEDURE IF EXISTS doLog $$
CREATE PROCEDURE doLog(in logMsg varchar(512))
BEGIN
	DECLARE CONTINUE HANDLER FOR 1146 -- Table not found

	BEGIN
		CALL setupTmpLog();

		INSERT INTO tmplog VALUES('resetup tmp table');
		INSERT INTO tmplog VALUES(logMsg);
	END;
	
    INSERT INTO tmplog VALUES(logMsg);
END $$

DROP PROCEDURE IF EXISTS saveAndLog $$
CREATE PROCEDURE saveAndLog(IN thingId INT, IN lastMsg varchar(512))
BEGIN
   CALL doLog(lastMsg);
   INSERT INTO sp_logger(thingId, msg) (SELECT thingId, msg FROM tmplog);
   TRUNCATE TABLE tmplog;
END $$


-- Example of using the Logger
DROP PROCEDURE IF EXISTS parseAndStoreList $$
CREATE PROCEDURE parseAndStoreList(
	in thingId int,
	in i_list varchar (128),
    out returnCode INT)
BEGIN
    DECLARE v_loopIndex INT DEFAULT 0;
    DECLARE Exit Handler FOR SQLEXCEPTION
    BEGIN
         call saveAndLog(thingId, 'got exception parsing list');    -- save the logs if things go badly
         set returnCode = -1;
    END;
 
    call doLog(concat_ws('got list:', i_list));   -- say we got to the start
    parse_loop: LOOP
         set v_loopIndex = v_loopIndex + 1;
         call doLog(concat_wc(',', 'at loop iteration ',  v_loopIndex));  -- say we got to nth iteration
         -- actually do the parsing, or whatever
    END LOOP parse_loop;
    set returnCode = 0;
 END $$
 
DELIMITER ;


CALL parseAndStoreList(12, 786, @returnCode);

select @returnCode;