Zeos and SQLite3 Easy (code and sample)
lazarus / 16-Databases

----------------------------------------------------------------------
u2o · 2013-09-08 07:25

I had enough troubles with SQLite3, I created a module to simplify
connection to the database. You can use sqlite3.dll with another file
name and in the directory you want. Not need sqlite3.dll copy on
C:\Windows or C:\Windows\System32, to execute on client machine.

But in order to compile the project, Lazarus and Zeos do need
sqlite.dll and sqlite3.dll in C:\Windows\ or C:\Windows\System32\

Requirements to test the project compiled (on \bin\ folder):
None, only execute EZSQlite.exe. The sqlite3.dll has renamed to
sqlite3_library.dll to show that is working.
Requirements to compile the project:
SQlite.dll on C:\Windows\ - I don't know from where download, i use a
copy from Adobe Reader 11 directory.SQlite3.dll on C:\Windows\ -
download the file sqlite-dll-win32-x86-3080001.zip  from the site
http://www.sqlite.org/download.html .ZEOSDBO-7.0.4-stable.zip from
http://sourceforge.net/projects/zeoslib/  And ofcourse, compile and
install it on Lazarus.
I apologize for the few comments in the code. Supposedly this is for
those with little experience, to learn. So if you like, add examples
of connections and relationships that help simplify.

Tested only on Windows [XP/VISTA/7/8]

This is the code -->

mzsqliteeasy.pas
Code: [Select]unit mZSQLiteEasy;

{$mode objfpc}{$H+}

interface

uses
  ZConnection, ZDataset, ZCompatibility,      { extra components
needed for this module | Zeos }
  DB, DBGrids, DBCtrls,                       { extra components
needed for this module | db controls }
  Dialogs, Classes, SysUtils;

// ZCompatibility    --> Zeos Database Objects --> for asign
TZControlsCodePage

type
  TZSQLiteEasy = class(TObject)
  public
    procedure DefineLibraryLocation(sPath: string);
    procedure DefineDatabaseLocation(sPath: string);
    //-------
    procedure DatabaseOpen(ZC: TZConnection; DS: TDatasource; ZQ:
TZQuery; DBG: TDBGrid = nil; DBN: TDBNavigator = nil);
    procedure DatabaseClose(ZC: TZConnection; ZQ: TZQuery);
    procedure DatabaseQueryOpen(ZC: TZConnection; ZQ: TZQuery; sQry:
string);
    procedure DatabaseQueryReOpen(ZC: TZConnection; ZQ: TZQuery; sQry:
string);
    procedure DatabaseQueryClose(ZC: TZConnection; ZQ: TZQuery);
    procedure DBGridAddColumn(DBGrid: TDBGrid; sDBField: string;
sColCaption: string; nColWidth: integer);
  private

  end;


implementation

var
  sLibraryLocation: string;
  bLibraryDefined: boolean;
  sDatabaseLocation: string;
  bDatabaseDefined: boolean;

procedure TZSQLiteEasy.DefineLibraryLocation(sPath: string);
begin
  sLibraryLocation := sPath;
  bLibraryDefined := True;
end;

procedure TZSQLiteEasy.DefineDatabaseLocation(sPath: string);
begin
  sDatabaseLocation := sPath;
  bDatabaseDefined := True;
end;

//-------

procedure TZSQLiteEasy.DatabaseOpen(ZC: TZConnection; DS: TDatasource;
ZQ: TZQuery; DBG: TDBGrid = nil; DBN: TDBNavigator = nil);
begin
  ZQ.Connection := ZC;
  DS.DataSet := ZQ;
  if not (DBN = nil) then DBN.DataSource := DS;
  if not (DBG = nil) then DBG.DataSource := DS;

  if not bLibraryDefined then begin
    ShowMessage('SQLite3 dll not defined...');
    Exit;
  end;
  if not bDatabaseDefined then begin
    ShowMessage('Database not defined...');
    Exit;
  end;

  //if ZC.Connected then ZC.Disconnect;

  ZC.LibraryLocation := sLibraryLocation;             { this is the
magic to use SQLite3.dll in a custom location and with custom file-
name }

  ZC.ClientCodepage := 'UTF-8';
  ZC.ControlsCodePage := TZControlsCodePage.cCP_UTF8;
  ZC.LoginPrompt := False;
  ZC.Protocol := 'sqlite-3';
  ZC.HostName := '';                                  // Hostname must
blank for embedded server projects
  ZC.Database := sDatabaseLocation;
  ZC.Connect;
end;


