SQL For Dummies®, 7th Edition

Chapter 20. Handling Errors

Wouldn't it be great if every application you wrote worked perfectly every time? Yeah, and it would also be really cool to win $314.9 million playing Powerball. Unfortunately, both possibilities are equally unlikely to happen. Error conditions of one sort or another are inevitable, so it's helpful to know what causes them. SQL's mechanism for returning error information to you is the status parameter (or host variable) SQLSTATE. Based on the contents of SQLSTATE, you can take different actions to remedy the error condition.

For example, the WHENEVER directive enables you to take a predetermined action whenever a specified condition (if SQLSTATE has a non-zero value, for example) is met. You can also find detailed status information about the SQL statement that you just executed in the diagnostics area. In this chapter, I explain these helpful error-handling facilities and how to use them.

SQLSTATE

SQLSTATE specifies a large number of anomalous conditions. SQLSTATE is a five-character string in which only the uppercase letters A through Z and the numerals 0 through 9 are valid characters. The five-character string is divided into two groups: a two-character class code and a three-character subclass code. Figure 20-1 illustrates the SQLSTATE layout.

The SQL standard defines any class code that starts with the letters A through H or the numerals 0 through 4; therefore, these class codes mean the same thing in any implementation. Class codes that start with the ...