Using the ADO Stream Object to Manage BLOBs
The
following is an unedited excerpt from my new book
Since ADO 2.5, the Stream object has evolved into a pivotal component in the ADO data access paradigm. As we will see in later chapters, streams play an even more critical role in ADO.NET. As developers discover the flexibility and performance of Stream objects, and how well they integrate into ADO, I expect more and more code to include routines driven from Stream IO. The Stream is basically an in-memory data structure that can be used to persist columns, entire Recordsets, or virtually any form of data. Since developers often have to extract BLOB data from Recordsets into memory and immediately save this data to files, ADO’s Stream object is an ideal mechanism for managing these large blocks of binary or text data.
In the following example, you’ll see that in order to
fetch a BLOB you simply need to return a Recordset containing the data and use
the Stream object to extract the data from the Field.Value property. To save a
BLOB, reverse the process, filling the Value property from the Stream. Once you
set the Field.Value, all you need to do is use the Recordset Update method to
save it—
Note: Just before this section, the book discusses why
you should avoid putting Binary Large Objects (BLOBs) in the database in the
first place. This example is intended for those who have already decided to do
so against my advice.
The first routine after the object setup declarations
executes a stored procedure that returns all of the Cover images from the
database whose titles match a given string.
Option Explicit
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim stm As ADODB.Stream
Dim fso As FileSystemObject
Private Sub btnFindPicture_Click()
If rs.State = adStateOpen Then rs.Close
cn.GetCoverByTitle txtTitleWanted.Text, rs
If rs.RecordCount > 0 Then
GetAndShowPicture rs
If rs.RecordCount > 1 Then
btnNextPicture.Enabled = True
Else
btnNextPicture.Enabled = False
End If
Else
MsgBox "No picture on file for this title"
End If
End Sub
This next routine (GetAndShowPicture) fetches the binary Cover column from the Recordset, and in one operation saves it to a temporary file (destroying any existing temp file by the same name in the process). The routine turns around and immediately uses this file to load an image control used to display the file in a WinForm application.
Sub GetAndShowPicture(rs As ADODB.Recordset)
Dim strTempFilename As String
strTempFilename = App.Path & "\" & fso.GetTempName
Set stm = New ADODB.Stream
With stm
.Open
.Type = adTypeBinary
.Write rs.Fields("Cover").Value
.SaveToFile strTempFilename, adSaveCreateOverWrite
End With
Image1.Picture = LoadPicture(strTempFilename)
Kill strTempFilename
End Sub
The Stream object can also be used to save BLOB data to the database. The following example saves TEXT data from a text file to the database using an updatable Recordset. Frankly, this scenario is a bit contrived as I would be hesitant to use an updatable Recordset for this task—I would much more likely use an UPDATE statement or a stored procedure. However, this is really pretty easy so it does have some appeal, but I also expect the overhead to be somewhat higher than query techniques I’ve already discussed.
The following example (yes, it’s on the CD that comes with the book), opens a file (based on the CommonDialog control), and uses the ADO Stream object to open it. The same stream object is used as a source to pass to the Recordset Value property. Yes, I created an updatable Recordset for this example. Not that I wanted to, but it was necessary for this example.
Option Explicit
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim stm As ADODB.Stream
Dim PubIDFound As Integer
Here we choose a file and use the Stream to open it as an ASCII file. Of course this approach could also be used to read binary files (such as Microsoft Word or Excel documents) just as easily, but you’ll have to change a few of the Stream properties to accommodate the binary file content. In this case I set the Stream Charset property to “ASCII” to reflect that I’m reading files created with Visual Notepad. If you don’t use this option, the Stream defaults to Unicode and the Visual Basic TextBox (and most other “Windows-based” controls don’t know how to display Unicode—so you get lots of “?” characters).
Tip: You can use the Visual Basic “Lightweight” controls to display Unicode
Private Sub btnGetFilename_Click()
With CommonDialog1
.ShowOpen
txtFileName.Text = .FileName
txtComments.FileName = .FileName
End With
Set stm = New ADODB.Stream
With stm
.Charset = "ascii"
.Open
.LoadFromFile txtFileName.Text
.Position = 0
txtComments.Text = .ReadText
End With
Next, the code reads from the Stream and writes to the Value property. Notice that I set the Stream Position property to start reading from the first (0) character of the Stream.
End Sub
Private Sub btnSaveBLOB_Click()
stm.Position = 0
rs.Fields("Comments").Value = stm.ReadText ' Read from Stream into the Column
rs.Update
End Sub
Folks, it does not get much easier than that.