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

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.