vba 我可以使用参数化查询从 SQL Server VarBinary 列返回字节数组吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2307830/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can I return a byte array from a SQL Server VarBinary column using a parameterized query?
提问by Kuyenda
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.
我编写了一个小的 VBA 程序来测试使用 ADO 将文件作为二进制数据上传和下载到 SQL Server 中的 VarBinary 列中。上传过程似乎有效，但我无法使下载过程正常工作。
I believe the output parameter for VarBinary is setup incorrectly, but I cannot find any documentation on how to do it correctly.
我相信 VarBinary 的输出参数设置不正确，但我找不到任何有关如何正确执行此操作的文档。
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)
我收到运行时错误 3708“参数对象定义不正确。提供的信息不一致或不完整。” 在线.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.
更新：SELECT ? = myblob FROM bin_table WHERE ID = ?;似乎正在返回一个二进制字符串，而不是一个二进制数组。我相信这就是问题所在，但我仍然不知道如何解决它。
Update: I fixed the compile error "Type mismatch: array or user-defined type expected" by adding adding .Valueto the end of the line WriteFile "C:\some_new_file.pdf", .Parameters("@myblob").
更新：我通过.Value在行的末尾添加添加来修复编译错误“类型不匹配：数组或用户定义类型预期” 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
采纳答案by Kuyenda
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.
我找不到任何使用参数从 SQL Server 中的 VarBinary 列返回字节数组的方法。但是，我确实发现从记录集中执行此操作是可行的。附加的代码可以完成这项工作。
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
