Solved Adding files using ADODB.Stream to SQL BLOB - varbinary(max) doesn't work - same code used to work OK for both SQL and Oracle BEs (1 Viewer)

bastanu

AWF VIP
Local time
Today, 03:02
Joined
Apr 13, 2010
Messages
1,401
Hi all,
I am curious if anyone can spot anything wrong with the following code used to add files (PDFs, pictures, etc.) to a SQL table. Column is defined as varbinary(max). Same code used to work OK for both SQL server and Oracle back-ends. The records get created but the problem seems to be with strStream.Read as it returns Null. Just before that line I check the strStream.Size in the intermediate window and it returns the correct size for the picture file I am attempting to load. All records show null in that column and attempting to retrieve the file using stream.write fails.
Screenshot 2021-07-26 091010.png


Code:
Private Sub cmdAddFileToBlob_Click()
      
        
Dim sConnectionString As String, RS As ADODB.Recordset, cnnConnection As ADODB.Connection, sFileName As String
Dim strStream As ADODB.Stream, sFileType As String

Dim f    As Object
Set f = Application.FileDialog(3)
f.AllowMultiSelect = False
If f.Show = True Then
    sFileName = f.selecteditems(1)
Else
    sFileName = ""
End If

On Error Resume Next


If sFileName = "" Then Exit Sub

sFileType = Mid(sFileName, InStrRev(sFileName, ".") + 1)


Set cnnConnection = New ADODB.Connection
Set RS = New ADODB.Recordset
sConnectionString = CurrentDb.TableDefs("tbl_ChapterFiles").Connect

sConnectionString = Replace(sConnectionString, "ODBC;", "")
cnnConnection.Open (sConnectionString)
RS.Open "Select * from  tbl_ChapterFiles ", cnnConnection, adOpenKeyset, adLockOptimistic
        Set strStream = New ADODB.Stream
        strStream.Type = adTypeBinary
        strStream.Open
        strStream.LoadFromFile sFileName
        RS.AddNew
        
        RS.Fields("ChapterFile").Value = strStream.Read
        RS.Fields("ChapterID") = Me.ChapterID
        RS.Fields("UploadedON") = Now()
        RS.Fields("UploadedBY") = Forms![frmMainSwitchboard]![UserName]
        RS.Fields("FILENAME") = StripLast(sFileName)
        RS.Fields("FILETYPE") = sFileType
        RS.Update

Set strStream = Nothing
RS.Close
cnnConnection.Close
Set cnnConnection = Nothing
Set RS = Nothing

Me.sfrmChapterFiles.Form.Requery

procExitSub:
    Exit Sub
procNoPicture:
    
    GoTo procExitSub
        
End Sub

Thanks!
Vlad
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:02
Joined
Oct 29, 2018
Messages
21,357
Hi Vlad. Can't say what's wrong with your code, and I don't have any examples of this, but have you tried using the AppendChunk method?

Just a thought...
 

bastanu

AWF VIP
Local time
Today, 03:02
Joined
Apr 13, 2010
Messages
1,401
Not yet, I will if I can't get it to work (I'll also try the pass-through method using OpenRowSet), just hopping someone might know why it stopped working. Maybe a setting in the SQL Server itself got changed\added in the newer version(s)?

Cheers,
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:02
Joined
Oct 29, 2018
Messages
21,357
Not yet, I will if I can't get it to work (I'll also try the pass-through method using OpenRowSet), just hopping someone might know why it stopped working. Maybe a setting in the SQL Server itself got changed\added in the newer version(s)?

Cheers,
Sounds good. Good luck and let us know how it goes.

In case the first link doesn't help, here's one with a sample code, but it's DAO.

 

bastanu

AWF VIP
Local time
Today, 03:02
Joined
Apr 13, 2010
Messages
1,401
It might be the driver, I currently use "DRIVER=ODBC Driver 17 for SQL Server", did anyone have any issues with this? I will try tomorrow with Native Client 11 which should still be on my laptop...
Cheers,
 

Minty

AWF VIP
Local time
Today, 10:02
Joined
Jul 26, 2013
Messages
10,353
Hi Vlad, we have switched over to ODBC Driver 17, for nearly all our projects as it seems much more robust.
I have an image upload routine somewhere - let me find it and try it, the code looks very similar.
 

sonic8

AWF VIP
Local time
Today, 11:02
Joined
Oct 27, 2015
Messages
998
It might be the driver, [...]
If you correctly diagnosed the problem as strStream.Read not returning data, it cannot be caused by the SQL Server Driver. The Stream was only supposed to load a local file yet. The driver is not involved in that at all.
 

sonic8

AWF VIP
Local time
Today, 11:02
Joined
Oct 27, 2015
Messages
998
Just a long shot....

