Delphi FMX save blob to MSSQL

[jangbu]

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]

How do you get blob fields from the database?

[jangbu]

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]

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]

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

[Stellar]

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

[jangbu]

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]

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]

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]

Great, that works fine!
many thanks!!!

[Stellar]

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

[jimmyad07]

Thank you so much.
Its working in excellent way.
happy to be the part of forum.