Output parameter streaming feature in SQL Native client 11

Recently we had a case where customer wanted to stream output parameter using SNAC 11. Rather than let the good research go to waste, we're posting the steps here.

It’s not optimum or sometimes  not even possible to define  large buffer  when the  parameter that is being  fetched from the SQL server is very large, because earlier versions ODBC Driver Manager version 3.8 does not support retrieving the large output parameter in small chunks multiple times.

Beginning from ODBC  Driver Manager 3.8 & SQL Native Client Version 11.0 supports a new feature called Output Parameter Streaming. Applications  memory footprint size can be reduced using this feature, by  invoking SQLGetData using small size buffer multiple times  to retrieve large output parameter value.  This feature is supported by ODBC Driver manager version 3.8 and SQL Native client version 11.0 or higher only.

Lets follow these steps to implement this feature  using sample application:

1. Download and install the latest version of Windows platform SDK  http://msdn.microsoft.com/en-us/windows/bb980924
2. Install SQL Native Client 11.0 on the application machine or workstation - http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9
3. Create a sample  table and import  some binary data in SQL Server ( For example, SQL 2008, SQL 2008 R2)

CREATE TABLE TableImage(Document varbinary(max)) INSERT INTO TableImage(Document)  SELECT * FROM OPENROWSET(BULK N'SomeImage.bmp', SINGLE_BLOB) AS I

4. Create stored procedure that returns image of given ID:

CREATE PROCEDURE [dbo].[SP_TestOutputPara] @Param1 integer,

@Param2 VARBINARY(max) OUTPUT

AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;

SELECT @Param2 = [Document]  FROM [pubs].[dbo].[TableImage] where [TableImage].[id]  = 1

END
GO

5. Define DSN using SQL Native Client  Version 11.0  to point to the database that hosts the table and SP created at the above steps 3 & 4

6. The sample code that shows  how to bind  the  streamed output parameter and retrieve the large output  parameter multiple times  using SQLGetData()

BOOL GetBinaryDataInChunks(SQLUINTEGER idOfPicture, SQLHSTMT hstmt)
{

SQLINTEGER lengthOfPicture=SQL_DATA_AT_EXEC;

Read more: Microsoft SQL Server Support Blog

Posted via email from .NET Info