Recent

Author Topic: [SOLVED] How to make a unit for sql-procedures using TDBGrid etc.?  (Read 3528 times)

Hartmut

  • Hero Member
  • *****
  • Posts: 742
I am a beginner to the LCL. I wrote some sql-programs, which all include the same procedure:

Code: Pascal  [Select][+][-]
  1. procedure TForm1.sql_read(sql: ansistring);
  2.    {executes the sql-command 'sql' and reads all records into the dataset
  3.     'DBGrid1.DataSource.dataset'. If DBGrid1.Visible=True, the result is
  4.     shown in a Grid}
  5.    begin
  6.    try
  7.       SQLQuery1.Close;               // sonst ist SQL-Befehl nicht änderbar
  8.       SQLQuery1.SQL.Text:=sql;       // wanted SQL-Befehl
  9.       DBConnection.Connected:= True; // establish the connection to the DB
  10.       SQLTransaction1.Active:= True; // activate the transaction
  11.       SQLQuery1.Open;                // open the SQL-query
  12.       SQLQuery1.Last; {puts the cursor at the last record in the dataset,
  13.                        fetching all records from the underlying DB if needed}
  14.    except
  15.       on E: EDatabaseError do
  16.          begin
  17.          ShowMessage('DB-Error = ' + E.message);
  18.          end;
  19.    end; {try}
  20.    end; {sql_read}  

and some more doubly procedures. I am looking for a better, re-usable way like a unit to collect all my common sql-procedures. I started with:

Code: Pascal  [Select][+][-]
  1. unit sqlunit;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. interface
  6.  
  7. uses sqlite3conn, sqldb, db, Forms, Dialogs, DBGrids;
  8.  
  9. type
  10.  TFormSQL = class(TForm)
  11.   DataSource1: TDataSource;
  12.   DBConnection: TSQLite3Connection;
  13.   DBGrid1: TDBGrid;
  14.   SQLQuery1: TSQLQuery;
  15.   SQLTransaction1: TSQLTransaction;
  16.  
  17.   procedure sql_read(sql: ansistring);
  18.  
  19.  private { private declarations }
  20.  public  { public declarations }
  21.  end;
  22.  
  23. var FormSQL: TFormSQL;
  24.  
  25. implementation
  26.  
  27. {$R *.lfm}
  28.  
  29. procedure TFormSQL.sql_read(sql: ansistring);
  30.    {executes the sql-command 'sql' and reads all records into the dataset
  31.     'DBGrid1.DataSource.dataset'. If DBGrid1.Visible=True, the result is
  32.     shown in a Grid}
  33.    begin
  34.    try
  35.       SQLQuery1.Close;               // sonst ist SQL-Befehl nicht änderbar
  36.       SQLQuery1.SQL.Text:=sql;       // wanted SQL-Befehl
  37.       DBConnection.Connected:= True; // establish the connection to the DB
  38.       SQLTransaction1.Active:= True; // activate the transaction
  39.       SQLQuery1.Open;                // open the SQL-query
  40.       SQLQuery1.Last; {puts the cursor at the last record in the dataset,
  41.                        fetching all records from the underlying DB if needed}
  42.    except
  43.       on E: EDatabaseError do
  44.          begin
  45.          ShowMessage('DB-Error = ' + E.message);
  46.          end;
  47.    end; {try}
  48.    end; {sql_read}
  49.  
  50. end.

But from now I don't know how to continue, because now I have 2 Forms to handle (1 in unit sqlunit and 1 in my main unit) and don't know how to "connect" both and how to handle them via Object Inspector. 

Procedure sql_read() shall display it's data in a TDBGrid, so I think type TFormSQL must be derived from class TForm. Is that correct?
How can the Object Inspector handle TFormSQL?
All the GUI-controls of my program need a second Form, because I cannot put them in my common sql-unit.
How must I create this second Form in my main unit? Must this main Form be a heritage of TFormSQL?
How can the Object Inspector handle this second Form?

Thanks very much in advance for your help.
« Last Edit: June 13, 2018, 06:52:15 pm by Hartmut »

