Delphi FMX save blob to MSSQL

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
jangbu
Posts: 6
Joined: Sun 21 Jun 2020 09:31

Delphi FMX save blob to MSSQL

Post by jangbu » Sun 21 Jun 2020 09:53

Hi, I'm trying to save an Image taken from android smartphone camera to MSSQL. I'm using Delphi 10.3.3. and SDAC 9.2.4.
The record will be stored in DB wihout error. But when I try to read it from DB I get this message: "not supported stream format". The same code used in VCL application on windows PC an internal camera and Ado-Query works fine!

How can I store an camera Image to MSSQL-DB in Delphi FMX application running at Android smartphone?


Code: Select all

procedure TfrmMain.SaveImage_in_DB(Sender: TObject);
var MStream: TMemoryStream;
  sql_str : string;
  _rec_cnt : integer;
begin

  MStream := TMemoryStream.Create;
  try

    ADOQuery1.Close;
    ADOQuery1.SQL.Clear;
    sql_str := 'select * from sign';
    ADOQuery1.SQL.Add(sql_str);
    ADOQuery1.Open;

    if ADOQuery1.RecordCount > 0 then
      _rec_cnt := ADOQuery1.RecordCount;

    MStream.Position := 0;

    //  Image1.Bitmap.SaveToStream(MStream);  -> FMX-Variante !!!!
    //  Image1.Picture.SaveToStream(MStream); -> VCL-Variante !!!!

    imgProfile.Picture.SaveToStream(MStream);
    MStream.Position := 0;

    ADOQuery1.Append;

    ADOQuery1.FieldByName('ID').AsInteger := _rec_cnt + 1;
    ADOQuery1.FieldByName('DateTime').AsDateTime := now;
    TBlobField(ADOQuery1.FieldByName('image1')).LoadFromStream(MStream);

    ADOQuery1.Post;
    ADOQuery1.Close;
  except
    on E: Exception do
    begin
      ShowMessage('Error: ' + E.ClassName + ' ' + E.Message);
    end;
  end;
  MStream.Free;
end;
jangbu

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

Re: Delphi FMX save blob to MSSQL

Post by Stellar » Mon 22 Jun 2020 14:17

How do you get blob fields from the database?

jangbu
Posts: 6
Joined: Sun 21 Jun 2020 09:31

Re: Delphi FMX save blob to MSSQL

Post by jangbu » Mon 22 Jun 2020 19:03

I'm using this (VCL) code in my windows application to read stored blob fileds (photos) from DB.
In that windows VCL application I'm using ADOConnection, AdoQuery, DataSource, DBImage to read Data from DB.

Code: Select all

