[ic] SQL Price Rounding Problem

[Jon Jensen]

On Sat, 18 Feb 2006, Cameron B. Prince wrote:

> I recently moved a customer over and shortly after was informed of a problem
> where all the product prices had the cents stripped off. A price in the
> products.txt of 17.99 was showing up in MySQL as 17.00.
>
> This is IC v4.8.5, MySQL v4.1.16, perl v5.6.2 and the latest DBI and
> DBD::mysql.
>
> I reimported the table several times and verified this was happening each
> time. I did a describe on the table and found that the price column had not
> been modified from the default foundation catalog:
>
> | price         | decimal(12,2) |      | MUL | 0.00    |       |
>
> To further test, I setup a standard catalog using IC v5.4.0 and copied the
> products.txt file over and imported it. I had the same problem.
>
> So I begin troubleshooting by dropping the product rows from 29,000+ to
> 1,000. I still had the same problem. I edited the
> dbconf/mysql/products.mysql file and removed:
>
> Database  products  NUMERIC      price
>
> Once I imported again, the prices were correct. Just as a sanity check, I
> put the line back in the dbconf file and imported again. The problem
> reoccurred. There appears to be a bug in the import function.
>
> I recall seeing code in the admin pages or source in the past that used the
> NUMERIC directive to control sorting I believe. Are there any other
> ramifications I should be aware of by removing it from the dbconf file?

I believe it also affects the quoting in Interchange's database 
abstraction layer, when creating queries for DBI.

Are you really re-importing the table often enough that import failures 
matter? I would just put the NUMERIC setting back after an import and then 
not import anymore, using MySQL's native import routines. But if you can 
figure out what's causing the problem, it would be nice to fix.

Jon


--
Jon Jensen
End Point Corporation
http://www.endpoint.com/