Appendix C – Non-portable SQL Features¶

Overall Method of SQL Support¶

The exact SQL syntax and method for accessing SQL-data supported by a DBMS is non-standard for four reasons:

1. The SQL Standard allows implementors to choose to support either one of the following options for SQL conformance: Core SQL or Enhanced SQL.
2. The SQL Standard allows implementors to choose to support either one or more of the following options for binding style: Module Language, Embedded Syntax or Direct SQL Invocation.
3. The SQL Standard contains numerous non-standardized areas where implementors are required to decide upon appropriate responses on their own: the “implementor-defined” and “implementor-dependent” areas contained in the Standard.
4. The SQL Standard allows implementors to provide options for processing database operations that the Standard does not address (e.g.: a CREATE INDEX statement), as well as allowing implementors to provide options for processing Standard-defined SQL in a non-conforming manner. To quote the Standard: “An [SQL-]implementation remains conforming even if it provides user options to process nonconforming SQL language or to process conforming SQL language in a nonconforming manner.”

Connection and Session Results¶

The result of executing CONNECT TO is non-standard because the SQL Standard allows implementors to choose to support either one of the following options:

1. a DBMS may allow the establishment of only one SQL-session at a time, or
2. a DBMS may support multiple concurrent SQL-sessions.

A DBMS’s default SQL-session and initial default SQL-Connection are non- standard because the SQL Standard requires implementors to choose their own initial default <Connection name>.

The effect of CONNECT TO DEFAULT; is non-standard because the SQL Standard requires implementors to define what the default SQL-Connection and the default SQL-server are.

A <Connection name> must be a <regular identifier> or a <delimited identifier> that is no more than 128 octets in length, but the value of a valid <Connection name> is non-standard because the SQL Standard requires implementors to define what a valid <Connection name> may be and what Character set <Connection name>s belong to.

A DBMS’s initial default <AuthorizationID> is non-standard because the SQL Standard requires implementors to choose their own initial default <AuthorizationID>.

The effect of omitting the optional USER clause from a CONNECT statement is non-standard because the SQL Standard requires implementors to define their own initial default SQL-session <AuthorizationID>.

Whether the <user name> in a CONNECT statement must be identical to the Module <AuthorizationID> is non-standard because the SQL Standard requires implementors to define whether the two must match.

A DBMS’s initial default Catalog is non-standard because the SQL Standard requires implementors to choose their own initial default <Catalog name>.

A DBMS’s initial default Schema is non-standard because the SQL Standard requires implementors to choose their own initial default <Schema name>.

A DBMS’s initial default Character set is non-standard for two reasons:

1. The SQL Standard requires implementors to choose their own initial default <Character set name>.
2. The SQL Standard requires implementors to define the character repertoire for their initial default Character set.

A DBMS’s initial default time zone offset is non-standard because the SQL Standard requires implementors to define their own initial default time zone offset.

The result of executing SET SESSION AUTHORIZATION is non-standard for two reasons:

1. Although it is mandatory to support the use of SET SESSION AUTHORIZATION at the start of a SQL-session, the SQL Standard allows implementors to decide whether to allow the SQL-session default <AuthorizationID> to be changed at any other time.
2. For DBMSs that allow the use of SET SESSION AUTHORIZATION at other times, the SQL Standard allows implementors to define when exactly when the SQL-session default <AuthorizationID> may also be changed.

SQL does not include any CREATE CLUSTER, OPEN CLUSTER, ADD TO CLUSTER or DROP CLUSTER statements. The method you’ll use to access a Cluster with your DBMS is thus non-standard because the SQL Standard requires implementors to define what the physical aspects of a Cluster are, whether any Catalog can be part of more than one Cluster at a time, how a Cluster comes into being, how it may be accessed and how it may be destroyed.

SQL does not include any CREATE CATALOG, OPEN CATALOG or DROP CATALOG statements. The method you’ll use to access a Catalog with your DBMS is thus non-standard because the SQL Standard requires implementors to define how a Catalog comes into being, how it may be accessed and how it may be destroyed.

The total number of Views in INFORMATION_SCHEMA, and their exact definition, is non-standard because the SQL Standard allows implementors to add additional Views, as well as to add additional Columns to the Standard-defined Views, to describe additional, implementation-defined features.

Parsing/Display¶

A newline character, or end-of-line marker, is non-standard because the SQL Standard requires implementors to define which white space characters their parsers will recognize as newline characters.

The logical representation of the null value is non-standard because the SQL Standard requires implementors to define the character used to display the null value.

