SQLSTATEs

SQLSTATEs provide detailed information about the cause of a warning or error. The SQLSTATEs in this manual are based on those found in the ISO/IEF CLI specification, although those SQLSTATEs that start with IM are specific to ODBC.

Unlike return codes, the SQLSTATEs in this manual are guidelines, and drivers are not required to return them. Thus, while drivers should return the proper SQLSTATE for any error or warning they are capable of detecting, applications should not count on this always occurring. The reasons for this situation are two-fold:

Because SQLSTATEs are not returned reliably, most applications just display them to the user along with their associated diagnostic message, which is often tailored to the specific error or warning that occurred, and native error code. There is rarely any loss of functionality in doing this, because applications cannot base programming logic on most SQLSTATEs anyway. For example, suppose SQLExecDirect returns SQLSTATE 42000 (Syntax error or access violation). If the SQL statement that caused this error is hard-coded or built by the application, this is a programming error and the code needs to be fixed. If the SQL statement is entered by the user, this is a user error and the application has done all that is possible by informing the user of the problem.

When applications do base programming logic on SQLSTATEs, they should be prepared for the SQLSTATE not to be returned or for a different SQLSTATE to be returned. Exactly which SQLSTATEs are returned reliably can be based only on experience with numerous drivers. However, a general guideline is that SQLSTATEs for errors that occur in the driver or Driver Manager, as opposed to the data source, are more likely to be returned reliably. For example, most drivers probably return SQLSTATE HYC00 (Optional feature not implemented) while fewer drivers probably return SQLSTATE 42021 (Column already exists).

The following SQLSTATEs indicate run-time errors or warnings and are good candidates on which to base programming logic. However, there is no guarantee that all drivers return them.

SQLSTATE HYC00 (Optional feature not implemented) is particularly significant, because it is the only way in which an application can determine whether a driver supports a particular statement or connection attribute.

For a complete list of SQLSTATEs and what functions return them, see Appendix A, “ODBC Error Codes.” For a detailed explanation of the conditions under which each function might return a particular SQLSTATE, see that function.