我可以使用参数化查询从SQL Server VarBinary列返回字节数组吗？ [英] Can I return a byte array from a SQL Server VarBinary column using a parameterized query?

问题描述

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

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.

我相信VarBinary的输出参数设置不正确，但是我找不到关于它的任何文档

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

我收到运行时错误3708参数对象定义不正确。提供的信息不一致或不完整。在行 .Parameters.Append .CreateParameter（ @ myblob，adVarBinary，adParamOutput）

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 =？; 似乎正在返回二进制字符串，而不是二进制数组。我相信这就是问题所在，但我仍然不知道如何解决。

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 ：我通过在行 WriteFile C的末尾添加 .Value ，修复了编译错误类型不匹配：预期为数组或用户定义的类型。 \some_new_file.pdf，.Parameters（ @ myblob）。

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

推荐答案

我找不到任何方法来返回SQL Server的VarBinary列中使用参数的字节数组。但是，我确实从记录集中发现这样做是可行的。

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

这篇关于我可以使用参数化查询从SQL Server VarBinary列返回字节数组吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！