I'm trying to add stream_id from SQL Server FileTable into the stream_id column in category table using the second insert statement in the code below. I am following the code of Dan but after the stored procedure runs, stream_id column is still NULL. I do not know if the reason is that the value I get from GetPathLocator() function does not equal the path_locator column in the FileTable or there is something wrong with the stored procedure. Here is an image of the queried FileTable.
This is the stored procedure code:
ALTER PROCEDURE [dbo].[SpCategory_AddCategory]
@CategoryName nvarchar(100),
@RelativeFilePath nvarchar(MAX),
@Username nvarchar(100),
@FileName nvarchar(1000),
@Id int = 0 output
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql nvarchar(max),
@attributes nvarchar(10),
@tablename nvarchar(50),
@filenameStr nvarchar(1000),
@relativeFP nvarchar(MAX)
SET @filenameStr = @FileName
SET @relativeFP = @RelativeFilePath;
SET @attributes = N' * '
SET @tablename = N'FileData '
SET @sql = N'SELECT ' + CHAR(39) + @filenameStr + CHAR(39) + N', ' + @attributes +
N' FROM ' + N'OPENROWSET(BULK ' + CHAR(39)+ @relativeFP + CHAR(39) + ', SINGLE_BLOB)' + N' AS ' + @tablename
INSERT INTO [dbo].[LibraryTable] ([name],[file_stream])
EXEC(@sql)
DECLARE @FileTableRoot varchar(1000);
SELECT @FileTableRoot = FileTableRootPath('dbo.LibraryTable');
INSERT INTO dbo.[Category] (categoryName, stream_id, userId)
VALUES(@CategoryName, (SELECT stream_id
FROM dbo.LibraryTable
WHERE path_locator = GetPathLocator(CONCAT(@FileTableRoot, @relativeFP))) , (SELECT id FROM dbo.[User] WHERE dbo.[User].username = @Username))
Select @Id = SCOPE_IDENTITY();
END
This is the execution of the stored procedure:
USE [LibraryMS]
GO
DECLARE @return_value int,
@Id int
EXEC @return_value = [dbo].[SpCategory_AddCategory]
@CategoryName = N'Encyclopedia',
@RelativeFilePath = N'C:\Users\mahmo\Desktop\NTNU_Backup\Code_Exercises\c_sharp_exercises\PROJECT_library_management_system\Images\Images\Encyclopedias.jpg',
@Username = N'mamo_00',
@FileName = N'Encyclopedias.jpg',
@Id = @Id OUTPUT
SELECT @Id as N'@Id'
SELECT 'Return Value' = @return_value
GO
I managed to fix my problem while i was in the process of dismantling my stored procedure to see exactly why it did not work. What i did was creating two additional stored procedures, one to handle loading files into the FileTable and one to insert the data into the Category table. I did this to see if any of those part where fault in my stored procedure not working. But while in the process of doing this my [dbo].[SpCategory_AddCategory] actually worked.
In conlusion i can not say why worked, it just did.
This is the main stored procedure that handles both inserting files into FileTable and inserting into Category table.
ALTER PROCEDURE [dbo].[SpCategory_AddCategory]
@CategoryName nvarchar(100),
@Username nvarchar(100),
@RelativeFilePath nvarchar(MAX),
@FileName nvarchar(1000)
AS
BEGIN
SET NOCOUNT ON;
EXEC dbo.spLibraryTable_LoadFilesIntoFT @RelativeFilePath = @RelativeFilePath, @FileName = @FileName;
------------------------------------------------------------------------------------------------------------------
DECLARE @stream_id uniqueidentifier;
DECLARE @FileTableRoot varchar(1000);
DECLARE @fullpath nvarchar(1000);
DECLARE @Id int;
SELECT @FileTableRoot = FileTableRootPath();
SELECT @fullpath = @FileTableRoot + ft.file_stream.GetFileNamespacePath()
FROM [dbo].LibraryTable AS ft
WHERE name = @FileName;
SET @stream_id = (SELECT stream_id FROM dbo.LibraryTable WHERE path_locator = GETPATHLOCATOR(@fullpath));
EXEC dbo.spCategory_InsertIntoCategory @stream_id, @fullpath, @Username, @CategoryName, @Id = @Id OUTPUT
END
Here is dbo.spLibraryTable_LoadFilesIntoFT
ALTER PROCEDURE [dbo].[spLibraryTable_LoadFilesIntoFT]
@RelativeFilePath nvarchar(MAX),
@FileName nvarchar(1000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql nvarchar(max),
@attributes nvarchar(10),
@tablename nvarchar(50),
@filenameStr nvarchar(1000),
@relativeFP nvarchar(MAX)
SET @filenameStr = @FileName;
SET @relativeFP = @RelativeFilePath;
SET @attributes = N' * ';
SET @tablename = N'FileData ';
SET @sql = N'SELECT ' + CHAR(39) + @filenameStr + CHAR(39) + N', ' + @attributes +
N' FROM ' + N'OPENROWSET(BULK ' + CHAR(39)+ @relativeFP + CHAR(39) + ', SINGLE_BLOB)' + N' AS ' + @tablename;
INSERT INTO [dbo].[LibraryTable] ([name],[file_stream])
EXEC(@sql);
END
Here is dbo.spCategory_InsertIntoCategory
ALTER PROCEDURE [dbo].[spCategory_InsertIntoCategory]
@stream_id uniqueidentifier,
@fullpath nvarchar(1000),
@Username nvarchar(100),
@CategoryName nvarchar(100),
@Id int = 0 output
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.[Category] (categoryName, stream_id, userId)
VALUES(@CategoryName, @stream_id , (SELECT id FROM dbo.[User] WHERE dbo.[User].username = @Username));
Select @Id = SCOPE_IDENTITY();
END