CONTENTS

  Title Page

  Copyright Page

  Send Us Your Comments

  Preface

  Technical Changes and New Features

  1      Introduction to SQL Syntax
    1.1 Conformance to Federal Information Processing Standard
    1.2 Using SQL
      1.2.1      Defining SQL Symbols
    1.3 How to Read Syntax Diagrams
    1.4 Executable and Nonexecutable Statements
    1.5 Summary of SQL Statements
    1.6 Keywords and Line Terminators
      1.6.1      Statement Terminators and Comment Characters
    1.7 Support for Multivendor Integration Architecture

  2      Language and Syntax Elements
    2.1 Supported Character Sets
      2.1.1      Logical Names for Character Sets
      2.1.2      Identifier Character Sets
      2.1.3      Default Character Sets
      2.1.4      National Character Sets
      2.1.5      Literal Character Sets
    2.2 User-Supplied Names
      2.2.1      Database Names
        2.2.1.1      DEC Rdb Attach Specifications
        2.2.1.2      Repository Path Names
        2.2.1.3      DBI Attach Specification
        2.2.1.4      DEC DB Gateway Attach Specification
      2.2.2      Aliases
      2.2.3      Names in Multischema Databases
      2.2.4      Stored Names
      2.2.5      Authorization Identifiers
      2.2.6      Connection Names
      2.2.7      Catalog Names
      2.2.8      Schema Names
      2.2.9      Table and View Names
      2.2.10     Column Names
        2.2.10.1     Correlation Names
        2.2.10.2     Outer References
      2.2.11     Domain Names
      2.2.12     Trigger Names
      2.2.13     Index Names
      2.2.14     Cursor Names
      2.2.15     Constraint Names
      2.2.16     Storage Area Names
      2.2.17     Storage Map Names
      2.2.18     Statement Names (Dynamic SQL Only)
      2.2.19     Multistatement Procedure Variables and Stored Procedure Parameters
        2.2.19.1     Host Structures and Indicator Arrays
      2.2.20     Parameters, Stored Procedure Parameters, and SQL Variables
        2.2.20.1     Main Parameters and Indicator Parameters
      2.2.21     Nonstored Module, Procedure, and Parameter Names (Module Language Only)
    2.3 Data Types
      2.3.1      Character Data Types
      2.3.2      DECIMAL and NUMERIC Data Types
      2.3.3      Fixed-Point Numeric Data Types
      2.3.4      Floating-Point Numeric Data Types
      2.3.5      Date-Time Data Types
      2.3.6      LIST OF BYTE VARYING Data Type
        2.3.6.1      On-Disk Format of Lists
      2.3.7      Data Type Conversions
        2.3.7.1      Conversion from Unsupported Data Types
        2.3.7.2      Conversion Between Supported Data Types
    2.4 Literals
      2.4.1      Numeric Literals
      2.4.2      Character String Literals
        2.4.2.1      Quoted Character String Literals
          2.4.2.1.1       Quoted Character String Literals Qualified by a Character Set
          2.4.2.1.2       Quoted Character String Literals Qualified by the National Character Set
        2.4.2.2      Hexadecimal Character String Literals
      2.4.3      Date-Time Literals
      2.4.4      Keywords That Refer to Literals
    2.5 SQL and DATATRIEVE Formatting Clauses
      2.5.1      QUERY HEADER Clause
      2.5.2      EDIT STRING Clause
    2.6 Value Expressions
      2.6.1      Built-In Functions
        2.6.1.1      CHARACTER_LENGTH Function
        2.6.1.2      OCTET_LENGTH Function
        2.6.1.3      CAST Function
        2.6.1.4      UPPER Function
        2.6.1.5      LOWER Function
        2.6.1.6      TRANSLATE Function
        2.6.1.7      SUBSTRING Function
        2.6.1.8      EXTRACT Function
        2.6.1.9      USER Function
        2.6.1.10     CURRENT_USER Function
        2.6.1.11     SESSION_USER Function
        2.6.1.12     SYSTEM_USER Function
        2.6.1.13     CURRENT_DATE Function
        2.6.1.14     CURRENT_TIME Function
        2.6.1.15     CURRENT_TIMESTAMP Function
      2.6.2      Aggregate Functions
        2.6.2.1      COUNT Function
        2.6.2.2      SUM Function
        2.6.2.3      AVG Function
        2.6.2.4      MAX Function
        2.6.2.5      MIN Function
      2.6.3      External Functions
      2.6.4      Database Keys
      2.6.5      String Concatenation Operator
      2.6.6      Arithmetic Expressions and Operators
      2.6.7      Conditional Expressions
        2.6.7.1      NULLIF Expressions
        2.6.7.2      COALESCE Expressions
        2.6.7.3      CASE Expressions
    2.7 Predicates
      2.7.1      Basic Predicate
      2.7.2      BETWEEN Predicate
      2.7.3      Complex Predicate
      2.7.4      CONTAINING Predicate
      2.7.5      EXISTS Predicate
      2.7.6      IN Predicate
      2.7.7      IS NULL Predicate
      2.7.8      LIKE Predicate
      2.7.9      Quantified Predicate
      2.7.10     SINGLE Predicate
      2.7.11     STARTING WITH Predicate
    2.8 Select Expressions and Column Select Expressions
      2.8.1      Select Expressions
      2.8.2      Column Select Expressions
    2.9 Context Structures
    2.10 Database Options

  3      SQL Module Language
    3.1 Overview of the SQL Module Language and Processor
    3.2 SQL Module Language Syntax
    3.3 Declaring the Length of Character Parameters
    3.4 Equivalent SQL and Host Language Data Types
    3.5 Using Context Files with SQL Module Language
    3.6 SQL Module Language Processor Command Line

  4      SQL Precompiler
    4.1 Embedding SQL Statements in Programs
      4.1.1      Using the Two-Phase Commit Protocol in Embedded Programs
      4.1.2      Embedding Module Clauses in Host Language Code
    4.2 SQL Precompiler Syntax
    4.3 SQL Precompiler Command Line
    4.4 Host Language Variable Declarations Supported by the Precompiler
      4.4.1      Specifying Length of Character Parameters
      4.4.2      Supported Ada Variable Declarations
      4.4.3      Supported C Variable Declarations
      4.4.4      Supported COBOL Variable Declarations
      4.4.5      Supported FORTRAN Variable Declarations
      4.4.6      Supported Pascal Variable Declarations
      4.4.7      Supported PL/I Variable Declarations

  5      SQL Routines

  SQL$CLOSE_CURSORS

  SQL$GET_ERROR_TEXT

  SQL$SIGNAL

  SQL_DEREGISTER_ERROR_HANDLER

  SQL_GET_ERROR_HANDLER

  SQL_REGISTER_ERROR_HANDLER

  6      SQL Statements

  ALTER DATABASE Statement

  ALTER DATABASE Statement (DBI)

  ALTER DOMAIN Statement

  ALTER INDEX Statement

  ALTER LINK Statement (DBI)

  ALTER SCHEDULE Statement (Data Distributor)

  ALTER STORAGE MAP Statement

  ALTER TABLE Statement

  ALTER TABLE Statement (DBI)

  ALTER TRANSFER Statement (Data Distributor)

  ATTACH Statement

  BEGIN DECLARE Statement

  CALL Statement

  CASE Control Statement

  CLOSE Statement

  COMMENT ON Statement

  COMMIT Statement

  Compound Statement

  CONNECT Statement

  CREATE CATALOG Statement

  CREATE COLLATING SEQUENCE Statement

  CREATE DATABASE Statement

  CREATE DATABASE Statement (DBI)

  CREATE DOMAIN Statement

  CREATE DOMAIN Statement (DBI)

  CREATE FUNCTION Statement

  CREATE INDEX Statement

  CREATE INDEX Statement, Pass-Through (DBI)

  CREATE LINK Statement (DBI)

  CREATE MODULE Statement

  CREATE OUTLINE Statement

  CREATE SCHEDULE Statement (Data Distributor)

  CREATE SCHEMA Statement

  CREATE STORAGE AREA Clause

  CREATE STORAGE MAP Statement

  CREATE TABLE Statement

  CREATE TABLE Statement (DBI)

  CREATE TABLE Statement, Pass-Through (DBI)

  CREATE TRANSFER Statement (Data Distributor)

  CREATE TRIGGER Statement

  CREATE VIEW Statement

  CREATE VIEW Statement (DBI)

  CREATE VIEW Statement, Partitioned (DBI)

  DCL Invoke ( $ ) Statement

  DECLARE ALIAS Statement

  DECLARE CURSOR Statement

  DECLARE CURSOR Statement, Dynamic

  DECLARE CURSOR Statement, Extended Dynamic

  DECLARE MODULE Statement

  DECLARE STATEMENT Statement

  DECLARE TABLE Statement

  DECLARE TRANSACTION Statement

  DECLARE Variable Statement

  DELETE Statement

  DESCRIBE Statement

  DISCONNECT Statement

  DROP CATALOG Statement

  DROP COLLATING SEQUENCE Statement

  DROP CONSTRAINT Statement

  DROP DATABASE Statement

  DROP DOMAIN Statement

  DROP FUNCTION Statement

  DROP INDEX Statement

  DROP INDEX Statement (DBI), Pass-Through

  DROP LINK Statement (DBI)

  DROP MODULE Statement

  DROP OUTLINE Statement

  DROP PATHNAME Statement

  DROP SCHEDULE Statement (Data Distributor)

  DROP SCHEMA Statement

  DROP STORAGE MAP Statement

  DROP TABLE Statement

  DROP TRANSFER Statement (Data Distributor)

  DROP TRIGGER Statement

  DROP VIEW Statement

  EDIT Statement

  END DECLARE Statement

  Execute ( @ ) Statement

  EXECUTE Statement

  EXECUTE IMMEDIATE Statement

  EXIT Statement

  EXPORT Statement

  FETCH Statement

  FOR Control Statement

  GET DIAGNOSTICS Statement

  GRANT Statement

  GRANT Statement, ANSI/ISO-Style

  GRANT Statement (Data Distributor)

  GRANT Statement (DBI)

  GRANT Statement (DBI), Pass-Through

  HELP Statement

  IF Control Statement

  IMPORT Statement

  INCLUDE Statement

  INSERT Statement

  INTEGRATE Statement

  LEAVE Control Statement

  LOOP Control Statement

  OPEN Statement

  PREPARE Statement

  PRINT Statement

  QUIT Statement

  REINITIALIZE TRANSFER Statement (Data Distributor)

  RELEASE Statement

  REVOKE Statement

  REVOKE Statement, ANSI/ISO-Style

  REVOKE Statement (Data Distributor)

  REVOKE Statement (DBI)

  REVOKE Statement (DBI), Pass-Through

  ROLLBACK Statement

  SELECT Statement
    SELECT Statement:  General Form
    SELECT Statement:  Singleton Select

  SET Statement

  SET ALL CONSTRAINTS Statement

  SET ANSI Statement

  SET Control Statement

  SET CATALOG Statement

  SET CHARACTER LENGTH Statement

  SET CONNECT Statement

  SET DEFAULT CHARACTER SET Statement

  SET DEFAULT DATE FORMAT Statement

  SET DIALECT Statement

  SET IDENTIFIER CHARACTER SET Statement

  SET KEYWORD RULES Statement

  SET LITERAL CHARACTER SET Statement

  SET LOGICAL_NAME TRANSLATION FOR TRANSFER Statement (Data Distributor)

  SET NAMES Statement

  SET NATIONAL CHARACTER SET Statement

  SET OPTIMIZATION LEVEL Statement

  SET QUOTING RULES Statement

  SET SCHEMA Statement

  SET TRANSACTION Statement

  SET VIEW UPDATE RULES Statement

  SHOW Statement

  SHOW TRANSFER Statement (Data Distributor)

  Simple Statement

  START TRANSFER Statement (Data Distributor)

  STOP TRANSFER Statement (Data Distributor)

  TRACE Control Statement

  UPDATE Statement

  WHENEVER Statement

  A Error Messages
    A.1 Types of Error Messages and Their Format
    A.2 Error Message Documentation
    A.3 Errors Generated when You Use SQL Statements
    A.4 Identifying Precompiler and Module Language Errors

  B The SQL Communications Area (SQLCA) and the Message Vector
    B.1 The SQLCA
    B.2 The Message Vector
    B.3 Declarations of the SQLCA and the Message Vector

  C SQLSTATE
    C.1 Definition of the SQLSTATE Status Parameter
    C.2 Use of the SQLSTATE Status Parameter

  D The SQL Dynamic Descriptor Areas (SQLDA and SQLDA2)
    D.1 Purpose of the SQLDA
    D.2 How SQL and Programs Use the SQLDA
    D.3 Declaring the SQLDA
    D.4 Description of Fields in the SQLDA
    D.5 Parameters Associated with the SQLDA: SQLSIZE and SQLDAPTR
    D.6 Purpose of the SQLDA2
      D.6.1     Declaring the SQLDA2
      D.6.2     Description of Fields in the SQLDA2

  E Differences from and Extensions to the ANSI/ISO 1989 Standard
    E.1 Differences from the ANSI/ISO 1989 Standard
    E.2 Extensions to the ANSI/ISO 1989 Standard

  F Differences from and Extensions to the MIA Specification
    F.1 DEC Rdb SQL Features Supporting the MIA Specification
    F.2 DEC Rdb Extensions to the MIA Specification
      F.2.1      ANSI/ISO SQL-Compliant DEC Rdb Features that Are Not Part of the MIA Specification

  G Logical Names Used by SQL

  H Obsolete SQL Syntax
    H.1 Incompatible Syntax
      H.1.1     Incompatible Syntax Containing the SCHEMA Keyword
        H.1.1.1      CREATE SCHEMA Meaning Incompatibly Changed
        H.1.1.2      SHOW SCHEMA Meaning Incompatibly Changed
        H.1.1.3      DROP SCHEMA Meaning Incompatibly Changed
      H.1.2     DROP TABLE Now Restricts by Default
      H.1.3     Database Handle Names Restricted to 25 Characters
      H.1.4     Deprecated Default Semantics of the ORDER BY Clause
      H.1.5     Change to EXTERNAL NAMES IS Clause
    H.2 Deprecated Syntax
      H.2.1     Command Line Qualifiers
      H.2.2     Deprecated Interactive SQL Statement
      H.2.3     Constraint Conformance to the ANSI/ISO SQL Standard
      H.2.4     Obsolete Keywords
    H.3 Reserved Words Deprecated as Identifiers
      H.3.1     ANSI/ISO 1989 Standard Reserved Words
      H.3.2     ANSI/ISO SQL Standard Reserved Words
      H.3.3     ANSI/ISO SQL3 Reserved Words
      H.3.4     VAX MACRO Reserved Symbols
    H.4 Punctuation Changes
      H.4.1     Single Quotation Marks Required for String Literals
      H.4.2     Double Quotation Marks Required for ANSI/ISO SQL Delimited Identifiers
      H.4.3     Colons Required Before Host Language Variables in SQL Module Language
    H.5 Suppressing Diagnostic Messages