Introduction

Portable error handling has always been a headache when working with different database systems. All database vendors use their own native error code systems, which are often accurate and precise, but these code systems fail when systems must be portable. The international SQL standards committee has come up with an alternative: SQLSTATEs.

Although SQLSTATEs have been around since the introduction of SQL-92, they are rarely used. One possible reason for this could be that SQLSTATEs are a bit trickier to work with than native error codes. Native error codes are usually 32-bit integers which are easy to compare, while SQLSTATEs are five-character strings and perhaps not so easy to compare.

But, by using portable SQLSTATEs for error management, your applications remain portable across platforms and database environments.

Description

SQLSTATEs are five character strings which are designed to represent error conditions in a portable fashion. SQLSTATEs have a few interesting properties:

  • They are always five characters in length. They are most easily compared to an SQL CHARACTER(5) data type.
  • Only digits and simple Latin upper-case letters are allowed. No spaces, punctuation or any other separation character are allowed.
  • Each SQLSTATE is comprised of a 2-character class and a 3-character subclass. Error conditions that are related belong to the same class.
  • The SQL standard specifies that SQLSTATE classes and subclasses that begin with digits 0, 1, 2, 3 or 4 and those that begin with the letters A through H are defined in the standard while the others (5 through 9 or I through Z) are for implementation-defined SQLSTATEs.
  • The 00, 01 and 02 classes are defined in the SQL standard as a little more important than the classes. 00 means successful completion, 01 means warning, and 02 means no data.
  • Implementation-defined classes may be combined with standardized classes. For instance, a database vendor might choose to return the warning condition ‘Out of jolt-cola’. Some readers probably want to classify out-of-jolt-cola as an error condition. They may then choose the subclass value I00 and combine this with the class 01, predefined for warnings, to form 01I00. An application not aware of the vendor-specific ‘out of jolt-cola’ condition will still be able to recognize the warning since it belongs to the 01-class.
  • Since SQLSTATEs that are logically related belong to the same class, an application might for simplicity only check the class origin to determine what went wrong. For instance, an application might check for the class origin 22 to determine if a data error occurred, or class origin 40 for a transaction error.

Example

The examples below show some simple methods of how to use SQLSTATEs. These examples are not supposed to be complete or to have a meaningful application, but merely give an idea of how to use SQLSTATEs in different environments.

C and ODBC

void myquery(SQLHANDLE hstmt,char *query)
{
  SQLRETURN rc;
  rc = SQLExecDirect(hstmt,query,SQL_NTS);
  if (rc!=SQL_SUCCESS) {
    SQLRETURN rc2;
    char SQLSTATE[5+1];
    int recnum=1;
    do {
      rc2 = SQLGetDiagField(SQL_HANDLE_STMT,hstmt,recnum,
      SQL_DIAG_SQLSTATE,SQLSTATE,5+1,NULL);
      if (SQLSTATE[0]==’2’ && SQLSTATE[1]==’2’) {
        printf(“Data error!\n”);
      }
      recnum++;
    } while (rc2==SQL_SUCCESS);
  }
...
}

Java and JDBC

class MyClass { 
  void myquery(Statement stmt,String query) { 
    try { 
      boolean results_q = stmt.execute(query); 
      ... 
    } catch (SQLException e) { 
      if (e.getSQLState().startsWith(“22”)) { 
        System.out.println(“Data error!\n”); 
      } 
    } 
  } 
}

C and Embedded SQL

exec sql BEGIN DECLARE SECTION;
static char SQLSTATE[6];
exec sql END DECLARE SECTION;

main()
{
  exec sql BEGIN DECLARE SECTION;
  varchar schema[129];
  varchar table [129];
  char type [21];
  exec sql END DECLARE SECTION;

  exec sql DECLARE MYCURSOR CURSOR FOR
           SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE
           FROM INFORMATION_SCHEMA.TABLES;

  exec sql CONNECT TO '' USER 'SYSADM' USING 'SYSADM';
  
  exec sql OPEN MYCURSOR;
  while (1) {
    exec sql FETCH MYCURSOR INTO :schema,:table,:type;
    if (strcmpn(SQLSTATE,"22",2)==0) {
      printf("Data error!\n");
     continue;
    }
    if (strcmp(SQLSTATE, "02000") == 0) {
      break; /* No more rows */
    }
    printf("%s %s %s\n", schema, table, type);
  }

  exec sql CLOSE MYCURSOR;
  exec sql COMMIT;
  exec sql DISCONNECT ALL;
  exit(0);
}

Throwing User-defined SQLSTATEs in Procedures

create procedure MYPROC(in A integer,in B integer) 
begin 
  -- Input parameter check, and throw an SQLSTATE if wrong. 
  if A=B then 
    signal sqlstate ‘22I00’; 
  end if; 
  -- Do something useful. 
  ... 
end

Links

SQLSTATEs are defined and described in the SQL standard, see various excellent books covering SQL in our page for External Resources.

For more detailed information see Mimer SQL Documentation Set:

Graphic Element - Cube