Making a union of views in MySQL Stored Procedure

Question

I'm creating a MySQL stored procedure that receives the names of two views, and performs a Union upon them into the first of the two.

I've used prepared statements to take view names as strings, so I can use this stored procedure with a wealth of other stored procedures that produce views of varying names.

The following code works as expected:

DROP view if exists test1;
DROP view if exists test2;

CREATE VIEW test1 as SELECT "Cows";
CREATE VIEW test2 as SELECT "Horses";

DROP VIEW if exists tempView;
CREATE VIEW tempView AS SELECT * FROM test1 UNION SELECT * FROM test2;
SELECT * from tempView;

However, when I execute the following code:

DELIMITER //
DROP PROCEDURE IF EXISTS SP_unionViews //
CREATE PROCEDURE SP_unionViews(IN viewname varchar(255), 
                               IN viewname2 varchar(255))
BEGIN
    DROP VIEW IF EXISTS tempView;
    SET @in1 = viewname;
    SET @in2 = viewname2;
    SET @str = 'CREATE VIEW tempView AS 
       SELECT * FROM ? UNION SELECT * FROM ?';
    PREPARE stmt FROM @str;
    EXECUTE stmt USING @in1, @in2;

    SET @str2 = 'DROP VIEW ?';
    SET @in3 = viewname;
    PREPARE stmt2 FROM @str2;
    EXECUTE stmt2 USING @in3;

    SET @str3 = 'CREATE VIEW ? AS SELECT * FROM tempView';
    PREPARE stmt3 FROM @str3;
    EXECUTE stmt3 USING @in3;

    DEALLOCATE PREPARE stmt;
    DEALLOCATE PREPARE stmt2;
    DEALLOCATE PREPARE stmt3;
END //
DELIMITER ;

DROP view if exists test1;
DROP view if exists test2;

CREATE VIEW test1 as SELECT "Cows";
CREATE VIEW test2 as SELECT "Horses";
CALL SP_unionViews(test1, test2);
SELECT * from test1;

I get the following error:

#1054 - Unknown column 'test1' in 'field list'

That would seem to indicate that this stored procedure is trying to use test1 as a column somewhere it's not intended to be one. But I can't figure out where.

UPDATE: When edited as below, I expected the solution to work, but it gives me another error.

The new code reads:

DELIMITER //
DROP PROCEDURE IF EXISTS SP_unionViews //
CREATE PROCEDURE SP_unionViews(IN viewname varchar(255), 
                               IN viewname2 varchar(255))
BEGIN
    DROP VIEW IF EXISTS tempView;
    SET @str = CONCAT('
    CREATE VIEW tempView AS 
        SELECT * FROM ', viewname, ' UNION SELECT * FROM ', viewname2);
    PREPARE stmt FROM @str;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET @str2 = CONCAT('DROP VIEW ', viewname, ';');
    PREPARE stmt2 FROM @str2;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;

    SET @str3 = CONCAT('CREATE VIEW ', viewname ,' AS SELECT * FROM tempView');
    PREPARE stmt3 FROM @str3;
    EXECUTE stmt3;
    DEALLOCATE PREPARE stmt3;
END //
DELIMITER ;

DROP view if exists test1;
DROP view if exists test2;

CREATE VIEW test1 as SELECT "Cows";
CREATE VIEW test2 as SELECT "Horses";
CALL SP_unionViews("test1", "test2");
SELECT * from test1;

And its error reads:

#1615 - Prepared statement needs to be re-prepared

btw, please don't worry about the 'Select *' stuff. This is boilerplate. — Feygon, Nov 17 '18 at 07:28
Edited to fix: "test1", "test2" needed to be string literals. — Feygon, Nov 17 '18 at 08:14
Also edited to fix: Creating a view didn't like the subquery. — Feygon, Nov 17 '18 at 08:14

Nick · Accepted Answer · 2018-11-17T07:38:36.510

1

You can't use parameters for table names in a prepared statement, so you will need to put the table names into the query string before you prepare the statement. Try changing your queries to these:

SET @str = CONCAT('
CREATE VIEW tempView AS 
    SELECT * FROM (
        SELECT * FROM ', viewname, ' UNION SELECT * FROM ', viewname2, '
    )');

SET @str2 = CONCAT('DROP VIEW ', viewname);

SET @str3 = CONCAT('CREATE VIEW ', viewname, ' AS SELECT * FROM tempView');

Once you have done this you will no longer need any parameters to the EXECUTEs

edited Nov 17 '18 at 07:38

answered Nov 17 '18 at 07:27

Nick

138,499
22
57
95

Are parameters in prepared statements only useful for columns and comparison values, then? – Feygon Nov 17 '18 at 07:33
Oh, I see. This is a question of what the '?' can do. – Feygon Nov 17 '18 at 07:34
@Feygon they can basically only be used for data literals. – Nick Nov 17 '18 at 07:37
@Feygon sorry about the missing `)`s, I've edited the answer. – Nick Nov 17 '18 at 07:39
New Error. I really thought your solution would work. – Feygon Nov 17 '18 at 08:14
It should... Here are some links which might solve this problem: https://stackoverflow.com/questions/4380813/how-to-get-rid-of-mysql-error-prepared-statement-needs-to-be-re-prepared https://dba.stackexchange.com/questions/144427/getting-error-err-1615-prepared-statement-needs-to-be-re-prepared-with-mys http://blog.corrlabs.com/2013/04/mysql-prepared-statement-needs-to-be-re.html – Nick Nov 17 '18 at 08:19
Oh, bummer. It looks like it's an issue with my super user status. I can't increase the cache size for creating tables, which is required for dynamic view creation using prepared statements. I guess I'll try something else. I want to keep using this platform. – Feygon Nov 17 '18 at 08:30

Making a union of views in MySQL Stored Procedure

1 Answers1