Readme_Sending BLOB Data to SQL SERVER Using IROWSETFASTLOAD and ISEQUENTIALSTREAM

11/05/2008 21:36:06


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:
  • 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 that are available 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 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* © 2008 Microsoft Corporation. All rights reserved.

Last edited Nov 6, 2008 at 9:55 PM by bonniefe, version 3

Comments

No comments yet.