Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine

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]
[Corion]: Oh - I released a new version of some module, thanks to a pull request. But I don't consider "update Makefile.PL" and "update author tests" as "writing code" ;-D
[marto]: sounds fun, the opposite of my weekend :P
[Corion]: marto: You wrote Perl? Or did you have to work (and wrote Perl)?
[marto]: the fun part, I had no fun this weekend, very stressful :)
[Corion]: But I feel an introductory talk gestating, working title "Reading CPAN" - how to read module documentation, how to judge a module, how to read the module tests/examples
[Corion]: marto: Ouch :-/ The kids are making trouble?
[Corion]: Meh - food becons, and colleagues are staring at me, talk to you later, sorry!

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (7)
As of 2017-08-21 09:30 GMT
Find Nodes?
    Voting Booth?
    Who is your favorite scientist and why?

    Results (319 votes). Check out past polls.