Recent

Author Topic: Zeos and SQLite3 Easy (code and sample)  (Read 14258 times)

u2o

  • Jr. Member
  • **
  • Posts: 72
  • No message
Zeos and SQLite3 Easy (code and sample)
« on: September 08, 2013, 09:25:11 am »
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:

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
« Last Edit: September 08, 2013, 09:30:58 am by u2o »

eny

  • Hero Member
  • *****
  • Posts: 1609
Re: Zeos and SQLite3 Easy (code and sample)
« Reply #1 on: September 08, 2013, 10:37:04 am »
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 .
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.
All posts based on: Win10 (Win64); Lazarus 2.0.10 'stable' (x64) unless specified otherwise...

 

TinyPortal © 2005-2018