SQLite CVSTrac

In its default configuration, SQLite API routines return one of 26 integer result codes described at result-codes. However, experience has shown that many of these result codes are too coarse-grained. They do not provide as much information about problems as users might like. In an effort to address this, newer versions of SQLite (version 3.3.8 and later) include support for additional result codes that provide more detailed information about errors. The extended result codes are enabled (or disabled) for each database connection using the sqlite3_extended_result_codes() API.

We expect the number of extended result codes will be expand over time. Software that uses extended result codes should expect to see new result codes in future releases of SQLite.

You can get the codes like this assuming res contains a result:

  #define RESULT_CODE(res)             ((res)&0xff)
  #define EXTENDED_RESULT_CODE(res)    (res)

The symbolic name for an extended result code always contains a related primary result code as a prefix. Primary result codes contain a single "_" character. Extended result codes contain two or more "_" characters. The numeric value of an extended result code can be converted to its corresponding primary result code by masking off the lower 8 bits.

A complete list of available extended result codes and details about the meaning of the various extended result codes can be found by consulting the C code, especially the sqlite3.h header file and its antecedent sqlite.h.in.

As of 3.4.0, this is the list. Note how SQLITE_IOERR is the standard result code (in the low byte) with the upper bytes containing more detail.

  #define SQLITE_IOERR_READ          (SQLITE_IOERR | (1<<8))
  #define SQLITE_IOERR_SHORT_READ    (SQLITE_IOERR | (2<<8))
  #define SQLITE_IOERR_WRITE         (SQLITE_IOERR | (3<<8))
  #define SQLITE_IOERR_FSYNC         (SQLITE_IOERR | (4<<8))
  #define SQLITE_IOERR_DIR_FSYNC     (SQLITE_IOERR | (5<<8))
  #define SQLITE_IOERR_TRUNCATE      (SQLITE_IOERR | (6<<8))
  #define SQLITE_IOERR_FSTAT         (SQLITE_IOERR | (7<<8))
  #define SQLITE_IOERR_UNLOCK        (SQLITE_IOERR | (8<<8))
  #define SQLITE_IOERR_RDLOCK        (SQLITE_IOERR | (9<<8))
  #define SQLITE_IOERR_DELETE        (SQLITE_IOERR | (10<<8))
  #define SQLITE_IOERR_BLOCKED       (SQLITE_IOERR | (11<<8))

User comment: I like the idea of more specific result codes, but I think that the specific set of extended error codes listed here is barking up the wrong tree. If the base code is SQLITE_IOERR, telling you what I/O operation failed is not the most useful piece of additional detail you could provide. It'd be more useful to provide the operating system's error code for the failing operation: the errno value on Unix, for instance. (The library user cannot safely assume that the value of errno when an sqlite3_* call returns SQLITE_IOERR, is the value set by the OS-level operation that actually failed. It needs to be saved immediately after the failing operation. Also, errno isn't the right thing to look at on Windows, if I remember correctly.)

I don't know if errno values can safely be crammed into the high 24 bits of the result code. It might be better to return them from a separate callback, say, sqlite3_os_errcode(), guaranteed to present the OS-level error code from the last operation that returned SQLITE_IOERR. If that were done, I would also recommend that sqlite3_errmsg() automatically augment the error string with the equivalent of strerror(saved_errno) when appropriate. That would allow library users to present better diagnostics to users without any code changes.

-- Zack Weinberg <zackw@panix.com> 9 Jan 2007

Comment about above: Windows error codes are 32 bits and use all of the bits and hence cannot be squeezed into the 24 bit space available.

-- Erik Pearson <adapterik@gmail.com> 7 Apr 2007

Another critical error reporting need is sql errors. It is really hard to believe that all sql errors are wrapped up into "SQL error or missing database"!!!

sqlite - Extended Result Codes