Accéder à des données FILESTREAM avec OpenSqlFilestream

S’applique à : SQL Server

L’API OpenSqlFilestream obtient un descripteur de fichier compatible Win32 pour un objet BLOB FILESTREAM stocké dans le système de fichiers. Le descripteur peut être passé à chacune des API Win32 suivantes : ReadFile, WriteFile, TransmitFile, SetFilePointer, SetEndOfFileou FlushFileBuffers. Si vous passez ce descripteur à une autre API Win32, l'erreur ERROR_ACCESS_DENIED est retournée. Le descripteur doit être fermé en le passant à l’API CloseHandle Win32 avant que la transaction soit validée ou restaurée. Si le descripteur n'est pas fermé, il peut y avoir des fuites de ressources côté serveur.

Vous devez effectuer tout accès au conteneur de données FILESTREAM dans une transaction SQL Server. Vous pouvez également exécuter des instructions Transact-SQL dans la même transaction. Cela maintient la cohérence entre les données SQL et les données BLOB FILESTREAM.

Pour accéder à l’objet BLOB FILESTREAM à l’aide de Win32, l’Autorisation Windows doit être activée.

Syntaxe

  
HANDLE OpenSqlFilestream (  
    LPCWSTR FilestreamPath,  
    SQL_FILESTREAM_DESIRED_ACCESS DesiredAccess,  
    ULONG OpenOptions,  
    LPBYTE FilestreamTransactionContext,  
    SIZE_T FilestreamTransactionContextLength,  
    PLARGE_INTEGER AllocationSize);  

Paramètres

FilestreamPath
[in] Est le chemin nvarchar(max) retourné par la fonction PathName . PathName doit être appelé à partir du contexte d’un compte qui dispose des autorisations SQL Server SELECT ou UPDATE sur la table et la colonne FILESTREAM.

DesiredAccess
[in] Définit le mode utilisé pour accéder aux données BLOB FILESTREAM. Cette valeur est passée à la fonction DeviceIoControl.

OpenOptions
[in] Les attributs et indicateurs de fichier. Ce paramètre peut également inclure toute combinaison des indicateurs suivants.

FilestreamTransactionContext
[in] Valeur renvoyée par la fonction GET_FILESTREAM_TRANSACTION_CONTEXT .

FilestreamTransactionContextLength
[in] Nombre d’octets dans les données varbinary(max) retournés par la fonction GET_FILESTREAM_TRANSACTION_CONTEXT. La fonction retourne un tableau de N octets. N est déterminé par la fonction et est une propriété du tableau d'octets retourné.

AllocationSize
[in] Spécifie la taille d'allocation initiale du fichier de données en octets. Cet indicateur est ignoré en mode lecture. Ce paramètre peut être NULL, auquel cas le comportement de système de fichiers par défaut est utilisé.

Valeur de retour

Si la fonction réussit, la valeur de retour est un descripteur ouvert vers un fichier spécifié. Si la fonction échoue, la valeur de retour est INVALID_HANDLE_VALUE. Pour obtenir plus d’informations sur les erreurs, appelez GetLastError().

Exemples

Les exemples suivants indiquent comment utiliser l'API OpenSqlFilestream pour obtenir un descripteur Win32.

