Learn SQLSTATE, sqlstate08001

Learn SQLSTATE, sqlstate08001

1. What is SQLSTATE?

Shell> SELECT * FROM no_such_table;

ERROR 1146 (42S02): Table 'test. no_such_table 'doesn' t exist

Display after an error occurs in the preceding SQL statement. 1146 is the error code defined by MySQL, and 42S02 is the error code defined by ansi SQL and ODBC. "Table 'test. no_such_table 'doesn' t exist" is the error cause returned by MySQL.

Here, 42S02 is the SQLSTATE to be discussed in this article.

 

2. Why SQLSTATE?

42S02 is an error code defined by ansi SQL and ODBC. It can be understood as an error code standard. If there is no SQLSTATE, what will the world look like? You have developed a database driver to be compatible with MySQL, Oracle, and SQLServer. For lock conflicts, MySQL Returns Error Code 2011, Oracle returns 9912, and SQLServer returns 3231 (the above three data are fabricated). If you want to check the lock conflicts, immediately execute do_something (), then you need to write the code like this:

If (2011 = conn. errno | 9912 = conn. errno | 3231 = conn. errno ){

Do_something ();

}

If you want to support Postgre, you need to add the Postgre error code. This is a sad story.

It can be seen that the custom error codes of databases are unreliable and they are independent of each other. You may wonder why these database vendors cannot coordinate and unify the error codes? The ideal is full, and the reality is very skinny. In a specific database, four or five different internal error codes may be defined to indicate lock conflicts. An error code cannot meet the requirements of internal logic. Therefore, the perfect solution is:

* Internal error codes are used in databases. If you want to use them, you can use them as needed. When you need to output the error codes to external systems, you must first convert them to SQLSTATE.

* The database driver only looks at SQLSTATE and ignores the custom error codes of the database.

 

3. SQLSTATE data format description

SQLSTATE contains five letters, the first two indicate the error category, and the last three represent the subclass, both of which are 0 ~ 9, ~ Z (uppercase. 00000 indicates no error.

Error categories defined by the first two letters:

00 = no error

01 = WARNING

02 = cursor NOT FOUND

> 02 indicates an exception, MySQL exception, For details, see the http://dev.mysql.com/doc/refman/5.6/en/error-messages-server.html here defines the ing between more than 800 error codes in MySQL and SQLSTATE

 

Not every internal error code can be clearly mapped to a meaningful SQLSTATE. For such internal error codes, all are mapped to the SQLSTATE of HY000, meaning: I don't know which SQLSTATE the Error Code corresponds. Example: Error:1004SQLSTATE:HY000(ER_CANT_CREATE_FILE)

About SQLSTATE format, there are a lot of exquisite, detailed reference this document, relatively clear: https://mariadb.com/kb/en/sql-99/sqlstate-codes/

4. How to Implement SQLSTATE in the database

You can create a Map to Map error codes to SQLSTATE. If the error code is designed from 0 ~ N, or 0 ~ -N, you can directly use arrays to implement this ing. The error code is the subscript of the array. A more common method is to use arrays, but the search method is binary search, which is also very convenient.

For implementation in MySQL, see share/errmsg.txt and include/SQL _state.h.

 

5. How to Implement SQLSTATE in OceanBase

See lib/ob_errno.cpp

We can see that OB is more difficult than MySQL: It maps the internal error codes of OceanBase into the internal error codes of MySQL as much as possible. When can someone write a database map internal error codes to OceanBase?