How to use Visual FoxPro 6.0 and later versions and the ADO Stream object to handle Binary Large Object (BLOB) data (272338)


The information in this article applies to:

  • Microsoft Visual FoxPro for Windows 6.0
  • Microsoft Visual FoxPro for Windows 7.0
  • Microsoft Visual FoxPro 8.0
  • Microsoft Visual FoxPro 9.0 Professional Edition
This article was previously published under Q272338

SUMMARY

Microsoft Visual FoxPro 6.0 and later versions have the capability to handle Binary Large Object (BLOB) data from providers like Microsoft SQL Server by using ActiveX Data Objects (ADO) Stream objects and recordsets.

A BLOB is a type of data column that contains binary data such as graphics, sound, or compiled code. From the perspective of an application, this data is "formless." In SQL Server, this data is commonly known as an "Image" data type.

MORE INFORMATION

The following code sample assumes that you have the necessary access and privileges to a Microsoft SQL Server, and that you have the pub_info table in the SQL Server 7.0 or SQL Server 2000 Pubs sample database.
  1. In Visual FoxPro, create a new program.
  2. Paste the following code into the new program:
    CLEAR
CLEAR ALL
RELEASE ALL
#DEFINE LOC_SQLSERVERNAME	"MYSERVER"
#DEFINE LOC_SQLUID		"SA"
#DEFINE LOC_SQL_PWD	""

LOCAL lcConnStr, lcSQL, loConnection, lcImageName, ;
	loRecordSet, loADOStream

lcConnStr="Provider=SQLOLEDB;" +;
	"Data Source=" + LOC_SQLSERVERNAME + ";" +;
	"Initial Catalog=PUBS;" +;
	"UID=" + LOC_SQLUID + ";" +;
	"Pwd=" + LOC_SQL_PWD

lcSQL = "SELECT * FROM pub_info"

*!* Creates the objects.
loConnection = CREATEOBJECT("ADODB.Connection")
loRecordSet = CREATEOBJECT("ADODB.Recordset")
loADOStream = CREATEOBJECT("ADODB.Stream")

*!* Open the connections.
loConnection.OPEN(lcConnStr)
loRecordSet.OPEN(lcSQL,loConnection,2,3)

*!* Set Stream Object properties.
loADOStream.TYPE = 1	&& 1=Binary Data, 2=Text Data.
loADOStream.OPEN

loADOStream.WRITE(loRecordSet.FIELDS("logo")) 	&& Pass data to the stream object.
lcImageName = "BLOB.gif"                  		&& Sets the local file name.
loADOStream.SaveToFile(lcImageName,2)         	&& Saves stream object to a file.
@1,1 SAY CURDIR() + lcImageName BITMAP    		&& Displays image file

*!* Close connections.
loRecordSet.CLOSE
loConnection.CLOSE
loADOStream.CLOSE

ERASE CURDIR() + "BLOB.gif"
  3. Change the three #DEFINE statements to reflect your local setup, and then save and run the program.
  4. Observe the image on the Visual FoxPro desktop.
(c) Microsoft Corporation 2000, All Rights Reserved. Contributions by Reinaldo Torrales, Microsoft Corporation.

REFERENCES

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

258038 How to access and modify SQL Server BLOB data by using the ADO Stream object

Modification Type:MajorLast Reviewed:3/18/2005
Keywords:kbhowto KB272338
©2004 Microsoft Corporation. All rights reserved.