using System.IO;
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace FILESTREAM
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection sqlConnection = new SqlConnection(
                "Integrated Security=true;server=(local)");

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;

            try
            {
                sqlConnection.Open();

                //The first task is to retrieve the file path
                //of the SQL FILESTREAM BLOB that we want to
                //access in the application.

                sqlCommand.CommandText =
                      "SELECT Chart.PathName()"
                    + " FROM Archive.dbo.Records"
                    + " WHERE SerialNumber = 3";

                String filePath = null;

                Object pathObj = sqlCommand.ExecuteScalar();
                if (DBNull.Value != pathObj)
                    filePath = (string)pathObj;
                else
                {
                    throw new System.Exception(
                        "Chart.PathName() failed"
                      + " to read the path name "
                      + " for the Chart column.");
                }

                //The next task is to obtain a transaction
                //context. All FILESTREAM BLOB operations
                //occur within a transaction context to
                //maintain data consistency.

                //All SQL FILESTREAM BLOB access must occur in 
                //a transaction. MARS-enabled connections
                //have specific rules for batch scoped transactions,
                //which the Transact-SQL BEGIN TRANSACTION statement
                //violates. To avoid this issue, client applications 
                //should use appropriate API facilities for transaction management, 
                //management, such as the SqlTransaction class.

                SqlTransaction transaction = sqlConnection.BeginTransaction("mainTranaction");
                sqlCommand.Transaction = transaction;

                sqlCommand.CommandText =
                    "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()";

                Object obj = sqlCommand.ExecuteScalar();
                byte[] txContext = (byte[])obj;

                //The next step is to obtain a handle that
                //can be passed to the Win32 FILE APIs.

                SqlFileStream sqlFileStream = new SqlFileStream(filePath, txContext, FileAccess.ReadWrite);

                byte[] buffer = new byte[512];

                int numBytes = 0;

                //Write the string, "EKG data." to the FILESTREAM BLOB.
                //In your application this string would be replaced with
                //the binary data that you want to write.

                string someData = "EKG data.";
                Encoding unicode = Encoding.GetEncoding(0);

                sqlFileStream.Write(unicode.GetBytes(someData.ToCharArray()),
                    0,
                    Encoding.GetBytes());

                //Read the data from the FILESTREAM
                //BLOB.

                sqlFileStream.Seek(0L, SeekOrigin.Begin);

                numBytes = sqlFileStream.Read(buffer, 0, buffer.Length);

                string readData = unicode.GetString(buffer);

                if (numBytes != 0)
                    Console.WriteLine(readData);

                //Because reading and writing are finished, FILESTREAM 
                //must be closed. This closes the c# FileStream class, 
                //but does not necessarily close the underlying 
                //FILESTREAM handle. 
                sqlFileStream.Close();

                //The final step is to commit or roll back the read and write
                //operations that were performed on the FILESTREAM BLOB.

                sqlCommand.Transaction.Commit();
            }
            catch (System.Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                sqlConnection.Close();
            }
            return;
        }
    }
}

Imports System.IO
Imports System 
Imports System.Collections.Generic 
Imports System.Text 
Imports System.Data 
Imports System.Data.SqlClient 
Imports System.Data.SqlTypes 

