How to display BLOB Image from database in the TAdvStringGrid with the help of DataSet

Question

I have been making an application at Delphi XE3. I am trying to display values from database to the TAdvStringGrid component placed on the form. I am using dataset to display results at TAdvSTringGRid (code is given below). All other values are displaying perfectly except Image in database. Where it is expected to show image, it is showing junk characters. How to display image perfectly from DataBase at TAdvStringGrid.

SQLConnection1: TSQLConnection;
SQLMonitor1: TSQLMonitor;
DataSource1: TDataSource;
ADOConnection1: TADOConnection;
ClientDataSet1: TClientDataSet;
AdvStringGrid1: TAdvStringGrid;
procedure Button1Click(Sender: TObject);
procedure ShowSelectResults(results: TDataSet; sg: TAdvSTringGrid);
procedure FormCreate(Sender: TObject);


procedure TForm2.FormCreate(Sender: TObject);
var
    results: TDataSet;
begin
    SQLConnection1.Params.Add('Database=E:\playdb.s3db');
    try
        SQLConnection1.Connected := true;

        SQLMonitor1.Active := True;

        SQLConnection1.Execute('Select * from plays', nil, results);


    except
        on E: EDatabaseError do
          ShowMessage('Exception raised with message' + E.Message);
    end;
    ShowSelectResults(results, advstringgrid1);
 end;

Call to ShowSelectResult below

procedure TForm2.ShowSelectResults(results: TDataSet; sg: TAdvStringGrid);
var
  names: TStringList;
  i,j,k, rc: Integer;
  resultsfield: variant;
  Field: TblobField;
  Stream: TStream;
  Jpg: TJPEGImage;
  Picture: TPicture;

begin
    if not results.IsEmpty then

    //Prints Data in the TAdvStringGrid
    results.First;
    j := 1;
    while not results.EOF do
    begin
      if (j>sg.rowcount) then
        sg.rowcount := sg.rowcount + 1;
      for i := 0 to results.fields.Count - 1 do
      begin
        if i=0 then

        else if i = 4 then
        //Here I want to display image from db
          Field := TBlobField(results.FieldByName(names[i]).AsString);
          Stream := results.CreateBlobStream(Field, bmRead);
          sg.CreatePicture(i, j, true, ShrinkWithAspectRatio, 20, haCenter, vaAboveText).Picture
        else
        sg.cells[i,j] := results.FieldByName(names[i]).AsString;
      end;
      results.Next;
      inc(j);
    end;
end;

Problem is at the else if i=4 loop in the above code at sg.CreatePicture (format of the CreatePicture procedure is given below), where I want to display image in that particular column.

In manual of TAdvStringGrid they have mentioned following methods for picture display at grid cells

Grid.CreatePicture(2,3,True,Shrink,0,haLeft,vaTop).LoadFromFile(‘TST.JPG’);
procedure AddPicture(ACol,ARow: Integer;APicture:TPicture;transparent: Boolean; stretchmode:TStretchMode; padding: Integer; hal:TCellHalign; val:TCellValign);
function GetPicture(ACol,ARow: Integer): TPicture;
Grid.CreateFilePicture(2,3,True,Shrink,0,haLeft,vaTop).Filename := ‘TST.JPG’;

But there is no mention about how to use it with DataSet.I am messing with CreatePicture procedure of TAdvStringGRid, not getting it worked out with DataSet.

Latest Development

Finally I find out way with the help of some scholars like Bummi to save the JPEG image into memorystream and then display same.

My latest code is as follows

procedure TForm2.ShowSelectResults(results: TDataSet; sg: TAdvStringGrid);
var
  names: TStringList;
  Field: TblobField;
  //Stream: TStream;
  Stream: TMemoryStream;
  //blobType := TBlobType;
  Jpg: TJPEGImage;
  Picture: TPicture;
  Image: TImage;
  Graphic: TGraphic;

Begin
    //k := results.FieldCount;
    //sg.Rowcount := rc;

    results.First;
    j := 1;
    while not results.EOF do
    begin
      if (j>sg.rowcount) then
        sg.rowcount := sg.rowcount + 1;
      for i := 0 to results.fields.Count - 1 do
      begin
        if i=0 then

        else if i = 4 then  // Column 5 for Image
          begin
           try
           if ((results.FieldByName(names[i]).AsString) <> '') then
           Begin
               Stream := TMemoryStream.Create;
               Image := Timage.Create(Self);
               Jpg := TJPEGImage.Create;
               Picture := TPicture.Create;
               Field := TBlobField(results.FieldByName('image'));
               Stream := results.CreateBlobStream(Field, bmReadWrite);
               //Field.SaveToStream(Stream);
               Stream.Position := 0;
               Jpg.LoadFromStream(Stream);
               Picture.Assign(Jpg); 

              //Jpg.LoadFromFile('C:\Sample Pictures\Cabo.jpg');
              //Picture.Assign(Jpg);

              sg.AddPicture(i,j,Picture,True,ShrinkWithAspectRatio,0,haLeft,vaTop);
            end;
           finally
              Jpg.Free;
              Stream.Free;
           end;
         end
        else
        //Prints data in other columns
        sg.cells[i.j] := results.FieldByName(names[i]).AsString;

        inc(j);
       end;   
