SQL数据库外文翻译

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 SELECT operations, and to transliterate from a client application’s 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 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

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 databases contain identically named tables.

Each database handle must be unique among all variables used in a program.

Database handles cannot duplicate host-language 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/’;

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 host name is included as part of the database file

specification on a TCP/IP network:

EXEC SQL

SET DATABASE DB1 = ’jupiter:/usr/interbase/examples/’;

On a Windows network that uses the Netbeui protocol, specify the path as

follows:

EXEC SQL

SET DATABASE DB1 = ’//venus/C:/Interbase/examples/’;

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.

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 disconnecting 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 = ’’;

EXEC SQL

SET DATABASE DB1 = ’’;

. . .

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 .

For example, in the following code, 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 EE

WHERE TESTNO > 100;

EXEC SQL

DECLARE EIDMATCH CURSOR FOR

SELECT EMPNO INTO :empid FROM EE

WHERE EMPNO = :matchid;

. . .

CHAPTER 3 WORKING WITH DATABASES

38 INTERBASE 6

IMPORTANT

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

DSQL applications cannot access multiple databases simultaneously.

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 open and prepare a

database with CONNECT, see “Opening a database” on page 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 program, the same database file is accessed. Different databases can be

specified for preprocessing and run time when necessary.4Using the

COMPILETIME clause A program can be designed to run against any one of several

identically structured databases. In other cases, the actual database that a program

will use at runtime is not available when a program is preprocessed and compiled.

In such cases, SET DATABASE can include a COMPILETIME clause to specify a

database for gpre to test against during preprocessing. For example, the following

SET DATABASE statement declares that is to be used by gpre during

preprocessing:

EXEC SQL

SET DATABASE EMP = COMPILETIME ’’;

IMPORTANT

The file specification that follows the COMPILETIME keyword must always be a

hard-coded, 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 ’’;

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:

. . .

char db_name[125];

. . .

printf("Enter the desired database name, including node and path):n");

gets(db_name);

EXEC SQL

SET DATABASE EMP = COMPILETIME ’’ RUNTIME : db_name;

. . .

Note host-language variables in SET DATABASE must be preceded, as always,

by a colon.

Controlling SET DATABASE scope

By default, SET DATABASE creates a handle that is global to all modules in an

application.

A global handle is one that may be referenced in all host-language modules

comprising the program. SET DATABASE provides two optional keywords to

change the scope of a declaration:

g STATIC limits declaration scope to the module containing the SET DATABASE

statement. No other program modules can see or use a database handle declared

STATIC.

CHAPTER 3 WORKING WITH DATABASES

40 INTERBASE 6

EXTERN notifies gpre that a SET DATABASE statement in a module duplicates a

globally-declared database in another module. If the EXTERN keyword is used,

then another module must contain the actual SET DATABASE statement, or an

error occurs during compilation.

The STATIC keyword is used in a multi-module program to restrict database

handle access to the single module where it is declared. The following example

illustrates the use of the

STATIC keyword:

EXEC SQL

SET DATABASE EMP = STATIC ’’;

The EXTERN keyword is used in a multi-module 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 ’’;

If an application contains an EXTERN reference, then when it is used at run

time, the actual SET DATABASE declaration must be processed first, and the

database connected before other modules can access it.

A single SET DATABASE statement can contain either the STATIC or EXTERN

keyword, but not both. A scope declaration in SET DATABASE applies to both

COMPILETIME and RUNTIME databases.

Specifying a connection character set

When a client application connects to a database, it may have its own

character set requirements. The server providing database access to the client

does not know about these requirements unless the client specifies them. The

client application specifies its character set requirement using the SET NAMES

statement before it connects to the database.

SET NAMES specifies the character set the server should use when translating

data from the database to the client application. Similarly, when the client sends

data to the database, the server translates the data from the client’s character set

to the database’s default character set (or the character set for an individual

column if it differs from the database’s default character set). For example, the

following statements specify that the client is using the DOS437 character set, then

connect to the database:

EXEC SQL

OPENING A DATABASE

EMBEDDED SQL GUIDE 41

SET NAMES DOS437;

EXEC SQL

CONNECT ’’ USER ’JAMES’ PASSWORD ’U4EEAH’;

For more information about character sets, see the Data Definition Guide. For

