I have a MySQL (5) database which has a table containing an ENUM column.
I'd like to (programatically) get a list of what the valid options are for
this ENUM. I've looked through the metadata but can't see anything. Can
someone point out what I'm missing?
Many thanks
>I have a MySQL (5) database which has a table containing an ENUM column.
>I'd like to (programatically) get a list of what the valid options are for
>this ENUM. I've looked through the metadata but can't see anything. Can
>someone point out what I'm missing?
You can write something like this:
/**
* stage codes, upper case
* U = untranslated
* ? = unsure of translation
* T = translated
* P = proofread (only proofreader/admin can set P or C status)
* C = complete (proofread in context of the actual program making
sure it fits in the box, looks good etc.)
*/
stage ENUM( 'U','?','T','P', 'C' ) NOT NULL,
The values you put and get will be ordinary strings.
There is an a abominably documented feature to use enums by index
rather than value. It follows advice in my "how to write
unmaintainable code" essay. Perhaps this could be worked up into a
method.
I found this snippet of PHP code. Perhaps you can decipher it and
produce a Java equivalent.
"I wrote this php function to return an array from enum values, using
the preg() solution posted by Willem-Jan van Dinter above, hope
someone finds it useful
Note: returns array(0 => 'None') if no results or no such table
function enum($object) {
list($table, $col) = explode(".", $object);
$row=@mysql_fetch_assoc(mysql_query("SHOW COLUMNS FROM ".$table." LIKE
'".$col."'"));
return ($row ?
explode("','",preg_replace("/(enum|set)\('(.+?)'\)/","\\2",$row['Type']))
: array(0=>'None'));
}
$optarray = enum("table.column");"

Signature
Roedy Green Canadian Mind Products
The Java Glossary
http://mindprod.com
Arne Vajhøj - 01 Oct 2007 04:13 GMT
> I found this snippet of PHP code. Perhaps you can decipher it and
> produce a Java equivalent.
> function enum($object) {
> list($table, $col) = explode(".", $object);
[quoted text clipped - 6 lines]
>
> $optarray = enum("table.column");"
A Java snippet:
ResultSet sc = stmt.executeQuery("SHOW COLUMNS FROM et LIKE
'ef'");
while(sc.next()) {
System.out.println(sc.getString("Type"));
Pattern p = Pattern.compile("'(\\w+)'");
Matcher m = p.matcher(sc.getString("Type"));
while (m.find()) {
System.out.println(m.group(1));
}
}
sc.close();
Arne
Rupert Woodman - 16 Oct 2007 22:55 GMT
Really sorry for the delay in responding chaps - been kind of busy with
work, and away.
Thanks for the tips. It looks to me as if it's something that needs to be
well encapsulated - it's certainly not portable code, tho having said that,
MySQL ENUMs aren't portable either!
rgds
Rupert
>> I found this snippet of PHP code. Perhaps you can decipher it and
>> produce a Java equivalent.
[quoted text clipped - 25 lines]
>
> Arne