HOWTO: Access and Modify SQL Server BLOB Data by Using the
ADO Stream Object |
The information in this article applies to:
- ActiveX Data Objects (ADO), version 2.5
- Microsoft Visual Basic Professional and Enterprise Editions
for Windows, version 6.0
- Microsoft OLE DB Provider for SQL Server, version 7.0
- Microsoft SQL Server version 7.0
SUMMARY
The Stream object 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 GetChunk and
AppendChunk methods of the Field Object to 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
Stream object 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.
MORE INFORMATION
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 DiskThe 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 EXE Visual Basic project.
- On the Project menu, click to select References,
and then set a reference to the Microsoft ActiveX Data Objects
2.5 Object Library.
- Place a CommandButton control on Form1.
- Make the following declarations in the form's General
declarations section:
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mstream As ADODB.Stream
- Cut and paste the following code into the Click event
of the CommandButton that you added to the form:
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;data Source=<name of your SQL Server>;
Initial Catalog=pubs;User Id=<Your Userid>;Password=<Your 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.Write rs.Fields("logo").Value
mstream.SaveToFile "c:\publogo.gif", adSaveCreateOverWrite
rs.Close
cn.Close
- Save and run the Visual Basic project.
- Click the CommandButton to 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
Stream object and sets its Type property to
adTypeBinary to 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 Stream object by calling its
Write method. The Stream object now contains the
binary data that is saved to the file by calling its
SaveToFile method 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.
Example 2 : Transfer the Image Stored in a .gif File to an Image
Column in a SQL Server TableThe 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 EXE Visual Basic project.
- On the Project menu, click to select References,
and then set a reference to the Microsoft ActiveX Data Objects
2.5 Object Library.
- Place a CommandButton on Form1.
- Make the following declarations in the form's General
declarations section:
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mstream As ADODB.Stream
- Cut and paste the following code in the Click event of
the CommandButton that you added to the form:
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;data Source=<name of your SQL Server>;
Initial Catalog=pubs;User Id=<Your Userid>;Password=<Your 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 "<path to .gif file>"
rs.Fields("logo").Value = mstream.Read
rs.Update
rs.Close
cn.Close
- Save and run the Visual Basic project.
- Click on the CommandButton to run the code to stream
the contents of the .gif file to the ADO Stream object, and
save the data in the Stream to the logo column in the first
record of the recordset.
- Verify that the image in the logo column has been modified by
using the code in Example 1.
Additional query words:
Keywords : kbVBp600 kbSQLServ700 kbGrpVBDB kbDSupport kbMDAC250
kbADO250
Version : WINDOWS:2.5,6.0,7.0; winnt:7.0
Platform :
WINDOWS winnt
Issue type : kbhowto
Technology : kbvcSearch
|
|
.gif) |
|
Send to a friend