the complete syntax of SET NAMES and CONNECT, see the Language Reference.

Opening a database

After a database is declared, it must be attached with a CONNECT statement

before it can be used. CONNECT:

1. Allocates system resources for the database.

2. Determines if the database file is local, residing on the same host where the

application itself is running, or remote, residing on a different host.

3. Opens the database and examines it to make sure it is valid.

InterBase provides transparent access to all databases, whether local or

remote. If the database structure is invalid, the on-disk structure (ODS) number

does not correspond to the one required by InterBase, or if the database is corrupt,

InterBase reports an error, and permits no further access. Optionally, CONNECT

can be used to specify:

4. A user name and password combination that is checked against the

server’s security database before allowing the connect to succeed. User names

can be up to 31 characters.

Passwords are restricted to 8 characters.

5. An SQL role name that the user adopts on connection to the database,

provided that the user has previously been granted membership in the role.

Regardless of role memberships granted, the user belongs to no role unless

specified with this ROLE clause.

The client can specify at most one role per connection, and cannot switch roles

except by reconnecting.

6. The size of the database buffer cache to allocate to the application when the

default cache size is inappropriate.

Using simple CONNECT statements

In its simplest form, CONNECT requires one or more database parameters,

each specifying the name of a database to open. The name of the database can be

a:

Database handle declared in a previous SET DATABASE statement.

CHAPTER 3 WORKING WITH DATABASES

42 INTERBASE 6

1. Host-language variable.

2. Hard-coded file name.

4Using a database handle

If a program uses SET DATABASE to provide database handles, those handles

should be used in subsequent CONNECT statements instead of hard-coded names.

For example,

. . .

EXEC SQL

SET DATABASE DB1 = ’’;

EXEC SQL

SET DATABASE DB2 = ’’;

EXEC SQL

CONNECT DB1;

EXEC SQL

CONNECT DB2;

. . .

There are several advantages to using a database handle with CONNECT:

1. Long file specifications can be replaced by shorter, mnemonic handles.

2. Handles can be used to qualify table names in multi-database transactions.

DSQL applications do not support multi-database transactions.

3. Handles can be reassigned to other databases as needed.

4. The number of database cache buffers can be specified as an additional

CONNECT parameter.

For more information about setting the number of database cache buffers, see

“Setting database cache buffers” on page 47. 4Using strings or host-language

variables Instead of using a database handle, CONNECT can use a database name

supplied at run time. The database name can be supplied as either a

host-language variable or a hard-coded, quoted string.

The following C code demonstrates how a program accessing only a single

database might implement CONNECT using a file name solicited from a user at run

time:

. . .

char fname[125];

. . .

printf(’Enter the desired database name, including node

and path):n’);

OPENING A DATABASE

EMBEDDED SQL GUIDE 43

gets(fname);

. . .

EXEC SQL

CONNECT :fname;

. . .

Tip

This technique is especially useful for programs that are designed to work with

many identically structured databases, one at a time, such as CAD/CAM or

architectural databases.

MULTIPLE DATABASE IMPLEMENTATION

To use a database specified by the user as a host-language variable in a

CONNECT statement in multi-database programs, follow these steps:

1. Declare a database handle using the following SET DATABASE syntax:

EXEC SQL

SET DATABASE handle = COMPILETIME ’ dbname’;

Here, handle is a hard-coded database handle supplied by the programmer,

dbname

is a quoted, hard-coded 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 host-language variable.

4. Use the handle to open the database, associating the host-language

variable with the handle using the following CONNECT syntax:

EXEC SQL

CONNECT : variable AS handle;

The following C code illustrates these steps:

. . .

char fname[125];

. . .

EXEC SQL

SET DATABASE DB1 = ’’;

printf("Enter the desired database name, including node

and path):n");

gets(fname);

EXEC SQL

CONNECT :fname AS DB1;

. . .

CHAPTER 3 WORKING WITH DATABASES

44 INTERBASE 6

In this example, SET DATABASE provides a hard-coded 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 hard-coded database names

IN SINGE-DATABASE PROGRAMS

In a single-database program that omits SET DATABASE, CONNECT must

contain a hard-coded, quoted file name in the following format:

EXEC SQL

CONNECT ’[ host[ path]] filename’; host is required only if a program and a

