Results 1 to 10 of 10
  1. #1
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313

    Unanswered: in search of MySQL metadata

    Is there any other place to go looking for database metadata in MySQL 5.0 other than the INFORMATION_SCHEMA views?

    In particular, there seems to be no PARAMETERS or REFERENTIAL_CONSTRAINTS views as this page states:
    http://dev.mysql.com/doc/refman/5.0/...ma-tables.html

    are there system tables somewhere (perhaps undocumented, i don't care so much about that) that I could look in for this information? Perhaps some use of the SHOW command?

    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use the SHOW command
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    thanks!

    however, I don't see a SHOW command that will list the parameters of a procedure or function in a result set (sorry I didn't make my wish clear in the OP ). it looks like the best I can get is just to show the DDL for the proc using SHOW CREATE PROCEDURE. Or am I missing something obvious?

    I was hoping for something analogous to SHOW COLUMNS for tables - something that would list out all params, with their datatypes, etc, in a result set.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by jezemine
    however, I don't see a SHOW command that will list the parameters of a procedure or function in a result set (sorry I didn't make my wish clear in the OP ).
    There must be some tables, because e.g. the JDBC driver does list this information. You could download the source code for the JDBC driver and have a look at the class DatabaseMetaData.java. Search for getProcedureColumns. You should see the used SQL statement somewhere in there.

  5. #5
    Join Date
    Dec 2005
    Location
    Tilburg, Netherlands
    Posts
    73
    Try select param_list from mysql.proc. Is that what you were looking for ?
    I also am in desperate need of the INFORMATION_SCHEMA.PARAMETERS table. IN the meanwhile I created my own (unfortunately not in the INFORMATION_SCHMEA 'database' as that is not permitted) and created a procedure that will fill that table for me for the procedure I feed it ... if interested, let me know.

    Gr,
    Yveau

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by Yveau01
    Try select param_list from mysql.proc. Is that what you were looking for ?
    I also am in desperate need of the INFORMATION_SCHEMA.PARAMETERS table. IN the meanwhile I created my own (unfortunately not in the INFORMATION_SCHMEA 'database' as that is not permitted) and created a procedure that will fill that table for me for the procedure I feed it ... if interested, let me know.
    That's better, but not great. It's all the parameters for a proc in a single column, hardly a normalized design. I was hoping for a way to get each param in its own row, with a column for name, datatype, length, default value, etc.

    I would love to see your code (I assume you are parsing out the param_list value into a result set?) Anyway, can you post it here? Thanks.

  7. #7
    Join Date
    Dec 2005
    Location
    Tilburg, Netherlands
    Posts
    73
    OK, here goes:

    First create the table, I used the test database:
    ----------
    use test;

    drop table if exists test.PARAMETERS;

    create table test.PARAMETERS
    ( SPECIFIC_CATALOG varchar(64)
    , SPECIFIC_NAME varchar(64)
    , ORDINAL_POSITION bigint
    , PARAMETER_MODE varchar(3)
    , PARAMETER_NAME varchar(64)
    , DATA_TYPE varchar(64)
    , CHARACTER_MAXIMUM_LENGTH bigint
    , NUMERIC_PRECISION bigint
    , NUMERIC_SCALE bigint);

    ----------

    Next, create a function to generate a sort of normalized output from the param_list, again I used the test database:
    ----------
    drop function if exists test.fnSplitString;

    delimiter //

    create function test.fnSplitString(pText varchar(200), pSeparator char(1), pNumber int)
    returns varchar(200)
    begin
    declare TextString varchar(200);
    declare Positie int;
    declare Lengte int;
    declare Current int;

    set TextString = concat(pText, pSeparator);
    set Positie = 1;
    set Lengte = locate(pSeparator, TextString) - 1;
    set Current = 1;

    -- select pText, TextString, Positie, Lengte;

    while (Lengte > 0) and (Current < pNumber) do
    -- select pText, TextString, Positie, Lengte, replace(substring(TextString,Positie,Lengte), pSeparator, '');
    -- select replace(substring(TextString,Positie,Lengte), pSeparator, '') as Parameter;

    set Positie = Positie + Lengte + char_length(pSeparator);
    set Lengte = locate(pSeparator, TextString, Positie) - Positie;
    set Current = Current + 1;
    end while;
    return replace(substring(TextString,Positie,Lengte), pSeparator, '');
    end;
    //

    delimiter ;

    ----------

    Finally, create the stored procedure that will fill up the test.PARAMETERS table, using the database and procedure names as input:
    ----------
    drop procedure if exists test.csProcParm;

    delimiter //

    create procedure test.csProcParm(IN pDb varchar(64), IN pProcedure varchar(64))
    begin
    declare lCur int;
    declare lParam varchar(64);

    delete
    from PARAMETERS
    where SPECIFIC_NAME = pProcedure
    and SPECIFIC_CATALOG = pDb;

    set lCur = 1;
    set lParam = (select fnSplitString(trim(fnSplitString(param_list, ', ', lCur)), ' ',2)
    from mysql.proc
    where type = 'PROCEDURE'
    and db = pDb
    and name = pProcedure);

    while (char_length(lParam) > 0) do
    insert
    into PARAMETERS
    ( SPECIFIC_CATALOG
    , SPECIFIC_NAME
    , ORDINAL_POSITION
    , PARAMETER_MODE
    , PARAMETER_NAME
    , DATA_TYPE
    , CHARACTER_MAXIMUM_LENGTH
    , NUMERIC_PRECISION
    , NUMERIC_SCALE)
    select pDb
    , pProcedure
    , lCur
    , fnSplitString(trim(fnSplitString(pr.param_list, ', ', lCur)), ' ',1)
    , lParam
    , co.DATA_TYPE
    , co.CHARACTER_MAXIMUM_LENGTH
    , co.NUMERIC_PRECISION
    , co.NUMERIC_SCALE
    from mysql.proc pr
    inner join INFORMATION_SCHEMA.COLUMNS co
    on co.table_schema = pr.db
    and co.table_name = substring(pr.name, 3, char_length(pr.name)-5)
    and co.column_name = substring(lParam, 2)
    where pr.type = 'PROCEDURE'
    and pr.db = pDb
    and pr.name = pProcedure;

    set lCur = lCur + 1;
    set lParam = (select fnSplitString(trim(fnSplitString(param_list, ', ', lCur)), ' ',2)
    from mysql.proc
    where type = 'PROCEDURE'
    and db = pDb
    and name = pProcedure);
    end while;

    end;
    //

    delimiter ;

    ----------

    I needed this for a set of 'elementary stored procedures' (insert, update, delete) so in the procedure I make a join with the INFORMATION_SCHEMA.COLUMNS table, because my parameters are the same as the column names of the table it is designed for. (the procedures have a 2 character prefix and a 3 character postfix, that explains the substring(x,2,char_length(x)-5. and the parameters have a 1 character prefix, that explains the substring(x,2))

    Hope this gives you a starting point for creating your own sollution. Let me know if you need some more help.

    Gr,
    Yveau

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    cool, thanks - I'll try it out.

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    fyi, one thing I just found is that this scheme doesn't work if there are any comments mixed in with the proc definition where the parameters are defined, because the comments become part of the param_list column. The problem is that the mysql devs were a bit lazy and they just dump everything between the parens into the param_list column. They don't actually parse it using a sql parser.

    Here's an example of what I'm talking about:

    Code:
    CREATE DEFINER=`root`@`localhost` PROCEDURE `film_in_stock`
    (
           in p_film_id int,          -- this parameter is for x...
           in p_store_id int,       -- this parameter is for y...
           out p_film_count int   -- this parameter is for z...
    )
        READS SQL DATA
    -- insert proc code here...

  10. #10
    Join Date
    Dec 2005
    Location
    Tilburg, Netherlands
    Posts
    73
    Pfew, now there's a challenge to get the comments out ...
    Hope I'll find some time to break my brain on this one ... Thanks for noticing !

    > SELECT * FROM users WHERE clue > 0;
    Empty set (0.00 sec)


Posting Permissions

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