A General Purpose Dynamic Cursor - Part 3 of 3

Permalink: http://bit.ly/1jxurF0



See part 1 for the rationale behind the code and part 2 to understand limitations faced by cursors in MySQL.

The code snippet below shows an example that performs an action with the cursor data as well as a good way to debug the stored procedure. This iteration also works around MySQL's cursor problem mentioned in part 2 by calling the dynamic cursor fix stored procedure:

DELIMITER $$
DROP PROCEDURE IF EXISTS `dynamicCursor` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `dynamicCursor`(
 IN selectStmt TEXT,
 IN whatAction VARCHAR(255),
 INOUT inOutParam VARCHAR(255))
BEGIN
 #Used by whatAction ='debug'
 DECLARE cursorRead TEXT DEFAULT '';
 #Cursor-specific variables
 DECLARE colVal1, colVal2, colVal3 TEXT;
 DECLARE noMoreRows BOOLEAN DEFAULT FALSE;
 DECLARE dynamicCursor CURSOR FOR
  SELECT *
  FROM `dynamic_cursor`;
 DECLARE CONTINUE HANDLER
  FOR 1329
 SET noMoreRows = TRUE;
 DROP TABLE IF EXISTS `dynamic_cursor`;
 SET @createTableStmt = CONCAT(
  'CREATE TABLE `dynamic_cursor` AS ',
  selectStmt);
 PREPARE createTableStmt FROM @createTableStmt;
 EXECUTE createTableStmt;
 DEALLOCATE PREPARE createTableStmt;
 CALL dynamicCursorFix('dynamic_cursor');
 OPEN dynamicCursor;
 dynamicCursorLoop: LOOP
 FETCH dynamicCursor INTO colVal1, colVal2, colVal3;
  IF (noMoreRows)
  THEN
   CLOSE dynamicCursor;
   LEAVE dynamicCursorLoop;
  END IF;
  CASE whatAction
  WHEN 'debug'
  THEN
   SET cursorRead = CONCAT_WS(', ', 
     cursorRead, colVal1, colVal2, colVal3);
  ELSE
   BEGIN
   END;
  END CASE;
 END LOOP dynamicCursorLoop;
 CASE whatAction
 WHEN 'debug'
 THEN
  SELECT cursorRead 'Values read by the cursor';
 ELSE
  BEGIN
  END;
 END CASE;
 DROP TABLE `dynamic_cursor`;
END $$
DELIMITER ;

Important: The workaround for MySQL's cursor limitation that causes cursors to read stale table data is implemented in a stored procedure dynamicCursorFix(). This solution is implemented externally from the dynamic cursor so that it may be called by other nested dynamic cursors if needed. You can find this fix at this link: http://mysql-0v34c10ck.blogspot.com/2011/05/dynamic-cursor-fix.html.

You can test the function and/or debug the dynamic cursor by passing the following SELECT statement and specifying the 'debug' action:

CALL dynamicCursor(
 "SELECT T.`TABLE_NAME`
  FROM `information_schema`.`TABLES` T
  WHERE `TABLE_SCHEMA` = DATABASE()",
 'debug',
 @NULL);

The INOUT parameter is not used by the 'debug' action so we have set it to a dummy variable @NULL. For the above given SELECT statement, expect the result set to contain 'dynamic_cursor' together with any existing tables in the current database to confirm that the stored procedure is working. This also demonstrates how to implement actions that will be performed when the cursor fetches data.

You will want to replace the SELECT statement to a specific SELECT statement you are analyzing to pass to the dynamic cursor. Remember that a temporarily created table is present inside the current database and you may want to filter it out of your SELECT statement by adding a WHERE clause that excludes 'dynamic_cursor%' or as the case may be.



You may opt to nest several dynamic cursors inside the case for the 'whatAction' parameter if needed. Simply copy the stored procedure and change its name to `dynamicCursorNested` or the like, and the table name `dynamic_cursor` to another name such as `dynamic_cursor_nested`. The nested cursors will need to depend on the same fix mentioned above.

Return to part 1 or part 2 or see another workaround on a missing MySQL feature, disabling/enabling triggers on demand.