saving pictures from an sqlite3 db
lazarus / 16-Databases

----------------------------------------------------------------------
davesimplewear · 2011-08-10 23:35

I have an sqlite3 db with picture blobs that I wish to transfer
between tables and have found by assigning the value of one field to
another table that the transferred picture doesn't work, I have tried
saving to a temp folder then loading from that, using streams and
clipboard, but nothing seems to work, can someone help please. The db
tables I am using are sqliteDBo.

Regards
Dave

----------------------------------------------------------------------
Goodman H__ · 2011-08-11 01:47

Below code sample is from one of my exercise.It uses Zeos.The two
procedure readImageFromDB and WriteImageToDB did the job reading blob
data from a db and show the image in an TDBImage control,and saving an
image file into blob field of a given sqlite3 table.I am not
formatting the source in camel case,please note.

Help this helps a little.
Code: [Select]unit unit1;

{$mode objfpc}{$H+}

interface

uses
   ZConnection, ZDataset, ZAbstractDataset,lconvencoding,fpimage;

type

  { tform1 }

  tform1 = class(tform)
    datasource1: tdatasource;
   
    dbimage1: tdbimage;
   
    opendialog1: topendialog;
    txtImagePath:TEdit;//opendilaog->Select an image file
                                   //->show image file  path in the
TEdit control;
    zconnection1: tzconnection;
    zquery1: tzquery;
   
   
    procedure writeimagetodb;//save a new record,write image file into
table's blob field.
    procedure readimagefromdb;//reading image from blob field when
                                                   // navigating
record or at the form start up.
   

  private
    { private declarations }
  public
    { public declarations }
  end;

var
  form1: tform1;
  fstream:tstream;
  fpicture:tjpegimage;


implementation

{$R *.lfm}

{ tform1 }



procedure tform1.writeimagetodb(ImageFIleName:string);
begin

  try

    tblobfield(zquery1.fieldbyname('picture')).LoadFromFile(utf8tocp93
6(ImageFileName));

  except
    on e:exception do
    showmessage(e.Message);

  end;

end;

procedure tform1.readimagefromdb;
begin
  if not (tblobfield(zquery1.FieldByName('picture')).IsNull) then
     begin
       fstream:=zquery1.CreateBlobStream(zquery1.FieldByName('picture'
),bmread);
       fpicture:=tjpegimage.create;

       try
       fpicture.LoadFromStream(fstream);
       dbimage1.Picture.Assign(fpicture);
       finally
         fstream.Free;
         fpicture.Free;
       end;
     end;

end;


procedure tform1.dbnavigator1click(sender: tobject; button:
tdbnavbuttontype);
begin

  if(button=nbpost) then
     begin
       zquery1.Edit;
         writeimagetodb(txtImgPath.text);
         zquery1.Post;
     end;
  readimagefromdb;

end;

procedure tform1.formactivate(sender: tobject);
begin
  readimagefromdb;

end;



end.

----------------------------------------------------------------------
jhmos · 2011-08-11 01:56

I had to find the answer to dealing with blobs just a week ago. The
trick is to use TSQLQuery rather than Sqlite3Dataset to address the
DB, since blob handling does not work properly yet with
Sqlite3Dataset.
I found out how to do it on this forum but don't want to search for
the links right now. Here is some fragments of code that I used for
testing reading and writing to a buffer. A couple of changes would
have you handling images.
Code: [Select]var

  BlobStream : TMemoryStream;

  TestBytes : array [0..255] of byte;

  aByte : Byte;
  i : integer;
begin

  BlobStream:= TMemoryStream.Create;

  BlobStream.Clear;

  for aByte:= 0 to 255 do         // fill with some known data
    TestBytes[aByte]:= aByte;
  SQLQuery1.Open;

  // Test we can write a Blob
  BlobStream.Write(TestBytes, 256);

  BlobStream.Position:= 0;

  SQLQuery1.Append;

  (SQLQuery1.FieldByName('aBlob') as
TBlobField).LoadFromStream(BlobStream);

  SQLQuery1.Post;

  SQLQuery1.ApplyUpdates;

  SQLQuery1.Close;


  // Test that we can read the blob back
  SQLQuery1.Open;
  BlobStream.Clear;

  (SQLQuery1.FieldByName('aBlob') as
TBlobField).SaveToStream(BlobStream);

  BlobStream.Position:= 0;



  BlobStream.Read(TestBytes,256);

  for i:= 0 to 255 do         // Check the data
     if  TestBytes[i] <> i then
     begin
       ShowMessage('We got a wrong value back');

       break;
     end;
  if i > 254 then
    ShowMessage('Blob read correctly');
  SQLQuery1.close;

  BlobStream.Free;



end;


Hope it helps.
Regards,
John.

----------------------------------------------------------------------
davesimplewear · 2011-08-11 03:56

Thank You Both, That solved the problem.
Regards
Dave