Update SQL's varbinary(max) field from VB6

[Andrzejek]

I have this code in VB6 application - reading PDF file into a byte array:

Dim aryByte() As Byte
Open App.Path & "\ABC_XYZ.pdf" For Binary Access Read As #1
ReDim aryByte(0 To LOF(1) - 1)
Get #1, , aryByte
Close #1

So, at this time I have my PDF in aryByte variable.

Now I need to update an existing record in SQL table:

UPDATE MyTable
Set Body = ??? aryByte ???
Where ID = 1234

Body field is declared as varbinary(max)

Any trick in updating varbinary field?

 

[Andrzejek]

I did try the Parameterized query, but I could not resolve the adVarBinary line

Dim cmd As New ADODB.Command
...
strSQL = "UPDATE SampleReport SET " & vbNewLine _
    & " Body                 = ? " & vbNewLine _
    & " WHERE SampleReportID = ?"

With cmd
    .ActiveConnection = CnS
    .CommandText = strSQL
    .Parameters.Append .CreateParameter("@SomeName", adVarBinary, adParamInput, 1234, aryByte)
    .Parameters.Append .CreateParameter("@ID", adInteger, adParamInput, 18, 12345)
    .Execute
End With

But then, I've got this gem:

strSQL = "UPDATE SampleReport SET " & vbNewLine _
    & " Body = (Select BulkColumn From OpenRowset (Bulk '" & YourPath\FileName.pdf & "', Single_Blob) src) " & vbNewLine _
    & " WHERE SampleReportID = 1234 " 
    
CnS.Execute strSQL

I don't really know how it works, it just does.

 

[SaltyTheFrog]

At our site OpenRowset is not allowed to be used. The DBA group considers it a security risk.

 

[Andrzejek]

I get it. I can do it on my local DB, but I need special permission to do it to other DB.
I need this permission to just load the data once (I hope) so no security risk.

 

[Andrzejek]

Update - after spending a lot of time with DBA, we could not make this process happen
So, back to the Parameterized query. And it works! (I was so close...)

Dim aryByte() As Byte
Dim cmd As ADODB.Command
Dim L As Long

If FSO.FileExists(PDF_PATH & !FileName.Value) Then
    Open PDF_PATH & !FileName.Value For Binary Access Read As #1
    ReDim aryByte(0 To LOF(1) - 1)
    L = LOF(1)
    Get #1, , aryByte
    Close #1
   
    strSQL = "UPDATE SampleReport SET " & vbNewLine _
        & " Body = ? " & vbNewLine _
        & " WHERE SampleReportID = ?"
   
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = CnS
        .CommandText = strSQL
        .Parameters.Append .CreateParameter("@Body", adVarBinary, adParamInput, L, aryByte)
        .Parameters.Append .CreateParameter("@SRID", adInteger, adParamInput, 18, rst!ID.Value)
        .Execute
    End With
    Set cmd = Nothing
End If