Postgresql 中文操作指南

36.8. Error Handling #

本部分介绍如何在嵌入式 SQL 程序中处理异常条件和警告。为此，有两个互不排斥的工具。

This section describes how you can handle exceptional conditions and warnings in an embedded SQL program. There are two nonexclusive facilities for this.

36.8.1. Setting Callbacks #

捕捉错误和警告的一个简单方法是在发生特定条件时设置要执行的特定操作。一般而言：

One simple method to catch errors and warnings is to set a specific action to be executed whenever a particular condition occurs. In general:

EXEC SQL WHENEVER condition action;

condition 可能属于以下情形之一：

condition can be one of the following:

  • SQLERROR #

    • The specified action is called whenever an error occurs during the execution of an SQL statement.

  • SQLWARNING #

    • The specified action is called whenever a warning occurs during the execution of an SQL statement.

  • NOT FOUND #

    • The specified action is called whenever an SQL statement retrieves or affects zero rows. (This condition is not an error, but you might be interested in handling it specially.)

action 可能属于以下情形之一：

action can be one of the following:

  • CONTINUE #

    • This effectively means that the condition is ignored. This is the default.

  • GOTO _labelGO TO _label #

    • Jump to the specified label (using a C goto statement).

  • SQLPRINT #

    • Print a message to standard error. This is useful for simple programs or during prototyping. The details of the message cannot be configured.

  • STOP #

    • Call exit(1), which will terminate the program.

  • DO BREAK #

    • Execute the C statement break. This should only be used in loops or switch statements.

  • DO CONTINUE #

    • Execute the C statement continue. This should only be used in loops statements. if executed, will cause the flow of control to return to the top of the loop.

  • CALL _name (args)DO _name (args_)_ #

    • Call the specified C functions with the specified arguments. (This use is different from the meaning of CALL and DO in the normal PostgreSQL grammar.)

SQL 标准仅提供 CONTINUE 和 GOTO （以及 GO TO）的操作。

The SQL standard only provides for the actions CONTINUE and GOTO (and GO TO).

下面是您可能希望在简单程序中使用的示例。它在发生警告时打印简单消息，并在出现错误时中止程序：

Here is an example that you might want to use in a simple program. It prints a simple message when a warning occurs and aborts the program when an error happens:

EXEC SQL WHENEVER SQLWARNING SQLPRINT;
EXEC SQL WHENEVER SQLERROR STOP;

语句 EXEC SQL WHENEVER 是 SQL 预处理器的指令，而不是 C 语句。它设置的错误或警告操作应用于处理函数设置点下方显示的所有嵌入式 SQL 语句，除非在第一个 EXEC SQL WHENEVER 与导致情况的 SQL 语句之间为相同情况设置了不同的操作，而不管 C 程序中的控制流如何。因此，以下两个 C 程序摘录都达不到所需效果：

The statement EXEC SQL WHENEVER is a directive of the SQL preprocessor, not a C statement. The error or warning actions that it sets apply to all embedded SQL statements that appear below the point where the handler is set, unless a different action was set for the same condition between the first EXEC SQL WHENEVER and the SQL statement causing the condition, regardless of the flow of control in the C program. So neither of the two following C program excerpts will have the desired effect:

/*
 * WRONG
 */
int main(int argc, char *argv[])
{
    ...
    if (verbose) {
        EXEC SQL WHENEVER SQLWARNING SQLPRINT;
    }
    ...
    EXEC SQL SELECT ...;
    ...
}
/*
 * WRONG
 */
int main(int argc, char *argv[])
{
    ...
    set_error_handler();
    ...
    EXEC SQL SELECT ...;
    ...
}

static void set_error_handler(void)
{
    EXEC SQL WHENEVER SQLERROR STOP;
}

36.8.2. sqlca #

为了更强大的错误处理，嵌入式 SQL 接口提供了一个全局变量，其名称为 sqlca（SQL 通讯区域），该变量具有以下结构：

For more powerful error handling, the embedded SQL interface provides a global variable with the name sqlca (SQL communication area) that has the following structure:

struct
{
    char sqlcaid[8];
    long sqlabc;
    long sqlcode;
    struct
    {
        int sqlerrml;
        char sqlerrmc[SQLERRMC_LEN];
    } sqlerrm;
    char sqlerrp[8];
    long sqlerrd[6];
    char sqlwarn[8];
    char sqlstate[5];
} sqlca;

