Code to read and then write a file into a SQL Server BLOB column producing files with different bytes

Question

Using:

• MS-SQL Server 2014
• MS-Access 2013 with linked ODBC tables to the SQL Server database
• "SQL Server" ODBC driver (10.00.10586.00, Microsoft Corporation, SQLSRV32.DLL)
• DAO

We have an Access database with linked ODBC tables to a SQL Server 2014 database with VBA code behind a form in the Access application to upload a file to a SQL Server blob (varbinary[max]) column, and to later download the file from the same blob column.

However we found that upon retrieving a file that was uploaded earlier from the blob column, the saved file has some extra bytes added to the end of the file.

A screen capture of a comparison of the 2 files in Beyond Compare is below:

I would appreciate if someone could check and point out the error in the code. Code follows:

Function ReadBLOB(SourceFileName As String, TableName As String, FieldName As String, _
                  IDFieldName As String, IDFieldValue As Variant)
    Dim NumBlocks As Integer, SourceFile As Integer, i As Integer
    Dim FileLength As Long
    Dim LeftOver As Long
    Dim FileData() As Byte
    Dim RetVal As Variant
    Dim BlockSize As Long

    Dim s As String

    On Error GoTo Err_ReadBLOB

    BlockSize = 32767

    ' Open the source file.
    SourceFile = FreeFile
    Open SourceFileName For Binary Access Read As SourceFile

    ' Get the length of the file.
    FileLength = LOF(SourceFile)
    If FileLength = 0 Then
        ReadBLOB = 0
        Exit Function
    End If

    ' Calculate the number of blocks to read and leftover bytes.
    NumBlocks = FileLength \ BlockSize
    LeftOver = FileLength Mod BlockSize

    Dim T As dao.Recordset

    If TypeName(IDFieldValue) = "String" Then
        IDFieldValue = "'" & IDFieldValue & "'"
    End If

    s = "SELECT [" & FieldName & "] FROM [" & TableName & "] WHERE [" & IDFieldName & "] = " & IDFieldValue

    Set T = CurrentDb.OpenRecordset(s, dbOpenDynaset, dbSeeChanges)

    T.Edit

    ' Read the 1st block of data (upto Leftover in size), writing it to the table.
    'FileData = String$(LeftOver, 32)
    ReDim FileData(LeftOver)
    Get SourceFile, , FileData
    T(FieldName).AppendChunk (FileData)

    ' Read the remaining blocks of data, writing them to the table.
    'FileData = String$(BlockSize, 32)
    ReDim FileData(BlockSize)
    For i = 1 To NumBlocks
        Get SourceFile, , FileData
        T(FieldName).AppendChunk (FileData)

    Next i

    ' Update the record and terminate function.
    T.Update
    Close SourceFile
    ReadBLOB = FileLength
    Exit Function

Err_ReadBLOB:
    ReadBLOB = -Err

    MsgBox Err.Description

    Exit Function
End Function

Function WriteBLOB2(TableName As String, FieldName As String, IDFieldName As String, _
                    IDFieldValue As Variant, DestinationFileName As String) As Long

    Dim NumBlocks As Integer, DestFile As Integer, i As Integer
    Dim FileLength As Long, LeftOver As Long
    Dim FileData() As Byte
    Dim RetVal As Variant
    Dim BlockSize As Long
    Dim s As String
    Dim f As String

    On Error GoTo Err_WriteBLOB

    BlockSize = 32767

    Dim T As dao.Recordset

    If TypeName(IDFieldValue) = "String" Then
        IDFieldValue = "'" & IDFieldValue & "'"
    End If

    s = "SELECT [" & FieldName & "] FROM [" & TableName & "] WHERE [" & IDFieldName & "] = " & IDFieldValue

    Set T = CurrentDb.OpenRecordset(s, dbOpenSnapshot, dbSeeChanges)

    If T.RecordCount = 0 Then
        WriteBLOB2 = 0
        Exit Function
    End If

    ' Get the size of the field.
    FileLength = T(FieldName).FieldSize()
    If FileLength = 0 Then
        WriteBLOB2 = 0
        Exit Function
    End If

    ' Calculate number of blocks to write and leftover bytes.
    NumBlocks = FileLength \ BlockSize
    LeftOver = FileLength Mod BlockSize

    ' Remove any existing destination file.
    DestFile = FreeFile
    Open DestinationFileName For Output As DestFile
    Close DestFile

    ' Open the destination file.
    Open DestinationFileName For Binary As DestFile

    ' Write the leftover data to the output file.
    FileData = T(FieldName).GetChunk(0, LeftOver)
    Put DestFile, , FileData

    ' Write the remaining blocks of data to the output file.
    For i = 1 To NumBlocks
        ' Reads a chunk and writes it to output file.
        FileData = T(FieldName).GetChunk((i - 1) * BlockSize + LeftOver, BlockSize)
        Put DestFile, , FileData

    Next i

    ' Terminates function
    Close DestFile
    WriteBLOB2 = FileLength
    Exit Function

Err_WriteBLOB:
    WriteBLOB2 = -Err

    MsgBox Err.Description

    Exit Function
End Function

Public Sub ClearSQLBlob2(TableName As String, FieldName As String, _
                         IDFieldName As String, IDFieldValue As Variant)

    If TypeName(IDFieldValue) = "String" Then
        IDFieldValue = "'" & IDFieldValue & "'"
    End If

    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE [" & TableName & "] SET [" & FieldName & "] = NULL WHERE [" & IDFieldName & "] = " & IDFieldValue
    DoCmd.SetWarnings True
End Sub

Answer 1

I think the problem is:

Unless you have an Option Base 1 declaration in your module, arrays are zero-based.

So if e.g. LeftOver = 2,

ReDim FileData(LeftOver)

will actually declare an array FileData(0 To 2), which contains 3 bytes. And so the following Get will read 3 bytes, but you expect it to read 2 bytes.

The same is true for the full-size arrays.

In the end you read NumBlocks + 1 bytes too much from the file, and the remainders will be 00 bytes.

Solution: use

ReDim FileData(1 To LeftOver)
ReDim FileData(1 To BlockSize)

Edit: Note that you will have to check for the case LeftOver = 0.