read datareader and convert binary to string

Maxxim · July 9th, 2007, 10:09 AM

Hello my friends!

I'm having some problems with binary fields!

I had starter with this script (by Englere): HERE

This application is for asp.net 2.0 and while read from a datareader insert fields into another database.

I want to use it in asp.net 1.1 to create an huge sql file with all insert lines!

I have a schema datatable that tell what type of data all columns have.

If my sqltype is varchar, nvarchar, int, text, etc... my application do:

Code:

    Private Shared Function setParamterTypeAndValue(ByVal table As String, _ 
      ByVal dr As SqlDataReader, _
      ByVal col As Integer, _
      ByVal sqlProviderType As String, _
      ByVal colName As String) As String
        Dim result As String = ""
        If (sqlProviderType.ToString = "Int") Or _
        (sqlProviderType.ToString = "SmallInt") Then
            If (dr(col) Is DBNull.Value) Then
                result += "NULL, "
            Else
                result += CType(dr(col), String) & ", "
            End If
        (...)
        ElseIf (sqlProviderType.ToString = "TinyInt") Then
            If (dr(col) Is DBNull.Value) Then
                result += "NULL, "
            Else
                result += CType(dr(col), Byte).ToString
            End If
        ElseIf (sqlProviderType.ToString = "VarChar") Then
            If (dr(col) Is DBNull.Value) Then
                result += "NULL, "
            Else
                result += "'" & CType(dr(col), String) & "', "
            End If

My big problem is when my sqlType is "binary" !!!!
I have a binary filed on my database with passwords:
(binary / lenght = 20)

I have to do something like:

Code:

ElseIf (sqlProviderType.ToString = "Binary") Then
            If (dr(col) Is DBNull.Value) Then
                result += "NULL, "
            Else
                result += CType(dr(col), String) & ", "
            End If

But I couldn't use "result += CType(dr(col), String)" when dr(col) is binary!!
I just need to generate a string like :
0xD62F739DA2796769AF9E727E7905F91A369F44A

I spent much time with this without success!
Can anyone tell wath can i do??
Thanks!
Max

planoie · July 9th, 2007, 12:29 PM

In my experience, I have never found a way to create a T-SQL command string with binary data in it. However, to satisfy my own curiosity I used a sql data comparison tool on some tables with image type fields and looked at the scripts it generated...

Based on what I'm seeing, you can put the string of hex right in the T-SQL string as-is, like this:

UPDATE table SET field = 0xD62F739DA2796769AF9E727E7905F91A369F44A

So try putting the binary string in the sql command without any quotes around it.

-Peter

Maxxim · July 9th, 2007, 01:18 PM

Thanks planoie, but this is my problem!

If I use:

Code:

result += CType(dr(col), Byte()).ToString & ", "
or
result += (dr(col)).ToString & ", "

When i open my txt file i see:
(...)aaaaa', System.Byte[], 'aaaaaa(..)

With the value System.Byte[] I don't do anything!
My question Is, how can i retrieve:
(...)aaaaa', 0xD62F739DA2796769AF9E727E7905F91A369F44A, 'aaaaaa(..)

I hope you understand my doubt!

Thanks

Maxxim · July 9th, 2007, 07:14 PM

I found a way!

My hosting company don't allow me to acess master db! master.dbo.fn_varbintohexstr solve my problems
I made a script with all the update I need.

If anyone has the same problem!
(I spent much time working on this)

Code:

declare @pstrout nvarchar(4000)
    ,@i int
    ,@firstnibble int
    ,@secondnibble int
    ,@tempint int
    ,@hexstring char(16)
    ,@cbytesin int
    ,@startoffset int
    ,@fsetprefix bit
    ,@pbinin varbinary(8000)
    ,@counter int
    ,@curPass varchar(8000)
    ,@curName varchar(15)


set @startoffset = 1
set @fsetprefix = 1
set @cbytesin = 0
set @counter = 0




CREATE TABLE #TempUsers
(
    ID             int IDENTITY PRIMARY KEY,
    querystr        varchar(600)
)
    while @counter < (select (max(UserID)) from accounts_Users)
    begin
      set @counter = @counter + 1
      set @pbinin = (select password from accounts_users where userid = @counter)
      Set @curName = (select UserName from accounts_users where userid = @counter)
      set @curPass = NULL
      set @pstrout = NULL

    if (@pbinin IS NOT NULL)
    begin    
        select @i = 0
                ,@cbytesin = case when (@cbytesin > 0) then @cbytesin else DATALENGTH(@pbinin) end
                ,@pstrout =  case when (@fsetprefix = 1) then N'0x' else N'' end
                ,@hexstring = '0123456789abcdef'

        if ( ((@cbytesin * 2) + 2 > 4000) or ((@cbytesin * 2) + 2 < 1) )
            --select NULL
            Set @curPass = NULL

        if ( ( @startoffset > @cbytesin ) or ( @startoffset < 1 ) )
            --select NULL
            Set @curPass = NULL

        --
        -- do for each byte
        --
        while (@i < @cbytesin)
        begin
            --
            -- Each byte has two nibbles
            -- which we convert to character
            --
            select @tempint = cast(substring(@pbinin, @i + @startoffset, 1) as int)
            select @firstnibble = @tempint / 16
            select @secondnibble = @tempint % 16

            --
            -- we need to do an explicit cast with substring 
            -- for proper string conversion. 
            --
            select @pstrout = @pstrout +
                cast(substring(@hexstring, (@firstnibble+1), 1) as nvarchar) +
                cast(substring(@hexstring, (@secondnibble+1), 1) as nvarchar)

            select @i = @i + 1
        END
    END

    -- All done
    --select @pstrout
    Set @curPass = @pstrout

    IF (@curPass IS NOT NULL AND @curName IS NOT NULL)
      BEGIN
       INSERT INTO #TempUsers (querystr) VALUES
       ('UPDATE FORUMS_ACCOUNTS SET PASSWORD = ' + @curPass + ' WHERE UserID = ' + CONVERT(VARCHAR(4), @counter) + ' AND USERNAME =' + @curName )
        END
    END

SELECT querystr FROM #TempUsers

DROP TABLE #TempUsers