in reply to SQLServer varbinary headache

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

Replies are listed 'Best First'.
Re: Re: SQLServer varbinary headache
by fizbin (Chaplain) on Apr 20, 2004 at 19:08 UTC
    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.
[reply]
[d/l]
[select]
Re: Re: SQLServer varbinary headache
by bfdi533 (Friar) on Apr 23, 2004 at 19:53 UTC

    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

[reply]
[d/l]

      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]
[reply]
[d/l]
[select]

        Right you are!

        I, on the other hand, have no clue how map works no matter how many times I read through its description. So, I tend to use the code from the previous programming incarnations I know (C, etc.). I will definitely use your subroutine in my next rev. as it is definitely more readable and maintainable.

        Ed

[reply]

In Section Seekers of Perl Wisdom