Recipe 9.3. Determining Whether a Statement Produced a Result Set

Problem

You just executed an SQL statement, but you're not sure whether it produced a result set.

Solution

Check the column count in the metadata. If the count is zero, there is no result set.

Discussion

If you write an application that accepts statement strings from an external source such as a file or a user entering text at the keyboard, you may not necessarily know whether it's a statement such as SELECT that produces a result set or a statement such as UPDATE that does not. That's an important distinction, because you process statements that produce a result set differently from those that do not. Assuming that no error occurred, one way to tell the difference is to check the metadata value that indicates the column count after executing the statement (as shown in Section 9.2). A column count of zero indicates that the statement was an INSERT, UPDATE, or some other statement that returns no result set. A nonzero value indicates the presence of a result set, and you can go ahead and fetch the rows. This technique distinguishes SELECT from non-SELECT statements, even for SELECT statements that return an empty result set. (An empty result is different from no result. The former returns no rows, but the column count is still correct; the latter has no columns at all.)

Some APIs provide ways to distinguish statement types other than checking the column count:

• In JDBC, you can issue arbitrary statements using the execute⁠(⁠ ⁠ ⁠) method, which directly indicates whether there is a result set by returning true or false.

• In PHP, PEAR DB programs should check the result from statement-execution methods to see whether the return value is a DB_result object:

  $result =& $conn->query ($stmt); if (PEAR::isError ($result))   die ("Statement failed\n"); if (is_a ($result, "DB_result")) {   # statement generates a result set } else {   # statement generates no result set } 

  
  

  Do this instead of checking the column count because attempting to invoke numCols⁠(⁠ ⁠ ⁠) on a result that isn't a DB_result object causes an error.

• In Python, the value of cursor.description is None for statements that produce no result set.