CONTENTS

  Title Page

  Copyright Page

  Send Us Your Comments

  Preface

  Technical Changes and New Features

  1      Introduction to SQL Programming
    1.1 What Are the Two SQL Programming Interfaces?
      1.1.1      SQL Module Processor
      1.1.2      SQL Precompiler
    1.2 Choosing a Programming Interface
    1.3 Finding Online Program Examples

  2      SQL Program Development Cycle
    2.1 Overview of the Application Program Development Cycle
    2.2 Understanding End-User Requirements
    2.3 Investigating Metadata and Data
    2.4 Developing a Prototype
    2.5 Converting the Prototype to an Application Program
    2.6 Developing an Application Program

  3      Introduction to SQL Module Language
    3.1 Overview of SQL Module Language
    3.2 Developing Your SQL Module Language Application Program:  Basic Steps
    3.3 Creating an SQL Module Source File
      3.3.1      Including Blank Lines and Comments in an SQL Module
      3.3.2      Naming a Module
      3.3.3      Specifying the Dialect
      3.3.4      Specifying Character Sets for a Session
      3.3.5      Identifying the Host Language That Calls Module Procedures
      3.3.6      Specifying the Catalog
      3.3.7      Specifying the Schema
      3.3.8      Specifying an Authorization Identifier
      3.3.9      Specifying the Alias
      3.3.10     Specifying That Parameters Must Include Colons
      3.3.11     Specifying DECLARE Statements in Modules
    3.4 Calling SQL Module Procedures from a Host Language Program
    3.5 Program Portability with the SQL Module Processor
    3.6 Finding More Information About the SQL Module Processor

  4      Writing Module SQL Procedures
    4.1 Introducing SQL Module Procedures
    4.2 Specifying the Common Elements of SQL Module Procedures
      4.2.1      Naming a Procedure
      4.2.2      Declaring Procedure Parameters
      4.2.3      Parameters Required for Different Kinds of Procedures
      4.2.4      Associating Procedure Parameters and Actual Parameters
      4.2.5      Specifying Parameter Data Types
      4.2.6      Effect of the LANGUAGE Clause on the Parameter Data Type
      4.2.7      Effect of the LANGUAGE Clause on the Parameter-Passing Mechanism
      4.2.8      Overriding the Default Passing Mechanism for a Procedure Parameter
      4.2.9      Requesting a Run-Time Check of Parameters Used by the Calling Module
      4.2.10     Bypassing Parameter Checking for Faster Compilations
    4.3 Using Single SQL Statements in Procedures
    4.4 Using Compound Statements in Multistatement Procedures

  5      Processing SQL Modules and Host Language Files
    5.1 Processing SQL and Host Language Modules
      5.1.1      Invoking the SQL Module Processor
    5.2 Improving SQL Module Processor Performance for Remote Databases
    5.3 Using Context Files with SQL Module Language
    5.4 Deciding on the Scope of an SQL Module
    5.5 Understanding the Restrictions of the SQL Module Language

  6      Using Precompiled SQL
    6.1 Overview of the Precompiler Process
    6.2 Embedding SQL Statements in Host Programs
    6.3 Precompiling a Host Language File That Contains SQL Statements
      6.3.1      Invoking the Precompiler
      6.3.2      Finding Precompile-Time and Compile-Time Errors
      6.3.3      Improving Precompiler Performance for Remote Databases
    6.4 Specifying Compile-Time and Run-Time Options
      6.4.1      Using the DECLARE MODULE Statement
      6.4.2      Including Declarations in an SQL Context File
    6.5 Language-Specific Guidelines for Using the SQL Precompiler
      6.5.1      Embedding SQL Statements in Ada Source Files
      6.5.2      Precompiling Ada Programs
      6.5.3      Embedding SQL Statements in C Source Files
      6.5.4      Embedding SQL Statements in COBOL Source Files
      6.5.5      Embedding SQL Statements in FORTRAN Source Files
      6.5.6      Embedding SQL Statements in Pascal Source Files
      6.5.7      Embedding SQL Statements in PL/I Source Files
    6.6 Locating Sample Files on Line

  7      Creating Images for Program Execution
    7.1 Creating Executable Images
      7.1.1      Using the Linker
      7.1.2      Creating an Executable Image That Links with a Shareable Image
    7.2 Creating a Shareable Image
      7.2.1      Executable and Shareable Images Not Sharing Database Attaches
      7.2.2      Executable and Shareable Images Sharing Database Attaches
    7.3 Installing Shareable Images
    7.4 Running a Program
    7.5 Debugging SQL Statements and Program Code
    7.6 Linking Ada Objects

  8      Declaring and Using Parameters
    8.1 Overview of Declaring and Using Parameters
    8.2 Understanding Terminology
    8.3 Understanding Parameter Function and Declaration Options
    8.4 Declaring the Data Types of Parameters
    8.5 Copying Parameter Declarations from a Source Outside Your Program
      8.5.1      Using the SQL INCLUDE Statement
      8.5.2      Using the SQL Module Language FROM path-name Clause
      8.5.3      Using Host Language COPY or INCLUDE Statements
    8.6 Declaring and Using Main Parameters
      8.6.1      Declaring Main Parameters
      8.6.2      Using Main Parameters
    8.7 Declaring and Using Indicator Parameters
      8.7.1      Declaring Indicator Parameters
      8.7.2      Using Indicator Parameters
      8.7.3      Using Indicator Parameters When Retrieving Values
      8.7.4      Using Indicator Parameters When Storing Values
    8.8 Avoiding Common Mistakes When Declaring and Using Parameters
      8.8.1      Avoiding Mistakes When Using Embedded SQL
      8.8.2      Avoiding Mistakes When Using SQL Modules
    8.9 Declaring and Using Parameters in Source Files
      8.9.1      Declaring and Using Parameters in Ada Source Files
      8.9.2      Declaring and Using Parameters in C Source Files
      8.9.3      Declaring and Using Parameters in COBOL Source Files
      8.9.4      Declaring and Using Parameters in FORTRAN Source Files
      8.9.5      Declaring and Using Parameters in Pascal Source Files
      8.9.6      Declaring and Using Parameters in PL/I Source Files
      8.9.7      Declaring and Using Parameters in an SQL Module and Calling Program

  9      Using Date-Time Data Types
    9.1 Understanding the Structure of Date-Time Examples
    9.2 Using Date-Time Data Types in Data Definition Operations
      9.2.1      Creating Tables and Domains That Include Date-Time Data Types
      9.2.2      Altering Columns That Include Date-Time Data Types
      9.2.3      Storing Date-Time Data Type Data
    9.3 Using Date-Time Data Types in Programs
      9.3.1      Date-Time Considerations Using the SQL Precompiler
      9.3.2      Converting Date-Time Data Types for Program Development
      9.3.3      Date-Time Considerations Using the SQL Module Language
    9.4 Planning Program Portability
    9.5 Converting Applications and Databases
    9.6 Using Date-Time Data Types with Dynamic SQL
    9.7 Using Date-Time Data Types in Platform-Specific Contexts
      9.7.1      Using Date-Time Data Types with Layered Products
      9.7.2      Using Date-Time Data Types with Applications Specific to OpenVMS
    9.8 Using the DATE VMS 16-Character Format

  10     Handling Run-Time Errors
    10.1 Overview of SQL Error Handling
    10.2 Monitoring Execution of SQL Statements for Errors
      10.2.1     Using SQLSTATE to Monitor Statement Execution
      10.2.2     Using SQLCODE to Monitor Statement Execution
      10.2.3     Using RDB$MESSAGE_VECTOR and RDB$LU_STATUS to Monitor Statement Execution
      10.2.4     Using WHENEVER to Monitor Statement Execution
      10.2.5     Using the SQL Error-Handling Routines
        10.2.5.1     How to Call the SQL Error-Handling Routines
        10.2.5.2     How to Declare User-Written Error-Handling Routines
    10.3 Displaying Error Messages
      10.3.1     Calling SQL$SIGNAL
      10.3.2     Calling SYS$PUTMSG
      10.3.3     Calling SQL$GET_ERROR_TEXT
      10.3.4     Displaying User-Supplied Error Messages
    10.4 Handling Duplicate Value Errors and Constraint Violations
      10.4.1     Status Values for Constraint Violations and Duplicate Value Errors
      10.4.2     Controlling Constraint Evaluation
    10.5 Handling Deadlocks and Lock Conflicts
      10.5.1     Handling Lock-Conflict Errors
      10.5.2     Handling Deadlock Errors
    10.6 Handling Errors Caused by Failure to Attach to a Database or Start a Transaction
    10.7 Improving Program Portability When Handling Errors and Constraints

  11     Using Dynamic SQL
    11.1 Introducing Dynamic SQL
      11.1.1     SQL Statements That Process Other SQL Statements
      11.1.2     Categories of Statements That Can Be Dynamically Executed
      11.1.3     Steps in Processing SQL Statements in Dynamic SQL
    11.2 Executing Non-SELECT Statements Without Parameter Markers
    11.3 Handling Parameter Markers and Select List Items
      11.3.1     Using the SQLDA and SQLDA2 Structures
      11.3.2     Declaring SQLDA and SQLDA2 Structures
    11.4 Executing Non-SELECT Statements with Parameter Markers
    11.5 Processing SELECT Statements
      11.5.1     Executing SELECT Statements Without Parameter Markers:  Declaring Dynamic and Extended Dynamic Cursors
      11.5.2     Executing SELECT Statements That Contain Parameter Markers
      11.5.3     Using SQLDA2 and SQLERRD Structures to Test for Parameter Markers and SELECT Statements
    11.6 Processing Sets of 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
    11.7 Finding the Sample Programs Used in This Chapter

  12     Using Compound Statements in SQL
    12.1 Introducing Compound Statements
    12.2 Using Compound Statements to Increase Performance
    12.3 Writing a Compound Statement
      12.3.1     Declaring and Assigning Variables
      12.3.2     Using the IF Statement
      12.3.3     Using the CASE Statement
      12.3.4     Using the LOOP Statement
      12.3.5     Using the FOR Statement
      12.3.6     Using Labels in Compound Statements
      12.3.7     Using the LEAVE Statement
    12.4 Controlling the Atomicity of Compound Statements
    12.5 Controlling Transactions in Compound Statements
    12.6 Processing Compound Statements Dynamically
    12.7 Debugging Compound Statements
    12.8 Handling Exception and Completion Conditions

  13     Using Stored Procedures
    13.1 What Is a Stored Procedure?
    13.2 Benefits of Storing Procedures in a Database
    13.3 Creating Stored Procedures (CREATE MODULE)
    13.4 Invoking Stored Procedures (CALL)
    13.5 Deleting Stored Procedures (DROP MODULE)
    13.6 Privileges Required to Use Stored Procedures
    13.7 Tracking Stored Procedure Dependencies
      13.7.1     Procedure Dependency Type
      13.7.2     Language Semantic Dependency Type
      13.7.3     Transaction Dependency Types
    13.8 Invalidation of Procedures
    13.9 Revalidating Stored Procedures
      13.9.1     Revalidating Invalidated Stored Procedures
      13.9.2     Re-Creating Invalidated Stored Procedures with Language Semantic Dependencies

  14     Using External Functions
    14.1 Introduction to External Functions
    14.2 Creating External Functions
      14.2.1     Creating an External Function Based on a Predefined OpenVMS Routine
      14.2.2     Writing a Jacket Routine to Invoke an External Function
      14.2.3     Writing a User-Defined External Function
      14.2.4     Illustration of a User-Defined External Function
    14.3 Defining and Deleting External Functions
      14.3.1     Defining an External Function Definition
      14.3.2     Deleting an External Function Definition
    14.4 Creating Shareable Images for External Functions
      14.4.1     Creating Shareable Images for External Functions on an OpenVMS VAX System
      14.4.2     Creating Shareable Images for External Functions on an OpenVMS AXP System
    14.5 Data Types, Parameter Mechanisms, Return Mechanisms, and Parameter Modes Used with External Functions
    14.6 Language-Specific Guidelines for Coding External Functions
      14.6.1     Mapping SQL Data Types to External Function Language Data Types
      14.6.2     Ada Usage Notes
      14.6.3     C Usage Notes
      14.6.4     COBOL Usage Notes
      14.6.5     FORTRAN Usage Notes
      14.6.6     Pascal Usage Notes
    14.7 Securing External Functions
    14.8 Invoking External Functions
      14.8.1     Invoking the SOUNDEX External Function in a Variety of SQL Statements
      14.8.2     Invoking an External Function Within a Trigger
    14.9 External Function Exception Handling
    14.10 External Function Restrictions
    14.11 Additional Notes About External Functions

  15     Managing Database Context
    15.1 Specifying and Attaching to a Database
      15.1.1     Specifying Repository or File Access for Database Attachment
      15.1.2     Ways to Specify the Database Name
      15.1.3     Specifying Different Databases for Compile Time and Run Time
      15.1.4     Specifying a Database on a Remote Node
        15.1.4.1     Using a Proxy Account for Remote Access
        15.1.4.2     Using a Logical Name for Remote Access
        15.1.4.3     Using the RDB$REMOTE Default Account for Remote Access
      15.1.5     Avoiding Undetected Deadlock with Distributed Transactions
      15.1.6     Restrictions on Distributed Transactions Related to the DISTRIBTRAN Security Privilege
      15.1.7     Using Aliases for Multiple Attaches
    15.2 Detaching from a Database

  16     Managing Transaction Context
    16.1 Transaction Overview
    16.2 Designing Transactions
      16.2.1     Understanding the Scope of a Transaction
      16.2.2     Distributed Transactions
      16.2.3     Locking
        16.2.3.1     Locking Strategies
        16.2.3.2     Intent Locks
        16.2.3.3     Lock Conflicts
        16.2.3.4     Read-Only Transactions and the Snapshot File
        16.2.3.5     Encountering Lock-Conflict Errors for Read-Only Transactions
        16.2.3.6     Improving Concurrent Access
      16.2.4     Designing Transactions So They Do Not Span Terminal I/O Operations
    16.3 Specifying Transaction Characteristics in Your Program
      16.3.1     Using Read-Only Transactions
      16.3.2     Using Read/Write Transactions
      16.3.3     Using Batch-Update Transactions
      16.3.4     Default Transaction Characteristics
      16.3.5     Using Aliases to Access More Than One Database in a Single Transaction
      16.3.6     Using the RESERVING Clause
      16.3.7     Choosing Whether to Wait for Locks to Be Obtained
      16.3.8     Choosing an Isolation Level
        16.3.8.1     An Example of a Repeatable Read Transaction
        16.3.8.2     An Example of a Read Committed Transaction
      16.3.9     Benefits of Using Various Isolation Levels
        16.3.9.1     Running Reporting Applications at Reduced Isolation Levels
        16.3.9.2     Running Update Applications at Reduced Isolation Levels
        16.3.9.3     Isolation Level Restrictions
        16.3.9.4     Using Isolation Levels with Databases Other Than DEC Rdb and DEC Rdb Databases Before DEC Rdb V4.2
      16.3.10    Deciding When to Evaluate Constraints
        16.3.10.1    Specifying Constraint Evaluation Time
        16.3.10.2    Recommendations for When to Evaluate Constraints
    16.4 Committing or Rolling Back a Transaction

  17     Using Cursors
    17.1 Introduction to Cursors
      17.1.1     How Cursors Work
      17.1.2     Comparing Cursors and Views
      17.1.3     Understanding the Different Categories of Cursors
    17.2 Using Table Cursors
      17.2.1     Cursor Modes
      17.2.2     Deciding When a Cursor Is Needed
    17.3 Using List Cursors
    17.4 Using Scrollable List Cursors
    17.5 Using Dynamic Cursors
    17.6 Using Extended Dynamic Cursors

  18     Inserting, Updating, and Deleting Data
    18.1 Loading a Database
    18.2 Inserting Rows
      18.2.1     Using Indicator Parameters with the INSERT Statement
      18.2.2     Using the INSERT . . . SELECT Statement
    18.3 Using List Cursors to Insert Large Data Structures
    18.4 Updating Rows
      18.4.1     Selecting Data in the UPDATE Statement
      18.4.2     Using the UPDATE Statement with a Cursor
      18.4.3     Using the UPDATE . . . RETURNING Statement
    18.5 Deleting Rows
    18.6 Using Triggers with Insert, Update, and Delete Operations

  19     Managing Multiple Connections in Programs
    19.1 Introducing Connections
      19.1.1     Defining a Session
      19.1.2     Defining a Database Environment
      19.1.3     Defining a Connection
    19.2 Creating, Switching Between, and Ending Connections
      19.2.1     Creating Connections
      19.2.2     Duplicating the Default Database Environment
      19.2.3     Specifying Different Databases for the Same Aliases
      19.2.4     Specifying an Additional Run-Time Attach
      19.2.5     Switching Between Connections
      19.2.6     Ending Connections
    19.3 Using Transactions with Connections
    19.4 Enabling and Disabling Connections in Programs
      19.4.1     Enabling and Disabling Connections for Module Programming
      19.4.2     Enabling and Disabling Connections for Precompiled Programs
    19.5 Using Connections in an Application

  20     Using the Multiple Schema Option
    20.1 Overview of Multiple Schema Options
    20.2 Multischema Considerations with the SQL Module Processor
      20.2.1     Default Settings for SQL Module Files
      20.2.2     Using Multischema Naming in an SQL Module File and C Program
    20.3 Multischema Considerations with the SQL Precompiler
      20.3.1     Default Settings for the SQL Precompiler
      20.3.2     Using Multischema Naming in a Precompiled Program

  A Using SQL International Options
    A.1 Controlling Input and Display Formats
    A.2 Specifying Collating Sequences
    A.3 Using Collating Sequences
    A.4 Collating Order for DEC Rdb Character Sets