Recent

Author Topic: [SOLVED] How to display image from DB in DBGrid?  (Read 850 times)

Vodnik

  • Full Member
  • ***
  • Posts: 138
[SOLVED] How to display image from DB in DBGrid?
« on: September 24, 2019, 09:57:29 pm »
USERS table in Informix DB that I'm working with have AVATAR column.
It is of type BYTE and size 2147483647.
Attached is screenshot how WinSQL shows it as "Raw data" (other tabs do not work).
Being saved to file, it successfully opens as BMP.
...Well, it would be nice to show this avatar picture in DBGrid.
Following discussion provides a solution for displaying external files:
https://forum.lazarus.freepascal.org/index.php?topic=43467.0
But how to display image from DB in DBGrid?
(I have no graphic experience yet)
« Last Edit: October 02, 2019, 09:46:31 am by Vodnik »

winni

  • Hero Member
  • *****
  • Posts: 1158
Re: How to display image from DB in DBGrid?
« Reply #1 on: September 24, 2019, 10:55:40 pm »
Hi!

The simple and slowest solution is this way:

Save your raw data to a temporary file
Create a TBitmap
Load the file into this bitmap
Compute your destination rectangle on the canvas of the DBGrid
Do a Bitmap.Stretchdraw on the DBGrid.canvas
Free the bitmap
delete the temporary file
done

For a faster handling there might be a way to get the raw data into a memorystream and put it into the bitmap. But I have no idea how to do.

Winni

paweld

  • Full Member
  • ***
  • Posts: 190
Re: How to display image from DB in DBGrid?
« Reply #2 on: September 25, 2019, 06:11:50 am »
Code: Pascal  [Select]
  1. procedure TForm1.DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect;
  2.   DataCol: Integer; Column: TColumn; State: TGridDrawState);
  3. var
  4.   pic: TPicture;
  5.   foto : TMemoryStream;
  6. begin
  7.   if Column.Index=2 then
  8.   begin
  9.     pic:=TPicture.Create;
  10.         foto:=TMemoryStream.Create;
  11.     try
  12.           foto.CopyFrom(SQLQuery1.CreateBlobStream(SQLQuery1.FieldByName('AVATAR'), bmRead), 0);
  13.       pic.LoadFromStream(foto);
  14.       DBGrid1.Canvas.Draw(Rect.left, Rect.Top, pic);
  15.     finally
  16.       pic.Free;
  17.           foto.Free;
  18.     end;
  19.   end;
  20. end;
Best regards
paweld

Vodnik

  • Full Member
  • ***
  • Posts: 138
Re: How to display image from DB in DBGrid?
« Reply #3 on: September 30, 2019, 05:14:51 pm »
@paweld, thank you for the idea!

Though I didn't succeeded with it yet:

1. I replaced TPicture with TBitmap, because DBGridUsers.Canvas.Draw expects TGraphic, not TPicture:

Code: Pascal  [Select]
  1. procedure TMainForm.DBGridUsersDrawColumnCell(Sender: TObject;
  2.   const Rect: TRect; DataCol: Integer; Column: TColumn; State: TGridDrawState);
  3. var
  4.   bmp: TBitmap;
  5.   foto : TMemoryStream;
  6. begin
  7.   if Column.FieldName='avatar' then
  8.   begin
  9.     foto:=TMemoryStream.Create;
  10.     bmp:=TBitmap.Create;
  11.     try
  12.       foto.CopyFrom(SQLQuery.CreateBlobStream(SQLQuery.FieldByName('avatar'), bmRead), 0);
  13.       bmp.LoadFromStream(foto);
  14.       DBGridUsers.Canvas.Draw(Rect.left, Rect.Top, bmp);
  15.     finally
  16.       bmp.Free;
  17.       foto.Free;
  18.     end;
  19.   end;
  20. end;
  21.  

2. This function call raises exception class 'External: SIGSEGV':

foto.CopyFrom(SQLQuery.CreateBlobStream(SQLQuery.FieldByName('avatar'), bmRead), 0);

What may be the reason for that?

Vodnik

  • Full Member
  • ***
  • Posts: 138
Re: How to display image from DB in DBGrid?
« Reply #4 on: September 30, 2019, 08:18:08 pm »
Well, from docs:
https://www.freepascal.org/docs-html/fcl/db/tdataset.createblobstream.html


CreateBlobStream is not intended for use by application programmers. It creates a stream object which can be used to read or write data from a blob field. Instead, application programmers should use the TBlobField.LoadFromStream and TBlobField.SaveToStream methods when reading and writing data from/to BLOB fields


Do somebody have a very simple example of using these functions?

GAN

  • Sr. Member
  • ****
  • Posts: 274