database file it uses reside on different nodes.

Similarly, path is required only if the database file does not reside in the

current working directory. For example, the following CONNECT statement

contains a hard-coded file name that includes both a Unix host name and a path

name:

EXEC SQL

CONNECT ’valdez:usr/interbase/examples/’;

Note Host syntax is specific to each server platform.

IMPORTANT A program that accesses multiple databases cannot use this form

of CONNECT.

IN MULTI-DATABASE 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

SET DATABASE DB2 = ’’;

EXEC SQL

CONNECT DB1;

EXEC SQL

CONNECT DB2;

. . .

Later, when the program closes these databases, the database handles are no

longer in use. These handles can be reassigned to other databases by hard-coding

a file name in a subsequent CONNECT statement. For example,

OPENING A DATABASE

EMBEDDED SQL GUIDE 45

. . .

EXEC SQL

DISCONNECT DB1, DB2;

EXEC SQL

CONNECT ’’ AS DB1;

. . .

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 complete discussion of CONNECT syntax and its uses, see the Language

Reference.

Syntax Description Example

Single access

Multiple access

CONNECT ‘dbfile’; Opens a single, hard-coded database file, dbfile.

EXEC SQL

CONNECT ‘’;

Yes No

CONNECT handle; Opens the database file associated with a previously

declared database handle. This is the preferred CONNECT syntax.

EXEC SQL

CONNECT EMP;

Yes Yes

CONNECT ‘dbfile’ AS handle;

Opens a hard-coded database file, dbfile, and assigns a previously declared

database handle to it.

EXEC SQL

CONNECT ‘’

AS EMP;

Yes Yes CONNECT :varname AS handle;

Opens the database file stored in the host-language variable, varname, and

assigns a previously declared database handle to it.

EXEC SQL CONNECT :fname AS EMP;

Yes Yes

TABLE 3.1 CONNECT syntax summary

CHAPTER 3 WORKING WITH DATABASES

46 INTERBASE 6

Attaching to multiple databases

CONNECT can attach to multiple databases. To open all databases specified in

previous SET

DATABASE statements, use either of the following CONNECT syntax options:

EXEC SQL

CONNECT ALL;

EXEC SQL

CONNECT DEFAULT;

CONNECT can also attach to a specified list of databases. Separate each

database request from others with commas. For example, the following statement

opens two databases specified by their handles:

EXEC SQL

CONNECT DB1, DB2;

The next statement opens two hard-coded database files and also assigns

them to previously declared handles:

EXEC SQL

CONNECT ’’ AS DB1, ’’ AS DB2;

Tip Opening multiple databases with a single CONNECT is most effective

when a program’s database access is simple and clear. In complex programs that

open and close several databases, that substitute database names with

host-language variables, or that assign multiple handles to the same database, use

separate CONNECT statements to make program code easier to read, debug, and

modify.

Handling CONNECT errors. The WHENEVER statement should be used to trap

and handle runtime errors that occur during database declaration. The following C

code fragment illustrates an error-handling routine that displays error messages

and ends the program in an orderly fashion:

. . .

EXEC SQL

WHENEVER SQLERROR

GOTO error_exit;

. . .

OPENING A DATABASE

EMBEDDED SQL GUIDE 47

:error_exit

isc_print_sqlerr(sqlcode, status_vector);

EXEC SQL

DISCONNECT ALL;

exit(1);

. . .

For a complete discussion of SQL error handling, see Chapter 12, “Error

Handling and Recovery.”

数据库的工作

这章描述怎样使用在嵌入式应用过程中的SQL语句控制数据库。有3 个数据库陈述

建立并且打开进入的数据库： 确定数据库宣布一数据库经营，把这个柄与一真实数据库文

件联系起来，并且选择分配给数据库的操作的参数。

确定名字选择指定客户应用为CHAR，VARCHAR 和正文一些数据使用的字符集。 服

务器使用这信息从形成客户性质从而选择经营的一数据库反映性质直译， 从应用性质和更

新操作的数据库的一客户那里直译。连结打开数据库，分配去它的系统资源，并且选择因

那些数据库而分配操作参数。在一个程序结束之前，全部数据库必须被关闭。 一数据库可

能通过使用不连接或者在附加选择对最后做的释放或者在一个程序内的返回而被关闭。

