PDO.PgSQL returns numeric fields as C# strings, but is unable to query a numeric field using a C# string argument

[kripper]

Currently, the PDO.PgSQL driver returns numeric field values as C# strings (eg: '1283923.124124124'). This is probably so, because native C# floating DataTypes are not capable to store big PgSQL numerics. Integer fields are returned as C# integers.

But when we query a numeric field (eg: ... WHERE my_numeric_field = :arg), the statement will fail if :arg is a string, ie. it will throw an exception saying operator does not exist: integer = text.

This is an inconsistency and will cause problems, because most of the time we will fetch values and use them as parameters in subsequent queries.

I believe the solution is to allow the PDO driver to accept string parameters for querying integer and numeric fields. Zend's PDO driver is flexible and allows this.

Note that $pdo->setAttribute(\PDO::ATTR_STRINGIFY_FETCHES, false) must be used (because Peachpie defaults to true while Zend defaults to false). Otherwise all values will be converted to strings, which currently will generate even more datatype missmatches when querying.

[kripper]

Hi @jakubmisek, do you know if it's possible to mimic Zend's PDO behavaiour, ie. to allow the PDO driver to accept string parameters for querying integer and numeric fields?

[kripper]

Hi @jakubmisek, any chance to take a look at this problem?

IMO this is a design problem that we should address the sooner the better. How have you dealt with this problem with the other DB backends?

Key here is "most of the time we will fetch values and use them as parameters in subsequent queries".

[jakubmisek]

@kripper thank you for reporting the issue. I'd need a test case to try that;

[kripper]

Hi, the problem was that PeachPie's PDO doesn't support providing a string with an integer ("123") as a nummeric argument (i.e. an argument that is expected to be a number, like in SELECT 1 = ?), while Zend supports it. But on the other hand, Zend's PDO doesn't support providing a float (123.456) nor a string containing a float ("123.456") as a nummeric argument. This is really frustrating, since it forces to rewrite SQL sentences to include CASTs. Since Zend is broken for floats, I'm not sure if it is worth fixing this problem only for integers...unless we accept to fix both cases in PeachPie even when it probably won't be fixed on Zend. Closing for now.