This is all about db4free.net and the database systems that it offers. The main focus are practical examples of the new features of MySQL 5 and 6 that should show you how to use them for your advantage in your daily life ;-).
Yep, you're right - this would work without prepared statements as well. They would only be necessary if the parameters should be inserted e.g. for the table name (information_schema.KEY_COLUMN_USAGE in this example) or on other places where parameters can't be used directly (like the LIMIT clause - I filed a feature request to remove this limitation a while ago).
Anyway, it's great that my example inspired you to extend its functionality to create another useful example of how stored procedures and information_schema play together nicely ;-).
Yep, you're right - this would work without prepared statements as well. They would only be necessary if the parameters should be inserted e.g. for the table name (information_schema.KEY_COLUMN_USAGE in this example) or on other places where parameters can't be used directly (like the LIMIT clause - I filed a feature request to remove this limitation a while ago).
ReplyDeleteAnyway, it's great that my example inspired you to extend its functionality to create another useful example of how stored procedures and information_schema play together nicely ;-).
Hey Markus,
ReplyDeleteno it's supposed NOT to be a bug (..yeah right..) But feel free to join my campaign:
http://bugs.mysql.com/bug.php?id=14642
BTW Can't you do the query without dynamic sql? It makes it simpler in my opinion. Somthing like this:
create procedure bogus(
p_schema varchar(64)
, p_table varchar(64)
)
select concat(
table_schema
, '.'
, table_name
, ' ('
, constraint_name
, ') '
, group_concat(
column_name
, ' -> '
, referenced_column_name
order by ordinal_position
)
) as dependencies
from information_schema.key_column_usage kcu
where kcu.referenced_table_schema = p_schema
and kcu.referenced_table_name = p_table
group by constraint_schema
, constraint_name
, table_schema
, table_name
;
Or am i missing something?
I tried to change it, but then it worked once but crashed the server when I ran the procedure a second time. Seems like there's still a bug.
ReplyDeleteYour feature request is a good thing - I already added a comment that I'd like this feature, too ;-).