Module Module1
    Public Sub Main(ByVal args As String())
        '        Dim sqlConnection As New SqlConnection("Integrated Security=true;server=(local)")
        Dim sqlConnection As New SqlConnection("Integrated Security=true;server=kellyreyue\MSSQL1")

        Dim sqlCommand As New SqlCommand()
        sqlCommand.Connection = sqlConnection

        Try
            sqlConnection.Open()

            'The first task is to retrieve the file path 
            'of the SQL FILESTREAM BLOB that we want to 
            'access in the application. 

            sqlCommand.CommandText = "SELECT Chart.PathName()" + " FROM Archive.dbo.Records" + " WHERE SerialNumber = 3"

            Dim filePath As String = Nothing

            Dim pathObj As Object = sqlCommand.ExecuteScalar()
            If Not pathObj.Equals(DBNull.Value) Then
                filePath = DirectCast(pathObj, String)
            Else
                Throw New System.Exception("Chart.PathName() failed" + " to read the path name " + " for the Chart column.")
            End If

            'The next task is to obtain a transaction 
            'context. All FILESTREAM BLOB operations 
            'occur within a transaction context to 
            'maintain data consistency. 

            'All SQL FILESTREAM BLOB access must occur in 
            'a transaction. MARS-enabled connections 
            'have specific rules for batch scoped transactions, 
            'which the Transact-SQL BEGIN TRANSACTION statement 
            'violates. To avoid this issue, client applications 
            'should use appropriate API facilities for transaction management, 
            'management, such as the SqlTransaction class. 

            Dim transaction As SqlTransaction = sqlConnection.BeginTransaction("mainTranaction")
            sqlCommand.Transaction = transaction

            sqlCommand.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()"

            Dim obj As Object = sqlCommand.ExecuteScalar()
            Dim txContext As Byte() = Nothing

            Dim contextLength As UInteger

            If Not obj.Equals(DBNull.Value) Then
                txContext = DirectCast(obj, Byte())
                contextLength = txContext.Length()
            Else
                Dim message As String = "GET_FILESTREAM_TRANSACTION_CONTEXT() failed"
                Throw New System.Exception(message)
            End If

            'The next step is to obtain a handle that 
            'can be passed to the Win32 FILE APIs. 

            Dim sqlFileStream As New SqlFileStream(filePath, txContext, FileAccess.ReadWrite)

            Dim buffer As Byte() = New Byte(511) {}

            Dim numBytes As Integer = 0

            'Write the string, "EKG data." to the FILESTREAM BLOB. 
            'In your application this string would be replaced with 
            'the binary data that you want to write. 

            Dim someData As String = "EKG data."
            Dim unicode As Encoding = Encoding.GetEncoding(0)

            sqlFileStream.Write(unicode.GetBytes(someData.ToCharArray()), 0, Encoding.GetBytes())

            'Read the data from the FILESTREAM 
            'BLOB. 

            sqlFileStream.Seek(0, SeekOrigin.Begin)

            numBytes = sqlFileStream.Read(buffer, 0, buffer.Length)

            Dim readData As String = unicode.GetString(buffer)

            If numBytes <> 0 Then
                Console.WriteLine(readData)
            End If

            'Because reading and writing are finished, FILESTREAM 
            'must be closed. This closes the c# FileStream class, 
            'but does not necessarily close the underlying 
            'FILESTREAM handle. 
            sqlFileStream.Close()

            'The final step is to commit or roll back the read and write 
            'operations that were performed on the FILESTREAM BLOB. 

            sqlCommand.Transaction.Commit()
        Catch ex As System.Exception
            Console.WriteLine(ex.ToString())
        Finally
            sqlConnection.Close()
        End Try
        Return
    End Sub
End Module

#include <windows.h>
#include <sql.h>
#include<sqltypes.h>
#include<sqlext.h>
#include <stdio.h>
#include <msodbcsql.h>

#define COPYBUFFERSIZE 4096

/// <summary>
///This class iterates though the ODBC error queue and prints all of the
///accumulated error messages to the console.
/// </summary>

class ODBCErrors
{
private:
    int         m_iLine;    //Source code line on which the error occurred
    SQLSMALLINT m_type;     //Type of handle on which the error occurred
    SQLHANDLE   m_handle;   //ODBC handle on which the error occurred

public:
    /// <summary>
    ///Default constructor for the ODBCErrors class
    ///</summary>

    ODBCErrors()
    {
        m_iLine  = -1;
        m_type   = 0;
        m_handle = SQL_NULL_HANDLE;
    }

    /// <summary>
    ///Constructor for the ODBCErrors class
    /// </summary>
    /// <param name="iLine">
    /// This parameter is the source code line
    /// at which the error occurred.
    ///</param>
    /// <param name="type">
    /// This parameter is the type of ODBC handle passed in
    /// the next parameter.
    ///</param>
    /// <param name="handle">
    /// This parameter is the handle on which the error occurred.
    ///</param>

    ODBCErrors(int iLine, SQLSMALLINT type, SQLHANDLE handle)
    {
        m_iLine  = iLine;
        m_type   = type;
        m_handle = handle;
    }

    ///<summary>
    /// This method iterates though the error stack for the handle passed
    /// into the constructor and displays those errors on the console.
    ///</summary>