（在多线程程序中，每个线程都能自动获取自己的 sqlca 副本。这类似于处理标准 C 全局变量 errno 的方式。）

(In a multithreaded program, every thread automatically gets its own copy of sqlca. This works similarly to the handling of the standard C global variable errno.)

sqlca 涵盖警告和错误。如果在执行语句期间出现多个警告或错误，则 sqlca 将仅包含一个有关上一个警告或错误的信息。

sqlca covers both warnings and errors. If multiple warnings or errors occur during the execution of a statement, then sqlca will only contain information about the last one.

如果在最后一个 SQL 语句中未发生错误，则 sqlca.sqlcode 将为 0，而 sqlca.sqlstate 将为 "00000"。如果出现警告或错误，则 sqlca.sqlcode 将为负，而 sqlca.sqlstate 将与 "00000" 不同。正数 sqlca.sqlcode 表示无害情况，如上次查询返回 0 行。sqlcode 和 sqlstate 是两个不同的错误代码方案；详细信息如下所列。

If no error occurred in the last SQL statement, sqlca.sqlcode will be 0 and sqlca.sqlstate will be "00000". If a warning or error occurred, then sqlca.sqlcode will be negative and sqlca.sqlstate will be different from "00000". A positive sqlca.sqlcode indicates a harmless condition, such as that the last query returned zero rows. sqlcode and sqlstate are two different error code schemes; details appear below.

如果上一个 SQL 语句成功，那么在适用的情况下，sqlca.sqlerrd[1] 将包含经处理行的 OID，并且 sqlca.sqlerrd[2] 将包含经处理或返回的行数（如果适用于命令）。

If the last SQL statement was successful, then sqlca.sqlerrd[1] contains the OID of the processed row, if applicable, and sqlca.sqlerrd[2] contains the number of processed or returned rows, if applicable to the command.

在发生错误或警告的情况下，sqlca.sqlerrm.sqlerrmc 将包含描述错误的字符串。字段 sqlca.sqlerrm.sqlerrml 包含存储在 sqlca.sqlerrm.sqlerrmc 中的错误消息长度（是 strlen() 的结果，对于 C 程序员而言它并不重要）。请注意，一些消息太长了，无法放入固定大小的 sqlerrmc 数组中；它们将被截断。

In case of an error or warning, sqlca.sqlerrm.sqlerrmc will contain a string that describes the error. The field sqlca.sqlerrm.sqlerrml contains the length of the error message that is stored in sqlca.sqlerrm.sqlerrmc (the result of strlen(), not really interesting for a C programmer). Note that some messages are too long to fit in the fixed-size sqlerrmc array; they will be truncated.

如果出现警告，sqlca.sqlwarn[2] 被设置为 W。（在所有其他情况下，它被设置为不同于 W 的内容。）如果 sqlca.sqlwarn[1] 被设置为 W，那么当值存储在主机变量中时，它会被截断。如果将任何其他元素设置为 W 以指示警告，那么 sqlca.sqlwarn[0] 将被设置为 W。

In case of a warning, sqlca.sqlwarn[2] is set to W. (In all other cases, it is set to something different from W.) If sqlca.sqlwarn[1] is set to W, then a value was truncated when it was stored in a host variable. sqlca.sqlwarn[0] is set to W if any of the other elements are set to indicate a warning.

字段 sqlcaid、sqlabc、sqlerrp 以及 sqlerrd 和 sqlwarn 的剩余元素目前不包含任何有用的信息。

The fields sqlcaid, sqlabc, sqlerrp, and the remaining elements of sqlerrd and sqlwarn currently contain no useful information.

结构 sqlca 未在 SQL 标准中定义，但已在其他几个 SQL 数据库系统中实现。其核心定义类似，但如果你想编写可移植的应用程序，那么你应该仔细调查不同的实现。

The structure sqlca is not defined in the SQL standard, but is implemented in several other SQL database systems. The definitions are similar at the core, but if you want to write portable applications, then you should investigate the different implementations carefully.

