Share via

Readme_Sending BLOB Data to SQL SERVER Using IROWSETFASTLOAD and ISEQUENTIALSTREAM

  • Article

This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.

This sample shows how to use IRowsetFastLoad to stream varying length BLOB data per row.

SQL Server samples and sample databases must be downloaded and installed before you can view or work with them. For more information, see Considerations for Installing SQL Server Samples and Sample Databases.

Scenario

By default, this sample shows how to use IRowsetFastLoad to send variable length BLOB data per row by using in-line bindings. The in-line BLOB data must fit in available memory. This method performs best when the BLOB data is less than a few megabytes, because there is no additional stream overhead. For data larger than a few megabytes, especially data that is is not available in a block, streaming provides better performance.

In the source code, when you uncomment #define USE_ISEQSTREAM, the sample will use ISequentialStream. The stream implementation is defined in the sample, and can send any size BLOB data simply by changing MAX_BLOB. Stream data does not have to fit in memory or be available in one block. You call this provider by using IRowsetFastLoad::InsertRow. Pass a pointer using IRowsetFastLoad::InsertRow to the stream implementation in the data buffer (rgBinding.obValue offset) along with the amount of data available to read from the stream. Some providers might not have to know the length of the data when binding occurs. In this case, the length can be omitted from the binding.

The sample does not use the provider’s stream interface to write data to the provider. Instead, the sample passes a pointer to the stream object that the provider will consume to read the data. Typically, Microsoft providers (SQLOLEDB and SQLNCLI) will read data in 1024-byte chunks from the object until all data has been processed. Neither SQLOLEDB nor SQLNCLI have full implementations for allowing the consumer to write data to the provider's stream object. Only zero length data can be sent through the provider's stream object.

The consumer-implemented ISequentialStream object can be used with rowset data (IRowsetChange::InsertRow, IRowsetChange::SetData) and with parameters by binding a parameter as DBTYPE_IUNKNOWN.

Because DBTYPE_IUNKNOWN is specified as the data type in the binding, it must match the type of the column or target parameter. Conversions are not possible when sending data through ISequentialStream from rowset interfaces. For parameters, you should avoid using ICommandWithParameters::SetParameterInfo and specify a different type to force a conversion; this would require the provider to cache all the BLOB data locally, to convert it before sending to SQL Server. Caching a large BLOB and converting it locally does not provide good performance.

For more information, see BLOBs and OLE Objects.

Languages

This sample uses Transact-SQL and Visual C++.

Prerequisites

Before running this sample, make sure the following software is installed:

Building the Sample

  • This sample connects to your computer's default SQL Server instance. On some Windows operating systems, you will have to change (local) to the name of your SQL Server instance. To connect to a named instance, change the connection string from (local) to "(local)\\name", where name is the named instance. By default, SQL Server Express installs to a named instance.

  • Make sure your INCLUDE environment variable includes the directory that contains sqlncli.h.

  • The sample requires you to execute the following stored procedure, which you can find in the sample's scripts directory:

    use master
create table fltest(col1 int, col2 int, col3 image)

    In SQL Server Management Studio, load and execute the Scripts\setup.sql script or execute the following command in a Command Prompt window:

    sqlcmd -E -I -i Scripts\setup.sql

  • If you are using Visual Studio, load the IRowsetFastLoad.sln file and build it.

  • If you are using MSBuild.exe, invoke MSBuild.exe at a command prompt, passing it the IRowsetFastLoad.sln file:

    MSBuild IRowsetFastLoad.sln

Running the Sample

  • From Visual Studio, invoke Start Without Debugging (CTRL+F5).
  • If you built with MSBuild.exe, invoke IRowsetFastLoad.exe.

See Also

Concepts

Data Access Samples

Help and Information

Getting SQL Server 2008 Assistance