Skip to content

filestream-support.md

Latest commit

 

History

History
79 lines (56 loc) · 4.83 KB

filestream-support.md

File metadata and controls

79 lines (56 loc) · 4.83 KB
title FILESTREAM support in OLE DB Driver for SQL Server
description SQL Server supports the enhanced FILESTREAM feature, which lets you store and access large binary values, either through SQL Server or the file system.
author David-Engel
ms.author davidengel
ms.reviewer randolphwest
ms.date 10/02/2023
ms.service sql
ms.subservice connectivity
ms.topic reference
helpviewer_keywords
FILESTREAM [SQL Server], OLE DB Driver for SQL Server
OLE DB Driver for SQL Server [FILESTREAM support]

FILESTREAM support in OLE DB Driver for SQL Server

[!INCLUDE sql windows only]

[!INCLUDE Driver_OLEDB_Download]

Beginning with [!INCLUDE sql2008-md], OLE DB Driver for SQL Server supports the enhanced FILESTREAM feature. For samples, see FILESTREAM and OLE DB.

FILESTREAM provides a way to store and access large binary values, either through [!INCLUDE ssNoVersion] or by direct access to the Windows file system. A large binary value is a value larger than 2 gigabytes (GB). For more information about enhanced FILESTREAM support, see FILESTREAM (SQL Server).

When a database connection is opened, @@TEXTSIZE is set to -1 (unlimited), by default.

It is also possible to access and update FILESTREAM columns using Windows file system APIs.

For more information, see Access FILESTREAM Data with OpenSqlFilestream.

Query for FILESTREAM Columns

Schema rowsets in OLE DB don't report whether a column is a FILESTREAM column. ITableDefinition in OLE DB can't be used to create a FILESTREAM column.

To create FILESTREAM columns or to detect which existing columns are FILESTREAM columns, you can use the is_filestream column of the sys.columns catalog view.

The following script is an example:

-- Create a table with a FILESTREAM column.
CREATE TABLE Bob_01 (
    GuidCol1 UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWID(),
    IntCol2 INT,
    varbinaryCol3 VARBINARY(MAX) FILESTREAM
);

-- Find FILESTREAM columns.
SELECT name
FROM sys.columns
WHERE is_filestream = 1;

-- Determine whether a column is a FILESTREAM column.
SELECT is_filestream
FROM sys.columns
WHERE name = 'varbinaryCol3'
    AND object_id IN (
        SELECT object_id
        FROM sys.tables
        WHERE name = 'Bob_01'
    );

Down-Level Compatibility

If your client was compiled using OLE DB Driver for SQL Server, and the application connects to [!INCLUDE ssSQL11] and later versions, then varbinary(max) behavior is compatible with the behavior introduced by [!INCLUDE ssNoVersion] Native Client in [!INCLUDE ssVersion2005]. That is, the maximum size of returned data is limited to 2 GB. For result values larger that 2 GB, truncation occurs and a "string data right truncation" warning is returned.

When data-type compatibility is set to 80, client behavior is consistent with down-level client behavior.

For clients that use SQLOLEDB or other providers that were released before the [!INCLUDE ssVersion2005], varbinary(max) is mapped to image.

Comments

  • To send and receive varbinary(max) values greater than 2 GB, an application uses DBTYPE_IUNKNOWN in parameter and result bindings. For parameters the provider must call IUnknown::QueryInterface for ISequentialStream and for results that return ISequentialStream.

  • For OLE DB, checking related to ISequentialStream values are relaxed. When wType is DBTYPE_IUNKNOWN in the DBBINDING struct, length checking can be disabled either by omitting DBPART_LENGTH from dwPart, or by setting the length of the data (at offset obLength in the data buffer) to ~0. In this case, the provider doesn't check the length of the value, and requests and returns all of the data available through the stream. This change is applied to all large object (LOB) types and XML, but only when connected to [!INCLUDE ssVersion2005] (or later) servers. This provides greater flexibility for developers, while maintaining consistency and backward compatibility for existing applications and downlevel servers. This change affects all interfaces that transfer data, principally IRowset::GetData, ICommand::Execute, and IRowsetFastLoad::InsertRow.

Related content