procedure TForm3.btn_selectClick(Sender: TObject);
var sql_str : string;
begin
  AdoQuery1.Close;
  AdoQuery1.SQL.Clear;
  sql_str := 'select * from sign where id = ''' + edt_id.Text + '''';
  AdoQuery1.SQL.Add(sql_str);
    AdoQuery1.Open;
  if AdoQuery1.RecordCount = 0 then
    ShowMessage('id not fond');
end;
Is my FMX code to store Blobfiled in DB (Android) ok?

Who can I store blob fields in MS-SQL with FMX application on android device by using DEVART SDAC (9.2.4) components?

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

Re: Delphi FMX save blob to MSSQL

Post by Stellar » Tue 23 Jun 2020 06:47

Unfortunately, we can't reproduce the issue. To investigate this behavior of SDAC, please compose a small sample demonstrating the issue and send it to us, including database objects creating scripts.
You can send the sample using the contact form at our site: devart.com/company/contactform.html

jangbu
Posts: 6
Joined: Sun 21 Jun 2020 09:31

Re: Delphi FMX save blob to MSSQL

Post by jangbu » Wed 24 Jun 2020 16:45

your site: devart.com/company/contactform.html is not reachable:
-> "We are sorry, the page you requested cannot be found"?
jangbu

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

Re: Delphi FMX save blob to MSSQL

Post by Stellar » Fri 26 Jun 2020 12:17

Please try to open the page again:
https://www.devart.com/company/contactform.html

jangbu
Posts: 6
Joined: Sun 21 Jun 2020 09:31

Re: Delphi FMX save blob to MSSQL

Post by jangbu » Sat 27 Jun 2020 14:47

when I open your link at first i have to log in to devart forum with my account but at next I get the message from your website: https://www.devart.com/company/contactform.html

We are sorry, the page you requested cannot be found

Where can i send my documented sample to?

jangbu
Posts: 6
Joined: Sun 21 Jun 2020 09:31

Re: Delphi FMX save blob to MSSQL

Post by jangbu » Mon 29 Jun 2020 13:06

I got a bit further.
With FMX + SDAC stored images as blob field can be read back from DB only with FMX application using this code:

Code: Select all

procedure TForm2.btn_save_to_DBClick(Sender: TObject);
var
  MStream : TMemoryStream;
begin
  MStream := TMemoryStream.Create;
  try
    MSQuery1.SQL.Text := 'INSERT INTO sign (ID,image1,DateTime,ok) VALUES (:ID,:image1,:DateTime,:ok)';
    Image1.Bitmap.SaveToStream(MStream)
    MStream.Seek(0,0);
    try
      MSQuery1.ParamByName('ID').AsInteger := 109;
      MSQuery1.ParamByName('image1').LoadFromStream(MStream, ftBlob);
      MSQuery1.ParamByName('DateTime').AsDateTime := now;
      MSQuery1.ParamByName('ok').AsBoolean := false;
      MSQuery1.ExecSQL();
    except
      on E: Exception do
        ShowMessage('Error on ExecSQL! ' + e.Message);
    end;
    MStream.Free;
  finally
    MSQuery1.Close;
  end;
end;

procedure TForm2.btn_read_from_DBClick(Sender: TObject);
var BlobStream : TStream;
begin
  if not MSConnection1.Connected then
    MSConnection1.Connected := true;
    try
      MSQuery1.Close;
      MSQuery1.SQL.Clear;
      MSQuery1.SQL.Add('select * from sign where id = ''' + edt_id.Text + '''');
      MSQuery1.Open;
      BlobStream := MSQuery1.CreateBlobStream(MSQuery1.FieldByName('image1'),TBlobStreamMode.bmRead);

      Image3.Bitmap.LoadFromStream(BlobStream); // FMX version

      BlobStream.Free;
    except
      on E: Exception do
        ShowMessage('Fehler bei lesen blob from DB ' + e.Message);
    end;
end;
But when I'm using this windows-VLC application code to read the blob field from back from DB, I get the message:
"DB Bitmap is invalid"

Code: Select all

procedure TfrmMain.btn_read_from_DBClick(Sender: TObject);
var BlobStream : TStream;
begin
  if not MSConnection1.Connected then
    MSConnection1.Connected := true;
    try
      MSQuery1.Close;
      MSQuery1.SQL.Clear;
      MSQuery1.SQL.Add('select * from sign where id = ''' + edt_id.Text + '''');
      MSQuery1.Open;
      BlobStream := MSQuery1.CreateBlobStream(MSQuery1.FieldByName('image1'),TBlobStreamMode.bmRead);

      Image2.Picture.Bitmap.LoadFromStream(BlobStream);  // VCL version

      BlobStream.Free;
    except
      on E: Exception do
        ShowMessage('Fehler bei lesen blob from DB ' + e.Message);
    end;
end;
What's the problem to read blob fields (images) back from DB with windows VCL application, that has been stored in DB by FMX android application?
jangbu

paweld
Posts: 13
Joined: Mon 29 Sep 2014 08:56

Re: Delphi FMX save blob to MSSQL

Post by paweld » Mon 29 Jun 2020 13:45

FMX.Image.Bitmap is saved as a PNG image, so you must read as png in the VCL application:

Code: Select all

uses
  pngimage;
  
procedure TfrmMain.btn_read_from_DBClick(Sender: TObject);
var 
  BlobStream : TStream;
  png: TPngImage;
begin
  if not MSConnection1.Connected then
    MSConnection1.Connected := true;
    try
      png: TPngImage.Create;
      MSQuery1.Close;
      MSQuery1.SQL.Clear;
      MSQuery1.SQL.Add('select * from sign where id = ''' + edt_id.Text + '''');
      MSQuery1.Open;
      BlobStream := MSQuery1.CreateBlobStream(MSQuery1.FieldByName('image1'),TBlobStreamMode.bmRead);
      BlobStream.Position:=0;
      png.LoadFromStream(BlobStream);  // VCL version
      Image2.Picture.Graphic:=png;
      png.Free;
      BlobStream.Free;
    except
      on E: Exception do
        ShowMessage('Fehler bei lesen blob from DB ' + e.Message);
    end;
end;

jangbu
Posts: 6
Joined: Sun 21 Jun 2020 09:31

Re: Delphi FMX save blob to MSSQL

Post by jangbu » Mon 29 Jun 2020 16:20

Great, that works fine!
many thanks!!!

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

Re: Delphi FMX save blob to MSSQL

Post by Stellar » Tue 30 Jun 2020 10:06

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

Post Reply