Database Warning Messages - Functional Specification
On occasion, it is useful for the DataBase server to report a warning to the Client. A warning does not impede normal client/server operations, but may advise the client of a problem that needs investigation.
The SQL specification points out several instances where Warnings are to be issued.
DELETE FROM tab WHERE 1=0;
This delete statement affects no rows. SQL specifies that a "Completion Condition" be reported back to the SQL Client. Completion conditions are not an indication of any error - but does indicate that the user may have issued the wrong query.
All InterBase Server API functions return error information in a Status Vector.
typedef ULONG ISC_STATUS; ISC_STATUS status_vector;
When an error is returned, a status vector is formatted as follows:
gds_arg_gds isc_arith_except gds_arg_end …
With no error (the normal case!) a status vector appears as:
gds_arg_gds 0 gds_arg_end …
Application programs typically check for no errors by examining the 2nd element of the status vector. A zero entry indicates no error. GPRE itself generates code with this assumption.
Additionally, all API calls return 0 if no error, and return an error code identical to the 2nd element of the status vector if there is an error.
With InterBase warnings, a warning code is emitted after the last gds_arg_gds code clump in a status vector, and before the gds_arg_end.
In the case of an error with a warning, the status vector would appear as:
gds_arg_gds isc_arith_except gds_arg_warning isc_no_rows_affected gds_arg_end …
With no error, and one warning, a status vector appears as:
gds_arg_gds 0 gds_arg_warning isc_no_rows_affected gds_arg_end …
If multiple warnings occur as part of a statement, the warnings are sequential (identical to how errors are stacked inside a status vector):
gds_arg_gds 0 gds_arg_warning isc_no_rows_affected gds_arg_warning isc_random isc_arg_string "Danger Will Robinson" gds_arg_end …
Warnings appear as status codes identical to error codes in ibase.h. The difference between a warning an error is indicated by the preceding gds_arg_gds (for an error) or gds_arg_warning (for a warning). All status vectors start with an error code, which must indicate 0 if there is no error to report.
Warning messages can thus have parameterized inserts, as do error messages.
API calls that result in a warning, but no errors, return 0. The return code from an API call indicates if an ERROR occurred only.
GPRE: None. When an SQLCODE is constructed it ignores warning messages.
SET WARNINGS [ON/OFF];
The default for SET WARNINGS is ON.
If SET WARNINGS is ON, then ISQL will display any warning messages returned in a status vector, even if no error occurred during the statement.
If SET WARNINGS is OFF, then ISQL will only display warnings only if an error also occurred in the statement.
Usage of Warnings:
Warnings are issued for the following conditions:
SQL Statement with no effect (DELETE or UPDATE of an empty set of rows)
Usage of SQL expressions that results in different data manipulation semantics in InterBase V5 vs InterBase V6. In InteBase V5 the statement
returns 1.333333333 (a floating point number).
In InterBase V6, with SQL Dialect 2 or 3, the ISO SQL specification semantics for division are enforced. 4 / 3 is performed using Integer division, resulting in a return value of 1 (an INTEGER). Similar constructs occur for AVG() & TIMESTAMP arithmetic.
Requirements and Constraints
General InterBase requirements apply.
In a future release of InterBase, the warning mechanism can be used in additional constructs.
For instance, in the Procedure / Trigger language, we can trap for warnings with an "ON WARNING W" construct, similar to the existing traps for SQLCODE or GDS_CODE.
In Embedded SQL, constructs such as SQLWARNING are defined by the SQL specification. This technology provides the basis for a future implementation of SQLWARNING or SQLERROR.
ANSI SQL 3 Draft Specification, Oct 1997. (Note: DATE & TIME support was finalized in SQL 92, the SQL 3 specification introduces some clarifications). (See /usr/gds/SQL/sqlfound.txt)
ANSI SQL 3: Part 3 Call-Level Interface (SQL/CLI)