Before reading this document, please refer to the document https://blog.csdn.net/download/article/details/24374609

Environment: alicloud ECS SQL Server 2017 + Delphi7

It seems that xcopy, robocopy and other commands can not be used to migrate files. If you have any interested friends, let me know.

You can use T-SQL, but you need files on the server, which is a bit uncomfortable. As follows:

--We use this function to insert a picture file into the directory: the path here needs to be the path on the server.
declare @image1 varbinary(max), @path_locator hierarchyid;
select @image1=cast(bulkcolumn as varbinary(max)) from openrowset(bulk N'C:\1.png', single_blob) as x;
select @path_locator=path_locator from DocumentStores where [name]='MyDir1';
insert into DocumentStores(name, file_stream, path_locator) 
       values('1.png', @image1, dbo.fnGetNewPathLocator(newid(), @path_locator));

--If you want to use SQL Server Provided by itself hierarchyid Hierarchy, the following function may help you:
create FUNCTION fnGetNewPathLocator
        (@child uniqueidentifier, 
         @parent hierarchyid)
returns hierarchyid
as 
begin
  declare @ret hierarchyid, @binid Binary(16) = convert(binary(16), @child);
  select @ret=hierarchyid::Parse(
            COALESCE(@parent.ToString(), N'/') +
            CONVERT(nvarchar, CONVERT(bigint, SUBSTRING(@binId, 1, 6))) + N'.' +
            CONVERT(nvarchar, CONVERT(bigint, SUBSTRING(@binId, 7, 6))) + N'.' +
            CONVERT(nvarchar, CONVERT(bigint, SUBSTRING(@binId, 13, 4))) + N'/');
  return @ret;
end;

It can also be realized through the program, but if the level is too deep and the generated path ﹐ locator is too long, it always feels unreliable.

The following is the Delphi implementation, Insert operation (all files under the local E:\Doc directory are migrated to FileTable).

procedure TForm1.BitBtn9Click(Sender: TObject);
var
  lst, lstContent: TStrings;
  I: Integer;
  strSQL: string;
begin
  lst := TStringList.Create;
  lstContent := TStringList.Create;
  try
    GetFileStructureList('E:\Doc', lst);
    strSQL := EmptyStr;
    rzprogressbar1.TotalParts := lst.Count;
    for I:=0 to lst.Count-1 do
    begin
      SplitString(lst.Strings[I], '|', lstContent);
      if SameText(lstContent.Strings[0], '0') then      //catalog
        strSQL := strSQL + Format('Insert into DocumentStores(name, path_locator, is_directory, is_archive) values(%S, %S, %D, %D);',
                                   [QuotedStr(ExtractFileName(lstContent.Strings[1])), QuotedStr(lstContent.Strings[2]), 1, 0]) + #13#10
      else if SameText(lstContent.Strings[0], '1') then //file
        strSQL := strSQL + Format('Insert into DocumentStores(name, path_locator, file_stream) values(%S, %S, %S);',
                                   [QuotedStr(ExtractFileName(lstContent.Strings[1])), QuotedStr(lstContent.Strings[2]),
                                    StrToHex(BaseEncodeFile(lstContent.Strings[1]))]) + #13#10;
      rzprogressbar1.PartsComplete := rzprogressbar1.PartsComplete + 1;
      Application.ProcessMessages;
    end;
    try
      ADOConnection1.Connected := True;
      ADOConnection1.BeginTrans;
      ADOQuery1.SQL.Text := strSQL;
      ADOQuery1.ExecSQL;
      ADOConnection1.CommitTrans;
    except
      ADOConnection1.RollbackTrans;
    end;
  finally
    lst.Free;
    lstContent.Free;
  end;
end;

//Here is the common unit
unit U_Commfunc;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, EncdDecd, Contnrs;

  //generate filetable Used path_locator
  function GetPathLocator(root: Boolean=True): string;
  function GetGUID: string;
  function StrToHex(AStr: string): string;
  //File to character streaming
  function BaseEncodeFile(fn: TFileName): string;
  procedure SplitString(Source,Deli:string; var lst :TStrings);
  //Get directory+The list return value of files is the number of files, and the top level is the selected directory filetalbe For insertion
  function GetFileStructureList(Path: PChar; var lst: TStrings): LongInt;

implementation


function GetGUID: string;
var
  LTep: TGUID;
  sGUID: string;
begin
  CreateGUID(LTep);
  sGUID := GUIDToString(LTep);
  sGUID := StringReplace(sGUID, '-', '', [rfReplaceAll]);
  sGUID := Copy(sGUID, 2, Length(sGUID) - 2);
  Result := sGUID;
end;

