Connecting to multiple databases concurrently
lazarus / 16-Databases

----------------------------------------------------------------------
kribjo · 2011-01-11 14:30

Hi,

I have created a small program that connects to two Oracle Database
servers (physically separated) with ZEOS, to do some queries and
updates (sys admin purposes :-) ) I have done this adding components
at design time so my program is rather static. More servers are
already in place that I need to connect to. I can continue doing this
statically, adding components, develop and recompile for every time
there are new servers. But this is a rather bad implementation and can
be done in a better way. Dynamically add servers at run-time.

However I have not done this before, and wonder if there are any best
practice or good solution for this? Did search for design patterns on
this on Google, but no luck. Can someone please give me an example?
Just to let you know. I have changed my program so that I add the
components at design time, but do all the configuration in Pascal.
This works, and I understand how it works. But still it's a bad static
implementation.

Regards,

Bjørn

----------------------------------------------------------------------
cdbc · 2011-01-11 17:26

Hi
Hmmm... sounds like faktory-pattern to me...
Need more info to go further...

Regards - Benny

----------------------------------------------------------------------
clauslack · 2011-01-12 12:37

Hello
you can create component dynamically
I use this for Firebird.(fragment)
Code: [Select]program example;

{$mode objfpc}{$H+}

uses
  {$IFDEF UNIX}{$IFDEF UseCThreads}
  cthreads,
  {$ENDIF}{$ENDIF}
  {$ifdef linux}
   pthreads,
  {$endif}
  Classes, SysUtils, CustApp,IBConnection,sqldb  ;

type
    TMyApplication = class(TCustomApplication)
    protected
        procedure DoRun; override;
    public
        constructor Create(TheOwner: TComponent); override;
        destructor Destroy; override;
    end;
{ TMyApplication }
procedure TMyApplication.DoRun;
var  IBConnection1,IBexecSQL: TIBConnection;
    SQLQuery1: TSQLQuery;    SQLTransaction1: TSQLTransaction;

begin
    try
        IBConnection1:= TIBConnection.Create(nil);
        IBexecSql   :=  TIBConnection.Create(nil);
            SQLQuery1:=TSQLQuery.Create(nil);
        SQLTransaction1:=TSQLTransaction.Create(nil);
        //
        IBConnection1.databasename:=BaseDeDatos;
        IBexecSql.DatabaseName:=BaseDeDatos;
        IBConnection1.username:=Usuario;
            IBConnection1.password:=Clave;
        IBexecSql.username:=Usuario;IBexecSql.password:=Clave;
        IBConnection1.open; IBexecSql.open;
        //
                //Work with this databases.
                //
    finally
        IBConnection1.free;   IBexecSql.free;  
            SQLQuery1.free;       SQLTransaction1.free;
    end;
    Terminate;
end;

You can use inifiles for save parameters of each database.
Regards

----------------------------------------------------------------------
kribjo · 2011-01-12 13:29

Thanks for taking interest, Benny.

I'll describe my static approach.

1. I created an application with adding two data connections
(ZConnection) on a form.
2. I added datasources and ZQuerys and different db-aware fields and
grids to fulfill my needs.
3. Compiled and run the program for a while.
4. Then new database server had to be added to the program.
5. For this I did step 1-3 again.
6. In the future more servers will be added. How many and when I don't
know.

What I would like to do:
1. Create a INI-file which list the databases
2. Read the INI-file on startup of application.
3. Create database connections, datasources, ZQuery's dynamically for
each database listed in INI-file
4. May be set up db-aware fields and grids in one tabsheet and
dynamically create one tabsheet for
   every database connection created above.

At the momemt my program is manageable, but when more servers will be
added in the future it will be hard to manage. I need a more
professional approach to deal with this.

Hope this info is ok.

Regards,

Bjørn

----------------------------------------------------------------------
kribjo · 2011-01-12 14:23

Hi clauslack,

Thank you for your feedback.
But what do you do when you want to connect to several databases in
your program at the same time? I don't understand how your code allows
it? Can you please explain?

Regards,

----------------------------------------------------------------------
clauslack · 2011-01-12 14:41

I don't use ZEOS

you must create ZConnection dinamically, and then set database, user,
password, etc.
Then create ZQuerys dinamically, etc, etc.

I guest
   Zconnection1 := TZconnection.Create(nil);
   Zquery1 := TZquery (nil);
  Work with this components then
  Zconnection1.Free;
  Zquery1.Free;

See example above,  IBConnection is similar to ZConnection, SQLQuery
is similar to ZQuerys


