How to load binary files to SQL Server?

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Ivan_Carpio89
Posts: 29
Joined: Mon 10 Dec 2018 23:49

How to load binary files to SQL Server?

Post by Ivan_Carpio89 » Sat 02 Feb 2019 02:28

Hello everyone. I'm migrating from FireDAC to UniDAC and I need to know all necessary steps to load binary files to SQL Server Database.

I currently do it this way on FireDAC using TFDQuery:

//Some code before ....
FDQuery1.SQL.Clear;
FDQuery1.Params.Clear;
FDQuery1.SQL.Add('INSERT INTO MY_TABLE VALUES (:pVarbinaryMaxField)');
FDQuery1.Params.CreateParam(ftBlob, 'pVarbinaryMaxField', ptInput).SetStream(MyMemoryStream, ftBlob);
FDQuery1.ExecSQL;
//Some code after......

With TFDQuery:

//Some code before.....
FDTable1.Append;
MyBlobStream := FDTable1.CreateBlobStream(FDTable1.FieldByName('VARBINARY_FIELD'), bmWrite);
MyBlobStream.CopyForm(MyMemoryStream, 0);
MyBlobStream.Free; //This is necessary on FireDAC
FDTable1.Post;
//Some code after......


Thanks for your help.

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: How to load binary files to SQL Server?

Post by Stellar » Tue 05 Feb 2019 14:19

To load binary data to a SQL Server database, you can use the following example:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var
  Query: TUniQuery;
  Stream: TFileStream;
begin
  Query := TUniQuery.Create(Self);
  try
    Query.Connection := UniConnection1;
    Query.SQL.Text := 'INSERT INTO TABLE_NAME (f_blob) VALUES (:p1)';

    Stream := TFileStream.Create('c:\Temp\123.jpg', fmOpenRead);
    try
      Query.ParamByName('p2').LoadFromStream(Stream, ftBlob); 
    finally
      Stream.Free;
    end;
    Query.Execute;
  finally
    Query.Free;
  end;

  Query := TUniQuery.Create(Self);
  try
    Query.Connection := UniConnection1;
    Query.SQL.Text := 'SELECT * FROM TABLE_NAME';
    Query.Open;

    Query.Append;
    Stream := TFileStream.Create('c:\Temp\123.jpg', fmOpenRead);
    try
      (Query.FieldByName('f_blob') as TBlobField).LoadFromStream(Stream);
    finally
      Stream.Free;
    end;
    Query.Post;
  finally
    Query.Free;
  end;
end;

Post Reply