Either a NULL is greater than all non-null values or a NULL is less than all non-null values – it’s non-standard because the SQL Standard requires implementors to define whether NULLs sort high or low.

Names¶

An <SQL-server name> must be unique (for all Clusters) within an SQL- environment, but is non-standard for two reasons:

1. The SQL Standard requires implementors to define what constitutes a valid <SQL-server name>.
2. The SQL Standard requires implementors to define the Character set that the characters of a <SQL-server name> belong to.

An <AuthorizationID> is non-standard for four reasons:

1. The SQL Standard requires implementors to define what constitutes a valid <AuthorizationID>.
2. The SQL Standard requires implementors to define their own method for mapping <AuthorizationID>s to users.
3. The SQL Standard requires implementors to define their own method for creating an <AuthorizationID>. SQL does not include a CREATE AUTHORIZATIONID statement.
4. The SQL Standard requires implementors to define their own method for dropping an <AuthorizationID>. SQL does not include a DROP AUTHORIZATIONID statement.

<Connection name>s are non-standard for two reasons:

1. The SQL Standard requires implementors to define what constitutes a valid <Connection name>.
2. The SQL Standard requires implementors to define the Character set that the characters of a <Connection name> belong to.

A <Catalog name> is a <regular identifier> or <delimited identifier> that is unique (for all Catalogs) within the Cluster it belongs to, but is non- standard because the SQL Standard requires implementors to define all valid <Catalog name>s.

Defining <literal>s¶

A national <character string literal> belongs to a Character set which is the same Character set used for NCHAR and NCHAR VARYING <data type>s but is non-standard for two reasons:

1. The SQL Standard requires implementors to define the Character set that the characters of a national <character string literal> belong to.
2. The SQL Standard requires implementors to define the character repertoire for their national <character string literal> Character set.

The maximum size of a <time value>’s fractional seconds may not be less than 6 digits but is non-standard because the SQL Standard requires implementors to define the maximum fractional seconds precision.

The maximum length of a <time literal> may not be less than 15 characters but is non-standard because the SQL Standard requires implementors to define the maximum size of a <time value>’s fractional seconds precision.

The allowable range for a <time value> must include, at a minimum, all times from: TIME '00:00:00.0' to: TIME '23:59:61.999999' but the exact range of valid values is non-standard because the SQL Standard requires implementors to define the maximum size of a <time value>’s fractional seconds precision.

The maximum length of a <timestamp literal> may not be less than 26 characters but is non-standard because the SQL Standard requires implementors to define the maximum size of a <time value>’s fractional seconds precision.

The allowable range for a <timestamp value> must include, at a minimum, all timestamps from: TIMESTAMP '0001-01-01 00:00:00.0' to: TIMESTAMP '9999-12-31 23:59:61.999999' but the exact range of valid values is non-standard because the SQL Standard requires implementors to define the maximum size of a <time value>’s fractional seconds precision.

The maximum “start datetime” precision for an <interval qualifier> may not be less than 2 digits but is non-standard because the SQL Standard requires implementors to uefine the maximum leading precision.

The maximum fractional seconds precision for an <interval qualifier>’s “start datetime” value or “end datetime” value of SECOND may not be less than 6 digits but is non-standard because the SQL Standard requires implemented Privileges held on the Module by any other <AuthorizationID> are also revoked.

All SQL routines, Triggers, Views and Constraints that depend on the Module are dropped with a CASCADE drop behaviour.

BEGIN … END: Compound Statement¶

Advance warning: BEGIN ... END has several optional clauses. We are going to start with the simplest form, and examine the options in following sections.

In its simplest form, BEGIN ... END in SQL serves the same purpose as “begin…end” in Pascal or “{…}” in C. BEGIN ... END encloses a sequence of statements which are part of the same syntactical unit: a compound statement. The simplest required syntax is:

BEGIN
   [ <SQL statement>; ... ]
END

Here’s a simple example:

BEGIN
    INSERT INTO Table_1 VALUES (5);
    INSERT INTO Table_2 VALUES (6);
END

BEGIN [ [ NOT ] ATOMIC ]    /* whether compound statement is atomic */
  [ <SQL statement>; ... ]
END

BEGIN ATOMIC
   INSERT INTO Table_1 VALUES (5);
   INSERT INTO Table_2 VALUES (6);
END

 BEGIN [ [ NOT ] ATOMIC ]
   [ <variable declaration>; ... ]     /* variable-declaration list */
   [ <SQL statement>; ... ]
 END

<variable declaration> ::=
DECLARE <SQL variable name> <data type> [ DEFAULT default value ]

