PostgreSQL Bytea to String

Category:

Database

Type:

Modules

Difficulty:

Advanced

Author:

Christopher Rhodes

Version Compatibility: Visual Basic 6, Visual Basic 5, Visual Basic.NET

More information:

Written by Marketing Solutions, Inc., the functions below will create a Bytea escaped string for inputing binary information into a type bytea column in PostgreSQL. FileToByteaString will take a file name, read the content, and then output a bytea escaped string for proper insertion into the database. PQescapeByteaToString will take a Byte array and convert it to bytea escaped string for proper insertion into the database.

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:
None.

Code:

Public Function FileToByteaString(FileName As String) As Variant
    Dim bData() As Byte
    Dim filelength As Long
    Dim fnum As Integer
    
    fnum = FreeFile()
        
    filelength = FileLen(FileName)
    Open FileName For Binary As #fnum
    ReDim bData(filelength)
    
    For i = 1 To filelength
        Get #fnum, i, bData(i)
    Next i
    
    FileToByteaString = PQescapeByteaToString(bData)
    
End Function

Public Function PQescapeByteaToString(vp() As Byte) As Variant

Dim rp() As Byte
    Dim totallength As Long
    Dim rpindex As Long
    Dim i As Long
    Dim tempstring As String
    
    totallength = 0
    
    For i = 1 To UBound(vp)
        If ((vp(i) < &H20) Or (vp(i) > &H7E)) Then
            totallength = totallength + 5
        ElseIf (vp(i) = Asc("'")) Then
            totallength = totallength + 2
        ElseIf (vp(i) = Asc("\\")) Then
            totallength = totallength + 4
        Else
            totallength = totallength + 1
        End If
    Next
    
    rpindex = 1
    ReDim rp(1 To totallength) As Byte
    
        For i = 1 To UBound(vp)
        If ((vp(i) < &H20) Or (vp(i) > &H7E)) Then
            rp(rpindex) = Asc("\\")
            rp(rpindex + 1) = Asc("\\")
            
            tempstring = Right("000" & Oct$(vp(i)), 3)
            rp(rpindex + 2) = Asc(Left$(tempstring, 1))
            rp(rpindex + 3) = Asc(Mid$(tempstring, 2, 1))
            rp(rpindex + 4) = Asc(Right$(tempstring, 1))
            rpindex = rpindex + 5
        ElseIf (vp(i) = Asc("'")) Then
            rp(rpindex) = Asc("\\")
            rp(rpindex + 1) = Asc("'")
            rpindex = rpindex + 2
        ElseIf (vp(i) = Asc("\\")) Then
            rp(rpindex) = Asc("\\")
            rp(rpindex + 1) = Asc("\\")
            rp(rpindex + 2) = Asc("\\")
            rp(rpindex + 3) = Asc("\\")
            rpindex = rpindex + 4
        Else
            rp(rpindex) = vp(i)
            rpindex = rpindex + 1
        End If
    Next
    
    PQescapeByteaToString = StrConv(rp, vbUnicode)
    
End Function