Olaf Doschke
Programmer
Talking about SQL Server 2017 FileTables.
I found a workaround to storing a file into a FileTable into a subdirectory of the FileStream share:
1. Store the file into root
2. update the path_location using the node.GetReparentedValue() method.
The file name also comes in as parameter, I just don't want to make this unclear with multiple question marks and specify a hardcoded name here, instead.
By the way this can also be used to relocate from one SubDir to another, you'll just need another hierarchyid variable for the initial path, 0x is simply always the root path.
I will not use the root directory for permanent file storage, all files will go into subdirectories, nevertheless for the general case I fear just relying on being able to store a file with its name into an empty root folder only containing directories is not the best thing to do, if you reuse a path_locator you violate uniqueness of PK.
Other ways I found precompute the path_locator (it would be easy if you could set the parent_path_locator, but that's a computed column). Such a solution seems overcomplicated to me, for example, see The basis is an existing directory 'MyFiles', then doing:
Not too much code, but because of NewID it can't be put into a sproc, I think it's a bit inconvenient. Also because it's white box code, making use of the knowledge about how path_locator values are represented in string format. that might change in later SQL Server versions.
Any idea how to make this more generic? You can only apply GetReparentedValue(0x, @finalpath) to an existing node in root, so even though my @finalpath is equal to @path and I don't need another path value but can use 0x, GetReparentedValue() doesn't work in an INSERT, you need a SELECT or UPDATE query with an existing path_locator value and thus the temporary storage into root, it seems.
Bye, Olaf.
I found a workaround to storing a file into a FileTable into a subdirectory of the FileStream share:
1. Store the file into root
2. update the path_location using the node.GetReparentedValue() method.
Code:
DECLARE @finalpath hierarchyid;
Use pmanage_filetest2;
SELECT @finalpath = path_locator from dbo.Documents where name = 'Subfolder' AND path_locator.GetLevel() = 1; -- sub folders are all at level 1 in my case.
Insert Into dbo.Documents (name, file_stream) VALUES ('Newfile.xyz', ?) -- here the file itself comes in via query parameter, that works fine.
UPDATE dbo.Documents SET path_locator = path_locator.GetReparentedValue(0x, @finalpath) WHERE name = 'Newfile.xyz' AND path_locator.GetLevel() = 1;By the way this can also be used to relocate from one SubDir to another, you'll just need another hierarchyid variable for the initial path, 0x is simply always the root path.
I will not use the root directory for permanent file storage, all files will go into subdirectories, nevertheless for the general case I fear just relying on being able to store a file with its name into an empty root folder only containing directories is not the best thing to do, if you reuse a path_locator you violate uniqueness of PK.
Other ways I found precompute the path_locator (it would be easy if you could set the parent_path_locator, but that's a computed column). Such a solution seems overcomplicated to me, for example, see The basis is an existing directory 'MyFiles', then doing:
Code:
DECLARE @path HIERARCHYID
DECLARE @new_path VARCHAR(675)
SELECT @path = path_locator
FROM dbo.Documents
WHERE name = 'MyFiles' -- AND path_locator.GetLevel() = 1
SELECT @new_path = @path.ToString() +
CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 1, 6))) + '.' +
CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 7, 6))) + '.' +
CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 13, 4))) + '/'
INSERT INTO dbo.Documents(name, file_stream, path_locator)
SELECT 'Notes2.txt', 0x, @new_path -- or whatever filename and filestream data, eg parametersNot too much code, but because of NewID it can't be put into a sproc, I think it's a bit inconvenient. Also because it's white box code, making use of the knowledge about how path_locator values are represented in string format. that might change in later SQL Server versions.
Any idea how to make this more generic? You can only apply GetReparentedValue(0x, @finalpath) to an existing node in root, so even though my @finalpath is equal to @path and I don't need another path value but can use 0x, GetReparentedValue() doesn't work in an INSERT, you need a SELECT or UPDATE query with an existing path_locator value and thus the temporary storage into root, it seems.
Bye, Olaf.
