sql数据库外文翻译.doc

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 optionally 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 database’s default character set to the client’s character set on

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 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 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

SET DATABASE DB1 = ’employee.gdb’;

This database declaration identifies the database file, employee.gdb, 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 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

EXEC SQL

SET DATABASE DB1 = ’//venus/C:/Interbase/examples/employee.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-database transaction.

2. Qualify table names.

3. Specify databases to open in CONNECT statements.

. . .

EXEC SQL

SET DATABASE DB2 = ’employee2.gdb’;

EXEC SQL

SET DATABASE DB1 = ’employee.gdb’;

. . .

4Using handles 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.

distinguish between two tables, each named EMPLOYEE:

. . .

EXEC SQL

DECLARE IDMATCH CURSOR FOR

SELECT TESTNO INTO :matchid FROM TEST.EMPLOYEE

WHERE TESTNO > 100;

CHAPTER 3 WORKING WITH DATABASES

38 INTERBASE 6

This use of database handles applies only to embedded SQL applications. DSQL

4Using handles with operations

In multi-database programs, database handles must be specified in CONNECT

statements to identify which databases among several to open and prepare for use in

subsequent transactions.

Database handles can also be used with DISCONNECT, COMMIT RELEASE, and

ROLLBACK

RELEASE to specify a subset of open databases to close.To open and prepare a

database with CONNECT, see “Opening a database” on page 41.To close a database

with DISCONNECT, COMMIT RELEASE, or ROLLBACK RELEASE, see“Closing

a database” on page 49. To learn more about using database handles in transactions,

see “Accessing an open database” on page 48.

Preprocessing and run time databases

Normally, each SET DATABASE statement specifies a single database file to

associate with a handle. When a program is preprocessed, gpre uses the specified file

to validate the program’s table and column references. Later, when a user runs the

test against during preprocessing. For example, the following SET DATABASE

statement declares that employee.gdb is to be used by gpre during preprocessing:

EXEC SQL

SET DATABASE EMP = COMPILETIME ’employee.gdb’;

IMPORTANT

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

The file specification that follows the RUNTIME keyword can be either a hard-

coded, quoted string, or a host-language variable. For example, the following C code

fragment prompts the user for a database name, and stores the name in a variable that

is used later in SET DATABASE:

SET DATABASE EMP = COMPILETIME ’employee.gdb’ RUNTIME : db_name;

. . .