Setting properties ofdatabase components such as TSQLQuery at runtime
lazarus / 16-Databases

----------------------------------------------------------------------
georges · 2014-10-02 01:19

Hi

I'm new to Lazarus and learning how to set up the connectivity a
Microsoft SQL Server 2008 R2 database.  I want to be able to set the
component properties at run time.  At the moment, I'm setting the
values manually in the code but eventually will be getting the values
from TEdit component.

I have written the following code which compiles without errors. 
However, the DBGrid displays with no columns.  What am I doing wrong?

var
   v_mssqlconnection: TMSSQLConnection;
   v_sqltransaction: TSQLTransaction;
   v_sqlquery: TSQLQuery;
   v_string: string;
   v_datasource: TDataSource;
   v_dbgrid: TDBGrid;

begin
   v_mssqlconnection:= TMSSQLConnection.Create(nil);
   v_mssqlconnection.HostName := 'e6530-01-vm03';
   v_mssqlconnection.DatabaseName := 'TESTDB';
   v_mssqlconnection.UserName := 'sa';
   v_mssqlconnection.Password := 'super';
   v_mssqlconnection.Connected := true;

   v_sqltransaction:= TSQLTransaction.Create(nil);
   v_sqltransaction.Database := v_mssqlconnection;
   v_sqltransaction.Active := true;

   v_sqlquery:= TSQLQuery.Create(nil);
   v_sqlquery.Database:= v_mssqlconnection;
   v_sqlquery.SQL.Text:= 'select ITEM_NO from IM_ITEM';
   v_sqlquery.Open;

   v_datasource:= TDataSource.Create(nil);
   v_datasource.DataSet := v_sqlquery;

   v_dbgrid:= TDBGrid.Create(nil);
   v_dbgrid.DataSource := v_datasource;

end.

----------------------------------------------------------------------
rvk · 2014-10-02 06:51

You don't set a visual parent for your dbgrid. Your grid shouldn't
even be visible at that point. Are you sure you haven't defined a
dbgrid at design time? If so... remove it and add a line below
tdbgrid.create:
Code: [Select]DbGrid.parent:=self;
You also might want to set the dimensions of the dbgrid to something
other then the default.

----------------------------------------------------------------------
mangakissa · 2014-10-02 06:52

Welcome to this form.

QuoteI have written the following code which compiles without errors. 
However, the DBGrid displays with no columns.  What am I doing
wrong?There will be no DBGrid on form at all. If you use GUI
components @runtime, give the parent to your form.
Example, your form is form1:
Code: [Select]   v_dbgrid:= TDBGrid.Create(form1);
   v_dbgrid.DataSource := v_datasource;
   v_dbgrid.Align := alClient;
   v_dbgrid.Parent := form1;
This works like a charm.

Don't forget to release your datacomponents. If you use
TMSSQLConnection.Create(form1), the form will clean all related
components on the form after closing it.

You say to start a beginner. Why creating GUI components @runtime and
not @designtime? Are there reasons?

----------------------------------------------------------------------
georges · 2014-10-03 02:07

Hi rvk

Thanks for the assistance.  I followed your advice and replaced the
following lines

   v_dbgrid:= TDBGrid.Create(nil);
   v_dbgrid.DataSource := v_datasource;

with these

   v_dbgrid:= TDBGrid.Create(nil);
   v_dbgrid.DataSource := v_datasource;
   v_dbgrid.parent:=self;

but I get the error " Error: Identifier not found "self" ".   Can you
give me a bit more guidance as to what I may be doing wrong?

----------------------------------------------------------------------
georges · 2014-10-03 02:08

Hi mangakissa

Thanks for the greeting.  I followed your advice and made the
following changes to my previously posted code:

change #1
---------
   original code
   -------------   
   v_mssqlconnection:= TMSSQLConnection.Create(nil);

   new code
   -------------   
   v_mssqlconnection:= TMSSQLConnection.Create(form1);


change #2
---------
   original code
   -------------   
   v_dbgrid:= TDBGrid.Create(nil);
   v_dbgrid.DataSource := v_datasource;


   new code
   -------------   
   v_dbgrid:= TDBGrid.Create(form1);
   v_dbgrid.DataSource := v_datasource;
   v_dbgrid.Align := alClient;
   v_dbgrid.Parent := form1;


The code compiles without any errors.  It also displays the Grid but
it is empty.  I have attached a picture of what it is doing.

