Selecting Timestamp Column Causes Encoding Issues

[laclemen]

Below is my sample php script I am using to reproduce the issue. I created a tempTable with an int and timestamp column. Then I loop through and insert 5 values and attempt to select * from the table. When selecting however, the result is not correct. Executing it from the command line I get the following.

Array
(
[0] => Array
(
[EventId] => 1
[0] => 1
[RowTS] => 
[1] => 
)

[1] => Array
    (
        [EventId] => 2
        [0] => 2
        [RowTS] =>
        [1] =>
    )

[2] => Array
    (
        [EventId] => 3
        [0] => 3
        [RowTS] =>     �
        [1] =>     �
    )

[3] => Array
    (
        [EventId] => 4
        [0] => 4
        [RowTS] =>      �
        [1] =>      �
    )

[4] => Array
    (
        [EventId] => 5
        [0] => 5
        [RowTS] =>      �
        [1] =>      �
    )

).

In my error logs the array is immediately broken upon the first RowTS in the array.

[25-Sep-2017 10:57:35 CST6CDT] Array
(
[0] => Array
(
[EventId] => 1
[0] => 1
[RowTS] =>

`<?php
try
{
$uid = '';
$pwd = '';
$conn = new PDO("sqlsrv:server = localhost ; Database = testDb ; ConnectRetryCount = 6; ConnectRetryInterval = 10;", $uid, $pwd);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch( PDOException $e )
{
echo "Could not connect.\n";
print_r( $e->getMessage() );
}

try
{
$stmt = $conn->query("IF OBJECT_ID('testDb.dbo.tempTable', 'U') IS NOT NULL DROP TABLE testDb.dbo.tempTable");
$stmt = $conn->query("create table tempTable (EventId int, RowTS TIMESTAMP)");

$sql = "INSERT INTO tempTable (EventId) VALUES (?)";
for( $t = 1; $t <= 5; $t++ )
{
    $stmt = $conn->prepare($sql);
    $stmt->execute([$t]);
}

$select = $conn->query( "select * from tempTable" );
$result = $select->fetchAll();
echo print_r($result, true);
error_log(print_r($result, true));

}
catch( PDOException $e )
{
print_r($e->getTraceAsString());
}`

[david-puglielli]

Hi @laclemen the SQL Server data type timestamp is a unique binary number. Are you perhaps intending to insert actual times/dates into the table? If so you must use the datetime data type. See the MSDN timestamp page:

The timestamp data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime data type.

[laclemen]

@david-puglielli no I am not trying to insert timestamps in our prod code. That was just to provide a sample script. We have a timestamp/rowversion column on all tables for tracking purposes. I am unable to do a select * from any table due to this encoding issue. We just upgraded from release 4.1.7 to 4.1.8 in order to take advantage of the Connection Resiliency settings and upon upgrading I am no longer able to select a column of type timestamp. I also tried using 4.3.0 with no luck. This seems to have been supported in previous releases.

[david-puglielli]

Okay, I see. Could you provide us with some expected output for the script above? It would be useful to know what you were seeing before the behaviour changed so we can verify if this is a regression. Thanks!

[david-puglielli]

@laclemen If you are trying to recover the hex representation of a timestamp value, then you can use the PHP function bin2hex() for any field containing binary data.

For example, try this in the script above:

    $select = $conn->query( "select * from tempTable" );
    while ($result = $select->fetch())
    {
        echo "EventID :".$result[0]."\n";
        echo "RowTS   :".bin2hex($result[1])."\n";
    }

Output:

EventID :1
RowTS   :000000000000878e
EventID :2
RowTS   :000000000000878f
EventID :3
RowTS   :0000000000008790
EventID :4
RowTS   :0000000000008791
EventID :5
RowTS   :0000000000008792

[yitam]

Hi @laclemen what you saw was actually a result of a fix to Issue #270 (by PR #297) earlier this year for 4.1.7-preview release

Since then, for data from binary fields we will now display binary values instead of hex. You can use bin2hex() as suggested by @david-puglielli above.

[yitam]

Hi @laclemen I just want to know if we have answered your questions. Thanks!

[Hadis-Knj]

@laclemen closing this issue due to inactivity, feel free to reopen the issue 😃