Retrieving Status and Error Information

RETRIEVING STATUS AND ERROR INFORMATION

This chapter defines the ODBC return codes and error handling protocol. The return codes indicate whether a function succeeded, succeeded but returned a warning, or failed. The error handling protocol defines how the components in an ODBC connection construct and return error messages through SQLError.

The protocol describes:

Use of the error text to identify the source of an error.

Rules to ensure consistent and useful error information.

Responsibility for setting the ODBC SQLSTATE based on the native error.

Function Return Codes

When an application calls an ODBC function, the driver executes the function and returns a predefined code. These return codes indicate success, warning, or failure status. The following table defines the return codes.

Return Code	Description
SQL_SUCCESS	Function completed successfully; no additional information is available.
SQL_SUCCESS_WITH_INFO	Function completed successfully, possibly with a nonfatal error. The application can call SQLError to retrieve additional information.
SQL_NO_DATA_FOUND	All rows from the result set have been fetched.
SQL_ERROR	Function failed. The application can call SQLError to retrieve error information.
SQL_INVALID_HANDLE	Function failed due to an invalid environment handle, connection handle, or statement handle. This indicates a programming error. No additional information is available from SQLError.
SQL_STILL_EXECUTING	A function that was started asynchronously is still executing.
SQL_NEED_DATA	While processing a statement, the driver determined that the application needs to send parameter data values.

The application is responsible for taking the appropriate action based on the return code.

Retrieving Error Messages

If an ODBC function other than SQLError returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an application can call SQLError to obtain additional information. The application may need to call SQLError more than once to retrieve all the error messages from a function, since a function may return more than one error message. When the application calls a different function, the error messages from the previous function are deleted.

Additional error or status information can come from one of two sources:

Error or status information from an ODBC function, indicating that a programming error was detected.

Error or status information from the data source, indicating that an error occurred during SQL statement processing.

The information returned by SQLError is in the same format as that provided by SQLSTATE in the X/Open and SQL Access Group SQL CAE specification (1992). Note that SQLError never returns error information about itself.

ODBC Error Messages

ODBC defines a layered architecture to connect an application to a data source. At its simplest, an ODBC connection requires two components: the Driver Manager and a driver.

A more complex connection might include more components: the Driver Manager, a number of drivers, and a (possibly different) number of DBMS’s. The connection might cross computing platforms and operating systems and use a variety of networking protocols.

As the complexity of an ODBC connection increases, so does the importance of providing consistent and complete error messages to the application, its users, and support personnel. Error messages must not only explain the error, but also provide the identity of the component in which it occurred. The identity of the component is particularly important to support personnel when an application uses ODBC components from more than one vendor. Because SQLError does not return the identity of the component in which the error occurred, this information must be embedded in the error text.

Error Text Format

Error messages returned by SQLError come from two sources: data sources and components in an ODBC connection. Typically, data sources do not directly support ODBC. Consequently, if a component in an ODBC connection receives an error message from a data source, it must identify the data source as the source of the error. It must also identify itself as the component that received the error.

If the source of an error is the component itself, the error message must explain this. Therefore, the error text returned by SQLError has two different formats: one for errors that occur in a data source and one for errors that occur in other components in an ODBC connection.

For errors that do not occur in a data source, the error text must use the format:

[vendor-identifier][ODBC-component-identifier]

   component-supplied-text

For errors that occur in a data source, the error text must use the format:

[vendor-identifier][ODBC-component-identifier]

   [data-source-identifier] data-source-supplied-text

The following table shows the meaning of each element.

Element	Meaning
vendor-identifier	Identifies the vendor of the component in which the error occurred or that received the error directly from the data source.
ODBC-component-identifier	Identifies the component in which the error occurred or that received the error directly from the data source.
data-source-identifier	Identifies the data source. For single-tier drivers, this is typically a file format, such as Xbase¹. For multiple-tier drivers, this is the DBMS product.
component-supplied-text	Generated by the ODBC component.
data-source-supplied-text	Generated by the data source.
1In this case, the driver is acting as both the driver and the data source.

Note that the brackets ([ ]) are included in the error text; they do not indicate optional items.

Sample Error Messages

The following are examples of how various components in an ODBC connection might generate the text of error messages and how various drivers might return them to the application with SQLError. Note that these examples do not represent actual implementations of the error handling protocol. For more information on how an individual driver has implemented the protocol, see the documentation for that driver.

Single-Tier Driver

A single-tier driver acts both as an ODBC driver and as a data source. It can therefore generate errors both as a component in an ODBC connection and as a data source. Because it also is the component that interfaces with the Driver Manager, it formats and returns arguments for SQLError.

