8/10/2019 How to Access and Modify SQL Server BLOB

1/1

The Streamobject introduced in ActiveX Data Objects (ADO) 2.5 can be used to greatly simplify the code that needs to be written to access and modify Binary Large

Object (BLOB) data in a SQL Server Database. The previous versions of ADO [ 2.0, 2.1, and 2.1 SP2 ] required careful usage of the GetChunkand AppendChunk

methods of the Field Objectto read and write BLOB data in fixed-size chunks from and to a BLOB column. An alternative to this method now exists with the advent

of ADO 2.5. This article includes code samples that demonstrate how the Streamobject can be used to program the following common tasks:

Save the data stored in a SQL Server Image column to a file on the hard disk.

Move the contents of a .gif file to an Image column in a SQL Server table.

The following code samples are based on the data stored in the pub_info table in the SQL Server 7.0 pubs sample database. You need to modify the ADO connection

string to point to your SQL Server installation.

Example 1 : Saving the Data in a SQL Server Image Column to a File on the Hard Disk

The code in this example opens a recordset on the pub_info table in the pubs database and saves the binary image data stored in the logo column of the first record

to a file on the hard disk, as follows:

Open a new Standard EXEVisual Basic project.1.

On the Projectmenu, click to select References, and then set a reference to the Microsoft ActiveX Data Objects 2.5 Object Library .2.

Place a CommandButtoncontrol on Form1.3.

Make the following declarations in the form's General declarations section:4.

Cut and paste the following code into the Clickevent of the CommandButtonthat you added to the form:5.

Save and run the Visual Basic project.6.

Click the CommandButtonto save the binary data in the logo column of the first record to the file c:\publogo.gid. Look for this file in Windows Explorer and

open it to view the saved image.

The code in this example declares an ADODB Streamobject and sets its Type property to adTypeBinaryto reflect that this object will be used to work with

Binary data. Following this, the binary data stored in the logo column of the first record in the pub_info table is written out to the Streamobject by calling its

Writemethod. The Streamobject now contains the binary data that is saved to the file by calling its SaveToFilemethod and passing in the path to the file.

The adSaveCreateOverWrite constant passed in as the second parameter causes the SaveToFile method to overwrite the specified file if it already exists.

7.

Example 2 : Transfer the Image Stored in a .gif File to an Image Column in a SQL Server Table

The code in this example saves an image stored in a .gif file to the logo column in the first record of the pub_info table by overwriting its current contents, as follows:

Open a new Standard EXEVisual Basic project.1.

On the Project menu, click to select References, and then set a reference to the Microsoft ActiveX Data Objects 2.5 Object Library .2.

Place a CommandButtonon Form1.3.

Make the following declarations in the form's General declarations section:4.

Cut and paste the following code in the Clickevent of the CommandButtonthat you added to the form:5.

Save and run the Visual Basic project.6.

Click on the CommandButtonto run the code to stream the contents of the .gif file to the ADO Streamobject, and save the data in the Streamto the logo

column in the first record of the recordset.

7.

Verify that the image in the logo column has been modified by using the code in Example 1.8.

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

308042(http://support.microsoft.com/kb/308042/EN-US/ ) How To Read and Write BLOB Data by Using ADO.NET with Visual Basic .NET

SUMMARY

MORE INFORMATION

Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim mstream As ADODB.Stream

Set cn = New ADODB.Connection cn.Open "Provider=SQLOLEDB;data Source=; Initial Catalog=pubs;UserId=;Password=" Set rs = New ADODB.Recordset rs.Open "Select * from pub_info", cn, adOpenKeyset,adLockOptimistic Set mstream = New ADODB.Stream mstream.Type = adTypeBinary mstream.Open mstream.Writers.Fields("logo").Value mstream.SaveToFile "c:\publogo.gif", adSaveCreateOverWrite rs.Close cn.Close

Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim mstream As ADODB.Stream

Set cn = New ADODB.Connection cn.Open "Provider=SQLOLEDB;data Source=; Initial Catalog=pubs;UserId=;Password=" Set rs = New ADODB.Recordset rs.Open "Select * from pub_info", cn, adOpenKeyset,adLockOptimistic Set mstream = New ADODB.Stream mstream.Type = adTypeBinary mstream.Open mstream.LoadFromFile "" rs.Fields("logo").Value = mstream.Read rs.Update rs.Close cn.Close

REFERENCES

How To Access and Modify SQL Server BLOB Dat... http://support2.microsoft.com/kb/258

of 1 29/09/2014 00