function GetPathLocator(root: Boolean): string;
var
  //LocatorPath Three components of S1,S2,S3;
  sGuid, S1, S2, S3: string;
begin
  Result := '';
  if root then
    Result := '/';
  sGuid := GetGUID;
  S1 := IntToStr(StrToInt64(StrToHex(Copy(sGuid, 1, 6))));
  S2 := IntToStr(StrToInt64(StrToHex(Copy(sGuid, 7, 6))));
  S3 := IntToStr(StrToInt64(StrToHex(Copy(sGuid, 13, 4))));
  Result := Result + S1 + '.' + S2 + '.' + S3 + '/';
end;

function StrToHex(AStr: string): string;
var
  i : Integer;
  ch:char;
begin
  Result:='0x';
  for i:=1 to length(AStr)  do
  begin
    ch:=AStr[i];
    Result:=Result+IntToHex(Ord(ch),2);
  end;
end;

function BaseEncodeFile(fn: TFileName): string;
var
  ms: TMemoryStream;
  ss: TStringStream;
  str: string;
begin
  ms := TMemoryStream.Create;
  ss := TStringStream.Create('');
  try
    ms.LoadFromFile(fn);
    EncdDecd.EncodeStream(ms, ss);                       // take ms Content Base64 reach ss in
    str := ss.DataString;
    str := StringReplace(str, #13, '', [rfReplaceAll]);  // here ss The carriage return line feed is automatically added to the data in, so the carriage return line feed needs to be replaced with an empty character
    str := StringReplace(str, #10, '', [rfReplaceAll]);
    result := str;                                       // The return value is Base64 Of Stream
  finally
    FreeAndNil(ms);
    FreeAndNil(ss);
  end;
end;

procedure SplitString(Source,Deli:string; var lst :TStrings);
var
  EndOfCurrentString: Integer;
begin
  if  lst = nil then exit;
  lst.Clear;
  while Pos(Deli, Source)>0 do
  begin
    EndOfCurrentString := Pos(Deli, Source);
    lst.add(Copy(Source, 1, EndOfCurrentString - 1));
    Source := Copy(Source, EndOfCurrentString + length(Deli), length(Source) - EndOfCurrentString);
  end;
  lst.Add(source);
end;

function GetFileStructureList(Path: PChar; var lst: TStrings): LongInt;
var
  SearchRec: TSearchRec;
  Found: Integer;
  TmpStr, TmpLocator: string;
  CurDir, DirLocator: PChar;
  DirQue: TQueue;
  C: Cardinal;
begin
  Result := 0;
  if lst = nil then exit;
  dirQue := TQueue.Create;
  try
    CurDir := Path;
    DirLocator := PChar(GetPathLocator());
    lst.Add('0|'+CurDir+'|'+DirLocator);
    while CurDir <> nil do
    begin
      //Search for suffixes, such as: c:\*.*;
      TmpStr := IncludeTrailingPathDelimiter(curDir)+'*.*';
      Found := FindFirst(TmpStr, faAnyFile, SearchRec);
      while Found = 0 do
      begin
        C := GetFileAttributes(PChar(IncludeTrailingPathDelimiter(curDir) + SearchRec.Name));
        //if (searchRec.Attr and faDirectory)<>0 then  //There seems to be something wrong with this/
        if (C and FILE_ATTRIBUTE_DIRECTORY)<> 0 then
        begin
          if (SearchRec.Name <> '.') and (SearchRec.Name <> '..') then
          begin
            TmpStr := IncludeTrailingPathDelimiter(curDir)+SearchRec.Name;
            TmpLocator := GetPathLocator(False);
            TmpLocator := DirLocator + TmpLocator;
            lst.Add('0|'+TmpStr+'|'+TmpLocator);
            DirQue.Push(StrNew(PChar(TmpStr)));
            DirQue.Push(StrNew(PChar(TmpLocator)));
          end;
        end else begin
          Result:=Result+1;
          TmpLocator := GetPathLocator(False);
          TmpLocator := DirLocator + TmpLocator;
          lst.Add('1|'+IncludeTrailingPathDelimiter(curDir)+SearchRec.Name+'|'+TmpLocator);
        end;
        found:=FindNext(SearchRec);
      end;
      {After the current directory is found, if there is no data in the queue, it means that all are found;
      //Otherwise, there are subdirectories that have not been found. Take one and continue to find.}
      if DirQue.Count > 0 then
      begin
        CurDir := DirQue.Pop;
        DirLocator := DirQue.Pop;
      end else begin
        CurDir := nil;
        DirLocator := nil;
      end;
    end;
  finally
    dirQue.Free;
  end;
end;

end.

The renderings are as follows. There are 20 directories plus files in total.

Local folder E:\Doc:

FileTable virtual directory file Doc:

Data stored in database table: