Can I return a byte array from a SQL Server VarBinary column using a parameterized query?

I wrote a small VBA procedure to test uploading and downloading of files as binary data into and out of a VarBinary column in SQL Server using ADO. The upload process appears to work, but I cannot get the download process to work.

I believe the output parameter for VarBinary is setup incorrectly, but I cannot find any documentation on how to do it correctly.

  • SQL Server: Importing database from .mdf?
  • Currently running query inside a stored procedure
  • ReportViewer does not show any results while Sql Server shows results
  • One to many SQL query with diagonal results
  • How to modify data in .csv during BULK INSERT?
  • How to migrate to specific version using Migrator.NET
  • I get run-time error 3708 “Parameter object is improperly defined. Inconsistent or incomplete information was provided.” at line .Parameters.Append .CreateParameter("@myblob", adVarBinary, adParamOutput)

    Update: SELECT ? = myblob FROM bin_table WHERE ID = ?; appears to be returning a binary string, not a binary array. I believe this is where the problem lies, but I still don’t know how to fix it.

    Update: I fixed the compile error “Type mismatch: array or user-defined type expected” by adding adding .Value to the end of the line WriteFile "C:\some_new_file.pdf", .Parameters("@myblob").

    Any help is greatly appreciated. Thanks!

    Private Sub TestReadWriteBlob()
    
        Dim objConnection As New ADODB.Connection
        Dim objCommand As New ADODB.Command
        Dim objRecordset As New ADODB.Recordset
        Dim intNewID As Integer
    
        With objConnection
            .CursorLocation = adUseClient
            .ConnectionString = "PROVIDER=SQLOLEDB;Server=<server>;Database=<database>;UID=<uid>;PWD=<pwd>;trusted_connection=false;"
            .Open
        End With
    
        With objCommand
            .ActiveConnection = objConnection
            .CommandText = "INSERT INTO bin_table ( myblob ) VALUES ( ? ); SELECT ? = id FROM bin_table WHERE ID = @@IDENTITY;"
            .CommandType = adCmdText
            .Parameters.Append .CreateParameter("@myblob", adVarBinary, adParamInput, -1, ReadFile("C:\some_file.pdf"))
            .Parameters.Append .CreateParameter("@NewID", adInteger, adParamOutput)
            .Execute
            intNewID = .Parameters("@NewID")
        End With
    
        Debug.Print intNewID
    
        Set objCommand = Nothing
        With objCommand
            .ActiveConnection = objConnection
            .CommandText = "SELECT ? = myblob FROM bin_table WHERE ID = ?;"
            .CommandType = adCmdText
            .Parameters.Append .CreateParameter("@myblob", adVarBinary, adParamOutput)
            .Parameters.Append .CreateParameter("@NewID", adInteger, adParamInput, , intNewID)
            .Execute
            WriteFile "C:\some_new_file.pdf", .Parameters("@myblob").Value
        End With
    
    End Sub
    
    Public Function ReadFile(ByVal strPath As String) As Byte()
    
        Dim intFile As Integer
    
        intFile = FreeFile
        Open strPath For Binary Access Read As intFile
        ReDim ReadFile(LOF(intFile) - 1)
        Get intFile, , ReadFile
        Close intFile
    
    End Function
    
    Public Sub WriteFile(ByVal strPath As String, bytBlob() As Byte, Optional ByVal Overwrite As Boolean = True)
    
        Dim intFile As Integer
    
        intFile = FreeFile
        If Overwrite And Dir(strPath) <> "" Then
            Kill strPath
        End If
        Open strPath For Binary Access Write As intFile
        Put intFile, , bytBlob
        Close intFile
    
    End Sub
    

    One Solution collect form web for “Can I return a byte array from a SQL Server VarBinary column using a parameterized query?”

    I could not find any way to return the byte array from the VarBinary column in SQL Server using a parameter. I did, however, figure out that doing it from the recordset works. The attached code does the job.

    I am still looking for a way to use the parameter to return the byte array and will hold out on accepting an answer for a few days in case someone has a solution.

    Private Sub TestReadWriteBlob()
    
        Dim objConnection As New ADODB.Connection
        Dim objCommand As New ADODB.Command
        Dim intNewID As Integer
    
        With objConnection
            .CursorLocation = adUseClient
            .ConnectionString = "PROVIDER=SQLOLEDB;Server=<server>;Database=<database>;UID=<uid>;PWD=<pwd>;trusted_connection=false;"
            .Open
        End With
    
        With objCommand
            .ActiveConnection = objConnection
            .CommandText = "INSERT INTO bin_table ( myblob ) VALUES ( ? ); SELECT ? = id FROM bin_table WHERE ID = @@IDENTITY;"
            .CommandType = adCmdText
            .Parameters.Append .CreateParameter("@myblob", adVarBinary, adParamInput, -1, ReadFile("C:\Users\Thomas\Desktop\some_file.pdf"))
            .Parameters.Append .CreateParameter("@NewID", adInteger, adParamOutput)
            .Execute
            intNewID = .Parameters("@NewID")
        End With
    
        Set objCommand = Nothing
        With objCommand
            .ActiveConnection = objConnection
            .CommandText = "SELECT myblob FROM bin_table WHERE ID = ?;"
            .CommandType = adCmdText
            .Parameters.Append .CreateParameter("@NewID", adInteger, adParamInput, , intNewID)
            WriteFile "C:\Users\Thomas\Desktop\blob\some_file.pdf", .Execute.Fields("myblob").Value
        End With
    
    End Sub
    
    Public Function ReadFile(ByVal strPath As String) As Byte()
    
        Dim intFile As Integer
    
        intFile = FreeFile
        Open strPath For Binary Access Read As intFile
        ReDim ReadFile(LOF(intFile) - 1)
        Get intFile, , ReadFile
        Close intFile
    
    End Function
    
    Public Sub WriteFile(ByVal strPath As String, bytBlob() As Byte, Optional ByVal Overwrite As Boolean = True)
    
        Dim intFile As Integer
    
        intFile = FreeFile
        If Overwrite And Dir(strPath) <> "" Then
            Kill strPath
        End If
        Open strPath For Binary Access Write As intFile
        Put intFile, , bytBlob
        Close intFile
    
    End Sub
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.