Dynamic Cursor Fix

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



This is the workaround to create dynamic cursors in MySQL and is meant to be used together with the general purpose dynamic cursor stored procedure.

Increase the compared value of colValN in the WHILE-DO construct to the maximum number of columns your dynamic cursor will be needing. This fixes MySQL's limitation on cursors by providing the dynamic cursor with consistent column names that are independent of the underlying SELECT statement and also provide a predictable number of columns.

The final result is that the temporarily created table will be constant, while the records that the cursor will traverse will remain dynamic. The code in part 3 of the general purpose dynamic cursor together with this fix creates a working dynamic cursor that can be called multiple times using different SELECT statement parameters with no stale table data returned.

DELIMITER $$
DROP PROCEDURE IF EXISTS `dynamicCursorFix` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `dynamicCursorFix`(
 IN tableName VARCHAR(64))
BEGIN
 DECLARE columnNo TINYINT DEFAULT 0;
 DECLARE columnName TEXT;
 DECLARE noMoreColumns BOOLEAN DEFAULT FALSE;
 DECLARE columnNameCursor CURSOR FOR
 SELECT `COLUMN_NAME`
  FROM `information_schema`.`COLUMNS`
  WHERE (`TABLE_SCHEMA` = DATABASE())
   AND (`TABLE_NAME` = tableName);
 DECLARE CONTINUE HANDLER
  FOR 1329
  SET noMoreColumns = TRUE;
 OPEN columnNameCursor;
 columnNameCursorLoop: LOOP
 FETCH columnNameCursor INTO columnName;
  IF (noMoreColumns)
  THEN
   CLOSE columnNameCursor;
   LEAVE columnNameCursorLoop;
  END IF;
  SET columnNo = columnNo + 1;
  SET @changeColStmt = CONCAT(
   'ALTER TABLE `', tableName, '`
   CHANGE `', columnName, '` `column', columnNo, '` TEXT');
  PREPARE changeColStmt FROM @changeColStmt;
  EXECUTE changeColStmt;
  DEALLOCATE PREPARE changeColStmt;
 END LOOP columnNameCursorLoop;
 #Increase as needed by the dynamic cursor
 WHILE columnNo < 3
 DO
  SET columnNo = columnNo + 1;
  SET @addColStmt = CONCAT(
   'ALTER TABLE `', tableName, '`
   ADD `column', columnNo, '` TINYINT');
  PREPARE addColStmt FROM @addColStmt;
  EXECUTE addColStmt;
  DEALLOCATE PREPARE addColStmt;
 END WHILE;
END $$
DELIMITER ;

Call this fix from 'dynamicCursor' by passing the table name of its temporarily created table 'dynamic_cursor'. This same fix should be called by any nested dynamic cursor like 'dynamicCursorNested' and passing the nested dynamic cursor's temporarily created table name such as 'dynamic_cursor_nested'. See the 3rd part of the general purpose dynamic cursor for an explanation on how to nest dynamic cursors.

See part 1 for the rationale on creating a dynamic cursor. Temporary tables are not the way to go due to MySQL's limitation of hiding temporary tables from the `information_schema` database. Part 2 discusses how MySQL might fetch stale table data if different SELECT statements are passed to a dynamic cursor. This problem also occurs when the tables referred to by a fixed cursor's SELECT statement are altered by changing the table's columns.



This fix applies for the final iteration found in part 3 of the general purpose dynamic cursor blog entry and by continuing to use normal (not temporary) tables, the column names are made visible in the `information_schema` database.