As to the reason why I am creating GUI components at runtme and not
designtime, it is because I want to have the flexibility be able to
set the following properties at runtime and not hard coded:


   TMSSQLConnectionHostName
      TMSSQLConnectionDatabaseName
      TMSSQLConnectionUserName
      TMSSQLConnection.Password

It may not be absolutely necessary now but I want to develop my skills
for more challenging stuff later.

----------------------------------------------------------------------
BigChimp · 2014-10-03 03:07

It is indeed not absolutely necessary.

There is absolutely no need to create controls dynamically just to be
able to set properties at runtime. You can set properties at runtime
for controls dropped on a form just as well.
See http://wiki.lazarus.freepascal.org/SQLdb_Tutorial1 and
following...

----------------------------------------------------------------------
rvk · 2014-10-03 04:27

Quote from: georges on October 03, 2014, 04:08:24 amI have attached a
picture of what it is doing.
There was no picture attached.
Could you also show your complete form-unit code?
We need to know where this code was executed in the source.

You say "self" was an unknown identifier so this code is apparently
not in the formcreate of your form (where this is usually done). (We
need to see that code too)

----------------------------------------------------------------------
georges · 2014-10-03 04:59

Hello BigChimp

I did try to set the properties at runtime for controls dropped on a
form but I kept getting errors.   However, after your reply I tried
again and saw what I was doing wrong.   I was placing the code to set
the properties between the begin and end of the unit when I should
have placed it in the FormCreate procedure.   Now it works. 

Thanks a lot.

BTW rvk, I also see your post inferred that as well.  Thank you as
well.

----------------------------------------------------------------------
u2o · 2014-10-21 13:36

Hi!

@georges: I'm working (in the last year) in a application to manage
loans of books in a library.

To simplify all SQlite3 (runtime) code refering to: open/close/query,
etc...  I use the Modularization.

Create a unit named unitdatabases.pas (not a form, a unit)

Code: [Select]unit UnitDatabases;

interface

uses
  Sqlite3DS,
  SQLite3Conn,
  sqldb,
  DBCtrls,
  DB,
  Classes, SysUtils, ExtCtrls;

type
  {TDatabases}

  TDatabases = class
  public
    class procedure DatabaseOpen(Sqlite3Dataset: TSqlite3Dataset;
DataSource: TDataSource; DBNavigator: TDBNavigator; sDBFileName:
string; sDBTableName: string; sDBPrimaryKey: string); overload;
    class procedure DatabaseClose(Sqlite3Dataset: TSqlite3Dataset);
overload;
    class procedure DatabaseOpen(SQLite3Connection:
TSQLite3Connection; SQLTransaction: TSQLTransaction; DataSource:
TDataSource; SQLQuery: TSQLQuery; sDBFileName: string); overload;
    class procedure DatabaseClose(SQLite3Connection:
TSQLite3Connection; SQLTransaction: TSQLTransaction; DataSource:
TDataSource; SQLQuery: TSQLQuery); overload;
    class procedure DatabaseOpenQuery(Sqlite3Dataset: TSqlite3Dataset;
DataSource: TDataSource; DBNavigator: TDBNavigator; sDBFileName:
string; sDBTableName: string; sDBPrimaryKey: string; sQuery: string);
  end;

implementation

class procedure TDatabases.DatabaseOpen(Sqlite3Dataset:
TSqlite3Dataset; DataSource: TDataSource; DBNavigator: TDBNavigator;
sDBFileName: string; sDBTableName: string; sDBPrimaryKey: string);
overload;
begin
  TDatabases.DatabaseClose(Sqlite3Dataset);
  Sqlite3Dataset.FileName := sDBFileName;
  Sqlite3Dataset.TableName := sDBTableName;
  Sqlite3Dataset.PrimaryKey := sDBPrimaryKey;

  DataSource.DataSet := Sqlite3Dataset;
  if not (DBNavigator = nil) then DBNavigator.DataSource :=
DataSource;
  Sqlite3Dataset.Open;

  Sqlite3Dataset.Active := True;
  Sqlite3Dataset.SaveOnClose := True;
  Sqlite3Dataset.SaveOnRefetch := True;
end;

class procedure TDatabases.DatabaseClose(Sqlite3Dataset:
TSqlite3Dataset); overload;
begin
  Sqlite3Dataset.Active := False;
  Sqlite3Dataset.Close;
end;

