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 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 double quotation marks instead of single quotation marks around the string constant:SELECT "Hotel:",NAME FROM HOTEL;
Double quotation marks are used to delimit identifiers containing special characters, so that the statement is interpreted as a request to select two columns, called
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
NULLin a search condition:SELECT RESERVATION FROM BOOK_GUEST WHERE CHECKOUT = CAST(NULL as DATE);
This will always give an empty result set, since
NULLis 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 are constructions which break the rules for formulating SQL statements.
- spelling errors in keywords
- incorrect or missing delimiters
- incorrect clause ordering
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.
Some examples of errors and resulting error messages are listed below.
Incorrect statement:SELECT AVG(country) FROM countries;
Error message:SELECT AVG(country) FROM countries; ^ Invalid operand type, expected type is NUMERIC or INTERVAL
Incorrect statement:SELECT country FROM countries WHERE currency_code ON ('USD','GBP','SEK');
Error message:SELECT country FROM countries WHERE currency_code ON ('USD','GBP','SEK'); ^ Syntax error, 'ON' assumed to mean 'IN'
In the following example, the error analysis is misleading:SELECT country FROM countries WJERE currency_code = 'USD';
Error message:SELECT country FROM countries WJERE currency_code = 'USD'; ^ Syntax error, END-OF-QUERY assumed missing
The misspelled word
WJEREis not recognized as an attempt to write
WHERE, so that the second line is not interpreted as a selection condition.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40