List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:December 21 2001 3:40pm
Subject:Re: Getting Possible Values of an Enum Field
View as plain text  
At 3:35 PM +0100 12/21/01, Henning Sprang wrote:
>Hy there,
>I would like to know if there is a possibility to get all allowed values
>of an enum field out of the database.
>
>I know there are methods to get the field type and those give me back
>something like "enum('value1', 'value2', 'value3')", and I can parse my
>possible values out of this with regexp's in the Programming language of
>my Choice, but I am interested to know if there isn't a more "beautiful"
>possibility where Mysql gives me back the result direct.

Nope.  You have to use the metadata.  Here's an example in Perl.
It actually gets more than the list of values, and it works for
SET columns, too.

# Take a database connection, a table name, and a column name.
# Return a reference to a hash with name, type, values, default,
# and nullable keys.

sub get_enumorset_info
{
my ($dbh, $tbl_name, $col_name) = @_;
my $info = {};

     $info->{name} = $col_name;
     # escape any SQL pattern characters in column name
     $col_name =~ s/([_%])/\\$1/g;
     my @row = $dbh->selectrow_array (
                 "SHOW COLUMNS FROM $tbl_name LIKE '$col_name'"
             );
     return undef unless @row;
     return undef unless $row[1] =~ /^(enum|set)\((.*)\)$/;
     $info->{type} = $1;
     # split value list on commas, trim quotes from end of each word
     my @val = split (",", $2);
     s/^'(.*)'$/$1/ foreach (@val);
     $info->{values} = [ @val ];
     # determine whether or not column can contain NULL values
     $info->{nullable} = ($row[2] eq "YES");
     # get default value (undef represents NULL)
     $info->{default} = $row[4];

     return $info;
}

>
>
>greets,
>henning

Thread
Getting Possible Values of an Enum FieldHenning Sprang21 Dec
  • RE: Getting Possible Values of an Enum FieldCarsten H. Pedersen21 Dec
    • RE: Getting Possible Values of an Enum FieldHenning Sprang21 Dec
      • RE: Getting Possible Values of an Enum FieldCarsten H. Pedersen21 Dec
    • RE: Getting Possible Values of an Enum FieldKeith C. Ivey21 Dec
    • Re: Getting Possible Values of an Enum FieldDibo Chen21 Dec
  • Re: Getting Possible Values of an Enum FieldPaul DuBois21 Dec
    • Re: Getting Possible Values of an Enum FieldRobert Alexander21 Dec