The mysql db I am using is in a Hosted platform. I use the connection type to be TCP and provide all the necessary details like HOST, DB name, username etc. I am able to successfully login into the DB via TOAD for mysql and all the tables, views get reflected.
When I click on Procedures, I see the MySQL Database error
SELECT Command denied for user 'xxx' for table 'proc'
I would like to have the group members input on how to overcome this issue.
When I execute the below script, it reflects all the procedures in the DB which I created using a different tool.
I think it was a bug because the right way to retreive procedures list is to use "select * from information_schema.routines where routine_schema='databaseName'" instead of "select * from mysql.proc where db='databaseName'".
Using the second way, that pre-supposes to have extended rights on the 'mysql' database ! But it's not always possible !
Using the proc table to determine what stored procedures are available is just as valid as using the INFORMATION_SCHEMA tables. It isn't Toad's fault that the administrators of this specific instance of MySQL has set up these privileges.
You can edit the SQL that Toad uses to fetch this data yourself though by going into the Tools|Options. Then select the page Database|SQL Dictionary. Go through the list on the top right and find the SQL Db.CommonSql|ListFunctions & Db.CommonSql|ListProcedures. Then you can put in whatever SQL you want Toad to use for fetching this list. However be careful to use the same kind of bind variables and columns as the original SQL or otherwise you might be unexpected results.
Does anyone have any idea how to resolve the discrepancy above? I put the appropriate query in the SQL dictionary, but when I click on the 'Functions' tab in Toad it runs a different query and produces an error. Any help is GREATLY appreciated.
I finally figured out the problem. It turns out that I had changed the code in the SQL Dictionary for the wrong version of mysql. I needed to go into the 'Selection:' dropdown and select the other version of mysql and then put the the code that mauritz provided. Everything worked perfect after that. Thanks to everyone for your help.