VarBinary

[Valgardur]

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]

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]

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]

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]

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]

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