SQL Server timestamp fields coming out unprintable with Perl DBI

Question

I am connecting to an SQL Server 2014 database, via the Perl DBI module with DBD::ODBC. The table I'm looking at has the following structure:

CREATE TABLE [dbo].[JonesFrank$EMSM Printouts](
    [timestamp] [timestamp] NOT NULL,
    [Document Type] [int] NOT NULL,
    [Document No_] [nvarchar](20) NOT NULL,
    [Line No_] [int] NOT NULL,
    [Entry No_] [int] NOT NULL,
    [Printout] [image] NULL,
    [Date] [datetime] NOT NULL,
 CONSTRAINT [JonesFrank$EMSM Printouts$0] PRIMARY KEY CLUSTERED 
(
    [Document Type] ASC,
    [Document No_] ASC,
    [Line No_] ASC,
    [Entry No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

As you can see, it has a timestamp column, cleverly named 'timestamp'. When I select values from this table within the SQL Server application, they come out as long hex numbers, like so:

+====================+
| timestamp          |
+====================+
| 0x000000000AA531FB |
| 0x000000000AB0F485 |
| 0x000000000AB0F483 |
| 0x000000000A941C0C |
| 0x000000000AA531F5 |
| 0x000000000AA53448 |
+====================+

However, when I run a Perl program, using DBI and DBD::ODBC, the results are strings containing non-printable characters:

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Getopt::Long;

GetOptions (
   'dbname=s' => \my $dbname,
   'driver=s' => \my $driver,
   'server=s' => \my $server,
   'user=s'   => \my $user,
   'pwd=s'    => \my $pwd,
) or die "Invalid command line options\n";

die "--dbname <database name> required!\n" unless defined $dbname;

$driver //= 'SQL Server';
$server //= 'SQL';
$user   //= 'xxx';
$pwd    //= 'xxxxxxxx';

my $table = 'TheTable';    

my $dbh = DBI->connect(
    "dbi:ODBC:Driver={$driver};Server=$server;UID=$user;PWD=$pwd",
    { RaiseError => 1}
) or die "Cannot connect: $DBI::errstr";

$dbh->do("use $dbname");

my $sql = "SELECT [timestamp] FROM [$table] WHERE Printout IS NOT NULL";
my $sth = $dbh->prepare($sql);
$sth->execute();
while (my $row = $sth->fetch()) {
   print $row->[0], "\n";
}

(I have to post the output as an image, as I don't know how to reproduce the strings that are output just by typing on my keyboard here...)

I am using perl 5.22.2 for cygwin, DBI.pm 1.636, and DBD/ODBC.pm 1.52.

Does anyone know why the timestamp values are coming out like this, and how to get the 'real' values, that I see in the SQL Server application?

@Stivan: I don't see any improvement in your edit: the original code is readable and nicely indented. But your proposal has blocked anyone from making any changes until three of your peers have reviewed your changes. Please be aware of the manifestations of your next edit — Borodin, Jun 03 '16 at 17:45
I'm guessing you think that a timestamp is something that represents a date and or time. If that's the case then [Timestamps aren't what you think they are](http://stackoverflow.com/a/8119407/119477) . — Conrad Frix, Jun 03 '16 at 17:46
It looks like that column is being retrieved as a packed 32-bit binary number. Please show the output if you use `print unpack 'N', $row->[0], "\n";`. If that produces strange results then go for `print unpack 'V', $row->[0], "\n";`. But what you see will be something akin to a sequence number or an access count; its value isn't usually useful unless you want to compare them, and that's best left to the database engine — Borodin, Jun 03 '16 at 17:53
@ConradFrix I actually did not think that, based on the fact that SQL Server was showing me long hex numbers. I don't know what they are, but no, I didn't think they were dates and times. — Paul L, Jun 03 '16 at 18:26

score 3 · Accepted Answer · answered Jun 03 '16 at 17:56

I can see the original data in the console output. chr(0x0A) is a newline, chr(0xA5) is a weird character, chr(0x31) is the digit 1, chr(0xFB) is another weird character, etc.

So the 64-bit integer displayed in the SQL Server application is encoded in a string. And when you hear the words "integer", "string", and "encoding", then you should think "pack" and "unpack".

After some trial-and-error, I find that the Perl code to recreate SQL Server's hex strings from the output to Perl looks like:

sub rawTimestampToHex {
    my $i = shift;
    sprintf "0x%016X", unpack("Q>",$i);
}

where the "Q>" template means to interpret the first 8-bytes of input as a packed unsigned quad (64-bit integer) value in big-endian byte-order (most significant bits first)

Example:

print rawTimestampToHex("\x00\x00\x00\x00\x0A\xA5\x31\xFB")
0x000000000AA531FB

I am woefully ignorant as to the meaning and usage of `pack` and `unpack`. Off to http://perldoc.perl.org I go. Thank you very much, @mob! — Paul L, Jun 03 '16 at 18:29

SQL Server timestamp fields coming out unprintable with Perl DBI

1 Answers1