Professional Web Applications Themes

query for function/procedure metadata - MySQL

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 ps the result, but I am facing problems with ...

  1. #1

    Default 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 ps 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 Guest

  2. #2

    Default Re: query for function/procedure metadata

    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 ps 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 p 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]
    Giuseppe Maxia Guest

  3. #3

    Default Re: query for function/procedure metadata

    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 p 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 Guest

  4. #4

    Default Re: query for function/procedure metadata

    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 p 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
    Laurenz Albe Guest

  5. #5

    Default Re: query for function/procedure metadata

    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 p 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
    madyak Guest

Similar Threads

  1. Is stored procedure safe to query a large result set?
    By howachen@gmail.com in forum MySQL
    Replies: 0
    Last Post: September 1st, 02:15 PM
  2. Problems with Oracle Stored Function (Procedure)
    By Rob@PSA in forum Coldfusion Database Access
    Replies: 11
    Last Post: March 18th, 01:20 AM
  3. Using query string to pass a value to a stored procedure parameter
    By Machelle Chandler in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: October 20th, 11:22 PM
  4. Cannot execute dynamic query in stored procedure
    By Net in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 4th, 09:13 PM
  5. Challenge? dynamic query in store procedure
    By Alan in forum Oracle Server
    Replies: 0
    Last Post: December 8th, 07:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139