I'm trying to grab the enum values from a column in a table using the DBI column_info method. According to the DBI docs I should be able to do this:


However, when I do this:


I get this error:


By that message it looks like I can't specify a column, correct? If I use % as the column name it will work but I get every column in the table. I came up with this as a workaround for now:


I have two questions:

1) Is there an easier way to get the enum values for a column?

and more importantly:

2) (DB design related) Should I be going about this in a different way? With unit of measure being somewhat dynamic I'm thinking I might should have another table for unit of measure options.

Thanks,
Charlie

Hmm... my guess would be that the driver you're using doesn't support column matching. It may be possible with another driver but not for the DB/driver pair you've got. Your result is probably the only one that's available to you right now.

I think the design only becomes a problem if you want users to be able to add more units of measure but, for just a fixed subset, enum is just fine. I'm using to using ints than translating later on myself but, that's just a preference issue (though it could help with internationalization)

Thanks Aki.

I tried upgrading DBD::mysql and it still throws the same error. I agree that it must be a driver limitation. I found this bit in the DBI pod about needing to escape any _'s and %'s in the table name but that didn't help either.

As for the DB design, I will implement a new table for UOM's. I want the user to be able to add/edit/remove them and I also want to enforce referential itegrety so a new table would be the easiest way to do that. I usually use TINYINT for my on/off yes/no type of values too. I thought enum would be an easy way out here but it's not good for dynamic values.

Thanks,
Charlie

You could always do:

my $sth = $dbh->prepare("describe tablename");
$sth->execute;
while (my $col = $sth->fetchrow_hashref) {
...
}

to get a description of the table (mysql only though).

Cheers,

Alex
--
Gossamer Threads Inc.