Recent

Author Topic: how save and load images in sqlite database  (Read 6659 times)

Ericktux

  • Full Member
  • ***
  • Posts: 198
how save and load images in sqlite database
« on: December 28, 2017, 08:43:03 am »
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

  • Hero Member
  • *****
  • Posts: 9401
Re: how save and load images in sqlite database
« Reply #1 on: December 28, 2017, 09:01:16 am »
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)
« Last Edit: December 28, 2017, 09:05:26 am by Thaddy »
also related to equus asinus.

Jurassic Pork

  • Hero Member
  • *****
  • Posts: 753
Re: how save and load images in sqlite database
« Reply #2 on: December 28, 2017, 09:07:07 am »
hello,
ErickTux  have a look to my database example (in Lazarus\examples\Database\Image_Mushrooms ) :
Quote
[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
« Last Edit: December 28, 2017, 09:12:19 am by Jurassic Pork »
Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

Thaddy

  • Hero Member
  • *****
  • Posts: 9401
Re: how save and load images in sqlite database
« Reply #3 on: December 28, 2017, 09:15:55 am »
Yup. it is also here http://wiki.lazarus.freepascal.org/TDBImage Good example.
also related to equus asinus.

Ericktux

  • Full Member
  • ***
  • Posts: 198
Re: how save and load images in sqlite database
« Reply #4 on: December 28, 2017, 04:08:09 pm »
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

  • Hero Member
  • *****
  • Posts: 9401
Re: how save and load images in sqlite database
« Reply #5 on: December 28, 2017, 04:11:46 pm »
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.
also related to equus asinus.

Jurassic Pork

  • Hero Member
  • *****
  • Posts: 753
Re: how save and load images in sqlite database
« Reply #6 on: December 28, 2017, 04:26:00 pm »
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;

Quote
- 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.
« Last Edit: December 28, 2017, 04:29:42 pm by Jurassic Pork »
Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

Ericktux

  • Full Member
  • ***
  • Posts: 198
Re: how save and load images in sqlite database
« Reply #7 on: December 29, 2017, 05:34:43 am »
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
« Last Edit: December 29, 2017, 05:37:58 am by Ericktux »