1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
8 CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
10 HREF="mailto:pgsql-docs@postgresql.org"><LINK
12 TITLE="PostgreSQL 7.4.1 Documentation"
13 HREF="index.html"><LINK
15 TITLE="ECPG - Embedded SQL in C"
16 HREF="ecpg.html"><LINK
18 TITLE="Using SQL Descriptor Areas"
19 HREF="ecpg-descriptors.html"><LINK
21 TITLE="Including Files"
22 HREF="ecpg-include.html"><LINK
25 HREF="stylesheet.css"><META
27 CONTENT="2003-12-22T03:48:47"></HEAD
33 SUMMARY="Header navigation table"
43 >PostgreSQL 7.4.1 Documentation</TH
51 HREF="ecpg-descriptors.html"
87 HREF="ecpg-include.html"
102 >30.9. Error Handling</A
105 > This section describes how you can handle exceptional conditions
106 and warnings in an embedded SQL program. There are several
107 nonexclusive facilities for this.
115 >30.9.1. Setting Callbacks</A
118 > One simple method to catch errors and warnings is to set a
119 specific action to be executed whenever a particular condition
122 CLASS="PROGRAMLISTING"
123 >EXEC SQL WHENEVER <VAR
136 > can be one of the following:
150 > The specified action is called whenever an error occurs during
151 the execution of an SQL statement.
161 > The specified action is called whenever a warning occurs
162 during the execution of an SQL statement.
172 > The specified action is called whenever an SQL statement
173 retrieves or affects zero rows. (This condition is not an
174 error, but you might be interested in handling it specially.)
185 > can be one of the following:
199 > This effectively means that the condition is ignored. This is
219 > Jump to the specified label (using a C <TT
233 > Print a message to standard error. This is useful for simple
234 programs or during prototyping. The details of the message
235 cannot be configured.
248 >, which will terminate the
259 > Execute the C statement <TT
263 only be used in loops or <TT
291 > Call the specified C functions with the specified arguments.
298 The SQL standard only provides for the actions
312 > Here is an example that you might want to use in a simple program.
313 It prints a simple message when a warning occurs and aborts the
314 program when an error happens.
316 CLASS="PROGRAMLISTING"
317 >EXEC SQL WHENEVER SQLWARNING SQLPRINT;
318 EXEC SQL WHENEVER SQLERROR STOP;</PRE
324 >EXEC SQL WHENEVER</TT
326 of the SQL preprocessor, not a C statement. The error or warning
327 actions that it sets apply to all embedded SQL statements that
328 appear below the point where the handler is set, unless a
329 different action was set for the same condition between the first
332 >EXEC SQL WHENEVER</TT
333 > and the SQL statement causing
334 the condition, regardless of the flow of control in the C program.
335 So neither of the two following C program excerpts will have the
338 CLASS="PROGRAMLISTING"
342 int main(int argc, char *argv[])
346 EXEC SQL WHENEVER SQLWARNING SQLPRINT;
355 CLASS="PROGRAMLISTING"
359 int main(int argc, char *argv[])
368 static void set_error_handler(void)
370 EXEC SQL WHENEVER SQLERROR STOP;
384 > For a more powerful error handling, the embedded SQL interface
385 provides a global variable with the name <VAR
389 that has the following structure:
391 CLASS="PROGRAMLISTING"
408 (In a multithreaded program, every thread automatically gets its
412 >. This works similar to the
413 handling of the standard C global variable
423 > covers both warnings and errors. If
424 multiple warnings or errors occur during the execution of a
429 information about the last one.
432 > If no error occurred in the last <ACRONYM
447 >. If a warning or error occurred, then
451 > will be negative and
455 > will be different from
463 > indicates a harmless condition,
464 such as that the last query returned zero rows.
472 different error code schemes; details appear below.
475 > If the last SQL statement was successful, then
478 >sqlca.sqlerrd[1]</TT
479 > contains the OID of the
480 processed row, if applicable, and
483 >sqlca.sqlerrd[2]</TT
484 > contains the number of
485 processed or returned rows, if applicable to the command.
488 > In case of an error or warning,
491 >sqlca.sqlerrm.sqlerrmc</TT
492 > will contain a string
493 that describes the error. The field
496 >sqlca.sqlerrm.sqlerrml</TT
497 > contains the length of
498 the error message that is stored in
501 >sqlca.sqlerrm.sqlerrmc</TT
506 >, not really interesting for a C
510 > In case of a warning, <TT
512 >sqlca.sqlwarn[2]</TT
517 >. (In all other cases, it is set to
518 something different from <TT
524 >sqlca.sqlwarn[1]</TT
529 >, then a value was truncated when it was
530 stored in a host variable. <TT
532 >sqlca.sqlwarn[0]</TT
537 > if any of the other elements are set
538 to indicate a warning.
552 >, and the remaining elements of
560 > currently contain no useful
567 > is not defined in the SQL
568 standard, but is implemented in several other SQL database
569 systems. The definitions are similar in the core, but if you want
570 to write portable applications, then you should investigate the
571 different implementations carefully.
596 > are two different schemes that
597 provide error codes. Both are specified in the SQL standard, but
601 > has been marked deprecated in the 1992
602 edition of the standard and has been dropped in the 1999 edition.
603 Therefore, new applications are strongly encouraged to use
613 > is a five-character array. The five
614 characters contain digits or upper-case letters that represent
615 codes of various error and warning conditions.
619 > has a hierarchical scheme: the first
620 two characters indicate the general class of the condition, the
621 last three characters indicate a subclass of the general
622 condition. A successful state is indicated by the code
630 the most part defined in the SQL standard. The
634 > server natively supports
638 > error codes; therefore a high degree
639 of consistency can be achieved by using this error code scheme
640 throughout all applications. For further information see
642 HREF="errcodes-appendix.html"
650 >, the deprecated error code scheme, is a
651 simple integer. A value of 0 indicates success, a positive value
652 indicates success with additional information, a negative value
653 indicates an error. The SQL standard only defines the positive
654 value +100, which indicates that the last command returned or
655 affected zero rows, and no specific negative values. Therefore,
656 this scheme can only achieve poor portability and does not have a
657 hierarchical code assignment. Historically, the embedded SQL
665 > values for its use, which
666 are listed below with their numeric value and their symbolic name.
667 Remember that these are not portable to other SQL implementations.
668 To simplify the porting of applications to the
672 > scheme, the corresponding
676 > is also listed. There is, however, no
677 one-to-one or one-to-many mapping between the two schemes (indeed
678 it is many-to-many), so you should consult the global
683 HREF="errcodes-appendix.html"
689 > These are the assigned <TT
702 >ECPG_OUT_OF_MEMORY</TT
706 > Indicates that your virtual memory is exhausted. (SQLSTATE
713 >ECPG_UNSUPPORTED</TT
717 > Indicates the preprocessor has generated something that the
718 library does not know about. Perhaps you are running
719 incompatible versions of the preprocessor and the
720 library. (SQLSTATE YE002)
726 >ECPG_TOO_MANY_ARGUMENTS</TT
730 > This means that the command specified more host variables than
731 the command expected. (SQLSTATE 07001 or 07002)
737 >ECPG_TOO_FEW_ARGUMENTS</TT
741 > This means that the command specified fewer host variables than
742 the command expected. (SQLSTATE 07001 or 07002)
748 >ECPG_TOO_MANY_MATCHES</TT
752 > This means a query has returned multiple rows but the statement
753 was only prepared to store one result row (for example, because
754 the specified variables are not arrays). (SQLSTATE 21000)
764 > The host variable is of type <TT
768 the database is of a different type and contains a value that
769 cannot be interpreted as an <TT
776 > for this conversion. (SQLSTATE
783 >ECPG_UINT_FORMAT</TT
787 > The host variable is of type <TT
791 datum in the database is of a different type and contains a
792 value that cannot be interpreted as an <TT
796 >. The library uses <CODE
800 for this conversion. (SQLSTATE 42804)
806 >ECPG_FLOAT_FORMAT</TT
810 > The host variable is of type <TT
814 in the database is of another type and contains a value that
815 cannot be interpreted as a <TT
822 > for this conversion.
829 >ECPG_CONVERT_BOOL</TT
833 > This means the host variable is of type <TT
837 the datum in the database is neither <TT
854 > The statement sent to the <SPAN
858 server was empty. (This cannot normally happen in an embedded
859 SQL program, so it may point to an internal error.) (SQLSTATE
866 >ECPG_MISSING_INDICATOR</TT
870 > A null value was returned and no null indicator variable was
871 supplied. (SQLSTATE 22002)
881 > An ordinary variable was used in a place that requires an
882 array. (SQLSTATE 42804)
888 >ECPG_DATA_NOT_ARRAY</TT
892 > The database returned an ordinary variable in a place that
893 requires array value. (SQLSTATE 42804)
903 > The program tried to access a connection that does not exist.
914 > The program tried to access a connection that does exist but is
915 not open. (This is an internal error.) (SQLSTATE YE002)
921 >ECPG_INVALID_STMT</TT
925 > The statement you are trying to use has not been prepared.
932 >ECPG_UNKNOWN_DESCRIPTOR</TT
936 > The descriptor specified was not found. The statement you are
937 trying to use has not been prepared. (SQLSTATE 33000)
943 >ECPG_INVALID_DESCRIPTOR_INDEX</TT
947 > The descriptor index specified was out of range. (SQLSTATE
954 >ECPG_UNKNOWN_DESCRIPTOR_ITEM</TT
958 > An invalid descriptor item was requested. (This is an internal
959 error.) (SQLSTATE YE002)
965 >ECPG_VAR_NOT_NUMERIC</TT
969 > During the execution of a dynamic statement, the database
970 returned a numeric value and the host variable was not numeric.
977 >ECPG_VAR_NOT_CHAR</TT
981 > During the execution of a dynamic statement, the database
982 returned a non-numeric value and the host variable was numeric.
993 > Some error caused by the <SPAN
997 server. The message contains the error message from the
1014 > server signaled that
1015 we cannot start, commit, or rollback the transaction.
1026 > The connection attempt to the database did not succeed.
1037 > This is a harmless condition indicating that the last command
1038 retrieved or processed zero rows, or that you are at the end of
1039 the cursor. (SQLSTATE 02000)
1053 SUMMARY="Footer navigation table"
1064 HREF="ecpg-descriptors.html"
1082 HREF="ecpg-include.html"
1092 >Using SQL Descriptor Areas</TD
1106 >Including Files</TD