class procedure TDatabases.DatabaseOpen(SQLite3Connection:
TSQLite3Connection; SQLTransaction: TSQLTransaction; DataSource:
TDataSource; SQLQuery: TSQLQuery; sDBFileName: string); overload;
begin
  SQLite3Connection.DatabaseName := sDBFileName;
  SQLite3Connection.Connected := True;
  SQLTransaction.Database := SQLite3Connection;
  SQLQuery.Database := SQLite3Connection;
  DataSource.DataSet := SQLQuery;
end;

class procedure TDatabases.DatabaseClose(SQLite3Connection:
TSQLite3Connection; SQLTransaction: TSQLTransaction; DataSource:
TDataSource; SQLQuery: TSQLQuery); overload;
begin
  SQLQuery.Close;
  SQLTransaction.CloseDataSets;
  SQLTransaction.Active := False;
  SQLite3Connection.CloseDataSets;
  SQLite3Connection.CloseTransactions;
  SQLite3Connection.Close;
end;


class procedure TDatabases.DatabaseOpenQuery(Sqlite3Dataset:
TSqlite3Dataset; DataSource: TDataSource; DBNavigator: TDBNavigator;
sDBFileName: string; sDBTableName: string; sDBPrimaryKey: string;
sQuery: string);
begin
  TDatabases.DatabaseClose(Sqlite3Dataset);
  Sqlite3Dataset.FileName := sDBFileName;
  Sqlite3Dataset.TableName := sDBTableName;
  Sqlite3Dataset.PrimaryKey := sDBPrimaryKey;
  DataSource.DataSet := Sqlite3Dataset;
  Sqlite3Dataset.SQL := sQuery;

  //  DBGrid1.DataSource := DataSource;
  if not (DBNavigator = nil) then DBNavigator.DataSource :=
DataSource;
  Sqlite3Dataset.ExecSQL;
  Sqlite3Dataset.Open;

  Sqlite3Dataset.Active := True;
  Sqlite3Dataset.SaveOnClose := True;
  Sqlite3Dataset.SaveOnRefetch := True;
end;


end.

After in your Form, create the visual components, but no edit any
properties...  The code does it for you...

The followed code has default names of visual controls, you can change
if you need.

Code: [Select]unit Unit1;

{$mode objfpc}{$H+}

interface

uses
  UnitDatabases,
  Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs;

type
  TForm1 = class(TForm)
    Button1: TButton;
    Button2: TButton;
    Datasource1: TDatasource;
    DBEdit1: TDBEdit;
    DBEdit2: TDBEdit;
    DBEdit3: TDBEdit;
    DBNavigator1: TDBNavigator;
    Sqlite3Dataset1: TSqlite3Dataset; 
    procedure Button1Click(Sender: TObject); 
    procedure Button2Click(Sender: TObject); 
    procedure FormCreate(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

procedure TfrmMaterialesActualizacion.FormCreate(Sender: TObject);
begin
  DBEdit1.DataField := 'ID_BOOK';
  DBEdit2.DataField := 'NAME';
  DBEdit3.DataField := 'AUTHOR';
  Edit1.MaxLength := 10;
  Edit2.MaxLength := 80;
  Edit3.MaxLength := 80
end;

procedure TForm1.Button1Click(Sender: TObject);
var
  sDBPath, sTableName, sTableIdx, sQuery : String;
begin
  sDBPath := 'Path\To\Your\Database.db';
  sTableName :=  'books';
  sTableIdx := 'id';
  sQuery := 'SELECT * FROM ' + sTableName + ' WHERE ACTIVE="1" ';

  TDatabases.DatabaseOpenQuery(Sqlite3Dataset1, DataSource1,
DBNavigator1, sDBPath , sTableName,  sTableIdx, sQuery);
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  TDatabases.DatabaseClose(Sqlite3Dataset1);
end;         


end.

No really functional Sample (need to add the visual controls), but use
your imagination.

Now you can joke with the code.

if you do not understand something, just ask.

Regards!

P/d: Maybe tomorrow I can complete a functional example to compile,
for now it's all...

-- edit ------------------------------------

If you create the Conection Controls manually, you need all units
required by that Controls  added into  the form in Uses clause. Some
controls need add Components to the project, so first add the visual
control, then delete it from the visual interfase of the Form and
finally, manage as you need the runtime creation of the Conection
Controls.

When you know exactly needed code, modularize all.