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 in C and C++ for C or C++ applications, SQLSTATE and SQLCODE Variables in COBOL for COBOL applications, or SQLSTATE and SQLCODE Variables in FORTRAN 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 SQLERROR 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: UTILAPI.C and COBOL Example: CHECKERR.CBL 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 "utilemb.h"
EXEC SQL INCLUDE SQLCA;
/*#############################################################################
** 1. SQL_CHECK section
**
** 1.1 - SqlInfoPrint - prints on the screen everything that
** goes unexpected.
** 1.2 - TransRollback - rolls back the transaction
#############################################################################*/
/******************************************************************************
** 1.1 - SqlInfoPrint - prints on the screen everything that
** goes unexpected.
******************************************************************************/
int SqlInfoPrint( char * appMsg,
struct sqlca * pSqlca,
int line,
char * file )
{ int rc = 0;
char sqlInfo[1024];
char sqlInfoToken[1024];
char sqlstateMsg[1024];
char errorMsg[1024];
if (pSqlca->sqlcode != 0 && pSqlca->sqlcode != 100)
{ strcpy(sqlInfo, "");
if( pSqlca->sqlcode < 0)
{ sprintf( sqlInfoToken, "\n---- error report ----\n");
strcat( sqlInfo, sqlInfoToken);
}
else
{ sprintf( sqlInfoToken, "\n---- warning report ----\n");
strcat( sqlInfo, sqlInfoToken);
} /* endif */
sprintf( sqlInfoToken, " app. message = %s\n", appMsg);
strcat( sqlInfo, sqlInfoToken);
sprintf( sqlInfoToken, " line = %d\n", line);
strcat( sqlInfo, sqlInfoToken);
sprintf( sqlInfoToken, " file = %s\n", file);
strcat( sqlInfo, sqlInfoToken);
sprintf( sqlInfoToken, " SQLCODE = %ld\n", pSqlca->sqlcode);
strcat( sqlInfo, sqlInfoToken);
/* get error message */
rc = sqlaintp( errorMsg, 1024, 80, pSqlca);
/* return code is the length of the errorMsg string */
if( rc > 0)
{ sprintf( sqlInfoToken, "%s\n", errorMsg);
strcat( sqlInfo, sqlInfoToken);
}
/* get SQLSTATE message */
rc = sqlogstt( sqlstateMsg, 1024, 80, pSqlca->sqlstate);
if (rc == 0)
{ sprintf( sqlInfoToken, "%s\n", sqlstateMsg);
strcat( sqlInfo, sqlInfoToken);
}
if( pSqlca->sqlcode < 0)
{ sprintf( sqlInfoToken, "--- end error report ---\n");
strcat( sqlInfo, sqlInfoToken);
printf("%s", sqlInfo);
return 1;
}
else
{ sprintf( sqlInfoToken, "--- end warning report ---\n");
strcat( sqlInfo, sqlInfoToken);
printf("%s", sqlInfo);
return 0;
} /* endif */
} /* endif */
return 0;
}
/******************************************************************************
** 1.2 - TransRollback - rolls back the transaction
******************************************************************************/
void TransRollback( )
{ int rc = 0;
/* rollback the transaction */
printf( "\nRolling back the transaction ...\n") ;
EXEC SQL ROLLBACK;
rc = SqlInfoPrint( "ROLLBACK", &sqlca, __LINE__, __FILE__);
if( rc == 0)
{ printf( "The transaction was rolled back.\n") ;
}
}
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 in Java.
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.
parse version rexxType .
parse source platform .
if platform == 'AIX/6000' & rexxType == 'REXXSAA' then
do
rcy = SysAddFuncPkg("db2rexx")
end
else
do
if RxFuncQuery('SQLDBS') <> 0 then
rcy = RxFuncAdd( 'SQLDBS', 'db2ar', 'SQLDBS' )
if RxFuncQuery('SQLEXEC') <> 0 then
rcy = RxFuncAdd( 'SQLEXEC', 'db2ar', 'SQLEXEC' )
end
·
·
·
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
/* 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