FILESTREAM データ用のクライアントアプリケーションの作成

[アーティクル]
05/23/2023

適用対象: SQL Server

Win32 API を使って、FILESTREAM BLOB のデータの読み書きができます。次の手順を実行する必要があります。

FILESTREAM ファイルのパスを読み取ります。
現在のトランザクションコンテキストを読み取ります。
Win32 ハンドルを取得し、そのハンドルを使用して FILESTREAM BLOB に対してデータの読み書きを行います。

Note

このトピックの例を実行するには、「 FILESTREAM が有効なデータベースを作成する方法」および「 FILESTREAM データを格納するテーブルを作成する方法」に基づいて、FILESTREAM が有効なデータベースとテーブルを作成する必要があります。

FILESTREAM データを操作するための関数

FILESTREAM を使用してバイナリラージオブジェクト (BLOB) データを格納すると、Win32 API を使用してそのファイルを操作できます。 Win32 アプリケーションで FILESTREAM BLOB データを操作できるようにするために、SQL Server には次の関数と API が用意されています。

PathName 。BLOB へのパスをトークンとして返します。アプリケーションでは、このトークンを使用して Win32 ハンドルを取得し、BLOB データを操作します。

FILESTREAM データを含むデータベースが Always On 可用性グループに属する場合、PathName 関数はコンピューター名ではなく仮想ネットワーク名 (VNN) を返します。
GET_FILESTREAM_TRANSACTION_CONTEXT() は、セッションの現在のトランザクションを表すトークンを返します。アプリケーションでは、このトークンを使用して、FILESTREAM のファイルシステムストリーミング操作をトランザクションにバインドします。
OpenSqlFilestream API 。Win32 ファイルハンドルを取得します。アプリケーションでは、このハンドルを使って FILESTREAM データをストリーミングして、Win32 API の ReadFile、 WriteFile、 TransmitFile、 SetFilePointer、 SetEndOfFile、 FlushFileBuffersにハンドルを渡すことができます。このハンドルを使用してその他の API を呼び出すと、ERROR_ACCESS_DENIED エラーが返されます。ハンドルは、 CloseHandleを使用して閉じる必要があります。

すべての FILESTREAM データコンテナーアクセスは、SQL Server トランザクションで実行されます。 Transact-SQL ステートメントは、SQL データと FILESTREAM データの一貫性を維持するため、同じトランザクション内で実行することができます。

FILESTREAM データにアクセスするための手順

FILESTREAM ファイルのパスの読み取り

FILESTREAM テーブルの各セルには、ファイルパスが関連付けられています。パスを読み取るには、Transact-SQL ステートメントで varbinary(max) 列の PathName プロパティを使用します。 varbinary(max) 列のファイルパスを読み取る方法を次の例に示します。

DECLARE @filePath VARCHAR(MAX);

SELECT @filePath = Chart.PathName()
FROM Archive.dbo.Records
WHERE SerialNumber = 3;

PRINT @filepath;

トランザクションコンテキストの読み取り

現在のトランザクションコンテキストを取得するには、Transact-SQLの GET_FILESTREAM_TRANSACTION_CONTEXT() 関数を使用します。トランザクションを開始して現在のトランザクションコンテキストを読み取る方法を次の例に示します。

DECLARE @txContext VARBINARY(MAX);

BEGIN TRANSACTION;
SELECT @txContext = GET_FILESTREAM_TRANSACTION_CONTEXT();
PRINT @txContext;
COMMIT;

Win32 ファイルハンドルの取得

Win32 ファイルハンドルを取得するには、OpenSqlFilestream API を呼び出します。この API は、sqlncli.dll ファイルからエクスポートされます。返されるハンドルは、Win32 API の ReadFile、 WriteFile、 TransmitFile、 SetFilePointer、 SetEndOfFile、 FlushFileBuffersに渡すことができます。 Win32 ファイルハンドルを取得し、これを使用して FILESTREAM BLOB に対してデータを読み書きする方法を次の例に示します。

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,
                    someData.Length);

                //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, someData.Length)

            '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);
}

アプリケーションの設計と実装のベストプラクティス

FILESTREAM を使用するアプリケーションの設計と実装を行う場合は、次のガイドラインを考慮してください。
初期化されていない FILESTREAM 列を表すには、0x の代わりに NULL を使用します。 0x 値を使用するとファイルが作成されますが、NULL の場合はファイルが作成されません。
null でない FILESTREAM 列を含むテーブルでは、挿入操作と削除操作を行わないでください。挿入操作および削除操作を行うと、ガベージコレクション用の FILESTREAM テーブルが変更される場合があります。その結果、アプリケーションパフォーマンスが時間と共に低下する可能性があります。
レプリケーションを使用するアプリケーションでは、NEWID() の代わりに NEWSEQUENTIALID() を使用します。このようなアプリケーションでは、GUID の生成に関して NEWID() よりも NEWSEQUENTIALID() の方が高いパフォーマンスを発揮します。
FILESTREAM API は、データへの Win32 ストリーミングアクセス向けに設計されています。 Transact-SQL を使用して 2 MB を超える FILESTREAM バイナリラージオブジェクト (BLOB) の読み取り/書き込みを行わないでください。 Transact-SQL から BLOB データの読み取り/書き込みを行う必要がある場合は、Win32 から FILESTREAM BLOB を開く前にすべての BLOB データを必ず使用してください。すべての Transact-SQL データが使用されていない場合、後続の FILESTREAM を開く操作または閉じる操作が失敗することがあります。
FILESTREAM BLOB の更新や FILESTREAM BLOB の後または前にデータを追加する Transact-SQL ステートメントは使用しないようにします。そのような操作を行うと、BLOB データが tempdb データベースにスプールされ、新しい物理的ファイルに戻される可能性があります。
小さな BLOB 更新を FILESTREAM BLOB に追加しないようにします。追加操作ごとに、基になる FILESTREAM ファイルがコピーされます。アプリケーションで小さな BLOB を書き込む必要がある場合は、BLOB を varbinary(max) 列に書き込んでおき、BLOB の数があらかじめ設定された制限に達したときに FILESTREAM BLOB に対して 1 回の書き込み操作を行います。
アプリケーションで大量の BLOB ファイルのデータ長を取得しないようにします。サイズは SQL Server データベースエンジンに格納されないため、この操作を実行するには多くの時間が必要になります。 BLOB ファイルの長さを確認する必要がある場合は、Transact-SQL DATALENGTH() 関数を使用して BLOB のサイズを確認します (BLOB が閉じている場合)。 DATALENGTH() でサイズを取得する際には、BLOB ファイルは開かれません。
アプリケーションで Message Block1 (SMB1) プロトコルを使用する場合、60 KB 単位で FILESTREAM BLOB データを読み取ると、パフォーマンスが最適化されます。

参照

FILESTREAM アプリケーションでのデータベース操作との競合の回避
 OpenSqlFilestream による FILESTREAM データへのアクセス
 バイナリラージオブジェクト (Blob) データ (SQLServer)
FILESTREAM データの部分的な更新

次の方法で共有

FILESTREAM データ用のクライアントアプリケーションの作成

FILESTREAM データを操作するための関数

FILESTREAM データにアクセスするための手順

FILESTREAM ファイルのパスの読み取り

トランザクションコンテキストの読み取り

Win32 ファイルハンドルの取得

アプリケーションの設計と実装のベストプラクティス

参照

フィードバック

その他のリソース

次の方法で共有

FILESTREAM データ用のクライアント アプリケーションの作成

FILESTREAM データを操作するための関数

FILESTREAM データにアクセスするための手順

FILESTREAM ファイルのパスの読み取り

トランザクション コンテキストの読み取り

Win32 ファイル ハンドルの取得

アプリケーションの設計と実装のベスト プラクティス

参照

フィードバック

その他のリソース

FILESTREAM データ用のクライアントアプリケーションの作成

トランザクションコンテキストの読み取り

Win32 ファイルハンドルの取得

アプリケーションの設計と実装のベストプラクティス