0

I have developed a system that gets product data from Sage 50 Accounts via an ODBC connector. The sync works great however I'm struggling hugely with product prices being rounded up/down and I can't figure out why. I have researched this extensively however it's not a common occurrence from what I can tell.

Code to get data from Sage:

$sql = "SELECT STOCK_CODE, DESCRIPTION, SALES_PRICE, QTY_IN_STOCK FROM stock";  
$r = odbc_exec($conn, $sql);  
$x = 0;  
$ignores=array('',);  
while ($row = odbc_fetch_row($r)) {  
for ($i = 1; $i <= odbc_num_fields($r); $i++)  
$results[$x][odbc_field_name($r, $i)] = odbc_result($r, $i);  
$x++;  
}

I have one product in Sage with a price of 0.0886 however when it's inserted into my MySQL data it is 0.09. I have checked what I'm getting out of Sage and it is 0.09 so it's not the insert that's messing with the figure.

From the functions I have found on the ODBC page within the php manual I have discovered the following on the column in question:

odbc_field_type = DOUBLE  
odbc_field_scale = 4  
odbc_field_len = 15

So I can see that the precision on this field within Sage is 4 decimal places, which is no surprise as you can store 0.1234 as product price but I am at a loss as to why I'm getting 0.09 instead of 0.0886.

The product database within Sage holds around 5500 records and more than 50% contain prices in the 4 decimal point format, manually entering the prices is far from ideal and would also make the sync less worthwhile.

I hope I've explained this issue in enough detail for someone to help.

kcrisman
  • 4,374
  • 20
  • 41
LinusIT
  • 1
  • 4

1 Answers1

0

Whilst I don't have an answer to the issue above I have now solved the problem.

I pulled the same data from Sage into Excel using the same ODBC connector. In doing this I got the correct prices, I.E no rounding to 2 decimal places. I checked the PHP code over once more but couldn't find anything that could be causing the rounding issue.

Whilst my solution is rather convoluted instead of fixing the above code, it works. I now export to excel, upload to a temporary table in MySQL and then run an update query to update the prices already stored in the table.

LinusIT
  • 1
  • 4