SQLError - Retrieve error information

SQLError() returns the diagnostic information associated with the most recently called Db2® for i CLI function for a particular statement, connection, or environment handle.

The information consists of a standardized SQLSTATE, an error code, and a text message. Refer to Diagnostics in a Db2 for i CLI application for more information.

Call SQLError() after receiving a return code of SQL_ERROR or SQL_SUCCESS_WITH_INFO from another function call.

Unicode (UTF-16) equivalent: This function can also be used with the Unicode (UTF-16) character set. The corresponding Unicode function is SQLErrorW(). Refer to Unicode in Db2 for i CLI for more information about Unicode support for DB2® CLI.

Syntax

SQLRETURN SQLError (SQLHENV       henv,
                    SQLHDBC       hdbc,
                    SQLHSTMT      hstmt,
                    SQLCHAR       *szSqlState,
                    SQLINTEGER    *pfNativeError,
                    SQLCHAR       *szErrorMsg,
                    SQLSMALLINT   cbErrorMsgMax,
                    SQLSMALLINT   *pcbErrorMsg);

Function arguments

Table 1. SQLError arguments
Data type	Argument	Use	Description
SQLHENV	henv	Input	Environment handle. To obtain diagnostic information associated with an environment, pass a valid environment handle. Set hdbc to SQL_NULL_HDBC. Set hstmt to SQL_NULL_HSTMT.
SQLHDBC	hdbc	Input	Database connection handle. To obtain diagnostic information associated with a connection, pass a valid database connection handle, and set hstmt to SQL_NULL_HSTMT. The henv argument is ignored.
SQLHSTMT	hstmt	Input	Statement handle. To obtain diagnostic information associated with a statement, pass a valid statement handle. The henv and hdbc arguments are ignored.
SQLCHAR *	szSqlState	Output	SQLSTATE as a string of 5 characters terminated by a null character. The first 2 characters indicate error class; the next 3 indicate subclass. The values correspond directly to SQLSTATE values defined in the X/Open SQL CAE specification and the ODBC specification, augmented with IBM® specific and product specific SQLSTATE values.
SQLINTEGER *	pfNativeError	Output	Native error code. In Db2 for i CLI, the pfNativeError argument contains the SQLCODE value returned by the Database Management System (DBMS). If the error is generated by Db2 for i CLI and not the DBMS, this field is set to -99999.
SQLCHAR *	szErrorMsg	Output	Pointer to buffer to contain the implementation defined message text. In Db2 for i CLI, only the DBMS generated messages is returned; Db2 for i CLI itself does not return any message text describing the problem.
SQLSMALLINT	cbErrorMsgMax	Input	Maximum (that is, the allocated) length of the buffer szErrorMsg. The recommended length to allocate is SQL_MAX_MESSAGE_LENGTH + 1.
SQLSMALLINT *	pcbErrorMsg	Output	Pointer to total number of bytes available to return to the szErrorMsg buffer.

Usage

The SQLSTATEs are those defined by the X/OPEN SQL CAE and the X/Open SQL CLI snapshot, augmented with IBM specific and product specific SQLSTATE values.

To obtain diagnostic information associated with an environment, pass a valid environment handle. Set hdbc to SQL_NULL_HDBC. Set hstmt to SQL_NULL_HSTMT.
To obtain diagnostic information associated with a connection, pass a valid database connection handle, and set hstmt to SQL_NULL_HSTMT. The henv argument is ignored.
To obtain diagnostic information associated with a statement, pass a valid statement handle. The henv and hdbc arguments are ignored.

If diagnostic information generated by one Db2 for i CLI function is not retrieved before a function other than SQLError() is called with the same handle, the information for the previous function call is lost. This is true whether diagnostic information is generated for the second Db2 for i CLI function call.

To avoid truncation of the first level error message, declare a buffer length of SQL_MAX_MESSAGE_LENGTH + 1. To avoid truncation of the second level error message, set the size of the buffer to a value greater than SQL_MAX_MESSAGE_LENGTH.

Return codes

SQL_ERROR
SQL_INVALID_HANDLE
SQL_NO_DATA_FOUND
SQL_SUCCESS

Diagnostics

SQLSTATEs are not defined because SQLError() does not generate diagnostic information for itself. SQL_ERROR is returned if argument szSqlState, pfNativeError, szErrorMsg, or pcbErrorMsg is a null pointer.

Example

Note: By using the code examples, you agree to the terms of the Code license and disclaimer information.

/*************************************************************************
** file = typical.c
************************************************************************/
int print_error (SQLHENV    henv,
                    SQLHDBC    hdbc,
                    SQLHSTMT   hstmt)
{
SQLCHAR     buffer[SQL_MAX_MESSAGE_LENGTH + 1];
SQLCHAR     sqlstate[SQL_SQLSTATE_SIZE + 1];
SQLINTEGER  sqlcode;
SQLSMALLINT length;
 
 
    while ( SQLError(henv, hdbc, hstmt, sqlstate, &sqlcode, buffer,
                     SQL_MAX_MESSAGE_LENGTH + 1, &length) == SQL_SUCCESS )
    {
        printf("\n **** ERROR *****\n");
        printf("         SQLSTATE: %s\n", sqlstate);
        printf("Native Error Code: %ld\n", sqlcode);
        printf("%s \n", buffer);
    };
    return (0);
 
}