#!/usr/bin/perl -w
use strict;

my $x = 
'0x0105000000000005150000009E15AE084A7CBC7A881C6A37C63F0000';

open    GG, ">c:/PerlMonks_346728.out" or die;
binmode GG or die;
print   GG second_to_first($x);
close   GG or die;

sub first_to_second {
  return join('', '0x', unpack 'H*', $_[0]);
}
sub second_to_first {
  local $_ = shift;
  s{^0x}{}i or die;
  return pack 'H*', $_;
}
[download]

Well, I don't have experience with this, but turning your output into what SQL Server says isn't too hard:

sub bin2hextext {
  my ($blob) = shift;
  "0x" . join ('', map {sprintf("%02X",unpack("C",$_))} 
                       split(//, $blob));
}

my $DSN = 'driver={SQL Server};Server=new-borg;database=master;uid=;pw
+d=;';
my $dbh = DBI->connect("dbi:ODBC:$DSN") or die "$DBI::errstr\n";

my $sql = qq{ select * from sysusers };

my $sth = $dbh->prepare ($sql);
$sth->execute();

my @varbincolumns = qw(2);
while (@row = $sth->fetchrow_array()) {
    @row[@varbincolumns] = map {bin2hextext($_)} @row[@varbincolumns];
    print $#row,':',join(':',@row),"\n";
}

$sth->finish();
[download]

A more polished version would figure out the contents of the @varbincolumns array using $sth->{TYPE}.

Actually, use what Util said for the hex2bintext subroutine:

sub bin2hextext {
  join '', '0x', unpack("H*", $_[0] );
}
[download]

I thought this looked like a job for pack/unpack, but I couldn't remember all the pack codes.

That is more elegant than the code I wrote to try this out. Thanks for the swift and accurate advice.

I am not sure that I understand the differences in the different pack/unpack codes. What I tried before I got your answer, which also works, was:

while (@row = $sth->fetchrow_array()) {
        @s = unpack('C*',$row[4]);
        $sid = "0x";
        foreach $s (@s) {
            $sid .= sprintf "%2.2x", $s;
        }
        $row[4] = $sid;
        #print $server,':',$db,':',join(':',@row),"\n";

        $sth_ins->execute($server,$db,@row);
    }
[download]

What would the difference be between my code and yours?

Ed

TMTOWTDI

Well, the main difference is that I sequestered the convert-blob-to-long-hex-string dance off in its own subroutine (bin2hex) and then applied it to those columns that needed converting (just one column this time, but there might be other columns in similar code in the future, so you could reuse the code).

As far as the way we both did the blob-to-hex-string conversion, there are some things that I think your version does better and some that mine does better. Basically, I'm not too comfortable with pack/unpack, so I tend to use it less than I really should. I am, however, very comfortable with map and so will use that in preference to a for loop when I feel it makes sense. (I still think that the corrected bin2hex I posted, based on what Util said in this same thread, is really the best option)

--
@/=map{[/./g]}qw/.h_nJ Xapou cets krht ele_ r_ra/;
map{y/X_/\n /;print}map{pop@$_}@/for@/
[download]

select ..., convert(varchar, sid), ... from sysusers
[download]

Yes, that is not a bad idea but since it is a binary value, you get the converted binary value as a string. Still looks like garbage as it contains non-printable characters.

Thanks for the input though.

Ed

Hmmm - you're right. I thought it would convert to a hex string. Guess I'll have to look into this...

Michael