Regards

----------------------------------------------------------------------
kribjo · 2011-01-12 19:47

Sorry, I wasn't clear enough in my post.

I want to know how to manage several database connections (connect,
disconnect, selects, updates etc) with one code. Maybe you show it in
your code, but I don't understand it. I did read about
TCustomApplication, but I don't understand how this will allow me to
do what I want.

What I'm thinking is more like an array of ZConnection and so on for
other components which are created dynamically, but I'm not sure if an
array is a good solution.

Regards,

Bjørn

----------------------------------------------------------------------
cdbc · 2011-01-12 21:16

Hi

Pseudo code for connecting multiple databases to one app.
It goes somewhat like this:
Code: [Select]  type
          { combo class to collect three classes into one "pointer" }
          TZDbObject = class
          private
            fConnection: TZConnection;
            fQuery: TZQuery;
            fDataSource: TDataSource;
          protected
          public
            constructor Create(aHost,aProtocol,aDb,aUser,aPassw,aSql:
string);
            destructor Destroy; override;
            property Connection: TZConnection read fConnection;
            property Query: TZQuery read fQuery;
            property DataSource: TDataSource read fDataSource;
          end;
          { dynamic array of combo classes }
          TConnectionArray = array of TZDbObject;

        { factories }
        function CreateConnectionArray(var anArray: TConnectionArray):
boolean;
        function DisposeOfConnectionArray(var anArray:
TConnectionArray): boolean;
        function CreateConnectionPages(aPageControl: TPageControl;
                                       anArray: TConnectionArray):
boolean;

        var
          MultipleConn: TConnectionArray;

        implementation

        { factories }
        function CreateConnectionArray(var anArray: TConnectionArray):
boolean;
        var
          NumDb, Idx: cardinal;
        begin
                Result:= false;
          1)    Read the ini-file and count the number of databases to
connect to.
                NumDb:= "ini-count"
          2)    SetLength(anArray,NumDb);
          3)    for Idx:= 0 to NumDb-1 do begin
                  anArray[Idx]:= TZDbObject.Create("params read in
ini-file");
                  anArray[Idx].Connection.Connect;
                  anArray[Idx].Query.Open; { would probably do this
later to speed up app-startup }
                end;
                Result:= true;
        end;

        function DisposeOfConnectionArray(var anArray:
TConnectionArray): boolean;
        var
          Idx: cardinal;
        begin
          Result:= false;
          for Idx:= low(anArray) to high(anArray) do begin
            FreeAndNil(anArray[Idx]); { free the individual objects }
          end;
          SetLength(anArray,0); { release the memory occupied by the
array }
          Result:= true;
        end;

        function CreateConnectionPages(aPageControl: TPageControl;
                                       anArray: TConnectionArray):
boolean;
        var
          Idx: cardinal;
        begin
                Result:= false;
                for Idx:= low(anArray) to high(anArray) do begin
                  { create a new page
                    create controls"Idx" with the new page as Owner &
Parent
                    and connect the lot to the datasource.
                    if you get it right the page-index = Idx }
                  Control.DataSource:= anArray[Idx].DataSource;
                end;
                Result:= true;
        end;

        TZDbObject.Create(aHost,aProtocol,aDb,aUser,aPassw,aSql:
string);
        begin
          inherited Create;
          fConnection:= TZConnection.Create;
          {
                setup the different params here, aHost etc...
          }
          fQuery:= TZQuery.Create;
          fQuery.Connection:= fConnection;
          fQuery.Sql..Text:= aSql;
          fDataSource:= TDataSource.Create;
          fDataSource.DataSet:= fQuery;
        end;

        TZDbObject.Destroy;
        begin
          fQuery.Close;
          fConnection.Close;
          FreeAndNil(fDataSource);
          FreeAndNil(fQuery);
          FreeAndNil(fConnection);
          inherited Destroy;
        end;

        end.
BEWARE: This is untested and just to give you an idea about how you
could solve the problem.
   It probably needs a lot of work, before it plays ball, but hey...

hth
Regards Benny

----------------------------------------------------------------------
cpalx · 2011-01-13 00:40

several servers?
several dababase?

maybe you can create a table with information of the servers/database,
and your app reads all configurations from it. If you add a
server/database your application automatically can connect and read
it.

----------------------------------------------------------------------
kribjo · 2011-01-13 11:14

Thanks Benny,

I will try to adapt your suggestion. Will update the thread on the
result. It will be some days though. :-)

Regards,

Bjørn