下面是一个同时使用 WHENEVER 和 sqlca 的示例，它在发生错误时打印出 sqlca 的内容。在安装更“用户友好”的错误处理程序之前，这或许对于调试或制作应用程序原型有用。

Here is one example that combines the use of WHENEVER and sqlca, printing out the contents of sqlca when an error occurs. This is perhaps useful for debugging or prototyping applications, before installing a more “user-friendly” error handler.

EXEC SQL WHENEVER SQLERROR CALL print_sqlca();

void
print_sqlca()
{
    fprintf(stderr, "==== sqlca ====\n");
    fprintf(stderr, "sqlcode: %ld\n", sqlca.sqlcode);
    fprintf(stderr, "sqlerrm.sqlerrml: %d\n", sqlca.sqlerrm.sqlerrml);
    fprintf(stderr, "sqlerrm.sqlerrmc: %s\n", sqlca.sqlerrm.sqlerrmc);
    fprintf(stderr, "sqlerrd: %ld %ld %ld %ld %ld %ld\n", sqlca.sqlerrd[0],sqlca.sqlerrd[1],sqlca.sqlerrd[2],
                                                          sqlca.sqlerrd[3],sqlca.sqlerrd[4],sqlca.sqlerrd[5]);
    fprintf(stderr, "sqlwarn: %d %d %d %d %d %d %d %d\n", sqlca.sqlwarn[0], sqlca.sqlwarn[1], sqlca.sqlwarn[2],
                                                          sqlca.sqlwarn[3], sqlca.sqlwarn[4], sqlca.sqlwarn[5],
                                                          sqlca.sqlwarn[6], sqlca.sqlwarn[7]);
    fprintf(stderr, "sqlstate: %5s\n", sqlca.sqlstate);
    fprintf(stderr, "===============\n");
}

结果可能如下（这里所展示的是由于输入拼写错误的表名称而出现的错误）：

The result could look as follows (here an error due to a misspelled table name):

==== sqlca ====
sqlcode: -400
sqlerrm.sqlerrml: 49
sqlerrm.sqlerrmc: relation "pg_databasep" does not exist on line 38
sqlerrd: 0 0 0 0 0 0
sqlwarn: 0 0 0 0 0 0 0 0
sqlstate: 42P01
===============

36.8.3. SQLSTATE vs. SQLCODE #

字段 sqlca.sqlstate 和 sqlca.sqlcode 是提供错误代码的两种不同方案。两者都派生自 SQL 标准，但 SQLCODE 已在标准的 SQL-92 版本中被标记为已弃用，并且在后续版本中已被移除。因此，强烈建议新的应用程序使用 SQLSTATE。

The fields sqlca.sqlstate and sqlca.sqlcode are two different schemes that provide error codes. Both are derived from the SQL standard, but SQLCODE has been marked deprecated in the SQL-92 edition of the standard and has been dropped in later editions. Therefore, new applications are strongly encouraged to use SQLSTATE.

SQLSTATE 是一个五字符数组。这五个字符包含表示各种错误和警告条件代码的数字或大写字母。SQLSTATE 具有分层方案：前两个字符指示条件的一般类型，后三个字符指示一般条件的子类型。代码 00000 表示成功状态。SQLSTATE 代码在 SQL 标准中大部分定义。PostgreSQL 服务器本机支持 SQLSTATE 错误代码；因此，在所有应用程序中使用此错误代码方案可以实现高度一致性。有关详细信息，请参见 Appendix A。

SQLSTATE is a five-character array. The five characters contain digits or upper-case letters that represent codes of various error and warning conditions. SQLSTATE has a hierarchical scheme: the first two characters indicate the general class of the condition, the last three characters indicate a subclass of the general condition. A successful state is indicated by the code 00000. The SQLSTATE codes are for the most part defined in the SQL standard. The PostgreSQL server natively supports SQLSTATE error codes; therefore a high degree of consistency can be achieved by using this error code scheme throughout all applications. For further information see Appendix A.