宣布数据库

在数据库之前可能被打开并且被在计划内使用，它必须首先确定数据库被宣布：

第三章数据库的工作确定数据库操作。 联系数据库与文件关于一地方和遥远节点储存

的一数据库一起经营。一数据库处理一独特，缩写的别名适合一真实数据库名字。 数据库

处理被使用在过程中随后连结，做释放，和随后释放陈述指定他们影响哪数据库。 除了在

动态的SQL(DSQL) 应用里， 数据库处理也能在相互联系里面使用使有合格的块，或者使

有差异，表格是当打开两个数据库或更多包含同等命名的表格什么时候的名字。

每个数据库处理一定在一个计划内使用的全部变量中是独特的。 数据库经营不能复制

主语言保留字，并且不能是InterBase 保留字。 以下的陈述说明一个简单的数据库宣告：

EXEC SQL

SET DATABASE DB1 = ’’;

宣告数据库这鉴定那些文件数据库，employee. gdb，作为那些计划使用，并且分配

那些数据库一个处理或者别名，DB1的数据库。

如果一个程序在一份不同于包含数据库文件的目录里运转， 然后那些说明文件名在数

据库也必须包括全部路径名。例如，确定宣告指定全部的数据库的那些如下内容通向的路

径:

EXEC SQL

SET DATABASE DB1 = ’/interbase/examples/’;

如果它使用的一个程序和一个数据库文件保存在不同的主人， 然后文件名说明也必须

包括一个主机名。以下的宣告说明一个Unix主机名怎样被作为关于一个传输控制协议/网

际协议网络的数据库文件说明表的部分包括：

EXEC SQL

SET DATABASE DB1 = ’jupiter:/usr/interbase/examples/’;

在使用Netbeui 协议的一个Windows 网络上，指定道路如下：

EXEC SQL

SET DATABASE DB1 = ’//venus/C:/Interbase/examples/’;

宣布数据库嵌入SQL指南

宣布多数据库

一个SQL程序，但不是一个DSQL程序，能同时访问多数据库。 在多数据库的计划

内，数据库处理被要求。 习惯于：多数据库交易参考个别数据库。

1．使表格有合理的名字。

2．指定数据库为打开并且连结状态。

3．表明，要接受的数据库没有连接，做释放，并且随后释放。 DSQL 计划能访问单

一数据库只一次，数据库处理使用连接并且从数据库那里拆开限制。

在多数据库计划内，不是每数据库都一定被宣布用一单独陈述数据库。例如，以下的

代码包含两个固定的数据库陈述：

. . .

EXEC SQL

SET DATABASE DB2 = ’’;

EXEC SQL

SET DATABASE DB1 = ’’;

. . .

当相同的表格名字在不止一次同时访问的数据库发生时，使用为表格名字处理，数据

库处理必须用来把一个表格名字和另一个区别开。 数据库经营被用作给表格名字的一前

缀，并且处理形式。例如，在以下代码内，数据库办理，测试和EMP，用来

分清二张表格，每一个命名雇员：

. . .

EXEC SQL

DECLARE IDMATCH CURSOR FOR

SELECT TESTNO INTO :matchid FROM EE

WHERE TESTNO > 100;

EXEC SQL

DECLARE EIDMATCH CURSOR FOR

SELECT EMPNO INTO :empid FROM EE

WHERE EMPNO = :matchid;

. . .

这数据库的使用经营只申请嵌入SQL 应用。 DSQL 应用同时不能访问多数据库。

使用在多数据库的程序里用操作处理，数据库经营一定在里指定连结陈述鉴定哪几个

中的数据库打开并且准备供随后交易使用。 数据库处理被用于可能不连接，做释放，并且

随后释放指定子集合的关闭的正在打开的数据库。

数据库有连结，看在第41 页上"数据库，打开"。 数据库有拆开，做释放，或者随后

释放，看在第49 页上" 数据库，关闭"。为了了解更多的数据库在连接过程中处理的使用，

看在第48 页"访问开的数据库"。

预处理和运行时间数据库

通常，每个数据库陈述指定单一数据库文件同一个文件交往。 当一个计划被预处理时，

gpre使用被指定的文件批准计划的表格和专栏参考。 过后，当一个用户运行程序时，相

