Pages

Showing posts with label Cursor. Show all posts
Showing posts with label Cursor. Show all posts

Tuesday, May 10, 2011

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

Monday, May 9, 2011

A General Purpose Dynamic Cursor - Part 1 of 3

Permalink: http://bit.ly/TBKQL3



Skip to part 2 for the code snippet or to part 3 for a working example and how to debug the stored procedure.

Also another workaround on a missing MySQL functionality, enabling/disabling triggers, can be found here.

As of version 5.5, MySQL still does not have the native ability to execute a dynamic cursor. This can be worked around but the resulting stored procedure will have a few limitations.

This stored procedure is a general purpose dynamic cursor. It is general purpose, in the sense that, it qualifies as a template that you can modify to suite a specific purpose and/or expand to cover a broader range. It can be called by passing the following parameters:

CALL dynamicCursor(
 "SELECT * FROM `dbName`.`tableName`", 
 'whatAction', 
 @inOutParam);

The first parameter is the SELECT statement that the cursor is declared to use. Quotation marks can be used to encapsulate the string so that single quotes can be used by the statement itself if needed.

The second parameter is a string that specifies what action to do with the cursor traversal data-- the statements to execute are written inside the stored procedure as cases of the second parameter. What you do with the data is up to you.

The third parameter is an INOUT variable which will allow you to get a return or to pass additional parameters as required by your particular case. You can expand the number of INOUT variables to the needed amount by simply adding more in the stored procedure. If a return or passing additional variables are not needed, simply set the INOUT variable to a dummy variable such as @NULL.

The dynamic cursor logic can be achieved through a dynamic MySQL statement that creates a copy of the data from the SELECT statement of the cursor declaration into a created table. The cursor, instead of using the SELECT statement, is declared to SELECT the table that was created. This causes the table data to become the part which is dynamic and not the cursor itself.

The creation of this table then stems to two methods: create the table as a temporary table or to create the table as a normal (not temporary) table. Both methods have limitations:
  • If a normal table is created, MySQL will see it as a DML statement. An implicit commit will be triggered if this stored procedure is used inside transactions.
  • If a temporary table is created, it will be invisible for SHOW TABLES, SHOW OPEN TABLES, SHOW TABLE STATUS, and more importantly as stated in a previous blog entry, "it is not possible to get a column list into a variable if it comes from a temporary table. This is due to the fact that temporary tables are invisible within the `information_schema` database." This means that the cursor's FETCH statement will need to have a fixed number of variables.
  • Both methods have a performance impact on the system since we are creating a new table and copying data from a SELECT statement each time the stored procedure is called.

The advantages between the two are:
  • By creating a normal table, we can determine the number of columns from the `information_schema` database. The FETCH statement can be nested inside a condition construct and fetch the cursor traversal into the appropriate number of variables up to a reasonable maximum quantity.
  • By creating a temporary table, the stored procedure can be used inside transactions without causing an implicit commit.

Due to the fact that using the method of creating a temporary table will work only for SELECT statements with a fixed or predetermined number of columns, it defeats the purpose of being dynamic. A temporary table is not the way to go unless you absolutely need a dynamic cursor for a transaction.

The normal table method is what we will use, however, there is the issue of the maximum number of variables that the cursor's FETCH statement should have for practical coding purposes. This is because FETCH statements are not supported by prepared statements which can dynamically write the specific number of variables that need to be created and fetch data into. For example, an InnoDB table can permit up to a maximum of 1,000 columns. To simplify, this equates to 1,000 cases for each of the possible number of columns. It is not practical to write all the variables for each of the cases and the 1,000 cases themselves. It would be better to write a non-dynamic cursor construct for SELECT statements which produce result sets with a large number of columns.



Therefore, a decision as to when it is practical to use a dynamic cursor and when to use a fixed cursor needs to be made. A dynamic cursor should be used only for SELECT statements that will produce a small result set. The result set should also have a practical number of columns. Consider using a fixed cursor if these two conditions are not met.

The second part of this blog entry shows the code for a general purpose dynamic cursor stored procedure and the third part shows a working example and how to debug the cursor.



Updated: 10/20/2012