query for function/procedure metadata

I have been asked to compose a query to return the metadata of a
function or procedure (MySQL 5.0): argument names and types, IN or OUT,
return value if appropriate.

The ultimate goal is to provide these metadata to an application that
accesses the database via .NET; the developer claims that there is no
way to retrieve these data via the .NET driver and asked me for a
workaround.

Selecting INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION returns only
the function body.

I had the idea to write a function that issues a SHOW CREATE FUNCTION
and parses the result, but I am facing problems with this approach:

- There is nothing like a 'SHOW ... INTO <variable>'

- I considered declaring a cursor for the statement and retrieve the
SHOW result this way.
However, I found no way to declare a cursor for a dynamic statement.

Can anybody think of a way to achieve my goal?

Thank you,
Laurenz Albe

Laurenz Albe wrote:
> I have been asked to compose a query to return the metadata of a
> function or procedure (MySQL 5.0): argument names and types, IN or OUT,
> return value if appropriate.
>
> The ultimate goal is to provide these metadata to an application that
> accesses the database via .NET; the developer claims that there is no
> way to retrieve these data via the .NET driver and asked me for a
> workaround.
>
> Selecting INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION returns only
> the function body.
>
> I had the idea to write a function that issues a SHOW CREATE FUNCTION
> and parses the result, but I am facing problems with this approach:
>
> - There is nothing like a 'SHOW ... INTO <variable>'
>
> - I considered declaring a cursor for the statement and retrieve the
> SHOW result this way.
> However, I found no way to declare a cursor for a dynamic statement.
>
> Can anybody think of a way to achieve my goal?
>
> Thank you,
> Laurenz Albe

I was trying to do exactly the same thing, and I found out that the
only viable solution, as of today, is to parse the output of
"SHOW CREATE FUNCTION ..." in the host language.

Quoting from the manual:
"INFORMATION_SCHEMA does not yet have a PARAMETERS table, so applications that need to acquire routine parameter
information at runtime must use workarounds such as parsing the output of SHOW CREATE statements."
http://dev.mysql.com/doc/refman/5.0/...trictions.html

ciao
gmax

--
_ _ _ _
(_|| | |(_|><
_|
http://gmax.oltrelinux.com

Giuseppe Maxia <gmax_@_cpan_._org> wrote:
> I was trying to do exactly the same thing, and I found out that the
> only viable solution, as of today, is to parse the output of
> "SHOW CREATE FUNCTION ..." in the host language.
>
> Quoting from the manual:
> "INFORMATION_SCHEMA does not yet have a PARAMETERS table, so applications
> that need to acquire routine parameter
> information at runtime must use workarounds such as parsing the output of
> SHOW CREATE statements."
> http://dev.mysql.com/doc/refman/5.0/...trictions.html

Thank you for this helpful reply!

Laurenz Albe

Giuseppe Maxia <gmax_@_cpan_._org> wrote:
> I was trying to do exactly the same thing, and I found out that the
> only viable solution, as of today, is to parse the output of
> "SHOW CREATE FUNCTION ..." in the host language.
>
> Quoting from the manual:
> "INFORMATION_SCHEMA does not yet have a PARAMETERS table, so applications
> that need to acquire routine parameter
> information at runtime must use workarounds such as parsing the output of
> SHOW CREATE statements."
> http://dev.mysql.com/doc/refman/5.0/...trictions.html

Just for the record, I find that parsing mysql.proc.param_list is easier,
though still a workaround.

Yours,
Laurenz Albe