同的数据库文件被访问。 不同的数据库必要时可能被指定预处理和运行时间。

使用COMPILETIME 条款，一个计划可能被用于撞上几同等组织的数据库中的任何

人。 在其他情况里，当一个计划被预处理并且编辑时，一个程序将在运行时间使用的实际

数据库没有出现。在这样的情况内，确定，gpre测试，数据库包括条款COMPILETIME 指

定数据库能在上在期间预处理。例如，规定数据库陈述的如下内容宣布将

在预处理期间被gpre使用：

EXEC SQL

SET DATABASE EMP = COMPILETIME ’’;

遵循那些关键字COMPILETIME的那些文件说明表必须总是一个坚固编码，引用线。

什么时候被确定数据库使用COMPILETIME 条款， 但是没有运行时间条款， 并且没

指定，不同数据库陆续编入说明一随后陈述，相同文件数据库被使用两个适合预处理和运

行时间。预处理和数据库运行时间与一起确定数据库，使用那些COMPILETIME和条款运

行时间。

使用运行时间条款， 当一个数据库文件被供使用指定时， 在预处理期间，确定数据

库能指定要通过包括运行时间关键字和一张运行时间文件说明表在运行时间使用的不同的

数据库：

EXEC SQL

SET DATABASE EMP = COMPILETIME ’’

RUNTIME ’’;

遵循的那些文件说明表关键字可能的那些运行时间或者一坚固编码，引用线或者易变

的主语言。例如， 以下C 代码碎片促使给一数据库名字的用户，和储存名字在使用过后

确定数据库的一变量内：

. . .

char db_name[125];

. . .

printf("Enter the desired database name, including node

and path):n");

gets(db_name);

EXEC SQL

SET DATABASE EMP = COMPILETIME ’’ RUNTIME :db_name;

. . .

注意到主语言变量在确定数据库一定被在前，象往常一样，以冒号。

数据库的控制装置因错误，数据库创造对在应用过程中的全部模件全球的一个文件。

全球文件可能在全部主语言内包括计划的模件确定数据库提供两个可选择的关键字改变一

个宣告的范围：

静止限制宣告机会在控制包含定型的数据库状态。 没有其他程序模块能看见或者使用

一数据库处理宣布静止。

EXTERN 通知gpre一确定的数据库陈述在一模件内复制全球宣布的在另一模内的数

据库。如果EXTERN关键字被使用， 然后另一个模件必须包含实际确定数据库陈述，否

则一个错误在编辑期间出现。 静止关键字在限制数据库的一多模件计划内使用办理随着宣

布在哪里的单个模件的进入。以下的例子说明使用静止的关键字：

EXEC SQL

SET DATABASE EMP = STATIC ’’;

EXTERN关键字在用信号通知确定在一模件内的数据库的一多模件计划内使用不是一

个真实宣告，但是指在一个不同的模件里做的一个宣告。 gpre在预处理期间使用这信息。

以下的例子说明使用EXTERN关键字：

EXEC SQL

SET DATABASE EMP = EXTERN ’’;

如果应用包含EXTERN 参考，那么被在运行时间使用， 实际确定数据库宣告必须被

首先处理，并且在其他模件能访问它之前，数据库连结。 一单独的数据库确定陈述能包含

的数据库或者静止或者EXTERN关键字，但不是两个都是。 宣告机会在确定数据库适用

于COMPILETIME和数据库运行时间。

指定连接字符集

当客户应用连接数据库时，指定连接字符集，它可能有它自己的字符集要求。 提供数

据库进入在客户的服务器不了解这些要求除非客户指定他们。在它连接数据库之前，客户

应用指定使用被确定的名字陈述的它的字符集要求。

当把数据从数据库翻译到客户应用时，确定名字指定服务器应该使用的字符集。与此

类似，客户把数据送到数据库， 开始数据库的默认字符集，服务器从客户的性格中翻译数

据 ( 或者一个个别的专栏的字符集，如果它不同于数据库的默认字符集)。 例如，以下的

陈述确切说明客户正使用DOS437字符集，然后连接数据库：

EXEC SQL

OPENING A DATABASE

EMBEDDED SQL GUIDE 41

SET NAMES DOS437;

EXEC SQL

