how save and load images in sqlite database

[Ericktux]

Good friends  , a question, I would like to have a list of contacts with their corresponding photos, how I could save their photos inside the database and then see them with a "tdbimage".

PS: I've searched for information about it but I'm confused. 

[Thaddy]

This discussion has code and pointers. http://forum.lazarus.freepascal.org/index.php?topic=17606.0
If you are using a sqldb type the sql engine is transparent.  (Note I would save/load image.picture and not use graphic)

[Jurassic Pork]

hello,
ErickTux  have a look to my database example (in Lazarus\examples\Database\Image_Mushrooms ) :

[MushRoomsDatabase by Jurassic Pork using SQLite - January 2014
Updated for Firebird Embedded - August 2014

Features:
- Use SqlDb and lazreport components.
- Sqlite3 or Firebird embedded database DeadlyMushrooms with 5 mushrooms.
Sqlite3 will be tried first; if no Sqlite library is available, Firebird
embedded will be tried.
- It demonstrates:
- creating a new SQLite3 database with table if the db does not exist
- use of TSQLScript to run multiple SQL statements
- use of FBAdmin to restore Firebird backup (smaller than the live .fdb file)
on first run, useful for keeping your setup file small and compatible with
older Firebird versions
- The images are stored in blob field without extension at the beginning.
With this you can view blob images with database browser editor
(e.g. sqlite2009pro).
- In the database there is also a field with images links (filenames).
- The linked images are stored in the folder images of the project.
- You can see the linked images in a Timage.
- You can change the images in the database:
- for Tdbimage (image in db): double click on the component and choose your
image.
- for Timage (linked image): click on the button near the image filename
(you must be in edit mode).
- Transaction commits when you click on Tdbnavigator refresh button or on close
form.
- Small pictures of the mushrooms are in the sqlite3Database. Largest images are
in files in the folder images.
- Print button to print all the mushrooms (lazreport).
On each page you have:
- a title.
- the field common_name of the mushroom database.
- the field notes of the mushroom database.
- the field picture of the mushroom database (picture picture1).
- the picture of the field image_link (picture picture2).

The report name is Mushroom_Report.lrf

You need to install Lazreport and sqldb packages for the example to work.

Friendly, J.P

[Thaddy]

Yup. it is also here http://wiki.lazarus.freepascal.org/TDBImage Good example.

[Ericktux]

thanks for your friends answers, I see that the only thing I have to do is convert the images in blob, then to see them it's easy to just link it with "tdbimage".

but to create "blob" I must use "TStream" or "TMemoryStream" or "TFileStream" or "TBlobStream" I'm confused. 

[Thaddy]

Any descendant of the abstract Tstream should do, depending on storage. Usually a TMemorystream, though.
Did you actually look at the pointers we gave you? Doesn't look like it.

[Jurassic Pork]

but to create "blob" I must use "TStream" or "TMemoryStream" or "TFileStream" or "TBlobStream" I'm confused. 

have a look of this procedure in my example  :

Code: Pascal  [Select][+][-]

1. procedure TForm1.LoadDbImage(aFileName: string);
2. begin
3.   // Disabling writeheader means the image is compatible with Delphi TDBImage applications
4.   // and other applications that only expect raw jpg data in the database blob.
5.   // However, it makes it a bit more difficult to mix various kinds of image types
6.   // e.g. jpg and tiff in the same database fields.
7.   // Fortunately, Lazarus can deal with that for us.
8.   DbImage1.WriteHeader:=false;
9.   DbImage1.Picture.LoadFromFile(aFileName);
10. end;

- The images are stored in blob field without extension at the beginning.
With this you can view blob images with database browser editor
(e.g. sqlite2009pro).
- You can change the images in the database:
- for Tdbimage (image in db): double click on the component and choose your
image.

[Ericktux]

Good friends, I share my advances.
I have reviewed the example of the friend "Juan" is excellent but I could not understand how to generate the "blob" from an image.

Anyway, trying a little, I made a small advance, this is:

Code: Pascal  [Select][+][-]

1. unit Unit1;
2.  
3. {$mode objfpc}{$H+}
4.  
5. interface
6.  
7. uses
8.   Classes, SysUtils, db, sqldb, sqlite3conn, FileUtil, Forms, Controls,
9.   Graphics, Dialogs, Buttons, DBGrids, DbCtrls, StdCtrls;
10.  
11. type
12.  
13.   { TForm1 }
14.  
15.   TForm1 = class(TForm)
16.     BitBtn1: TBitBtn;
17.     Button1: TButton;
18.     DataSource1: TDataSource;
19.     DBGrid1: TDBGrid;
20.     DBImage1: TDBImage;
21.     Edit1: TEdit;
22.     Edit2: TEdit;
23.     Edit3: TEdit;
24.     GroupBox1: TGroupBox;
25.     Label1: TLabel;
26.     Label2: TLabel;
27.     Label3: TLabel;
28.     Label4: TLabel;
29.     OpenDialog1: TOpenDialog;
30.     SQLite3Connection1: TSQLite3Connection;
31.     SQLQuery1: TSQLQuery;
32.     SQLTransaction1: TSQLTransaction;
33.     procedure BitBtn1Click(Sender: TObject);
34.     procedure Button1Click(Sender: TObject);
35.     procedure FormCreate(Sender: TObject);
36.   private
37.     { private declarations }
38.   public
39.     { public declarations }
40.   end;
41.  
42. var
43.   Form1: TForm1;
44.   rutaEXE: string;
45.  
46. implementation
47.  
48. {$R *.lfm}
49.  
50. { TForm1 }
51.  
52. procedure TForm1.FormCreate(Sender: TObject);  // start creating database if it does not exist
53. begin
54. rutaEXE:=ExtractFilePath(UTF8Encode(Application.ExeName));    // ejem    C:\Users\Ericktux\Desktop\
55.   if not FileExists(rutaEXE+'base1.db') then
56.          begin
57.          SQLite3Connection1.DatabaseName := 'base1.db';
58.          SQLite3Connection1.Connected := True;
59.          SQLTransaction1.DataBase:=SQLite3Connection1;
60.          SQLQuery1.DataBase:=SQLite3Connection1;
61.          SQLTransaction1.Active:= True;
62.          SQLite3Connection1.ExecuteDirect('CREATE TABLE contacts(id INTEGER PRIMARY KEY, name VARCHAR, last_name VARCHAR, Picture BLOB)');
63.          SQLTransaction1.Commit;
64.  
65.               SQLQuery1.SQL.Text:='SELECT * FROM contacts';
66.               SQLQuery1.Open;
67.               DataSource1.DataSet:=SQLQuery1;
68.               DBGrid1.DataSource:=DataSource1;
69.               DBGrid1.AutoFillColumns:=true;
70.  
71.           end
72.           else
73.               BEGIN
74.               SQLite3Connection1.DatabaseName:='base1.db';
75.               SQLite3Connection1.Connected:=true;
76.               SQLTransaction1.DataBase:=SQLite3Connection1;
77.               SQLQuery1.DataBase:=SQLite3Connection1;
78.               SQLTransaction1.Active:= True;
79.               SQLQuery1.SQL.Text:='SELECT * FROM contacts';
80.               SQLQuery1.Open;
81.               DataSource1.DataSet:=SQLQuery1;
82.               DBGrid1.DataSource:=DataSource1;
83.               DBGrid1.AutoFillColumns:=true;
84.  
85.               end;
86. end;
87.  
88. procedure TForm1.BitBtn1Click(Sender: TObject);  // add new item
89. begin
90.   SQLite3Connection1.DatabaseName := 'base1.db';
91.   SQLite3Connection1.Connected := True;
92.   SQLQuery1.SQL.Clear;
93.   SQLQuery1.SQL.Add('insert into contacts (name,last_name,picture) values (:name,:last_name,:picture)');
94.   SQLQuery1.Params.paramByName('name').AsString         := edit1.text;
95.   SQLQuery1.Params.paramByName('last_name').AsString    := edit2.text;
96.   SQLQuery1.Params.paramByName('picture').LoadFromFile(Edit3.Text, ftBlob);
97.   SQLQuery1.ExecSQL;
98.   SQLTransAction1.Commit;
99.   SQLQuery1.Close;
100.  
101.         begin
102.         SQLQuery1.Close;
103.         SQLQuery1.SQL.text:='Select * FROM contacts';
104.         SQLQuery1.ExecSQL;
105.         SQLQuery1.Open;
106.         end;
107. end;
108.  
109.  
110.  
111. procedure TForm1.Button1Click(Sender: TObject);  // choose image
112. begin
113.   OpenDialog1.InitialDir := ExtractFilePath(Application.ExeName)+'img';
114.   if OpenDialog1.Execute then
115.     begin
116.     Edit3.Text := OpenDialog1.FileName;
117.     end;
118. end;
119.  
120. end.

at the moment it works well, it only works with JPG and BMP. 
with GIF sometimes fails.

will it be the right path? 

attached image and project.

link of sqlite3 dll
http://www.mediafire.com/file/2sfkxszhaao0kk2/sqlite3.7z