Page 1 of 1
VarBinary
Posted: Wed 13 May 2020 12:18
by 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.
Re: VarBinary
Posted: Fri 15 May 2020 09:38
by 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
Re: VarBinary
Posted: Mon 18 May 2020 15:29
by 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
Re: VarBinary
Posted: Thu 21 May 2020 08:40
by 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;
Re: VarBinary
Posted: Thu 21 May 2020 12:30
by 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.
Re: VarBinary
Posted: Mon 25 May 2020 05:50
by Stellar
Glad to see that the issue was resolved.
Feel free to contact us if you have any further questions about our products.