Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
HomeAnnouncementsWhite Papers
Discussion GroupsFirst AidDatabasesJavaBeansGUIJava 3DVirtual MachineCORBASecurityToolsGeneral
Java DirectoryOpen Source ProjectsSample Book ChaptersUser GroupsWeb Resources
Related Topics
Databases.NETMore Topics ...

Java Forum / Databases / October 2007

Tip: Looking for answers? Try searching our database.

Finding valid ENUM values

Thread view: 
Rupert Woodman - 30 Sep 2007 02:12 GMT
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
Roedy Green - 01 Oct 2007 00:19 GMT
>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


Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...

Oracle MagazineNetwork ComputingComputer WorldBio-IT WorldeWeekInformation WeekInfosecurity
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.