CONTENTS

  Title Page

  Copyright Page

  Preface

  Technical Changes and New Features

  1      Introduction

  1.1     SQL and Digital Database Products

  1.2     Relational Concepts and Terminology

  1.3     How to Read Syntax Diagrams

  2      Overview of SQL Functions

  2.1     Data Definition Statements

  2.2     Statements That Allow Concurrent Updates

  2.3     Data Manipulation Statements

  2.4     Statements for VAX Data Distributor

  2.5     Statements for Interactive Control

  2.6     Statements for Processing SQL Statements Dynamically

  2.7     SQL Precompiler Statements

  2.8     SQL Module Language Elements

  2.9     Executable and Nonexecutable Statements

  2.10    Summary of SQL Statements

  3      Language and Syntax Elements

  3.1     Keywords and Line Terminators
    3.1.1      Required Keywords
    3.1.2      Optional Keywords
    3.1.3      Statement Terminators and Comment Characters

  3.2     User-Supplied Names
    3.2.1      Database Names
      3.2.1.1      VMS File Specifications
      3.2.1.2      Data Dictionary Path Names
    3.2.2      Aliases
    3.2.3      Names in Multischema Databases
    3.2.4      External Names
    3.2.5      Authorization Identifiers
    3.2.6      Connection Names
    3.2.7      Catalog Names
    3.2.8      Schema Names
    3.2.9      Table and View Names
    3.2.10     Column Names
      3.2.10.1     Correlation Names
      3.2.10.2     Outer References
    3.2.11     Domains
    3.2.12     Trigger Names
    3.2.13     Parameters
      3.2.13.1     Main Parameters and Indicator Parameters
      3.2.13.2     Host Structures and Indicator Arrays
    3.2.14     Index Names
    3.2.15     Cursor Names
    3.2.16     Statement Names (Dynamic SQL Only)
    3.2.17     Constraint Names
    3.2.18     Module, Procedure, and Parameter Names (Module Language Only)
    3.2.19     Storage Area Names
    3.2.20     Storage Map Names

  3.3     Data Types
    3.3.1      Character Data Types
    3.3.2      DECIMAL and NUMERIC Data Types
    3.3.3      Fixed-Point Numeric Data Types
    3.3.4      Floating-Point Numeric Data Types
    3.3.5      Date-Time Data Types
    3.3.6      LIST OF BYTE VARYING Data Type
    3.3.7      Data Type Conversions
      3.3.7.1      Conversion from Unsupported Data Types
      3.3.7.2      Conversion Between Supported Data Types

  3.4     Literals
    3.4.1      Numeric Literals
    3.4.2      Character String Literals
      3.4.2.1      Quoted Character String Literals
      3.4.2.2      Hexadecimal Character String Literals
    3.4.3      Date-Time Literals
    3.4.4      Keywords That Refer to Literals

  3.5     SQL and DATATRIEVE Formatting Clauses
    3.5.1      QUERY HEADER Clause
    3.5.2      EDIT STRING Clause

  3.6     Value Expressions
    3.6.1      Functions
      3.6.1.1      COUNT
      3.6.1.2      SUM
      3.6.1.3      AVG
      3.6.1.4      MAX
      3.6.1.5      MIN
      3.6.1.6      EXTRACT
      3.6.1.7      CAST
      3.6.1.8      CURRENT_DATE
      3.6.1.9      CURRENT_TIME
      3.6.1.10     CURRENT_TIMESTAMP
    3.6.2      Database Keys
    3.6.3      Substrings
    3.6.4      String Concatenation Operator
    3.6.5      Arithmetic Expressions and Operators

  3.7     Predicates
    3.7.1      Basic Predicate
    3.7.2      BETWEEN Predicate
    3.7.3      Complex Predicate
    3.7.4      CONTAINING Predicate
    3.7.5      EXISTS Predicate
    3.7.6      IN Predicate
    3.7.7      IS NULL Predicate
    3.7.8      LIKE Predicate
    3.7.9      Quantified Predicate
    3.7.10     STARTING WITH Predicate
    3.7.11     SINGLE Predicate

  3.8     Select Expressions and Column Select Expressions
    3.8.1      Select Expressions
    3.8.2      Column Select Expressions

  3.9     Context Structures

  4      SQL Module Language

  4.1     SQL Module Language Syntax

  4.2     Equivalent SQL and Host Language Data Types

  4.3     SQL Module Language Processor Command Line

  5      SQL Precompiler

  5.1     Embedding SQL Statements in Programs

  5.2     SQL Precompiler Command Line

  5.3     Host Language Variable Declarations Supported by the Precompiler
    5.3.1      Supported Ada Variable Declarations
    5.3.2      Supported C Variable Declarations
    5.3.3      Supported COBOL Variable Declarations
    5.3.4      Supported FORTRAN Variable Declarations
    5.3.5      Supported Pascal Variable Declarations
    5.3.6      Supported PL/I Variable Declarations

  6      Rdb/VMS System Relations

  6.1     RDB$CONSTRAINTS System Relation

  6.2     RDB$CONSTRAINT_RELATIONS System Relation

  6.3     RDB$DATABASE System Relation

  6.4     RDB$FIELD_VERSIONS System Relation

  6.5     RDB$FIELDS System Relation

  6.6     RDB$INDEX_SEGMENTS System Relation

  6.7     RDB$INDICES System Relation

  6.8     RDB$RELATION_FIELDS System Relation

  6.9     RDB$RELATIONS System Relation

  6.10    RDB$VIEW_RELATIONS System Relation

  6.11    RDBVMS$CATALOG_SCHEMA System Relation

  6.12    RDBVMS$COLLATIONS System Relation

  6.13    RDBVMS$INTERRELATIONS System Relation

  6.14    RDBVMS$PRIVILEGES System Relation

  6.15    RDBVMS$RELATION_CONSTRAINTS System Relation

  6.16    RDBVMS$RELATION_CONSTRAINT_FLDS System Relation

  6.17    RDBVMS$STORAGE_MAPS System Relation

  6.18    RDBVMS$STORAGE_MAP_AREAS System Relation

  6.19    RDBVMS$SYNONYMS System Relation

  6.20    RDBVMS$TRIGGERS System Relation
    6.20.1     Clumplets That Can Be Used in RDBVMS$TRIGGER_ACTIONS

  7      SQL Statements

  7.1     ALTER DATABASE Statement

  7.2     ALTER DOMAIN Statement

  7.3     ALTER INDEX Statement

  7.4     ALTER STORAGE MAP Statement

  7.5     ALTER TABLE Statement

  7.6     ATTACH Statement

  7.7     BEGIN DECLARE Statement

  7.8     CLOSE Statement

  7.9     COMMENT ON Statement

  7.10    COMMIT Statement

  7.11    CONNECT Statement

  7.12    CREATE CATALOG Statement

  7.13    CREATE COLLATING SEQUENCE Statement

  7.14    CREATE DATABASE Statement

  7.15    CREATE DOMAIN Statement

  7.16    CREATE INDEX Statement

  7.17    CREATE SCHEDULE Statement (VAX Data Distributor)

  7.18    CREATE SCHEMA Statement

  7.19    CREATE STORAGE AREA Clause

  7.20    CREATE STORAGE MAP Statement

  7.21    CREATE TABLE Statement

  7.22    CREATE TRANSFER Statement (VAX Data Distributor)

  7.23    CREATE TRIGGER Statement

  7.24    CREATE VIEW Statement

  7.25    DCL Invoke ( $ ) Statement

  7.26    DECLARE ALIAS Statement

  7.27    DECLARE CURSOR Statement

  7.28    DECLARE CURSOR Statement, Dynamic

  7.29    DECLARE CURSOR Statement, Extended Dynamic

  7.30    DECLARE STATEMENT Statement

  7.31    DECLARE TABLE Statement

  7.32    DECLARE TRANSACTION Statement

  7.33    DELETE Statement

  7.34    DESCRIBE Statement

  7.35    DISCONNECT Statement

  7.36    DROP CATALOG Statement

  7.37    DROP COLLATING SEQUENCE Statement

  7.38    DROP CONSTRAINT Statement

  7.39    DROP DATABASE Statement

  7.40    DROP DOMAIN Statement

  7.41    DROP INDEX Statement

  7.42    DROP PATHNAME Statement

  7.43    DROP SCHEDULE Statement (VAX Data Distributor)

  7.44    DROP SCHEMA Statement

  7.45    DROP STORAGE MAP Statement

  7.46    DROP TABLE Statement

  7.47    DROP TRANSFER Statement (VAX Data Distributor)

  7.48    DROP TRIGGER Statement

  7.49    DROP VIEW Statement

  7.50    EDIT Statement

  7.51    END DECLARE Statement

  7.52    Execute ( @ ) Statement

  7.53    EXECUTE Statement

  7.54    EXECUTE IMMEDIATE Statement

  7.55    EXIT Statement

  7.56    EXPORT Statement

  7.57    FETCH Statement

  7.58    GRANT Statement

  7.59    GRANT Statement ANSI-Style

  7.60    HELP Statement

  7.61    IMPORT Statement

  7.62    INCLUDE Statement

  7.63    INSERT Statement

  7.64    INTEGRATE Statement

  7.65    OPEN Statement

  7.66    PREPARE Statement

  7.67    PRINT Statement

  7.68    QUIT Statement

  7.69    REINITIALIZE TRANSFER Statement (VAX Data Distributor)

  7.70    RELEASE Statement

  7.71    REVOKE Statement

  7.72    REVOKE Statement ANSI-Style

  7.73    ROLLBACK Statement

  7.74    SELECT Statement
    7.74.1     SELECT Statement:  General Form
    7.74.2     SELECT Statement:  Singleton Select

  7.75    SET Statement

  7.76    SET ALL CONSTRAINTS Statement

  7.77    SET ANSI Statement

  7.78    SET CATALOG Statement

  7.79    SET CONNECT Statement

  7.80    SET SCHEMA Statement

  7.81    SET TRANSACTION Statement

  7.82    SHOW Statement

  7.83    SHOW TRANSFER Statement (VAX Data Distributor)

  7.84    START TRANSFER Statement (VAX Data Distributor)

  7.85    STOP TRANSFER Statement (VAX Data Distributor)

  7.86    UPDATE Statement

  7.87    WHENEVER Statement

  A   Syntax Differences Between SQL and RDO

  A.1     Syntax Differences Between SQL and RDO

  B   Error Messages

  B.1     Types of Error Messages and Their Format

  B.2     Error Message Documentation

  B.3     Errors Generated When You Use SQL Statements

  B.4     Identifying Precompiler and Module Language Errors

  C   The SQL Communications Area (SQLCA) and Message Vector

  C.1     The SQLCA

  C.2     The Message Vector

  C.3     Declarations of the SQLCA and Message Vector

  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 Standard

  E.1     Differences from the SQL-1989 Standard

  E.2     Extensions to the SQL-1989 Standard

  F   VIDA Differences

  G   Logical Names Used by SQL

  H   Obsolete SQL Syntax for Rdb/VMS Version 4.1

  H.1     Incompatible Syntax
    H.1.1      CREATE SCHEMA Meaning Incompatibly Changed
    H.1.2      SHOW SCHEMA Meaning Incompatibly Changed
    H.1.3      DROP SCHEMA Meaning Incompatibly Changed
    H.1.4      DROP TABLE Now Restricts by Default
    H.1.5      Database Handle Names Restricted to 25 Characters

  H.2     Deprecated Syntax
    H.2.1      Obsolete Keywords

  H.3     Reserved Words Deprecated as Identifiers
    H.3.1      ANSI89 Reserved Words
    H.3.2      ANSI/ISO SQL2 Draft Standard Reserved Words
    H.3.3      SQL3 Reserved Words

  H.4     Punctuation Changes
    H.4.1      Single Quotation Marks Required for String Literals
    H.4.2      Double Quotation Marks Required for ANSI/ISO Delimited Identifiers
    H.4.3      Colons Required Before Host Language Variables in SQL Module Language

  H.5     Suppressing Diagnostic Messages

  EXAMPLES

  6-1        BASIC Program That Uses an SQL Module to Query System Relations

  6-2        Reading System Relations Using an SQL Module

  7-1        Adding Columns with Default Values to Tables

  C-1        Fields in the SQLCA

  C-2        Including Error Literals in a COBOL Program

  C-3        Ada SQLCA and Message Vector Declaration

  C-4        BASIC SQLCA and Message Vector Declaration

  C-5        C SQLCA and Message Vector Declaration

  C-6        COBOL SQLCA and Message Vector Declaration

  C-7        FORTRAN SQLCA and Message Vector Declaration

  C-8        Pascal SQLCA and Message Vector Declaration

  C-9        PL/I SQLCA and Message Vector Declaration

  D-1        Declaration of the SQLDA in Ada

  D-2        Declaration of the SQLDA in BASIC

  D-3        Declaration of the SQLDA in C

  D-4        Declaration of the SQLDA in PL/I

  D-5        Declaration of the SQLDA2 in Ada

  D-6        Declaration of the SQLDA2 in BASIC

  D-7        Declaration of the SQLDA2 in C

  FIGURES

  1-1        Components of Rdb/VMS

  1-2        A Relational Table

  1-3        A Sample Syntax Diagram (FETCH)

  3-1        A Table with a List Column

  C-1        Fields of the Message Vector

  TABLES

  1-1        Differences in Relational Terminology

  2-1        SQL Data Definition Statements

  2-2        Requirements for Making Metadata Updates

  2-3        SQL Data Manipulation Statements

  2-4        VAX Data Distributor Statements for SQL

  2-5        SQL Statements for Interactive Control

  2-6        SQL Statements That Process Statements Dynamically

  2-7        SQL Precompiler Statements

  2-8        SQL Module Language Elements

  2-9        Summary of SQL Statements

  3-1        Summary of User-Supplied Names Used in SQL

  3-2        External and Internal Names

  3-3        Indicator Parameters and Null Values

  3-4        Comparison of SQL, DB2, and RDO Keywords with VMS Data Types

  3-5        Interval Qualifiers

  3-6        Fields in Year-Month INTERVAL Columns

  3-7        Fields in Day-Time INTERVAL Columns

  3-8        Format of Text Strings Converted to or from DATE VMS Data Type

  3-9        Conversion Rules

  3-10       Embedding Quotation Marks in Literals

  3-11       Alphabetic and Alphanumeric Replacement Edit String Characters

  3-12       Numeric Replacement Edit String Characters

  3-13       Alphanumeric Insertion Edit String Characters

  3-14       Numeric Insertion Edit String Characters

  3-15       Alphanumeric and Numeric Insertion Edit String Characters

  3-16       Numeric Floating Insertion Edit String Characters

  3-17       Floating-Point, Null Value, and Missing Value String Characters

  3-18       Date Replacement Edit String Characters

  3-19       Valid Operators Involving Date-Time and Interval Values

  3-20       SQL Conditional Operators

  3-21       Boolean Operators:  AND

  3-22       Boolean Operators:  OR

  3-23       Boolean Operators:  NOT

  3-24       Summary of Different Forms of the Select Statement

  4-1        Default Passing Mechanism for Host Languages to SQL Modules

  4-2        SQL and Corresponding VMS Data Types for Module Language

  4-3        Ada Declarations for SQL Formal Parameter Data Types

  4-4        BASIC Declarations for SQL Formal Parameter Data Types

  4-5        C Declarations for SQL Formal Parameter Data Types

  4-6        COBOL Declarations for SQL Formal Parameter Data Types

  4-7        FORTRAN Declarations for SQL Formal Parameter Data Types

  4-8        Pascal Declarations for SQL Formal Parameter Data Types

  4-9        PL/I Declarations for SQL Formal Parameter Data Types

  5-1        Precompiler Data Type Mapping

  5-2        Ada Declarations for SQL Data Types

  5-3        C Declarations for SQL Data Types

  5-4        COBOL Declarations for SQL Data Types

  5-5        FORTRAN Declarations for SQL Data Types

  5-6        Pascal Declarations for SQL Data Types

  5-7        PL/I Declarations for SQL Data Types

  6-1        SQL Data Types and Their RDO Equivalents

  6-2        The RDB$CONSTRAINTS System Relation

  6-3        The RDB$CONSTRAINT_RELATIONS System Relation

  6-4        The RDB$DATABASE System Relation

  6-5        The RDB$FIELD_VERSIONS System Relation

  6-6        Values for the RDB$FIELD_SUB_TYPE Field

  6-7        The RDB$FIELDS System Relation

  6-8        The RDB$INDEX_SEGMENTS System Relation

  6-9        The RDB$INDICES System Relation

  6-10       The RDB$RELATION_FIELDS System Relation

  6-11       The RDB$RELATIONS System Relation

  6-12       The RDB$VIEW_RELATIONS System Relation

  6-13       The RDBVMS$CATALOG_SCHEMA System Relation

  6-14       The RDBVMS$COLLATIONS System Relation

  6-15       The RDBVMS$INTERRELATIONS System Relation

  6-16       The RDBVMS$PRIVILEGES System Relation

  6-17       The RDBVMS$RELATION_CONSTRAINTS System Relation

  6-18       Values for RDBVMS$CONSTRAINT_TYPE

  6-19       The RDBVMS$RELATION_CONSTRAINT_FLDS System Relation

  6-20       The RDBVMS$STORAGE_MAPS System Relation

  6-21       The RDBVMS$STORAGE_MAP_AREAS System Relation

  6-22       The RDBVMS$SYNONYMS System Relation

  6-23       The RDBVMS$TRIGGERS System Relation

  6-24       Trigger Type Values

  7-1        Requirements for Making Metadata Updates to Database-Wide Parameters

  7-2        Availability of Row Data for Triggered Actions

  7-3        Classes, Types, and Modes of Cursors

  7-4        Required Terminators for BEGIN DECLARE and END DECLARE Statements

  7-5        SQL Privileges for Databases, Tables, and Columns

  7-6        Privilege Override Capability

  7-7        SQL and RDO Privileges

  7-8        SQL Statements That Can Be Dynamically Executed

  7-9        Logical Names for Internationalization of SET Statements

  7-10       SQL Share Modes

  7-11       Comparison of Row Locking for Updates

  7-12       Effects of Lock Specifications on Multiuser Access

  7-13       Defaults for the SET and DECLARE TRANSACTION Statements

  A-1        Syntax Differences Between SQL and RDO

  A-2        Syntax Differences Between RDO and SQL

  B-1        Explanation of Error Message Severity Codes

  B-2        SQL Errors Generated at Run Time

  C-1        Use of SQLCA and Message Vector

  C-2        Values Returned to SQLCODE Field

  C-3        Including the Error Literals File in Programs

  D-1        Fields in the SQLDA

  D-2        Codes for SQLTYPE Field of SQLDA and SQLDA2

  D-3        Fields in the SQLDA2

  D-4        Codes for Interval Qualifiers in the SQLDA2

  D-5        Codes for Date-Time Data Types in the SQLDA2

  G-1        Summary of SQL Logical Names

  H-1        Deprecated Keywords for SQL Version 4.1

  H-2        Obsolete SQL Keywords