For example, if a Microsoft driver for dBASE® could not allocate sufficient memory, it might return the following arguments for SQLError:

szSQLState = "S1001"

pfNativeError = NULL

szErrorMsg = "[Microsoft][ODBC dBASE Driver]Unable to    allocate sufficient memory."

pcbErrorMsg = 67

Because this error was not related to the data source, the driver only added prefixes to the error text for the vendor ([Microsoft]) and the driver ([ODBC dBASE Driver]).

If the driver could not find the file EMPLOYEE.DBF, it might return the following arguments for SQLError:

szSQLState = "S0002"

pfNativeError = NULL

szErrorMsg = "[Microsoft][ODBC dBASE Driver][dBASE]    Invalid file name;file EMPLOYEE.DBF not found."

pcbErrorMsg = 83

Because this error was related to the data source, the driver added the file format of the data source ([dBASE]) as a prefix to the error text. Because the driver was also the component that interfaced with the data source, it added prefixes for the vendor ([Microsoft]) and the driver ([ODBC dBASE Driver]).

Multiple-Tier Driver

A multiple-tier driver sends requests to a DBMS and returns information to the application through the Driver Manager. Because it is the component that interfaces with the Driver Manager, it formats and returns arguments for SQLError.

For example, if a Microsoft driver for DEC’s Rdb using SQL/Services encountered a duplicate cursor name, it might return the following arguments for SQLError:

szSQLState = "3C000"

pfNativeError = NULL

szErrorMsg = "[Microsoft][ODBC Rdb Driver]

   Duplicate cursor name:EMPLOYEE_CURSOR."

pcbErrorMsg = 67

Because the error occurred in the driver, it added prefixes to the error text for the vendor ([Microsoft]) and the driver ([ODBC Rdb Driver]).

If the DBMS could not find the table EMPLOYEE, the driver might format and return the following arguments for SQLError:

szSQLState = "S0002"

pfNativeError = -1

szErrorMsg = "[Microsoft][ODBC Rdb Driver][Rdb] 

   %SQL-F-RELNOTDEF, Table EMPLOYEE is not defined in    schema."

pcbErrorMsg = 92

Because the error occurred in the data source, the driver added a prefix for the data source identifier ([Rdb]) to the error text. Because the driver was the component that interfaced with the data source, it added prefixes for its vendor ([Microsoft]) and identifier ([ODBC Rdb Driver]) to the error text.

Gateways

In a gateway architecture, a driver sends requests to a gateway that supports ODBC. The gateway sends the requests to a DBMS. Because it is the component that interfaces with the Driver Manager, the driver formats and returns arguments for SQLError.

For example, if DEC based a gateway to Rdb on Microsoft Open Data Services, and Rdb could not find the table EMPLOYEE, the gateway might generate the following error text:

"[S0002][-1][DEC][ODS Gateway][Rdb]%SQL-F-RELNOTDEF,    Table EMPLOYEE is not defined in schema."

Because the error occurred in the data source, the gateway added a prefix for the data source identifier ([Rdb]) to the error text. Because the gateway was the component that interfaced with the data source, it added prefixes for its vendor ([DEC]) and identifier ([ODS Gateway]) to the error text. Note that it also added the SQLSTATE value and the Rdb error code to the beginning of the error text. This permitted it to preserve the semantics of its own message structure and still supply the ODBC error information to the driver.

Because the gateway driver is the component that interfaces with the Driver Manager, it would use the preceding error text to format and return the following arguments for SQLError:

szSQLState = "S0002"

pfNativeError = -1

szErrorMsg = "[DEC][ODS Gateway][Rdb]%SQL-F-RELNOTDEF,    Table EMPLOYEE is not defined in schema."

pcbErrorMsg = 81

Driver Manager

The Driver Manager can also generate error messages. For example, if an application passed an invalid argument value to SQLDataSources, the Driver Manager might format and return the following arguments for SQLError:

szSQLState = "S1009"

pfNativeError = NULL

szErrorMsg = "[Microsoft][ODBC DLL]Invalid argument    value: SQLDataSources."

pcbErrorMsg = 60

Because the error occurred in the Driver Manager, it added prefixes to the error text for its vendor ([Microsoft]) and its identifier ([ODBC DLL]).

Processing Error Messages

Applications should provide users with all the error information available through SQLError: the ODBC SQLSTATE, the native error code, the error text, and the source of the error. The application may parse the error text to separate the text from the information identifying the source of the error. It is the application’s responsibility to take appropriate action based on the error or provide the user with a choice of actions.