FireDAC BLOB streaming with MS SQL Server

FireDacBlobStream.pas

unit FireDacBlobStream;

interface

// https://docwiki.embarcadero.com/RADStudio/Alexandria/en/Support_for_Blob_Streaming_in_FireDAC

uses
  Data.DB,
  FireDAC.Comp.Client,
  FireDAC.Phys.Intf,
  FireDAC.Stan.Param,
  System.Classes,
  System.SysUtils;

procedure DownloadBlobStream(const FileName: string; DestStream: TStream; Connection : TFDConnection);

implementation

procedure DownloadBlobStream(const FileName: string; DestStream: TStream; Connection : TFDConnection);
begin
  Connection.StartTransaction;
  try
    var Query := TFDQuery.Create(nil);
    try
      Query.SQL.Text := Format('SELECT :p = BulkColumn FROM OPENROWSET (BULK %s, SINGLE_BLOB) MyFile', [QuotedStr(FileName)]);
      Query.Params[0].ParamType := ptOutput;
      Query.Params[0].DataType  := ftOraBlob;  // ftOraBlob is correct, even we use MS SQL ...
      Query.Params[0].AsStream  := DestStream;
      Query.Command.CommandKind := skExecute;
      Query.Connection := Connection;
      Query.ExecSQL;

      Connection.Commit;
    finally
      FreeAndNil(Query);
      // DestStream is now free'd by FireDAC
    end;
  except
    Connection.Rollback;
    raise;
  end;
end;

end.