sqlsql-servert-sqlfiletable

Get FileTable stream_id after creating image with File I/O Api


I tried inserting an image file into SQL Server FileTable directory with File I/O API. I need to get stream_id of inserted image and use it another table as a foreign key.

Can anyone help me, how can I get created image stream_id?


Solution

  • After creating the file, you'll need to query the file table using T-SQL with the newly created file path to get the stream_id. This is best done using the unique file table path_locator column.

    Below are example stored procedure and usage examples that get the unique stream_id of the file using either the relative or full UNC path. The path_locator of file tables has a unique constraint index so these queries are very efficient.

    CREATE DATABASE FileTableExample
        ON  PRIMARY 
        ( NAME = N'FileTableExample', FILENAME = N'D:\SqlFiles\FileTableExample.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB ), 
         FILEGROUP FileStreamFG CONTAINS FILESTREAM  DEFAULT
        ( NAME = N'FileTableExample_FileStream', FILENAME = N'D:\SqlFiles\FileTableExample_FileStreamFG' , MAXSIZE = UNLIMITED) 
         LOG ON 
        ( NAME = N'FileTableExample_log', FILENAME = N'D:\SqlFiles\FileTableExample_log.ldf' , SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB );
    GO
    
    ALTER DATABASE FileTableExample SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FileTableExample' ) 
    GO
    
    USE FileTableExample;
    GO
    
    CREATE TABLE [dbo].[Images] AS FILETABLE ON [PRIMARY] FILESTREAM_ON [FileStreamFG];
    WITH
    (
        FILETABLE_DIRECTORY = N'Images', FILETABLE_COLLATE_FILENAME = SQL_Latin1_General_CP1_CI_AS
    );
    GO
    
    CREATE PROC dbo.GetStreamIDByRelativePath
        @RelativeFilePath nvarchar(MAX)
    AS
    SELECT stream_id
    FROM dbo.Images
    WHERE path_locator = GetPathLocator(FileTableRootPath() + @RelativeFilePath);
    GO
    
    EXEC dbo.GetStreamIDByRelativePath @RelativeFilePath = N'\Images\YourImageFile.png';
    GO
    
    CREATE PROC dbo.GetStreamIDByFullUNCPath
        @FullUNCPath nvarchar(MAX)
    AS
    SELECT stream_id
    FROM dbo.Images
    WHERE path_locator = GetPathLocator(@FullUNCPath);
    GO
    
    EXEC dbo.GetStreamIDByFullUNCPath @FullUNCPath = N'\\YourSqlServer\YourFileStreamShare\YourDatabaseFileSteamDirectory\Images\YourImageFile.png';
    GO