stored procedure and random table name -> temp table, merge, prepared

stored procedure and random table name -> temp table, merge, prepared

am 11.12.2009 00:20:21 von Dante Lorenso

--0016e6d977fd167107047a680d7f
Content-Type: text/plain; charset=UTF-8

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

--0016e6d977fd167107047a680d7f--

RE: stored procedure and random table name -> temp table, merge,prepared statement

am 11.12.2009 02:46:52 von Gavin Towey

Q3JlYXRpbmcgYSB0ZW1wb3JhcnkgbWVyZ2UgdGFibGUgd29ya3MgZmluZSBm b3IgbWUgb24gNS4w
Lg0KDQpZb3VyIHRhYmxlIGlzbid0IGlubm9kYiBpcyBpdD8gIFRoYXQgd2ls bCBmYWlsIHdpdGgg
YW4gZXJyb3IgbGlrZSB5b3UncmUgZ2V0dGluZy4NCg0KUmVnYXJkcywNCkdh dmluIFRvd2V5DQoN
Ci0tLS0tT3JpZ2luYWwgTWVzc2FnZS0tLS0tDQpGcm9tOiBEYW50ZSBMb3Jl bnNvIFttYWlsdG86
ZGFudGVAbG9yZW5zby5jb21dDQpTZW50OiBUaHVyc2RheSwgRGVjZW1iZXIg MTAsIDIwMDkgMzoy
MCBQTQ0KVG86IG15c3FsQGxpc3RzLm15c3FsLmNvbQ0KU3ViamVjdDogc3Rv cmVkIHByb2NlZHVy
ZSBhbmQgcmFuZG9tIHRhYmxlIG5hbWUgLT4gdGVtcCB0YWJsZSwgbWVyZ2Us IHByZXBhcmVkIHN0
YXRlbWVudA0KDQpBbGwsDQoNCkkgaGF2ZSBhIHN0b3JlZCBwcm9jZWR1cmUg dGhhdCBJJ20gd3Jp
dGluZyB3aGVyZSBJIG5lZWQgdG8gcnVuIGEgbG90IG9mDQpxdWVyaWVzIGFn YWluc3QgYSBwYXJ0
aWN1bGFyIHRhYmxlLiAgVGhlIG5hbWUgb2YgdGhlIHRhYmxlIHdpbGwgYmUg YQ0KcGFyYW1ldGVy
IHRvIHRoZSBzdG9yZWQgcHJvY2VkdXJlIC4uLiBleGFtcGxlOg0KDQpDQUxM IG5vcm1hbGl6ZV9k
YXRhKCduYW1lX29mX3RhYmxlX2hlcmUnKTsNCg0KU2luY2UgSSB3YW50IHRv IHJ1biBxdWVyaWVz
IGFnYWluc3QgdGhpcyB0YWJsZSwgSSBkb24ndCB3YW50IHRvIGhhdmUgdG8g dXNlDQpwcmVwYXJl
ZCBzdGF0ZW1lbnRzIGZvciBhbGwgdGhlIHF1ZXJpZXMgYmVjYXVzZSB0cmVh dGluZyBteSBxdWVy
aWVzIGFzDQpzdHJpbmdzIGdldHMgdWdseS4gIElkZWFsbHkgSSB3YW50IHRv IHVzZSB0aGUgdGFi
bGUgbmFtZSBhcyBhIHZhcmlhYmxlIGluDQp0aGUgc3RvcmVkIHByb2NlZHVy ZSwgYnV0IGFzIGEg
aGFjayBhcm91bmQgdGhhdCwgSSB0aG91Z2h0IGFib3V0IHRyeWluZyB0aGlz DQp0cmljayBpbnN0
ZWFkOiBnaXZlIHRoZSB0YWJsZSBuYW1lIGFuIGFsaWFzLg0KDQotLSByZW1v dmUgb3VyIHRlbXBv
cmFyeSB0YWJsZSBpZiBpdCBhbHJlYWR5IGV4aXN0cw0KRFJPUCBUQUJMRSBJ RiBFWElTVFMgZGFu
dGU7DQotLQ0KLS0gY2xvbmUgdGhlIHRhYmxlIHN0cnVjdHVyZQ0KQ1JFQVRF IFRFTVBPUkFSWSBU
QUJMRSBkYW50ZSBMSUtFIG5hbWVfb2ZfdGFibGVfaGVyZTsNCi0tDQotLSBj aGFuZ2UgdGhlIHRl
bXBvcmFyeSB0YWJsZSB0byBhIG1lcmdlIHRhYmxlIHdoaWNoIHJlZmVyZW5j ZXMgdGhlIG5hbWVk
DQp0YWJsZQ0KQUxURVIgVEFCTEUgZGFudGUgRU5HSU5FPU1FUkdFIFVOSU9O KG5hbWVfb2ZfdGFi
bGVfaGVyZSk7DQoNCk9uY2UgdGhlc2UgMyBzdGF0ZW1lbnRzIHdlcmUgcnVu LCB0aGUgIm1lcmdl
IiB0YWJsZSB3b3VsZCBlc3NlbnRpYWxseSBqdXN0DQpiZSBhIHZpZXcgb24g dGhlIHVuZGVybHlp
bmcgdGFibGUgYW5kIGFsbCBteSBmb2xsb3dpbmcgcXVlcmllcyBjb3VsZA0K cmVmZXJlbmNlIHRo
ZSAiZGFudGUiIHRhYmxlIGFuZCBub3QgdGhlIHN0cmFuZ2VseSBuYW1lZCBy YW5kb20gdGFibGUu
DQoNCk5vdGUsIHRoYXQgcXVlcmllcyBhYm92ZSB0aGF0IHVzZSAibmFtZV9v Zl90YWJsZV9oZXJl
IiB3b3VsZCBuZWVkIHRvIGJlDQpwcmVwYXJlZCBhbmQgZXhlY3V0ZWQgdXNp bmcgdGhlIHN0cmlu
ZyBjb25jYXQgYXBwcm9hY2guDQoNClRoZSBwcm9ibGVtIEkgYW0gaGF2aW5n IGlzIHRoYXQgdGhp
cyBzdHJhdGVneSBpcyBub3Qgd29ya2luZy4gIEFmdGVyIHJ1bm5pbmcNCnRo ZSBzdGF0ZW1lbnRz
IGFib3ZlLCBJIGNoZWNrIG15IG5ldyAiZGFudGUiIHRhYmxlIGFuZCBpdCBk b2Vzbid0IHdvcms6
DQoNCkRFU0MgZGFudGU7DQpFcnJvciBDb2RlIDogMTE2OA0KVW5hYmxlIHRv IG9wZW4gdW5kZXJs
eWluZyB0YWJsZSB3aGljaCBpcyBkaWZmZXJlbnRseSBkZWZpbmVkIG9yIG9m DQpub24tTXlJU0FN
IHR5cGUgb3IgZG9lc24ndCBleGlzdA0KDQpTbywgaG93IGNhbiBJIGFjY29t cGxpc2ggd2hhdCBJ
IGFtIHRyeWluZyB0byBkbz8gIEkganVzdCB3YW50IHRvIGFsaWFzIGENCnJh bmRvbSB0YWJsZSB0
byBhIGZpeGVkIG5hbWUgKHByZWZlcmFibHkgYXMgYSB0ZW1wb3JhcnkgdGFi bGUgbmFtZSBzbyB0
aGF0DQppdCB3b24ndCBjb25mbGljdCB3aXRoIG90aGVyIGNvbm5lY3Rpb25z IHJ1bm5pbmcgc2lt
aWxhciBjb2RlDQpzaW11bHRhbmVvdXNseSkgc28gdGhhdCBJIGNhbiBhdm9p ZCBoYXZpbmcgdG8g
dXNlIHByZXBhcmVkIHN0YXRlbWVudHMNCnRocm91Z2ggbXkgd2hvbGUgc3Rv cmVkIHByb2NlZHVy
ZS4gIEkgbWF5IHBvdGVudGlhbGx5IHBlcmZvcm0gMjAtMzAgcXVlcmllcw0K dG8gdGhlIHRhYmxl
IHdoaWNoIGlzIHBhc3NlZCBpbiBhbmQgd2FudCB0byBrZWVwIHRoaXMgY29k ZSBsb29raW5nIGNs
ZWFuLg0KDQpJIGNvdWxkIGF2b2lkIHRoaXMgcHJvYmxlbSBhbHRvZ2V0aGVy IGlmIEkgY2FuIGFz
c2lnbiBhbiBhbGlhcyB0byBhIHRhYmxlOg0KDQpBTElBUyBkYW50ZSBUTyBu YW1lX29mX3RhYmxl
X2hlcmU7DQoNCm9yIHVzZSBhIHZhcmlhYmxlIHRhYmxlIG5hbWUgaW4gYSBx dWVyeSBpbnNpZGUg
YSBzdG9yZWQgcHJvY2VkdXJlOg0KDQpTRVQgQHRhYmxlX25hbWUgPSAnbmFt ZV9vZl90YWJsZV9o
ZXJlJzsNCg0KSU5TRVJUIElOVE8gc29tZV90YWJsZSAodmFsdWUpDQpTRUxF Q1Qgc29tZXRoaW5n
DQpGUk9NIEB0YWJsZV9uYW1lDQpXSEVSRSAuLi47DQoNCkFtIHVzaW5nIE15 U1FMIDUuMS4zNi4g
IEFueSBwb2ludGVycz8NCg0KLS0gRGFudGUNCg0KVGhpcyBtZXNzYWdlIGNv bnRhaW5zIGNvbmZp
ZGVudGlhbCBpbmZvcm1hdGlvbiBhbmQgaXMgaW50ZW5kZWQgb25seSBmb3Ig dGhlIGluZGl2aWR1
YWwgbmFtZWQuICBJZiB5b3UgYXJlIG5vdCB0aGUgbmFtZWQgYWRkcmVzc2Vl LCB5b3UgYXJlIG5v
dGlmaWVkIHRoYXQgcmV2aWV3aW5nLCBkaXNzZW1pbmF0aW5nLCBkaXNjbG9z aW5nLCBjb3B5aW5n
IG9yIGRpc3RyaWJ1dGluZyB0aGlzIGUtbWFpbCBpcyBzdHJpY3RseSBwcm9o aWJpdGVkLiAgUGxl
YXNlIG5vdGlmeSB0aGUgc2VuZGVyIGltbWVkaWF0ZWx5IGJ5IGUtbWFpbCBp ZiB5b3UgaGF2ZSBy
ZWNlaXZlZCB0aGlzIGUtbWFpbCBieSBtaXN0YWtlIGFuZCBkZWxldGUgdGhp cyBlLW1haWwgZnJv
bSB5b3VyIHN5c3RlbS4gRS1tYWlsIHRyYW5zbWlzc2lvbiBjYW5ub3QgYmUg Z3VhcmFudGVlZCB0
byBiZSBzZWN1cmUgb3IgZXJyb3ItZnJlZSBhcyBpbmZvcm1hdGlvbiBjb3Vs ZCBiZSBpbnRlcmNl
cHRlZCwgY29ycnVwdGVkLCBsb3N0LCBkZXN0cm95ZWQsIGFycml2ZSBsYXRl IG9yIGluY29tcGxl
dGUsIG9yIGNvbnRhaW4gdmlydXNlcy4gVGhlIHNlbmRlciB0aGVyZWZvcmUg ZG9lcyBub3QgYWNj
ZXB0IGxpYWJpbGl0eSBmb3IgYW55IGxvc3Mgb3IgZGFtYWdlIGNhdXNlZCBi eSB2aXJ1c2VzIG9y
IGVycm9ycyBvciBvbWlzc2lvbnMgaW4gdGhlIGNvbnRlbnRzIG9mIHRoaXMg bWVzc2FnZSwgd2hp
Y2ggYXJpc2UgYXMgYSByZXN1bHQgb2YgZS1tYWlsIHRyYW5zbWlzc2lvbi4g W0ZyaWVuZEZpbmRl
ciBOZXR3b3JrcywgSW5jLiwgMjIwIEh1bWJvbHQgY291cnQsIFN1bm55dmFs ZSwgQ0EgOTQwODks
IFVTQSwgRnJpZW5kRmluZGVyLmNvbQ0K

Re: stored procedure and random table name -> temp table, merge,prepared statement

am 11.12.2009 04:26:33 von Dante Lorenso

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@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=gcdmg-mysql-2@m.gmane.org