SQLCODE，已弃用的错误代码方案，是简单的整数。值为 0 表示成功，正值表示带附加信息的成功，负值表示错误。SQL 标准仅定义正值 +100，这表示上一个命令返回或影响了零行，并且没有具体负值。因此，此方案只能实现较差的可移植性，并且没有分层的代码分配。从历史上看，PostgreSQL 的嵌入式 SQL 处理器为其使用分配了一些特定的 SQLCODE 值，这些值在下文列出了其数字值和符号名称。请记住，这些值不可移植到其他 SQL 实现中。为了简化应用程序向 SQLSTATE 方案的移植，相应的 SQLSTATE 也在列表中。但是，这两个方案之间不能进行一对一或一对多的映射（实际上是多对多），因此你应在每种情况下查阅 Appendix A 中的全局 SQLSTATE 列表。

SQLCODE, the deprecated error code scheme, is a simple integer. A value of 0 indicates success, a positive value indicates success with additional information, a negative value indicates an error. The SQL standard only defines the positive value +100, which indicates that the last command returned or affected zero rows, and no specific negative values. Therefore, this scheme can only achieve poor portability and does not have a hierarchical code assignment. Historically, the embedded SQL processor for PostgreSQL has assigned some specific SQLCODE values for its use, which are listed below with their numeric value and their symbolic name. Remember that these are not portable to other SQL implementations. To simplify the porting of applications to the SQLSTATE scheme, the corresponding SQLSTATE is also listed. There is, however, no one-to-one or one-to-many mapping between the two schemes (indeed it is many-to-many), so you should consult the global SQLSTATE listing in Appendix A in each case.

以下是分配的 SQLCODE 值：

These are the assigned SQLCODE values:

  • 0 (ECPG_NO_ERROR) #

    • Indicates no error. (SQLSTATE 00000)

  • 100 (ECPG_NOT_FOUND) #

    • This is a harmless condition indicating that the last command retrieved or processed zero rows, or that you are at the end of the cursor. (SQLSTATE 02000)

    • When processing a cursor in a loop, you could use this code as a way to detect when to abort the loop, like this:

