Many Database Connections

Many Database Connections

[Elie Zgheib]

Hello everybody!

I am trying to write a code that maintains 2 connections to 2
different databases (on the same server).
The databases are is Postgre and I am using GnatColl to interface with
the servers and execute queries.

The problem I am having is that after executing a query on one of the
databases by using connection, the subsequent queries will be executed
to that same database even if I am using different connections.

To clarify this, imagine that I have 2 Databases, DB1 and DB2. I have
2 Gnatcoll databases connectors pointing to each one, lets say DB1_Con
for the connector to DB1, and DB2_Con for the other. I execute the
query on DB1 by calling Execute (DB1_Con, "INSERT t1 ...."), then call
the procedure Execute (DB2_Con, "INSERT t2..."). The second call to
Execute will fail because it tried to insert in DB1 and since DB1 does
not have a t2 table, it fails!!!

Has anybody experienced this? or has anybody successfully done this?
if so how? Any advise on what to look for while doing this sort of
stuff?

Thanks guys

Elie

Re: Many Database Connections

[Emmanuel Briot]

> To clarify this, imagine that I have 2 Databases, DB1 and DB2. I have
> 2 Gnatcoll databases connectors pointing to each one, lets say DB1_Con
> for the connector to DB1, and DB2_Con for the other. I execute the
> query on DB1 by calling Execute (DB1_Con, "INSERT t1 ...."), then call
> the procedure Execute (DB2_Con, "INSERT t2..."). The second call to
> Execute will fail because it tried to insert in DB1 and since DB1 does
> not have a t2 table, it fails!!!

Please provide an actual reproducer, since it all depends how you
create
the connections. For instance, we need to see what you use for the
factory
in Get_Task_Connection (or maybe you do not use that to create the
connection)

Emmanuel

Re: Many Database Connections

[iloAda]

On Jan 27, 10:37 pm, Emmanuel Briot <briot.emman...@gmail.com> wrote:
> > To clarify this, imagine that I have 2 Databases, DB1 and DB2. I have
> > 2 Gnatcoll databases connectors pointing to each one, lets say DB1_Con
> > for the connector to DB1, and DB2_Con for the other. I execute the
> > query on DB1 by calling Execute (DB1_Con, "INSERT t1 ...."), then call
> > the procedure Execute (DB2_Con, "INSERT t2..."). The second call to
> > Execute will fail because it tried to insert in DB1 and since DB1 does
> > not have a t2 table, it fails!!!
>
> Please provide an actual reproducer, since it all depends how you
> create
> the connections. For instance, we need to see what you use for the
> factory
> in Get_Task_Connection (or maybe you do not use that to create the
> connection)
>
> Emmanuel

On Jan 27, 10:37 pm, Emmanuel Briot <briot.emman...@gmail.com> wrote:
> > To clarify this, imagine that I have 2 Databases, DB1 and DB2. I have
> > 2 Gnatcoll databases connectors pointing to each one, lets say DB1_Con
> > for the connector to DB1, and DB2_Con for the other. I execute the
> > query on DB1 by calling Execute (DB1_Con, "INSERT t1 ...."), then call
> > the procedure Execute (DB2_Con, "INSERT t2..."). The second call to
> > Execute will fail because it tried to insert in DB1 and since DB1 does
> > not have a t2 table, it fails!!!
>
> Please provide an actual reproducer, since it all depends how you
> create
> the connections. For instance, we need to see what you use for the
> factory
> in Get_Task_Connection (or maybe you do not use that to create the
> connection)
>
> Emmanuel

Hello,

