Access Tables from Different Database

Hi all, I'm trying to write a program which works with 2 databases and has the following algorithms. Could you please give some leads to me on how to code this in Informix ESQL/C. As I understands that we cannot open connection for 2 database simultenaously. Please advise. 1. foreach entry in "Table A" from "Database 1" 1.1 derive new values based on "Table A".columns 1.2 update/insert new values derived from (b) into "Table B" in "Database 2" Do you think create VIEWs will helps, just my curiosity? Have a nice day, kitming

Kit Ming wrote: > I'm trying to write a program which works with 2 databases and has the > following algorithms. Could you please give some leads to me on how to > code this in Informix ESQL/C. As I understands that we cannot open > connection for 2 database simultenaously. Please advise. I advise you to look at several things. One is the CONNECT statement, and the related SET CONNECTION and DISCONNECT statements. The other area is distibuted queries. In a single SELECT statement, you can collect information from tables in more than one database. > 1. foreach entry in "Table A" from "Database 1" > 1.1 derive new values based on "Table A".columns > 1.2 update/insert new values derived from (b) into "Table B" in > "Database 2" The UPDATE/INSERT is problematic. You could probably use a VIOLATIONS table to record the failures. I think you'd try to insert all the new values and any failures would be logged in the violations tables. It is easier to track failing INSERTs than scanned updates that don't change any rows! > Do you think create VIEWs will helps, just my curiosity? No. As Bob pointed out, the main restriction on using multiple databases in a single SQL operation is that the logging modes of the two databases must be identical. You can't even mixed buffered logging with unbuffered logging, let alone MODE ANSI and unlogged. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"

Kit Ming wrote: > > Hi all, > > I'm trying to write a program which works with 2 databases and has the > following algorithms. Could you please give some leads to me on how to > code this in Informix ESQL/C. As I understands that we cannot open > connection for 2 database simultenaously. Please advise. Absolutely untrue. Not only can you open two databases concurrently in ESQL/C but you can access another database directly without making a second connection. If this will happen in a loop with a queries bouncing back and forth it tends to be faster to make two separate, named, connections, opened with the WITH CONCURRENT TRANSACTIONS clause so that transactions and cursors survive the switching between connections, and switch to a connection to the other database to query it. However if you want to join to the other or fetch once then it does not matter and the added complexity in coding is not worth it. To access a remote table directly just qualify it with its database (and if contained in another instance or on another server its servername) liek this: EXEC SQL DATABASE database_B; EXEC SQL SELECT * FROM database_A:table_a; --or-- EXEC SQL SELECT * FROM database_A@server_A:table_a; ... INSERT INTO table_b VALUES ..... The ESQL manual describes using multiple connections and you can see example code in my dbcopy.ec utility in the utils2_ak package. Art S. Kagel > 1. foreach entry in "Table A" from "Database 1" > 1.1 derive new values based on "Table A".columns > 1.2 update/insert new values derived from (b) into "Table B" in > "Database 2" > > Do you think create VIEWs will helps, just my curiosity? > > Have a nice day, > kitming

Just to supplement Art Kagel's information, and I have direct knowledge of this only in an OnLine environment, you can only maintain one connection if you are using shared memory to connect to the database. To maintain two or more connections you have to use unix domain sockets for a network connection. At our location this is done just by specifying a different name for the server, e.g. if the server "db" uses shared memory, "dbnet" connects to the same database using network connections. "Kit Ming" <kmlow72@hotmail.com> wrote in message news:39922D46.B31B6A9B@hotmail.com... > Hi all, > > I'm trying to write a program which works with 2 databases and has the > following algorithms. Could you please give some leads to me on how to > code this in Informix ESQL/C. As I understands that we cannot open > connection for 2 database simultenaously. Please advise. > > 1. foreach entry in "Table A" from "Database 1" > 1.1 derive new values based on "Table A".columns > 1.2 update/insert new values derived from (b) into "Table B" in > "Database 2" > > Do you think create VIEWs will helps, just my curiosity? > > Have a nice day, > kitming > > >