Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

Re: SQL Server SID value output as garbage using DBI

by poj (Prior)
on Jul 27, 2013 at 17:02 UTC ( #1046673=note: print w/replies, xml ) Need Help??

in reply to SQL Server SID value output as garbage using DBI

What data type is the SID column ?. I suspect it is binary, it only looks like hex in your Management client. This small demo program shows two option, use convert in your SQL statement or unpack the fetched value.

use strict; use DBI; my $dbh = mssql(); # connect # temp test table setup my $t = '#bintest'; $dbh ->do("CREATE TABLE $t (bin1 varbinary(max))"); my $hex_in = '0x151552dabe9d1ce4e8dd6b635f23d4c0'; $dbh->do("INSERT INTO $t (bin1) VALUES ( CONVERT(varbinary(max), ?, 1) )",undef,$hex_in); # my ($bin_out) = $dbh->selectrow_array( "SELECT bin1 FROM $t"); # style 1 adds 0x my ($hex_out) = $dbh->selectrow_array( "SELECT CONVERT(varchar(max),bin1,1) FROM $t"); print "hex input = $hex_in\n"; print "bin out = $bin_out\n"; print 'hex output = 0x',unpack('H*',$bin_out),"\n"; print "hex output = $hex_out\n";

Replies are listed 'Best First'.
Re^2: SQL Server SID value output as garbage using DBI
by mje (Curate) on Jul 29, 2013 at 16:14 UTC

    Obviously in this case he cannot use convert as the procedure is a system defined procedure I doubt he can change.

    As the current maintainer of DBD::ODBC, which I presume the OP is using, I thought you could just do "$sth->bind_col(2, undef, {TYPE => SQL_CHAR})" after the execute but it appears you cannot. If the OP wants to confirm which DBD they were using I will look into allowing a binary column's type to be overriden.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1046673]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (5)
As of 2017-06-24 04:25 GMT
Find Nodes?
    Voting Booth?
    How many monitors do you use while coding?

    Results (556 votes). Check out past polls.