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.pasunit 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.pasunit 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.7zEdited
-----------------
Sorry, the preview is
http://i40.tinypic.com/e0g21w.jpg