mORMot Open Source

Bsaidus

Member

From: Algeria

Registered: 2018-12-16

Posts: 50

Delphi TClie'tDataset TBlobField and mORMot.

Hello.
I have a little problem updating a table in containing a BLOB field that container Image.
The scenario is :
    - Having mORMot http server.
    - client application containing TClientDataset dBGrid and controls that goes with.
  After posting a data to ClientDataset in Local, the function updates record in remote server, but the image in the blob field do not and application gives error "can not open resultset" .
So what is the mechanism to assign the content of blob field (image) into the SQL query.

Ex: the update statement is : update tmptable set tmpblob=? Where Id=?
  The values of "?" Are given from the values of ClientDataset fields.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Delphi TClie'tDataset TBlobField and mORMot.

How to you communicate between the client and the server?
Using a REST service? The ORM layer? A SynDBRemote access? A custom protocol?

Bsaidus

Member

From: Algeria

Registered: 2018-12-16

Posts: 50

Re: Delphi TClie'tDataset TBlobField and mORMot.

I use SynDBRemote.
I edit locally the record by adding an image to the blob field of ClientDataset then I save locally, after that I construct my SQL statement with "?" and execute it.
here is the code.

procedure TForm1.btnUpdateClick(Sender: TObject);
const
  sSQL = 'Update uc_blobt set Name=?, Obs=?, Pics=? where id=?';
var
  LField: Data.DB.TBlobField;
  LStrRaw: RawByteString ;
  Val: Variant;
begin
  Screen.Cursor := crHourGlass;
  cd1.DisableControls;
  try
    LField := cd1.FieldByName('Pics') as TBlobField;   // 'cd1' TClientDataset Field containig image file. ( locally)
    try
      LStrRaw := fGetRawStringFromBlob(LField);  // Encode countent of the blobfield into RawByteString
      gfP.ExecuteNoResult( sSQL, [ cd1Name.AsWideString, cd1Obs.AsWideString, LStrRaw, cd1id.AsInteger ] );
    except
      //Abort;
    end;
  finally
    cd1.EnableControls;
    Screen.Cursor := crDefault;
  end;

end;

function TForm1.fGetRawStringFromBlob(const AField: Data.db.TField): RawByteString;
var
 LMStream: TMemoryStream;
 LSPic: TSynPicture;
 LOutStr: RawByteString ;
begin
  LOutStr := '';
  LMStream := TMemoryStream.Create;
  LSPic := TSynPicture.Create;
  try
    try
      TBlobField(AField).SaveToStream(LMStream);
      LMStream.Position := 0;
      LSPic.LoadFromStream(LMStream);
      if LSPic.Empty then
        Exit;
      SaveAsRawByteString(LSPic, LOutStr, gptJPG);
      Result := LOutStr;
    except

    end;
  finally
    FreeAndNil(LMStream);
    FreeAndNil(LSPic);
  end;

end;

Last edited by Bsaidus (2022-04-15 20:04:25)

Bsaidus

Member

From: Algeria

Registered: 2018-12-16

Posts: 50

Re: Delphi TClie'tDataset TBlobField and mORMot.

Working with Blob database field is ennoying when it comes to store and retrieve images.
I think there is a bug in the SynGDIplus.pas unit ( or It is ma that do not understand the use ).
I use delphi xe2 with last updates, mORMot trunk from github and MariaDB10.3 as database server
( Note that with lazarus all works fine without doing ).

As I sayed above, I use the RemoteDB protocol to Insert/Update and visualise data in simple form.
  I use Standard DB Controls from Delphi ( DBGrid, DbEdit, DbImage, ... )
    I use TClientDataset component for local data.

  To update record on database I use :
   

	    // cd1 = ClientDataset.
		procedure TForm1.btnUpdateClick(Sender: TObject);
		const
	   	  sSQL = 'Update uc_blobt set Name=?, Obs=?, Pics=? where id=?';
		var
		  LField: Data.DB.TBlobField;
		  LStrRaw: RawByteString ;
		begin
		  Screen.Cursor := crHourGlass;
		  cd1.DisableControls;
		  try
		    //LField := cd1pics as TBlobField;
		    LField := cd1.FieldByName('Pics') as TBlobField;
		    try
		      LStrRaw := fGetRawStringFromBlob(LField);   // Pasting Bitmap into local TBlobField
		      // LStrRaw := fGetRawStringFromBlob(img1);
		      gfP.ExecuteNoResult( sSQL, [ cd1Name.AsWideString, cd1Obs.AsWideString, LStrRaw, cd1id.AsInteger ] );
		    except
		      //Abort;
		    end;
		  finally
		     cd1.EnableControls;
		     Screen.Cursor := crDefault;
		   end;
		end;
			
		// Return RawByteString from TImage Bitmap ( not DB control )
		function TForm1.fGetRawStringFromBlob(const AImage: TImage): RawByteString;
		begin
		  try
		    SaveAsRawByteString(AImage.Picture, Result, gptBMP);
		  except
		  end;
		end;

                // Return RawByteString from TField ( TBlobField ) associated to DBImage
		function TForm1.fGetRawStringFromBlob(const AField: Data.db.TField): RawByteString;
		var
		  LMStream: TMemoryStream;
		  LSPic: TSynPicture;
		begin
		  LMStream := TMemoryStream.Create;
		  LSPic := TSynPicture.Create;
		  try
		    try
		      TBlobField(AField).SaveToStream(LMStream);
		      LMStream.Position := 0;
		      LSPic.LoadFromStream(LMStream);
		      if LSPic.Empty then
		        Exit;
		      SaveAsRawByteString(LSPic, Result, gptBMP);
		    except
		    end;
		  finally
		    FreeAndNil(LMStream);
		    FreeAndNil(LSPic);
		  end;
	     end;			
	

   
   
   
    Effectivelly the Image (Blob) is Updated into database server, But when trying to retrieve this Image using select, into a TImage or
    TBlobField (TdbImage) an Stream read error Exception occures
    I Tried to do another way by using a procedure that uses mORMOt GDI functions :

		procedure TForm1.fRetrieveImageBinary;
		var
		  LRStr: RawByteString ;
   		  LMStream: TMemoryStream;
		  Lbmp: TBitmap;
		begin
		//  LPic := TSynPicture.Create;
		//  LMStream := TMemoryStream.Create;
		  Lbmp := TBitmap.Create;
		  try
		//    TblobField(cd1Pics).SaveToStream(LMStream);
		//    LRStr := StreamToRawByteString(cd1Pics.AsAnsiString);
		    Lbmp := LoadFromRawByteString(cd1Pics.AsAnsiString);
		    img1.Picture.Assign(Lbmp);
		 finally
		   Lbmp.Free;
		//    LMStream.Free;
		//    LPic.Free;
		 end;
		end;

But nothing work.
(PS: In lazarus I use

 TBlobField.Value 

into the update/insert and it works.

here is a binary representation of the footprint in database blob field.
https://pasteboard.co/25A1VPoIQh46.jpg

So do you have a solution for this
Thanks you .

Last edited by Bsaidus (2022-04-18 09:19:44)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Delphi TClie'tDataset TBlobField and mORMot.

You need to encode the blob with BinToBase64WithMagic() for such query.

Please read https://synopse.info/files/html/Synopse … ml#TITL_36 about parameter binding.

Bsaidus

Member

From: Algeria

Registered: 2018-12-16

Posts: 50

Re: Delphi TClie'tDataset TBlobField and mORMot.

Thank you AB.
It works well now.