bug in SQLBindCol

I appologise in advance for not looking into this any further but I'm very time
poor at the moment (if you are on the dbi-users list you'll know why).

create table test(a numeric(10.6))
insert into test (1000)
SQLBindCol(SQL_C_CHAR, buffer length 11)
11 is OK as its 4.6 = 11 chrs and bound columns are not null terminated
SQLFetch

you get back "1000.00000" and a length of 11 instead of
"1000.000000" and a length of 11.

This is most noticeable under DBD::OBDC as the 11 is use to create a perl
scalar and so you get whatever trailing garbage is in the 11th chr. i.e. you
often end up with 1000.00000\u0000.

I got around the problem by changing my code to numeric(11.6) and ensuring no
value above 9999 can be entered.

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.

Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: bug in SQLBindCol

At 17:52 +0100 3/31/06, Martin J. Evans wrote:
>I appologise in advance for not looking into this any further but
>I'm very time
>poor at the moment (if you are on the dbi-users list you'll know why).
>
>create table test(a numeric(10.6))
>insert into test (1000)
>SQLBindCol(SQL_C_CHAR, buffer length 11)
> 11 is OK as its 4.6 = 11 chrs and bound columns are not null terminated
>SQLFetch
>
>you get back "1000.00000" and a length of 11 instead of
>"1000.000000" and a length of 11.
>
>This is most noticeable under DBD::OBDC as the 11 is use to create a perl
>scalar and so you get whatever trailing garbage is in the 11th chr. i.e. you
>often end up with 1000.00000\u0000.
>
>I got around the problem by changing my code to numeric(11.6) and ensuring no
>value above 9999 can be entered.
>
>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.ht ml

See the "Server Changes" section of that page.

--
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/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

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/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: bug in SQLBindCol

Paul DuBois wrote:

> At 17:52 +0100 3/31/06, Martin J. Evans wrote:
>
>> I appologise in advance for not looking into this any further but I'm
>> very time
>> poor at the moment (if you are on the dbi-users list you'll know why).
>>
>> create table test(a numeric(10.6))
>> insert into test (1000)
>> SQLBindCol(SQL_C_CHAR, buffer length 11)
>> 11 is OK as its 4.6 = 11 chrs and bound columns are not null
>> terminated
>> SQLFetch
>>
>> you get back "1000.00000" and a length of 11 instead of
>> "1000.000000" and a length of 11.
>>
>> This is most noticeable under DBD::OBDC as the 11 is use to create a
>> perl
>> scalar and so you get whatever trailing garbage is in the 11th chr.
>> i.e. you
>> often end up with 1000.00000\u0000.
>>
>> I got around the problem by changing my code to numeric(11.6) and
>> ensuring no
>> value above 9999 can be entered.
>>
>> 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.ht ml
>
> 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!

Martin



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

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.ht ml
>>>
>>> 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/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org