ALLBASE/SQL FORTRAN Application Programming Guide: HP 9000 Computer Systems

HP Part Number: 36217-90100

Fourth Edition

Printed in: U.S.A. 1992

Notice

Table of Contents

Preface

1 Getting Started with ALLBASE/SQL Programming in FORTRAN

ALLBASE/SQL FORTRAN Programs

Program Structure
DBEnvironment Access
Authorization
File Referencing
Use of Shared Memory
Choosing an Interprocess Communication Option

Native Language Support

The ALLBASE/SQL FORTRAN Preprocessor

Effect of Preprocessing on Source Code
Effect of Preprocessing on DBEnvironments

The Stored Section

Purpose of Sections
Section Validity

The Compiler and the Linker

The Executable Program

Installing the Program Module
Granting Required Owner Authorization
Granting Program User Authorization
Running the Program

Maintaining ALLBASE/SQL Programs

Updating Application Programs
Changing Program-Related Authorization
Obsoleting Programs

2 Using The ALLBASE/SQL FORTRAN Preprocessor

The Preprocessor and Program Development

FORTRAN Preprocessor Compiler Directives

Preprocessor Modes

Preprocessor Input and Output

Preprocessor Source File
Output File Attributes
Preprocessor Modified Source File
Variable Declaration Include File
ALLBASE/SQL Message File
Installable Module File
Stored Sections

Invoking the Fortran Preprocessor

Syntax Checking Mode
Full Preprocessing Mode

Running the Preprocessor in the Background

Preprocessing Errors

Preprocessor or DBEnvironment Termination
Preprocessor Invocation Errors
Source File Errors
DBEnvironment Errors

3 Embedding SQL Commands

General Rules for Embedding SQL

Location of SQL Commands
Prefix
FORTRAN Comments
SQL Comments
Continuation Lines

Declaring the SQLCA

Declaring Host Variables

Starting a DBE Session

Defining Transactions

Implicit Status Checking

Terminating a DBE Session

Defining and Manipulating Data

Data Definition
Data Manipulation

Explicit Status Checking

Obtaining ALLBASE/SQL Messages

4 Host Variables

Using Host Variables

Host Variable Names
Input and Output Host Variables
Data Values and Null Indicators

Declaring Host Variables

Declaring Variables for Data Types
Variable Declarations
Using Default Data Values
ALLBASE/SQL Program Elements
Data Type Compatibility
Declaring Host Variables for Data Values and Indicator Variables
Declaring Host Variables for Dynamic Commands
Declaring Host Variables for Savepoint Numbers
Declaring Host Variables for Message Catalog Messages
Declaring Host Variables Passed from Subprograms
Declaring Host Variables for HP-UX File Values
Declaring Host Variables for DBEnvironment Names

5 Runtime Status Checking and the SQLCA

The Importance of Status Checking

Handling Runtime Errors and Warnings
Maintaining Data Consistency
Determining Number of Rows Processed

The SQLCA COMMON Block

SQLCA.SQLCODE
SQLCA.SQLERRD(3)
SQLCA.SQLWARN(0)
SQLWARN(1)
SQLWARN(2)
SQLWARN(6)

Approaches to Status Checking

Implicit Status Checking
Explicit Status Checking

6 Overview of Data Manipulation

The Query

The SELECT Command
Selecting from Multiple Tables
Selecting Using Views
Query Efficiency

Simple Data Manipulation

Introducing the Cursor

Sequential Table Processing

Dynamic Operations

7 Simple Data Manipulation

Simple Data Manipulation Commands

The SELECT Command
The INSERT Command
The DELETE Command
The UPDATE Command

Transaction Management for Simple Operations

Program Using SELECT, UPDATE, DELETE, and INSERT

Select Function
Update Function
Delete Function
Insert Function

8 Processing with Cursors

Sequential Table Processing Commands

The DECLARE CURSOR Command
The OPEN Command
The FETCH Command
The UPDATE WHERE CURRENT Command
The DELETE WHERE CURRENT Command
The CLOSE Command

Transaction Management for Cursor Operations

Using KEEP CURSOR
OPEN Command Using KEEP CURSOR WITH LOCKS
OPEN Command Using KEEP CURSOR WITH NOLOCKS
KEEP CURSOR and Isolation Levels
KEEP CURSOR and BEGIN WORK
KEEP CURSOR and COMMIT WORK
KEEP CURSOR and ROLLBACK WORK
KEEP CURSOR and Aborted Transactions
Writing KEEP CURSOR Applications

Program Using UPDATE WHERE CURRENT

FetchUpdate Function
DisplayUpdate Subroutine

9 Using Dynamic Operations

Review of Preprocessing Events

Differences between Dynamic and Non-Dynamic Preprocessing

Permanently Stored vs. Temporary Sections
Examples of Non-Dynamic and Dynamic SQL Statements
Why Use Dynamic Preprocessing?

Passing Dynamic Commands to ALLBASE/SQL

Understanding Dynamic Operations

Preprocessing of Dynamic Non-Queries

Using EXECUTE IMMEDIATE
Using PREPARE and EXECUTE

Programs Using Dynamic Operations

Sample Program Using EXECUTE IMMEDIATE

