6/4/2008 1:31:57 AM

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, the sample shows how to use IRowsetFastLoad to send variable length BLOB data per row using in-line bindings. The in-line BLOB data must fit in available memory. This method has better performance when the BLOB data is less than a few megebytes because there is no additional stream overhead. For data larger than a ew megabytes, especially where the data is not available in a block, streaming gives 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. The provider is called using IRowsetFastLoad::InsertRow, passing a pointer 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 may not require the length of the data to be known when binding occurs, and in that case the length may be omitted from the binding.

The sample does not write data to the provider using the provider’s stream interface. Rather, 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 via 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.

Since 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 via 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 give 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:

  • SQL Server or SQL Server Express including Database Engine.
    You can download SQL Server Express from the Microsoft Download Center.
  • The SQL Server Database Engine samples. These samples are included with SQL Server. You can download the latest version of the samples at the Microsoft SQL Server Developer Center.
  • .NET Framework SDK 2.0 or Microsoft Visual Studio 2005. You can obtain .NET Framework SDK free of charge. See Installing the .NET Framework Documentation.

Building the Sample

  • This sample connects to your computer's default SQL Server instance (on some Windows operating systems, you will need 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