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:da...@lorenso.com]
Sent: Thursday, December 10, 2009 3:20 PM
To: mysql@lists.mysql.com
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
