Re: Stored procedures utility library

[Giuseppe Maxia]

There is one more thing that we should take into account when talking about general purpose stored routines, i.e. to which database such routines should be linked.
Unfortunately, in MySQL SP language, each routine belongs to a database, and although you can invoke a routine with the db_name.routine_name syntax, each action performed by the routine itself is intended for the default database, unless the programmer is very careful to specify database name and table name for each operation.
For instance, if I have a for_each stored procedure that will execute one query for each table, I would like to use it from any database. To do so, I need to store the routine in a free-for-all database, and invoke it from the database where I want it to work, taking great care at qualifying my intended command with db name and table names.
Alternatively, I can store the routine in each database I am using, and this is a sort of inconvenience when I need to update
the routine, for I need to update it everywhere.
I had a chat about this limitation with some MySQL developers in Frankfurt, during the open source database conference early this month, but nobody sees this situation improving anytime soon.
This is more food for thought. General purpose routines are not to be attached to a single database, so this problem is quite real for almost every routine in my collection as of today.

ciao
Giuseppe