Query works design-time, but not runtime?
lazarus / 16-Databases

----------------------------------------------------------------------
aqdennis · 2017-11-09 14:48

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 · 2017-11-09 15:05

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

----------------------------------------------------------------------
aqdennis · 2017-11-09 15:14

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 · 2017-11-09 15:23

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 · 2017-11-09 16:19

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 · 2017-11-09 16:38

You could try this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
frame; {$mode objfpc}{$H+} interface uses  sqldb, Forms,
StdCtrls; type   TMyFrame = class(TFrame)    cbFrameData: TComboBox; 
 SQLQuery: TSQLQuery;  private    procedure FillData;  protected   
procedure Loaded; override;  end; implementation {$R *.lfm} procedure
TMyFrame.Loaded;begin  inherited Loaded;  FillData;end; procedure
TMyFrame.FillData;begin  SQLQuery.SQL.Text := 'SELECT * FROM
Countries' ;  // etc. end; end.

----------------------------------------------------------------------
aqdennis · 2017-11-10 15:58

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i const 
WM_LOADDATA = WM_USER + 123 ;  
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i TMyFrame =
class(TFrame)    ...    ...   private      procedure WMLoadData(var
Msg : TMessage) ; message WM_LOADDATA ;   end;
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i constructor
TMyFrame.Create(TheOwner: TComponent);begin  inherited
Create(TheOwner);  Parent := TWinControl(TheOwner) ;       .... 
PostMessage(Handle,WM_LOADDATA,0,0) ;end; procedure
TMyFrame.WMLoadData(var Msg : TMessage) ;begin  FillData ;end; 
It may not win the beauty contest sending a message to itself.....but
it works.

Thanks for all the help,

Dennis

----------------------------------------------------------------------
Mike.Cornflake · 2017-11-11 17:33

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Procedure
TFrameBase.FormCreate(Sender: TObject);Begin  FSettingsLoaded :=
False;End; Procedure TFrameBase.FormActivate(Sender: TObject);Begin 
If Not FSettingsLoaded Then  Begin    DoLoadSettings;   
FSettingsLoaded := True;  End;End;