Is your actual code exactly as the code you posted above?
Please double check for any additional calls to Stream.Read before you try to set the .Value of the recordset field. (Maybe for debugging purposes.)
Stream.Read will advance the internal position pointer to the position the stream has read to already. I.e, if you call Stream.Read once, the pointer will be at the end of the Stream and all subsequent calls to Stream.Read will return Null. You can use the .Position property to check or reset the internal position pointer of the stream.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:02
Joined
May 7, 2009
Messages
19,169
if you made changes to the datatype in mssql, delete/relink the table.
 

bastanu

AWF VIP
Local time
Today, 03:02
Joined
Apr 13, 2010
Messages
1,401
Thanks guys for all the great suggestions. It seems strange as this same code used to work years ago for both SQL Server and Oracle.
@sonic8 - yes, that is the entire code behind my Add File button. If I step through the code it shows the Rs("ChapterFile").Value=strStream.Read as empty; the record gets add OK but the file is not loaded.
Screenshot 2021-07-27 083348.png


I have found this older thread that made me wonder if the driver has anything to do with this:

EDIT:After some more reading it looks like that the driver doesn't support ADO: https://accessexperts.com/blog/2018/09/11/new-sql-server-odbc-and-oledb-driver/


Cheers,
Vlad
 
Last edited:

bastanu

AWF VIP
Local time
Today, 03:02
Joined
Apr 13, 2010
Messages
1,401
Hi guys,
I ended up changing it to use AppendChunk (DAO) and it works. Funny thing is the retrieval of the file is still using the ADODB stream.write and that works fine.

Here is what I now have:
Code:
Private Sub cmdAddFileToBlob_Click()
      
        
Dim sFileName As String
Dim sFileType As String
Dim f    As Object
Dim rs As DAO.Recordset

Set f = Application.FileDialog(3)
f.AllowMultiSelect = False
If f.Show = True Then
    sFileName = f.selecteditems(1)
Else
    sFileName = ""
End If

'On Error Resume Next

If sFileName = "" Then Exit Sub
sFileType = Mid(sFileName, InStrRev(sFileName, ".") + 1)

Set rs = CurrentDb.OpenRecordset("tbl_ChapterFiles", dbOpenDynaset, dbSeeChanges)
        rs.AddNew
        'RS.Fields("ChapterFile").Value = strStream.Read
        rs.Fields("ChapterID") = Me.ChapterID
        rs.Fields("UploadedON") = Now()
        rs.Fields("UploadedBY") = Forms![frmMainSwitchboard]![UserName]
        rs.Fields("FILENAME") = StripLast(sFileName)
        rs.Fields("FILETYPE") = sFileType
        rs.Update

rs.Close
Set rs = Nothing

Dim lId As Long
lId = DMax("FileID", "tbl_ChapterFiles")
Call ReadBLOB(sFileName, "tbl_ChapterFiles", "ChapterFile", "FileID", lId)

Me.sfrmChapterFiles.Form.Requery

procExitSub:
    Exit Sub
procNoPicture:
    
    GoTo procExitSub
        
End Sub

And the ReadBLOB:
Code:
Public Function ReadBLOB(SourceFileName As String, TableName As String, FieldName As String, IDFieldName As String, IDFieldValue As Variant)
    Dim NumBlocks As Integer, SourceFile As Integer, i As Integer
    Dim FileLength As Long
    Dim LeftOver As Long
    Dim FileData() As Byte
    Dim RetVal As Variant
    Dim BlockSize As Long

    Dim s As String

    On Error GoTo Err_ReadBLOB

    BlockSize = 32767

    ' Open the source file.
    SourceFile = FreeFile
    Open SourceFileName For Binary Access Read As SourceFile

    ' Get the length of the file.
    FileLength = LOF(SourceFile)
    If FileLength = 0 Then
        ReadBLOB = 0
        Exit Function
    End If

    ' Calculate the number of blocks to read and leftover bytes.
    NumBlocks = FileLength \ BlockSize
    LeftOver = FileLength Mod BlockSize

    Dim T As DAO.Recordset

    If TypeName(IDFieldValue) = "String" Then
        IDFieldValue = "'" & IDFieldValue & "'"
    End If

    s = "SELECT [" & FieldName & "] FROM [" & TableName & "] WHERE [" & IDFieldName & "] = " & IDFieldValue

    Set T = CurrentDb.OpenRecordset(s, dbOpenDynaset, dbSeeChanges)

    T.Edit

    ' Read the 1st block of data (upto Leftover in size), writing it to the table.
    'FileData = String$(LeftOver, 32)
    ReDim FileData(1 To LeftOver)
    Get SourceFile, , FileData
    T(FieldName).AppendChunk (FileData)

    ' Read the remaining blocks of data, writing them to the table.
    'FileData = String$(BlockSize, 32)
    ReDim FileData(1 To BlockSize)
    For i = 1 To NumBlocks
        Get SourceFile, , FileData
        T(FieldName).AppendChunk (FileData)

    Next i

    ' Update the record and terminate function.
    T.Update
    Close SourceFile
    ReadBLOB = FileLength
    Exit Function

