{"id":91,"date":"2010-09-05T20:18:56","date_gmt":"2010-09-05T20:18:56","guid":{"rendered":"http:\/\/sqlornosql.10sa.com\/?p=91"},"modified":"2010-09-05T20:19:42","modified_gmt":"2010-09-05T20:19:42","slug":"mysql-plsql-errors-exceptions-support","status":"publish","type":"post","link":"http:\/\/10sa.com\/sql_stories\/?p=91","title":{"rendered":"MySQL PL\/SQL Errors \/ Exceptions support"},"content":{"rendered":"<p>Functions like mysql_error() in PHP or try\/catch blocks and MySqlException in C# are very helpful to discover problems.<br \/>\nUnfortunately, inside stored procedure (routine) there is no ready component to catch errors to see what would return SHOW ERRORS. But I have wanted such feature at my EVENT (Event Scheduler)! Hopefully, I realized that I can divide my code into many blocks with separate blocks responsible for error handling and thus collect these bad queries (then inspect&#8217;em all).<br \/>\nThat can be represented in such manner:<\/p>\n<pre lang=\"sql\">\r\nBEGIN\r\n DECLARE EXIT|CONTINUE HANDLER FOR SQLEXCEPTION;\r\n  BEGIN\r\n   DO SOMETHING ON ERROR;\r\n  END\r\n DO STH;\r\n BEGIN\r\n  DECLARE EXIT|CONTINUE HANDLER FOR SQLEXCEPTION;\r\n   BEGIN\r\n    DO SOMETHING ON ERROR2;\r\n   END\r\n  DO SOMETHING ELSE;\r\n END\r\nEND\r\n<\/pre>\n<p>Example:<\/p>\n<p>1. Create tables.<\/p>\n<pre lang=\"sql\">\r\nCREATE TABLE IF NOT EXISTS `routine_error` (\r\n  `id` int(10) unsigned NOT NULL,\r\n  PRIMARY KEY  (`id`)\r\n) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Test table';\r\n\r\n\r\nCREATE TABLE IF NOT EXISTS `routine_logs` (\r\n  `id` int(10) unsigned NOT NULL auto_increment,\r\n  `error_sql` text,\r\n  PRIMARY KEY  (`id`)\r\n) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Test table' AUTO_INCREMENT=1 ;\r\n\r\n\r\n<\/pre>\n<p>2. Create procedure.<\/p>\n<pre lang=\"sql\">\r\nCREATE PROCEDURE `RoutineErrorTest` ()\r\nMAIN_BLOCK: BEGIN\r\nDECLARE MyId INT(11) UNSIGNED DEFAULT 0;\r\nDECLARE done TINYINT DEFAULT 0;\r\nDECLARE MyCursor CURSOR FOR Select `Id` From  `routine_error`;\r\nDECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;\r\n  \r\nSET done = 0;\r\nOPEN MyCursor;\r\nREPEAT  \t\r\n IF done = 0 THEN\r\n  UNIQUE_JOIN_BLOCK: BEGIN\r\n   DECLARE EXIT HANDLER FOR SQLEXCEPTION\r\n    BEGIN \r\n     -- Make clear (Rollback, etc)\r\n     \/* Log. This works provided that the bug is not in this query :) *\/\r\n     INSERT INTO `routine_logs` (`id`, `error_sql`) VALUES (NULL, @InsertSQL);\r\n    END;\t\t\t\r\n   FETCH MyCursor INTO MyId;\r\n   SET @InsertSQL = CONCAT(\"INSERT INTO routine_error (`id`) VALUES(\", MyId, \");\");\r\n   PREPARE MyStmt FROM @InsertSQL;\r\n   EXECUTE MyStmt;\r\n  END UNIQUE_JOIN_BLOCK;\r\n END IF;\r\nUNTIL done END REPEAT;\r\nCLOSE MyCursor;\r\nEND MAIN_BLOCK;\r\n<\/pre>\n<p>Voila, your errors are here:<\/p>\n<pre lang=\"sql\">\r\nSELECT * FROM routine_logs;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Functions like mysql_error() in PHP or try\/catch blocks and MySqlException in C# are very helpful to discover problems. Unfortunately, inside stored procedure (routine) there is no ready component to catch errors to see what would return SHOW ERRORS. But I have wanted such feature at my EVENT (Event Scheduler)! Hopefully, I realized that I can [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[7,3],"tags":[],"_links":{"self":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/91"}],"collection":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=91"}],"version-history":[{"count":21,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/91\/revisions"}],"predecessor-version":[{"id":133,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/91\/revisions\/133"}],"wp:attachment":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=91"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=91"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=91"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}