[SOLVED] How can you get the value of an ENUM within a MySQL Procedure

How can you get the value of an ENUM within a MySQL Procedure

It seems to me that enums are broken within MySQL, at least on version 5.0.51a-24+lenny5 (Debian). I have a simple lookup table which contains an enum field:

CREATE TABLE `FrameworkServices` (
  ...
  `DNSType` enum('IP','CNAME') NOT NULL,
  ...
)

Ad-hoc, I can run the following without issue:

SELECT DNSType, CNameOrIP INTO @hackEnum, @ipAddress
FROM FrameworkServices WHERE FrameworkTypeId = 1 LIMIT 1;
SELECT @hackEnum, @ipAddress;

However, if I put the EXACT same code into a Procedure, @hackEnum returns empty (not null, at least through TOAD).

I have tried selecting DNSType+0, CONCAT(DNSType,'') and everything else that seems to be valid on enums and the result is the same: EVERY article I read works ad-hoc, but returns empty within the Procedure!

I've even tried using local variables and defining hackEnum as the same ENUM type.

Solution

I had the exact same problem - I was retrieving an ENUM value and then checking it via IF...ELSE...END IF. It wasn't working and driving me nuts as to why not. The solution was to select into a char(1) and then treat the ENUM column value as a string:

DECLARE use_free CHAR(1);

SELECT use_free INTO use_free FROM ...;

IF use_free = '1' THEN
  <do something>
ELSE
  <do something else>
END IF;

I first used:

DECLARE use_free ENUM('0','1');

and while technically correct, failed. Hope this helps you.