1、Working with Databases This chapter describes how to use SQL statements in embedded applications to control databases. There are three database statements that set up and open databases for access: SET DATABASE declares a database handle, associates the handle with an actual database file, and optio
2、nally assigns operational parameters for the database. SET NAMES optionally specifies the character set a client application uses for CHAR, VARCHAR, and text Blob data. The server uses this information to transliterate from a databases default character set to the clients character set on SELECT ope
3、rations, and to transliterate from a client applications character set to the database character set on INSERT and UPDATE operations. g CONNECT opens a database, allocates system resources for it, and optionally assigns operational parameters for the database.All databases must be closed before a pr
4、ogram 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. Est
5、ablish a database handle. Associate the database handle with a database file stored on a local or remote node.A 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 whic
6、h 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 u
7、sed in a program. Database handles cannot duplicate host-language reserved words, and cannot be InterBase reserved words.The following statement illustrates a simple database declaration: EXEC SQL SET DATABASE DB1 = employee.gdb； This database declaration identifies the database file, employee.gdb,
8、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 DATABAS
9、E declaration specifies the full path to employee.gdb: EXEC SQL SET DATABASE DB1 = /interbase/examples/employee.gdb； If a program and a database file it uses reside on different hosts, then the file name specification must also include a host name. The following declaration illustrates how a Unix ho
10、st name is included as part of the database file specification on a TCP/IP network: EXEC SQL SET DATABASE DB1 = jupiter:/usr/interbase/examples/employee.gdb； On a Windows network that uses the Netbeui protocol, specify the path as follows: EXEC SQL SET DATABASE DB1 = /venus/C:/Interbase/examples/emp
11、loyee.gdb； DECLARING A DATABASE EMBEDDED SQL GUIDE 37 Declaring multiple databases An SQL program, but not a DSQL program, can access multiple databases at the same time. In multi-database programs, database handles are required. A handle is used to: 1. Reference individual databases in a multi-data
12、base transaction. 2. Qualify table names. 3. Specify databases to open in CONNECT statements. Indicate databases to close with DISCONNECT, COMMIT RELEASE, and ROLLBACK RELEASE. DSQL programs can access only a single database at a time, so database handle use is restricted to connecting to and discon
13、necting from a database. In multi-database programs, each database must be declared in a separate SET DATABASE statement. For example, the following code contains two SET DATABASE statements: . . . EXEC SQL SET DATABASE DB2 = employee2.gdb； EXEC SQL SET DATABASE DB1 = employee.gdb； . . . 4Using hand
14、les for table names When the same table name occurs in more than one simultaneously accessed database, a database handle must be used to differentiate one table name from another. The database handle is used as a prefix to table names, and takes the form handle.table. For example, in the following c
15、ode, the database handles, TEST and EMP, are used to distinguish between two tables, each named EMPLOYEE: . . . EXEC SQL DECLARE IDMATCH CURSOR FOR SELECT TESTNO INTO :matchid FROM TEST.EMPLOYEE WHERE TESTNO 100； EXEC SQL DECLARE EIDMATCH CURSOR FOR SELECT EMPNO INTO :empid FROM EMP.EMPLOYEE WHERE EMPNO = :matchid； . . . CHAPTER 3 WORKING WITH DATABASES 38 INTERBASE 6
