Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

Re: SQL Server SID value output as garbage using DBI

by poj (Priest)
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";
poj


Comment on Re: SQL Server SID value output as garbage using DBI
Download Code
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?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1046673]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (11)
As of 2014-10-20 10:12 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (75 votes), past polls