List:General Discussion« Previous MessageNext Message »
From:D. Dante Lorenso Date:December 11 2009 3:26am
Subject:Re: stored procedure and random table name -> temp table, merge,
prepared statement
View as plain text  
Gavin Towey wrote:
> Creating a temporary merge table works fine for me on 5.0.
> Your table isn't innodb is it?  That will fail with an error like you're getting.

Strange.  Now that I am on my home network and trying this again, it 
seems to be working.

mysql> SELECT version();
+------------------+
| version()        |
+------------------+
| 5.1.41-community |
+------------------+
1 row in set (0.00 sec)

Here is my stored procedure:

-------------------- 8< --------------------
CREATE PROCEDURE `test_massage_table`(IN in_table_name VARCHAR(64)) 

     NOT DETERMINISTIC
     MODIFIES SQL DATA
BEGIN
     -- remove temp table
     DROP TABLE IF EXISTS test_temp;

     -- clone table structure from submitted table
     SET @s = CONCAT('CREATE TABLE test_temp LIKE ', in_table_name);
     PREPARE stmt FROM @s;
     EXECUTE stmt;

     -- convert table type to MERGE.  Pass through to original table
     SET @s = CONCAT('ALTER TABLE test_temp ENGINE=MERGE UNION(', 
in_table_name, ')');
     PREPARE stmt FROM @s;
     EXECUTE stmt;

     -- test query 1
     UPDATE test_temp SET value = value * value;

     -- test query 2
     UPDATE test_temp SET modified = NOW();

     -- test query 3
     DELETE FROM test_temp WHERE value > 10;

     -- test query 4
     SELECT *
     FROM test_temp;
END;
-------------------- 8< --------------------

Then, here is the code I used to test it:

-------------------- 8< --------------------
-- destroy tables
DROP TABLE IF EXISTS test_table_odds;
DROP TABLE IF EXISTS test_table_evens;
DROP TABLE IF EXISTS test_temp;
-- 
-- create new tables
CREATE TABLE `test_table_odds` (
   `value` int(11) unsigned NOT NULL,
   `modified` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE test_table_evens LIKE test_table_odds;
--
-- add sample data
INSERT INTO test_table_odds (value) VALUES (1), (3), (5);
INSERT INTO test_table_evens (value) VALUES (2), (4), (6);
--
-- check table
SELECT * FROM test_table_odds;
SELECT * FROM test_table_evens;
--
-- run new procedure stuff
CALL test_massage_table('test_table_odds');
CALL test_massage_table('test_table_evens');
-------------------- 8< --------------------

And here is my output:

-------------------- 8< --------------------
mysql> -- destroy tables
mysql> DROP TABLE IF EXISTS test_table_odds;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS test_table_evens;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS test_temp;
Query OK, 0 rows affected (0.00 sec)

mysql> --
mysql> -- create new tables
mysql> CREATE TABLE `test_table_odds` (
     ->   `value` int(11) unsigned NOT NULL,
     ->   `modified` datetime DEFAULT NULL
     -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE test_table_evens LIKE test_table_odds;
--
-- add sample data
INSERT INTO test_table_odds (value) VALUES (1), (3), (5);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE test_table_evens LIKE test_table_odds;
Query OK, 0 rows affected (0.00 sec)

mysql> --
mysql> -- add sample data
mysql> INSERT INTO test_table_odds (value) VALUES (1), (3), (5);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test_table_evens (value) VALUES (2), (4), (6);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> --
mysql> -- check table
mysql> SELECT * FROM test_table_odds;
+-------+----------+
| value | modified |
+-------+----------+
|     1 | NULL     |
|     3 | NULL     |
|     5 | NULL     |
+-------+----------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM test_table_evens;
+-------+----------+
| value | modified |
+-------+----------+
|     2 | NULL     |
|     4 | NULL     |
|     6 | NULL     |
+-------+----------+
3 rows in set (0.00 sec)

mysql> --
mysql> -- run new procedure stuff
mysql> CALL test_massage_table('test_table_odds');
+-------+---------------------+
| value | modified            |
+-------+---------------------+
|     1 | 2009-12-10 21:18:59 |
|     9 | 2009-12-10 21:18:59 |
+-------+---------------------+
2 rows in set (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CALL test_massage_table('test_table_evens');
+-------+---------------------+
| value | modified            |
+-------+---------------------+
|     4 | 2009-12-10 21:19:01 |
+-------+---------------------+
1 row in set (0.15 sec)

Query OK, 0 rows affected (0.15 sec)

-------------------- 8< --------------------

So, thanks for the reply.  I hope someone can review what I've done here 
and let me know if there is a smarter way to accomplish what I'm trying 
to do.  Otherwise, I'll have to review what I was working on at the 
office and figure out why that wasn't working for me.  It might be a 
mysql version difference, but I'm guessing it was something to do with 
the original table being too complex for the Merge table to work.  Maybe 
indexes not matching?

Anyhow, let me know what you think of this strategy for sidestepping the 
need for prepared statements when working with MyISAM tables passed to 
stored procedures.

-- Dante


> 
> Regards,
> Gavin Towey
> 
> -----Original Message-----
> From: Dante Lorenso [mailto:dante@stripped]
> Sent: Thursday, December 10, 2009 3:20 PM
> To: mysql@stripped
> Subject: stored procedure and random table name -> temp table, merge, prepared
> statement
> 
> 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
Thread
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