    void Print()
    {
        SQLSMALLINT i = 0, len = 0;
        SQLINTEGER  native;
        SQLTCHAR    state[9], text[256];
        SQLRETURN   sqlReturn = SQL_SUCCESS;

        if ( m_handle == SQL_NULL_HANDLE )
        {
            wprintf_s(TEXT("The error handle is not a valid handle.\n"), m_iLine);
            return;
        }

        wprintf_s(TEXT("Error Line(%d)\n"), m_iLine);

        while( sqlReturn == SQL_SUCCESS )
        {
            len = 0;

            sqlReturn = SQLGetDiagRec(
                m_type,
                m_handle,
                ++i,
                state,
                &native,
                text,
                sizeof(text)/sizeof(SQLTCHAR),
                &len);

            if ( SQL_SUCCEEDED(sqlReturn) )
                wprintf_s(TEXT("Error(%d, %ld, %s) : %s\n"), i, native, state, text);
        }
    }
};


BOOL CopyFileToSQL(LPTSTR srcFilePath, LPTSTR dstFilePath, LPBYTE transactionToken, SQLINTEGER cbTransactionToken)
{
    BOOL bRetCode = FALSE;

    HANDLE srcHandle = INVALID_HANDLE_VALUE;
    HANDLE dstHandle = INVALID_HANDLE_VALUE;
    BYTE   buffer[COPYBUFFERSIZE] = { 0 };

    TCHAR *szErrMsgSrc   = TEXT("Error opening source file.");
    TCHAR *szErrMsgDst   = TEXT("Error opening destFile file.");
    TCHAR *szErrMsgRead  = TEXT("Error reading source file.");
    TCHAR *szErrMsgWrite = TEXT("Error writing SQL file.");

    try
    {
        if ( (srcHandle = CreateFile(
            srcFilePath,
            GENERIC_READ,
            FILE_SHARE_READ,
            NULL,
            OPEN_EXISTING,
            FILE_FLAG_SEQUENTIAL_SCAN,
            NULL)) == INVALID_HANDLE_VALUE )
            throw szErrMsgSrc;

        if ( (dstHandle =  OpenSqlFilestream(
            dstFilePath,
            Write,
            0,
            transactionToken,
            cbTransactionToken,
            0)) == INVALID_HANDLE_VALUE)
            throw szErrMsgDst;

        DWORD bytesRead = 0;
        DWORD bytesWritten = 0;

        do
        {
            if ( ReadFile(srcHandle, buffer, COPYBUFFERSIZE, &bytesRead, NULL) == 0 )
                throw szErrMsgRead;

            if (bytesRead > 0)
            {
                if ( WriteFile(dstHandle, buffer, bytesRead, &bytesWritten, NULL) == 0 )
                    throw szErrMsgWrite;
            }
        } while (bytesRead > 0);

        bRetCode = TRUE;
    }
    catch( TCHAR *szErrMsg )
    {
        wprintf_s(szErrMsg);
        bRetCode = FALSE;
    }

    if ( srcHandle != INVALID_HANDLE_VALUE )
        CloseHandle(srcHandle);

    if ( dstHandle != INVALID_HANDLE_VALUE )
        CloseHandle(dstHandle);

    return bRetCode;
}

