Delphi load image save as blob in a sql database

Question

I'm trying to load a Image control from a image blob saved previously in a sql database.I have testd so many ways and i can't make it work. The image blob is saved as:

qry.SQL.Text := 'update tbl set pic = :blobVal where id = :idVal';
qry.Parameters.ParamByName('blobVal').LoadFromFile('c:\sample.jpg', ftBlob);
qry.Parameters.ParamByName('idVal').Value := 1;

any suggestion?

Answer 1

There a a lot of treads here about loading images to as database, but I did not find one with update or insert parameters.

You might simply assign a graphic object to your parameter. If you want to store different graphic types you should add a column keeping the Information which kind of graphic should be stored (e.g. jpeg,bmp,png). to be able to create the needed TGraphic class descendant if you want to retrieve the picture from the database.

uses jpeg, pngimage;

type
 TitTYPES=(itJPG,itPNG,itBMP);

procedure TDEMO.Button1Click(Sender: TObject);
var
 jp:TJpegimage;
 g:TGraphic;
begin

  jp:=TJpegimage.Create;
  try
    ads.Close;
    jp.LoadFromFile('C:\Bilder1\PIC.jpg');
    ads.SQL.Text := 'Insert into IMGBlob (ID,Blob,typ) Values (:ID,:BLOB,:typ)';
    ads.Parameters[0].Value := 1;
    ads.Parameters[1].Assign(jp);
    ads.Parameters[2].Value := itJPG;
    ads.ExecSQL;

    ads.SQL.Text := 'Select * from IMGBlob where ID=:ID';
    ads.Parameters[0].Value := 1;
    ads.Open;
    try
      case TitTYPES(ads.FieldByName('typ').AsInteger) of
           itJPG: g:=TJpegimage.Create;
           itPNG: g:=TPNGImage.Create;
           itBMP: g:=TBitmap.Create;
      end;
    g.Assign(ads.FieldByName('Blob'));
    Image1.Picture.Assign(g);
    finally
      g.Free;
    end;
  finally
    jp.Free;
  end;
end;

Answer 2

To load a BLOB field into an image, you need to use TDataSet.CreateBlobStream.

var
  Stream: TStream;
  JPG: TJpegImage;
begin
  JPG := TJpegImage.Create;
  try
    Stream := Qry.CreateBlobStream(Qry.FieldByName('BLOBVAL'), bmRead);
    try
      JPG.LoadFromStream(Stream);
    finally
      BlobStream.Free;
    end;
  finally
    JPG.Free;
  end;
end;

To store the image back, you'll need to do the reverse:

var
  Stream: TBlobStream;
  Jpg: TJpegImage;
begin
  Jpg := TJpegImage.Create;
  try
    Jpg.Assign(Image1.Picture.Graphic);
    // Assign other query parameters here
    Stream := Qry.CreateBlobStream(Qry.FieldByName('BLOBVAL'), bmWrite);
    try
      Jpg.SaveToStream(Stream);
      Qry.ExecSQL;
    finally
      Stream.Free;
    end;
  finally
    Jpg.Free;
  end;
end;

TDBImage is only designed to work with bitmaps (when the field is ftGraphic), so it won't work with JPEG images directly. The easiest thing to do is to load the blob as a JPEG, and assign it to a standard TImage.Picture.Graphic in an event handler for the dataset (such as it's AfterScroll event).

Answer 3

save to db:

var
  ms:tmemorystream;
Begin  
  ms:=tmemorystream.create;
  ms.position:=0;
  image1.picture.bitmap.savetostream(ms);
  ms.position:=0;
  with yourfield as tblobfield do
    loadfromstream(ms);
  freeandnil(ms);
end; 

Load from db:

var
  ms:tmemorystream;
Begin  
  ms:=tmemorystream.create;
  ms.position:=0;
  with yourfield as tblobfield do
    savetostream(ms);
  ms.position:=0;
  image1.picture.bitmap.loadfromstream(ms);
  freeandnil(ms);
end; 

Answer 4

It does not work with all graphic types like PNG etc.

This one does work on PNG as well:

var mBitmap : TGraphic;
var mStream : TStream;
var mClass  : TGraphicClass;
begin
      mStream := Query.CreateBlobStream(Query.FieldByName('yourBlobfield'), bmRead);
      mClass  := GetGraphicClassForFileExtension(mStream.ReadAnsiString);
      mBitmap := mClass.Create;
      mBitmap.LoadFromStream(mStream);
      Image4.Picture.Assign(mBitmap);
 end;