PHP, PDO SQL server, the leading 0 number is cut from the return data

Question

I'm working on PHP + Slim Framework. I have to migrate MySQL to SQL Server. There is something wrong with the return result from SELECT statement. Here is my example data,

╔════╦═══════╦════════════╦════════════╦═══════════════════════╗
║ id ║item_id║    lat     ║     lng    ║       timestamp       ║
╠════╬═══════╬════════════╬════════════╬═══════════════════════╣
║  1 ║  1    ║51.42743450 ║-0.72776696 ║2017-07-05 09:54:49.000║
║  2 ║  1    ║51.59665507 ║-0.72777098 ║2017-07-05 11:54:49.000║
║  3 ║  2    ║51.59664690 ║-0.67272032 ║2016-08-10 10:11:49.000║
║  4 ║  2    ║51.59664874 ║-0.67270288 ║2016-08-10 11:05:49.000║
║  5 ║  2    ║51.59665167 ║-0.67271587 ║2016-08-10 10:08:49.000║
╚════╩═══════╩════════════╩════════════╩═══════════════════════╝

And here is the code,

public function test(Request  $request, Response  $response, $args)
{
  $query = 'SELECT item_id, lat, lng FROM mySchema.record WHERE item_id = 1';
  $sth = $this->db->prepare($query);
  $sth->execute();
  $rows = $sth->fetchAll();
  $data = array('data' => $rows);
  return $response->withJson($data);
}

Previously, in MySQL, the data is returned correctly like this (example),

"data" : [
    {
        "item_id" : "1",
        "lat" : "51.42743450",
        "lng" : "-0.72776696"
    }
]

But after working with SQL Server, it returns data to me like this (example),

 "data" : [
    {
        "item_id" : "1",
        "lat" : "51.42743450",
        "lng" : "-.72776696"
    }
]

It can be seen that the leading 0 number is cut from the lng. I really have no idea how to fix it. Could someone help? Thanks a lot.

**EDIT : Lng datatype is DECIMAL(11,8) in SQL Server which is same as MySQL

Update

There is a workaround - to directly add 0 to the data through sprintf(). But the problem will not be fixed at the root cause if I go this way. However, I will go this way while there is no proper solution yet.

What is the output of $sth->fetchAll()? Just to be sure nothing happends on its way through your function calls. — Araw, Jul 18 '17 at 15:04
It works normally if I execute it directly. But PDO + sqlsrv might cause something to the data. — Hikaru Shindo, Jul 18 '17 at 15:21
does anything break? i mean, the leading zero is not a significant digit, and `-.72` is really the same value as `-0.72`. Does the consumer of the json fail trying to read without the leading zero? — SlimsGhost, Sep 14 '17 at 17:23

score 1 · Answer 1 · answered Sep 26 '17 at 21:03

1

This is a known problem in a native client driver, unfotunately I have no evidence it has been ever corrected. Try to return the value as NVARCHAR

answered Sep 26 '17 at 21:03

David Komanek

124
9

score 1 · Answer 2 · answered Oct 07 '22 at 15:29

1

Maybe I'm a little late but this has been solved with a parametrization. Here's the docs.

Short answer: set PDO::SQLSRV_ATTR_FORMAT_DECIMALS parameter to true.

answered Oct 07 '22 at 15:29

Mark

645
2
9
27

PHP, PDO SQL server, the leading 0 number is cut from the return data

Update

2 Answers2