www.mimer.com/developer

Errors in BSQL

---

Error messages are shown when you attempt to execute an erroneous SQL statement. There are two types of errors: semantic errors and syntax errors.

Semantic Errors

Semantic errors arise when SQL statements are formulated with correct syntax, but do not reflect the user's intentions.

For example, suppose that a user wishes to select the string constant 'Hotel:' and the actual hotel name from the table HOTEL, but uses quotation marks instead of apostrophes around the string constant:

 SELECT  "Hotel:",NAME
 FROM    HOTEL;
 

Quotation marks are used to delimit identifiers containing special characters, so that the statement is interpreted as a request to select two columns, called 'Hotel:' and NAME, from the table. The first 'column' does not exist.

This example will in fact lead to an execution error, and is easily detected. Other semantic mistakes can be more difficult to find, when the statement is executed but gives the 'wrong' answer.

An example is the incorrect use of NULL in a search condition:

 SELECT  RESERVATION FROM BOOK_GUEST
 WHERE   CHECKOUT = CAST(NULL as DATE);
 

This will always give an empty result set, since NULL is not equal to anything.

The correct formulation would read WHERE CHECKOUT IS NULL.

Always check that the result of an SQL query looks reasonable, in particular if the query is complicated.

Syntax Errors

Syntax errors are constructions which break the rules for formulating SQL statements.

For example:

• spelling errors in keywords

  SLEECT (for SELECT)

• incorrect or missing delimiters

  DELETEFROM (for DELETE FROM)

  SELECT column1 column2 (for SELECT column1,column2)

• incorrect clause ordering

  UPDATE table WHERE condition SET values

  (for UPDATE table SET values WHERE condition)

Syntactically incorrect statements are not accepted and an appropriate error message is displayed.

The error must be corrected before the statement can be executed.

For syntax errors, BSQL analyzes the statement and makes an intelligent guess as to where the error lies. This guess is based upon the most likely syntax or appearance of the statement in question. The system then points out the error and lists an error message based on this analysis. The appearance of this pointer on your screen is machine dependent. In the examples shown in this chapter, the pointer appears as '^'. The messages are self-explanatory.

The statement analysis is however not completely foolproof and misleading error messages may arise. If the message seems to be inaccurate, check the statement construction against the syntax diagram in the Mimer SQL Reference Manual.

Error Examples

Some examples of errors and resulting error messages are listed below.

Incorrect statement:

 SELECT  AVG(NAME) FROM HOTEL;

Error message:

 SELECT  AVG(NAME) FROM HOTEL;
             ^
 Invalid operand type, expected type is NUMERIC or INTERVAL

Incorrect statement:

 SELECT  NAME FROM HOTEL 
 WHERE   CITY ON ('STOCKHOLM','UPPSALA');

Error message:

 SELECT  NAME FROM HOTEL 
 WHERE   CITY ON ('STOCKHOLM','UPPSALA');
              ^
 Syntax error, 'ON' assumed to mean 'IN'

Incorrect statement:

In the following example, the error analysis is misleading:

 SELECT  NAME FROM HOTEL
 WJERE   HOTELCODE = 'LAP';

Error message:

 SELECT  NAME FROM HOTEL
 WJERE   HOTELCODE = 'LAP';
         ^
 Syntax error, END-OF-QUERY assumed missing
 

The misspelled word WJERE is not recognized as an attempt to write WHERE, so that the second line is not interpreted as a selection condition.

Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se