Re: bug in SQLBindCol

Paul DuBois wrote:

> 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
>
Cheers - I've give that a try - that is what I expect.
Is there any way to turn this on in the mysql client for
all clients? (from myodbc and dbd::mysql for instance).

Martin




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