sql-server - 我可以使用参数化查询从 SQL Server VarBinary 列返回字节数组吗？

Question

我编写了一个小的 VBA 过程来测试使用 ADO 将文件作为二进制数据上传和下载到 SQL Server 中的 VarBinary 列和从中下载。上传过程似乎有效，但我无法使下载过程有效。

我认为 VarBinary 的输出参数设置不正确，但我找不到任何关于如何正确设置的文档。

我收到运行时错误 3708“参数对象定义不当。提供的信息不一致或不完整。”在行 .Parameters.Append .CreateParameter("@myblob", adVarBinary, adParamOutput)

更新: SELECT ? = myblob FROM bin_table WHERE ID = ?; 似乎返回二进制字符串，而不是二进制数组。我相信这就是问题所在，但我仍然不知道如何解决。

更新:我通过在 行末尾添加 .Value 修复了编译错误“类型不匹配:预期的数组或用户定义类型” WriteFile "C:\some_new_file.pdf", .Parameters("@myblob").

非常感谢任何帮助。谢谢!

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

Answer 1

我找不到任何方法来使用参数从 SQL Server 中的 VarBinary 列返回字节数组。然而，我确实发现从记录集中这样做是可行的。附加的代码可以完成这项工作。

我仍在寻找一种使用参数返回字节数组的方法，并将坚持几天接受答案，以防有人有解决方案。

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