List:General Discussion« Previous MessageNext Message »
From:Dante Lorenso Date:December 10 2009 11:20pm
Subject:stored procedure and random table name -> temp table, merge, prepared
View as plain text  

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
-- 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
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

stored procedure and random table name -> temp table, merge, prepared statementDante Lorenso11 Dec
  • RE: stored procedure and random table name -> temp table, merge,prepared statementGavin Towey11 Dec
    • Re: stored procedure and random table name -> temp table, merge,prepared statementD. Dante Lorenso11 Dec