HP 3000 Manuals HP 3000 Manuals
Table of Contents
ALLBASE/SQL COBOL Application Programming Guide
ALLBASE/SQL COBOL Application Programming Guide : COPYRIGHT NOTICE
Ch 1. Getting Started with ALLBASE/SQL Programming in COBOL
ALLBASE/SQL COBOL Programs
Program Structure
DBEnvironment Access
Authorization
File Referencing
Native Language Support
The ALLBASE/SQL COBOL 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
ALLBASE/SQL Program Execution
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
Ch 2. Using the ALLBASE/SQL COBOL Preprocessor
The Preprocessor and Application Development
Preprocessor Modes
Preprocessor Input and Output
Source File
Output File Attributes
Modified Source File
Preprocessor Input and Output (cont.)
Preprocessor Generated Include Files
COBOL COPY Statement Support
Using the COPY Statement with ALLBASE/SQL
COPY Statement Code Example
$SET and $IF Statement Support
Code Example
Considerations When Using $SET and $IF
ALLBASE/SQL Message File
Installable Module File
Stored Sections
Invoking the COBOL Preprocessor
Syntax Checking Mode
Description
Authorization
Example
Full Preprocessing Mode
Parameters
Description
Authorization
Example
Using the Preprocessor UDC's
Running the Preprocessor in Job Mode
Preprocessing Errors
Preprocessor or DBEnvironment Termination
Preprocessor Invocation Errors
SQLIN Errors
DBEnvironment Errors
Ch 3. Embedding SQL Commands
General Rules for Embedding SQL
Location of SQL Commands
Prefix and Suffix
Punctuation
COBOL Comments
ALLBASE/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
Ch 4. Host Variables
Using Host Variables
Host Variable Names
Input and Output Host Variables
Indicator Variables
Bulk Processing Variables
Declaring Host Variables
Creating Declaration Sections
Declaring Variables for Data Types
CHAR Data
VARCHAR Data
SMALLINT Data
INTEGER Data
FLOAT Data
ALLBASE/SQL FLOAT Data
Floating Point Data Compatibility
COBOL DECIMAL Data
BINARY Data
Binary Data Compatibility
Using the LONG Phrase with Binary Data Types
DATE, TIME, DATETIME, and INTERVAL Data
Odd-Byte Columns
Using Default Data Values
Coding Considerations
When the DEFAULT Clause Cannot be Used
Declaring Variables for Compatibility
String Data Conversion
String Data Truncation
Numeric Data Conversion
Declaring Variables for Program Elements
SQLCA Array
Bulk Processing Arrays
Indicator Variables
Dynamic Commands
Savepoint Numbers
Messages from the Message Catalog
DBEnvironment Name
Declaring Host Variables Passed Between Subprograms
Ch 5. Runtime Status Checking and the SQLCA
Purposes of Status Checking
Handling Runtime Errors and Warnings
Maintaining Data Consistency
Checking the Most Recently Executed Command
Using the SQLCA
SQLCODE
SQLERRD(3)
SQLWARN0
SQLWARN1
SQLWARN2
SQLWARN6
Approaches to Status Checking
Implicit Status Checking Techniques
Implicitly Invoking Status-Checking Procedures
Code the Preprocessor Generates
Explicit Status Checking Techniques
Handling Deadlock and Shared Memory Problems
Determining Number of Rows Processed
INSERT, UPDATE, and DELETE Operations
BULK Operations
Detecting End of Scan
Determining When More Than One Row Qualifies
Detecting Log Full Condition
Handling Out of Space Conditions
Checking for Authorizations
Ch 6. Overview Of Data Manipulation
The Query
The SELECT Command
Selecting from Multiple Tables
Selecting Using Views
Simple Data Manipulation
Introducing The Cursor
Sequential Table Processing
BULK Table Processing
Dynamic Operations
Ch 7. Simple Data Manipulation
SQL Commands
SELECT
INSERT
UPDATE
DELETE
Transaction Management
Sample Program COBEX7 Using Simple DML Commands
Ch 8. Processing with Cursors
SQL Cursor Commands
DECLARE CURSOR
OPEN
FETCH
UPDATE WHERE CURRENT
DELETE WHERE CURRENT
CLOSE
Transaction Management for Cursor Operations
Using KEEP CURSOR
KEEP CURSOR and Isolation Levels
KEEP CURSOR and Declaring for Update
OPEN Command Without KEEP CURSOR
OPEN Command Using KEEP CURSOR WITH LOCKS and CS Isolation Level
OPEN Command Using KEEP CURSOR WITH NOLOCKS
KEEP CURSOR and BEGIN WORK
KEEP CURSOR and COMMIT WORK
KEEP CURSOR and ROLLBACK WORK
KEEP CURSOR and Aborted Transactions
Writing Keep Cursor Applications
Examples
Common StatusCheck Procedure
Single Cursor WITH LOCKS
Multiple Cursors and Cursor Stability
Avoiding Locks on Terminal Reads
Program Using UPDATE WHERE CURRENT
Ch 9. Bulk Table Processing
Variables Used in BULK Processing
SQL Bulk Commands
BULK SELECT
BULK FETCH
BULK INSERT
Transaction Management for BULK Operations
Sample Program Using BULK Processing
Ch 10. 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 the Types of Dynamic Operations
Preprocessing of Dynamic Queries with C or Pascal Routines
COBOL Call Example
C Subprogram Example
Pascal Subprogram Example
How To Preprocess, Compile, Link and Run the Example Programs
COBOL Calling a C Subprogram
COBOL Calling a Pascal Subprogram
Preprocessing of Dynamic Non-Queries
Using PREPARE and EXECUTE
Defining SQL Commands at Run Time
Sample Program Using EXECUTE IMMEDIATE
Sample Program Using PREPARE and EXECUTE
Ch 11. 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
Ch 12. 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 a 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
Example LONG Column Descriptor Declaration
Using LONG Columns with a BULK SELECT Command
Example
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
File versus Random Heap Space
File Naming Conventions
Considering Multiple Users
Deciding How Much Space to Allocate and Where
Ch 13. 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
Examples of TO_DATETIME, TO_DATE, TO_TIME, and TO_INTERVAL Functions
Example Using the INSERT Command
Example Using the UPDATE Command
Example Using the SELECT Command
Example Using the DELETE Command
Using Date/Time Output Functions
Example TO_CHAR Function
Example TO_INTEGER Function
Using the Date/Time ADD_MONTHS Function
Example ADD_MONTHS Function
Coding Considerations
Program Examples for Date/Time Data
Example Program Using Date/Time Functions
Example Program Converting a Column from CHAR to DATE Data Type
Example Program to Convert from CHAR to Default Data Type
Programming with TID Data Access
Understanding TID Function Input and Output
Using the TID Function in a Select List
Using the TID Function in a WHERE Clause
Declaring TID Host Variables
Understanding the SQLTID Data Format
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
Coding Strategies
Reducing Commit Overhead for Multiple Updates with TID Access
INDEX
Index