PDO FetchAll() Number Rounding Issue

Question

I am using PDO to execute Stored Procedures on an MSSQL database. Everything works absolutely brilliantly apart from one small hitch with PDOStatement::fetchAll(). When returning floats from the Stored Procedure, instead of returning them with the correct value and decimal place, it is returning the value with extra decimal places like so:

Database | Returned by PDO
-----------------------------------
614.9    | 614.89999999999998
1.18     | 1.1799999999999999

It seems as if there are rounding issues... I don't understand why it is trying to round the number anyway, surely it should just take the value from the database as it appears?

I have tried:

Running the Stored Procedure on the DB and this works absolutely fine, therefore it is definitely something to do with PDO.
Running a normal query (instead of a Stored Procedure), and this still produces the same result.

Obviously I can format the number myself, but this is irrelevant as I have to be sure that what I get from the database is exactly what is in the database!

Are you sure it's not the other way around? What you see in the "Database" gets rounded? — nice ass, Jun 06 '13 at 11:12
Try inserting `4.21` and `4.1999999` and check what PDO / "Database" returns in both cases — nice ass, Jun 06 '13 at 11:17
@OneTrickPony I inserted 2.1 and the result is 2.1000000000000001. Very confused!! :-( — Ben Carey, Jun 06 '13 at 11:34
@OneTrickPony I think the problem may lie with how Microsoft handle Floating points in a database... — Ben Carey, Jun 06 '13 at 11:37
Did you try zaq's suggestion? `bindColumn` with `PDO::PARAM_STR`, then fetch with `PDO::FETCH_BOUND` ? — nice ass, Jun 06 '13 at 11:45
@OneTrickPony Afraid not, still comes out with a load of decimal places — Ben Carey, Jun 06 '13 at 11:49

pinepain · Answer 1 · 2013-06-06T11:21:37.027

1

Maybe the problem is how float numbers are stored internally in PHP? Try to read about it in official docs.

UPD: You may also specify result column as string (PDO::PARAM_STR) with PDOStatement::bindColumn, but I didn't check it.

edited Jun 06 '13 at 11:21

answered Jun 06 '13 at 11:14

pinepain

12,453
3
60
65

Thats interesting, could be this, I will look into it. Thank you – Ben Carey Jun 06 '13 at 11:17
I have tried both sugestions, sadly neither have solved this. I believe it is to do with how Microsoft handle Floating Points – Ben Carey Jun 06 '13 at 11:49
can you fix your routine to cast returned values to string (if it an option for you)? – pinepain Jun 06 '13 at 11:58
That was something I looked into and it works but as this SP returns over 500 fields, it is not very practical... I am going to format my numbers using PHP instead... – Ben Carey Jun 06 '13 at 12:00
can you make a benchmarks of both solutions and publish it here? i would appreciate it very much. – pinepain Jun 06 '13 at 12:05
Any one find a clear answer to this problem other than turning the numbers into a string ? – hounded Oct 07 '14 at 21:07

PDO FetchAll() Number Rounding Issue

1 Answers1