• 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.
  
  

void myquery(SQLHANDLE hstmt,char *query)
{
  SQLRETUN 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);
  }
  ...
}

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”);
       }
    }
  }
}

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);
}

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