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