We are migrating an old system to another newer and we need to recover all information stored on the database first. I usually use a vba script to download files from SQL Server stored in image columns.
In this particular case it didn't work. I can download and save the files but when I try to open them many errors appears due problems with encoding.
Sub download()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql, path, strData, db, user, pass, server As String
Dim oStream As ADODB.Stream
Dim fso As FileSystemObject
Set cn = New ADODB.Connection
cn.Open "Provider = sqloledb;" & _
"Data Source=IP;" & _
"Initial Catalog=DB;" & _
"User ID=USER;" & _
"Password=PASSWORD;"""
sql = "select ID, NAME, FILE from anexo"
Set rs = New ADODB.Recordset
Set fso = New FileSystemObject
rs.Open sql, cn
Do Until rs.EOF
Set oStream = New ADODB.Stream
path = "C:\" & rs.Fields(0).Value
If Not fso.FolderExists(path) Then
MkDir (path)
End If
With oStream
.Type = 1
.Open
.Write rs.Fields(2).Value
.SaveToFile path & "\" & rs.Fields(1).Value, adSaveCreateOverWrite
.Close
End With
Set oStream = Nothing
rs.MoveNext
Loop
rs.Close
cn.Close
End Sub
In addition when I upload the file on https://www.motobit.com/util/binary-file-to-sql-hexstring.asp and convert the binary to simulate an insert query, the output is not equal to the information on the image field from the DB for the same file.
For example, the online binary converted output starts with 0x255044462D312E340A25C7EC8FA2... and the data in the DB starts with 0x504B0304140002000800E4708339A27B1F9FE37....
PD: The corrupted pdf files could be repair using this tool https://www.pdf2go.com/
So I suppose It could be an encoding issue but I'm not familiar with ByteArray encodings.