List:General Discussion« Previous MessageNext Message »
From:Steve Edberg Date:March 8 2009 10:31pm
Subject:Re: Possible to get better error handling for invalid enum
parameter to stored program?
View as plain text  
If you want to keep the enum column, you can set the strict SQL mode:

mysql> show variables like 'SQL_MODE';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)

mysql> create table test2 (test enum('foo','bar'));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test2 values('baz');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'test' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> set session sql_mode='traditional';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test2 values('bloop');
ERROR 1265 (01000): Data truncated for column 'test' at row 1
mysql> select * from test2;
+------+
| test |
+------+
|      |
+------+
1 row in set (0.00 sec)

I'm running v5.0.51a; as you can see, the first invalid value was 
truncated to '' with a warning, the second caused an error and did 
not insert. I don't know what version you're running, perhaps this 
does not apply to you. For more info:

	http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
	http://dev.mysql.com/doc/refman/5.0/en/enum.html

	- steve edberg


At 2:00 PM -0800 3/6/09, David Karr wrote:
>Thanks.  I thought that was the case, but I wanted to be sure.
>
>On Fri, Mar 6, 2009 at 12:07 PM, Perrin Harkins <perrin@stripped> wrote:
>
>>  I'm afraid enums are useless for anything except compressing your
>>  data, since they don't reject bad values.  If you want to limit a
>>  field to a set of values, you need to use a lookup table and a foreign
>>  key constraint.
>>
>>  - Perrin
>>
>>  On Fri, Mar 6, 2009 at 1:35 PM, David Karr <davidmichaelkarr@stripped>
>>  wrote:
>>  > If I define an enum parameter for a stored program, and the calling code
>>  > sends an invalid value, they get the less than useful "data truncated"
>>  > error.  Is it possible to define the stored program to produce better
>>  error
>>  > handling for that kind of error?
>>  >
>>  > This is probably a FAQ, but in general, it appears that error diagnostics
>>  in
>>  > stored programs are very primitive. Are there any plans in a roadmap to
>>  > improve this?
>>  >
>>


-- 
+--------------- my people are the people of the dessert, ---------------+
| Steve Edberg                                http://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center                            sbedberg@stripped |
| Bioinformatics programming/database/sysadmin             (530)754-9127 |
+---------------- said t e lawrence, picking up his fork ----------------+
Thread
Possible to get better error handling for invalid enum parameter to stored program?David Karr6 Mar
  • Re: Possible to get better error handling for invalid enum parameter to stored program?Perrin Harkins6 Mar
    • Re: Possible to get better error handling for invalid enum parameter to stored program?David Karr6 Mar
      • Re: Possible to get better error handling for invalid enumparameter to stored program?Steve Edberg8 Mar
Re: Possible to get better error handling for invalid enum parameter to stored program?David Karr8 Mar