Hi Panoss,
Below is what I am using to consolidate my interaction with SQLite.
Per my experience with sqldb the disconnect did not work the way I wanted so I switched.
unit SQLite_Utils_Zeos;
{$mode objfpc}{$H+}
interface
uses
Classes, SysUtils, ZConnection, ZDataset;
type
Query = class
private
{SQLite DB Attributes}
db_filename : string;
db_driver : string;
{DB Objects}
ZConnSQLite : TZConnection;
ZSQLiteQuery : TZQuery;
public
constructor Create(TheDBFile, TheSQLiteDLL : string);
function RunQSQLite(AQuery : string) : boolean;
function QuerySQLitebyTaskID(parameter: string; AQuery : string): string;
function ReturnColumns(table_name: string):string;
end;
implementation
constructor Query.Create(TheDBFile, TheSQLiteDLL : string);
begin
db_filename := TheDBFile;
db_driver := TheSQLiteDLL;
end;
function Query.RunQSQLite(AQuery : string) : boolean;
begin
ZConnSQLite := TZConnection.Create(nil);
ZConnSQLite.Protocol := 'sqlite-3';
ZConnSQLite.Database := db_filename;
ZConnSQLite.LibraryLocation:= db_driver;
ZConnSQLite.Connect;
Result := ZConnSQLite.ExecuteDirect(AQuery);
ZConnSQLite.CleanupInstance;
ZConnSQLite.Disconnect;
ZConnSQLite.FreeInstance;
end;
function Query.QuerySQLitebyTaskID(parameter: string; AQuery : string): string;
var
sql_text : string;
begin
ZConnSQLite := TZConnection.Create(nil);
ZConnSQLite.Protocol := 'sqlite-3';
ZConnSQLite.Database := db_filename;
ZConnSQLite.LibraryLocation:= db_driver;
ZConnSQLite.Connect;
ZSQLiteQuery := TZQuery.Create(nil);
ZSQLiteQuery.Connection := ZConnSQLite;
if length(parameter) > 0 then sql_text := (AQuery + parameter) else sql_text := (AQuery);
ZSQLiteQuery.SQL.Add(sql_text);
ZSQLiteQuery.Open;
Result := ZSQLiteQuery.Fields[0].AsString;
ZSQLiteQuery.CleanupInstance;
ZSQLiteQuery.Close;
ZSQLiteQuery.FreeInstance;
ZConnSQLite.CleanupInstance;
ZConnSQLite.Disconnect;
ZConnSQLite.FreeInstance;
end;
function Query.ReturnColumns(table_name : string):string;
var
list_of_columns : string = '';
begin
ZConnSQLite := TZConnection.Create(nil);
ZConnSQLite.Protocol := 'sqlite-3';
ZConnSQLite.Database := db_filename;
ZConnSQLite.LibraryLocation:= db_driver;
ZConnSQLite.Connect;
ZSQLiteQuery := TZQuery.Create(nil);
ZSQLiteQuery.Connection := ZConnSQLite;
ZSQLiteQuery.SQL.Text:= 'PRAGMA table_info(' + table_name + ')';
ZSQLiteQuery.Open;
ZSQLiteQuery.Next; //skip first column which is the autincremented ID
while not ZSQLiteQuery.EOF do
begin
if list_of_columns = '' then list_of_columns := '''' + ZSQLiteQuery.Fields[1].AsString + '''' else list_of_columns := list_of_columns + ',' + '''' + ZSQLiteQuery.Fields[1].AsString + '''';
ZSQLiteQuery.Next;
end;
Result := list_of_columns;
ZSQLiteQuery.CleanupInstance;
ZSQLiteQuery.Close;
ZSQLiteQuery.FreeInstance;
ZConnSQLite.CleanupInstance;
ZConnSQLite.Disconnect;
ZConnSQLite.FreeInstance;
end;
end.