VarBinary

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Valgardur
Posts: 164
Joined: Tue 24 Nov 2009 19:33

VarBinary

Post by Valgardur » Wed 13 May 2020 12:18

Hi, I am using SDAC for MSSQL Server, one field is VarBinary(8000), and I need to insert and retrieve data.. I have read all VarBinary threads, but (1) I could not find a clear explanation (2) my best guess does not work.

I am using DevExpress edit controls, a direct load save would be even better, but even manual does not work.

I am working with Delphi 10.3.3 and SDAC 9.1.3 - Windows 10 64-bit - and the database is Azure hosted.

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

Re: VarBinary

Post by Stellar » Fri 15 May 2020 09:38

If you just want to save the data to the Varbinary field, you should create an array of Variant and set it as the value for the field. For example:

Code: Select all

var
  Values: Variant;
begin
  Values := VarArrayCreate([0, 4], varByte);
  Values[0] := 9;
  Values[1] := 8;
  Values[2] := 7;
  Values[3] := 1;
//  ...
//  Values[n] := 10;

  MSQuery1.SQL.Text := 'SELECT * FROM Table_Name';
  MSQuery1.Open;
  MSQuery1.Append;
  MSQuery1.FieldByName('f_Varbinary').Value := Values;
  MSQuery1.Post;
end;
Refer to our documentation for information on using FILESTREAM:
https://www.devart.com/sdac/docs/work_filestream.htm

Valgardur
Posts: 164
Joined: Tue 24 Nov 2009 19:33

Re: VarBinary

Post by Valgardur » Mon 18 May 2020 15:29

thank you for your reply..

I am trying to insert a JPG file (with a picture) in this column.
As far as I can tell VarBinary is now recommended for this.
Filestream not an available datatype at Azure SQL

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

Re: VarBinary

Post by Stellar » Thu 21 May 2020 08:40

You can try handling VarBinary fields as regular BLOB fields. Note that you're trying to save the image to the field with fixed-length of 8,000 bytes. This size is insufficient for an image, therefore the image size may exceed the storage size. To prevent this, you may use VarBinary(max) fields.

Code: Select all

CREATE TABLE Table_Name (
  ID [int] IDENTITY(1,1) NOT NULL,
  f_Varbinary VarBinary(max),  
  PRIMARY KEY CLUSTERED (ID ASC)
);
Open

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin
  MSConnection1.Connect;
  MSQuery1.SQL.Text := 'SELECT * FROM Table_Name';
  MSQuery1.Open;
end;
Write

Code: Select all

procedure TForm1.Button2Click(Sender: TObject);
var
  Stream: TFileStream;
  Buf: TBytes;
begin
  Stream := TFileStream.Create('с:\Temp\Picture.jpg', fmOpenRead);
  try
    Stream.Position := 0;
    SetLength(Buf, Stream.Size);
    Stream.ReadBuffer(Buf, Stream.Size);

    MSQuery1.Append;
    MSQuery1.FieldByName('f_Varbinary').AsBytes := Buf;
    MSQuery1.Post;
  finally
    Stream.Free;
  end;
end;
Read

Code: Select all

procedure TForm1.Button3Click(Sender: TObject);
var
  Buf: TBytes;
  Stream: TMemoryStream;
begin
  Stream := TMemoryStream.Create;
  try
    Buf := MSQuery1.FieldByName('f_Varbinary').AsBytes;
    Stream.Write(Buf, Length(Buf));
    Stream.Position := 0;
    Image1.Picture.LoadFromStream(Stream);
  finally
    Stream.Free;
  end;
end;

Valgardur
Posts: 164
Joined: Tue 24 Nov 2009 19:33

Re: VarBinary

Post by Valgardur » Thu 21 May 2020 12:30

Thanks, yes of course the 8000 was not only unnecessary (from some example I copied and as I am testing with tiny pictures I thought it would not be relevant) - but the reason for my problems.

Thank you for your help.

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

Re: VarBinary

Post by Stellar » Mon 25 May 2020 05:50

Glad to see that the issue was resolved.
Feel free to contact us if you have any further questions about our products.

Post Reply