【正文】 system resources for it, and optionally assigns operational parameters for the databases must be closed before a program ends. A database can be closed by using DISCONNECT, or by appending the RELEASE option to the final COMMIT or ROLLBACK in a program. Declaring a database Before a database can be opened and used in a program, it must first be declared with SET DATABASE to: CHAPTER 3 WORKING WITH DATABASES. Establish a database handle. Associate the database handle with a database file stored on a local or remote database handle is a unique, abbreviated alias for an actual database name. Database handles are used in subsequent CONNECT, COMMIT RELEASE, and ROLLBACK RELEASE statements to specify which databases they should affect. Except in dynamic SQL (DSQL) applications, database handles can also be used inside transaction blocks to qualify, or differentiate, table names when two or more open databases contain identically named tables. Each database handle must be unique among all variables used in a program. Database handles cannot duplicate hostlanguage reserved words, and cannot be InterBase reserved following statement illustrates a simple database declaration: EXEC SQL SET DATABASE DB1 = ??。 This database declaration identifies the database file, , as a database the program uses, and assigns the database a handle, or alias, DB1. If a program runs in a directory different from the directory that contains the database file, then the file name specification in SET DATABASE must include a full path name, too. For example, the following SET DATABASE declaration specifies the full path to : EXEC SQL SET DATABASE DB1 = ?/interbase/examples/?。 On a Windows work that uses the Netbeui protocol, specify the path as follows: EXEC SQL SET DATABASE DB1 = ?//venus/C:/Interbase/examples/?。 EXEC SQL SET DATABASE DB1 = ??。 EXEC SQL DECLARE EIDMATCH CURSOR FOR SELECT EMPNO INTO :empid FROM WHERE EMPNO = :matchid。 IMPORTANT The file specification that follows the COMPILETIME keyword must always be a hardcoded, quoted string. DECLARING A DATABASE EMBEDDED SQL GUIDE 39 When SET DATABASE uses the COMPILETIME clause, but no RUNTIME clause, and does not specify a different database file specification in a subsequent CONNECT statement, the same database file is used both for preprocessing and run time. To specify different preprocessing and runtime databases with SET DATABASE, use both the COMPILETIME and RUNTIME clauses. 4Using the RUNTIME clause When a database file is specified for use during preprocessing, SET DATABASE can specify a different database to use at run time by including the RUNTIME keyword and a runtime file specification: EXEC SQL SET DATABASE EMP = COMPILETIME ?? RUNTIME ??。 . . . printf(Enter the desired database name, including node and path):\n)。 EXEC SQL SET DATABASE EMP = COMPILETIME ?? RUNTIME : db_name。 The EXTERN keyword is used in a multimodule program to signal that SET DATABASE in one module is not an actual declaration, but refers to a declaration made in a different module. Gpre uses this information during preprocessing. The following example illustrates the use of the EXTERN keyword: EXEC SQL SET DATABASE EMP = EXTERN ??。 EXEC SQL CONNECT ?? USER ?JAMES? PASSWORD ?U4EEAH?。 EXEC SQL SET DATABASE DB2 = ??。 EXEC SQL CONNECT DB2。 . . . printf(?Enter the desired database name, including node and path):\n?)。 . . . EXEC SQL CONNECT :fname。 Here, handle is a hardcoded database handle supplied by the programmer, dbname is a quoted, hardcoded database name used by gpre during preprocessing. 2. Prompt the user for a database to open. 3. Store the database name entered by the user in a hostlanguage variable. 4. Use the handle to open the database, associating the hostlanguage variable with the handle using the following CONNECT syntax: EXEC SQL CONNECT : variable AS handle。 . . . EXEC SQL SET DATABASE DB1 = ??。 gets(fname)。 . . . CHAPTER 3 WORKING WITH DATABASES 44 INTERBASE 6 In this example, SET DATABASE provides a hardcoded database file name for preprocessing with gpre. When a user runs the program, the database specified in the variable, fname, is used instead. 4Using a hardcoded database names IN SINGEDATABASE PROGRAMS In a singledatabase program that omits SET DATABASE, CONNECT must contain a hardcoded, quoted file name in the following format: EXEC SQL CONNECT ?[ host[ path]] filename?。 Note Host syntax is specific to each server platform. IMPORTANT A program that accesses multiple databases cannot use this form of CONNECT. IN MULTIDATABASE PROGRAMS A program that accesses multiple databases must declare handles for each of them in separate SET DATABASE statements. These handles must be used in subsequent CONNECT statements to identify specific databases to open: . . . EXEC SQL SET DATABASE DB1 = ??。 EXEC SQL CONNECT DB1。 . . . Later, when the program closes these databases, the database handles are no longer in use. These handles can be reassigned to other databases by hardcoding a file name in a subsequent CONNECT statement. For example, OPENING A DATABASE EMBEDDED SQL GUIDE 45 . . . EXEC SQL DISCONNECT DB1, DB2。 . . . Additional CONNECT syntax CONNECT supports several formats for opening databases to provide programming flexibility. The following table outlines each possible syntax, provides descriptions and examples, and indicates whether CONNECT can be used in programs that access single or multiple databases: For a plet