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

[9 Sep 2005 20:46] philip antoniades
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
[9 Sep 2005 20:56] Miguel Solorzano
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>
[13 Sep 2005 20:36] philip antoniades
Hi, 

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

thanks, 

Philip
[24 Oct 2005 20:54] Bugs System
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
[24 Oct 2005 21:05] Alexander Nozdrin
Approved by Sanja Byelkin by email.
Fixed in 5.0, currently tagged 5.0.15.
[27 Oct 2005 7:25] Jon Stephens
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.