procedure TZSQLiteEasy.DatabaseClose(ZC: TZConnection; ZQ: TZQuery);
begin
  if ZQ.Active then exit;                     // check if active
  if ZC.Connected then ZC.Disconnect;         // disconect
end;

procedure TZSQLiteEasy.DatabaseQueryOpen(ZC: TZConnection; ZQ:
TZQuery; sQry: string);
begin
  if not ZC.Connected then exit;              // check if connected
  if ZQ.Active then exit;                     // check if active

  ZQ.SQL.Text := sQry;
  ZQ.Open;                                    // execute query

end;

procedure TZSQLiteEasy.DatabaseQueryReOpen(ZC: TZConnection; ZQ:
TZQuery; sQry: string);
begin
  if not ZC.Connected then exit;              // check if connected

  if ZQ.Active then ZQ.Close;                 // close the query

  ZQ.SQL.Text := sQry;
  ZQ.Open;                                    // execute query

end;

procedure TZSQLiteEasy.DatabaseQueryClose(ZC: TZConnection; ZQ:
TZQuery);
begin
  if not ZC.Connected then exit;              // check if connected
  if not ZQ.Active then exit;                 // check if active

  if ZQ.Active then ZQ.Close;

end;


procedure TZSQLiteEasy.DBGridAddColumn(DBGrid: TDBGrid; sDBField:
string; sColCaption: string; nColWidth: integer);
var
  nColumnsCount: integer;
begin
  DBGrid.Columns.Add.FieldName := sDBField;
  nColumnsCount := DBGrid.Columns.Count - 1;
  DBGrid.Columns[nColumnsCount].Title.Caption := sColCaption;
  DBGrid.Columns[nColumnsCount].Width := nColWidth;
end;

end.

unit1.pas
Code: [Select]unit Unit1;

{$mode objfpc}{$H+}

interface

uses
  mZSQLiteEasy,                                                 {
custom module }
  ZConnection, ZDataset,                                        { Zeos
modules }
  DB, DBCtrls, DBGrids,                                         { db
controls }
  Classes, SysUtils, FileUtil, Forms, Controls, Graphics,
  Dialogs, StdCtrls;

type

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    Datasource1: TDatasource;
    DBGrid1: TDBGrid;
    DBNavigator1: TDBNavigator;
    ZConnection1: TZConnection;
    ZQuery1: TZQuery;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  Form1: TForm1;
  oZSQLite: mZSQLiteEasy.TZSQLiteEasy;                   { Object of
the module mZSQLiteEasy }

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);
begin
  DBGrid1.Columns.Clear;
  oZSQLite.DBGridAddColumn(DBGrid1, 'player_name', 'Player', 80);
  oZSQLite.DBGridAddColumn(DBGrid1, 'record', 'Record', 50);

  oZSQLite.DatabaseOpen(ZConnection1, Datasource1, ZQuery1, DBGrid1,
DBNavigator1);
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  oZSQLite.DatabaseQueryOpen(ZConnection1, ZQuery1, 'select * from
records');
end;

procedure TForm1.Button3Click(Sender: TObject);
begin
  oZSQLite.DatabaseQueryClose(ZConnection1, ZQuery1);
  oZSQLite.DatabaseClose(ZConnection1, ZQuery1);
end;

procedure TForm1.FormCreate(Sender: TObject);
var
  sAppPath, sLibraryLocation, sDatabaseLocation: string;
begin
  sAppPath := ExtractFilePath(Application.Exename);

  sLibraryLocation := sAppPath + 'sqlite3_library.dll';
  sDatabaseLocation := sAppPath + 'sample.db';

  oZSQLite := TZSQLiteEasy.Create;                         { create
object of the module mZSQLiteEasy}

  oZSQLite.DefineLibraryLocation(sLibraryLocation);
  oZSQLite.DefineDatabaseLocation(sDatabaseLocation);

end;


end.


Uploaded to Mega. File Size : 1,2 MB. Download the file Zeos_Easy.7z


Edited
-----------------

Sorry, the preview is http://i40.tinypic.com/e0g21w.jpg

----------------------------------------------------------------------
eny · 2013-09-08 08:37

Quote from: u2o on September 08, 2013, 09:25:11 amRequirements to
compile the project:
SQlite.dll on C:\Windows\ - I don't know from where download, i use a
copy from Adobe Reader 11 directory.SQlite3.dll on C:\Windows\ -
download the file sqlite-dll-win32-x86-3080001.zip  from the site
http://www.sqlite.org/download.html .Or simply copy them into the
lazarus folder for development purposes and add a copy to the bin
folder of your program.
Especially useful in corporate environments where the windows system
folder(s) is often off limits.
No reason to add to the dll hell that Windows already provides.