HPlogo

ALLBASE/SQL C Application Programming Guide: HP 3000 MPE/iX Computer Systems

» 

Technical documentation

Complete book in PDF
» Feedback

 » Table of Contents

 » Index

HP Part Number: 36216-90080

Edition: Fourth Edition

Published: Printed in: U.S.A 1992


Table of Contents

Preface
1 Getting Started with ALLBASE/SQL Programming in C
Understanding ALLBASE/SQL Operations
Using DML to Manipulate Data
Using DDL to Define Database Objects
Using DCL to Manage Security
Handling Transactions
Handling Errors
Dynamic and Non-Dynamic Operations
Understanding the Program Life Cycle
Developing ALLBASE/SQL Applications
Creating Source Files
General Rules for Embedding SQL
Preprocessing the Source File
Compiling and Linking the Program
Running the Program
Authorizations
Debugging and Testing
Moving into the Production Phase
Installing Program Modules
Granting Module Owner Authorizations
Granting Program User Authorization
Maintaining ALLBASE/SQL Applications
Tuning Performance
Managing Source Code
Updating Application Programs
Changing Program-Related Authorization
Dropping Obsolete Modules
Programming Under the MPE XL Operating System
Security Considerations
File Naming Conventions
Native Language Support
Looking at an Embedded SQL Source Program
2 Using the Preprocessor
Invoking the C Preprocessor
Full Preprocessing Mode
Preprocessor Syntax I
Parameters
Description
Authorization
Example
Syntax Checking Mode
Preprocessor Syntax II
Description
Authorization
Example
DBEnvironment Access
Compiling and Linking
Using the Preprocessor UDCs
Using the Preprocessor in Job Mode
Running the Program
Accessing Multiple DBEnvironments
Identifying Preprocessor Input
Source File
ALLBASE/SQL Message Catalog
Identifying Preprocessor Output
Modified Source File
Include Files
ALLBASE/SQL Message File
Stored Module Containing Sections
Installable Module File
Handling Preprocessor Errors
Preprocessor or DBEnvironment Termination
Preprocessor Invocation Errors
Source File Errors
DBEnvironment Errors
Sample Modified Source File
Sample Preprocessor Generated Include Files
3 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
Using Default Data Values
Declaring Variables for Compatibility
Declaring Variables for Program Elements
4 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[2]
sqlwarn[0]
sqlwarn[1]
sqlwarn[2]
sqlwarn[3]
sqlwarn[6]
Approaches to Status Checking
Implicit Status Checking Techniques
Explicit Status Checking Techniques
5 Simple Data Manipulation
SQL Commands
SELECT
INSERT
UPDATE
DELETE
Transaction Management for Simple Operations
Sample Program Using Simple DML Commands
6 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
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
Sample Program Using Cursor Operations
7 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
8 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 Non-Queries
Using EXECUTE IMMEDIATE
Using PREPARE and EXECUTE
Preprocessing of Dynamic Queries
Dynamically Updating and Deleting Data
Setting Up the SQLDA
Setting Up the Format Array
Setting Up the Data Buffer
Using the Dynamic Query Data Structures
Parsing the Data Buffer
Preprocessing of Commands That May or May Not Be Queries
Sample Programs Using Dynamic Query Operations
cex10a: Program for Dynamic Commands of Unknown Format
cex10b: Program Using Dynamic Commands of Known Format
9 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
10 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 [BULK] INSERT Command
Insert Using Host Variables for LONG Column I/O Strings
Bulk Insert Using Host Variables for LONG Column I/O Strings
Retrieving LONG Column Data with a [BULK] SELECT, FETCH, or REFETCH Command
Using the LONG Column Descriptor
Using LONG Columns with a BULK 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
File versus Random Heap Space
File Naming Conventions
Considering Multiple Users
Deciding How Much Space to Allocate and Where
11 Programming with ALLBASE/SQL 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
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 Example for Date/Time Data
Example Program cex9a
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
Coding Strategies
Reducing Commit Overhead for Multiple Updates with TID Access
Index

List of Figures

1-1 Creating an ALLBASE/SQL C Application Program
1-2 Skeleton ALLBASE/SQL C Program
1-3 Components of a Stored Section
1-4 Ways of Compiling and Linking an ALLBASE/SQL C Program
1-5 Moving an Application to a Production System
1-6 Runtime Dialog of Program cex2
1-7 Program cex2: Using Simple SELECT
2-1 Compiling and Linking
2-2 UDC for Preprocessing SQLIN
2-3 UDC for Preprocessing, Compiling, and Preparing SQLIN
2-4 Sample UDC Invocation
2-5 Full Preprocessing Mode Input and Output
2-6 Sample sqlmsg Showing Errors
2-7 Sample sqlmsg Showing Warning
2-8 Information in SYSTEM.SECTION on Stored Sections
2-9 Figure 2-9. Modified Source File For Program cex2
2-10 Sample Type Include File
2-11 Sample Variable Include File
2-12 Sample Externals Include File
3-1 Host Variable Declarations
3-2 Declaring Host Variables for Single-Row Query Results
3-3 Declaring Host Variables for Multiple-Row Query Results
3-4 Declaring Host Variables for Dynamic Commands
3-5 Declaring Host Variables for Savepoint Numbers
3-6 Declaring Host Variables for Message Catalog Messages
3-7 Declaring Host Variables for DBEnvironment Names
4-1 Program cex5: Implicit and Explicit Status Checking
4-2 Explicit Status Checking Procedures
4-3 Determining Number of Rows Processed After a BULK SELECT
5-1 Flow Chart of Program cex7
5-2 Figure 5-1. Flow Chart of Program cex7 (page 2 of 2)
5-3 Runtime Dialog of Program cex7
5-4 Program cex7: Using INSERT, UPDATE, SELECT and DELETE
6-1 Cursor Operation without the KEEP CURSOR Feature
6-2 Cursor Operation Using KEEP CURSOR WITH LOCKS
6-3 Cursor Operation Using KEEP CURSOR WITH NOLOCKS
6-4 Keep Cursor Application Program
6-5 Flow Chart of Program cex8
6-6 Runtime Dialog of Program cex8
6-7 Program cex8: Using UPDATE WHERE CURRENT
7-1 Flow Chart of Program cex9
7-2 Runtime Dialog of Program cex9
7-3 Program cex9: Using BULK INSERT
8-1 Creation and Use of a Program that has a Stored Module
8-2 Creation and Use of a Program that has no Stored Module
8-3 Procedure Hosting Dynamic Non-Query Commands
8-4 Dynamic Query Data Structures and Data Assignment
8-5 Format of the Data Buffer
8-6 Parsing the Data Buffer in cex10a
8-7 Flow Chart of Program cex10a
8-8 Run Time Dialog of Program cex10a
8-9 Program cex10a: Dynamic Queries of Unknown Format
8-10 Flow Chart of Program cex10b
8-11 Run Time Dialog of Program cex10b
8-12 Program cex10b: Dynamic Queries of Known Format
9-1 Constraints Enforced on the Recreation Database
10-1  Flow of LONG Column Data and Related Information to the Database
10-2  Flow of LONG Column Data and Related Information from the Database
11-1 Program cex9a: Using Date/Time Functions
11-2 Using RC and RR Transactions with BULK SELECT, SELECT, and UPDATE
11-3 Using TID Access to Reduce Commit Overhead
Feedback to webmaster