INF: Interpreting BINARY Data w/ Visual Basic Library for SQL

  - Microsoft SQL Server Programmer's Toolkit, version 4.2

SUMMARY

Special handling is necessary when using the Visual Basic Library for SQL in order to form an ASCII representation of BINARY or VARBINARY data retrieved from SQL Server. The SQLData$ function returns character string representations of most datatypes; however, BINARY, VARBINARY, and IMAGE data are returned as strings of binary data. That is, the string returned from SQLData$ represents the actual data stored in SQL Server.

Character string representation of BINARY data might be necessary in applications making use of TIMESTAMP data (represented by VARBINARY(8) within SQL Server) when evaluating SQL Server global variables such as @@DBTS, or when implementing a robust ad-hoc query processor.

MORE INFORMATION

The code fragment below describes how to incorporate processing within a SQL results handler in order to form an ASCII representation of BINARY data. The PadLeft$() function is necessary to ensure that each hexadecimal number is translated into a two-character ASCII representation.

' code fragment for processing binary data

    coltype% = SQLColType%(sqlconn%, X%)
    colvalue$ = SqlData(sqlconn%, X%)
   ' coltype 45 = BINARY DATA
    If coltype% = 45 Then
        buffer$ = "0x"
        For yy = 1 To Len(colvalue$)
    buffer$ = buffer$ + PadLeft$(Hex$(Asc(Mid$(colvalue$, yy, 1))), 2, "0")
         Next yy
         colvalue$ = buffer$
    End If

' zero padding function

    ST$ = Trim(ST)
    PD$ = ""

    If (Len(ST) < NUM) Then
        For X = 1 To NUM - Len(ST)
            PD$ = PD$ + padchar$
        Next X
    End If

    PadLeft$ = PD$ + ST$