Sample Program Using PREPARE and EXECUTE

10 Programming with Constraints

Comparing Statement Level and Row Level Integrity

Using Unique and Referential Integrity Constraints

Designing an Application Using Statement Level Integrity Checks

Insert a Member in the Recreation Database
Update an Event in the Recreation Database
Delete a Club in the Recreation Database
Delete an Event in the Recreation Database

11 Programming with LONG Columns

General Concepts

Restrictions

Defining LONG Columns with a CREATE TABLE or ALTER TABLE Command

Defining Input and Output with the LONG Column I/O String

Putting Data into a LONG Column with an INSERT Command

Insert Using Host Variables for LONG Column I/O Strings

Retrieving LONG Column Data with a SELECT, FETCH, or REFETCH Command

Using the LONG Column Descriptor
Using LONG Columns with a SELECT Command
Using LONG Columns with a Dynamic FETCH Command

Changing a LONG Column with an UPDATE [WHERE CURRENT] Command

Removing LONG Column Data with a DELETE [WHERE CURRENT] Command

Coding Considerations

12 Programming with ALLBASE/SQL Functions

Programming with Date/Time Functions

Where Date/Time Functions Can Be Used
Defining and Using Host Variables with Date/Time Functions
Using Date/Time Input Functions
Using Date/Time Output Functions
Using the Date/Time ADD_MONTHS Function

Programming with TID Data Access

Understanding TID Function Input and Output

Transaction Management with TID Access

Comparing TID Access to Other Types of Data Access
Verifying Data that is Accessed by TID
Considering Interactive User Applications

Index

List of Figures

1-1 Creating an ALLBASE/SQL FORTRAN Application Program
1-2 Use of DBCore Shared Memory in a DBEnvironment
1-3 Preprocess-Time Events
1-4 Compile-Time and Link-Time Events
1-5 Runtime Events
2-1 Developing a FORTRAN Program
2-2 Developing a FORTRAN Program with Subprograms
2-3 FORTRAN Preprocessor Input and Output
2-4 FORTRAN Compiler Input
2-5 Runtime Dialog of Program forex2
2-6 Program forex2
2-7 Modified Source File for Program forex2
2-8 Sample Variable Declaration Include File
2-9 Sample sqlmsg Showing Errors
2-10 Sample sqlmsg Showing Warning
2-11 Information in SYSTEM.SECTION on Stored Sections
3-1 Sample Source File
4-1 Host Variable Declarations
4-2 Declaring Host Variables for Single-Row Query Result
4-3 Declaring Host Variables for Multiple-Row Query Result
4-4 Declaring Host Variables for Dynamic Commands
4-5 Declaring Host Variables for Savepoint Numbers
4-6 Declaring Host Variables for Message Catalog Messages
4-7 Declaring Host Variables Passed From Subprograms
4-8 Declaring Host Variables for HP-UX File Values
4-9 Declaring Host Variables for DBEnvironment Names
5-1 Program forex5: Implicit and Explicit Status Checking
5-2 Explicitly Invoking Status-Checking Subprogram Units
6-1 Sample Query Joining Multiple Tables
6-2 Effect of SQL Commands on Cursor and Active Sets
7-1 Flow Chart of Program forex7
7-2 Flow Chart of Program forex7 (page 2 of 2)
7-3 Runtime Dialog of Program forex7
7-4 Program forex7: Using INSERT, UPDATE, SELECT, and DELETE
8-1 Cursor Operation without the KEEP CURSOR Feature
8-2 Cursor Operation Using KEEP CURSOR WITH LOCKS
8-3 Cursor Operation Using KEEP CURSOR WITH NOLOCKS
8-4 Flow Chart of Program forex8
8-5 Runtime Dialog of Program forex8
8-6 Program forex8: Using UPDATE WHERE CURRENT
9-1 Creation and Use of a Program that has a Stored Module
9-2 Creation and Use of a Program that has No Stored Module
9-3 Procedure Hosting Dynamic Non-Query Commands
9-4 Runtime Dialog for Program forex9a
9-5 Program forex9a: Sample Program Using EXECUTE IMMEDIATE
9-6 Runtime Dialog of Program forex9b
9-7 Program forex9b: Sample Program Using PREPARE and EXECUTE
10-1 Constraints Enforced on the Recreation Database
11-1 Flow of LONG Column Data and Related Information to the Database
11-2 Flow of LONG Column Data and Related Information from the Database

List of Tables

4-1 Data Description Entries for Host Variables
4-2 ALLBASE/SQL Floating Point Column Specifications
4-3 Program Element Declarations
4-4 Data Type Equivalency and Compatibility
6-1 How Data Manipulation Commands May Be Used
10-1 Commands Used with Integrity Constraints
10-2 Constraint Test Matrix
11-1 Commands You Can Use with LONG Columns
11-2 LONG Column Descriptor
12-1 Where to Use Date/Time Functions
12-2 Host Variable Data Type Compatibility for Date/Time Functions
12-3 Sample of User Requested Formats for Date/Time Data
12-4 SQLTID Data Internal Format

ALLBASE/SQL FORTRAN Application Programming Guide: HP 9000 Computer Systems

Technical documentation

» Table of Contents

» Index