SQL For Dummies®

Chapter 20. Handling Errors

In This Chapter

• Flagging error conditions

• Branching to error‐handling code

• Determining the exact nature of an error

• Determining which DBMS generated an error condition

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.

Figure 20.1.  Figure 20-1: SQLSTATE status parameter ...