CONNECT ’’ USER ’JAMES’ PASSWORD ’U4EEAH’;

对更多的关于字符集的信息来说，看数据定义指南。 完整句法确定名字并且连结，看

那些参考语言。

打开一个数据库

在数据库被宣布之后，它一定附有的打开数据库一连结陈述在它可能被使用之前。

1． 连结： 为数据库分配系统资源。

2． 确定， 数据库文件如果为当地的，保存在应用它那里的相同主人，如果是遥远的，

保存在不同主人。

3．打开数据库并且检查它保证它有效。 无论当地还是遥远，InterBase提供明晰的

全部数据库的入口。 如果数据库结构是无效的， 在磁盘上的结构(ODS)数目不相当于

InterBase需要的那个， 或者如果数据库是错误的，InterBase 报告一个错误，并且允许

没有更进一步的进入。选择，连结能用来指定：

4．在允许之前的数据库服务器那加以核对的名字用户和结合密码连接成功。 用户名

字可能能达到31个字符。 密码限制到8个字符。

5． SQL 角色名字用户在连接给数据库上采用，用户以前假如在角色内的会籍假若。

不管准许的角色会员，用户没有属于角色，除非与这项角色条款指定。 除了通过再接通，

客户能每连接在大多数那个角色确切说明，并且不能改变角色。

6． 给拖欠高速缓存尺寸是不适当的的应用分配的数据库缓冲器高速缓存的大小。

使用简单连结

在它简单形式内的陈述，连结要求一数据库参数，每一个指定打开的数据库的名字或

更多。数据库的名字可能是A：

1.数据库操作在一个以前的固定的数据库陈述里声明。

2.重力加速度主语言易变。

3.严重编码的文件名。

使用一数据库处理

如果一个程序使用确定数据库提供数据库处理，处理那些应该被使用在内随后代替坚

固代号陈述。 例如，

. . .

EXEC SQL

SET DATABASE DB1 = ’’;

EXEC SQL

SET DATABASE DB2 = ’’;

EXEC SQL

CONNECT DB1;

EXEC SQL

CONNECT DB2;

. . .

有几个优势使用数据库处理有连结：

1． 长的文件说明表可能因为更短，记忆的装卸被替换。

2． 处理能用来使表格有资格名字在多数据库交易内。 DSQL 应用不支持多数据库

的交易。

3． 操作可能按需要被再分配到其他数据库。 缓冲器那些高速缓存数据库的数量

4． 可能被指定当时一另外参数。 对更多的关于确定数据库高速缓存缓冲器的数量的

信息来说，看在第47 页"确定数据库高速缓存缓冲器"。

使用线或者主语言变量而不是使用一数据库处理，连结能使用名字在运行时间提供的

一数据库。名字那些数据库可能被作为或者易变的主语言提供或者一坚固编码，引用线。 以

下C 代码证明一单数据库可能实现的访问只的一计划连结使用一文件名从一用户那里恳

求在运行时间怎样：

. . .

char fname[125];

. . .

printf(’Enter the desired database name, including node

and path):n’);

OPENING A DATABASE

EMBEDDED SQL GUIDE 43

gets(fname);

. . .

EXEC SQL

CONNECT :fname;

. . .

建议这种技术特别对被用于与很多同等组织的数据库合作的计划有用，一次一个，例

如计算机辅助设计/计算机辅助制造或者建筑的数据库。

要使用的多实施数据库用

那些用户说明的数据库当时易变的主语言在内一连结多数据库计划陈述，遵循这几次

步：

1. 宣布数据库处理使用规定数据库句法的如下内容： 规定数据库的行政人员SQL操

作 = COMPILETIME ' dbname ' ； 这里，经营在坚固编码数据库经营被以那些程序员

提供，dbname被引用，名字在预处理期间以gpre使用的编码的数据库。

2. 迅速给数据库打开的用户。

3. 数据库名字用易变一种主语言用户进入储存。

4. 使用这个文件打开数据库，联系与使用如下内容的这个易变的文件的主语言连结句

法：

EXEC SQL

CONNECT : variable AS handle;

以下的C 代码说明这些步：

. . .

char fname[125];

. . .

EXEC SQL

SET DATABASE DB1 = ’’;

printf("Enter the desired database name, including node

and path):n");

gets(fname);

