pdo + ms sql server gives different results with binded or hard coded parameters

Asked Apr 11 '23 at 07:48

Active Apr 11 '23 at 15:10

Viewed 66 times

Pdo results with bound values are different than hard coded values.

$q = $conn->prepare('SELECT 0.27 * 0.08 * SUM(n) as ok, 
                        :i * SUM(n) as notOK 
                    from mytable 
                    group by id'); 
$q->bindValue(':i', 0.27*0.08, PDO::PARAM_STR); // tax
$q->execute();
var_dump($q->fetchAll(PDO::FETCH_ASSOC));

Results:

["ok"]=> "313.095888" 
["notOK"]=> "289.9036" 

["ok"]=> "80.601048" 
["notOK"]=> "74.6306"
...

ok != notOK, but it should (ok is correct).

My question is why?

details, what you asked for:

column 'n' and 'id' is int
tested on php7.4 + pdo for mssql (both linux and win)
it works with PDO::PARAM_INT (thanks for the suggestion :), although the suggested bind type is PDO::PARAM_STR for decimal PDO::PARAM for type decimal? )

edited Apr 11 '23 at 15:10

asked Apr 11 '23 at 07:48

steve

3

PDO::PARAM_STR might be a problem? – siggemannen Apr 11 '23 at 08:34
1

Leaving out `* SUM(n)` from the query might shed some light on what's going on. – KIKO Software Apr 11 '23 at 08:36
Try `$q->bindValue(':i', 0.27*0.08, PDO::PARAM_STR_CHAR);` that worked for me – RiggsFolly Apr 11 '23 at 08:45
What driver do you use to connect to SQL Server and what are the actual data types of `id` and `n` columns? – Zhorov Apr 11 '23 at 10:23
I'm able to return the expected result with `PDO::PARAM_INT` (`$q->bindValue(':i', 0.27*0.08, PDO::PARAM_INT);`). I'm using PHP Driver for SQL Server. – Zhorov Apr 11 '23 at 10:40
siggemannen: bingo! :) although the suggested bind type is PDO::PARAM_STR for decimal https://stackoverflow.com/questions/2718628/pdoparam-for-type-decimal – steve Apr 11 '23 at 15:11

pdo + ms sql server gives different results with binded or hard coded parameters

0 Answers0