Msphpsql: PDO_SQLSRV returns everything as a string

[nemchik]

Driver version or file name

4.3.0+9905

SQL Server version

Irrelevant

Client operating system

Windows

PHP version

7.1.7 x64 (From WebPI)

Microsoft ODBC Driver version

phpinfo() shows ODBCVER | 0x0350

Table schema

Irrelevant

Problem description

PDO_SQLSRV returns everything as a string.
Reference the post here https://stackoverflow.com/a/13756696/1384186 by Robert Calhoun on Dec 7 '12 and the point number 2 here https://github.com/Microsoft/msphpsql/issues/60 and the first paragraph of this page https://docs.microsoft.com/en-us/sql/connect/php/how-to-retrieve-date-and-time-type-as-strings-using-the-sqlsrv-driver stating

It is an error to use the ReturnDatesAsStrings connection option with the PDO_SQLSRV driver.

The SQLSRV driver apparently returns data as the correct type. The PDO_SQLSRV driver should also return data as the correct type. As it is now data returned from the PDO_SQLSRV driver that should be datetime or int is returned as string and must then be converted to to the correct data type manually.

Expected behavior and actual behavior

Expected: PDO_SQLSRV returns data in the type matching what it is in the database.
Actual: PDO_SQLSRV returns everything as a string.

Repro code

I am unsure where this might be in your repo.

[yitam]

Yes @nemchik PDO_SQLSRV returns dates as strings. However, for numeric columns like integers or floats, you can return them as numeric values using the attribute PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE as shown below,

$conn = new PDO("sqlsrv:Server=$server;database=$dbName;", $uid, $pwd, $options); 
$conn->setAttribute(PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE, true);

The above works except for Decimals or Numerics, because we want to preserve accuracy (see #291 for your interest).

Hope this helps.

[nemchik]

@yitam PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE accomplishes everything that I personally use except the date data types. Even with PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE dates are still returned as strings when using PDO, however dates are returned as dates with using the non PDO SQLSRV driver (as pointed out on the StackOverflow link).

Also it seems like the default behavior of the SQLSRV driver is to return data in its respective type, while the PDO_SQLSRV driver requires a special flag to enable this behavior. Wouldn't it make more sense for both drivers to behave the same out of the box and provide a flag for the PDO_SQLSRV driver that fetches all data as strings? (basically reverse the behavior)

[yitam]

I understand what you mean, but the PDO_SQLSRV driver has to conform to PHP PDO

[yitam]

@nemchik , you might find this particular tip from a user useful

[nemchik]

I understand conforming to PHP PDO, and since PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE mostly meets my need I can live with that.

Do you have any plans to work on dates?

[yitam]

Not at this point as you can use existing built-in date time methods. I'll mark this as feature request, @nemchik

[yitam]

Hi @nemchik

If we are going to return datetime values as date time objects, we will not reuse the same flag PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE as this might be a breaking change to other people. It's likely we will introduce a new flag for this purpose.

[nemchik]

I would be fine with it being a separate flag.

[yitam]

Just so you know, @nemchik, for values from a column of datetime2 type, with the default precision of 7 digits, the values returned as strings (the existing behavior in pdo_sqlsrv) or as datetime objects may vary.

Consider the following:

date_default_timezone_set('America/Los_Angeles');
$now = '2018-09-07 12:12:56.9506139';
var_dump($now);

$date = date_create($now);
var_dump($date);
var_dump($date->format('Y-m-d H:i:s.u'));

The output is

string(27) "2018-09-07 12:12:56.9506139"
object(DateTime)#2 (3) {
  ["date"]=>
  string(26) "2018-09-07 12:12:56.950613"
  ["timezone_type"]=>
  int(3)
  ["timezone"]=>
  string(19) "America/Los_Angeles"
}
string(26) "2018-09-07 12:12:56.950613"

Note that returning the column data as a string actually preserves the accuracy and precision of 7 digits.

However, returning the same value as a DateTime object the last digit is dropped, because a DateTime object supports microseconds up to 6 digits.

This is consistent with the other driver sqlsrv however.

[nemchik]

While I appreciate the accuracy and understand its purpose, my practical use of having the PDO_SQLSRV driver return dates as the non PDO driver does is just to skip the step of converting from string to date in PHP for the various uses I have for date data being pulled from a database. If it comes out in a date format already that is more convenient and takes a step out of my process for using the data in PHP. I would imagine it would also take a step out of the process the driver has to complete by not converting the date to a string on the driver end.

It's a trivial amount of effort to convert strings to dates in PHP, but it's >0 so I figured I would ask. If there is no possibility for an option like this to be added to the PDO driver that's understandable, but if it's something you would consider it could be beneficial.

As a side note I think it just makes sense to output dates as dates, just like numeric types come out as numeric types when using the flag. But that's just my opinion 😄

[yitam]

Yes @nemchik we are considering this feature request, but we want to make sure you are aware of the slight loss of accuracy as described above. 😄

[yitam]

Closing this issue @nemchik. Feature already implemented in 5.4.0-preview