Re: DB Definition Reference

Mark (and Kerry!) This is actually a sort of FAQ, but I can't find any reference to the issue in the FAQ version 2.2 (to be precise, I couldn't find the keyword non-procedural in the FAQ), so here goes with mildly reworked answers which have been given previously... Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> --------------------------------------------------------------------------- >From: mrainis@interaccess.com (Mark R. Ainis) >Date: Wed, 14 Aug 1996 08:04:08 >X-Informix-List-Id: <news.27112> > >We have an application [written in] Informix 4gl [which has] record [and >variable] declarations that reference current database table definitions >using the LIKE key word. The problem [is that] the compiler must know the >database name explicitly specified in the first line of the code in order >to define the record definitions. Is there a known way of eliminating the >hard-coded database name in the source such as passing the name to >compiler during compilation? The short answer to the question is "No". The database referenced in the source code must be available at compile time. The normal reason for asking the question is that the program needs to be run against different databases at run-time. This is readily achievable if the databases have the same tables, and the tables have the same definitions. Further, the technique for achieving this does not require modifications to most of the source code. If the database structures are different, then you do have a problem which is probably best handled by invoking a shell script in place of c4gl (or fglpc). This script edits the source code before submitting it to the normal (c4gl or fglpc) compiler, replacing the standard database name with the required database name. Error recovery can be tricky in this scenario, but it is otherwise feasible. Let us assume that the compile-time and run-time databases have the same structure, at least for the tables which the program references. Procedural versus Non-procedural Database Statements ---------------------------------------------------- When the DATABASE statement precedes any variable declarations or any functions or reports or MAIN, it a a non-procedural database statement. When the DATABASE statement is in the body of a function or MAIN (it shouldn't normally occur in a report), it is a procedural database statement, and is an executable statement in the same way as any other SQL statement. Only non-procedural database statements affect the compilation of a program. At compile time, if the source file does not contain MAIN, then the database is used to extract the type information for records and variables defined using the LIKE keyword. It does not matter what database is used at runtime -- it is conventionally the same database as was used at compile time, but nothing in the code ensures that this is the case. If the actual run-time table structures differ from the compile-time structures, you may get unexpected results (extra conversions, conversion failures, unset variables, and other nasties), but that's all. If the file contains a non-procedural database statement and also MAIN, then the compiler inserts an implicit procedural DATABASE statement at the start of the program before the first executable statement in the program (which also means before any WHENEVER ERROR statement). So, when the program is run, the first thing which happens is that the (compile-time) database is opened. On the other hand, if the MAIN is compiled without a non-procedural database statement, then no database is opened automatically, and the programmer can arrange to open any database required by any mechanism desired -- usually by executing an explicit, procedural DATABASE statement. Note that there is no need to use the non-procedural database statement if the code does not use LIKE to declare records or variables and if you do not want the MAIN to automatically select the database when it is started. Choosing the Database at Run-Time. ---------------------------------- There are a couple of ways of handling the selection of the database at run time. You could use the following code: DATABASE Compile_dbs MAIN CALL select_database() CALL other_work() END MAIN FUNCTION function01() DEFINE thingy RECORD LIKE Thingy.* ... END FUNCTION This starts the program, opens Compile_dbs, and then closes it and opens some other database at run time. The overhead here is that the Compile_dbs must exist at run time, and it is opened unnecessarily. Alternatively, you can create a miniscule module (source file) for the MAIN: MAIN CALL select_database() CALL other_work() END This is my preferred strategy. This source file has no DATABASE statement anywhere, nor any functions other than MAIN. It does not need to refer to any globals either. When this is run, no database is selected automatically because there is no non-procedural DATABASE statement. Everything else can be compiled with reference to Compile_dbs; at run-time, they will actually use whatever database has been selected by the select_database() function. The Compile_dbs does not need to exist at run-time. Note that there was an oddity in pre-4.0 versions of ESQL/C which meant that if "$DATABASE $dbname;" didn't find the database, there was no error reported (ie, sqlca.sqlcode == 0). To really test for whether the database was opened successfully, it was a good idea to check, using something like "$SELECT Tabid FROM Systables WHERE Tabid = 1;" and test sqlca.sqlcode after that too. This ensured that there really was a database. (Systables has Tabid = 1). I am not certain whether this was corrected in 4.00 or 4.10. Also note that you should always close a database before opening a new one, because if the old database was being accessed remotely, it was not implicitly closed. You also need to ensure that if the database has transactions, no transaction is active (eg do ROLLBACK WORK followed by CLOSE DATABASE). Some example code follows. MAIN would call select_database(), and any code might call get_database() to find the actual name of the database in use. -- Synopsis: Open the database indicated by environment variable -- PROJECT_DATABASE, defaulting to the name "project". Note that -- function fatal_error() is defined separately. You can make many -- minor variations on this theme, but somewhere, the default database -- name (probably the one referred to by the non-procedural database -- statements) has to be defined as a literal string. Preerving the -- sqlca.sqlawarn information is important as it tells you what sort -- of database you are connected to. DEFINE current_database CHAR(18) FUNCTION select_database() DEFINE dbase CHAR(18), err_msg CHAR(60), op_status INTEGER LET dbase = fgl_getenv("PROJECT_DATABASE") IF dbase IS NULL THEN LET dbase = "project" END IF LET op_status = op