Re: using environment value in DATABASE statement

>From: mitch@lexmark.com (Gary A. Mitchell) >Date: Fri, 27 Oct 1995 14:07:03 GMT >X-Informix-List-Id: <news.18324> > >In article <46p030$lj4@natasha.rmii.com>, > scdickey@corbsys.com (Stephen C. Dickey) wrote: >>I am trying to connect to a specific database that is defined in an >>environment variable called "DB_NAME". >> >>The following syntax gives a compiler error saying fgl_getenv is not a >>database. >> >>DATABASE fgl_getenv("DB_NAME") >[snip] > >This won't work for the database statement in the header of the program Gary is certainly correct here. >but you can use this for the database statement in the body of the >program. No, you can't. The arguments to any SQL statement must be simple variables, not the results of an expression. Hence the code Gary supplies does not compile; you have to use: DATABASE test_db MAIN DEFINE dbname CHAR(40) LET dbname = fgl_getenv("DB_NAME") CLOSE DATABASE DATABASE dbname ... END MAIN >I think you're doing something very similar to what my application >does (access identical databases under different names). The database >statement in the header can't contain the variable but you can put >it in the program. > >DATABASE test_db > >MAIN > CLOSE DATABASE > DATABASE fgl_getenv("DB_NAME") > ..... >END MAIN This general technique certainly works once a variable is used for the database name, but the compile-time database must also exist in the run-time database environment. This is not always useful. The best way around that is to code the file with MAIN as: MAIN CALL select_database() CALL real_application() END MAIN Those four lines, and no others. Specifically -- no DATABASE statements at all. This program is not started with a database connection. The select_database() function can still do a CLOSE DATABASE (within WHENEVER ERROR CONTINUE / WHENEVER ERROR STOP to ignore any error), and then open the required database. The real_application() code is in another file which is compiled with the non-procedural DATABASE statement in place. Note that this avoids opening a database only to close it again, and avoids the requirement that the compile-time database must exist in the run-time environment. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>