RayoGlauco

  • Full Member
  • ***
  • Posts: 176
  • Beers: 1567
Re: How to make a unit for sql-procedures using TDBGrid etc.?
« Reply #1 on: June 12, 2018, 12:25:32 pm »
Hello, Hartmut,

You can create several instances of TFormSQL, if you need it. The changes you make in TformSQL design will apply to any TFormSQL you create in your code.

Code: Pascal  [Select][+][-]
  1. var Form1, Form2: TFormSQL;
  2.  
  3. begin
  4.   Form1:= TFormSQL.Create(self);
  5.   // ...
  6.   Form2:= TFormSQL.Create(self);
  7.   // ...
  8. end;
To err is human, but to really mess things up, you need a computer.

Hartmut

  • Hero Member
  • *****
  • Posts: 742
Re: How to make a unit for sql-procedures using TDBGrid etc.?
« Reply #2 on: June 12, 2018, 01:28:09 pm »
If I understand you right, you want to create my "main Form" dynamically. But afaik then I can't create and manage all my GUI-controls on the main Form via the Object Inspector... Or am I wrong? How can this be done?

RayoGlauco

  • Full Member
  • ***
  • Posts: 176
  • Beers: 1567
Re: How to make a unit for sql-procedures using TDBGrid etc.?
« Reply #3 on: June 12, 2018, 02:08:33 pm »
If I understand you right, you want to create my "main Form" dynamically. But afaik then I can't create and manage all my GUI-controls on the main Form via the Object Inspector... Or am I wrong? How can this be done?

You can edit your TformSQL as usual. When you create instances of TformSQL, these will be copies of your TformSQL, with the same controls and code. It will be more complicated if you want each instance to have different controls or code.
« Last Edit: June 12, 2018, 02:11:15 pm by RayoGlauco »
To err is human, but to really mess things up, you need a computer.

RayoGlauco

  • Full Member
  • ***
  • Posts: 176
  • Beers: 1567
Re: How to make a unit for sql-procedures using TDBGrid etc.?
« Reply #4 on: June 12, 2018, 02:31:22 pm »
I created a little project as an example. I attached it. I hope it illustrates this subject (multiple instances of a form). You can go to Unit2 and make any changes you want to the form design or code. To test, execute the project and clic button1 several times.
« Last Edit: June 12, 2018, 02:59:45 pm by RayoGlauco »
To err is human, but to really mess things up, you need a computer.

Hartmut

  • Hero Member
  • *****
  • Posts: 742
Re: How to make a unit for sql-procedures using TDBGrid etc.?
« Reply #5 on: June 12, 2018, 02:58:28 pm »
Thank you, RayoGlauco, for your help. I understood how to create a second Form as an heritage from another Form. But my problem is, that I need all my Buttons and other GUI-controls on the 2nd Form, but this Form is dynamically.

The 1st Form 'TFormSQL' is part of a common sql-unit, which I want to use in different main programs. So I can't put there all the GUI-controls of a main program. They must be put in the 2nd Form. If this is dynamically, I can't use the Object Inspector to create and manage all the GUI-controls of the main program...

RayoGlauco

  • Full Member
  • ***
  • Posts: 176
  • Beers: 1567
Re: How to make a unit for sql-procedures using TDBGrid etc.?
« Reply #6 on: June 12, 2018, 03:08:07 pm »
If I undestand you, you want this TFormSQL to be a form containing a common set of controls and code, where you can add more things to create your main form.
If this is right, you can use TFormSQL as a template: you make a copy and you customize it. I think Lazarus supports application templates, but I don't use this feature, and I don't know how it works.
To err is human, but to really mess things up, you need a computer.

Hartmut

  • Hero Member
  • *****
  • Posts: 742
