How to use an ADO Stream object to access and modify SQL Server BLOB Data

Summary

UseStreamObjects can greatly simplify accessing and modifying the code to be written for Binary large objects (BLOB) in the SQL Server database. Used in earlier versions of ADO (2.0, 2.1, and 2.1 SP2)Field objectOfGetchunkAndAppendChunkWhen reading BLOB data from blob columns with a fixed block size, or writing BLOB data into it, you must be careful. Currently, ADO 2.5 provides an alternative method. The sample code in this article demonstrates how to useStreamObject programming to execute the following common tasks:

•	Save the data stored in the SQL Server image column to a file on the hard disk.
•	Move the. GIF file content to the image column of the SQL Server table.

Back to Top

More information

The following code example is based on the data stored in the pub_info table in the SQL Server 7.0 pubs sample database. You need to change the ADO connection string to your own SQL Server.

Example 1: save data in the SQL Server image column to a file on the hard disk

The code in this example will open a record set in the pub_info table in the pubs database, and save the binary image data stored in the logo column of the first record to the file on the hard disk, as shown below:

1.	Open a newStandard exeVisual Basic Project.
2.	InProjectOn the menu, click to selectReferenceAnd then setMicrosoft ActiveX Data Object 2.5 Object Library.
3.	PlaceCommandbuttonControl.
4.	Make the following statement in the General Declaration section of the form: Dim cn As ADODB.ConnectionDim rs As ADODB.RecordsetDim mstream As ADODB.Stream
5.	Cut and paste the following code into the form you addedCommandbuttonControlClickEvent: Set cn = New ADODB.Connectioncn.Open "Provider=SQLOLEDB;data Source=<name of your SQL Server>;Initial Catalog=pubs;User Id=<Your Userid>;Password=<Your Password>"Set rs = New ADODB.Recordsetrs.Open "Select * from pub_info", cn, adOpenKeyset, adLockOptimisticSet mstream = New ADODB.Streammstream.Type = adTypeBinarymstream.Openmstream.Write rs.Fields("logo").Valuemstream.SaveToFile "c:/publogo.gif", adSaveCreateOverWriters.Closecn.Close
6.	Save and run the Visual Basic Project.
7.	ClickCommandbuttonSave the binary data in the logo column of the first record to the C:/publogo. GID file. Find and open this file in Windows Resource Manager to view saved images. The code in this example declares an ADODBStreamObject, andTypeSet propertyAdtypebinaryTo indicate that the object will be used to process binary data. ThenStreamObjectWriteMethod: Write the binary data stored in the logo column of the first record in the pub_info table to this object. Next, callStreamObjectSavetofileTo save the binary data contained in the object to the file. Passed as the second parameterAdsavecreateoverwriteConstant causesSavetofileMethod to overwrite the specified file (if the file exists ).

Example 2: transfer the image stored in the. GIF file to the image column of the SQL Server table

In this example, the code saves the images stored in the. GIF file to the logo column of the first record in the pub_info table to overwrite the current content of the column, as shown below:

1.	Open a newStandard exeVisual Basic Project.
2.	InProjectOn the menu, click to selectReferenceAnd then setMicrosoft ActiveX Data Object 2.5 Object Library.
3.	PlaceCommandbuttonControl.
4.	Make the following statement in the General Declaration section of the form: Dim cn As ADODB.ConnectionDim rs As ADODB.RecordsetDim mstream As ADODB.Stream
5.	Cut and paste the following code into the form you addedCommandbuttonControlClickEvent: Set cn = New ADODB.Connectioncn.Open "Provider=SQLOLEDB;data Source=<name of your SQL Server>;Initial Catalog=pubs;User Id=<Your Userid>;Password=<Your Password>"Set rs = New ADODB.Recordsetrs.Open "Select * from pub_info", cn, adOpenKeyset, adLockOptimisticSet mstream = New ADODB.Streammstream.Type = adTypeBinarymstream.Openmstream.LoadFromFile "<path to .gif file>"rs.Fields("logo").Value = mstream.Readrs.Updaters.Closecn.Close
6.	Save and run the Visual Basic Project.
7.	ClickCommandbuttonRun the code to transmit the content of the. GIF file to ADO as a data stream.StreamObject, and thenStreamSave the data to the logo column of the first record in the record set.
8.	Use the code in Example 1 to verify that the image in the logo column has been modified.