MySQL Bugs: #13095: Cannot create VIEWs in prepared statements

Bug #13095	Cannot create VIEWs in prepared statements
Submitted:	9 Sep 2005 20:46	Modified:	27 Oct 2005 7:25
Reporter:	philip antoniades	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.13-beta	OS:	Linux (Linux (RHAS 3.0))
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any

Description:
Attempting to create a view dynamically through the PREPARE, EXECUTE method in a procedure produces the error:
ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

How to repeat:
create procedure dynamic_view
(
   str_tablename 	varchar(32)
)
BEGIN
	SET @create_table = CONCAT( "CREATE TABLE IF NOT EXISTS ", str_tablename, "( stuff char(15) )" );
	PREPARE stmt_tbl FROM @create_table;
	EXECUTE stmt_tbl;

	SET @add_rows = CONCAT( "INSERT INTO ", str_tablename, " VALUES( 'rar' ),( 'gar' ),( 'har' )" );
	PREPARE stmt_tbl FROM @add_rows;
	EXECUTE stmt_tbl;

	SET @create_view = CONCAT( "CREATE VIEW MyView( mystuff ) AS SELECT stuff FROM ", str_tablename );
	PREPARE stmt_view FROM @create_view;
	EXECUTE stmt_view;

	SELECT * FROM MyView;
END

I was unable to repeat with below changeset:

ChangeSet@1.1961, 2005-09-08 18:27:05+02:00, petr@mysql.com
  Merge bk-internal.mysql.com:/home/bk/mysql-5.0
  into mysql.com:/usersnfs/pchardin/mysql-5.0

miguel@hegel:~/dbs/5.0>  bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.13-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delimiter //
mysql> create procedure dynamic_view
    -> (
    ->    str_tablename varchar(32)
    -> )
    -> BEGIN
    -> SET @create_table = CONCAT( "CREATE TABLE IF NOT EXISTS ", str_tablename, "(
    "> stuff char(15) )" );
    -> PREPARE stmt_tbl FROM @create_table;
    -> EXECUTE stmt_tbl;
    -> 
    -> SET @add_rows = CONCAT( "INSERT INTO ", str_tablename, " VALUES( 'rar' ),(
    "> 'gar' ),( 'har' )" );
    -> PREPARE stmt_tbl FROM @add_rows;
    -> EXECUTE stmt_tbl;
    -> 
    -> SET @create_view = CONCAT( "CREATE VIEW MyView( mystuff ) AS SELECT stuff FROM
    "> ", str_tablename );
    -> PREPARE stmt_view FROM @create_view;
    -> EXECUTE stmt_view;
    -> 
    -> SELECT * FROM MyView;
    -> END//
Query OK, 0 rows affected (0.03 sec)

mysql> show create procedure dynamic_view\G
*************************** 1. row ***************************
       Procedure: dynamic_view
        sql_mode: 
Create Procedure: CREATE PROCEDURE `dynamic_view`(
   str_tablename varchar(32)
)
BEGIN
SET @create_table = CONCAT( "CREATE TABLE IF NOT EXISTS ", str_tablename, "(
stuff char(15) )" );
PREPARE stmt_tbl FROM @create_table;
EXECUTE stmt_tbl;
SET @add_rows = CONCAT( "INSERT INTO ", str_tablename, " VALUES( 'rar' ),(
'gar' ),( 'har' )" );
PREPARE stmt_tbl FROM @add_rows;
EXECUTE stmt_tbl;
SET @create_view = CONCAT( "CREATE VIEW MyView( mystuff ) AS SELECT stuff FROM
", str_tablename );
PREPARE stmt_view FROM @create_view;
EXECUTE stmt_view;
SELECT * FROM MyView;
END
1 row in set (0.00 sec)

mysql>

Hi, 

Were you also able to execute the procedure and get results?

thanks, 

Philip

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/31405

Approved by Sanja Byelkin by email.
Fixed in 5.0, currently tagged 5.0.15.

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

Documented fix in 5.0.15 changelog.