EXEC SQL

CONNECT :fname AS DB1;

. . .

在这个例子里，确定数据库为用gpre预处理提供艰难编码的数据库文件名。当一个

用户运行程序，在变量里指定的数据库时，fname，被改为使用。

使用一坚固编码数据库名字

在单一数据库程序

在忽略确定数据库的一单数据库程序内，连结包含必须一艰难编码，引用陆续编入名

字那些遵循形式：

EXEC SQL

CONNECT ’[ host[ path]] filename’;

主人被要求，只要一个程序和它使用的一个数据库文件保存在不同的节点。与此类似，

道路被要求，只要数据库文件不居住现行工作目录。 例如，如下内容连结陈述包含包括一

个Unix主机名和一个路径名的艰难编码的文件名：

EXEC SQL

CONNECT ’valdez:usr/interbase/examples/’;

注意到主人句法对每个服务器平台具体。重要多的出入口数据库不能使用这形式的一

计划连结。

在多数据库程序内

数据库多的出入口宣布必须的计划适合他们中的每个处理在内单独陈述数据库。处理

这些一定被使用在内随后陈述鉴定具体数据库打开：

. . .

EXEC SQL

SET DATABASE DB1 = ’’;

EXEC SQL

SET DATABASE DB2 = ’’;

EXEC SQL

CONNECT DB1;

EXEC SQL

CONNECT DB2;

. . .

过后，计划关闭这些数据库什么时候，数据库办理不再在使用中。处理这些文件名去

其他数据库可能被再分配和随后陈述。 例如，打开数据库嵌入SQL指南45

. . .

EXEC SQL

DISCONNECT DB1, DB2;

EXEC SQL

CONNECT ’’ AS DB1;

. . .

另外连结句法

连结支持几形式在打开数据库提供程序设计灵活性时。下述表格轮廓每个可能句法，

提供描述和例子，并且表明是否连结可能在计划使用那进入单个或者多个数据库：

完整讨论以来连结句法和它用途，看那些参考语言。句法描述例子

单个的进入

多进入

连结' dbfile '；

打开单独一个，严重编码的数据库文件，dbfile。

EXEC SQL 连结' '；

是并不连结文件；

打开数据库与以前宣布的数据库相关的文件操作。这是更好的连结句法。

EXEC SQL 连结EMP；

是连结' dbfile '作为处理；

打开一个艰难编码的数据库文件，dbfile，并且分配以前宣布的数据库对它操作。

EXEC SQL

连结' '作为EMP；

是连结：varname当时经营；

打开文件在易变的主语言内储存的数据库，varname，并且分配一以前宣布的数据库

给处理。

EXEC SQL 连结：fname 作为EMP；

是表格3.1 连结句法摘要

粘贴多数据库

连结能粘贴多数据库。对打开全部在以前里指定的数据库确定数据库陈述，使用任何

一个如下内容连结句法选择：

EXEC SQL

CONNECT ALL;

EXEC SQL

CONNECT DEFAULT;

连结附在也能上一个指定的目录的数据库的。分开每数据库其它人用逗号的请求。 例

如，以下的陈述打开用他们的柄说明的两数据库：

EXEC SQL

CONNECT DB1, DB2;

下一陈述打开两编码数据库文件以及以前选派他们去宣布处理：

EXEC SQL

CONNECT ’’ AS DB1, ’’ AS DB2;

打开与一单独和一起的多数据库的信息连结非常有效计划的数据库出入口是简单和清

楚的。在启闭几数据库的复杂的程序里， 有主语言变量的那替换词数据库名字， 或者那

把多文件分配给相同数据库，使用单独陈述使计划容易读，调整，并且修改代码。

处理连结错误

陈述用来使陷入困境并且办理运行时间错误在宣告数据库期间发生。 以下的C 代码

碎片说明展示错误信息并且用整齐方式结束程序的处理错误的程序：

. . .

EXEC SQL

WHENEVER SQLERROR

GOTO error_exit;

. . .

打开数据库嵌入SQL指南47

:error_exit

isc_print_sqlerr(sqlcode, status_vector);

EXEC SQL

DISCONNECT ALL;

exit(1);

. . .

对一个操作的SQL 错误的完整的讨论来说，看第12章，"操作的错误和恢复"。