Err_ReadBLOB:
    ReadBLOB = -Err

    MsgBox Err.Description

    Exit Function
End Function

Thanks all!
Vlad
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:02
Joined
Oct 29, 2018
Messages
21,357
Hi guys,
I ended up changing it to use AppendChunk (DAO) and it works. Funny thing is the retrieval of the file is still using the ADODB stream.write and that works fine.

Here is what I now have:
Code:
Private Sub cmdAddFileToBlob_Click()
     
       
Dim sFileName As String
Dim sFileType As String
Dim f    As Object
Dim rs As DAO.Recordset

Set f = Application.FileDialog(3)
f.AllowMultiSelect = False
If f.Show = True Then
    sFileName = f.selecteditems(1)
Else
    sFileName = ""
End If

'On Error Resume Next

If sFileName = "" Then Exit Sub
sFileType = Mid(sFileName, InStrRev(sFileName, ".") + 1)

Set rs = CurrentDb.OpenRecordset("tbl_ChapterFiles", dbOpenDynaset, dbSeeChanges)
        rs.AddNew
        'RS.Fields("ChapterFile").Value = strStream.Read
        rs.Fields("ChapterID") = Me.ChapterID
        rs.Fields("UploadedON") = Now()
        rs.Fields("UploadedBY") = Forms![frmMainSwitchboard]![UserName]
        rs.Fields("FILENAME") = StripLast(sFileName)
        rs.Fields("FILETYPE") = sFileType
        rs.Update

rs.Close
Set rs = Nothing

Dim lId As Long
lId = DMax("FileID", "tbl_ChapterFiles")
Call ReadBLOB(sFileName, "tbl_ChapterFiles", "ChapterFile", "FileID", lId)

Me.sfrmChapterFiles.Form.Requery

procExitSub:
    Exit Sub
procNoPicture:
   
    GoTo procExitSub
       
End Sub

And the ReadBLOB:
Code:
Public Function ReadBLOB(SourceFileName As String, TableName As String, FieldName As String, IDFieldName As String, IDFieldValue As Variant)
    Dim NumBlocks As Integer, SourceFile As Integer, i As Integer
    Dim FileLength As Long
    Dim LeftOver As Long
    Dim FileData() As Byte
    Dim RetVal As Variant
    Dim BlockSize As Long

    Dim s As String

    On Error GoTo Err_ReadBLOB

    BlockSize = 32767

    ' Open the source file.
    SourceFile = FreeFile
    Open SourceFileName For Binary Access Read As SourceFile

    ' Get the length of the file.
    FileLength = LOF(SourceFile)
    If FileLength = 0 Then
        ReadBLOB = 0
        Exit Function
    End If

    ' Calculate the number of blocks to read and leftover bytes.
    NumBlocks = FileLength \ BlockSize
    LeftOver = FileLength Mod BlockSize

    Dim T As DAO.Recordset

    If TypeName(IDFieldValue) = "String" Then
        IDFieldValue = "'" & IDFieldValue & "'"
    End If

    s = "SELECT [" & FieldName & "] FROM [" & TableName & "] WHERE [" & IDFieldName & "] = " & IDFieldValue

    Set T = CurrentDb.OpenRecordset(s, dbOpenDynaset, dbSeeChanges)

    T.Edit

    ' Read the 1st block of data (upto Leftover in size), writing it to the table.
    'FileData = String$(LeftOver, 32)
    ReDim FileData(1 To LeftOver)
    Get SourceFile, , FileData
    T(FieldName).AppendChunk (FileData)

    ' Read the remaining blocks of data, writing them to the table.
    'FileData = String$(BlockSize, 32)
    ReDim FileData(1 To BlockSize)
    For i = 1 To NumBlocks
        Get SourceFile, , FileData
        T(FieldName).AppendChunk (FileData)

    Next i

    ' Update the record and terminate function.
    T.Update
    Close SourceFile
    ReadBLOB = FileLength
    Exit Function

Err_ReadBLOB:
    ReadBLOB = -Err

    MsgBox Err.Description

    Exit Function
End Function

Thanks all!
Vlad
Hi Vlad. Congratulations! Did you also stick with ODBC 17?
 

bastanu

AWF VIP
Local time
Today, 03:02
Joined
Apr 13, 2010
Messages
1,401
Yes, I didn't want to change to Microsoft OLE DB Driver 18.6 for SQL Server as I didn't want to break anything with existing DAO code.

Cheers,
Vlad
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:02
Joined
Oct 29, 2018
Messages
21,357
Yes, I didn't want to change to Microsoft OLE DB Driver 18.6 for SQL Server as I didn't want to break anything with existing DAO code.

Cheers,
Vlad
Thanks for the update. Good luck!
 

Users who are viewing this thread

Top Bottom