A General Purpose Dynamic Cursor - Part 2 of 3

Permalink: http://bit.ly/RcRieg



Refer to part 1 for the rationale behind the code or you can skip to part 3 for a working example as well as how you can debug the stored procedure.

Important: The SP will create a table named `dynamic_cursor`. Make sure this table does not exist in the database where you will be storing the procedure. Here's the 1st iteration of a general purpose dynamic cursor:

DELIMITER $$
DROP PROCEDURE IF EXISTS `dynamicCursor` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `dynamicCursor`(
 IN selectStmt TEXT,
 IN whatAction VARCHAR(255),
 INOUT inOutParam VARCHAR(255))
BEGIN
 DECLARE colVal1, colVal2, colVal3 TEXT;
 DECLARE colCount TINYINT;
 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;
 SELECT COUNT(*)
 FROM `information_schema`.`COLUMNS`
 WHERE (`TABLE_SCHEMA` = DATABASE())
  AND (`TABLE_NAME` = 'dynamic_cursor')
 INTO colCount;
 OPEN dynamicCursor;
 dynamicCursorLoop: LOOP
  CASE colCount
   WHEN 1 THEN
    FETCH dynamicCursor INTO colVal1;
   WHEN 2 THEN
    FETCH dynamicCursor INTO colVal1, colVal2;
   WHEN 3 THEN
    FETCH dynamicCursor INTO colVal1, colVal2, colVal3;
  END CASE;
  IF (noMoreRows)
  THEN
   CLOSE dynamicCursor;
   LEAVE dynamicCursorLoop;
  END IF;
  CASE whatAction
  WHEN 'doSomething'
  THEN
   BEGIN
   END;
  ELSE
   BEGIN
   END;
  END CASE;
 END LOOP dynamicCursorLoop;
 DROP TABLE `dynamic_cursor`;
END $$
DELIMITER ;

Regarding MySQL's limitations on cursors: As of version 5.5, the above stored procedure will only reliably work on the first call in the current session. Subsequent calls may fail if the SELECT statement passed is different from the first one. This is because the cursor is traversing the column names from the first call and is not aware that the columns it's supposed to read are now different. The cursor reads its own copy of the table defined from the first call, which results to stale data when the columns of the table are subsequently altered. Part 3 implements the workaround to this inherent MySQL cursor limitation. The bug is due to MySQL's optimization on the SELECT statement where it wrongly assumes that the columns are still the same.

The code snippet can accept a SELECT statement parameter that will produce a result set from 1-3 columns. You may want to increase the maximum to around 12 columns or even more. Simply increase the number of ColValN variables and create additional cases for colCount and write their FETCH statements.

The intention here is that the statements to execute using the data fetched from the cursor can be written inside the cases for the whatAction string parameter. You may want to create additional CASE constructs before the cursor's OPEN and/or after the LOOP is ended depending on your specific needs.

Additionally, you can add additional INOUT parameters as needed like inOutParam1, inOutParam2, ... inOutParamN. However, keep in mind that the procedure relies on MySQL's automatic data type conversion. This means that in order to keep the procedure able to accept a broad range of inputs and maintain its general purpose nature, you need to keep using generic data types such as VARCHAR or TEXT. The data will be converted at run-time if it isn't a VARCHAR or TEXT variable.



Part 3 shows an example that performs an action with the cursor data as well as a good way to debug the stored procedure.

Return to part 1 for the rationale behind the code.



Updated: 10/22/2012