All,
I have a stored procedure that I'm writing where I need to run a lot of
queries against a particular table. The name of the table will be a
parameter to the stored procedure ... example:
CALL normalize_data('name_of_table_here');
Since I want to run queries against this table, I don't want to have to use
prepared statements for all the queries because treating my queries as
strings gets ugly. Ideally I want to use the table name as a variable in
the stored procedure, but as a hack around that, I thought about trying this
trick instead: give the table name an alias.
-- remove our temporary table if it already exists
DROP TABLE IF EXISTS dante;
--
-- clone the table structure
CREATE TEMPORARY TABLE dante LIKE name_of_table_here;
--
-- change the temporary table to a merge table which references the named
table
ALTER TABLE dante ENGINE=MERGE UNION(name_of_table_here);
Once these 3 statements were run, the "merge" table would essentially just
be a view on the underlying table and all my following queries could
reference the "dante" table and not the strangely named random table.
Note, that queries above that use "name_of_table_here" would need to be
prepared and executed using the string concat approach.
The problem I am having is that this strategy is not working. After running
the statements above, I check my new "dante" table and it doesn't work:
DESC dante;
Error Code : 1168
Unable to open underlying table which is differently defined or of
non-MyISAM type or doesn't exist
So, how can I accomplish what I am trying to do? I just want to alias a
random table to a fixed name (preferably as a temporary table name so that
it won't conflict with other connections running similar code
simultaneously) so that I can avoid having to use prepared statements
through my whole stored procedure. I may potentially perform 20-30 queries
to the table which is passed in and want to keep this code looking clean.
I could avoid this problem altogether if I can assign an alias to a table:
ALIAS dante TO name_of_table_here;
or use a variable table name in a query inside a stored procedure:
SET @table_name = 'name_of_table_here';
INSERT INTO some_table (value)
SELECT something
FROM @table_name
WHERE ...;
Am using MySQL 5.1.36. Any pointers?
-- Dante
