CONTENTS

  Title Page

  Copyright Page

  Preface

  Technical Changes and New Features

  1      Getting Started with SQL

  1.1     Using the Sample Personnel Databases

  1.2     Using Interactive SQL
    1.2.1      Defining the SQL$DATABASE Logical Name
    1.2.2      Displaying Information About the Sample Database
    1.2.3      Querying the Personnel Database
    1.2.4      Using SQL Command Procedures

  1.3     Setting Up Your Interactive SQL Environment

  1.4     Using SQL in Application Programs
    1.4.1      Using Dynamic SQL in Programs
    1.4.2      Finding Online Program Examples
    1.4.3      Using the SQL Interactive Kit

  1.5     Using Editors with SQL
    1.5.1      Using LSE with SQL

  1.6     Using the Rdb/VMS SQL Interface with Other Digital Products

  1.7     Using SQL Internationalization Options
    1.7.1      Controlling Input and Display Formats
    1.7.2      Specifying Collating Sequences

  2      Managing Schema and Transaction Contexts

  2.1     Specifying and Attaching to a Database
    2.1.1      Specifying Data Dictionary or File Access for Database Attachment
    2.1.2      Specifying the Source of Schema Definitions
    2.1.3      Specifying a Database on a Remote Node
    2.1.4      Using Authorization Identifiers and Attaching to More Than One Database

  2.2     Detaching from a Database

  2.3     Specifying and Starting a Transaction
    2.3.1      Specifying Transaction Characteristics
    2.3.2      Using DECLARE TRANSACTION and SET TRANSACTION Statements
    2.3.3      Using Read-Only Transactions
    2.3.4      Using Read/Write Transactions
    2.3.5      Using Batch-Update Transactions
    2.3.6      Choosing Whether to Wait for Locks to Be Released
      2.3.6.1      Setting the Length of the Wait Interval
      2.3.6.2      Lock-Conflict Errors Encountered by Read-Only Transactions
      2.3.6.3      Deadlock Errors
    2.3.7      Choosing a Consistency Level
    2.3.8      Locking Rdb/VMS Database Resources Appropriately
      2.3.8.1      Understanding Table and Row Locking
      2.3.8.2      Specifying Table and Row Locking
      2.3.8.3      Locking and Sorted Indexes

  2.4     Committing or Rolling Back a Transaction
    2.4.1      Understanding the Scope of a Transaction
    2.4.2      Ending a Transaction

  2.5     Considering Program Transportability When Managing Schema and Transaction Context
    2.5.1      Avoiding DECLARE SCHEMA Statements in Source Files
    2.5.2      Avoiding Complex Transaction Statements in Source Files
    2.5.3      Including Declarations in an SQL Context File

  3      Retrieving Data:  Result Tables and the SELECT Statement

  3.1     Understanding the Structure of SELECT Statements

  3.2     Selecting Columns

  3.3     Specifying Data Sources: Aliases and Column Name Qualification

  3.4     Retrieving All Rows in a Table or View

  3.5     Retrieving Limited Rows in a Table or View

  3.6     Retrieving Unique Rows: Eliminating Duplicate Sets of Values

  3.7     Retrieving Rows in Sorted Order

  3.8     Retrieving Rows That Satisfy Conditions
    3.8.1      Specifying Values in Conditions
    3.8.2      Using Conditional and Boolean Operators
    3.8.3      Selecting Data Based on a Matching Condition
      3.8.3.1      Retrieving Rows Where Values Partially Match Another Value
      3.8.3.2      Retrieving Rows Where Values Do Not Match Another Value
      3.8.3.3      Retrieving Rows Where Values Exactly Match One of Several Values
    3.8.4      Selecting Data Based on Range Retrieval
    3.8.5      Selecting Data Based on Nonexistent (Null) Values
    3.8.6      Selecting Data Based on Existence or Nonexistence of a Set of Rows
    3.8.7      Selecting Data Based on Negated, Combined, or Alternative Conditions
      3.8.7.1      Retrieving Rows That Do Not Satisfy a Condition
      3.8.7.2      Retrieving Rows That Satisfy Two or More Conditions
      3.8.7.3      Retrieving Rows That Satisfy Any One of Several Conditions
      3.8.7.4      Using Parentheses to Control Evaluation of Conditions

  3.9     Using Functions (Aggregate Expressions)

  3.10    Combining Tables

  3.11    Joining Tables

  3.12    Using Column Select Expressions as Value Expressions

  3.13    Ensuring Program Transportability When Retrieving Data

  4      Defining and Using Cursors

  4.1     Understanding the Different Categories of Cursors

  4.2     Understanding How Cursors Work

  4.3     Using Table Cursors

  4.4     Using List Cursors
    4.4.1      Using Cursors in Programs

  4.5     Using Dynamic Cursors

  4.6     Using Extended Dynamic Cursors

  4.7     Improving Program Transportability When Using Cursors

  5      Loading and Updating Data

  5.1     Loading a Database

  5.2     Inserting Rows

  5.3     Using List Cursors to Insert Large Data Structures

  5.4     Updating Rows

  5.5     Deleting Rows

  6      Developing Host Language Programs

  6.1     Including SQL Statements in Programs

  6.2     Developing Programs for SQL Applications
    6.2.1      Investigating Database Structure
    6.2.2      Testing SQL Statements Interactively
    6.2.3      Converting Interactive SQL Statements to SQL Statements in Programs

  6.3     Finding Information About Programming in Other Chapters

  7      Creating and Processing Programs with the SQL Module Processor

  7.1     Using the SQL Module Language

  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 an Authorization Identifier
    7.2.6      Including DECLARE Statements in Modules
    7.2.7      Writing SQL Module Procedures
      7.2.7.1      Naming a Procedure in an SQL Module
      7.2.7.2      Declaring Procedure Parameters
      7.2.7.3      Parameters Required for Different Kinds of Procedures
      7.2.7.4      Associating Procedure and Call Parameters
      7.2.7.5      Specifying Parameter Data Types
      7.2.7.6      Overriding the Default Passing Mechanism for a Procedure Parameter
      7.2.7.7      Requesting a Run-Time Check of Parameters Used by the Calling Module
      7.2.7.8      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     Improving Program Transportability When Using SQL Module Language

  8      Creating and Processing Programs with the SQL Precompiler

  8.1     Finding Information About Sample Programs

  8.2     Embedding SQL Statements in Programming Language Source Files
    8.2.1      Embedding SQL Statements in Ada Source Files
      8.2.1.1      Ending SQL Statements in Ada Source Files
      8.2.1.2      Limiting Length of File Names
      8.2.1.3      Embedding SQL Statements in Ada Files with Multiple Procedures
    8.2.2      Embedding SQL Statements in C Source Files
      8.2.2.1      Using Character Strings in C
      8.2.2.2      Ending SQL Statements in C Source Files
      8.2.2.3      Embedding SQL Statements in C Files with Multiple Functions
    8.2.3      Embedding SQL Statements in COBOL Source Files
      8.2.3.1      Ensuring That COBOL Source Files Are in the Correct Format
      8.2.3.2      Ending SQL Statements in COBOL
      8.2.3.3      Embedding SQL Statements in COBOL Files That Contain Multiple Programs
    8.2.4      Embedding SQL Statements in FORTRAN Source Files
      8.2.4.1      Ending SQL Statements in Precompiled FORTRAN Programs
      8.2.4.2      Limiting Number of Characters Per Line
      8.2.4.3      Limiting Number of Continuation Lines
      8.2.4.4      Limiting Length of FORTRAN Field Names Beginning with SQL
      8.2.4.5      Embedding SQL Statements in FORTRAN IF Statements
      8.2.4.6      Embedding SQL Statements in FORTRAN DO Loops
      8.2.4.7      Embedding SQL Statements in FORTRAN Files with Multiple END Statements
    8.2.5      Embedding SQL Statements in Pascal Source Files
      8.2.5.1      Ending SQL Statements in Pascal Source Files
      8.2.5.2      Embedding SQL Statements in Pascal Files with Multiple Procedures
      8.2.5.3      Modifying Pascal Source Files to Meet SQL Restrictions
    8.2.6      Embedding SQL Statements in PL/I Source Files
      8.2.6.1      Ending SQL Statements in Precompiled PL/I Programs
      8.2.6.2      Embedding SQL Statements in PL/I Files with Multiple Procedures

  8.3     Precompiling a Host Language File That Contains SQL Statements
    8.3.1      Files and Qualifiers Associated with SQL Precompiler and Host Language Compilers
    8.3.2      Invoking the Precompiler
    8.3.3      Precompiling Ada Programs
    8.3.4      Logging Precompile- and Compile-Time Errors
    8.3.5      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 Parameter Function and Declaration Options

  10.2    Declaring the Data Types of Parameters

  10.3    Copying Parameter Declarations from a Source Outside Your Program
    10.3.1     Using the INCLUDE Statement of SQL
    10.3.2     Using the SQL Module Language FROM Clause
    10.3.3     Using Host Language COPY or INCLUDE Statements

  10.4    Declaring and Using Main Parameters
    10.4.1     Declaring Main Parameters
    10.4.2     Using Main Parameters

  10.5    Declaring and Using Indicator Parameters
    10.5.1     Declaring Indicator Parameters
    10.5.2     Using Indicator Parameters
    10.5.3     Using Indicator Parameters When Retrieving Values
    10.5.4     Using Indicator Parameters When Storing Values

  10.6    Avoiding Common Mistakes When Declaring and Using Parameters
    10.6.1     Avoiding Mistakes When Using Embedded SQL
    10.6.2     Avoiding Mistakes When Using SQL Modules

  10.7    Declaring and Using Parameters in Ada Source Files

  10.8    Declaring and Using Parameters in C Source Files

  10.9    Declaring and Using Parameters in COBOL Source Files

  10.10  Declaring and Using Parameters in FORTRAN Source Files

  10.11  Declaring and Using Parameters in Pascal Source Files

  10.12  Declaring and Using Parameters in PL/I Source Files

  10.13  Declaring and Using Parameters in an SQL Module and Calling Program

  10.14  Considering Program Transportability When Declaring and Using Parameters

  11     Handling Run-Time Errors and the ROLLBACK Statement

  11.1    Handling Errors Using SQL Options

  11.2    Monitoring Execution of SQL Statements
    11.2.1     Using SQLCODE to Monitor Statement Execution
    11.2.2     Using the RDB$MESSAGE_VECTOR and RDB$LU_STATUS to Monitor Statement Execution
    11.2.3     Using WHENEVER to Monitor Statement Execution

  11.3    Displaying Error Messages
    11.3.1     Calling SQL$SIGNAL
      11.3.1.1     Calling SQL$SIGNAL from Ada Programs
      11.3.1.2     Calling SQL$SIGNAL from C Programs
      11.3.1.3     Calling SQL$SIGNAL from COBOL Programs
      11.3.1.4     Calling SQL$SIGNAL from FORTRAN Programs
      11.3.1.5     Calling SQL$SIGNAL from Pascal Programs
      11.3.1.6     Calling SQL$SIGNAL from PL/I Programs
    11.3.2     Calling SYS$PUTMSG
    11.3.3     Calling SQL$GET_ERROR_TEXT
    11.3.4     Displaying User-Supplied Error Messages

  11.4    Handling Multi-User Conflicts
    11.4.1     Handling Lock-Conflict Errors
    11.4.2     Handling Deadlock Errors

  11.5    Handling Duplicate Value Errors and Constraint Violations
    11.5.1     Status Values for Constraint Violations and Duplicate Value Errors
    11.5.2     Controlling When Constraints Are Evaluated During a Transaction
      11.5.2.1     Rdb/VMS and SQL Constraint Evaluation Concepts
      11.5.2.2     Options for Controlling Constraint Evaluation Time
      11.5.2.3     Guidelines for Controlling Constraint Evaluation Time
    11.5.3     Managing the Requirements Imposed by Constraints and Indexes

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

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

  11.8    Improving Program Transportability When Handling Errors and Constraints

  12     Using Dynamic SQL

  12.1    Understanding Dynamic SQL

  12.2    Using Dynamic SQL

  12.3    Executing Non-SELECT Statements Without Parameter Markers

  12.4    Executing Statements That Contain Parameter Markers or Select List Items
    12.4.1     Executing INSERT, DELETE, and UPDATE Statements That Contain Parameter Markers
    12.4.2     Executing SELECT Statements Without Parameter Markers
    12.4.3     Executing SELECT Statements with Parameter Markers

  12.5    Executing Any Type of SQL Statement
    12.5.1     Accepting the Statement from the Terminal
    12.5.2     Processing the Statement from the Terminal
      12.5.2.1     Declaring SQLDA Structures
      12.5.2.2     Using SQLDA Structures to Test for Parameter Markers and SELECT Statements
      12.5.2.3     Handling Parameter Markers in the Statement String
      12.5.2.4     Processing SELECT Statements
      12.5.2.5     Executing Non-SELECT Statements
      12.5.2.6     Releasing Prepared Statements

  12.6    Using a Single Set of Dynamic SQL Statements to Concurrently Process Any Number of Dynamically Generated Statements
    12.6.1     Storing Statement Identifiers and Cursor Names
    12.6.2     Executing Many Non-SELECT Statements
    12.6.3     Executing Many SELECT Statements

  EXAMPLES

  1-1        Displaying Information About the Sample Database

  1-2        Displaying a Specified Table

  1-3        Displaying Columns in a Table

  1-4        Displaying a List of Tables in a Database

  1-5        Querying the Sample Database

  1-6        Working with Data from More Than One Table

  1-7        Updating Data

  1-8        Using Cursors

  2-1        Using Authorization Identifiers

  2-2        Working with More Than One Database

  2-3        Combining Data from More Than One Database

  3-1        Selecting Columns

  3-2        Specifying Data Sources and Qualifying Names

  3-3        Selecting All Rows from a Table or View

  3-4        Limiting the Number of Rows in a Result Table

  3-5        Selecting Unique Rows

  3-6        Ordering Rows by Column Values

  3-7        Ordering Rows by Ordinal Position

  3-8        Specifying Values in Conditions

  3-9        Comparing a Value to Multiple Values

  3-10       Selecting Rows Based on a Range of Values

  3-11       Selecting Rows Based on Null Values

  3-12       Finding Out if a Set of Rows Exists

  3-13       Using Negated Conditions

  3-14       Listing Multiple Conditions for Data Selection

  3-15       Listing Alternative Conditions for Data Selection

  3-16       Using Parentheses to Control Condition Evaluation

  3-17       Using Functions to Provide Summary Information

  3-18       Combining Two Queries into One Table

  3-19       Using Joins Correctly

  3-20       Using SQL to Perform Percent Calculations

  4-1        Basic Cursor Operations

  4-2        Using Cursors to Format Row Displays

  4-3        Limiting the Number of Rows in a Cursor

  4-4        Using List Cursors to Read Lists

  4-5        Using a Cursor in a Precompiled COBOL Program

  4-6        Output from SQL$REPORT Program

  4-7        Using Dynamic Cursors

  4-8        Using Extended Dynamic Cursors

  5-1        Using the INSERT Statement in Interactive SQL

  5-2        Loading a Table from a Data File in a Precompiled COBOL Program

  5-3        Inserting Rows When Restructuring a Table

  5-4        Creating a Table and Inserting Rows from Another Table

  5-5        Using List Cursors to Insert Large Data Structures

  5-6        Updating Rows

  5-7        Updating Rows in a Precompiled COBOL Program

  5-8        Deleting Rows

  5-9        Deleting Rows Using a Cursor

  7-1        Inserting Data Using an SQL Module

  7-2        Using a COBOL Program to Call an SQL Module

  7-3        Compiling, Linking, and Running an SQL Module Application

  7-4        Parts of an SQL Module

  7-5        Writing SQL Modules to Accept the Schema Name at Run time

  7-6        Passing the Schema Name to an SQL Module

  10-1       Declaring and Using Parameters in a Precompiled Ada Program

  10-2       Declaring and Using Parameters in a Precompiled C Program

  10-3       Declaring and Using Parameters in a Precompiled COBOL Program

  10-4       Declaring and Using Parameters in a Precompiled FORTRAN Program

  10-5       Declaring and Using Parameters in a Precompiled Pascal Program

  10-6       Declaring and Using Parameters in a Precompiled PL/I Program

  10-7       Declaring and Using Parameters in an SQL Module

  10-8       Declaring and Using Parameters in a Calling Ada Program

  11-1       Evaluating RDB$LU_STATUS

  11-2       Using RDB$LU_STATUS to Monitor Statement Execution

  11-3       Updating a Row in a Multi-User Environment

  11-4       Updating a Table Containing Constraints

  12-1       Using the EXECUTE IMMEDIATE Statement in a Dynamic SQL Program

  12-2       Executing Non-SELECT Statements with Parameter Markers

  12-3       Writing SQL Modules That Execute SELECT Statements Without Parameter Markers

  12-4       Writing Host Language Programs That Execute SELECT Statements Without Parameter Markers

  12-5       Executing SELECT Statements That Contain Parameter Markers

  12-6       Declaring SQLDA Structures

  12-7       Testing the SQLDA.SQLD Field

  12-8       Handling Parameter Markers

  12-9       Allocating Storage

  12-10      Retrieving Rows from SELECT Statements

  12-11      Displaying Rows

  12-12      Executing Non-SELECT Statements

  12-13      Releasing Prepared Statements

  12-14      Storing Statement Identifiers and Cursor Names in Arrays

  12-15      Executing More Than One Non-SELECT Statement

  12-16      Executing More Than One SELECT Statement

  FIGURES

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

  2-1        Share Mode and Lock Type Options for Read/Write Transactions

  2-2        Examples of Control Options for Concurrent Users

  2-3        Chart of Database Access Conflicts

  2-4        Transaction Scope with a SET TRANSACTION Statement

  2-5        Transaction Scope with a DECLARE TRANSACTION Statement

  2-6        Transaction Scope with SET TRANSACTION and DECLARE TRANSACTION Statements

  2-7        Transaction Recovery-Unit Journal File During an Update Transaction

  7-1        Correspondence Between Call Parameters, Procedure Parameters, and Columns

  TABLES

  1-1        Files to Create Sample Databases

  3-1        Conditional and Boolean Operators in Predicates

  8-1        Language Identifiers and Default File Types Used in Precompiling and Compiling Programs

  8-2        Files Related to Precompiling Ada Source Modules

  12-1       SQL Statements That Can Be Dynamically Executed