HP 3000 Manuals HP 3000 Manuals
900 Series HP 3000 Computer Systems ALLBASE/SQL Release F.0 Application Programming Bulletin for MPE/iX HP Part No. 36216-90062 Printed in U.S.A. Edition First Edition E0692
________________________________________________________________________ |The information contained in this document is subject to change | |without notice. | | | |Hewlett-Packard makes no warranty of any kind with regard to this | |material, including, but not limited to, the implied warranties of | |merchantability or fitness for a particular purpose. Hewlett-Packard | |shall not be liable for errors contained herein or for direct, | |indirect, special, incidental or consequential damages in connection | |with the furnishing or use of this material. | | | |Hewlett-Packard assumes no responsibility for the use or reliability | |of its software on equipment that is not furnished by Hewlett-Packard.| | | |This document contains proprietary information which is protected by | |copyright. All rights are reserved. Reproduction, adaptation, or | |translation without prior written permission is prohibited, except as | |allowed under the copyright laws. | ________________________________________________________________________ Copyright (c) 1992 by Hewlett-Packard Company ________________________________________________________________________ |Use, duplication, or disclosure by the U.S. Government is subject to | |restrictions as set forth in subparagraph (c) (1) (ii) of the Rights | |in Technical Data and Computer Software clause at DFARS 252.227-7013. | |Rights for non-DoD U.S. Government Departments and agencies are as set| |forth in FAR 52.227-19 (c) (1,2). | | | |Hewlett-Packard Company | |3000 Hanover Street | |Palo Alto, CA 94304 U.S.A. | ________________________________________________________________________ Restricted Rights Legend Printed 1992 Printing History The following table lists the printings of this document, together with the respective release dates for each edition. The software version indicates the version of the software product at the time this document was issued. Many product releases do not require changes to the document. Therefore, do not expect a one-to-one correspondence between product releases and document editions. Edition Date Software Version --------------------------------------------------------------------------------------- First Edition June 1992 36216-02A.F0.00Preface This ALLBASE/SQL Release F.0 Application Programming Bulletin for MPE/iX is the second of a series of release specific bulletins. It describes new enhancements explicitly for application programmers using ALLBASE/SQL on HP 3000 systems. The title will carry forward to future bulletins except for the release number which will be specific to each document. MPE/iX, Multiprogramming Executive with Integrated POSIX, is the latest in a series of forward-compatible operating systems for the HP 3000 line of computers. In HP documentation and in talking with HP 3000 users, you will encounter references to MPE XL, the direct predecessor of MPE/iX. MPE/iX is a superset of MPE XL. All programs written for MPE XL will run without change under MPE/iX. You can continue to use MPE XL system documentation, although it may not refer to features added to the operating system to support POSIX (for example, hierarchical directories). This bulletin does not replace the four ALLBASE/SQL application programming guides written for C, COBOL, FORTRAN, and Pascal programmers. Indeed, if you are a new user of ALLBASE/SQL who will be assuming programmer's responsibilities, you should put this book down immediately and pick up the application programming guide for the language you use. Until you have read and absorbed your guide, much of the information included in the bulletin will be of little use to you. The bulletin is only a supplement and it continually refers you to the guide for additional information. The purpose of the bulletin is to give experienced programmers a close, immediate, isolated look at the new functionality. With subsequent software releases, the contents of this bulletin will be integrated into the application programming guides and another bulletin, covering only the functionality of the next release, will be published. Notable features of this new document relative to the existing ALLBASE/SQL manual set include the following: * It is language generic. The bulletin provides information for ALLBASE/SQL programmers as a group; whereas, each of the existing application programming guides is directed to programmers of a given language, C, COBOL, FORTRAN, or Pascal. The bulletin contains code segments and programs in C, COBOL, FORTRAN, and Pascal. Some examples are duplicated in more than one language. Other examples are presented in only one language. Language generic examples are also included. * The bulletin contains examples based on the sample database environment, often including message numbers that might be returned to your application. It frequently refers you to the application programming guides and other manuals in the ALLBASE/SQL manual set for additional information. * It is release specific and documents enhancements to the most current ALLBASE/SQL release. Both additions and changes to functionality are presented. In the case of changes, some information regarding the previous release can be invalidated. This is specifically referenced in the bulletin. To facilitate information lookup, one chapter is devoted to each enhancement or group of related enhancements. The index is task as well as reference oriented. The following topics are included in this manual: * Chapter 1, "Using New Data Integrity Features," compares the use of statement level integrity (the default) and row level integrity (not part of the previous release), discusses how to defer constraint error checking, how to use check constraints in tables and views, and how to use new features of the ALTER TABLE statement. * Chapter 2, "Using Procedures in Application Programs," presents application specific features for this new functionality. * Chapter 3, "Using Parameter Substitution in Dynamic Statements," introduces and gives examples for using dynamic parameters. * Chapter 4, "Preprocessing in Static Conversion Mode," describes a new preprocessing mode that converts any static SQL statements to dynamic ones and does not require a DBEnvironment name specification in its command line. * Chapter 5, "Transaction Management with Multiple DBEnvironment Connections," describes new functionality that allows multiple, simultaneous connections to one or more DBEnvironments and use of the SET TIMEOUT statement. * Chapter 6, "COBOL Preprocessor Enhancements," outlines two enhancements for the COBOL preprocessor: record descriptions for non-bulk queries and host variable initialization with the VALUE clause. * Chapter 7, "Programming with Indicator Variables in Expressions," discusses the use of input indicator variables. * Chapter 8, "Analyzing Queries with GENPLAN," describes how to use the new ISQL GENPLAN statement. * Chapter 9, "Using the VALIDATE Statement," introduces the new VALIDATE statement for validating sections prior to runtime. * Chapter 10, "Using Binary and Varbinary Data in C Applications," references two special host variable types used for binary data in C applications. Example code is based, for the most part, on the sample database environment, PartsDBE, which is a part of the ALLBASE/SQL product. (Refer to appendix C in the ALLBASE/SQL Database Administration Guide for information about the structure of PartsDBE and for listings of the sample database.) We hope you enjoy using the document and that you will send your comments and suggestions to our attention so that the bulletin can become even more effective. What's New in this Release... The following table highlights the new or changed functionality and shows you where each feature is documented. New Features in ALLBASE/SQL Release F.0 ----------------------------------------------------------------------------------------------- | | | | | Feature | Description | Documented in... | | (Category) | | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Rules (Standards) | Rules let you create | ALLBASE/SQL Reference Manual "Constraints, | | | generic constraints on | Procedures, and Rules" and "SQL | | | data by tying stored | Statements," CREATE RULE, DROP RULE; | | | procedures to the | ALLBASE/SQL Database Administration Guide | | | operations performed on a | "System Catalog" chapter. | | | table. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Procedures | A procedure is a | ALLBASE/SQL Reference Manual "Constraints, | | (Standards) | collection of SQL | Procedures, and Rules" and "SQL | | | statements, together with | Statements," CREATE PROCEDURE, DROP | | | local variables, control | PROCEDURE, EXECUTE PROCEDURE, and other new | | | flow statements, and | SQL statements; ALLBASE/SQL Database | | | status statements stored | Administration Guide "System Catalog" | | | as sections in the system | chapter; ALLBASE/SQL Release F.0 | | | catalog. | Application Programming Bulletin for MPE/iX | | | | "Using Procedures in Application Programs." | | | | | ----------------------------------------------------------------------------------------------- | | | | | Outer Join | The OUTER JOIN syntax | ALLBASE/SQL Reference Manual "SQL Queries" | | (Standards) | lets you include | and "SQL Statements," SELECT. | | | unmatched rows from one | | | | table in a join. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Constraints | The CREATE TABLE | ALLBASE/SQL Reference Manual "Constraints, | | (Standards) | statement now includes | Procedures, and Rules" and "SQL | | | check constraints, which | Statements," ALTER TABLE; ALLBASE/SQL | | | let you define particular | Database Administration Guide "System | | | restrictions on the data | Catalog" chapter; ALLBASE/SQL Release F.0 | | | values permitted in a | Application Programming Bulletin for MPE/iX | | | column. You can also now | chapter, "Using New Data Integrity | | | add and drop constraints | Features." | | | and define columns with | | | | constraints using ALTER | | | | TABLE. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Input Indicator | You can now use indicator | ALLBASE/SQL Reference Manual "Expressions" | | Variables in | variables along with | chapter; ALLBASE/SQL Release F.0 | | Expressions | column host variables in | Application Programming Bulletin for MPE/iX | | (Standards) | SQL expressions. | "Progamming with Indicator Variables in | | | | Expressions" chapter. | | | | | ----------------------------------------------------------------------------------------------- | | | | | Parentheses in | Parentheses are now | ALLBASE/SQL Reference Manual "SQL | | UNION syntax, | supported in the SELECT | Statements" chapter, SELECT, CREATE VIEW. | | UNION in CREATE | statement to dictate the | | | VIEW (Standards) | order of evaluation of an | | | | SQL statement which | | | | contains both UNION and | | | | UNION ALL statements. | | | | You can now use UNION and | | | | UNION ALL clauses within | | | | a CREATE VIEW statement. | | | | | | ----------------------------------------------------------------------------------------------- Table Front-0. New Features in ALLBASE/SQL Release F.0 (cont.) ----------------------------------------------------------------------------------------------- | | | | | Feature | Description | Documented in | | (Category) | | | | | | | ----------------------------------------------------------------------------------------------- | | | | | CREATE SCHEMA | This new statement lets | ALLBASE/SQL Reference Manual "SQL | | (Standards) | you define an entire | Statements" chapter, CREATE SCHEMA; | | | database (that is, a set | ALLBASE/SQL Database Administration Guide | | | of objects having the | "Database Creation and Security" chapter. | | | same ownership) at one | | | | time. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Deferred Error | You can use the SET | ALLBASE/SQL Reference Manual "Constraints, | | Checking for | CONSTRAINTS statement to | Procedures, and Rules" chapter, "SQL | | Constraints and | defer constraint checking | Statements" chapter, SET CONSTRAINTS, SET | | Atomicity Level | to the transaction level, | DML ATOMICITY; ALLBASE/SQL Release F.0 | | for General Error | or to reset constraint | Application Programming Bulletin for MPE/iX | | Checking | checking to the current | "Using New Data Integrity Features" | | (Standards) | DML checking level. You | chapter. | | | can also set the | | | | atomicity level for | | | | general DML error | | | | checking to ROW level or | | | | STATEMENT level. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | GRANT...WITH | You can now assign | ALLBASE/SQL Reference Manual "SQL | | GRANT OPTION | grantability along with | Statements" chapter, GRANT, REVOKE; | | (Standards) | an authority you grant on | ALLBASE/SQL Database Administration Guide | | | a table, so that the | "Database Creation and Security" chapter. | | | recipient of a grant can | | | | in turn use the GRANT to | | | | pass the authority to | | | | someone else. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | TID Access | The TID() function now | ALLBASE/SQL Reference Manual "Expressions" | | (Performance) | allows direct access to a | chapter, "TID Function"; application | | | row in an ALLBASE/SQL | programming guides. | | | DBEnvironment. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Multi-Connect and | You can now maintain | ALLBASE/SQL Reference Manual "Using | | Multi-Transaction | multiple connections in | ALLBASE/SQL," "Understanding Multiple | | (Performance) | one or more | Connections and Multiple Transactions"; | | | DBEnvironments, and you | "SQL Statements" chapter, CONNECT, SET | | | can have multiple | CONNECT, SET MULTITRANSACTION, START DBE, | | | transactions active | START DBE NEWLOG; ALLBASE/SQL Release F.0 | | | within a set of | Application Programming Bulletin for MPE/iX | | | connections (though only | "Transaction Management with Multiple | | | one transaction is | DBEnvironment Connections" chapter. | | | permitted per | | | | connection). | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Concurrent | Reduced locking on index | ALLBASE/SQL Reference Manual "Concurrency | | B-Trees | B-tree pages results in | Control" chapter. | | (Performance) | improved concurrency. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Memory Resident | ALLBASE/SQL now lets you | ALLBASE/SQL Database Administration Guide | | Data Buffers | set an option to force | "Physical Design" chapter; "SQLUtil" | | (Performance) | data buffers to be | appendix, ALTDBE, SHOWDBE. | | | memory-resident. | | | | | | ----------------------------------------------------------------------------------------------- Table Front-0. New Features in ALLBASE/SQL Release F.0 (cont.) ----------------------------------------------------------------------------------------------- | | | | | Feature | Description | Documented in | | (Category) | | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Pseudo-Mapped | ALLBASE/SQL now supports | ALLBASE/SQL Database Administration Guide | | Files | the use of pseudo-mapped | "Pseudo-Mapped Files" appendix; "SQLUtil" | | (Performance) | files for DBEFiles in a | appendix, MOVEFILE, SHOWACCESS. | | | DBEnvironment. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Query Access Plan | The GENPLAN statement | ALLBASE/SQL Reference Manual "SQL Queries" | | Display | lets you display the join | chapter; "SQL Statements" chapter, GENPLAN; | | (Usability) | order, join methods, and | ALLBASE/SQL Database Administration Guide | | | access plan chosen by the | "System Catalog" chapter, System.Plan. | | | ALLBASE/SQL optimizer for | | | | a query. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | String and Long | You can now retrieve | ALLBASE/SQL Reference Manual "Data Types" | | Column Functions | substrings and the length | and "Expressions" chapters. | | (Usability) | of string data. You can | | | | also retrieve the type of | | | | long column storage (disk | | | | file, shared memory, or | | | | heap space), and the | | | | output device names. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Revalidation | You can use a new | ALLBASE/SQL Reference Manual "SQL | | (Usability) | VALIDATE statement to | Statements" chapter, VALIDATE; ALLBASE/SQL | | | validate groups of | Release F.0 Application Programming | | | invalid sections prior to | Bulletin for MPE/iX "Using the VALIDATE | | | run time. | Statement" chapter. | | | | | ----------------------------------------------------------------------------------------------- | | | | | Transaction Waits | If the maximum number of | ALLBASE/SQL Database Administration Guide | | (Usability) | transactions is in | "System Catalog" chapter, System.Call. | | | progress, additional | | | | transaction requests are | | | | placed on a wait queue | | | | (known as the throttle | | | | wait queue). | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Dynamic Parameter | You can now include | ALLBASE/SQL Reference Manual "SQL | | Substitution | parameters inside | Statements" chapter, PREPARE, OPEN, | | (Usability) | statements you PREPARE; | EXECUTE; ALLBASE/SQL Release F.0 | | | you execute these | Application Programming Bulletin for MPE/iX | | | statements in an EXECUTE | "Using Parameter Substitution in Dynamic | | | or OPEN statement with a | Statements" chapter. | | | USING clause. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | CATALOG Views | A new set of special | ALLBASE/SQL Database Administration Guide | | (Usability) | system views now permits | "System Catalog" chapter. | | | non-DBA users to see | | | | system catalog | | | | information about objects | | | | they own. New views are | | | | owned by special user | | | | CATALOG. | | | | | | ----------------------------------------------------------------------------------------------- Table Front-0. New Features in ALLBASE/SQL Release F.0 (cont.) ----------------------------------------------------------------------------------------------- | | | | | Feature | Description | Documented in | | (Category) | | | | | | | ----------------------------------------------------------------------------------------------- | | | | | RUN BLOCKS Clause | You can now specify the | ALLBASE/SQL Reference Manual "SQL | | in START | desired number of runtime | Statements" chapter, START DBE, START DBE | | Statements | control blocks by using | NEW, START DBE NEWLOG. | | (Usability) | the new RUN BLOCKS clause | | | | in the START DBE, START | | | | DBE NEW, and START DBE | | | | NEWLOG statements. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Dynamic | The C and Pascal | ALLBASE/SQL Release F.0 Application | | Preprocessing | preprocessors now let you | Programming Bulletin for MPE/iX | | Option | choose a dynamic | "Preprocessing in Static Conversion Mode" | | (Usability) | preprocessing option to | chapter. | | | convert an embedded SQL | | | | application from static | | | | to dynamic. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | ALLBASE/ | You can now connect to | ALLBASE/SQL Release F.0 Application | | DB2Connect | DB2 databases through C | Programming Bulletin for MPE/iX | | support in C and | and Pascal application | "Preprocessing in Static Conversion Mode" | | Pascal | programs. | chapter; ALLBASE/DB2 CONNECT User's Guide. | | (Usability) | | | | | | | ----------------------------------------------------------------------------------------------- | | | | | ALLBASE/NET | You can now use wild | ALLBASE/NET User's Guide. | | support for | cards when specifying | | | wildcards in | user entries in the | | | NETUsers entries | Netusers file. | | | (Usability) | | | | | | | ----------------------------------------------------------------------------------------------- | | | | | SQLVER | A new utility program, | ALLBASE/SQL Database Administration Guide | | (Usability) | SQLVER, lets you display | "DBA Tasks and Tools" chapter. | | | version information for | | | | the ALLBASE/SQL files | | | | installed on your system. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | SET Commands in | You can use a SET command | ALLBASE/ISQL Reference Manual "ISQL | | Utility Programs | to control the behavior | Commands" chapter, SET; ALLBASE/SQL | | (Usability) | of utility programs when | Database Administration Guide, "SQLUtil" | | | errors are encountered. | and "SQLGEN" appendixes, SET. | | | | | ----------------------------------------------------------------------------------------------- | | | | | ISQL DO Command | The ISQL DO command lets | ALLBASE/ISQL Reference Manual "ISQL | | (Usability) | you execute any of the | Commands" chapter, DO. | | | commands in the ISQL | | | | command history buffer. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | LOAD Conversion | You can now convert | ALLBASE/ISQL Reference Manual "ISQL | | in ISQL | non-HP data types such as | Commands" chapter, LOAD. | | (Usability) | EBCDIC, zoned decimal, | | | | and packed decimal into | | | | ALLBASE/SQL data types | | | | when loading a database | | | | table from an external | | | | file. | | | | | | ----------------------------------------------------------------------------------------------- Table Front-0. New Features in ALLBASE/SQL Release F.0 (cont.) ----------------------------------------------------------------------------------------------- | | | | | Feature | Description | Documented in | | (Category) | | | | | | | ----------------------------------------------------------------------------------------------- | | | | | COBOL | The COBOL preprocessor | ALLBASE/SQL Release F.0 Application | | Preprocessor | now allows record | Programming Bulletin for MPE/iX chapter, | | Enhancements | descriptions for non-BULK | "COBOL Preprocessor Enhancements." | | | queries, and it lets you | | | | initialize host variables | | | | with the VALUE clause. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Improved Job | When running ISQL, | ALLBASE/ISQL Reference Manual "Getting | | Control for | SQLUtil, SQLGEN, or | Started with ISQL" chapter; "ISQL Commands" | | Utilities | SQLMigrate, you can echo | chapter, SET, INSTALL, and LIST INSTALL; | | | batch user input to | ALLBASE/SQL Database Administration Guide | | | $STDLIST, terminate the | "SQLUtil" and "SQLGEN" appendixes, SET | | | utility immediately when | EXIT_ON_DBERR; "SQLMIGRATE" appendix, SET. | | | an error is encountered, | | | | and set a JCW to the exit | | | | status to indicate | | | | success or failure. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Dynamic DBEFile | A DBEFile can be created | ALLBASE/SQL Reference Manual "SQL | | Expansion (High | with an increment size | Statements" chapter, CREATE DBEFILE; | | Availability) | and a maximum file size | ALLBASE/SQL Database Administration Guide | | | so that it will expand | "DBEnvironment Configuration and Security" | | | automatically when it | chapter. | | | fills up. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Support for | Backup and recovery can | ALLBASE/SQL Database Administration Guide | | multiple volume | now be carried out using | "Backup and Recovery" chapter and "SQLUtil" | | storage (High | multiple drives and new | appendix. | | Availability) | storage devices. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | User Timeouts | You can set a user | ALLBASE/SQL Reference Manual "SQL | | (Performance) | timeout value for waits | Statements" chapter, START DBE, START DBE | | | on locks and other system | NEW, START DBE NEWLOG, SET USER TIMEOUT; | | | resources. Global | ALLBASE/SQL Database Administration Guide | | | default and maximum | "SQLUtil" appendix, ALTDBE, SHOWDBE; and | | | values can be set by the | ALLBASE/SQL Release F.0 Application | | | DBA, and local values can | Programming Bulletin for MPE/iX | | | be set by the individual | "Transaction Management with Multiple | | | user. | DBEnvironment Connections" chapter. | | | | | ----------------------------------------------------------------------------------------------- Conventions UPPERCASE In a syntax statement, commands and keywords are shown in uppercase characters. The characters must be entered in the order shown; however, you can enter the characters in either uppercase or lowercase. For example: COMMAND can be entered as any of the following: command Command COMMAND It cannot, however, be entered as: comm com_mand comamnd italics In a syntax statement or an example, a word in italics represents a parameter or argument that you must replace with the actual value. In the following example, you must replace filename with the name of the file: COMMAND filename bold italics In a syntax statement, a word in bold italics represents a parameter that you must replace with the actual value. In the following example, you must replace filename with the name of the file: COMMAND(filename) punctuation In a syntax statement, punctuation characters (other than brackets, braces, vertical bars, and ellipses) must be entered exactly as shown. In the following example, the parentheses and colon must be entered: (filename):(filename) underlining Within an example that contains interactive dialog, user input and user responses to prompts are indicated by underlining. In the following example, yes is the user's response to the prompt: Do you want to continue? >> yes { } In a syntax statement, braces enclose required elements. When several elements are stacked within braces, you must select one. In the following example, you must select either ON or OFF: COMMAND { ON } { OFF} [ ] In a syntax statement, brackets enclose optional elements. In the following example, OPTION can be omitted: COMMAND filename [OPTION] When several elements are stacked within brackets, you can select one or none of the elements. In the following example, you can select OPTION or parameter or neither. The elements cannot be repeated. COMMAND filename [ OPTION ] [ parameter] Conventions (continued) [...] In a syntax statement, horizontal ellipses enclosed in brackets indicate that you can repeatedly select the element(s) that appear within the immediately preceding pair of brackets or braces. In the example below, you can select parameter zero or more times. Each instance of parameter must be preceded by a comma: [,parameter][...] In the example below, you only use the comma as a delimiter if parameter is repeated; no comma is used before the first occurrence of parameter: [parameter][,...] |...| In a syntax statement, horizontal ellipses enclosed in vertical bars indicate that you can select more than one element within the immediately preceding pair of brackets or braces. However, each particular element can only be selected once. In the following example, you must select A, AB, BA, or B. The elements cannot be repeated. { A} |...| { B} ... In an example, horizontal or vertical ellipses indicate where portions of an example have been omitted. In a syntax statement, the space symbol shows a required blank. In the following example, parameter and parameter must be separated with a blank: (parameter) (parameter) The symbol indicates a key on the keyboard. For example, RETURN represents the carriage return key or Shift represents the shift key. CTRLcharacterCTRLcharacter indicates a control character. For example, CTRLY means that you press the control key and the Y key simultaneously.
![[]](../../pic3k/M012008/FFN33c50.gif)