BqZwHGLr

1DELIMITER $$
2DROP PROCEDURE IF EXISTS setupLogging $$
3CREATE PROCEDURE setupLogging() 
4BEGIN
5	CREATE TABLE IF NOT EXISTS sp_logger(ts timestamp DEFAULT current_timestamp, thingID bigint, msg varchar(512)) ENGINE = MyISAM; 
6END $$
7
8CALL setupLogging() $$
9
10DROP PROCEDURE IF EXISTS setupTmpLog $$
11CREATE PROCEDURE setupTmpLog()
12BEGIN
13	CREATE TEMPORARY TABLE IF NOT EXISTS tmplog (msg varchar(512)) ENGINE = MEMORY;
14END $$
15
16DROP PROCEDURE IF EXISTS doLog $$
17CREATE PROCEDURE doLog(in logMsg varchar(512))
18BEGIN
19	DECLARE CONTINUE HANDLER FOR 1146 -- Table not found
20
21	BEGIN
22		CALL setupTmpLog();
23
24		INSERT INTO tmplog VALUES('resetup tmp table');
25		INSERT INTO tmplog VALUES(logMsg);
26	END;
27	
28    INSERT INTO tmplog VALUES(logMsg);
29END $$
30
31DROP PROCEDURE IF EXISTS saveAndLog $$
32CREATE PROCEDURE saveAndLog(IN thingId INT, IN lastMsg varchar(512))
33BEGIN
34   CALL doLog(lastMsg);
35   INSERT INTO sp_logger(thingId, msg) (SELECT thingId, msg FROM tmplog);
36   TRUNCATE TABLE tmplog;
37END $$
38
39
40-- Example of using the Logger
41DROP PROCEDURE IF EXISTS parseAndStoreList $$
42CREATE PROCEDURE parseAndStoreList(
43	in thingId int,
44	in i_list varchar (128),
45    out returnCode INT)
46BEGIN
47    DECLARE v_loopIndex INT DEFAULT 0;
48    DECLARE Exit Handler FOR SQLEXCEPTION
49    BEGIN
50         call saveAndLog(thingId, 'got exception parsing list');    -- save the logs if things go badly
51         set returnCode = -1;
52    END;
53 
54    call doLog(concat_ws('got list:', i_list));   -- say we got to the start
55    parse_loop: LOOP
56         set v_loopIndex = v_loopIndex + 1;
57         call doLog(concat_wc(',', 'at loop iteration ',  v_loopIndex));  -- say we got to nth iteration
58         -- actually do the parsing, or whatever
59    END LOOP parse_loop;
60    set returnCode = 0;
61 END $$
62 
63DELIMITER ;
64
65
66CALL parseAndStoreList(12, 786, @returnCode);
67
68select @returnCode;