Paul L Paul L - 5 months ago 21
Perl Question

SQL Server timestamp fields coming out unprintable with Perl DBI

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...)

enter image description 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?

mob mob
Answer

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
Comments