Query works design-time, but not runtime?

[aqdennis]

Hi,

I'm working on a frame containing edit, comboboxes etc that represents 1 record of a database.  I have created a DataModule containing a DataSource, SQLTransaction and SQLite3Connection while my frame contains an SQLQuery.
The reason I do so is that I may be creating multiple frames simultaneously so I want them to all have their own independent SQLQuery to retrieve the appropriate record.


If I query the database design time I get back the record I expected, but runtime I get no records at all.
The frame has a procedure " FillData" and this procedure contains:

SQLQuery.SQL.Text := 'SELECT * FROM MyTable' ;
SQLQuery.Open ;
while not SQLQuery.EOF do
begin                       
     ....
end

But since it works design time, I know transaction, datasource and connection are properly linked...

It gets even stranger: if I put a SQLQuery in my DataModule and then change the code above to:

DataModule.SQLQuery.SQL.Text := 'SELECT * FROM MyTable' ;
DataModule.SQLQuery.Open ;
while not DataModule.SQLQuery.EOF do
begin                       
     ....
end

This works both design time and runtime.....

Ah, I also tried to give the frame its own DataSource, SQLTransaction and SQLite3Connection. This didn't work either design time nor run time.

So....am I doing something wrong or is it not possible to use a database connection as part of a frame?

Cheers,
Dennis

[tudi_x]

hi,
try with ExecSQL also.
use a First on the query to position yourself at first record before browsing the returned dataset.

[aqdennis]

Hi,

I tried with ExecSQL, same result (that is, no result).

I can't use a First since neither Open nor ExecSQL puts the Dataset in Browse state, it remains in Inactive state.  When I use the SQLQuery from the Datamodule, it does change state from Inactive to Browse...

Dennis

[tudi_x]

i would draft all components in code for run-time only.
anyway you would need to pass the connection handle across queries as sqlite accepts only one connection as far as i experienced (needs critical section).

share some code maybe..

[aqdennis]

Ok, had to play around for a while to make a reduced version of the program not to post a huge listing. And in the proces I discovered how to get it to work....

I gave the frame its own constructor to be able to pass the ID of the record to be displayed to the frame.....and from the constructor I call the function to fill the fields in the frame. Apparently that is too soon...
If I add a button to the frame and load data upon pressing the button it does get all data from the database.

But ok, some source to show what I did (wrong):

=======
unit frame;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, sqldb, FileUtil, Forms, Controls, StdCtrls;

type

  { TMyFrame }

  TMyFrame = class(TFrame)
    cbFrameLoad: TButton;
    cbFrameData: TComboBox;
    SQLQuery: TSQLQuery;
    procedure cbFrameLoadClick(Sender: TObject);
  private
    procedure FillData ;
  public
    constructor Create(AOwner : TComponent) ; override ;
  end;

implementation

{$R *.lfm}

{ TMyFrame }


procedure TMyFrame.FillData ;
begin
  SQLQuery.SQL.Text := 'SELECT * FROM Countries' ;
  SQLQuery.Open ;
  while not SQLQuery.EOF do
  begin
    cbFrameData.Items.Add(SQLQuery.FieldByName('Country_Name').AsString) ;
    SQLQuery.Next ;
  end;
  SQLQuery.Close ;
end;

constructor TMyFrame.Create(AOwner : TComponent) ;
begin
  inherited Create(AOwner) ;

// this doesn't work

  FillData ;
end;

procedure TMyFrame.cbFrameLoadClick(Sender: TObject);
begin

// this DOES work

  FillData ;
end;

end.

==========

unit main;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, sqldb, FileUtil, Forms, Controls, Graphics, Dialogs,
  StdCtrls, frame;

type

  { TForm1 }

  TForm1 = class(TForm)
    MyFrame1: TMyFrame;
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }

end.         

=========
unit data;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, db, sqldb, sqlite3conn, FileUtil;

type

  { TMyDataModule }

  TMyDataModule = class(TDataModule)
    DataSource: TDataSource;
    SQLite3Connection: TSQLite3Connection;
    SQLTransaction: TSQLTransaction;
    procedure DataModuleCreate(Sender: TObject);
    procedure DataModuleDestroy(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  MyDataModule: TMyDataModule;

implementation

{$R *.lfm}

{ TMyDataModule }

procedure TMyDataModule.DataModuleCreate(Sender: TObject);
begin
  SQLite3Connection.DatabaseName := ExtractFilePath(ParamStr(0)) + 'data\cupping_data.sl3' ;
  SQLite3Connection.Connected := True ;
end;

procedure TMyDataModule.DataModuleDestroy(Sender: TObject);
begin
  SQLite3Connection.Connected := False ;
end;

end.
                     

[howardpc]

You could try this:

Code: Pascal  [Select][+][-]

1. unit frame;
2.  
3. {$mode objfpc}{$H+}
4.  
5. interface
6.  
7. uses
8.   sqldb, Forms, StdCtrls;
9.  
10. type
11.  
12.   TMyFrame = class(TFrame)
13.     cbFrameData: TComboBox;
14.     SQLQuery: TSQLQuery;
15.   private
16.     procedure FillData;
17.   protected
18.     procedure Loaded; override;
19.   end;
20.  
21. implementation
22.  
23. {$R *.lfm}
24.  
25. procedure TMyFrame.Loaded;
26. begin
27.   inherited Loaded;
28.   FillData;
29. end;
30.  
31. procedure TMyFrame.FillData;
32. begin
33.   SQLQuery.SQL.Text := 'SELECT * FROM Countries' ;
34.   // etc.
35.  
36. end;
37.  
38. end.

[aqdennis]

I tried, but apparently the "Loaded" still comes too early. So I now solved it by making a message handler in which the FillData function is called and the frame sends a message to itself at the end of the constructor:

Code: Pascal  [Select][+][-]

1. const
2.   WM_LOADDATA = WM_USER + 123 ;  

Code: Pascal  [Select][+][-]

1. TMyFrame = class(TFrame)
2.     ...
3.     ...
4.   private
5.       procedure WMLoadData(var Msg : TMessage) ; message WM_LOADDATA ;  
6. end;

Code: Pascal  [Select][+][-]

1. constructor TMyFrame.Create(TheOwner: TComponent);
2. begin
3.   inherited Create(TheOwner);
4.   Parent := TWinControl(TheOwner) ;    
5.   ....
6.   PostMessage(Handle,WM_LOADDATA,0,0) ;
7. end;
8.  
9. procedure TMyFrame.WMLoadData(var Msg : TMessage) ;
10. begin
11.   FillData ;
12. end;  

It may not win the beauty contest sending a message to itself.....but it works.

Thanks for all the help,

Dennis

[Mike.Cornflake]

Interesting solution, well solved :-)

I use FormActivate for this purpose.  Gotta be careful, because FormActivate gets calls every time the user switches to the form.  I prefer your code to mine.

Either way, I recommend putting the code in TFrameBase, then descend all your frames from this.  My DoLoadSettings is virtual.

Code: Pascal  [Select][+][-]

1. Procedure TFrameBase.FormCreate(Sender: TObject);
2. Begin
3.   FSettingsLoaded := False;
4. End;
5.  
6. Procedure TFrameBase.FormActivate(Sender: TObject);
7. Begin
8.   If Not FSettingsLoaded Then
9.   Begin
10.     DoLoadSettings;
11.     FSettingsLoaded := True;
12.   End;
13. End;