* Re: Many Database Connections
2011-01-27 21:37 ` Emmanuel Briot
@ 2011-01-28 12:10 ` iloAda
2011-01-28 15:22 ` Emmanuel Briot
0 siblings, 1 reply; 5+ messages in thread
From: iloAda @ 2011-01-28 12:10 UTC (permalink / raw)
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
^ permalink raw reply [flat|nested] 5+ messages in thread