Re: How to display image from DB in DBGrid?
« Reply #5 on: September 30, 2019, 09:27:32 pm »
Well, from docs:
https://www.freepascal.org/docs-html/fcl/db/tdataset.createblobstream.html


CreateBlobStream is not intended for use by application programmers. It creates a stream object which can be used to read or write data from a blob field. Instead, application programmers should use the TBlobField.LoadFromStream and TBlobField.SaveToStream methods when reading and writing data from/to BLOB fields


Do somebody have a very simple example of using these functions?

Sure, this works for me. I use Zeos Lib but I think the result must be the same.

Code: Pascal  [Select]
  1. procedure TfrmBancos.btnAplicaClick(Sender: TObject);
  2. var
  3.   ms:TMemoryStream;
  4. begin
  5.   DMBcos.ZQBancos.Insert;
  6.   DMBcos.ZQBancos.FieldByName('nombre').AsString:=edNombre.Text;
  7.   //...rest of fields
  8.   if (logotipo.Picture.Width>0) then
  9.     begin
  10.       ms:=TMemoryStream.Create;
  11.       Logotipo.Picture.SaveToStream(ms);
  12.       ms.Position:=0;
  13.       TBlobField(DMBcos.ZQBancos.FieldByName('logo')).LoadFromStream(ms);
  14.       ms.Free;
  15.     end
  16.   else
  17.     begin
  18.       DMBcos.ZQBancos.FieldByName('logo').AsString:='';
  19.     end;
  20.   DMBcos.ZQBancos.Post;
  21.   //...
  22. end;  
[/quote]
Lazarus 1.8.4 FPC 3.0.4 Linux Mint Mate 17.2 x86_64 GTK-2
Zeos 7.1.3 - Sqlite 3.8.2

Foro Lazarus en español http://forum.lazarus.freepascal.org/index.php/board,73.0.html

Vodnik

  • Full Member
  • ***
  • Posts: 138
Re: How to display image from DB in DBGrid?
« Reply #6 on: October 02, 2019, 12:04:02 am »
Thanks, GAN!
Now my procedure looks like:
Code: Pascal  [Select]
  1. procedure TMainForm.DBGridUsersDrawColumnCell(Sender: TObject;
  2.   const Rect: TRect; DataCol: Integer; Column: TColumn; State: TGridDrawState);
  3. var
  4.   bmp: TBitmap;
  5.   foto : TMemoryStream;
  6. begin
  7.   if Column.FieldName='avatar' then
  8.   begin
  9.     foto:=TMemoryStream.Create;
  10.     bmp:=TBitmap.Create;
  11.     try
  12.       TBlobField(SQLQuery.FieldByName('avatar')).SaveToStream(foto);
  13.       bmp.LoadFromStream(foto);
  14.       DBGridUsers.Canvas.Draw(Rect.left, Rect.Top, bmp);
  15.     finally
  16.       bmp.Free;
  17.       foto.Free;
  18.     end;
  19.   end;
  20. end;
  21.  
There are no errors reported now, but there are also no pictures in the cells. Column 'Avatar' just shows text (blob) in each row.
I also tried to draw just a rectangle in a cell 'Avatar'. Procedure fires, but result is the same.
Is there any hint how the drawable column should be defined?

wp

  • Hero Member
  • *****
  • Posts: 6862
Re: How to display image from DB in DBGrid?
« Reply #7 on: October 02, 2019, 12:37:25 am »
Maybe you must reset the stream (foto.Position := 0) after reading the image from the DB and before loading it into the bmp:

Code: Pascal  [Select]
  1. procedure TMainForm.DBGridUsersDrawColumnCell(Sender: TObject;
  2.   const Rect: TRect; DataCol: Integer; Column: TColumn; State: TGridDrawState);
  3. var
  4.   bmp: TBitmap;
  5.   foto : TMemoryStream;
  6. begin
  7.   if Column.FieldName='avatar' then
  8.   begin
  9.     foto:=TMemoryStream.Create;
  10.     bmp:=TBitmap.Create;
  11.     try
  12.       TBlobField(SQLQuery.FieldByName('avatar')).SaveToStream(foto);  // stream is at the end here
  13.       foto.Position := 0;  // rewind the stream to the start
  14.       bmp.LoadFromStream(foto);
  15.       DBGridUsers.Canvas.Draw(Rect.left, Rect.Top, bmp);
  16.     finally
  17.       bmp.Free;
  18.       foto.Free;
  19.     end;
  20.   end;
  21. end;
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

Vodnik

  • Full Member
  • ***
  • Posts: 138
Re: [SOLVED] How to display image from DB in DBGrid?
« Reply #8 on: October 02, 2019, 09:49:24 am »
@wp, thanks a lot, now it works!