void main()
{
    TCHAR *sqlDBQuery =
       TEXT("INSERT INTO Archive.dbo.Records(Id, SerialNumber, Chart)")
       TEXT(" OUTPUT GET_FILESTREAM_TRANSACTION_CONTEXT(), inserted.Chart.PathName()")
       TEXT("VALUES (newid (), 5, CONVERT(VARBINARY, '**Temp**'))");

    SQLCHAR transactionToken[32];
    
    SQLHANDLE henv = SQL_NULL_HANDLE;
    SQLHANDLE hdbc              = SQL_NULL_HANDLE;
    SQLHANDLE hstmt             = SQL_NULL_HANDLE;

    try
    {
        //These statements Initialize ODBC for the client application and
        //connect to the database.

        if ( SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_ENV, henv);

        if ( SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3, NULL) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_ENV, henv);

        if ( SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_ENV, henv);

        //This code assumes that the dataset name "Sql Server FILESTREAM"
        //has been previously created on the client computer system. An
        //ODBC DSN is created with the ODBC Data Source item in
        //the Windows Control Panel.

        if ( SQLConnect(hdbc, TEXT("Sql Server FILESTREAM"),
                SQL_NTS, NULL, 0, NULL, 0) <= 0 )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_DBC, hdbc);

        //FILESTREAM requires that all read and write operations occur
        //within a transaction.
        if ( SQLSetConnectAttr(hdbc,
            SQL_ATTR_AUTOCOMMIT,
            (SQLPOINTER)SQL_AUTOCOMMIT_OFF,
            SQL_IS_UINTEGER) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_DBC, hdbc);

        if ( SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_DBC, hdbc);

        if ( SQLExecDirect(hstmt, sqlDBQuery, SQL_NTS) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_STMT, hstmt);

        //Retrieve the transaction token.
        if ( SQLFetch(hstmt) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_STMT, hstmt);

        SQLINTEGER cbTransactionToken = sizeof(transactionToken);

        if ( SQLGetData(hstmt, 1,
            SQL_C_BINARY,
            transactionToken,
            sizeof(transactionToken),
            &cbTransactionToken) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_STMT, hstmt);

        //Retrieve the file path for the inserted record.

        TCHAR dstFilePath[1024];
        SQLINTEGER cbDstFilePath;

        if ( SQLGetData(hstmt, 2, SQL_C_TCHAR, dstFilePath, sizeof(dstFilePath), &cbDstFilePath) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_STMT, hstmt);

        if ( SQLCloseCursor(hstmt) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_STMT, hstmt);

        SQLUSMALLINT mode = SQL_ROLLBACK;

        if ( CopyFileToSQL(
            TEXT("C:\\Users\\Data\\chart1.jpg"),
            dstFilePath,
            transactionToken,
            cbTransactionToken) == TRUE )
            mode = SQL_COMMIT;

        SQLTransact(henv, hdbc, mode);
    }
    catch(ODBCErrors *pErrors)
    {
        pErrors->Print();
        delete pErrors;
    }

    if ( hstmt != SQL_NULL_HANDLE )
        SQLFreeHandle(SQL_HANDLE_STMT, hstmt);

    if ( hdbc != SQL_NULL_HANDLE )
        SQLDisconnect(hdbc);

    if ( hdbc != SQL_NULL_HANDLE )
        SQLFreeHandle(SQL_HANDLE_DBC, hdbc); 

    if ( henv != SQL_NULL_HANDLE )
        SQLFreeHandle(SQL_HANDLE_ENV, henv);
}

Notes

Cet exemple a été écrit à l’origine pour le client natif SQL Server (sqlncli.h) mais a été mis à jour pour utiliser le pilote Microsoft OLE DB (msoledbsql.h) pour SQL Server. SQL Server Native Client (souvent abrégé en SNAC) a été supprimé dans SQL Server 2022 (16.x) et SQL Server Management Studio 19 (SSMS). Le fournisseur OLE DB pour SQL Server Native Client (SQLNCLI ou SQLNCLI11) et le fournisseur Microsoft OLE DB hérité pour SQL Server (SQLOLEDB) ne sont pas recommandés dans les nouveaux développements. Utilisez à la place le nouveau Microsoft OLE DB Driver (MSOLEDBSQL) pour SQL Server.

Voir aussi

Données blob (Binary Large Object) (SQL Server)
Effectuer des mises à jour partielles de données FILESTREAM
Éviter les conflits avec les opérations de base de données dans les applications FILESTREAM