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.
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
.开发者_JAVA技巧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
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
精彩评论