BEGIN ATOMIC
  DECLARE v1 CHAR(5);                          /* variable declaration */
  DECLARE v2,v3,v4 SMALLINT;                   /* variable declaration */
  DECLARE v5 DATE DEFAULT DATE '1993-01-01';   /* variable declaration */
  SELECT * INTO v1,v2,v3,v4 FROM Table_1;      /* statement */
  INSERT INTO Table_2 VALUES (v1,v2,v3,v4,v5); /* statement */
END

SET
   <target>     /* where the value goes to; usually a variable */
   =
   <source>     /* where the value comes from; an expression */

SET v1 = 5
SET v1 = (v2+7)/5
SET v1 = NULL
SET v1 = column_1

BEGIN [ [ NOT ] ATOMIC ]
  [ <variable declaration>; ... ]
  [ DECLARE CURSOR statement; ... ]      /* Cursor-declaration list */
  [ <SQL statement>; ... ]
END

BEGIN
  DECLARE v1 SMALLINT;             /* variable-declaration */
  DECLARE cc CURSOR FOR
     SELECT column_1 FROM Table_1; /* Cursor-declaration */
  OPEN cc;                         /* statement */
  FETCH cc INTO v1;                /* statement */
  CLOSE cc;                        /* statement */
  INSERT INTO Table_2 VALUES (v1); /* statement */
END

INSERT INTO Table_2 SELECT column1 FROM Table_1;

 BEGIN [ [ NOT ] ATOMIC ]
   [ <variable | condition declaration>; ... ] /* variable-or-condition
 declaration list */
   [ DECLARE CURSOR statement; ... ]
   [ <SQL statement>; ... ]
 END
<condition declaration> ::=
   DECLARE <condition name> CONDITION [ FOR <sqlstate value> ]

BEGIN ATOMIC
  DECLARE v1 SMALLINT;                          /* variable-declaration */
  DECLARE warning_revoke CONDITION FOR '01006'; /* condition declaration */
  DECLARE divide_by_zero CONDITION FOR '22012'; /* condition declaration */
  DECLARE syntax_error CONDITION FOR '42000';   /* condition declaration */
  DECLARE cc CURSOR FOR
     SELECT column_1 FROM Table_1;              /* Cursor-declaration */
  OPEN cc;                                      /* statement */
  FETCH cc INTO v1;                             /* statement */
  CLOSE cc;                                     /* statement */
  INSERT INTO Table_2 VALUES (v1);              /* statement */
  INSERT INTO Table_1 VALUES (0);               /* statement */
  INSERT INTO Table_2 VALUES (1);               /* statement */
END

BEGIN [ [ NOT ] ATOMIC ]
  [ <variable | condition declaration>; ... ]
  [ DECLARE CURSOR statement; ... ]
  [ <handler declaration>; ...]               /* handler-declaration list */
  [ <SQL statement>; ... ]
END

<handler declaration> ::=
DECLARE <handler type> HANDLER FOR <condition value list> <handler action>

<handler type> ::= {CONTINUE | EXIT | UNDO }

<handler action> ::= <SQL statement>

<condition value list> ::= <condition value> [ {,<condition value>}... ]

<condition value> ::=
<sqlstate value>| <condition name>| SQLEXCEPTION | SQLWARNING | NOT FOUND

BEGIN
  DECLARE constraint_error CONDITION FOR '23000';/* condition declaration */
  DECLARE v1 CHAR(5) DEFAULT 'Okay!';            /* variable declaration */
  DECLARE CONTINUE HANDLER FOR '22003'           /* handler declaration */
     SET v1 = 'Ovflw';
  DECLARE CONTINUE HANDLER FOR constraint_error  /* handler declaration */
     SET v1 = 'c-err';
  DECLARE CONTINUE HANDLER FOR SQLWARNING        /* handler declaration */
     SET v1 = '?????';
  INSERT INTO Table_1 VALUES (99999);            /* statement */
  INSERT INTO Table_2 VALUES (v1);               /* statement */
END

INSERT INTO Table_1 VALUES (99999);

                                EMBEDDED SQL       CLI   PSM
method of declaration           EXEC SQL WHENEVER  none  handler-declaration
what happens                    GOTO               N/A   any SQL statement
handles SQLNOTFOUND?            yes                N/A   yes
handles SQLERROR?               yes                N/A   yes
handles SQLWARNING?             yes                N/A   yes
handles specific status codes?  no                 N/A   yes

[ <beginning_label>: ]
BEGIN [ [ NOT ] ATOMIC ]
  [ <variable | condition declaration>; ... ]
  [ DECLARE CURSOR statement; ... ]
  [ <handler declaration>; ...]
  [ <SQL statement>; ... ]