Re: How to make a unit for sql-procedures using TDBGrid etc.?
« Reply #7 on: June 12, 2018, 04:14:25 pm »
If I undestand you, you want this TFormSQL to be a form containing a common set of controls and code, where you can add more things to create your main form.
Nearly right. I want something like a common sql-unit, which contains common sql-procedures for to use in different programs (all GUI-controls should be created in the main programs). But because procedure sql_read() - see above - needs components like TDBGrid and more, I suppose that I must use here a class which is inherited from TForm, but I'm not sure. If I must, then I have 2 Forms and the described problems e.g. to use the Object Inspector.

Project Templates are complete new to me. What I read and understood is, that they make a static copy of the code. That is what I want to avoid, because if I want to improve a common procedure, I must do this in every copy.

RayoGlauco

  • Full Member
  • ***
  • Posts: 176
  • Beers: 1567
Re: How to make a unit for sql-procedures using TDBGrid etc.?
« Reply #8 on: June 12, 2018, 10:51:33 pm »
I think you can create in your unit functions wich can receive as parameters components or variables of different forms, like this:

Code: Pascal  [Select][+][-]
  1. procedure TFormSQL.sql_read(sql: ansistring; SQLQuery1:TSQLQuery; SQLTransaction1: TSQLTransaction; DBGrid1: TDBGrid);
  2.    {executes the sql-command 'sql' and reads all records into the dataset
  3.     'DBGrid1.DataSource.dataset'. If DBGrid1.Visible=True, the result is
  4.     shown in a Grid}
  5.    begin
  6.    try
  7.       SQLQuery1.Close;               // sonst ist SQL-Befehl nicht änderbar
  8.       SQLQuery1.SQL.Text:=sql;       // wanted SQL-Befehl
  9.       DBConnection.Connected:= True; // establish the connection to the DB
  10.       SQLTransaction1.Active:= True; // activate the transaction
  11.       SQLQuery1.Open;                // open the SQL-query
  12.       SQLQuery1.Last; {puts the cursor at the last record in the dataset,
  13.                        fetching all records from the underlying DB if needed}
  14.    except
  15.       on E: EDatabaseError do
  16.          begin
  17.          ShowMessage('DB-Error = ' + E.message);
  18.          end;
  19.    end; {try}
  20. end; {sql_read}      
« Last Edit: June 12, 2018, 10:55:53 pm by RayoGlauco »
To err is human, but to really mess things up, you need a computer.

Hartmut

  • Hero Member
  • *****
  • Posts: 742
Re: How to make a unit for sql-procedures using TDBGrid etc.?
« Reply #9 on: June 13, 2018, 06:51:47 pm »
A friend helped me and we found a solution which is very easy:
 - keep my common unit 'sqlunit' as it is (see above)
 - keep it together with it's corresponding sqlunit.lfm file
 - in Lazarus create a new project
 - in new 'unit1' add 'sqlunit' to the uses-clause
 - in new 'unit1' edit line "TForm1 = class(TForm)" into "TForm1 = class(TFormSQL)"
 - save the project
 - restart Lazarus
That's all!

Unit1 should now look like:

Code: Pascal  [Select][+][-]
  1. unit Unit1;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. interface
  6.  
  7. uses
  8.  Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs,
  9.  sqlunit;
  10.  
  11. type
  12.  TForm1 = class(TFormSQL) // use inherited Form
  13.   private
  14.   public
  15.  end;
  16.  
  17. var
  18.  Form1: TForm1;
  19.  
  20. implementation
  21.  
  22. {$R *.lfm}
  23.  
  24. end.

In Lazarus now you can see both Forms and manage them both as usual via Object Inspector (see attached screenshot).
After a while unit1.lfm is changed automatically to contain an inherited Form:

Code: Pascal  [Select][+][-]
  1. inherited Form1: TForm1
  2.   Left = 377
  3.   Height = 225
  4.   Top = 429
  5.   Width = 543
  6.   Caption = 'Form1'
  7.   ClientHeight = 225
  8.   ClientWidth = 543
  9. end

That's great! The developers of Lazarus are brilliant! Thanks a lot to them and to RayoGlauco who helped me.
« Last Edit: June 13, 2018, 07:53:47 pm by Hartmut »

 

TinyPortal © 2005-2018