Hot to refer to a TSQLQuery component of a form?
lazarus / 16-Databases

----------------------------------------------------------------------
panoss · 2014-11-21 17:11

I 'm trying to make a global function (placed in a unit) for closing
some forms.
Every form has a control TSQLquery named 'SQLquery'.
I 'm trying to do a TSQLquery.close:
Code: [Select]procedure GlobalFormClose(Sender: TObject);
var f: TForm;
var c: TSQLQuery;
begin
  f:=TForm(Sender);
//c := f.Controls['SQLQuery']; FAILS
//c := f.f.FindComponent();['SQLQuery']; FAILS
//f.SQLQuery.close; FAILS

 
  f.SQLTransaction.Active := False;
  f.SQLite3Connection.Connected := False;

  f.Destroy;
  f:=nil;
end;        
They all fail:
c := f.Controls['SQLQuery']; FAILS
c := f.f.FindComponent();['SQLQuery']; FAILS
f.SQLQuery.close; FAILS

----------------------------------------------------------------------
howardpc · 2014-11-21 17:21

Is there a compelling reason for your queries to be coupled to your
forms?
Have you tried containing your queries in a datamodule, which a form
(if shown) could access?

----------------------------------------------------------------------
panoss · 2014-11-21 17:30

I didn't think of this and also I have no idea how to do it  .
Is there some example?

I found some, I 'll try, thanks!

----------------------------------------------------------------------
panoss · 2014-11-21 18:45

No, I haven't made it work...
is there any working example?

----------------------------------------------------------------------
tudi_x · 2014-11-21 22:24

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.

Code: [Select]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.

----------------------------------------------------------------------
fpcoder · 2014-11-21 23:28

Have a look at this one.

Quote from: panoss on November 21, 2014, 07:45:08 pmNo, I haven't made
it work...
is there any working example?

----------------------------------------------------------------------
panoss · 2014-11-22 08:19

Thank you all very mush, I finally made it work (I had just to adjust
the datasource property of the data bound controls of the forms  ).