Re: bug in SQLBindCol

At 20:38 +0100 3/31/06, Martin J. Evans wrote:
>Paul DuBois wrote:
>
>>At 17:52 +0100 3/31/06, Martin J. Evans wrote:
>>>Also, this raised another issue with numeric(10.6) in mysql itself.
>>>If you insert 10000 into a 10.6 it goes in without error but when 
>>>you read it
>>>back it is 9999.999999.
>>
>>
>>10.6 allows 4 digits to the left of the decimal point. 10000 has 5 digits.
>>MySQL no longer stores 10000, it clips it.  This is mentioned at:
>>
>>http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html
>>
>>See the "Server Changes" section of that page.
>
>Thanks but I realise this. Other databases don't allow the insertion of
>invalid values into numerics - that was my point. I believe the reason
>they do this is "silently" ignoring invalid insertion into a column
>causes serious problems. Imagine, it was your salary going into
>your account!

Turn on strict SQL mode, for example, by setting sql_mode to TRADITIONAL.

mysql> drop table if exists t;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t (d numeric(10,6));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t set d = 10000;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'd' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

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

mysql> insert into t set d = 10000;
ERROR 1264 (22003): Out of range value for column 'd' at row 1

-- 
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]