How to Run a Script with phpMyAdmin in SQL for HTML5and CSS3 Programming
How to Implement a Database in MySQL for HTML5 and CSS3 Programming
How to Use SQL with Microsoft Access

How to Interpret the SQL Error Information Returned by SQLSTATE

When you use SQLSTATE to identify SQL errors, it can sometimes be tough to decide what to do with that information. CONNECTION_NAME and ENVIRONMENT_NAME identify the connection and environment to which you are connected at the time the SQL statement is executed.

If the report deals with a table operation, CATALOG_NAME, SCHEMA_NAME, and TABLE_NAME identify the table. COLUMN_NAME identifies the column within the table that caused the report to be made. If the situation involves a cursor, CURSOR_NAME gives its name.

Sometimes a DBMS produces a string of natural language text to explain a condition. The MESSAGE_TEXT item is for this kind of information. The contents of this item depend on the implementation; the SQL standard doesn’t explicitly define them. If you do have something in MESSAGE_TEXT, its length in characters is recorded in MESSAGE_LENGTH, and its length in octets is recorded in MESSAGE_OCTET_LENGTH.

If the message is in normal ASCII characters, MESSAGE_LENGTH equals MESSAGE_OCTET_LENGTH. If, on the other hand, the message is in kanji or some other language whose characters require more than an octet to express, MESSAGE_LENGTH differs from MESSAGE_OCTET_LENGTH.

To retrieve diagnostic information from a diagnostics area header, use the following:

GET DIAGNOSTICS status1 = item1 [, status2 = item2]... ;

statusn is a host variable or parameter; itemn can be any of the keywords NUMBER, MORE, COMMAND_FUNCTION, DYNAMIC_FUNCTION, or ROW_COUNT.

To retrieve diagnostic information from a diagnostics detail area, use the following syntax:

GET DIAGNOSTICS EXCEPTION condition-number
 status1 = item1 [, status2 = item2]... ;

Again statusn is a host variable or parameter, and itemn is any of the 28 keywords for the detail items listed in Table 21-2. The condition number is (surprise!) the detail area’s CONDITION_NUMBER item.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
How to Calculate Date Values in SQL Data for HTML5and CSS3 Programming
How to Maintain Referential Integrity in a Multitable SQL Database
How to Use EXISTS, UNIQUE, DISTINCT, and OVERLAPS in SQL Statements
How to Grant SQL Privileges across Levels
How to Take Away SQL Privileges
Advertisement

Inside Dummies.com