Applications issuing SQL statements and calling database manager APIs must properly check for error conditions by examining return codes and the SQLCA structure.
Most database manager APIs pass back a zero return code when successful. In general, a non-zero return code indicates that the secondary error handling mechanism, the SQLCA structure, may be corrupt. In this case, the called API is not executed. A possible cause for a corrupt SQLCA structure is passing an invalid address for the structure.
Error information is returned in the SQLCODE and SQLSTATE fields of the SQLCA structure, which is updated after every executable SQL statement and most database manager API calls.
A source file containing executable SQL statements can provide at least one SQLCA structure with the name sqlca. The SQLCA structure is defined in the SQLCA include file. Source files without embedded SQL statements, but calling database manager APIs, can also provide one or more SQLCA structures, but their names are arbitrary.
If your application is compliant with the FIPS 127-2 standard, you can declare the SQLSTATE and SQLCODE as host variables instead of using the SQLCA structure. For information on how to do this, see SQLSTATE and SQLCODE Variables for C or C++ applications, SQLSTATE and SQLCODE Variables for COBOL applications, or SQLSTATE and SQLCODE Variables for FORTRAN applications.
An SQLCODE value of 0 means successful execution (with possible SQLWARN warning conditions). A positive value means that the statement was successfully executed but with a warning, as with truncation of a host variable. A negative value means that an error condition occurred.
An additional field, SQLSTATE, contains a standardized error code consistent across other IBM database products and across SQL92 conformant database managers. Practically speaking, you should use SQLSTATEs when you are concerned about portability since SQLSTATEs are common across many database managers.
The SQLWARN field contains an array of warning indicators, even if SQLCODE is zero. The first element of the SQLWARN array, SQLWARN0, contains a blank if all other elements are blank. SQLWARN0 contains a W if at least one other element contains a warning character.
Refer to the Administrative API Reference for more information about the SQLCA structure, and the Message Reference for a listing of SQLCODE and SQLSTATE error conditions.
| Note: | If you want to develop applications that access various IBM RDBMS servers you
should:
|
Since tokens may be truncated in the SQLCA structure, you should not use the token info for diagnostic purposes. While you can define table and column names with lengths of up to 128 bytes, the SQLCA tokens will be truncated to 17 bytes plus a truncation terminator (>). Application logic should not depend on actual values of the sqlerrmc field. Refer to the SQL Reference for a description of the SQLCA structure, and a discussion of token truncation.
The WHENEVER statement causes the precompiler to generate source code that directs the application to go to a specified label if an error, warning, or if no rows are found during execution. The WHENEVER statement affects all subsequent executable SQL statements until another WHENEVER statement alters the situation.
The WHENEVER statement has three basic forms:
EXEC SQL WHENEVER SQLERROR action
EXEC SQL WHENEVER SQLWARNING action
EXEC SQL WHENEVER NOT FOUND action
In the above statements:
In each case, the action can be either of the following:
If the WHENEVER statement is not used, the default action is to continue processing if an error, warning, or exception condition occurs during execution.
The WHENEVER statement must appear before the SQL statements you want to affect. Otherwise, the precompiler does not know that additional error-handling code should be generated for the executable SQL statements. You can have any combination of the three basic forms active at any time. The order in which you declare the three forms is not significant. To avoid an infinite looping situation, ensure that you undo the WHENEVER handling before any SQL statements are executed inside the handler. You can do this using the WHENEVER CONTINUE statement.
For a complete description of the WHENEVER statement, refer to the SQL Reference.
An exception, signal, or interrupt handler is a routine that gets control when an exception, signal, or interrupt occurs. The type of handler applicable is determined by your operating environment, as shown in the following:
For other operating systems that are not in the above list, refer to the Application Building Guide.
Do not put SQL statements (other than COMMIT or ROLLBACK) in exception, signal, and interrupt handlers. With these kinds of error conditions, you normally want to do a ROLLBACK to avoid the risk of inconsistent data.
Note that you should exercise caution when coding a COMMIT and ROLLBACK in exception/signal/interrupt handlers. If you call either of these statements by themselves, the COMMIT or ROLLBACK is not executed until the current SQL statement is complete, if one is running. This is not the behavior desired from a Ctrl-C handler.
The solution is to call the INTERRUPT API (sqleintr/sqlgintr) before issuing a ROLLBACK. This interrupts the current SQL query (if the application is executing one) and lets the ROLLBACK begin immediately. If you are going to perform a COMMIT rather than a ROLLBACK, you do not want to interrupt the current command.
When using APPC to access a remote database server (DB2 for AIX or host database system using DB2 Connect), the application may receive a SIGUSR1 signal. This signal is generated by SNA Services/6000 when an unrecoverable error occurs and the SNA connection is stopped. You may want to install a signal handler in your application to handle SIGUSR1.
Refer to your platform documentation for specific details on the various handler considerations.
Do not use SQL or DB2 API calls in exit list routines. Note that you cannot disconnect from a database in an exit routine.
The code clips shown in C Example: UTIL.C, COBOL Example: CHECKERR.CBL, and FORTRAN Example: UTIL.F, demonstrate the use of the GET ERROR MESSAGE API to obtain the corresponding information related to the SQLCA passed in.
You can find information on building these examples in the README files, or in the header section of these sample programs.
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlenv.h>
#include <sqlda.h>
#include <sqlca.h>
#include <string.h>
#include <ctype.h>
/* #include "system.h" */
#ifdef DB268K
/* Need to include ASLM for 68K applications */
#include <LibraryManager.h>
#endif
#define SQLSTATE sqlca.sqlstate
#ifndef max
#define max(A, B) ((A) > (B) ? (A) : (B))
#endif
#ifndef min
#define min(A, B) ((A) > (B) ? (B) : (A))
#endif
#define CHECKERR(CE_STR) check_error (CE_STR, &sqlca)
#define LOBLENGTH 29
#if defined(DB2OS2)
#include <conio.h>
#elif defined (DB2AIX)
#include <curses.h>
#endif
short *errdata = NULL; /* Hold error counts */
#define mem_error(xx) \
{ printf("SQCERROR--Out of memory when %s.\n",xx); \
exit(-1); }
#define MAXERRORS 65536
struct lob_file {
unsigned long name_length;
unsigned long data_length;
unsigned long file_option;
char name[255];
};
struct col_info {
char name[19]; /* name of column */
int coltype; /* column type */
short length, /* length of the column */
colno; /* column number */
};
struct tab_info {
char name[19], /* name of table */
creator[9], /* creator of the table */
type;
short colcount; /* number of columns */
struct col_info **alpha_list, /* alphabetical listing of columns */
**num_list; /* numerical listing of columns */
};
/*************************************************************************
** Procedure : check_error
**
** Purpose : This procedure checks the SQLCACODE flag and prints out any
** information that is available related to the specific error.
**
*************************************************************************/
int check_error (char eString[], struct sqlca *caPointer) {
char eBuffer[1024];
char sBuffer[1024];
short rc, Erc;
if (caPointer->sqlcode != 0) {
printf ("--- error report ---\n");
printf ("ERROR occurred : %s.\nSQLCODE : %ld\n", eString,
caPointer->sqlcode);
/**********************\
* GET SQLSTATE MESSAGE *
\**********************/
rc = sqlogstt (sBuffer, 1024, 80, caPointer->sqlstate);
/******************************\
* GET ERROR MESSAGE API called *
\******************************/
Erc = sqlaintp (eBuffer, 1024, 80, caPointer);
/* return code is the length of the eBuffer string */
if (Erc > 0) printf ("%s", eBuffer);
if (caPointer->sqlcode < 0) {
if (rc == 0) {
printf ("\n%s", sBuffer);
}
printf ("--- end error report ---\n");
return 1;
} else {
/* errorCode is just a Warning message */
if (rc == 0) {
printf ("\n%s", sBuffer);
}
printf ("--- end error report ---\n");
printf ("WARNING - CONTINUING PROGRAM WITH WARNINGS!\n");
return 0;
} /* endif */
} /* endif */
return 0;
}
.
.
.
/* functions / procedures on setting up and outputting info from SQLDA */
.
.
.
JDBC and SQLJ applications throw an SQLException when an error occurs during SQL processing. Your applications can catch and display an SQLException with the following code:
try {
Statement stmt = connection.createStatement();
int rowsDeleted = stmt.executeUpdate(
"DELETE FROM employee WHERE empno = '000010'");
System.out.println( rowsDeleted + " rows were deleted");
}
catch (SQLException sqle) {
System.out.println(sqle);
}
For more information on handling SQLExceptions, see SQLSTATE and SQLCODE Values.
Identification Division.
Program-ID. "checkerr".
Data Division.
Working-Storage Section.
copy "sql.cbl".
* Local variables
77 error-rc pic s9(9) comp-5.
77 state-rc pic s9(9) comp-5.
* Variables for the GET ERROR MESSAGE API
* Use application specific bound instead of BUFFER-SZ
* 77 buffer-size pic s9(4) comp-5 value BUFFER-SZ.
* 77 error-buffer pic x(BUFFER-SZ).
* 77 state-buffer pic x(BUFFER-SZ).
77 buffer-size pic s9(4) comp-5 value 1024.
77 line-width pic s9(4) comp-5 value 80.
77 error-buffer pic x(1024).
77 state-buffer pic x(1024).
Linkage Section.
copy "sqlca.cbl" replacing ==VALUE "SQLCA "== by == ==
==VALUE 136== by == ==.
01 errloc pic x(80).
Procedure Division using sqlca errloc.
Checkerr Section.
if SQLCODE equal 0
go to End-Checkerr.
display "--- error report ---".
display "ERROR occurred : ", errloc.
display "SQLCODE : ", SQLCODE.
********************************
* GET ERROR MESSAGE API called *
********************************
call "sqlgintp" using
by value buffer-size
by value line-width
by reference sqlca
by reference error-buffer
returning error-rc.
************************
* GET SQLSTATE MESSAGE *
************************
call "sqlggstt" using
by value buffer-size
by value line-width
by reference sqlstate
by reference state-buffer
returning state-rc.
if error-rc is greater than 0
display error-buffer.
if state-rc is greater than 0
display state-buffer.
if state-rc is less than 0
display "return code from GET SQLSTATE =" state-rc.
if SQLCODE is less than 0
display "--- end error report ---"
go to End-Prog.
display "--- end error report ---"
display "CONTINUING PROGRAM WITH WARNINGS!".
End-Checkerr. exit program.
End-Prog. stop run.
subroutine checkerr (ca, errloc, *)
character ca(136)
character*60 errloc
include 'sqlenv.f'
include 'sqlutil.f'
include 'sqlca_cn.f'
include 'sql.f'
integer*4 error_rc
integer*4 state_rc
integer*2 line_width
* Use application-specifice bound instead of INTBUFSZ
* integer*2 buffer_size /INTBUFSZ/
* character error_buffer(INTBUFSZ)
* character state_buffer(INTBUFSZ)
integer*2 buffer_size /1024/
character error_buffer(1024)
character state_buffer(1024)
error_rc = sqlgmcpy (%ref(sqlca),
+ %ref(ca),
+ %val(136))
if ( sqlcode .ne. 0 ) then
print *, '--- error report ---'
print *, 'ERROR occurred : ', errloc
*************************
* GET ERROR MESSAGE *
*************************
error_rc = sqlgintp ( %val(buffer_size),
+ %val(line_width),
+ %ref(sqlca),
+ %ref(error_buffer))
****************************
* GET SQLSTATE MESSAGE *
****************************
state_rc = sqlggstt (%val(buffer_size),
+ %val(line_width),
+ %ref(sqlstate),
+ %ref(state_buffer))
if (state_rc .gt. 0) then
print *, (state_buffer(i), i=1,state_rc)
endif
if (state_rc .lt. 0) then
print *, 'return code from GET SQLSTATE = ', state_rc
endif
print *, 'SQLCODE : ', sqlcode
if (error_rc .gt. 0) then
print *, (error_buffer(i), i=1,error_rc)
print *, '--- end error report ---'
if (sqlcode .lt. 0) then
return 1
endif
if (sqlcode .gt. 0) then
print *, 'CONTINUING PROGRAM WITH WARNINGS'
return
endif
endif
print *, 'DID NOT GET ERROR MESSAGE'
return 1
endif
return
end
/* this variable (SYSTEM) must be user defined */
SYSTEM = AIX
if SYSTEM = OS2 then do
if RxFuncQuery('SQLDBS') <> 0 then
rcy = RxFuncAdd( 'SQLDBS', 'DB2AR', 'SQLDBS' )
if RxFuncQuery('SQLEXEC') <> 0 then
rcy = RxFuncAdd( 'SQLEXEC', 'DB2AR', 'SQLEXEC' )
end
if SYSTEM = AIX then
rcy = SysAddFuncPkg("db2rexx")
.
.
.
call CHECKERR 'INSERT'
.
.
.
CHECKERR:
arg errloc
if ( SQLCA.SQLCODE = 0 ) then
return 0
else do
say '--- error report ---'
say 'ERROR occurred :' errloc
say 'SQLCODE :' SQLCA.SQLCODE
/******************************\
* GET ERROR MESSAGE API called *
\******************************/
call SQLDBS 'GET MESSAGE INTO :errmsg LINEWIDTH 80'
say errmsg
say '--- end error report ---'
if (SQLCA.SQLCODE < 0 ) then
exit
else do
say 'WARNING - CONTINUING PROGRAM WITH ERRORS'
return 0
end
end
return 0