Re: How do I use 2 DB at once?

I think I've found the secret to using the 2 DB at once with CONNECT/ SET CONNECTION statements. The problem was: I had one database (alpha) open and didn't want to close it (PREPARED statements, etc). I wanted to get data from another database (beta) without using an engine name, hoping DBPATH would find it. A "SELECT * FROM beta:tablename" doesn't use DBPATH. I could access the database with SELECT, but I would have to keep an explicit enginename someplace. The two recommendations I got were: 1) Use the form "SELECT * FROM beta@enginename:tablename". 2) Create a synonym to point to the table that uses the beta@enginename format. Either way, I'm putting the enginename *someplace*. If it changes, you have to root out everyplace you've put that enginename and change it. Kind of like renaming the /bin directory. How many apps hard-code a /bin command? Can you imagine the impact on a UNIX system? In this case, synonyms would probably be easier than recompiling code, but still a real pain. Somebody has to remember or have an up-to-date list of all the synonym links on all the systems in your network and then take the time to re-do each and every one of them. You can probably guarantee that after several years and systems, no such thing as an up-to-date list will exist. So your apps don't work and your users suffer. Since all of our users have a generic, global login file to set up their environmental variables, my goal was just to make a change to the DBPATH setting in this login file. If an enginename changes, I edit one line of one file and everything works fine once the users log out/back in. That's IF I can get my programs to use DBPATH instead of hard-coding or storing an enginename someplace. Well, this seems to be the solution. /* The DATABASE statement makes 'alpha' the default (implicit) database */ EXEC SQL DATABASE alpha; /* This will open the 'beta' database, but keep the 'alpha' database in a suspended state. When it looks for the 'beta' database it uses DBPATH (unlike a SELECT * from beta:tablename statement). */ EXEC SQL CONNECT TO 'beta'; /* Now my connections are set up and I can jump back and forth between databases with a SET CONNECTION statement. At this point, I'm connected to 'beta'. */ /**** Do anything I need to with the 'beta' database here *****/ /* After this statement, I'm back to my 'alpha' database */ EXEC SQL SET CONNECTION DEFAULT; /* If I need to go back to my 'beta' database, I just do this */ EXEC SQL SET CONNECTION 'beta'; /**** Do anything I need to with the 'beta' database here *****/ /* And, after this statement, I'm back to my 'alpha' database again */ EXEC SQL SET CONNECTION DEFAULT; There's more about this in the 7.1 SQL Syntax Guide. Some other neat variations on this lets you do things like: * alias your connection names * keep a transaction going on one connection while you connect to another database * connect to more than 2 databases at once. Some restrictions and gotchas are: * Apparently you can only have one connection to your local engine if you're using shared-memory for OnLine. This is a problem if your DBPATH happens to find your 'beta' database on the same engine as your 'alpha' database. * You can cause yourself to deadlock since you can start transactions in multiple databases. Thank you for all your suggestions. (And I still think that SELECT not using DBPATH is a problem and/or bug). __________________________________________________________________________ Joel Schumacher JCPenney Co. - UNIX Network Systems jschumac@uns-dv1.jcpenney.com 12700 Park Central Pl (214) 591-7543 Dallas TX 75251