Falsely added precision with PDO, PHP & sybase

Question

I have a query that resembles what follows below. I get different results from PDO/PHP than I do in the interactive SQL prompt on the server. When running this query through interactive SQL I see transaction amounts correctly (ie. 45.34).

When I pull results through PDO I see incorrect results with falsely added precision (ie. 45.3400000000001). Is there something in PDO that can be set to return this data as is stored in the database?

Select tl.ID
       , tl.Transaction_Amount
from transactionLog tl

Update: The data returned from the query is being formatted into json. I have verified the pre-json data to also contain false data. One answer from antoox suggests rounding at the db level in the query. This, unfortunately, does not help as the query returns correct data when run at a SQL prompt, and rounded or not is displayed incorrectly once it has come through PDO. PHP bug #53632 is also mentioned by antoox. Unfortunately this bug is not a factor in our environment as we are on a far more recent version.

Update: The database field is numeric size 10 with scale 2 as reported by Sybase Central.

I added an update with this information. numeric size 10 with scale 2. Thanks. — wilbbe01, May 16 '13 at 14:12

score 0 · Accepted Answer · answered May 15 '13 at 14:49

0

Severals versions of PHP have a floating point bug.

Either you can do a query like:

SELECT tl.ID, round(tl.Transaction_Amount, 2) FROM transactionLog tl

,Or update your php version.

answered May 15 '13 at 14:49

antoox

1,309
11
9

Thanks for the advice antoox. Unfortunately we are well beyond the version listed in this bug and the test script listed at that url passes. Also, being as the data appears to be coming correctly from Sybase, rounding at the database level does not help as it appears the problem happens at the PDO level. – wilbbe01 May 15 '13 at 15:06
Since your answer is technically a possible solution to others having this problem I will give it to you even though it wasn't technically the solution to my specific issue. Thanks antoox. – wilbbe01 May 15 '13 at 21:40

score 0 · Answer 2 · edited May 23 '17 at 10:25

0

We ended up "solving" this by using a similar idea to How can I format numbers as money in JavaScript? answer, which while not ideal, performs well enough and is acceptable in this use case.

edited May 23 '17 at 10:25

Community

1
1

answered May 15 '13 at 21:39

wilbbe01

1,931
1
24
38

Falsely added precision with PDO, PHP & sybase

2 Answers2