query for function/procedure metadata

[Laurenz Albe]

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

[Giuseppe Maxia]

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."
[url]http://dev.mysql.com/doc/refman/5.0/en/routine-restrictions.html[/url]

ciao
gmax

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

[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."
> [url]http://dev.mysql.com/doc/refman/5.0/en/routine-restrictions.html[/url]

Thank you for this helpful reply!

Laurenz Albe

[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."
> [url]http://dev.mysql.com/doc/refman/5.0/en/routine-restrictions.html[/url]

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

Yours,
Laurenz Albe

[madyak]

Laurenzo,

I am not sure if this is peculiar to the development environment I use (ColdFusion), but within coldfusion, the results of a MySQL Show Create Table statement are returned to the variable "CREATE TABLE" - unfortunately ColdFusion does not like variables with space names, so I rename the column then I can query on it.

I've not had a need to manipulate the results of a SHOW CREATE statement directly within MySQL, so I don't know how that's handled.

It seems though that you could easily query the database from within .NET and issue a show create and then parse the results as I'm doing in ColdFusion.

Stumbled upon your post looking for something more elegant than my current solution. Please let me know if you did come up with a better way to handle this within MySQL.

Thanks!

Roberto