END [ <end_label> ]

<beginning_label> ::= <identifier>

<end_label> ::= <identifier>

full_blown_example:                      /* beginning_label */
BEGIN ATOMIC                             /* compound statement is atomic */
  DECLARE v1 INTEGER DEFAULT 0;          /* variable declaration */
  DECLARE c1 CONDITION FOR '01000';      /* condition declaration */
  DECLARE CONTINUE HANDLER FOR SQLERROR  /* handler declaration */
    SET v1 = 1;                          /* assignment statement */
  INSERT INTO Table_1 VALUES (0);        /* statement */
  INSERT INTO Table_2 VALUES (v1);       /* statement */
END full_blown_example                   /* end_label */

SIGNAL Statement¶

The SIGNAL statement is used to clear the diagnostics area. The required syntax for the SIGNAL statement is:

SIGNAL <condition name or sqlstate value>
   SET <signal information item list>

   <signal information item list> ::=
   <signal information item> [ {,<signal information item>}... ]

      <signal information item> ::=
      <condition information item name> = <simple value specification>

The SIGNAL statement clears every record in the diagnostics area. The end result is a record containing the passed condition name or sqlstate value. If you include the optional SET clause, your DBMS effectively executes:

RESIGNAL <signal information item list>;

Note: You’ll find the list of <condition information item name>s in our chapter on embedded SQL – see the GET DIAGNOSTICS statement.

RESIGNAL Statement¶

The RESIGNAL statement is used to pass conditions on to another handler. The required syntax for the RESIGNAL statement is:

RESIGNAL [ <condition name or sqlstate value> ]
   SET <signal information item list>

The RESIGNAL statement passes the given exception “up the line” to the next appropriate handler (since compound statements may be embedded in compound statements, this next appropriate handler will usually be in some outside context). The current diagnostics area remains unchanged, but – if the optional [<condition name or sqlstate value>] clause is specified – there will be one more diagnostics record, containing this new value. If you include the optional SET clause, the <condition information item name> field in the first condition area in the diagnostics area is changed to the value indicated.

CASE -- Switch depending on condition.
IF -- If (condition) do.
ITERATE -- Restart loop.
LOOP -- Do statement(s) repeatedly.
LEAVE -- Break out of a loop or block.
WHILE -- Repeat statement(s) as long as condition is true.
REPEAT -- Repeat statement(s) until condition is true.
FOR -- Cursor-based FETCH loop.

CASE Statement¶

The CASE statement is useful for switching between possible execution paths. There are two forms – one contains search conditions, the other contains value expressions. The required syntax for the CASE statement is:

searched CASE statement ::=
CASE
  WHEN <search condition> THEN <statement>(s)
  [ WHEN <search condition> THEN <statement>(s) ... ]
  [ ELSE <statement>(s) ]
END CASE

simple CASE statement ::=
CASE <case value>
   WHEN <when value> THEN <statement>(s)
   [ WHEN <when value> THEN <statement>(s) ... ]
   [ ELSE <statement>(s) ]
END CASE

A “simple CASE statement” is merely a shorthand, and may be replaced by a “searched CASE statement” which has the form: “CASE WHEN <when value> = <case value> …”. Thus, the following examples, showing a searched CASE statement on the left and a simple CASE statement on the right, are exactly equivalent:

CASE                                CASE parameter_value
  WHEN parameter_value = 15           WHEN 15
   THEN INSERT INTO t VALUES (15);     THEN INSERT INTO t VALUES (15);
  WHEN parameter_value = 17           WHEN 17
   THEN INSERT INTO t VALUES (17);     THEN INSERT INTO t VALUES (17);
  ELSE INSERT INTO t VALUES (0);      ELSE INSERT INTO t VALUES (0);
END CASE                            END CASE

When executing a CASE statement, the DBMS goes through the WHEN clauses from top to bottom, looking for a TRUE condition. If it finds one, it executes the statement(s) after THEN, and the CASE terminates. If it finds none, it executes the statements(s) after ELSE – or, if there is no ELSE, returns this SQLSTATE error: 20000 "case not found for case statement". For the above example, then, if the value of parameter_value is 5, then the DBMS will execute this SQL statement:

INSERT INTO t VALUES (0);

IF Statement¶

The IF statement is useful for simple “if (x) then (do this)” situations. The required syntax for the IF statement is:

IF <search condition> THEN <SQL statement>(s)
   ELSEIF <search condition> THEN <SQL statement>(s)
   ELSE <SQL statement>(s)
END IF

Here’s an example:

