|
DEC Rdb Guide to SQL Programming
*HyperReader |
|
|
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
|
|