CONTENTS

  Title Page

  Copyright Page

  Preface

  Technical Changes and New Features

  1      Introduction to SQL

  1.1     Using SQL Interactively

  1.2     Using SQL in Application Programs
    1.2.1      Using the SQL Module Processor
    1.2.2      Using the SQL Precompiler
    1.2.3      Using Dynamic SQL

  1.3     Using Sample Databases and Programs to Learn SQL
    1.3.1      Using the Sample Personnel Databases in Examples
    1.3.2      Finding Online Program Examples
    1.3.3      Distinguishing Among Rdb/VMS Kits

  1.4     Using SQL with Other Digital Products
    1.4.1      Accessing Non-Rdb/VMS and Rdb/VMS Databases from SQL
    1.4.2      Digital Products That Can Access SQL Databases

  1.5     Using This Guide

  2      Getting Started with Interactive SQL

  2.1     Learning about Interactive SQL
    2.1.1      Creating Versions of the Sample Personnel Database
    2.1.2      Invoking Interactive SQL
    2.1.3      Displaying Examples in 132-Character Width
    2.1.4      Defining the SQL$DATABASE Logical Name
    2.1.5      Attaching to One or More Databases
    2.1.6      Detaching from One or More Databases
    2.1.7      Naming Database Objects in Rdb/VMS Single and Multiple Schema Database

  2.2     Displaying Database Information
    2.2.1      Displaying Table Information
    2.2.2      Displaying Domains
    2.2.3      Displaying Constraints
    2.2.4      Displaying Indexes
    2.2.5      Displaying a List of Views
    2.2.6      Displaying Storage Map Information
    2.2.7      Displaying Trigger Information

  2.3     Querying the PERSONNEL Database

  2.4     Using SQL Command Procedures

  2.5     Tailoring Your Interactive SQL Environment

  2.6     Using Editors with SQL

  3      Retrieving Data

  3.1     What Is a Result Table

  3.2     Using Examples in This Chapter

  3.3     Understanding the Structure of SELECT Statements

  3.4     Selecting Columns

  3.5     Specifying Data Sources for Your Result Table

  3.6     Retrieving All Rows in a Table or View

  3.7     Retrieving Limited Rows in a Table or View

  3.8     Retrieving Unique Rows:  Eliminating Duplicate Sets of Values

  3.9     Retrieving Rows in Sorted Order

  3.10    Retrieving Rows That Satisfy Conditions
    3.10.1     Specifying Values in Conditions
    3.10.2     Using Conditional and Boolean Operators
    3.10.3     Selecting Data Based on a Matching Condition
      3.10.3.1     Retrieving Rows Where Values Partially Match Another Value
      3.10.3.2     Retrieving Rows Where Values Do Not Match Another Value
      3.10.3.3     Retrieving Rows Where Values Exactly Match One of Several Values
    3.10.4     Selecting Data Based on Range Retrieval
    3.10.5     Selecting Data Based on Nonexistent (Null) Values
    3.10.6     Selecting Data Based on Existence or Nonexistence of a Set of Rows
    3.10.7     Selecting Data Based on Negated, Combined, or Alternative Conditions
      3.10.7.1     Retrieving Rows That Do Not Satisfy a Condition
      3.10.7.2     Retrieving Rows That Satisfy Two or More Conditions
      3.10.7.3     Retrieving Rows That Satisfy Any One of Several Conditions
      3.10.7.4     Using Parentheses to Control Evaluation of Conditions

  3.11    Using Functions (Aggregate Expressions)

  3.12    Joining Tables

  3.13    Combining Tables

  3.14    Using Column Select Expressions as Value Expressions

  4      Retrieving Data Using Cursors

  4.1     Understanding How Cursors Work
    4.1.1      Comparing Cursors and Views
    4.1.2      Understanding Basic Cursor Operations
    4.1.3      Understanding the Different Categories of Cursors

  4.2     Using Table Cursors

  4.3     Using List Cursors

  4.4     Using Cursors in Programs

  4.5     Using Dynamic Cursors

  4.6     Using Extended Dynamic Cursors

  5      Loading and Updating Data

  5.1     Loading a Database
    5.1.1      Evaluating Constraints at Commit Time
    5.1.2      Examining Tables with Constraints That Refer to Other Tables
    5.1.3      Determining the Best Time to Create Indexes

  5.2     Inserting Rows

  5.3     Using List Cursors to Insert Large Data Structures

  5.4     Updating Rows

  5.5     Deleting Rows

  5.6     Using Triggers with Insert, Update, and Delete Operations

  6      Developing Host Language Programs

  6.1     Developing Programs for SQL Applications
    6.1.1      Examining the Database Structure
      6.1.1.1      Using a Database Structure Listing While Testing SQL Statements
      6.1.1.2      Using a Database Structure Listing While Converting SQL Statements
    6.1.2      Testing SQL Statements Interactively
      6.1.2.1      Creating an SQL Command File Using a Text Editor
      6.1.2.2      Creating an SQL Command Procedure from a Session Log
    6.1.3      Converting Interactive SQL Statements to SQL Statements in Programs
    6.1.4      Recommendations for Using SQL Module Language Instead of Precompiled SQL

  6.2     Referencing Other Sources for Programming Information

  7      Creating and Processing Programs with the SQL Module Processor

  7.1     Overview of SQL Module Creation

  7.2     Creating an SQL Module Source File
    7.2.1      Including Blank Lines and Comments in an SQL Module
    7.2.2      Deciding on the Scope of a Module
    7.2.3      Naming a Module
    7.2.4      Identifying the Host Language That Calls Module Procedures
      7.2.4.1      Effect of the LANGUAGE Clause on the Parameter Data Type
      7.2.4.2      Effect of the LANGUAGE Clause on the Parameter-Passing Mechanism
    7.2.5      Specifying a CATALOG Clause
    7.2.6      Specifying a SCHEMA Clause
    7.2.7      Specifying an Authorization Identifier
    7.2.8      Specifying an ALIAS Clause
    7.2.9      Specifying DECLARE Statements in Modules
    7.2.10     Writing SQL Module Procedures
      7.2.10.1     Following Syntax Rules for Writing Procedures
      7.2.10.2     Naming a Procedure
      7.2.10.3     Declaring Procedure Parameters
      7.2.10.4     Parameters Required for Different Kinds of Procedures
      7.2.10.5     Associating Procedure and Call Parameters
      7.2.10.6     Specifying Parameter Data Types
      7.2.10.7     Overriding the Default Passing Mechanism for a Procedure Parameter
      7.2.10.8     Requesting a Run-Time Check of Parameters Used by the Calling Module
      7.2.10.9     Specifying an Executable SQL Statement in a Procedure

  7.3     Calling SQL Module Procedures from a Host Language Module

  7.4     Understanding the Restrictions of the SQL Module Language

  7.5     Processing SQL and Host Language Modules
    7.5.1      Using the SQL Module Processor
      7.5.1.1      Invoking the SQL Module Processor
      7.5.1.2      Improving SQL Module Processor Performance for Remote Databases

  7.6     A Sample Module File and Matching Host Program

  7.7     Referencing Other Sources for SQL Module Processor Information

  8      Creating and Processing Programs with the SQL Precompiler

  8.1     Locating Sample Files On Line

  8.2     Embedding SQL Statements in Programming Language Source Files

  8.3     Precompiling a Host Language File That Contains SQL Statements
    8.3.1      Overview of the Precompiler Process
    8.3.2      Specifying Files and Qualifiers with SQL Precompiler and Host Language Compilers
    8.3.3      Invoking the Precompiler
    8.3.4      Precompiling Ada Programs
    8.3.5      Logging Precompile- and Compile-Time Errors
    8.3.6      Improving Precompiler Performance for Remote Databases

  9      Creating an Image

  9.1     Linking Object Modules
    9.1.1      Creating an Executable Image
    9.1.2      Linking Ada Objects
    9.1.3      Creating a Shareable Image
      9.1.3.1      Placing the Object File for Transfer Vectors First in the Image
      9.1.3.2      Specifying the NOSHR and LCL Program Section Attributes for Global Names
      9.1.3.3      Installing Shareable Images

  9.2     Running and Debugging the Program

  10     Declaring and Using Parameters

  10.1    Understanding Terminology

  10.2    Understanding Parameter Function and Declaration Options

  10.3    Declaring the Data Types of Parameters

  10.4    Copying Parameter Declarations from a Source Outside Your Program
    10.4.1     Using the INCLUDE Statement of SQL
    10.4.2     Using the SQL Module Language FROM Clause
    10.4.3     Using Host Language COPY or INCLUDE Statements

  10.5    Declaring and Using Main Parameters
    10.5.1     Declaring Main Parameters
    10.5.2     Using Main Parameters

  10.6    Declaring and Using Indicator Parameters
    10.6.1     Declaring Indicator Parameters
    10.6.2     Using Indicator Parameters
    10.6.3     Using Indicator Parameters When Retrieving Values
    10.6.4     Using Indicator Parameters When Storing Values

  10.7    Avoiding Common Mistakes When Declaring and Using Parameters
    10.7.1     Avoiding Mistakes When Using Embedded SQL
    10.7.2     Avoiding Mistakes When Using SQL Modules

  11     Using Dynamic SQL

  11.1    Understanding Dynamic SQL

  11.2    Using Dynamic SQL

  11.3    Executing Non-SELECT Statements Without Parameter Markers

  11.4    Executing Statements That Contain Parameter Markers or Select List Items
    11.4.1     Executing INSERT, DELETE, and UPDATE Statements That Contain Parameter Markers
    11.4.2     Executing SELECT Statements Without Parameter Markers
    11.4.3     Executing SELECT Statements with Parameter Markers

  11.5    Executing Any Type of SQL Statement
    11.5.1     Accepting an SQL Statement from the Terminal
    11.5.2     Processing an SQL Statement from the Terminal
      11.5.2.1     Declaring SQLDA2 Structures
      11.5.2.2     Using SQLDA2 Structures to Test for Parameter Markers and SELECT Statements
      11.5.2.3     Handling Parameter Markers in the Statement String
      11.5.2.4     Processing SELECT Statements
      11.5.2.5     Executing Non-SELECT Statements

  11.6    Processing Dynamically Generated Statements
    11.6.1     Storing Statement Identifiers and Cursor Names
    11.6.2     Executing Multiple Non-SELECT Statements
    11.6.3     Executing Multiple SELECT Statements

  12     Managing Database and Transaction Contexts

  12.1    Introducing SQL Database Context

  12.2    Introducing SQL Transaction Context

  12.3    Using Two-Phase Commit Protocol

  12.4    Specifying and Attaching to a Database
    12.4.1     Specifying Data Dictionary or File Access for Database Attachment
    12.4.2     Specifying the Source of Database Definitions
    12.4.3     Specifying a Database on a Remote Node
    12.4.4     Using Aliases for Multiple Attaches

  12.5    Detaching from a Database

  12.6    Specifying and Starting a Transaction
    12.6.1     Specifying Transaction Characteristics
    12.6.2     Using DECLARE TRANSACTION and SET TRANSACTION Statements
    12.6.3     Using Read-Only Transactions
    12.6.4     Using Read/Write Transactions
    12.6.5     Using Batch-Update Transactions
    12.6.6     Choosing Whether to Wait for Locks to Be Released
      12.6.6.1     Setting the Length of the Wait Interval
      12.6.6.2     Encountering Lock-Conflict Errors for Read-Only Transactions
      12.6.6.3     Encountering Deadlock Errors
    12.6.7     Choosing a Consistency Level
    12.6.8     Locking Rdb/VMS Database Resources Appropriately
      12.6.8.1     Understanding Table and Row Locking
      12.6.8.2     Specifying Table and Row Locking
      12.6.8.3     Locking and Sorted Indexes

  12.7    Committing or Rolling Back a Transaction
    12.7.1     Understanding the Scope of a Transaction
    12.7.2     Ending a Transaction

  13     Handling Run-Time Errors

  13.1    Handling Errors Using SQL Options

  13.2    Monitoring Execution of SQL Statements
    13.2.1     Using SQLCODE to Monitor Statement Execution
    13.2.2     Using RDB$MESSAGE_VECTOR and RDB$LU_STATUS to Monitor Statement Execution
    13.2.3     Using WHENEVER to Monitor Statement Execution

  13.3    Displaying Error Messages
    13.3.1     Calling SQL$SIGNAL
      13.3.1.1     Calling SQL$SIGNAL from Ada Programs
      13.3.1.2     Calling SQL$SIGNAL from C Programs
      13.3.1.3     Calling SQL$SIGNAL from COBOL Programs
      13.3.1.4     Calling SQL$SIGNAL from FORTRAN Programs
      13.3.1.5     Calling SQL$SIGNAL from Pascal Programs
      13.3.1.6     Calling SQL$SIGNAL from PL/I Programs
    13.3.2     Calling SYS$PUTMSG
    13.3.3     Calling SQL$GET_ERROR_TEXT
    13.3.4     Displaying User-Supplied Error Messages

  13.4    Handling Multi-User Conflicts
    13.4.1     Handling Lock-Conflict Errors
    13.4.2     Handling Deadlock Errors

  13.5    Handling Duplicate Value Errors and Constraint Violations
    13.5.1     Status Values for Constraint Violations and Duplicate Value Errors
    13.5.2     Controlling Constraint Evaluation
      13.5.2.1     Rdb/VMS and SQL Constraint Evaluation Concepts
      13.5.2.2     Options for Controlling Constraint Evaluation Time
      13.5.2.3     Guidelines for Controlling Constraint Evaluation Time
    13.5.3     Managing the Requirements Imposed by Constraints and Indexes

  13.6    Managing Transactions So They Do Not Span Terminal I/O Operations

  13.7    Handling Errors Caused by Failure to Attach to a Database or Start a Transaction

  14     Using the Multiple Schema Option

  14.1    Introduction to Multischema Databases

  14.2    Naming Multischema Objects
    14.2.1     Qualifying Schema Object Names with an Alias
    14.2.2     Setting the Default Catalog and Schema

  14.3    Multischema Internal and External Names

  14.4    Enabling and Disabling the Multischema Environment
    14.4.1     Enabling an Rdb/VMS Database for Multischema Naming
    14.4.2     Disabling an Rdb/VMS Database for Multischema Naming

  14.5    Using Rdb/VMS System Relations with Multischema Databases

  14.6    Multischema Considerations with the SQL Module Processor