[SOLVED] How to display image from DB in DBGrid?

[Vodnik]

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)

[winni]

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]

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;

[Vodnik]

@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]

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]

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]

[Vodnik]

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]

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;

[Vodnik]

@wp, thanks a lot, now it works!