MySQL, MyODBC and ACCESS 2003

External Since: Nov 30, 2004 Posts: 4

Ok I have an interesting problem. I have written a database in Access
to connect to our web shop and update prices etc from a supplier feed.
It works almost perfectly!

The prices are doubles, I am using ADO in access to connect via myodbc
(with options don't optimize column width, Allow big results and
change bigint to int). Everything works ok except if the price is
8351.41 in the code it puts this exact price into the price field of
the table, everything is fine, but when I update the price goes to
8351.41000000001 (not sure on the exact number of 0 but it is a lot!).

I have tried converting the value to a string then to a double before
writing it to the database, to make sure there are no hidden decimals
but it's the same.

I have tried myodbc 3.51.09 and 3.51.11 and it's the same on both!
In fact if I link the table in access and manually type in 8351.41
then move to a different row it changes it to 8351.41000000001!!

Am I going nuts or is this some kind of annoying bug?

External Since: Jan 10, 2005 Posts: 7

How did you define that column type? Did you use float? You can alter
the table to change the precision.

mysql> create temporary table test (price float);
Query OK, 0 rows affected (0.04 sec)

mysql> desc test;
+-------+-------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------+------+-----+---------+-------+
| price | float | YES | | NULL | |
+-------+-------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> alter table test change price price float(10,2);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| price | float(10,2) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into test (price) values (8.3566677777);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+-------+
| price |
+-------+
| 8.36 |
+-------+
1 row in set (0.00 sec)