©
This document usesPHP Chinese website manualRelease
This section describes how you can handle exceptional conditions and warnings in an embedded SQL program. There are several nonexclusive facilities for this.
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 WHENEVERconditionaction;
conditioncan be one of the following:
The specified action is called whenever an error occurs during the execution of an SQL statement.
The specified action is called whenever a warning occurs during the execution of an SQL statement.
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.)
actioncan be one of the following:
This effectively means that the condition is ignored. This is the default.
Jump to the specified label (using a Cgotostatement).
Print a message to standard error. This is useful for simple programs or during prototyping. The details of the message cannot be configured.
Callexit(1), which will terminate the program.
Execute the C statementbreak. This should only be used in loops orswitchstatements.
Call the specified C functions with the specified arguments.
The SQL standard only provides for the actionsCONTINUEandGOTO(andGO 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;
The statementEXEC SQL WHENEVERis 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 firstEXEC SQL WHENEVERand 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; }
For more powerful error handling, the embedded SQL interface provides a global variable with the namesqlcathat 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;
(In a multithreaded program, every thread automatically gets its own copy ofsqlca. This works similarly to the handling of the standard C global variableerrno.)
sqlcacovers both warnings and errors. If multiple warnings or errors occur during the execution of a statement, thensqlcawill only contain information about the last one.
If no error occurred in the lastSQLstatement,sqlca.sqlcodewill be 0 andsqlca.sqlstatewill be"00000". If a warning or error occurred, thensqlca.sqlcodewill be negative andsqlca.sqlstatewill be different from"00000". A positivesqlca.sqlcodeindicates a harmless condition, such as that the last query returned zero rows.sqlcodeandsqlstateare two different error code schemes; details appear below.
If the last SQL statement was successful, thensqlca.sqlerrd[1]contains the OID of the processed row, if applicable, andsqlca.sqlerrd[2]contains the number of processed or returned rows, if applicable to the command.
In case of an error or warning,sqlca.sqlerrm.sqlerrmcwill contain a string that describes the error. The fieldsqlca.sqlerrm.sqlerrmlcontains the length of the error message that is stored insqlca.sqlerrm.sqlerrmc(the result ofstrlen(), not really interesting for a C programmer). Note that some messages are too long to fit in the fixed-sizesqlerrmcarray; they will be truncated.
In case of a warning,sqlca.sqlwarn[2]is set toW. (In all other cases, it is set to something different fromW.) Ifsqlca.sqlwarn[1]is set toW, then a value was truncated when it was stored in a host variable.sqlca.sqlwarn[0]is set toWif any of the other elements are set to indicate a warning.
The fieldssqlcaid,sqlcabc,sqlerrp, and the remaining elements ofsqlerrdandsqlwarncurrently contain no useful information.
The structuresqlcais 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.
The fieldssqlca.sqlstateandsqlca.sqlcodeare two different schemes that provide error codes. Both are derived from the SQL standard, butSQLCODEhas 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 useSQLSTATE.
SQLSTATEis a five-character array. The five characters contain digits or upper-case letters that represent codes of various error and warning conditions.SQLSTATEhas 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 code00000. TheSQLSTATEcodes are for the most part defined in the SQL standard. ThePostgreSQLserver natively supportsSQLSTATEerror 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, 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 forPostgreSQLhas assigned some specificSQLCODEvalues 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 theSQLSTATEscheme, the correspondingSQLSTATEis 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 globalSQLSTATElisting in Appendix A in each case.
These are the assignedSQLCODEvalues:
Indicates that your virtual memory is exhausted. (SQLSTATE YE001)
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)
This means that the command specified more host variables than the command expected. (SQLSTATE 07001 or 07002)
This means that the command specified fewer host variables than the command expected. (SQLSTATE 07001 or 07002)
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)
The host variable is of typeintand the datum in the database is of a different type and contains a value that cannot be interpreted as anint. The library usesstrtol()for this conversion. (SQLSTATE 42804)
The host variable is of typeunsigned intand the datum in the database is of a different type and contains a value that cannot be interpreted as anunsigned int. The library usesstrtoul()for this conversion. (SQLSTATE 42804)
The host variable is of typefloatand the datum in the database is of another type and contains a value that cannot be interpreted as afloat. The library usesstrtod()for this conversion. (SQLSTATE 42804)
This means the host variable is of typebooland the datum in the database is neither't'nor'f'. (SQLSTATE 42804)
The statement sent to thePostgreSQLserver was empty. (This cannot normally happen in an embedded SQL program, so it might point to an internal error.) (SQLSTATE YE002)
A null value was returned and no null indicator variable was supplied. (SQLSTATE 22002)
An ordinary variable was used in a place that requires an array. (SQLSTATE 42804)
The database returned an ordinary variable in a place that requires array value. (SQLSTATE 42804)
The program tried to access a connection that does not exist. (SQLSTATE 08003)
The program tried to access a connection that does exist but is not open. (This is an internal error.) (SQLSTATE YE002)
The statement you are trying to use has not been prepared. (SQLSTATE 26000)
The descriptor specified was not found. The statement you are trying to use has not been prepared. (SQLSTATE 33000)
The descriptor index specified was out of range. (SQLSTATE 07009)
An invalid descriptor item was requested. (This is an internal error.) (SQLSTATE YE002)
During the execution of a dynamic statement, the database returned a numeric value and the host variable was not numeric. (SQLSTATE 07006)
During the execution of a dynamic statement, the database returned a non-numeric value and the host variable was numeric. (SQLSTATE 07006)
Some error caused by thePostgreSQLserver. The message contains the error message from thePostgreSQLserver.
ThePostgreSQLserver signaled that we cannot start, commit, or rollback the transaction. (SQLSTATE 08007)
The connection attempt to the database did not succeed. (SQLSTATE 08001)
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)