I know it too much asking you to see my code, but I am hitting a dead
end here.
I do use the Get_Task_Connection to create the connection, and I've
looked into the GNATCOLL library and it seems to me that it has a
global variable (DB_Attributes) that holds some info about the DB, the
first call to the Get_Task_Connection function will give a value to
DB_Attributes, and all subsequent calls to the Get_Task_Connection
function will return the value of DB_Attributes, even if we try to
connect to a new Database (The only solution I found till now is to
open a connection, insert values then directly close the connection,
but this isn't a good practice!)

Here is my code:

****************************************************************************************************
File : Connection_Manager.ads:
------------------------------


with GNATCOLL.SQL;
with GNATCOLL.SQL.Exec;


package Connection_Manager is
   --  Info about the first DB (DB1)
   DB1_Name     : constant String := "DB1";
   DB1_User     : constant String := "smartuser";
   DB1_Host     : constant String := "127.0.0.1";
   DB1_Password : constant String := "SmartPass!";

   --  Info about the second DB (DB2)
   DB2_Name     : constant String := "DB2";
   DB2_User     : constant String := "smartuser";
   DB2_Host     : constant String := "127.0.0.1";
   DB2_Password : constant String := "SmartPass!";



   --  A function that returns the connection to the first DB
   function Get_Connection_To_DB1
     return GNATCOLL.SQL.Exec.Database_Connection;

      --  A function that returns the connection to the second DB
   function Get_Connection_To_DB2
     return GNATCOLL.SQL.Exec.Database_Connection;


private
   DB1_Connection : GNATCOLL.SQL.Exec.Database_Connection;
   DB2_Connection : GNATCOLL.SQL.Exec.Database_Connection;

   DB1_Descr : GNATCOLL.SQL.Exec.Database_Description;
   DB2_Descr : GNATCOLL.SQL.Exec.Database_Description;

   Is_DB1_Connected : Boolean := False;
   Is_DB2_Connected : Boolean := False;


   --  An internal function that creates the connection to the
database
   function Connection_Factory
     (Desc : GNATCOLL.SQL.Exec.Database_Description)
      return GNATCOLL.SQL.Exec.Database_Connection;

end Connection_Manager;
***********************************************************

File: :Connection_Manager.adb:
------------------------------

with GNATCOLL.SQL.Postgres;
with Ada.Text_IO;

package body Connection_Manager is

   --------------------------------------
   -- Get_Connection_To_Device_Mgmt_DB --
   --------------------------------------
   --  If connection is not OK, connects to database and returns it
   function Get_Connection_To_DB1
     return GNATCOLL.SQL.Exec.Database_Connection
   is
   begin

      if not Is_DB1_Connected then
         GNATCOLL.SQL.Exec.Setup_Database
           (Description => DB1_Descr,
            Database      => DB1_Name,
            User          => DB1_User,
            Host          => DB1_Host,
            Password      => DB1_Password,
            DBMS          => GNATCOLL.SQL.Exec.DBMS_Postgresql);

         DB1_Connection := GNATCOLL.SQL.Exec.Get_Task_Connection
           (Description  => DB1_Descr,
            Factory      => Connection_Factory'Access,
            Username     => "postgres3");


         if GNATCOLL.SQL.Exec.Check_Connection (DB1_Connection) then
            Ada.Text_IO.Put_Line ("Connection to DB1 Base : OK");
            Is_DB1_Connected := True;
         else
            Ada.Text_IO.Put_Line
              ("WARNING : Connection to DB1 Base : NOK");
            Is_DB1_Connected := False;
         end if;

      end if;

      return DB1_Connection;

   end Get_Connection_To_DB1;

   --------------------------------------
   -- Get_Connection_To_Data_Mgmt_DB --
   --------------------------------------
   --  If connection is not OK, connects to database and returns it
   function Get_Connection_To_DB2
     return GNATCOLL.SQL.Exec.Database_Connection
   is
   begin
      if not Is_DB2_Connected then
         GNATCOLL.SQL.Exec.Setup_Database
           (Description => DB2_Descr,
            Database      => DB2_Name,
            User          => DB2_User,
            Host          => DB2_Host,
            Password      => DB2_Password,
            DBMS          => GNATCOLL.SQL.Exec.DBMS_Postgresql);

         DB2_Connection := GNATCOLL.SQL.Exec.Get_Task_Connection
           (Description  => DB2_Descr,
            Factory      => Connection_Factory'Access,
            Username     => "postgres3");


         if GNATCOLL.SQL.Exec.Check_Connection (DB2_Connection) then
            Ada.Text_IO.Put_Line ("Connection to DB2 Base : OK");
            Is_DB2_Connected := True;
         else
            Ada.Text_IO.Put_Line
              ("WARNING : Connection to DB1 Base : NOK");
            Is_DB2_Connected := False;
         end if;

      end if;

      return DB2_Connection;

   end Get_Connection_To_DB2;


   ------------------------
   -- Connection_Factory --
   ------------------------

   function Connection_Factory
     (Desc : GNATCOLL.SQL.Exec.Database_Description)
      return GNATCOLL.SQL.Exec.Database_Connection
   is
      DBMS : constant String := GNATCOLL.SQL.Exec.Get_DBMS (Desc);
   begin
      if DBMS = GNATCOLL.SQL.Exec.DBMS_Postgresql then
         Ada.Text_IO.Put_Line ("Making connection to Postgres
database...");
         return GNATCOLL.SQL.Postgres.Build_Postgres_Connection
(Desc);
      else
         return null;
      end if;
   end Connection_Factory;

end Connection_Manager;

***********************************************************
File test.adb (an example of how I use the connection manager):
---------------------------------------------------------------

with Connection_Manager;
with GNATCOLL.SQL.Exec;
with Ada.Text_IO;
procedure test
is

begin



   GNATCOLL.SQL.Exec.Execute
     (Connection => Connection_Manager.Get_Connection_To_DB1,
      Query      => "INSERT INTO t1 VALUES(1)");


   if GNATCOLL.SQL.Exec.Error
     (Connection_Manager.Get_Connection_To_DB1) /= "" then
      Ada.Text_IO.Put_Line
        ("Error in Insertion: " & GNATCOLL.SQL.Exec.Error
           (Connection_Manager.Get_Connection_To_DB1) & " for DB: " &
GNATCOLL.SQL.Exec.Get_Database
           (GNATCOLL.SQL.Exec.Get_Description
(Connection_Manager.Get_Connection_To_DB1)));
   end if;

   GNATCOLL.SQL.Exec.Commit_Or_Rollback
(Connection_Manager.Get_Connection_To_DB1);


   GNATCOLL.SQL.Exec.Execute
     (Connection => Connection_Manager.Get_Connection_To_DB2,
      Query      => "INSERT INTO t2 VALUES(1)");
   if GNATCOLL.SQL.Exec.Error
     (Connection_Manager.Get_Connection_To_DB2) /= "" then
      Ada.Text_IO.Put_Line
        ("Error in Insertion: " & GNATCOLL.SQL.Exec.Error
           (Connection_Manager.Get_Connection_To_DB2) & " for DB: " &
GNATCOLL.SQL.Exec.Get_Database
           (GNATCOLL.SQL.Exec.Get_Description
(Connection_Manager.Get_Connection_To_DB2)));
   end if;
   GNATCOLL.SQL.Exec.Commit_Or_Rollback
(Connection_Manager.Get_Connection_To_DB2);



end test;
**********************************************************************************************************

When I execute this code, it tells me "No "t2" table was found in the
Database", but there is a t2 table in the DB2 database.

Thanks a lot guys

Elie

Re: Many Database Connections

[Emmanuel Briot]

> I do use the Get_Task_Connection to create the connection, and I've
> looked into the GNATCOLL library and it seems to me that it has a
> global variable (DB_Attributes) that holds some info about the DB, the
> first call to the Get_Task_Connection function will give a value to
> DB_Attributes, and all subsequent calls to the Get_Task_Connection
> function will return the value of DB_Attributes, even if we try to
> connect to a new Database (The only solution I found till now is to
> open a connection, insert values then directly close the connection,
> but this isn't a good practice!)

DB_Attributes is not a global variable (I don't think gnatcoll is
using any such variable).
It is a package which allows us to store task attributes (ie for each
task we store which is the associated connection -- to match the
semantics of Get_Task_Connection).
So indeed the second call to Get_Task_Connection will return the first
connection, since you have a single task.
In your case, you do not want to use Get_Task_Connection since you
want multiple connections in the same task.

So use something like:

  DB1 := Connection_Factory (DB1_Descr);
  Reset_Connection (DB1, DB1_Descr);
  DB2 := Connection_Factory (DB2_Descr);
  Reset (DB2, DB2_Descr);

and then you have two independent connections that you should be able
to use from the same thread.

Re: Many Database Connections

[iloAda]

On Jan 28, 4:22 pm, Emmanuel Briot <briot.emman...@gmail.com> wrote:
> > I do use the Get_Task_Connection to create the connection, and I've
> > looked into the GNATCOLL library and it seems to me that it has a
> > global variable (DB_Attributes) that holds some info about the DB, the
> > first call to the Get_Task_Connection function will give a value to
> > DB_Attributes, and all subsequent calls to the Get_Task_Connection
> > function will return the value of DB_Attributes, even if we try to
> > connect to a new Database (The only solution I found till now is to
> > open a connection, insert values then directly close the connection,
> > but this isn't a good practice!)
>
> DB_Attributes is not a global variable (I don't think gnatcoll is
> using any such variable).
> It is a package which allows us to store task attributes (ie for each
> task we store which is the associated connection -- to match the
> semantics of Get_Task_Connection).
> So indeed the second call to Get_Task_Connection will return the first
> connection, since you have a single task.
> In your case, you do not want to use Get_Task_Connection since you
> want multiple connections in the same task.
>
> So use something like:
>
>   DB1 := Connection_Factory (DB1_Descr);
>   Reset_Connection (DB1, DB1_Descr);
>   DB2 := Connection_Factory (DB2_Descr);
>   Reset (DB2, DB2_Descr);
>
> and then you have two independent connections that you should be able
> to use from the same thread.

Thanks Emmanuel !!
It worked like a charm!!
I think that the final solution whould be a little smarter because the
final solution might very well be multithreaded!!

Thanks anyway!

ilo