end;

Now it's facing some memory issue according to me at the line Jpg.LoadFromStream(Stream); It is error code JPEG Error #53 , I came to know that above such error code display only when image you are trying to access via memorystream is corrupted but I have made sure image is not corrupted and displaying properly with the help of other software extracted from similar database. I also have renewed the image in the database. Still why I am getting JPEG Error #53. Problem is mainly at Jpg.LoadFromStream(Stream)

Note that the with commented code

Jpg.LoadFromFile('C:\Sample Pictures\Cabo.jpg'); 
Picture.Assign(Jpg);
sg.AddPicture(i,j,Picture,True,ShrinkWithAspectRatio,0,haLeft,vaTop); 

When it is extracted from static file it works perfectly. Problem is only with the MemoryStream. How to rectify this error?

Proteose

Is is absolutely sure that the blob field has the image you expect? For instance, if you drop a TDBImage on your form and link it to your blob field, will the component show the image? – Showroom

Yes there is already an image at SQLite database. Problem is with the TPicture. As we can display picture at TAdvSTringGRid with the help of following functions and procedures <br /> function CreatePicture(aCol,aRow:integer;transparent:boolean;stretchmode:TStretchMode;padding:integer;hal:TCellHalign;val:TCellValign):tpicture; procedure AddPicture(aCol,aRow:integer;apicture:tpicture;transparent:boolean;stretchmode:TStretchMode;padding:integer;hal:TCellHalign;val:TCellValign); procedure RemovePicture(aCol,aRow:integer); function GetPicture(aCol,aRow:integer):tpicture; – Proteose

Tpicture dont have any LoadFromStream procedure or function. How to get stream blob image data loaded into TPicture. – Proteose

try Picture.Graphic.LoadFromStream(Stream); sg.AddPicture(i, j, Picture, true, ShrinkWithAspectRatio, 20, haCenter, vaAboveText) finally Stream.Free; This is what I finished with just now. It dont give debugging error but gives SQLException error $C000000005 with access violation at 0x00409250; read of address 0xfffdffcd – Proteose

Answer 1

CreateBlobStream is creating a TStream object, not a TMemoryStream.
Since you do not want to write the JPG to the database you should use bmRead instead of bmReadWrite.
I am not used to SQLite, but you will have to make sure that you are using a suitable binary datetype (BLOB).

  JPG := TJpegImage.Create;
  Picture:= TPicture.Create;
  try
    st := results.CreateBlobStream(TBlobField(results.FieldByName('image')), bmRead);
    try
      JPG.LoadFromStream(st);
      Picture.Assign(JPG);
      sg.AddPicture(i,j,Picture,True,ShrinkWithAspectRatio,0,haLeft,vaTop);
    finally
      st.Free;
    end;
  finally
    JPG.Free;
    Picture.Free;
  end;

To ensure that the stored image is really a JPG you should write the JPG for testing with something like:

var
  ms: TMemoryStream;
begin
  ads.Open;
  ads.Append;
  ms := TMemoryStream.Create;
  try
    Image1.Picture.Graphic.SaveToStream(ms); // make sure having loaded a JPG
    ms.Position := 0;
    TBlobField(ads.FieldByName('image')).LoadFromStream(ms);
  finally
    ms.Free;
  end;
  ads.Post;
end;

Gatias

Answer 2

I realize this is a tad late, but I wanted to contribute. I quite simply did the following, having not to worry about the image format (.jpg, .png etc.). I simply create a TStream object, load the BLOB stream into it, and then I load the Image from the stream. Short and sweet, and it works great for me.

   var
     Stream : TStream;
   begin
     try
       Stream := TStream.Create;
       Stream := Dataset.CreateBlobStream(Dataset.FieldByName('SIGNATURE'), bmRead);
       Stream.Position := 0;
       lblPicSize.Caption := 'Picture is ' + IntToStr(Stream.Size) + ' Bytes';
       if Stream.Size <= 0 then
         pnlPic.Caption := '<No Signature>'
       else
         pnlPic.Caption := '';

       try
         imgSignature.Picture.LoadFromStream(Stream);
       except
         on E:Exception do
           begin
             ShowMessage(E.Message);
           end;
       end;

 finally
   Stream.Free;
 end;
end;

Fleece