while (1)
{
    EXEC SQL FETCH ... ;
    if (sqlca.sqlcode == ECPG_NOT_FOUND)
        break;
}

  • But WHENEVER NOT FOUND DO BREAK effectively does this internally, so there is usually no advantage in writing this out explicitly.

    • -12 (ECPG_OUT_OF_MEMORY) #

  • Indicates that your virtual memory is exhausted. The numeric value is defined as -ENOMEM. (SQLSTATE YE001)

    • -200 (ECPG_UNSUPPORTED) #

  • Indicates the preprocessor has generated something that the library does not know about. Perhaps you are running incompatible versions of the preprocessor and the library. (SQLSTATE YE002)

    • -201 (ECPG_TOO_MANY_ARGUMENTS) #

  • This means that the command specified more host variables than the command expected. (SQLSTATE 07001 or 07002)

    • -202 (ECPG_TOO_FEW_ARGUMENTS) #

  • This means that the command specified fewer host variables than the command expected. (SQLSTATE 07001 or 07002)

    • -203 (ECPG_TOO_MANY_MATCHES) #

  • This means a query has returned multiple rows but the statement was only prepared to store one result row (for example, because the specified variables are not arrays). (SQLSTATE 21000)

    • -204 (ECPG_INT_FORMAT) #

  • The host variable is of type int and the datum in the database is of a different type and contains a value that cannot be interpreted as an int. The library uses strtol() for this conversion. (SQLSTATE 42804)

    • -205 (ECPG_UINT_FORMAT) #

  • The host variable is of type unsigned int and the datum in the database is of a different type and contains a value that cannot be interpreted as an unsigned int. The library uses strtoul() for this conversion. (SQLSTATE 42804)

    • -206 (ECPG_FLOAT_FORMAT) #

  • The host variable is of type float and the datum in the database is of another type and contains a value that cannot be interpreted as a float. The library uses strtod() for this conversion. (SQLSTATE 42804)

    • -207 (ECPG_NUMERIC_FORMAT) #

  • The host variable is of type numeric and the datum in the database is of another type and contains a value that cannot be interpreted as a numeric value. (SQLSTATE 42804)

    • -208 (ECPG_INTERVAL_FORMAT) #

  • The host variable is of type interval and the datum in the database is of another type and contains a value that cannot be interpreted as an interval value. (SQLSTATE 42804)

    • -209 (ECPG_DATE_FORMAT) #

  • The host variable is of type date and the datum in the database is of another type and contains a value that cannot be interpreted as a date value. (SQLSTATE 42804)

    • -210 (ECPG_TIMESTAMP_FORMAT) #

  • The host variable is of type timestamp and the datum in the database is of another type and contains a value that cannot be interpreted as a timestamp value. (SQLSTATE 42804)

    • -211 (ECPG_CONVERT_BOOL) #

  • This means the host variable is of type bool and the datum in the database is neither 't' nor 'f'. (SQLSTATE 42804)

    • -212 (ECPG_EMPTY) #

  • The statement sent to the PostgreSQL server was empty. (This cannot normally happen in an embedded SQL program, so it might point to an internal error.) (SQLSTATE YE002)

    • -213 (ECPG_MISSING_INDICATOR) #

  • A null value was returned and no null indicator variable was supplied. (SQLSTATE 22002)

    • -214 (ECPG_NO_ARRAY) #

  • An ordinary variable was used in a place that requires an array. (SQLSTATE 42804)

    • -215 (ECPG_DATA_NOT_ARRAY) #

  • The database returned an ordinary variable in a place that requires array value. (SQLSTATE 42804)

    • -216 (ECPG_ARRAY_INSERT) #

  • The value could not be inserted into the array. (SQLSTATE 42804)

    • -220 (ECPG_NO_CONN) #

  • The program tried to access a connection that does not exist. (SQLSTATE 08003)

    • -221 (ECPG_NOT_CONN) #

  • The program tried to access a connection that does exist but is not open. (This is an internal error.) (SQLSTATE YE002)

    • -230 (ECPG_INVALID_STMT) #

  • The statement you are trying to use has not been prepared. (SQLSTATE 26000)

    • -239 (ECPG_INFORMIX_DUPLICATE_KEY) #

  • Duplicate key error, violation of unique constraint (Informix compatibility mode). (SQLSTATE 23505)

    • -240 (ECPG_UNKNOWN_DESCRIPTOR) #

  • The descriptor specified was not found. The statement you are trying to use has not been prepared. (SQLSTATE 33000)

    • -241 (ECPG_INVALID_DESCRIPTOR_INDEX) #

  • The descriptor index specified was out of range. (SQLSTATE 07009)

    • -242 (ECPG_UNKNOWN_DESCRIPTOR_ITEM) #

  • An invalid descriptor item was requested. (This is an internal error.) (SQLSTATE YE002)

    • -243 (ECPG_VAR_NOT_NUMERIC) #

  • During the execution of a dynamic statement, the database returned a numeric value and the host variable was not numeric. (SQLSTATE 07006)

    • -244 (ECPG_VAR_NOT_CHAR) #

  • During the execution of a dynamic statement, the database returned a non-numeric value and the host variable was numeric. (SQLSTATE 07006)

    • -284 (ECPG_INFORMIX_SUBSELECT_NOT_ONE) #

  • A result of the subquery is not single row (Informix compatibility mode). (SQLSTATE 21000)

    • -400 (ECPG_PGSQL) #

  • Some error caused by the PostgreSQL server. The message contains the error message from the PostgreSQL server.

    • -401 (ECPG_TRANS) #

  • The PostgreSQL server signaled that we cannot start, commit, or rollback the transaction. (SQLSTATE 08007)

    • -402 (ECPG_CONNECT) #

  • The connection attempt to the database did not succeed. (SQLSTATE 08001)

    • -403 (ECPG_DUPLICATE_KEY) #

  • Duplicate key error, violation of unique constraint. (SQLSTATE 23505)

    • -404 (ECPG_SUBSELECT_NOT_ONE) #

  • A result for the subquery is not single row. (SQLSTATE 21000)

    • -602 (ECPG_WARNING_UNKNOWN_PORTAL) #

  • An invalid cursor name was specified. (SQLSTATE 34000)

    • -603 (ECPG_WARNING_IN_TRANSACTION) #

  • Transaction is in progress. (SQLSTATE 25001)

    • -604 (ECPG_WARNING_NO_TRANSACTION) #

  • There is no active (in-progress) transaction. (SQLSTATE 25P01)

    • -605 (ECPG_WARNING_PORTAL_EXISTS) #

  • An existing cursor name was specified. (SQLSTATE 42P03)