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     Data Manipulation Statements

  2.3     Statements for VAX Data Distributor

  2.4     Statements for Interactive Control

  2.5     Statements for Processing SQL Statements Dynamically

  2.6     SQL Precompiler Statements

  2.7     SQL Module Language Elements

  2.8     Executable and Nonexecutable Statements

  2.9     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      Schema Names
      3.2.1.1      VMS File Specifications
      3.2.1.2      Data Dictionary Path Names
      3.2.1.3      Authorization Identifiers
    3.2.2      Table and View Names
    3.2.3      Column Names
      3.2.3.1      Aliases
      3.2.3.2      Outer References
    3.2.4      Domains
    3.2.5      Trigger Names
    3.2.6      Parameters
      3.2.6.1      Main Parameters and Indicator Parameters
      3.2.6.2      Host Structures and Indicator Arrays
    3.2.7      Index Names
    3.2.8      Cursor Names
    3.2.9      Statement Names (Dynamic SQL Only)
    3.2.10     Constraint Names
    3.2.11     Module, Procedure, and Parameter Names (Module Language Only)
    3.2.12     Storage Area Names
    3.2.13     Storage Map Names

  3.3     Data Types
    3.3.1      Character Data Types
    3.3.2      Fixed-Point Numeric Data Types
    3.3.3      DECIMAL and NUMERIC Data Types
    3.3.4      Floating-Point Numeric Data Types
    3.3.5      DATE Data Type
    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 Literals
    3.4.4      NULL, USER, and CURRENT_TIMESTAMP Keywords

  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.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     UNIQUE 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      SQL Statements

  6.1     ALTER DOMAIN Statement

  6.2     ALTER INDEX Statement

  6.3     ALTER SCHEMA Statement

  6.4     ALTER STORAGE MAP Statement

  6.5     ALTER TABLE Statement

  6.6     BEGIN DECLARE Statement

  6.7     CLOSE Statement

  6.8     COMMENT ON Statement

  6.9     COMMIT Statement

  6.10    CREATE COLLATING SEQUENCE Statement

  6.11    CREATE DOMAIN Statement

  6.12    CREATE INDEX Statement

  6.13    CREATE SCHEDULE Statement (VAX Data Distributor)

  6.14    CREATE SCHEMA Statement

  6.15    CREATE STORAGE AREA Clause

  6.16    CREATE STORAGE MAP Statement

  6.17    CREATE TABLE Statement

  6.18    CREATE TRANSFER Statement (VAX Data Distributor)

  6.19    CREATE TRIGGER Statement

  6.20    CREATE VIEW Statement

  6.21    DCL Invoke ( $ ) Statement

  6.22    DECLARE CURSOR Statement

  6.23    DECLARE CURSOR Statement, Dynamic

  6.24    DECLARE CURSOR Statement, Extended Dynamic

  6.25    DECLARE SCHEMA Statement

  6.26    DECLARE STATEMENT Statement

  6.27    DECLARE TABLE Statement

  6.28    DECLARE TRANSACTION Statement

  6.29    DELETE Statement

  6.30    DESCRIBE Statement

  6.31    DROP COLLATING SEQUENCE Statement

  6.32    DROP CONSTRAINT Statement

  6.33    DROP DOMAIN Statement

  6.34    DROP INDEX Statement

  6.35    DROP PATHNAME Statement

  6.36    DROP SCHEDULE Statement (VAX Data Distributor)

  6.37    DROP SCHEMA Statement

  6.38    DROP STORAGE MAP Statement

  6.39    DROP TABLE Statement

  6.40    DROP TRANSFER Statement (VAX Data Distributor)

  6.41    DROP TRIGGER Statement

  6.42    DROP VIEW Statement

  6.43    EDIT Statement

  6.44    END DECLARE Statement

  6.45    Execute ( @ ) Statement

  6.46    EXECUTE Statement

  6.47    EXECUTE IMMEDIATE Statement

  6.48    EXIT Statement

  6.49    EXPORT Statement

  6.50    FETCH Statement

  6.51    FINISH Statement

  6.52    GRANT Statement

  6.53    GRANT Statement ANSI-Style

  6.54    HELP Statement

  6.55    IMPORT Statement

  6.56    INCLUDE Statement

  6.57    INSERT Statement

  6.58    INTEGRATE Statement

  6.59    OPEN Statement

  6.60    PREPARE Statement

  6.61    PRINT Statement

  6.62    QUIT Statement

  6.63    REINITIALIZE TRANSFER Statement (VAX Data Distributor)

  6.64    RELEASE Statement

  6.65    REVOKE Statement

  6.66    REVOKE Statement ANSI-Style

  6.67    ROLLBACK Statement

  6.68    SELECT Statement
    6.68.1     SELECT Statement:  General Form
    6.68.2     SELECT Statement:  Singleton Select

  6.69    SET Statement

  6.70    SET ALL CONSTRAINTS Statement

  6.71    SET TRANSACTION Statement

  6.72    SHOW Statement

  6.73    SHOW TRANSFER Statement (VAX Data Distributor)

  6.74    START TRANSFER Statement (VAX Data Distributor)

  6.75    STOP TRANSFER Statement (VAX Data Distributor)

  6.76    UPDATE Statement

  6.77    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   Differences from and Extensions to the ANSI Standard

  B.1     Differences from the SQL-1989 Standard

  B.2     Extensions to the SQL-1989 Standard

  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 Descriptor Area (SQLDA)

  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

  E   VIDA Differences

  F   Definitions for the PERSONNEL Sample Database

  G   Logical Names Used by SQL

  H   Obsolete SQL Syntax for Rdb/VMS Version 4.0

  H.1     Punctuation Changes
    H.1.1     Single Quotation Marks Required for String Literals
    H.1.2     Colons Required Before Host Language Variables in Embedded SQL

  H.2     Keyword Changes
    H.2.1     SCHEMA Keyword Replaces DATABASE Keyword
    H.2.2     Underscores Deleted from DECLARE TRANSACTION and SET TRANSACTION Keywords
    H.2.3     CASCADE Keyword Added to DROP Statements

  H.3     Suppressing Diagnostic Messages

  EXAMPLES

  C-1        Fields in the SQLCA

  C-2        Ada SQLCA and Message Vector Declaration

  C-3        BASIC SQLCA and Message Vector Declaration

  C-4        C SQLCA and Message Vector Declaration

  C-5        COBOL SQLCA and Message Vector Declaration

  C-6        FORTRAN SQLCA and Message Vector Declaration

  C-7        Pascal SQLCA and Message Vector Declaration

  C-8        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

  F-1        PERSONNEL Sample Database

  FIGURES

  1-1        Access to Rdb/VMS and VIDA Database Systems

  1-2        A Relational Table

  1-3        A Sample Syntax Diagram (FETCH)

  3-1        The LIST OF BYTE VARYING Data Type

  C-1        Fields of the Message Vector

  TABLES

  1-1        Terms Used to Describe Relational Concepts

  2-1        SQL Data Definition Statements

  2-2        SQL Data Manipulation Statements

  2-3        VAX Data Distributor Statements for SQL

  2-4        SQL Statements for Interactive Control

  2-5        SQL Statements That Process Statements Dynamically

  2-6        SQL Precompiler Statements

  2-7        SQL Module Language Elements

  2-8        Summary of SQL Statements

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

  3-2        Indicator Parameters and Null Values

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

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

  3-5        Embedding Quotation Marks in Literals

  3-6        Alphabetic and Alphanumeric Replacement Edit String Characters

  3-7        Numeric Replacement Edit String Characters

  3-8        Alphanumeric Insertion Edit String Characters

  3-9        Numeric Insertion Edit String Characters

  3-10       Alphanumeric and Numeric Insertion Edit String Characters

  3-11       Numeric Floating Insertion Edit String Characters

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

  3-13       Date Replacement Edit String Characters

  3-14       SQL Conditional Operators

  3-15       Boolean Operators:  AND

  3-16       Boolean Operators:  OR

  3-17       Boolean Operators:  NOT

  3-18       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        Ada Declarations for SQL Data Types

  5-2        C Declarations for SQL Data Types

  5-3        COBOL Declarations for SQL Data Types

  5-4        FORTRAN Declarations for SQL Data Types

  5-5        Pascal Declarations for SQL Data Types

  5-6        PL/I Declarations for SQL Data Types

  6-1        Availability of Row Data for Triggered Actions

  6-2        Classes, Types, and Modes of Cursors

  6-3        Required Terminators for BEGIN DECLARE and END DECLARE Statements

  6-4        SQL Privileges for Schemas, Tables, and Columns

  6-5        Privilege Override Capability

  6-6        SQL and RDO Privileges

  6-7        SQL Statements That Can Be Dynamically Executed

  6-8        Logical Names for Internationalization of SET Statements

  6-9        SQL Share Modes

  6-10       Comparison of Row Locking for Updates

  6-11       Effects of Lock Specifications on Multiuser Access

  6-12       Defaults for the SET and DECLARE TRANSACTION Statements

  A-1        Explanation of Error Message Severity Codes

  A-2        SQL Errors Generated at Run Time

  C-1        Use of SQLCA and Message Vector

  C-2        Values Returned to SQLCODE Field

  D-1        Fields in the SQLDA

  D-2        Codes for SQLTYPE Field of SQLDA

  G-1        Summary of SQL Logical Names

  H-1        Obsolete Keywords for DECLARE and SET TRANSACTION