From mboxrd@z Thu Jan 1 00:00:00 1970 X-Spam-Checker-Version: SpamAssassin 3.4.4 (2020-01-24) on polar.synack.me X-Spam-Level: X-Spam-Status: No, score=-0.9 required=5.0 tests=BAYES_00,FORGED_GMAIL_RCVD, FREEMAIL_FROM autolearn=no autolearn_force=no version=3.4.4 X-Google-Thread: 103376,4ef7e3d0c7ac8809 X-Google-NewGroupId: yes X-Google-Attributes: gida07f3367d7,domainid0,public,usenet X-Google-Language: ENGLISH,ASCII Path: g2news1.google.com!postnews.google.com!n18g2000vbq.googlegroups.com!not-for-mail From: iloAda Newsgroups: comp.lang.ada Subject: Re: Many Database Connections Date: Fri, 28 Jan 2011 04:10:38 -0800 (PST) Organization: http://groups.google.com Message-ID: References: <99d20061-de06-4383-8675-e1abd9803b0d@o10g2000vbg.googlegroups.com> <88b03959-0d74-487b-9fcc-ab3a0dc6eab9@k14g2000pre.googlegroups.com> NNTP-Posting-Host: 137.194.2.20 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1296216639 12318 127.0.0.1 (28 Jan 2011 12:10:39 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Fri, 28 Jan 2011 12:10:39 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: n18g2000vbq.googlegroups.com; posting-host=137.194.2.20; posting-account=r1a26AoAAAD4nbikbegb_8j7mwzetiwY User-Agent: G2/1.0 X-HTTP-Via: 1.1 proxy.enst.fr:3128 (squid/2.6.STABLE18) X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US) AppleWebKit/534.10 (KHTML, like Gecko) Chrome/8.0.552.237 Safari/534.10,gzip(gfe) Xref: g2news1.google.com comp.lang.ada:16767 Date: 2011-01-28T04:10:38-08:00 List-Id: On Jan 27, 10:37=A0pm, Emmanuel Briot 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=A0pm, Emmanuel Briot 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 :=3D "DB1"; DB1_User : constant String :=3D "smartuser"; DB1_Host : constant String :=3D "127.0.0.1"; DB1_Password : constant String :=3D "SmartPass!"; -- Info about the second DB (DB2) DB2_Name : constant String :=3D "DB2"; DB2_User : constant String :=3D "smartuser"; DB2_Host : constant String :=3D "127.0.0.1"; DB2_Password : constant String :=3D "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 :=3D False; Is_DB2_Connected : Boolean :=3D 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 =3D> DB1_Descr, Database =3D> DB1_Name, User =3D> DB1_User, Host =3D> DB1_Host, Password =3D> DB1_Password, DBMS =3D> GNATCOLL.SQL.Exec.DBMS_Postgresql); DB1_Connection :=3D GNATCOLL.SQL.Exec.Get_Task_Connection (Description =3D> DB1_Descr, Factory =3D> Connection_Factory'Access, Username =3D> "postgres3"); if GNATCOLL.SQL.Exec.Check_Connection (DB1_Connection) then Ada.Text_IO.Put_Line ("Connection to DB1 Base : OK"); Is_DB1_Connected :=3D True; else Ada.Text_IO.Put_Line ("WARNING : Connection to DB1 Base : NOK"); Is_DB1_Connected :=3D 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 =3D> DB2_Descr, Database =3D> DB2_Name, User =3D> DB2_User, Host =3D> DB2_Host, Password =3D> DB2_Password, DBMS =3D> GNATCOLL.SQL.Exec.DBMS_Postgresql); DB2_Connection :=3D GNATCOLL.SQL.Exec.Get_Task_Connection (Description =3D> DB2_Descr, Factory =3D> Connection_Factory'Access, Username =3D> "postgres3"); if GNATCOLL.SQL.Exec.Check_Connection (DB2_Connection) then Ada.Text_IO.Put_Line ("Connection to DB2 Base : OK"); Is_DB2_Connected :=3D True; else Ada.Text_IO.Put_Line ("WARNING : Connection to DB1 Base : NOK"); Is_DB2_Connected :=3D 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 :=3D GNATCOLL.SQL.Exec.Get_DBMS (Desc); begin if DBMS =3D 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 =3D> Connection_Manager.Get_Connection_To_DB1, Query =3D> "INSERT INTO t1 VALUES(1)"); if GNATCOLL.SQL.Exec.Error (Connection_Manager.Get_Connection_To_DB1) /=3D "" 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 =3D> Connection_Manager.Get_Connection_To_DB2, Query =3D> "INSERT INTO t2 VALUES(1)"); if GNATCOLL.SQL.Exec.Error (Connection_Manager.Get_Connection_To_DB2) /=3D "" 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