CODEWITHSUNDEEP
phpsql-serverpdoslim
Hikaru Shindo
Hikaru Shindo

Reputation: 2913

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

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.

Upvotes: 6

Views: 512

Answers (2)

Mark
Mark

Reputation: 656

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.

Upvotes: 1

David Komanek
David Komanek

Reputation: 124

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

Upvotes: 1

Related Questions