IF
 5=5 THEN UPDATE Table_1 SET column_1 = column_1 + 1;
END IF

In this example, the search condition is TRUE, so the UPDATE statement will be executed. If the search condition had been FALSE or UNKNOWN, then the UPDATE statement would not have been executed.

LOOP Statement¶

The LOOP statement is useful for repeated execution of SQL statements. The required syntax for the LOOP statement is:

[ <beginning_label>: ]
LOOP
   <SQL statement>(s)
END LOOP [ <end_label> ]

The SQL statements between LOOP and END LOOP are repeated until the loop finishes. The <beginning_label> and the <end_label> must be equivalent, if you use them both. Here’s an example:

LOOP
   SET x = x + 1;
END LOOP

This example shows an infinite loop. The usual way to exit from a loop is with the LEAVE statement.

LEAVE Statement¶

The LEAVE statement is useful for exiting a block or for exiting a loop. The required syntax for the LEAVE statement is:

LEAVE <statement_label>

Here’s an example:

beginning_label:
LOOP
  SET x = x + 1;
  IF x > 1000 THEN LEAVE beginning_label; END IF;
END LOOP beginning_label

In this example, the loop will be exited once the value of x passes 1000.

WHILE Statement¶

The WHILE statement is useful for repeated execution of SQL statements, with a built-in equivalent to the LEAVE statement. The required syntax for the WHILE statement is:

[ <beginning_label>: ]
WHILE <search condition> DO
   <SQL statement>(s)
END WHILE [ <end_label> ]

As long as the <search condition> is TRUE, the SQL statements between WHILE and END WHILE are repeatedly executed. The <beginning_label> and the <end_label> must be equivalent, if you use them both. Here’s an example:

WHILE x <= 1000 DO
   SET x = x + 1;
END WHILE

This example will loop, incrementing x, until “x <= 1000” is either FALSE or UNKNOWN. If the <search condition> is FALSE or UNKNOWN when the loop begins, then nothing happens.

REPEAT Statement¶

The REPEAT statement is much like the WHILE statement, except that the condition is tested after the execution of the SQL statement(s). The required syntax for the REPEAT statement is:

[ <beginning_label>: ]
REPEAT
   <SQL statement>(s) UNTIL <search condition>
END REPEAT [ <end_label> ]

As long as the <search condition> is FALSE or UNKNOWN, the SQL statements between REPEAT and END REPEAT are repeatedly executed. The <beginning_label> and the <end_label> must be equivalent, if you use them both. Here’s an example:

REPEAT
   DELETE FROM Table_1 WHERE column_1 = x;
   SET x = x + 1;
   UNTIL x > 5
END REPEAT

In this example, the UPDATE statement will be repeated until x is greater than 5 – that is, the loop will repeat until after theIf a sensitive or asensitive holdable-Cursor is held open for a subsequent transaction, then whether any significant changes made to SQL-data (by this or any subsequent transaction in which the Cursor is held open) will be visible through that Cursor in the subsequent transaction is non-standard because the SQL Standard requires implementors to define how they will handle this situation.

Whether a DBMS is able to disallow significant changes that would not be visible through a currently open Cursor is non-standard because the SQL Standard requires implementors to define their actions in such situations.

The extent to which a DBMS may disallow independent changes that are not significant is non-standard because the SQL Standard requires implementors to define their actions in such situations.

The status of any open Cursors in any SQL-client Module associated with the current transaction that were opened by that transaction before the establishment of a savepoint to which a ROLLBACK is executed is non-standard because the SQL Standard requires implementors to define their actions in such situations.

Diagnostics¶

The actual length of variable-length character items in the diagnostics area has to be at least 128 octets but is otherwise non-standard because the SQL Standard requires implementors to define the actual length.

The character string value set for the diagnostic area’s CLASS_ORIGIN and SUBCLASS_ORIGIN fields may not be 'ISO 9075' but is otherwise non-standard because the SQL Standard requires implementors to define their own values for any non-standard errors.

The character string value set for the diagnostic area’s MESSAGE_TEXT field is non-standard because the SQL Standard requires implementors to define their own values for this field.

Any negative values set for the diagnostic area’s COMMAND_FUNCTION_CODE field indicate implementation-defined SQL-statements and are thus non-standard because the SQL Standard requires implementors to define their own values for this field if they support any non-standard SQL statements.

The method of flagging nonconforming SQL language or processing of conforming SQL language is implementation-defined, as is the list of additional <keyword>s that may be required by the DBMS.

The full set of known functional dependencies is non-standard because the SQL Standard allows implementors to define additional functional dependencies if they choose.