900 Series HP 3000 Computer Systems ALLBASE/SQL COBOL Application Programming Guide ABCDE HP Part No. 36216-90006 Printed in U.S.A.
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 tness 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.
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.
iv
Preface ALLBASE/SQL is a relational database management system for use on HP 3000 Series 900 computers. ALLBASE/SQL (Structured Query Language) is the language you use to de ne and maintain data in an ALLBASE/SQL DBEnvironment. This manual presents the techniques of embedding ALLBASE/SQL within COBOL language source code. This manual is intended as a learning tool and a reference guide for COBOL programmers.
Chapter 12, \Programming with LONG Columns," shows how to use columns much longer than regular columns. Chapter 13, \Programming with ALLBASE/SQL Functions," describes ALLBASE/SQL functions, including date/time functions and Tuple Identi cation (TID) functions. Chapters 2, 3, 5, 7 through 10, and chapter 13 contain sample programs for use with the sample database.
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 italics com_mand In a syntax statement or an example, a word in italics represents a parameter or argument that you must replace with the actual value.
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][...
Contents 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 . . . . . . . . E ect of Preprocessing on Source Code . . . . . . . . .
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 . . . . . . . . . . .
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 . . . .
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 . . . . . 7. Simple Data Manipulation SQL Commands . . . . SELECT . . . . . . . INSERT . . . . . . . UPDATE . . . . . . DELETE . . . . . . Transaction Management Sample Program COBEX7 . . . . . . . . . . . . . . . . .
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 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
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 . . . . . . . . . . . . . . . . . .
Figures 1-1. 1-2. 1-3. 1-4. 2-1. 2-2. 2-3. 2-4. 2-5. 2-6. 2-7. 2-8. 2-9. 2-10. 2-11. 2-12. 2-13. 2-14. 2-15. 3-1. 4-1. 4-2. 4-3. 4-4. 4-5. 4-6. 4-7. 4-8. 4-9. 5-1. 5-2. 5-3. 6-1. 6-2. 7-1. 7-2. 7-3. 8-1. 8-2. 8-3. 8-4. 8-5. Creating an ALLBASE/SQL COBOL Application Program . . . Preprocess Time Events . . . . . . . . . . . . . . . . . . . Compile-Time and Link-Time Events . . . . . . . . . . . . . Runtime Events . . . . . . . . . . . . . . . . . . . . . .
8-6. 9-1. 9-2. 9-3. 10-1. 10-2. 10-3. 10-4. 10-5. 10-6. 11-1. 12-1. 12-2. 13-1. 13-2. 13-3. Program COBEX8: Using UPDATE WHERE CURRENT . . . . . . . Flow Chart of Program COBEX9 . . . . . . . . . . . . . . . . . . Execution of Program COBEX9 . . . . . . . . . . . . . . . . . . Program COBEX9: Using BULK INSERT . . . . . . . . . . . . . . Creation and Use of a Program that has a Stored Module . . . . . . . Creation and Use of a Program that has No Stored Module . . . . . . Execution of Program COBEX10A . .
Tables 2-1. 4-1. 4-2. 4-3. 4-4. 5-1. 6-1. 11-1. 11-2. 12-1. 12-2. 13-1. 13-2. 13-3. 13-4. Compiler Directives for Implementing the COBOL COPY Statement ALLBASE/SQL Floating Point Column Speci cations . . . . . . . Host Variable Data Types . . . . . . . . . . . . . . . . . . . Program Element Data Description Entries . . . . . . . . . . . . COBOL Data Type Equivalency and Compatibility . . . . . . . . SQLCA Status Checking Fields . . . . . . . . . . . . . . . . .
Getting Started with ALLBASE/SQL Programming in 1 COBOL The steps in creating a COBOL application program that accesses an ALLBASE/SQL relational database environment (DBEnvironment) are summarized in Figure 1-1. Figure 1-1.
Using your favorite editor, you create COBOL source code. The source code is a compilable COBOL program or subprogram that contains SQL commands. The SQL commands contained within the COBOL program are said to be embedded. Refer to the ALLBASE/SQL Reference Manual for SQL terminology and usage rules. Material in this manual presumes a basic understanding of information in that manual. Before compiling the source code, you must preprocess it with the ALLBASE/SQL COBOL preprocessor.
Program Structure The following skeleton program illustrates the relationship between COBOL statements and embedded SQL commands in an application program. SQL commands may appear in a program at locations highlighted. IDENTIFICATION DIVISION. PROGRAM-ID. ProgramName. COBOL Statements . . . DATA DIVISION. FILE SECTION. Host Variable Declarations NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN COBOL Statements . . . WORKING-STORAGE SECTION.
. STOP RUN.
To delimit SQL commands for the preprocessor, you put the pre x EXEC SQL and the su x END-EXEC around each SQL command: EXEC SQL BEGIN WORK END-EXEC. Most SQL commands appear within the Procedure Division where you establish DBEnvironment access and manipulate data in a database. DBEnvironment Access You must always specify a DBEnvironment at preprocessing time. The preprocessor needs to access the DBEnvironment you specify in the INFO string.
After a le is preprocessed, it must be compiled so that no linking is performed before the next source le is preprocessed. When all source les have been preprocessed and compiled, you link them to create the executable program. Note that a program which accesses more than one DBEnvironment must do so in sequence. Such program design may adversely a ect performance and requires special consideration.
File Referencing When you create a DBEnvironment, a Database Environment Con guration (DBECon) le is created. The le name of this DBECon le is stored in the DBECon le itself. The group and account name at creation time are part of the DBECon le name. In all subsequent references to les, you may use either a fully quali ed le name or a le name relative to that of the DBECon le.
Native Language Support ALLBASE/SQL lets you manipulate databases in a number of native languages in addition to the default language, known as NATIVE-3000. You can use either 8-bit or 16-bit character data, as appropriate for the language you select. In addition, you can always include ASCII data in any database, since ASCII is a subset of each supported character set. The collating sequence for sorting and comparisons is that of the native language selected.
0 1 2 3 4 5 6 7 8 NATIVE-3000 AMERICAN C-FRENCH DANISH DUTCH ENGLISH FINNISH FRENCH GERMAN 9 10 11 12 13 14 41 51 ITALIAN NORWEGIAN PORTUGUESE SPANISH SWEDISH ICELANDIC KATAKANAC ARABIC 52 61 71 81 201 211 221 231 ARABICW GREEK HEBREW TURKISH CHINESE-S CHINESE-T JAPANESE KOREAN Resetting NLUSERLANG while you are connected to a DBEnvironment has no e ect on the current DBE session. The ALLBASE/SQL COBOL Preprocessor The ALLBASE/SQL COBOL preprocessor is speci cally for COBOL II/XL programs.
Figure 1-2. Preprocess Time Events Effect of Preprocessing on Source Code The COBOL preprocessor scans the source code for SQL commands. If the syntax of an SQL command is correct, the preprocessor converts the command into compilable COBOL statements that call ALLBASE/SQL external procedures at run time. During preprocessing, for example, the following SQL command is converted to modi ed source code.
The modi ed source code is as follows: **** * * * * * * * * **** Start SQL Preprocessor **** EXEC SQL SELECT PARTNUMBER, PARTNAME, SALESPRICE INTO :PARTNUMBER, :PARTNAME, :SALESPRICE :SALESPRICEIND FROM PURCHDB.
The names that appear in the inserted COBOL code (italicized in the above example) identify variables used by the ALLBASE/SQL external procedures; in this example, the names identify variables used by the SQLXFETO external procedure. Some of these variables are derived from host variables.
The Stored Section A section consists of ALLBASE/SQL instructions for executing an SQL command. The SQL commands that do not generate stored sections are listed in the \Stored Sections" paragraph of the \Using the ALLBASE/SQL COBOL Preprocessor." Not every SQL command requires a section. For each SQL command that does require a section, the preprocessor creates the section and assigns to it a unique reference number.
MOVE 1 TO SQLSECNUM . . . CALL SQLXCBL USING SQLXFET, SQLCA, SQLOWNER, SQLMODNAME, SQLSECNUM, SQLTEMPV, SQLINLEN, SQLOUTLEN, SQLTRUE Purpose of Sections A section serves two purposes: Access validation: Before executing a stored section at run time, ALLBASE/SQL ensures that any objects referenced exist and that runtime authorization criteria are satis ed.
The Compiler and the Linker Figure 1-3 summarizes the steps in creating an executable ALLBASE/SQL COBOL program from the les created by the COBOL preprocessor. Figure 1-3. Compile-Time and Link-Time Events You must use native mode to compile and link your program. You submit to the COBOL compiler a modi ed source code le and related include les created by the preprocessor. The compiler generates an object code module.
:HELLO PGMR1.ACCTDB,GROUPC . . :RUN PSQLCOB.PUB.SYS; INFO = 'SomeDBE.GROUPDB(MODULE(SOMEMOD))' . . :COB85XL Modi edSourceCodeFile,,$NULL :LINK ; TO=SOMEPROG ALLBASE/SQL Program Execution When an ALLBASE/SQL program is rst created, it can only be executed by the module OWNER or a DBA. In addition, it can only operate on the DBEnvironment used at preprocessing time if a module was generated.
Installing the Program Module When the preprocessor stores a module in a DBEnvironment, it also creates a le containing a copy of the module, which can be installed into another DBEnvironment. You use the INSTALL command in ISQL to install the module in another DBEnvironment. In this example, the module is installed in the SomeDBE environment which is in the same group and account as the PartsDBE environment: isql=> CONNECT TO 'SomeDBE.GROUPDB.ACCTDB'; isql=> INSTALL SOMEMOD.GROUPC.
Granting Program User Authorization In order to execute an ALLBASE/SQL program you must be able to start any DBE session initiated in the program. You must also have one of the following authorities in the DBEnvironment accessed by the program: RUN module OWNER DBA A DBA must grant the authority to start a DBE session. In most cases, application programs start a DBE session with the CONNECT command, so CONNECT authorization is su cient: isql=> CONNECT TO 'SomeDBE.GROUPDB.
Once you identify the ALLBASE/SQL message catalog and appropriate DBEnvironment, you can run the program: :RUN SomeProg.GROUPC.ACCTDB You must specify the name of an executable program le as SomeProg . Do not specify a module name in the RUN command. At run time, an ALLBASE/SQL program interacts with the DBEnvironment as illustrated in Figure 1-4. Figure 1-4.
All the COBOL statements inserted by the preprocessor and the stored sections automatically handle database operations, including providing the application program with status information after SQL commands are executed. SQL commands that have a stored section are executed if the section is valid at run time or can be validated by ALLBASE/SQL at run time. Dynamic commands are those not de ned until run time. Such commands can be entered by the user at run time.
The DROP MODULE command is also useful in conjunction with revised programs whose modules must be installed in a DBEnvironment di erent from that on which preprocessing occurred. Before using the INSTALL command to store the new module, you drop the existing module using the DROP MODULE command, preserving or dropping related RUN authorization as required.
2 Using the ALLBASE/SQL COBOL Preprocessor You use the ALLBASE/SQL COBOL preprocessor to develop COBOL application programs that access an ALLBASE/SQL DBEnvironment. The Preprocessor and Application Development COBOL ALLBASE/SQL application development involves the following steps: Preprocess those programs in the application that contain SQL commands. Compile the preprocessed (modi ed) source code as well as any source code not requiring preprocessing.
Figure 2-1. Developing a COBOL ALLBASE/SQL Program with Subprograms During preprocessing, the COBOL preprocessor actually accesses the same DBEnvironment to be used by your program at run time. The preprocessor stores a module in the DBEnvironment which is executed at run time. The module is used at run time to optimize and validate DBEnvironment operations. During any invocation, the COBOL preprocessor can access only one DBEnvironment.
Preprocessor Modes You can use the preprocessor in two modes: 1. To check your SQL syntax. 2. To perform full preprocessing which includes SQL syntax checking, creating compilable output, storing a module in a DBEnvironment, and creating a le that contains an installable copy of the stored module. As you develop the SQL portions of your COBOL programs, syntax checking mode is quite useful. Preprocessing is quicker in this mode than in full preprocessing mode.
Preprocessor Input and Output Regardless of the mode you use, the following les must be available when you invoke the COBOL preprocessor, as shown in Figure 2-2: source le: a le containing the source code for the COBOL ALLBASE/SQL program or subprogram with embedded SQL commands for a DBEnvironment. The formal le designator for this input le is: SQLIN ALLBASE/SQL message catalog : a le containing preprocessor messages and ALLBASE/SQL error and warning messages.
installable module le: a le containing a copy of the module created by the preprocessor. The formal le designator for this le is: SQLMOD When you run the preprocessor in full preprocessing mode , the preprocessor also stores a module in the DBEnvironment accessed by your program. The module is used at run time to execute DBEnvironment operations.
Figure 2-2. COBOL Preprocessor Input and Output Figure 2-3.
Source File The source le (SQLIN) must be an ASCII le (numbered or unnumbered) that contains at a minimum the following statements: IDENTIFICATION DIVISION. PROGRAM-ID ProgramName. AnyStatement. When parsing SQLIN, the COBOL preprocessor ignores COBOL statements and COBOL compiler directives in SQLIN except $SET, $IF, and $INCLUDE. Only the following information is parsed by the COBOL preprocessor: The PROGRAM-ID.
:RUN COBEX2P Program to SELECT specified rows from the Parts Table - COBEX2 Event List: Connect to PartsDBE Begin Work SELECT specified Part Number from Parts Table until user enters "/" Commit Work Disconnect from PartsDBE Connect to PartsDBE Enter Part Number within Parts Table or "/" to STOP> 1243-P-01 SELECT PartNumber, PartName, SalesPrice Begin Work Part Number not found! Commit Work Enter Part Number within Parts Table or "/" to STOP> 1323-D-01 SELECT PartNumber, PartName, SalesPrice Begin Work Commi
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * Program COBEX2: This program illustrates the use of SQL's SELECT command to retrieve one row at a time. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * IDENTIFICATION DIVISION. PROGRAM-ID. AUTHOR. INSTALLATION. DATE-WRITTEN. DATE-COMPILED. REMARKS. COBEX2. HP TRAINING HP. 17 JULY 1987. 17 JULY 1987. SQL'S SELECT WITH WHENEVER COMMAND. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SOURCE-COMPUTER. OBJECT-COMPUTER.
77 DONE-FLAG 88 NOT-DONE 88 DONE PIC X(01) VALUE 'N'. VALUE 'N'. VALUE 'Y'. 77 ABORT-FLAG 88 NOT-STOP 88 ABORT PIC X(01) VALUE 'N'. VALUE 'N'. VALUE 'Y'. 01 DEADLOCK PIC S9(9) COMP VALUE -14024. 01 RESPONSE. 05 RESPONSE-PREFIX 05 FILLER 01 DOLLARS $PAGE PROCEDURE DIVISION. PIC X(01) VALUE SPACE. PIC X(15) VALUE SPACES. PIC $$$,$$$,$$$.99. A100-MAIN. DISPLAY "Program to SELECT specified rows from " "the Parts Table - COBEX2". DISPLAY " ". DISPLAY "Event List:". DISPLAY " Connect to PartsDBE".
A200-CONNECT-DBENVIRONMENT. EXEC SQL WHENEVER SQLERROR GO TO S300-SERIOUS-ERROR END-EXEC. DISPLAY "Connect to PartsDBE". EXEC SQL CONNECT TO 'PartsDBE' END-EXEC. A200-EXIT. EXIT. A300-BEGIN-TRANSACTION. DISPLAY "Begin Work". EXEC SQL BEGIN WORK END-EXEC. A300-EXIT. EXIT. A400-END-TRANSACTION. DISPLAY "Commit Work". EXEC SQL COMMIT WORK END-EXEC. A400-EXIT. EXIT. A500-TERMINATE-PROGRAM. EXEC SQL RELEASE END-EXEC. STOP RUN. A500-EXIT. EXIT. $PAGE Figure 2-5.
B100-SELECT-DATA. MOVE SPACES TO RESPONSE. MOVE "Enter Part Number or '/' to STOP> " TO PROMPT. WRITE PROMPT AFTER ADVANCING 1 LINE. ACCEPT RESPONSE. IF RESPONSE-PREFIX = "/" MOVE "Y" TO DONE-FLAG GO TO B100-EXIT ELSE MOVE RESPONSE TO PARTNUMBER. EXEC SQL WHENEVER SQLERROR GO TO S400-SQL-ERROR END-EXEC. EXEC SQL WHENEVER SQLWARNING GO TO S500-SQL-WARNING END-EXEC. EXEC SQL WHENEVER NOT FOUND GO TO S600-NOT-FOUND END-EXEC. DISPLAY "SELECT PartNumber, PartName and SalesPrice".
B200-DISPLAY-ROW. DISPLAY " ". DISPLAY " Part Number: DISPLAY " Part Name: IF ELSE " PARTNUMBER. " PARTNAME. SALESPRICEIND < 0 DISPLAY " Sales Price is NULL" MOVE SALESPRICE TO DOLLARS DISPLAY " Sales Price: " DOLLARS. B200-EXIT. EXIT. $PAGE S100-STATUS-CHECK. IF SQLCODE < DEADLOCK MOVE 'Y' TO ABORT-FLAG. PERFORM S200-SQL-EXPLAIN THRU S200-EXIT UNTIL SQLCODE = 0. S100-EXIT. EXIT. S200-SQL-EXPLAIN. EXEC SQL SQLEXPLAIN :SQLMESSAGE END-EXEC. DISPLAY SQLMESSAGE. S200-EXIT. EXIT. S300-SERIOUS-ERROR.
S400-SQL-ERROR. PERFORM S100-STATUS-CHECK THRU S100-EXIT. IF ABORT-FLAG = 'Y' PERFORM A500-TERMINATE-PROGRAM ELSE PERFORM A400-END-TRANSACTION THRU A400-EXIT GO TO B100-EXIT. S400-EXIT. EXIT. S500-SQL-WARNING. DISPLAY "SQL WARNING has occurred. "of data may not be valid:". The following row " PERFORM B200-DISPLAY-ROW THRU B200-EXIT. PERFORM A400-END-TRANSACTION THRU A400-EXIT. GO TO B100-EXIT. S500-EXIT. EXIT. S600-NOT-FOUND. DISPLAY " ". DISPLAY "Part Number not found!".
Output File Attributes The COBOL preprocessor output les are temporary les. When the SQLIN illustrated in Figure 2-5 is preprocessed, the attributes of the output les created are as follows: :listftemp,2 TEMPORARY FILES FOR SOMEUSER.
In full preprocessing mode , the preprocessor also: Generates a COBOL declaration of the SQLCA following the INCLUDE SQLCA command. Generates COBOL sentences providing conditional instructions following SQL commands encountered after one of the following SQL commands: WHENEVER SQLERROR, WHENEVER SQLWARNING, and WHENEVER NOT FOUND. Generates COBOL sentences that call ALLBASE/SQL external procedures at run time.
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * Program COBEX2: This program illustrates the use of SQL's SELECT command to retrieve one row at a time. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * IDENTIFICATION DIVISION. PROGRAM-ID. AUTHOR. INSTALLATION. DATE-WRITTEN. DATE-COMPILED. REMARKS. * * * * * COBEX2. HP TRAINING HP. 17 JULY 1987. 17 JULY 1987. SQL'S SELECT WITH WHENEVER COMMAND. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SOURCE-COMPUTER. HP-3000.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 05 SQLERRP 05 SQLERRD PIC X(8). OCCURS 6 TIMES PIC S9(9) COMP SYNC. xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 05 SQLWARN.
01 DOLLARS $PAGE PROCEDURE DIVISION. PIC $$$,$$$,$$$.99. A100-MAIN. DISPLAY "Program to SELECT specified rows from " "the Parts Table - COBEX2". DISPLAY " ". DISPLAY "Event List:". DISPLAY " Connect to PartsDBE". DISPLAY " Begin Work". DISPLAY " SELECT specified Part Number from the " "Parts Table until user enters '/' ". DISPLAY " Commit Work". DISPLAY " Disconnect from PartsDBE". DISPLAY " ". OPEN OUTPUT CRT. PERFORM A200-CONNECT-DBENVIRONMENT THRU A200-EXIT.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx MOVE 264 TO SQLCONLEN CALL "SQLXCONO" USING SQLCA, SQLCONLEN, SQLCONST1 IF SQLCODE IS NEGATIVE GO TO S300-SERIOUS-ERROR END-IF **** End SQL Preprocessor **** .
A400-EXIT. EXIT. A500-TERMINATE-PROGRAM. xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx **** Start * * * **** SQL Preprocessor **** EXEC SQL RELEASE END-EXEC Start Inserted Statements **** MOVE 56 TO SQLCONLEN CALL "SQLXCONO" USING SQLCA, SQLCONLEN, SQLCONST4 IF SQLCODE IS NEGATIVE GO TO S300-SERIOUS-ERROR END-IF **** End SQL Preprocessor **** .
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx **** Start * * * * **** SQL Preprocessor **** EXEC SQL WHENEVER SQLERROR GO TO S400-SQL-ERROR END-EXEC Start Inserted Statements **** CONTINUE **** End SQL Preprocessor **** xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx MOVE 1 TO SQLSECNUM MOVE 16 TO SQLINLEN MOVE 54 TO SQLOUTLEN CALL "SQLXFETO" USING SQLCA, SQLOWNER, SQLMODNAME, SQLSECNUM, SQLTEMPV, SQLINLEN, SQLOUTLEN, SQLTRUE IF SQLCODE IS ZERO MOVE SQLREC2-FIELD1 TO PARTNUMBER MOVE SQLREC2-FIELD2 TO PARTNAME MOVE SQLREC2-FIELD3-IND TO SALESPRICEIND IF SQLREC2-FIELD3-IND IS NOT NEGATI
B200-EXIT. EXIT. $PAGE S100-STATUS-CHECK. IF SQLCODE < DEADLOCK MOVE 'Y' TO ABORT-FLAG. PERFORM S200-SQL-EXPLAIN THRU S200-EXIT UNTIL SQLCODE = 0. S100-EXIT. EXIT. S200-SQL-EXPLAIN.
S400-SQL-ERROR. PERFORM S100-STATUS-CHECK THRU S100-EXIT. IF ELSE ABORT-FLAG = 'Y' PERFORM A500-TERMINATE-PROGRAM PERFORM A400-END-TRANSACTION THRU A400-EXIT GO TO B100-EXIT. S400-EXIT. EXIT. S500-SQL-WARNING. DISPLAY "SQL WARNING has occurred. The following row " "of data may not be valid:". PERFORM B200-DISPLAY-ROW THRU B200-EXIT. PERFORM A400-END-TRANSACTION THRU A400-EXIT. GO TO B100-EXIT. S500-EXIT. EXIT. S600-NOT-FOUND. DISPLAY " ". DISPLAY "Part Number not found!".
Preprocessor Generated Include Files SQLCONST and SQLVAR are preprocessor generated include les which contain declarations for variables and constants referenced in preprocessor generated sentences in SQLOUT. Figure 2-7 and Figure 2-8 illustrate, respectively, the SQLCONST and SQLVAR les that correspond to the SQLOUT le in Figure 2-6.
01 SQLREC1 REDEFINES SQLTEMPV. 05 SQLREC1-FIELD1 PIC 01 SQLREC2 REDEFINES SQLTEMPV. 05 SQLREC2-FIELD1 PIC 05 SQLREC2-FIELD2 PIC 05 SQLREC2-FIELD3 PIC 05 SQLREC2-FIELD3-IND PIC 01 SQLREC3 REDEFINES SQLTEMPV. 05 FILLER PIC X(108). 01 SQLREC4 REDEFINES SQLTEMPV. 05 SQLREC4-FIELD1 PIC X(16). X(16). X(30). S9(8)V9(2) COMP-3. S9(4) COMP. X(132). Figure 2-8. Sample Variable Include File COBOL COPY Statement Support ALLBASE/SQL now supports the COBOL COPY statement.
Using the COPY Statement with ALLBASE/SQL COPY statement syntax and a complete explanation of its use in COBOL is found in chapter 13 of the HP COBOL II/XL Reference Manual . No syntactical di erences exist between COBOL and ALLBASE/SQL implementation of the COPY statement. However, you should be aware of the following speci cs: The reserved word NOLIST can be used to suppress printing the contents of the copylib module in the compiler listing.
**** * * * **** Start SQL Preprocessor **** EXEC SQL SQLEXPLAIN :SQLMESSAGE END-EXEC Start Inserted Statements **** MOVE SPACES TO SQLREC2 MOVE 2 TO SQLINLEN CALL SQLXCBL USING SQLXPLN, SQLCA, SQLTEMPV, SQLINLEN, SQLFALSE MOVE SQLREC2-FIELD1 TO SQLMESSAGE **** End SQL Preprocessor **** . DISPLAY SQLMESSAGE. S200-EXIT. EXIT.
An IF statement that appears without parameters has the same e ect as an IF satement that evaluates to true. When an IF statement evaluates to false, no source records are sent to the compiler until an IF statement evaluates to true is encountered. Code Example Suppose you want to conditionally preprocess some parts of your source code and send it to the compiler, and not preprocess other parts of your source code, you could use SET and IF statements in your source code, as follows: $SET X1=ON,X3=ON . . .
ALLBASE/SQL Message File Messages placed in the ALLBASE/SQL message le (SQLMSG) come from the ALLBASE/SQL message catalog. The formal le designator for the message catalog is: SQLCTxxx.PUB.SYS where xxx is the numerical value for the current language. If this catalog cannot be opened, ALLBASE/SQL looks for the default NATIVE-3000 message catalog: SQLCT000.PUB.SYS If the default catalog cannot be opened, ALLBASE/SQL returns an error message saying that the catalog le is not available.
:FILE SQLMSG=MyMsg;Rec=-80,16,f,Ascii :FILE SQLIN=COBEX2 :RUN PSQLCOB.PUB.SYS;INFO="PartsDBE (DROP)" WED, OCT 25, 1991, 1:38 PM HP36216-02A.E1.02 COBOL Preprocessor/3000 ALLBASE/SQL (C) COPYRIGHT HEWLETT-PACKARD CO. 1982,1983,1984,1985,1986,1987,1988, 1989,1990,1991. ALL RIGHTS RESERVED. 1 ERRORS 0 WARNINGS END OF PREPROCESSING. PROGRAM TERMINATED IN AN ERROR STATE. (CIERR 976) If you want to keep the message le, you should save the le you equate to SQLMSG. It is created as a temporary le.
As Figure 2-11 illustrates, the preprocessor can terminate with a warning message. Although a section is stored for the semantically incorrect command, the section is marked as invalid and will not execute at run time if it cannot be validated. :EDITOR HP32501A.07.20 EDIT/3000 FRI, OCT 27 1991, 10:20 AM (C) HEWLETT-PACKARD CO. 1990 /T SQLMSG;L ALL UNN FILE UNNUMBERED . . . SQLIN = COBEX2.SOMEGRP.
Installable Module File When the COBOL preprocessor stores a module in the system catalog of a DBEnvironment at preprocessing time, it places a copy of the module in an installable module le. By default the installable module le is named SQLMOD. The module in this le can be installed into a DBEnvironment di erent from the DBEnvironment accessed at preprocessing time by using the INSTALL command in ISQL. For example: :RUN PSQLCBL.PUB.
Stored Sections In full preprocessing mode, the preprocessor stores a section for each embedded command except : BEGIN DECLARE SECTION BEGIN WORK CLOSE COMMIT WORK CONNECT DECLARE CURSOR DELETE WHERE CURRENT DESCRIBE END DECLARE SECTION EXECUTE EXECUTE IMMEDIATE FETCH INCLUDE OPEN PREPARE RELEASE ROLLBACK WORK SAVEPOINT START DBE STOP DBE SQLEXPLAIN TERMINATE USER UPDATE WHERE CURRENT WHENEVER The commands listed above either require no authorization to execute or are executed based on information contai
SECTION: This column gives the section number. Each section associated with a module is assigned a number by the preprocessor as it parses the related SQL command at preprocessing time.
isql=>SELECT NAME,OWNER,DBEFILESET,SECTION,TYPE,VALID FROM SYSTEM.SECTION; SELECT NAME,OWNER,DBEFILESET,SECTION,TYPE,VALID FROM SYSTEM.
The rst eleven rows in this query result describe the sections stored for the system views. The next two rows describe the two views in the sample database: PURCHDB.PARTINFO and PURCHDB.VENDORSTATISTICS. Views are always stored as invalid sections, because the run tree is always generated at run time. The remaining rows describe sections associated with two preprocessed programs. COBEX2 contains only one section, for executing the SELECT command in the program illustrated in Figure 2-6.
Invoking the COBOL Preprocessor The COBOL preprocessor can be invoked to either Only check the syntax of embedded SQL commands, or Check the syntax of embedded SQL commands, create compilable output, store a module in a DBEnvironment, and create an installable module le. Syntax Checking Mode You use the following RUN command to only check the syntax of the SQL commands embedded in a le equated to SQLIN. :RUN PSQLCOB.PUB.
Example :FILE SQLIN=COBEX2 :RUN PSQLCOB.PUB.SYS;INFO="(SYNTAX)" WED, OCT 25, 1991, 1:38 PM HP36216-E1.02 COBOL Preprocessor/3000 ALLBASE/SQL (C) COPYRIGHT HEWLETT-PACKARD CO. 1982,1983,1984,1985,1986,1987,1988, 1989,1990,1991. ALL RIGHTS RESERVED. Syntax checked. 1 ERRORS 0 WARNINGS END OF PREPROCESSING. PROGRAM TERMINATED IN AN ERROR STATE. (CIERR 976) :EDITOR HP32501A.07.20 EDIT/3000 FRI, OCT 27, 1991, 9:35 AM (C) HEWLETT-PACKARD CO. 1990 /T SQLMSG;L ALL UNN FILE UNNUMBERED SQLIN . . . = COBEX2.
Full Preprocessing Mode You use the following RUN command to both check SQL syntax and create output les from SQLIN that can be processed by the COBOL compiler. This RUN command also stores a module in the DBEnvironment named and creates a le containing an installable version of the module. :RUN PSQLCOB.PUB.SYS;INFO= "DBEnvironmentName [( |...
Description Before invoking the preprocessor in this mode when the program being preprocessed already has a stored module, ensure that the earlier version of the program is not being executed. The preprocessor starts a DBE session in the DBEnvironment named in the RUN command by issuing a CONNECT TO 'DBEnvironmentName ' command. If the autostart ag is OFF, the DBE session can be initiated only after a START DBE command has been processed.
Example :FILE SQLIN=COBEX2 :RUN PSQLCOB.PUB.SYS;INFO=& "PartsDBE (MODULE(COBEX2) OWNER(OwnerP@SomeAcct) REVOKE DROP)" WED, OCT 25, 1991, 1:38 PM HP36216-E1.02 COBOL Preprocessor/3000 ALLBASE/SQL (C) COPYRIGHT HEWLETT-PACKARD CO. 1982,1983,1984,1985,1986,1987,1988, 1989,1990,1991. ALL RIGHTS RESERVED. 0 ERRORS 1 WARNINGS END OF PREPROCESSING. END OF PROGRAM :EDITOR HP32501A.07.20 EDIT/3000 FRI, OCT 27, 1991, 10:17 AM (C) HEWLETT-PACKARD CO. 1990 /T SQLMSG;L ALL UNN FILE UNNUMBERED . . .
Using the Preprocessor UDC's Two UDC's for invoking the COBOL preprocessor are provided with ALLBASE/SQL in the HPSQLUDC.PUB.SYS le: PCOB, illustrated in Figure 2-12, invokes the preprocessor in full preprocessing mode. You specify the source le name, a DBEnvironment name, and a name for SQLMSG (if you do not want preprocessor messages to go to $STDLIST). :PCOB SourceFileName,DBEnvironment The PCOB UDC uses the following preprocessor INFO string parameters: ModuleName is the name of the source le.
Using the Preprocessor UDC's PCOB srcfile,dbefile,msgfile=$stdlist continue setvar _savefence hpmsgfence setvar hpmsgfence 2 continue purge !msgfile purge sqlout purge sqlmod purge sqlvar purge sqlconst setvar hpmsgfence _savefence deletevar _savefence file sqlin = !srcfile file sqlmsg = !msgfile; rec=-80,16,f,ascii file sqlout; disc=10000,32; rec=-80,16,f,ascii file sqlmod; disc=1023,10,1; rec=250,,f,binary file sqlvar; disc=2048,32; rec=-80,16,f,ascii file sqlconst; disc=2048,32; rec=-80,16,f,ascii contin
Using the Preprocessor UDC's PPCOB srcfile,dbefile,pgmfile,msgfile=$stdlist continue setvar _savefence hpmsgfence setvar hpmsgfence 2 continue purge !msgfile purge sqlout purge sqlmod purge sqlvar purge sqlconst setvar hpmsgfence _savefence deletevar _savefence file sqlin = !srcfile file sqlmsg = !msgfile; rec=-80,16,f,ascii file sqlout; disc=10000,32; rec=-80,16,f,ascii file sqlmod; disc=1023,10,1; rec=250,,f,binary file sqlvar; disc=2048,32; rec=-80,16,f,ascii file sqlconst; disc=2048,32; rec=-80,16,f,asc
Using the Preprocessor UDC's :PPCOB COBEX2,PARTSDBE,COBEX2P WED, OCT 25, 1991, 1:38 PM HP36216-E1.02 COBOL Preprocessor/3000 ALLBASE/SQL (C) COPYRIGHT HEWLETT-PACKARD CO. 1982,1983,1984,1985,1986,1987,1988, 1989,1990,1991. ALL RIGHTS RESERVED. SQLIN DBEnvironment = COBEX2.SOMEGRP.SOMEACCT = partsdbe Module Name = COBEX2 1 Sections stored in DBEnvironment. 0 ERRORS 0 WARNINGS END OF PREPROCESSING. END OF PROGRAM PAGE 0001 COBOL II/XL HP31500A.03.00 [85] Copyright Hewlett-Pa . . .
Using the Preprocessor UDC's The line number referenced in the compiler output messages is the COBOL statement number in the compiler output listing .
Running the Preprocessor in Job Mode You can preprocess COBOL ALLBASE/SQL programs in job mode. Figure 2-15 illustrates a job le that uses the PPCOB UDC to preprocess several sample programs. !JOB JIM,MGR.HPDB,COBOL;OUTCLASS=,1 :ppcob cobp01,PartsDBE,cobp01p :ppcob cobp01a,PartsDBE,cobp01ap :ppcob cobp02,PartsDBE,cobp02p . . :ppcob cobp50,PartsDBE,cobp50p !TELL JIM,MGR.HPDB; COBOL Preprocessing is complete! !EOJ'' Figure 2-15.
SQLIN Errors When the COBOL preprocessor encounters errors when parsing SQLIN, messages are placed in SQLMSG. Refer to the discussion earlier in this chapter under SQLMSG for additional information on this category of errors. DBEnvironment Errors Some errors can be caused because: A DBEnvironment is not started yet. Resources are insu cient. A deadlock has occurred. Refer to the ALLBASE/SQL Database Administration Guide for information on handling DBEnvironment errors.
3 Embedding SQL Commands In every ALLBASE/SQL COBOL program, you embed SQL commands in the DATA DIVISION and the PROCEDURE DIVISION in order to: 41 5 Declare the SQL Communications Area (SQLCA) 42 5 Declare host variables 43 5 Start a DBE session by connecting to the DBEnvironment De ne transactions 4 4 5,4 5 5 46 5 Terminate the DBE session 47 5 Implicitly check the status of SQL command execution 48 5 De ne or manipulate data in the DBEnvironment 49 5 Explicitly check the status of SQL command execution
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * Program COBEX2: This program illustrates the use of SQL's SELECT command to retrieve one row at a time. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * IDENTIFICATION DIVISION. PROGRAM-ID. AUTHOR. INSTALLATION. DATE-WRITTEN. DATE-COMPILED. REMARKS. COBEX2. HP TRAINING HP. 17 JULY 1987. 17 JULY 1987. SQL'S SELECT WITH WHENEVER COMMAND. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SOURCE-COMPUTER. OBJECT-COMPUTER.
77 DONE-FLAG 88 NOT-DONE 88 DONE PIC X(01) VALUE 'N'. VALUE 'N'. VALUE 'Y'. 77 ABORT-FLAG 88 NOT-STOP 88 ABORT PIC X(01) VALUE 'N'. VALUE 'N'. VALUE 'Y'. 01 DEADLOCK PIC S9(9) COMP VALUE -14024. 01 RESPONSE. 05 RESPONSE-PREFIX 05 FILLER PIC X(01) VALUE SPACE. PIC X(15) VALUE SPACES. 01 DOLLARS $PAGE PROCEDURE DIVISION. PIC $$$,$$$,$$$.99. A100-MAIN. DISPLAY "Program to SELECT specified rows from " "the Parts Table - COBEX2". DISPLAY " ". DISPLAY "Event List:". DISPLAY " Connect to PartsDBE".
A200-CONNECT-DBENVIRONMENT. EXEC SQL WHENEVER SQLERROR GO TO S300-SERIOUS-ERROR END-EXEC. DISPLAY "Connect to PartsDBE". EXEC SQL CONNECT TO 'PartsDBE' END-EXEC. 43 5 A200-EXIT. EXIT. A300-BEGIN-TRANSACTION. DISPLAY "Begin Work". EXEC SQL BEGIN WORK END-EXEC. 44 5 A300-EXIT. EXIT. A400-END-TRANSACTION. DISPLAY "Commit Work". EXEC SQL COMMIT WORK END-EXEC. 45 5 A400-EXIT. EXIT. A500-TERMINATE-PROGRAM. EXEC SQL RELEASE END-EXEC. STOP RUN. A500-EXIT. EXIT. $PAGE Figure 3-1.
B100-SELECT-DATA. MOVE SPACES TO RESPONSE. MOVE "Enter Part Number or '/' to STOP> " TO PROMPT. WRITE PROMPT AFTER ADVANCING 1 LINE. ACCEPT RESPONSE. IF ELSE RESPONSE-PREFIX = "/" MOVE "Y" TO DONE-FLAG GO TO B100-EXIT MOVE RESPONSE TO PARTNUMBER. EXEC SQL WHENEVER SQLERROR GO TO S400-SQL-ERROR END-EXEC. 47 5 EXEC SQL WHENEVER SQLWARNING GO TO S500-SQL-WARNING END-EXEC. EXEC SQL WHENEVER NOT FOUND GO TO S600-NOT-FOUND END-EXEC. DISPLAY "SELECT PartNumber, PartName and SalesPrice".
B200-DISPLAY-ROW. DISPLAY " ". DISPLAY " Part Number: " PARTNUMBER. DISPLAY " Part Name: " PARTNAME. IF SALESPRICEIND < 0 DISPLAY " Sales Price is NULL" ELSE MOVE SALESPRICE TO DOLLARS DISPLAY " Sales Price: " DOLLARS. B200-EXIT. EXIT. $PAGE S100-STATUS-CHECK. IF SQLCODE < DEADLOCK MOVE 'Y' TO ABORT-FLAG. 49 5 PERFORM S200-SQL-EXPLAIN THRU S200-EXIT UNTIL SQLCODE = 0. S100-EXIT. EXIT. S200-SQL-EXPLAIN. EXEC SQL SQLEXPLAIN :SQLMESSAGE END-EXEC. DISPLAY SQLMESSAGE. S200-EXIT. EXIT. S300-SERIOUS-ERROR.
S400-SQL-ERROR. PERFORM S100-STATUS-CHECK THRU S100-EXIT. IF ELSE ABORT-FLAG = 'Y' PERFORM A500-TERMINATE-PROGRAM PERFORM A400-END-TRANSACTION THRU A400-EXIT GO TO B100-EXIT. S400-EXIT. EXIT. S500-SQL-WARNING. DISPLAY "SQL WARNING has occurred. The following row " "of data may not be valid:". PERFORM B200-DISPLAY-ROW THRU B200-EXIT. PERFORM A400-END-TRANSACTION THRU A400-EXIT. GO TO B100-EXIT. S500-EXIT. EXIT. S600-NOT-FOUND. DISPLAY " ". DISPLAY "Part Number not found!".
General Rules for Embedding SQL Embedded SQL commands must appear in certain locations within the COBOL program. Each embedded SQL command must be accompanied by a pre x and a su x and followed by punctuation appropriate to the location of the command in the program. Comments may be placed within an embedded command, and non-numeric literals in embedded commands may be continued from one line to another.
Punctuation The punctuation you use to terminate an embedded SQL command depends on its location in the program. In the DATA DIVISION, always terminate the SQL command with a period : EXEC SQL INCLUDE SQLCA END-EXEC. In the PROCEDURE DIVISION, terminate the SQL command with a period if it constitutes an entire COBOL sentence: EXEC SQL CONNECT TO 'PARTSDBE.SOMEGRP.SOMEACCT' END-EXEC.
ALLBASE/SQL Comments ALLBASE/SQL comments can be inserted in any line of an SQL statement, except the last line, by pre xing the comment character with at least one space followed by two hyphens followed by one space: EXEC SQL SELECT * FROM PurchDB.Parts WHERE SalesPrice > 500. END-EXEC. -- This code selects Parts Table values. The comment terminates at the end of the current line.
Declaring the SQLCA The SQLCA is an ALLBASE/SQL data structure that contains current information about a program's DBE session. Every ALLBASE/SQL COBOL program must contain an SQLCA declaration. When a program or subprogram starts a DBE session, the SQLCA declaration must be in its WORKING-STORAGE SECTION. If a subprogram called by such a program contains SQL commands to be executed in the same DBE session, the SQLCA declaration must also appear in the LINKAGE SECTION of the subprogram.
Starting a DBE Session As at 4 3 5 in Figure 3-1, in most application programs you embed the CONNECT command to start a DBE session in a program: EXEC SQL CONNECT TO 'DBEnvironmentName' END-EXEC. If autostart mode is ON at run time, this command starts a DBE session. If autostart mode is OFF, a DBA must issue a START DBE command before the program can be executed. Regardless of the autostart mode in e ect, the program user must have CONNECT and RUN authority for this command to execute.
Implicit Status Checking In the PROCEDURE DIVISION, you can use the WHENEVER command, as at 4 7 5 in Figure 3-1, to have ALLBASE/SQL examine SQLCA values and cause a speci c action to be taken. The WHENEVER command is a preprocessor directive that speci es the action to be taken if an error or warning condition occurs when each subsequent SQL command is executed: EXEC SQL WHENEVER SQLERROR | | | | | GO TO S400-SQL-ERROR END-EXEC.
Defining and Manipulating Data You embed data de nition and data manipulation commands in the PROCEDURE DIVISION.
In all non-dynamic data manipulation operations, you use host variables to pass data back and forth between your program and the DBEnvironment. Host variables can be used in the data manipulation commands wherever the syntax in the ALLBASE/SQL Reference Manual allows them. The SELECT command shown at 4 8 5 in Figure 3-1 retrieves the row from PURCHDB.PARTS that contains a part number matching the value in the host variable named in the WHERE clause (PARTNUMBER).
Obtaining ALLBASE/SQL Messages As shown at 4 10 5 in Figure 3-1, you use the SQLEXPLAIN command to obtain a message from the ALLBASE/SQL message catalog that describes the condition related to certain SQLCA values: EXEC SQL SQLEXPLAIN :SQLMESSAGE END-EXEC. ALLBASE/SQL puts a message from the ALLBASE/SQL message catalog into the host variable named SQLMESSAGE, and the program displays the message. Sometimes more than one message may be needed to completely describe how an SQL command executed.
4 Host Variables Host variables are variables used in SQL commands in the PROCEDURE DIVISION. They are used to pass the following information between an application program and ALLBASE/SQL: Data values. Null value indicators. String truncation indicators. Bulk processing rows to process. Dynamic commands. Savepoint numbers. Messages from the ALLBASE/SQL message catalog. DBEnvironment names. All host variables used in the PROCEDURE DIVISION of a COBOL program or subprogram are declared in the DATA DIVISION.
To hold null value indicators in these data manipulation commands: SELECT INSERT FETCH REFETCH UPDATE UPDATE WHERE CURRENT In queries to indicate string truncation and the string length before truncation To identify the starting row and the number of rows to process in the INTO clause of the following commands: BULK SELECT BULK INSERT To pass dynamic commands at run time with the following commands: PREPARE EXECUTE IMMEDIATE To hold savepoint numbers, which are used in the following commands: SAVEPOINT R
The COBOL preprocessor converts hyphens in host variable names to underscores (-) because ALLBASE/SQL names cannot contain hyphens. Thus, when you use a host variable name in conjunction with a minus sign, be sure to leave one intervening space between them: :NEWSALESPRICE Note - :OLDSALESPRICE ^ ^ |___|_ Leave at least one blank here! Even though hyphens are allowed in host variable names, they are not allowed in column names or names of other ALLBASE/SQL objects.
0 -1 >0 value is not null value is null string value is truncated; number indicates data length before truncation. In the INSERT, UPDATE, and UPDATE WHERE CURRENT commands, an indicator variable is an input host variable. The value you put in the indicator variable tells ALLBASE/SQL when to insert a null value in a column: >=0 <0 value is not null value is null The following SELECT command uses an indicator variable, PartNameInd, for data from the PartName column.
Bulk Processing Variables Bulk processing variables can be used with the BULK option of the SELECT or the INSERT command. When used with the BULK SELECT command, two input host variables may be named following the array name in the INTO clause to specify how ALLBASE/SQL should store the query result in the array: INTO :ArrayName [,:StartIndex [,:NumberOfRows]] The StartIndex value denotes at which array element the query result should start.
Declaring Host Variables If your program uses host variables in the PROCEDURE DIVISION, you must declare the host variables in the DATA DIVISION: If the host variable data is used only within a given program, declare host variables in the WORKING-STORAGE SECTION. If the host variable data is used in a calling program, declare host variables in the program's WORKING-STORAGE SECTION. Host variable data used in a called program or subprogram is declared in that program's LINKAGE SECTION.
DATA DIVISION. FILE SECTION. . . EXEC SQL BEGIN DECLARE SECTION END-EXEC. . . Declarations for host variables whose values . come from or go to an MPE XL le appear here. . EXEC SQL END DECLARE SECTION END-EXEC. . . . WORKING-STORAGE SECTION. . . EXEC SQL BEGIN DECLARE SECTION END-EXEC. . . Declarations for local host variables, including . those passed from a called program or subprogram, go here. . EXEC SQL END DECLARE SECTION END-EXEC. . . LINKAGE SECTION. . . EXEC SQL BEGIN DECLARE SECTION END-EXEC. . .
Declaring Variables for Data Types Table 4-2 summarizes how to write data description entries for host variables holding each type of ALLBASE/SQL data. Only the data descriptions shown in Table 4-2 are supported by the COBOL preprocessor. CHAR Data You can insert strings ranging from 1 to 3996 characters into a CHAR column. When ALLBASE/SQL assigns data to a CHAR host variable from a CHAR host variable, it adds blanks if necessary on the right of the string to ll up the accepting variable.
When ALLBASE/SQL copies data from REMARKS , it copies as many characters as you have speci ed in REMARKSLENGTH and stores the value in REMARKSLENGTH in a four-byte eld preceding the value in REMARKS . If the value in REMARKSLENGTH is an odd number, ALLBASE/SQL stores the number of characters speci ed, plus one space on the right; in this case, the value in REMARKSLENGTH is incremented by one and stored in the four-byte eld preceding the REMARKS value.
Table 4-1. ALLBASE/SQL Floating Point Column Specifications Possible Keywords REAL or FLOAT(n ) where n = 1 through 24 Range of Possible Values 03.402823 E+38 through 01.175495 E038 and 1.175495 E038 through 3.402823 E+38 and 0 Stored In and Boundary Aligned On 4 bytes DOUBLE PRECISION 01.79769313486231 E+308 through 02.22507385850721 E0308 8 bytes and or +2.22507385850721 E0308 through +1.79769313486231 E+308 FLOAT and or 0 FLOAT(n ) where n = 25 through 53 COBOL DECIMAL Data.
BINARY Data As with other data types, use the CREATE TABLE or ALTER TABLE command to de ne a binary or varbinary column. Up to 3996 bytes can be stored in such a column. Each byte contains two hexadecimal digits. For example, suppose you insert data via a host variable into a database column de ned as binary. The host variable contains the digits, 1234. In the database, these four digits are stored in two bytes. Each nibble (half byte) contains one digit in hexadecimal format.
Table 4-2. Host Variable Data Types SQL DATA TYPES CHAR(n) VARCHAR(n) BINARY VARBINARY(n) 01 DATA-NAME PIC X(n ). 01 GROUP-NAME . 49 LENGTH-NAME PIC S9(9) COMP. 49 VALUE-NAME PIC X(n ). 01 DATA-NAME PIC X(n ). 01 GROUP-NAME . 49 LENGTH-NAME PIC S9(9) COMP. 49 VALUE-NAME PIC X(n ). SMALLINT 01 DATA-NAME PIC S9(4) COMP. INTEGER 01 DATA-NAME PIC S9(9) COMP. FLOAT (DECIMAL(p,s)) 1 COBOL DATA DESCRIPTION ENTRIES 01 DATA-NAME PIC S9(p-s)V9(s) COMP-3. 1 DATE 01 DATA-NAME PIC X(10).
Table 4-3. Program Element Data Description Entries PROGRAM ELEMENT Indicator variable Array of n rows Data values Indicator variable StartIndex NumberOfRows COBOL DATA DECLARATIONS 01 IND-VAR-NAME SQLIND. 01 ARRAY-NAME . 05 ROW-NAME OCCURS n TIMES. 10 COLUMN1-NAME valid data clause . 10 COLUMN2-NAME valid data clause . 10 IND-VAR-NAME SQLIND. 01 START-INDEX-NAME PIC S9(4) COMP. or 01 START-INDEX-NAME PIC S9(9) COMP. 01 NUM-ROWS-NAME PIC S9(4) COMP. or 01 NUM-ROWS-NAME PIC S9(9) COMP.
DATE, TIME, DATETIME, and INTERVAL Data EXEC SQL BEGIN DECLARE SECTION END-EXEC. ** DATETIME DATA TYPE ** 01 BATCHSTAMP PIC X(23). ** DATE DATA TYPE ** 01 TESTDATE PIC X(10). 01 TESTDATEIND SQLIND. ** TIME DATA TYPE ** 01 TESTSTART PIC X(8). 01 TESTSTARTIND SQLIND. ** INTERVAL DATA TYPE ** 01 LABTIME PIC X(21). 01 LABTIMEIND SQLIND. EXEC SQL END DECLARE SECTION END-EXEC. *DECLARE and OPEN CURSOR C1 here. Nulls not allowed for BatchStamp.
EXEC 01 01 01 49 49 01 05 EXEC Source File SQL BEGIN DECLARE SECTION END-EXEC. ODDNUMBER PIC S9(7)V99 COMP-3. ODDCHAR PIC X(15). ODDVARCHAR. ODDVARCHARLEN PIC S9(9) COMP. ODDVARCHARVAL PIC X(15). BUFFER. BUFREC OCCURS 2 TIMES. 10 ODDCHARB PIC X(15). 10 ODDNUMBERB PIC S9(7)V99 C0MP-3. 10 ODDVARCHAR. 49 ODDLEN PIC S9(9) COMP. 49 ODDVAL PIC X(15). SQL END DECLARE SECTION END-EXEC. Modified Source File **** Start SQL Preprocessor **** *EXEC SQL BEGIN DECLARE SECTION END EXEC.
Using Default Data Values You can choose a default value other than NULL when you create or alter a table by using the DEFAULT speci cation. Then when data is inserted, and a given column is not in the insert list, the speci ed default value is inserted. Or when you alter a table, adding a column to existing rows, every occurrence of the column is initialized to the default value. (This conforms to ANSI SQL1 level 2 with addendum-1 and FIPS 127 standards.
CREATE PUBLIC TABLE PurchDB.Orders ( OrderNumber INTEGER DEFAULT 5 NOT NULL , VendorNumber INTEGER, OrderDate CHAR(8)) IN OrderFS NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN Coding Considerations Any default value must be compatible with the data type of its corresponding column. For example, when the default is an integer constant, the column for which it is the default must be created with an ALLBASE/SQL data type of INTEGER, REAL, or FLOAT.
Table 4-4.
isql=> INFO PURCHDB.ORDERITEMS; Column Name Data Type (length) Nulls Allowed --------------------------------------------------------------------ORDERNUMBER Integer NO ITEMNUMBER Integer NO VENDPARTNUMBER Char (16) YES PURCHASEPRICE Decimal (10,2) NO ORDERQTY SmallInt YES ITEMDUEDATE Char (8) YES RECEIVEDQTY SmallInt YES The example identi ed as Figure 4-3 is a query that accesses the PURCHDB.ORDERITEMS table. The query produces a single-row query result that consists of two maximum values.
DATA DIVISION. . . WORKING-STORAGE SECTION. EXEC SQL BEGIN DECLARE SECTION END-EXEC. . . 01 ORDERNUMBER PIC S9(9) COMP. 01 PURCHASEPRICE PIC S9(8)V9(2) COMP-3. 01 DISCOUNT PIC S9(8)V9(2) COMP-3. 01 ORDERQTY PIC S9(4) COMP. 01 ORDERQTYIND SQLIND. . . EXEC SQL END DECLARE SECTION END-EXEC. . . PROCEDURE DIVISION. . . EXEC SQL SELECT PURCHASEPRICE * :DISCOUNT, ORDERQTY, INTO :PURCHASEPRICE, :ORDERQTY :ORDERQTYIND, FROM PURCHDB.ORDERITEMS WHERE ORDERNUMBER = :ORDERNUMBER END-EXEC. Figure 4-3.
DATA DIVISION. . . . WORKING-STORAGE SECTION. EXEC SQL BEGIN DECLARE SECTION END-EXEC. . . . 01 DISCOUNT PIC S9(8)V9(2) COMP-3. 01 ORDERSARRAY. 05 EACH-ROW OCCURS 25 TIMES. 10 PURCHASEPRICE PIC S9(8)V9(2) COMP-3. 10 ORDERQTY PIC S9(4) COMP. 10 ORDERQTYIND SQLIND. 10 ORDERNUMBER PIC S9(9) COMP. 01 FIRSTROW PIC S9(4) COMP. 01 TOTALROWS PIC S9(4) COMP. 01 LOWVALUE PIC S9(9) COMP. 01 HIGHVALUE PIC S9(9) COMP. 01 GROUPCRITERION PIC S9(9) COMP. EXEC SQL END DECLARE SECTION END-EXEC. . . . PROCEDURE DIVISION. . .
String Data Conversion When ALLBASE/SQL moves string data of one type to a host variable declared as a compatible type, the following occurs: When moving CHAR data to a VARCHAR variable, ALLBASE/SQL places the length of the string in the appropriate 49-level variable and pads the string on the right with spaces to ll up the VARCHAR string variable. When moving VARCHAR data to a CHAR variable, ALLBASE/SQL pads the string on the right with spaces to ll up the CHAR string variable.
Numeric Data Conversion When you use numeric data of di erent types in an expression or comparison operation, data types with less precision are converted into data types of greater precision. The result has the greater precision. ALLBASE/SQL numeric types available in COBOL have the following precedence, from highest to lowest: 1. DECIMAL 2. INTEGER 3. SMALLINT The following example illustrates numeric type conversion: WORKING-STORAGE SECTION. EXEC SQL BEGIN DECLARE SECTION END-EXEC.
Declaring Variables for Program Elements The following section discusses how to declare elements speci c to ALLBASE/SQL programs. In addition, Table 4-3 provides template examples of these special elements. SQLCA Array Every ALLBASE/SQL COBOL program must have the SQL Communications Area (SQLCA) declared in the working storage section of the DATA DIVISION. You can use the INCLUDE command to declare the SQLCA: EXEC SQL INCLUDE SQLCA END-EXEC.
DATA DIVISION. . . . WORKING-STORAGE SECTION. EXEC SQL BEGIN DECLARE SECTION END-EXEC. . . . 01 DYNAMIC-COMMAND PIC X(2048). . . 01 DYNAMIC-COMMAND-2. 49 LENGTH PIC S9(9) COMP. 49 VALUE PIC X(80). . . . EXEC SQL END DECLARE SECTION END-EXEC. . . PROCEDURE DIVISION. . . EXEC SQL PREPARE COMMAND-ON-THE-FLY FROM :DYNAMIC-COMMAND END-EXEC. . . EXEC SQL PREPARE COMMAND-ON-THE-FLY FROM :DYNAMIC-COMMAND-2 END-EXEC. Figure 4-5.
Savepoint Numbers Savepoint numbers are positive numbers ranging from 1 to 2,147,483,647. A host variable for holding a savepoint number should be declared as an integer. DATA DIVISION. . . . WORKING-STORAGE SECTION. EXEC SQL BEGIN DECLARE SECTION END-EXEC. . . . 01 SAVEPOINT1 PIC S9(9) COMP. . . . EXEC SQL END DECLARE SECTION END-EXEC. . . PROCEDURE DIVISION. . . EXEC SQL SAVEPOINT :SAVEPOINT1 END-EXEC. Figure 4-6.
Messages from the Message Catalog The maximum size of a message catalog message is 256 bytes. Figure 4-7 illustrates how a host variable for holding a message might be declared. DATA DIVISION. . . . WORKING-STORAGE SECTION. EXEC SQL BEGIN DECLARE SECTION END-EXEC. . . . 01 STATUSMESSAGE. 49 MESSAGE-LENGTH PIC S9(4) COMP. 49 MESSAGE-TEXT PIC X(256). . . . EXEC SQL END DECLARE SECTION END-EXEC. . . . PROCEDURE DIVISION. . . . EXEC SQL SQLEXPLAIN :STATUSMESSAGE END-EXEC. DISPLAY MESSAGE-TEXT. Figure 4-7.
DBEnvironment Name The DBEnvironment you specify in the preprocessor command line is the same as the DBECon le name. The maximum length of a fully quali ed DBEnvironment name is 26 bytes. When used in a host variable, the DBEnvironment name can be unquoted or enclosed in single quotation marks. WORKING-STORAGE SECTION. EXEC SQL BEGIN DECLARE SECTION END-EXEC. . . . 01 SOMEDBE PIC X(26). . . . EXEC SQL END DECLARE SECTION END-EXEC. . . PROCEDURE DIVISION. . . DISPLAY "Enter DBEnvironment name> ".
NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN PROGRAM-ID. CallingProgram. . . WORKING-STORAGE SECTION. EXEC SQL INCLUDE SQLCA END-EXEC. 01 PARTNUMBER PIC X(16). . PROCEDURE DIVISION. . EXEC SQL CONNECT TO 'PARTSDBE.SOMEGRP.SOMEACCT' END-EXEC. . . IF RESPONSE-PREFIX = "1" THEN DISPLAY "INSERT rows into the Parts Table." CALL "INSERTsubpgm" USING SQLCA PARTNUMBER DISPLAY "Last row inserted had part number: " PARTNUMBER.
5 Runtime Status Checking and the SQLCA This chapter examines the need for runtime status checking. It describes the SQLCA and the conditions under which its data items are set by ALLBASE/SQL. It also gives several examples of implicit and explicit status checking, some of which use SQLEXPLAIN to display a status message. Examples of handling speci c status checking tasks are included under \Approaches to Status Checking.
When several errors or warnings occur, you can use SQLEXPLAIN to retrieve messages for all of them. Messages are available to your program with the most severe error appearing rst. When ALLBASE/SQL rolls back the current transaction, the message indicating the roll back will be the rst message, since it is the most severe. An example of this scenario is presented later in this chapter under \SQLCODE." Refer to the ALLBASE/SQL Message Manual for an explanation of all error and warning messages.
For example, in the sample database, PartsDBE, each order is de ned by rows in two tables: one row in the PurchDB.Orders table and one or more rows in the PurchDB.OrderItems table. A transaction that deletes orders from the database has to delete all the rows for a speci c order from both tables to maintain data consistency. A program containing such a transaction should commit work to the database only if it is able to delete the row from the PurchDB.
Using the SQLCA The SQL communications area is known as the SQLCA. Every ALLBASE/SQL COBOL program must declare the SQLCA by putting the INCLUDE SQLCA statement somewhere in the WORKING-STORAGE SECTION or the LINKAGE SECTION. WORKING-STORAGE SECTION. EXEC SQL INCLUDE SQLCA END-EXEC. The COBOL preprocessor generates the following declaration in SQLOUT after it parses this SQL command: WORKING STORAGE SECTION.
You may want to place the SQLCA declaration rst in the WORKING-STORAGE SECTION. That way, if you compile the program with range checking o , this vital data structure will not be inadvertently overwritten by array references beyond the limit of a previously declared array. As discussed in the previous chapter \Host Variables," the SQLCA must be passed whenever you call a subprogram that executes SQL commands in the same DBEnvironment. The following table gives an overview of how ALLBASE/SQL sets these elds.
SQLCODE SQLCODE can contain one of the following values: 0, when an SQL command executes without generating an error condition and without generating a no rows qualify condition. A negative number, when an error condition exists and an ALLBASE/SQL command cannot be executed.
To obtain all SQLCODEs associated with the execution of an SQL command, you execute the SQLEXPLAIN command until SQLCODE is 0: IF SQLCODE IS EQUAL TO 100 DISPLAY "No rows qualified for this operation." IF SQLCODE IS LESS THAN ZERO PERFORM SQL-STATUS-CHECK UNTIL SQLCODE IS ZERO. SQL-STATUS-CHECK. EXEC SQL SQLEXPLAIN :SQLMESSAGE END-EXEC. The paragraph named SQL-STATUS-CHECK is executed when SQLCODE is a negative number.
SQLERRD(3) SQLERRD(3) can contain one of the following values: 0, when SQLCODE is 100 or when one of the following commands causes an error condition: INSERT UPDATE DELETE UPDATE WHERE CURRENT DELETE WHERE CURRENT If an error occurs during execution of INSERT, UPDATE, or DELETE, one or more rows may have been processed prior to the error. In these cases, you may want to either COMMIT WORK or ROLLBACK WORK, depending on the transaction.
SQLWARN0 A W in SQLWARN0, in conjunction with a 0 in SQLCODE, indicates that the SQL command just executed caused a warning condition. Warning conditions ag unusual but not necessarily important conditions. For example, if a program attempts to submit an SQL command that grants an already existing authority, a message such as the following would be retrieved when SQLEXPLAIN is executed: User JOANN@GRAY already has DBA authorization.
SQLWARN1 A W in SQLWARN1 indicates truncation of at least one character string value when the string was stored in a host variable. Any associated indicator variable is set to the value of the string length before truncation. For example: EXEC SQL SELECT PartNumber, PartName INTO :PartNumber :PartName :PartNameInd FROM PurchDB.Parts WHERE PartNumber = :PartNumber; If PartName was declared as a character array of 20 bytes, and the PartName column in the PurchDB.
SQLWARN6 When an error occurs that causes ALLBASE/SQL to roll back the current transaction, SQLWARN6 is set to W. ALLBASE/SQL automatically rolls back transactions when SQLCODE is equal to -4008, or equal to or less than -14024. When such errors occur, ALLBASE/SQL: Sets SQLWARN6 to W Sets SQLWARN0 to W Sets SQLCODE to a negative number If you want to terminate your program any time ALLBASE/SQL has to roll back the current transaction, you can just test SQLWARN6.
Approaches to Status Checking This section presents examples of how to use implicit and explicit status checking and to notify program users of the results of status checking. Implicit status checking is useful when control to handle warnings and errors can be passed to one prede ned point in the program. Explicit status checking is useful when you want to test for speci c SQLCA values before passing control to one of several locations in your program.
NOT FOUND If WHENEVER NOT FOUND is in e ect, ALLBASE/SQL checks for the value 100 in SQLCODE after processing a SELECT or FETCH command. A WHENEVER command for each of these conditions can be in e ect at the same time. There are three possible WHENEVER actions: STOP If WHENEVER Condition STOP is in e ect, ALLBASE/SQL rolls back the current transaction and terminates the DBE session and the program when the Condition exists.
The code that the preprocessor generates depends on the condition and action in a WHENEVER command.
Implicitly Invoking Status-Checking Procedures The program illustrated in Figure 5-1 contains ve WHENEVER commands: The WHENEVER command numbered 415 handles errors associated with the following commands: CONNECT BEGIN WORK COMMIT WORK The WHENEVER commands numbered 425 through 445 handle warnings and errors associated with the SELECT command. The paragraph named S300-SERIOUS-ERROR is executed when an error occurs during the processing of session-related and transaction-related commands.
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *Program COBEX5: * *This program is the same as program COBEX2, except this * *program handles deadlocks differently. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * IDENTIFICATION DIVISION. PROGRAM-ID. AUTHOR. INSTALLATION. DATE-WRITTEN. DATE-COMPILED. REMARKS. COBEX5. HP TRAINING HP. 23 JULY 1987. 23 JULY 1987. SQL'S SELECT WITH WHENEVER COMMAND. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SOURCE-COMPUTER. HP-3000.
77 DONE-FLAG 88 NOT-DONE 88 DONE PIC X(01) VALUE "N". VALUE "N". VALUE "Y". 77 ABORT-FLAG 88 NOT-STOP 88 ABORT PIC X(01) VALUE "N". VALUE "N". VALUE "Y". 77 SQL-COMMAND-DONE-FLAG 88 NOT-SQL-CMD-DONE 88 SQL-COMMAND-DONE PIC X(01) VALUE "N". VALUE "N". VALUE "Y". 01 NOMEMORY 01 DEADLOCK PIC S9(9) COMP VALUE -4008. PIC S9(9) COMP VALUE -14024. 01 TRY-COUNTER 01 TRY-LIMIT PIC S9(4) COMP VALUE 0. PIC S9(4) COMP VALUE 3. 01 RESPONSE. 05 RESPONSE-PREFIX 05 FILLER PIC X(01) VALUE SPACE.
PERFORM A500-TERMINATE-PROGRAM THRU A500-EXIT. A100-EXIT. EXIT. A200-CONNECT-DBENVIRONMENT. EXEC SQL WHENEVER SQLERROR GO TO S300-SERIOUS-ERROR END-EXEC. 415 DISPLAY "Connect to PartsDBE". EXEC SQL CONNECT TO 'PartsDBE' END-EXEC. A200-EXIT. EXIT. A300-BEGIN-TRANSACTION. DISPLAY "Begin Work". EXEC SQL BEGIN WORK END-EXEC. A300-EXIT. EXIT. A400-END-TRANSACTION. DISPLAY "Commit Work". EXEC SQL COMMIT WORK END-EXEC. A400-EXIT. EXIT. A500-TERMINATE-PROGRAM. EXEC SQL RELEASE END-EXEC. STOP RUN. A500-EXIT. EXIT.
$PAGE B100-SELECT-DATA. MOVE SPACES TO RESPONSE. MOVE "Enter Part Number or '/' to STOP> " TO PROMPT. WRITE PROMPT AFTER ADVANCING 1 LINE. ACCEPT RESPONSE. IF RESPONSE-PREFIX = "/" MOVE "Y" TO DONE-FLAG GO TO B100-EXIT ELSE MOVE RESPONSE TO PARTNUMBER. EXEC SQL WHENEVER SQLERROR GO TO S100-SQL-ERROR END-EXEC. 425 EXEC SQL WHENEVER SQLWARNING GO TO S500-SQL-WARNING END-EXEC. 435 EXEC SQL WHENEVER NOT FOUND GO TO S600-NOT-FOUND END-EXEC. 445 MOVE "N" TO SQL-COMMAND-DONE-FLAG. MOVE 0 TO TRY-COUNTER.
PERFORM A300-BEGIN-TRANSACTION THRU A300-EXIT. EXEC SQL SELECT PARTNUMBER, PARTNAME, SALESPRICE INTO :PARTNUMBER, :PARTNAME, :SALESPRICE :SALESPRICEIND FROM PURCHDB.PARTS WHERE PARTNUMBER = :PARTNUMBER END-EXEC. IF SQL-COMMAND-DONE GO TO B110-EXIT. PERFORM A400-END-TRANSACTION THRU A400-EXIT PERFORM B200-DISPLAY-ROW THRU B200-EXIT. MOVE "Y" TO SQL-COMMAND-DONE-FLAG. B110-EXIT. EXIT. B200-DISPLAY-ROW. DISPLAY " ". DISPLAY " Part Number: DISPLAY " Part Name: IF ELSE " PARTNUMBER. " PARTNAME.
IF ELSE TRY-COUNTER = TRY-LIMIT MOVE "Y" TO SQL-COMMAND-DONE-FLAG DISPLAY "Deadlock occurred, or not enough shared " "memory. You may want to try again." GO TO B110-EXIT GO TO B110-EXIT ELSE PERFORM S200-SQL-EXPLAIN THRU S200-EXIT PERFORM A500-TERMINATE-PROGRAM. S100-EXIT. EXIT. S200-SQL-EXPLAIN. EXEC SQL SQLEXPLAIN END-EXEC. :SQLMESSAGE DISPLAY SQLMESSAGE. S200-EXIT. EXIT. S300-SERIOUS-ERROR. PERFORM S200-SQL-EXPLAIN THRU PERFORM A500-TERMINATE-PROGRAM S200-EXIT. THRU A500-EXIT. S300-EXIT. EXIT.
S600-NOT-FOUND. DISPLAY " ". DISPLAY "Part Number not found!". PERFORM A400-END-TRANSACTION THRU A400-EXIT. MOVE "Y" TO SQL-COMMAND-DONE-FLAG GO TO B110-EXIT. S600-EXIT. EXIT. Figure 5-1.
Explicit Status Checking Techniques With explicit status checking, you invoke a paragraph after explicitly checking SQLCA values rather than using the WHENEVER command. The program in Figure 5-1 has already illustrated several uses of explicit status checking to: Isolate errors so critical that they caused ALLBASE/SQL to roll back the current transaction. Control the number of times SQLEXPLAIN is executed. Detect when more than one row quali es for the SELECT operation.
01 01 01 01 01 . . . . DM. OK NOTFOUND DEADLOCK MULTIPLE-ROWS NOMEMORY PIC PIC PIC PIC PIC S9(9) S9(9) S9(9) S9(9) S9(9) COMP COMP COMP COMP COMP VALUE VALUE VALUE VALUE VALUE 0. 100. -14024. -1002. -4008. PERFORM DM THRU DM-EXIT UNTIL DONE. This paragraph prompts for a number that indicates whether the user wants to SELECT, UPDATE, DELETE, or INSERT rows, then invokes a paragraph that accomplishes the selected activity. The DONE ag is set when the user enters a slash. DM-EXIT. . . . INSERT-DATA.
DISPLAY-UPDATE. Sentences that prompt the user for new data appear here. . . . EXEC SQL UPDATE PURCHDB.PARTS SET PARTNAME = :PARTNAME, SALESPRICE = :SALESPRICE, WHERE PARTNUMBER = :PARTNUMBER END-EXEC. IF SQLCODE NOT = OK PERFORM S100-SQL-ERROR THRU S100-EXIT. DELETE-DATA. This paragraph veri es that the row(s) to be deleted exist, then invokes paragraph DISPLAY-DELETE to delete the row(s). . . . EXEC SQL SELECT PARTNUMBER, PARTNAME, SALESPRICE INTO :PARTNUMBER, :PARTNAME, :SALESPRICE FROM PURCHDB.
. SELECT-DATA. Sentences that prompt for a partnumber appear here. EXEC SQL SELECT PARTNUMBER, PARTNAME, SALESPRICE INTO :PARTNUMBER, :PARTNAME, :SALESPRICE FROM PURCHDB.PARTS WHERE PARTNUMBER = :PARTNUMBER END-EXEC. IF SQLCODE = OK PERFORM DISPLAY-ROW. IF SQLCODE NOT = OK PERFORM S100-SQL-ERROR THRU S100-EXIT. . S100-SQL-ERROR. IF SQLCODE = NOT-FOUND DISPLAY "Part Number not found!" PERFORM A400-END-TRANSACTION THRU A400-EXIT GO TO S100-EXIT.
Handling Deadlock and Shared Memory Problems A deadlock exists when two transactions need data that the other transaction already has locked. When a deadlock occurs, ALLBASE/SQL rolls back the transaction with the larger priority number. If two deadlocked transactions have the same priority, ALLBASE/SQL rolls back the newer transaction. An SQLCODE of -14024 indicates that a deadlock has occurred: Deadlock detected.
INSERT, UPDATE, and DELETE Operations. The example in Figure 5-2 could be modi ed to display the number of rows inserted, updated, or deleted by using SQLERRD(3). In the case of the update operation, for example, the actual number of rows updated could be displayed after the UPDATE command is executed: WORKING-STORAGE SECTION. . . . 01 OK PIC S9(9) COMP VALUE 0. 01 NUMBER-OF-ROWS PIC X(4). . . . PROCEDURE DIVISION. . . . DISPLAY-UPDATE. Sentences that prompt user for new data appear here.
In the code identi ed as 435, the value in SQLERRD(3) is used to control the number of times procedure DISPLAY-ROW is executed.
WORKING-STORAGE SECTION. EXEC SQL INCLUDE SQLCA END-EXEC. EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 PARTSTABLE. 05 TABLE-ELEMENT OCCURS 25 TIMES. 10 PARTNUMBER PIC X(16). 10 PARTNAME PIC X(30). 10 SALESPRICE PIC S9(8)V99 COMP-3. 01 ERRORMESSAGE PIC X(132). EXEC SQL END DECLARE SECTION END-EXEC. 01 OK PIC S9(9) COMP VALUE 0. 01 NOTFOUND PIC S9(9) COMP VALUE 100. 01 MAXIMUMROWS PIC S9(9) COMP VALUE 25. 01 I PIC S9(9) COMP. 01 NUMBER-OF-ROWS PIC X(4). 01 DOLLARS PIC $$$,$$$,$$$.99. . . . PROCEDURE DIVISION.
. . . DISPLAY-TABLE. IF SQLERRD(3) = MAXIMUMROWS; 425 DISPLAY " "; DISPLAY "WARNING: There may be additional rows that qualify!" The column headings are displayed here. PERFORM DISPLAY-ROW VARYING I FROM 1 BY 1 UNTIL I > SQLERRD(3). DISPLAY " ". 435 DISPLAY-ROW. MOVE SALESPRICE(I) TO DOLLARS. DISPLAY PARTNUMBER(I), "|", PARTNAME(I), "|", SALESPRICE(I), "|", DOLLARS. Figure 5-3.
Detecting End of Scan Previous examples in this chapter have illustrated how an SQLCODE of 100 can be detected and handled for data manipulation commands that do not use a cursor. When a cursor is being used, this SQLCODE value is used to determine when all rows in an active set have been fetched: . . . EXEC SQL OPEN CURSOR1 END-EXEC. . . . PERFORM FETCH-ROW THRU FETCH-ROW-EXIT UNTIL DONE-FETCH. . . . FETCH-ROW. EXEC SQL FETCH CURSOR1 INTO :PARTNUMBER, :PARTNAME, :SALESPRICE END-EXEC.
Determining When More Than One Row Qualifies If more than one row quali es for a non-BULK SELECT or FETCH operation, ALLBASE/SQL sets SQLCODE to -10002. In the following example, when SQLCODE is MULTIPLEROWS (de ned as -10002 in WORKING-STORAGE), a status-checking paragraph is not invoked; instead a warning message is displayed: UPDATE-DATA. This paragraph veri es that the row(s) to be changed exist, then invokes paragraph DISPLAY-UPDATE to accept new data from the user.
Handling Out of Space Conditions It is possible that data or index space may be exhausted in a DBEFileSet. This could happen as rows are being added or an index is being created or when executing queries which require that data be sorted. Your program can detect the problem, and the DBA must add index or data space to the appropriate DBEFileSet. SQLEXPLAIN retrieves the following message: Data or Index space exhaused in DBEFileSet.
6 Overview Of Data Manipulation To manipulate data in an ALLBASE/SQL DBEnvironment, you use one of the following SQL commands: SELECT: to retrieve one or more rows from one or more tables. INSERT: to insert one or more rows into a single table. DELETE: to delete one or more rows from a single table. UPDATE: to change the value of one or more columns in one or more rows in a single table.
Table 6-1. How Data Manipulation Commands May Be Used USABLE SQL COMMANNDS TYPE OF OPERATION SELECT FETCH Simple X Sequential X X BULK X X Dynamic INSERT DELETE UPDATE X X X DELETE UPDATE WHERE WHERE CURRENT CURRENT X X X X X X The remainder of this chapter brie y examines each of the four data manipulation techniques (each technique is discussed in detail in Chapters 7 through 10) and introduces the use of a cursor for data manipulation.
To retrieve all data from a table, the SELECT command need specify only the following: EXEC SQL BULK SELECT * INTO :MYARRAY FROM PURCHDB.PARTS END-EXEC. Although the shorthand notation * can be used in the select list to indicate you want all columns from one or more tables or views, it is better programming practice to explicitly name columns.
When you use an aggregate function in the select list, you can use the GROUP BY clause to indicate how ALLBASE/SQL should group rows before applying the function. You can also use the HAVING clause to limit the groups to only those satisfying certain criteria. The following SELECT command will produce a query result containing two columns: a sales price and a number indicating how many parts have that price: EXEC SQL BULK SELECT END-EXEC.
Selecting from Multiple Tables To retrieve data from more than one table or view, the query describes to ALLBASE/SQL how to join the tables before deriving the query result: In the FROM clause, you identify the tables and views to be joined. In the WHERE clause, you specify a join condition . A join condition de nes the condition(s) under which rows should be joined.
Figure 6-1. Sample Query Joining Multiple Tables You can also join a table to itself . This type of join is useful when you want to identify values within one table that have certain relationships.
The PURCHDB.SUPPLYPRICE table contains the unit price, delivery time, and other data for every vendor that supplies any part. Most parts are supplied by more than one vendor, and prices vary with vendor. You can join the PURCHDB.SUPPLYPRICE table to itself in order to identify for which parts the di erence among vendor prices is greater than $50. The query and its result would appear as follows: EXEC SQL BULK SELECT END-EXEC. X.PARTNUMBER, X.VENDORNUMBER, X.UNITPRICE, Y.VENDORNUMBER, Y.
Selecting Using Views Views are used to restrict data visibility as well as to simplify data access: Data visibility can be limited using views by de ning them such that only certain columns and/or rows are accessible through them. Data access can be simpli ed using views by creating views based on joins or containing columns that are derived from expressions or aggregate functions. The sample database has a view called PURCHDB.VENDORSTATISTICS, de ned as follows: CREATE VIEW PURCHDB.
The VENDORSTATISTICS view can be used to quickly determine the total dollar amount of orders existing for each vendor. Because the view de nition contains all the details for deriving this information, the query based on this view is quite simple: EXEC SQL SELECT VENDORNUMBER, SUM(TOTALPRICE) INTO :MYARRAY FROM PURCHDB.VENDORSTATISTICS GROUP BY VENDORNUMBER END-EXEC. The query result appears as follows: ------------+---------------------VENDORNUMBER|(EXPR) ------------+---------------------9001| 31300.
Simple Data Manipulation In simple data manipulation, you retrieve or insert single rows or update one or more rows based on a speci c criterion. In most cases, the simple data manipulation technique is used to support the random retrieval and/or change of speci c rows. In the following example, if the user wants to perform a DELETE operation, the program performs the operation only if a single row quali es. If no rows qualify or if more than one row quali es, the program displays a message.
END-EXEC. Chapter 7 provides more details about simple data manipulation.
Introducing The Cursor You use a cursor to manage a query result that may contain more than one row when you want to make all the qualifying rows available to the program user. Cursors are used in sequential table processing and BULK table processing, as shown later in this chapter. Like the cursor on a terminal screen, an ALLBASE/SQL cursor is a position indicator. It does not, however, point to a column. Rather, it points to one row in an active set.
Rows in the active set are returned to output host variables when the FETCH command is executed: EXEC SQL OPEN CURSOR1 END-EXEC. . . The OPEN command allocates internal . bu er space for the active set. . EXEC SQL [BULK] FETCH CURSOR1 INTO OutputHostVariables END-EXEC. The FETCH command delivers one row or (if the BULK option is used) multiple rows of the active set into output host variables.
EXEC SQL DECLARE CURSOR2 CURSOR FOR SELECT PARTNAME, SALESPRICE FROM PURCHDB.PARTS WHERE PARTNUMBER BETWEEN :LOWVALUE AND :HIGHVALUE FOR UPDATE OF SALESPRICE END-EXEC. . . Because the DECLARE CURSOR command is not . executed at run time, no status checking code . needs to appear here. . EXEC SQL OPEN CURSOR2 END-EXEC. . . The program fetches and displays one row at a time. . EXEC SQL FETCH CURSOR2 INTO :PARTNAME :PARTNAMEIND, :SALESPRICE :SALESPRICEIND END-EXEC. . .
After the last row in the active set has been fetched, the cursor is positioned after the last row fetched and the value in SQLCODE is equal to 100. Therefore to retrieve all rows in the active set, you execute the FETCH command until SQLCODE = 100. In the following example, a ag named DONE-FETCH is set to X after the last row in the active set has been fetched, and fetching stops: 77 88 88 DONE-FETCH-FLAG NOT-DONE-FETCH DONE-FETCH PIC X VALUE SPACE. VALUE SPACE. VALUE 'X'. . . . PROCEDURE DIVISION. . .
Figure 6-2. Effect of SQL Commands on Cursor and Active Sets Chapter 8 contains more detailed information about using cursors. See Chapter 11 for examples of using the KEEP CURSOR option of the OPEN command.
Sequential Table Processing In sequential table processing, you process an active set by fetching a row at a time and optionally deleting or updating it. Sequential table processing is useful when the likelihood of row changes throughout a set of rows is high and when a program user does not need to review multiple rows to decide whether to change a speci c row. In the following example, rows for parts having the same SALESPRICE are displayed one at a time.
ELSE PERFORM SQL-STATUS-CHECK.
DISPLAY-ROW. EXEC SQL FETCH PRICECURSOR INTO :PARTNUMBER, :PARTNAME PARTNAMEIND, :SALESPRICE SALESPRICEIND END-EXEC. . . If all rows have not been fetched, the next . row in the active set is displayed. Depending on . the user's response to a program prompt, the row may . be deleted or its SALESPRICE value changed. . IF RESPONSE = '/' GO TO DISPLAY-ROW-EXIT ELSE IF RESPONSE = 'D' EXEC SQL DELETE FROM PURCHDB.PARTS WHERE CURRENT OF PRICECURSOR END-EXEC . . Status checking code appears here. .
BULK Table Processing BULK table processing o ers a way to retrieve or insert multiple rows with the execution of a single SQL command. Three commands can be used in this fashion: You can use the BULK SELECT command when you know in advance the maximum number of rows in a multiple-row query result, as when the query result will contain a row for each month of the year or day of the week.
END-EXEC. . . . EXEC SQL OPEN PARTSCURSOR END-EXEC. IF SQLCODE = OK PERFORM DISPLAY-TABLE THRU DISPLAY-TABLE-EXIT UNTIL SQLCODE = NOTFOUND ELSE IF SQLCODE = NOTFOUND DISPLAY "The PurchDB.Parts table is empty!" ELSE PERFORM SQL-STATUS-CHECK. DISPLAY-TABLE. The STARTINDEX and NUMBEROFROWS host variables are initialized, then the BULK FETCH command is executed. MOVE 1 TO STARTINDEX. MOVE MAXIMUMROWS TO NUMBEROFROWS. EXEC SQL BULK FETCH PARTSCURSOR INTO :PARTSTABLE, :STARTINDEX, :NUMBEROFROWS END-EXEC.
Dynamic Operations Dynamic operations o er a way to execute SQL commands that cannot be completely de ned until run time. You accept part or all of an SQL command that can be dynamically preprocessed from the user, then use one of the following techniques to preprocess and execute the command: You can use the PREPARE command to preprocess a command, then execute it later during the same transaction using the EXECUTE command. The PREPARE and EXECUTE commands must be in the same program or subprogram.
7 Simple Data Manipulation Simple data manipulation is a programming technique used to SELECT or INSERT a single row. It can also be used to INSERT, DELETE, or UPDATE one or more rows based on a speci c criterion . These types of data manipulation operations are considered simple because they can be done with SQL data manipulation commands that: Do not contain the BULK option; therefore the host variables used are not arrays, and data references are simpli ed.
Note that the GROUP BY, HAVING, and ORDER BY clauses are not necessary, since these clauses usually describe multiple-row query results. You may omit the WHERE clause from certain queries when the select list contains only aggregate functions: EXEC SQL SELECT AVG(SALESPRICE) INTO :AVGSALESPRICE FROM PURCHDB.PARTS END-EXEC. A WHERE clause may be used, however, to qualify the rows over which the aggregate function is applied: EXEC SQL SELECT INTO FROM WHERE END-EXEC. AVG(SALESPRICE) :AVGSALESPRICE PURCHDB.
When a column named in the WHERE clause has a unique index on it, you can omit testing for multiple-row query results if the column was de ned NOT NULL. A unique index prevents the key column(s) from having duplicate values. The following index, for example, ensures that only one row will exist for any part number in PURCHDB.PARTS: CREATE UNIQUE INDEX PARTNUMINDEX ON PURCHDB.
To prohibit the multiple-row changes possible if multiple rows qualify for an UPDATE or DELETE operation, an application can use the SELECT command. If multiple rows qualify for the SELECT operation, the UPDATE or DELETE would not be executed.
Remember that when you do include column names but do not name all the columns in the target table, ALLBASE/SQL attempts to insert a null value into each unnamed column. If an unnamed column was de ned as NOT NULL, the INSERT command fails.
EXEC SQL UPDATE PURCHDB.VENDORS SET CONTACTNAME = :CONTACTNAME :CONTACTNAMEIND, VENDORSTREET = :VENDORSTREET, VENDORCITY = :VENDORCITY, VENDORSTATE = :VENDORSTATE, VENDORZIPCODE = :VENDORZIPCODE WHERE VENDORNUMBER = :VENDORNUMBER END-EXEC. In this example, column CONTACTNAME can contain a null value. To insert a null value, the program must assign a number less than 0 to the indicator variable for this column, CONTACTNAMEIND: The program prompts the user for new values for the four columns.
DELETE In simple data manipulation, you use the DELETE command to delete one or more rows from a table: DELETE FROM TableName WHERE SearchCondition The WHERE clause speci es a SearchCondition that rows must meet to be deleted, for example: EXEC SQL DELETE FROM PURCHDB.ORDERS WHERE ORDERDATE < :ORDERDATE END-EXEC. If the WHERE clause is omitted, all rows in the table are deleted.
The program displays the row, then prompts for another order number.
Because SELECT commands are often executed prior to a related UPDATE, DELETE, or INSERT command, you must decide whether to make each command a separate transaction or combine commands within one transaction. And you must decide which isolation level to use to attain your desired data consistency and to minimize possible lock contention.
In the following example, the DELETE and INSERT commands are used in place of the UPDATE command to insert null values into the target table. EXEC SQL BEGIN WORK END-EXEC. The DELETE command is executed. If the DELETE command fails, the transaction can be terminated as follows: EXEC SQL COMMIT WORK END-EXEC. If the DELETE command succeeds, the INSERT command is executed. If the INSERT command fails, the transaction is terminated as follows: EXEC SQL ROLLBACK WORK END-EXEC.
Sample Program COBEX7 Using Simple DML Commands The ow chart shown in Figure 7-1 summarizes the functionality of program COBEX7. This program uses the four simple data manipulation commands to operate on the PURCHDB.VENDORS table. COBEX7 uses a function menu to determine whether to execute one or more SELECT, UPDATE, DELETE, or INSERT operations. Each execution of a simple data manipulation command is done in a separate transaction.
If the SELECT command execution results in an error condition, paragraph S100-SQLSTATUS-CHECK is executed 4 14 5. This paragraph executes SQLEXPLAIN 4 51 5 to display all error messages. Then the transaction is terminated 4 15 5 and the user re-prompted for a vendor number or a 0. If the SELECT command can be successfully executed, paragraph D100-DISPLAY-ROW 4 12 5 is executed to display the row.
Paragraph C300-DELETE-DATA 4 31 5 lets the user DELETE one row. The paragraph prompts for a vendor number or a 0 4 32 5. If a 0 is entered, the function menu is re-displayed. If a vendor number is entered, paragraph A300-BEGIN-TRANSACTION is executed 4 33 5. Then a SELECT command is executed to retrieve all data for the vendor speci ed from PURCHDB.
Figure 7-1.
Figure 7-1.
:RUN COBEX7P Program for Simple Data Manipulation of Vendors Table - COBEX7 Connect to PartsDBE 1 2 3 4 . . . . . . . . . . . . . . . . SELECT UPDATE DELETE INSERT rows rows rows rows from with from into PurchDB.Vendors table null values in PurchDB.Vendors table PuchDB.Vendors table PurchDB.Vendors table Enter choice or 0 to stop> 4 *** Procedure to INSERT rows into PurchDB.
*** Procedure to SELECT rows from PurchDB.Vendors *** Enter Vendor Number or 0 for MENU> 9016 Begin Work SELECT * from PurchDB.Vendors VendorNumber: 9016 VendorName: Wolfe Works ContactName: Stanley Wolfe PhoneNumber: 408 975 6061 VendorStreet: 7614 Canine Way VendorCity: San Jose VendorState: CA VendorZipCode: 90016 VendorRemarks is NULL Commit Work Enter Vendor Number or 0 for MENU> 0 1 2 3 4 . . . . . . . . . . . . SELECT UPDATE DELETE INSERT rows rows rows rows from with from into PurchDB.
Enter Vendor Number or 0 for MENU> 0 1 2 3 4 . . . . . . . . . . . . SELECT UPDATE DELETE INSERT rows rows rows rows from with from into PurchDB.Vendors table null values in PurchDB.Vendors table PurchDB.Vendors table PurchDB.Vendors table Enter choice or 0 to STOP> 3 *** Procedure to DELETE rows from PurchDB.Vendors *** Enter Vendor Number or 0 for MENU> 9016 Begin Work SELECT * from PurchDB.
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * This program illustrates simple data manipulation. It uses * the UPDATE command with indicator variables to update any * row in the Vendors table that contains null values. It * also uses indicator variables in conjunction with SELECT * and INSERT. The DELETE command is also illustrated. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * IDENTIFICATION DIVISION. PROGRAM-ID. COBEX7. AUTHOR.
* * * * * * BEGIN HOST VARIABLE DECLARATIONS * * * * * * * EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 VENDORNUMBER PIC S9(4) COMP. 01 VENDORNAME PIC X(30). 01 CONTACTNAME PIC X(30). 01 CONTACTNAMEIND SQLIND. 01 PHONENUMBER PIC X(15). 01 PHONENUMBERIND SQLIND. 01 VENDORSTREET PIC X(30). 01 VENDORCITY PIC X(20). 01 VENDORSTATE PIC X(2). 01 VENDORZIPCODE PIC X(10). 01 VENDORREMARKS. 49 REMARKSLENGTH PIC S9(9) COMP. 49 REMARKS-DATA PIC X(60). 01 VENDORREMARKSIND SQLIND. 01 SQLMESSAGE PIC X(132).
A100-MAIN. - DISPLAY "Program for Simple Data Manipulation of Vendors Tabl "e - COBEX7" DISPLAY " ". OPEN OUTPUT CRT. PERFORM A200-CONNECT-DBENVIRONMENT THRU A200-EXIT. 41 5 PERFORM B100-DISPLAY-MENU THRU B100-EXIT UNTIL DONE. PERFORM A500-TERMINATE-PROGRAM THRU A500-EXIT. 42 5 A100-EXIT. EXIT. A200-CONNECT-DBENVIRONMENT. DISPLAY "Connect to PartsDBE". EXEC SQL CONNECT TO 'PartsDBE' END-EXEC.
A300-EXIT. EXIT. A400-COMMIT-WORK. DISPLAY " ". DISPLAY "Commit Work". EXEC SQL COMMIT WORK END-EXEC. 45 5 IF SQLCODE NOT = OK PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT PERFORM A500-TERMINATE-PROGRAM THRU A500-EXIT. A400-EXIT. EXIT. A500-TERMINATE-PROGRAM. EXEC SQL RELEASE END-EXEC. STOP RUN. A500-EXIT. EXIT. B100-DISPLAY-MENU. DISPLAY " 1 DISPLAY " 2 "in DISPLAY " 3 DISPLAY " 4 MOVE "Enter . . . SELECT rows from PurchDB.Vendors table ". . . . UPDATE rows with null values " PurchDB.Vendors table ". .
WRITE PROMPT AFTER ADVANCING 1 LINE. ACCEPT RESPONSE1 FREE. IF RESPONSE1 = ZERO MOVE "X" TO DONE-FLAG GO TO B100-EXIT. MOVE SPACES TO FUNC-DONE-FLAG. 47 5 - IF RESPONSE1 = 1 DISPLAY " " DISPLAY " *** Procedure to SELECT rows from PurchDB.Vendo "rs *** " DISPLAY " " PERFORM C100-SELECT-DATA THRU C100-EXIT UNTIL FUNC-DONE. - IF RESPONSE1 = 2 DISPLAY " " DISPLAY " *** Procedure to UPDATE rows in PurchDB.Vendors " *** " DISPLAY " " PERFORM C200-UPDATE-DATA THRU C200-EXIT UNTIL FUNC-DONE.
IF RESPONSE1 NOT AND RESPONSE1 AND RESPONSE1 AND RESPONSE1 AND RESPONSE1 = 0 NOT NOT NOT NOT = = = = 1 2 3 4 DISPLAY "Enter 0-4 only, please". B100-EXIT. C100-SELECT-DATA. MOVE "Enter VendorNumber or 0 for MENU> " TO PROMPT. WRITE PROMPT. ACCEPT RESPONSE1 FREE. IF RESPONSE1 = ZERO MOVE "X" TO FUNC-DONE-FLAG GO TO C100-EXIT ELSE MOVE RESPONSE1 TO VENDORNUMBER. PERFORM A300-BEGIN-TRANSACTION THRU A300-EXIT. 48 5 49 5 4 10 5 DISPLAY "SELECT * from PurchDB.Vendors".
C200-UPDATE-DATA. 4 16 5 MOVE "Enter VendorNumber or 0 for MENU> " TO PROMPT. DISPLAY " ". WRITE PROMPT. ACCEPT RESPONSE1 FREE. IF RESPONSE1 = ZERO MOVE "X" TO FUNC-DONE-FLAG GO TO C200-EXIT ELSE MOVE RESPONSE1 TO VENDORNUMBER. 4 17 5 PERFORM A300-BEGIN-TRANSACTION THRU A300-EXIT. 4 18 5 DISPLAY "SELECT * from PurchDB.Vendors". PERFORM D200-SQL-SELECT THRU D200-EXIT.
IF CONTACTNAMEIND < 0 MOVE SPACES TO CONTACTNAME MOVE "Enter New ContactName (0 for NULL)> " TO PROMPT WRITE PROMPT ACCEPT CONTACTNAME FREE. 4 27 5 4 28 5 IF PHONENUMBERIND < 0 MOVE SPACES TO PHONENUMBER MOVE "Enter New PhoneNumber (0 for NULL)> " TO PROMPT WRITE PROMPT ACCEPT PHONENUMBER FREE. IF VENDORREMARKSIND < 0 MOVE SPACES TO VENDORREMARKS MOVE "Enter New VendorRemarks (0 for NULL)> " TO PROMPT WRITE PROMPT ACCEPT REMARKS FREE.
IF SQLCODE NOT = OK PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT. C250-EXIT. EXIT. C300-DELETE-DATA. 4 31 5 MOVE "Enter VendorNumber or 0 for MENU> " TO PROMPT. WRITE PROMPT. ACCEPT RESPONSE1 FREE. IF RESPONSE1 = ZERO MOVE "X" TO FUNC-DONE-FLAG GO TO C300-EXIT ELSE MOVE RESPONSE1 TO VENDORNUMBER. 4 32 5 PERFORM A300-BEGIN-TRANSACTION THRU A300-EXIT. 4 33 5 DISPLAY "SELECT * from PurchDB.Vendors". PERFORM D200-SQL-SELECT THRU D200-EXIT.
IF RESPONSE-PREFIX = "Y" OR RESPONSE-PREFIX = "y" DISPLAY "DELETE row from PurchDB.Vendors" EXEC SQL DELETE FROM PURCHDB.VENDORS WHERE VENDORNUMBER = :VENDORNUMBER END-EXEC. 4 41 5 IF SQLCODE NOT = OK PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT. C350-EXIT. C400-INSERT-DATA. 4 42 5 MOVE "Enter Vendor Number or 0 for MENU> " TO PROMPT. WRITE PROMPT. ACCEPT RESPONSE1 FREE. IF RESPONSE1 = ZERO MOVE "X" TO FUNC-DONE-FLAG GO TO C400-EXIT ELSE MOVE RESPONSE1 TO VENDORNUMBER.
MOVE "Enter Phone Number (0 for null)> MOVE SPACES TO PHONENUMBER. WRITE PROMPT. ACCEPT PHONENUMBER FREE. IF PHONENUMBER = 0 MOVE -1 TO PHONENUMBERIND ELSE MOVE 0 TO PHONENUMBERIND. " TO PROMPT. MOVE "Enter Vendor Street> " TO PROMPT. MOVE SPACES TO VENDORSTREET. WRITE PROMPT. ACCEPT VENDORSTREET FREE. MOVE "Enter Vendor City> " TO PROMPT. MOVE SPACES TO VENDORCITY. WRITE PROMPT. ACCEPT VENDORCITY FREE. MOVE "Enter Vendor State> " TO PROMPT. MOVE SPACES TO VENDORSTATE. WRITE PROMPT.
IF VENDORREMARKSIND = 0 MOVE 0 TO COUNTER INSPECT VENDORREMARKS TALLYING COUNTER FOR CHARACTERS BEFORE INITIAL " " MOVE COUNTER TO REMARKSLENGTH. PERFORM A300-BEGIN-TRANSACTION THRU A300-EXIT. 4 46 5 DISPLAY "INSERT row into PurchDB.Vendors". EXEC SQL INSERT INTO PURCHDB.
IF CONTACTNAMEIND < 0 DISPLAY " ContactName is NULL" ELSE DISPLAY " ContactName: " CONTACTNAME. IF PHONENUMBERIND < 0 DISPLAY " PhoneNumber is NULL" ELSE DISPLAY " PhoneNumber: " PHONENUMBER. DISPLAY " VendorStreet: " VENDORSTREET. DISPLAY " VendorCity: " VENDORCITY. DISPLAY " VendorState: " VENDORSTATE. DISPLAY " VendorZipCode: " VENDORZIPCODE. IF VENDORREMARKSIND < 0 DISPLAY " VendorRemarks is NULL" ELSE DISPLAY " VendorRemarks: " REMARKS. 4 49 5 D100-EXIT. EXIT. D200-SQL-SELECT.
S100-SQL-STATUS-CHECK. IF SQLCODE < DEADLOCK MOVE 'X' TO ABORT-FLAG. PERFORM S200-SQLEXPLAIN UNTIL SQLCODE = 0. IF ABORT PERFORM A500-TERMINATE-PROGRAM THRU A500-EXIT. S100-EXIT. EXIT. S200-SQLEXPLAIN. EXEC SQL SQLEXPLAIN :SQLMESSAGE END-EXEC. DISPLAY SQLMESSAGE. S200-EXIT. EXIT. Figure 7-3.
8 Processing with Cursors Processing with cursors gives you the option of operating on a multiple-row query result, one row at a time. The query result is referred to as an active set. You use a pointer called a cursor to move through the active set, retrieving a row at a time into host variables and optionally updating or deleting the row. Reporting applications may nd this technique useful. Update applications such as those that periodically operate on tables not being concurrently accessed (e.g.
DECLARE CURSOR The DECLARE CURSOR command names a cursor and associates with it a particular SELECT command: DECLARE CursorName [IN DBEFileSetName] CURSOR FOR SelectCommand [FOR UPDATE OF ColumnName [,ColumnName...] This command does not retrieve rows from a table. In the physical order of your source program statements, the DECLARE CURSOR command must precede any command that references the cursor; for example, the OPEN command.
When performing cursor processing, the ORDER BY clause may be useful. In the previous example, the rows in the query result will be in order by ascending bin number, to help the program user, who will be moving from bin to bin, taking a physical inventory. The DECLARE CURSOR command is actually a preprocessor directive. When the COBOL preprocessor parses this command, it stores a section in the target DBEnvironment.
The rst time you execute the FETCH command, the rst row in the query result becomes the current row. With each subsequent execution of the FETCH command, each succeeding row in the query result becomes current. After the last row in the query result has been fetched, ALLBASE/SQL sets SQLCODE to 100. ALLBASE/SQL also sets SQLCODE to 100 if no rows qualify for the active set.
The UPDATE WHERE CURRENT command identi es the active set to be updated by naming the cursor and the column(s) to be updated: UPDATE TableName SET ColumnName = ColumnValue [,...
In cases where order is important and the ORDER BY clause must be used, you can use the UPDATE command with the WHERE clause to update values in the current row as well as any other rows that qualify for the search condition. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN EXEC SQL DECLARE INVENTORY CURSOR FOR SELECT PARTNUMBER, BINNUMBER, QTYONHAND, ADJUSTMENTQTY FROM PURCHDB.INVENTORY WHERE COUNTCYCLE = :COUNTCYCLE ORDER BY BINNUMBER END-EXEC. . . .
DELETE WHERE CURRENT This command can be used to delete the current row when the SELECT command associated with the cursor does not contain one of the following: DISTINCT clause in the select list. Aggregate function in the select list. FROM clause with more than one table. ORDER BY clause. GROUP BY clause.
CLOSE When you no longer want to operate on the active set, use the CLOSE command: CLOSE CursorName The CLOSE command frees up ALLBASE/SQL internal bu ers used to handle cursor operations. This command does not release any locks obtained since the cursor was opened; to release locks, you must terminate the transaction with a COMMIT WORK or a ROLLBACK WORK: The program opens a cursor and operates on the active set.
EXEC SQL DECLARE ORDERREVIEW CURSOR FOR SELECT ORDERNUMBER, ITEMNUMBER ORDERQTY, RECEIVEDQTY FROM PURCHDB.ORDERITEMS WHERE ORDERNUMBER > :ORDERNUMBER END-EXEC. The scope and strength of any lock obtained depends in part on the automatic locking mode of the target table(s). If the lock obtained is a shared lock, as for PUBLIC or PUBLICREAD tables, ALLBASE/SQL elevates the lock to an exclusive lock when you update or delete a row in the active set.
If you use the KEEP CURSOR WITH NOLOCKS option for a cursor that involves sorting, whether through the use of a DISTINCT, GROUP BY, or ORDER BY clause, or as the result of a union or a join operation, ALLBASE/SQL does not ensure data integrity. It is your responsibility to ensure data integrity by verifying the continued existence of a row before updating it or using it as the basis for updating some other table.
Figure 8-1. Cursor Operation without the KEEP CURSOR Feature After the cursor is opened, successive FETCH commands advance the cursor position. Any exclusive locks acquired along the way are retained until the transaction ends. If you have selected the Cursor Stability option in the BEGIN WORK command, locks on pages that have not been updated are released when the cursor moves to a tuple on a new data page. Exclusive locks are not released until a COMMIT WORK, which also closes the cursor.
Figure 8-2 shows the e ect of the KEEP CURSOR WITH LOCKS. Figure 8-2. Cursor Operation Using KEEP CURSOR WITH LOCKS OPEN Command Using KEEP CURSOR WITH NOLOCKS The feature has the following e ects: A COMMIT WORK command does not close the cursor. Instead, it ends the current transaction and immediately starts another one. When you issue a COMMIT WORK, all locks associated with the cursor are released.
You cannot use the KEEP CURSOR option WITH NOLOCKS for a cursor declared as a SELECT with a DISTINCT or ORDER BY clause. When using KEEP CURSOR WITH NOLOCKS, be aware that data at the cursor position may be lost before the next FETCH: If another transaction deletes the current row, ALLBASE/SQL will return the next row. No error message is displayed.
KEEP CURSOR and BEGIN WORK ALLBASE/SQL automatically begins a transaction whenever you issue a command if a transaction is not already in progress. Thus, although you can code an explicit BEGIN WORK to start transactions, it is not necessary to do so unless you wish to specify an isolation level other than RR.
Writing Keep Cursor Applications A skeleton outline of a KEEP CURSOR application showing the sections and speci c code examples follow appear below. Because of the potential for deadlock, you must be careful to test for that condition frequently in applications using KEEP CURSOR. An aborted transaction results when a deadlock is encountered. (There is no need to test for deadlock following a COMMIT WORK or a BEGIN WORK command.) Use the following steps to create your code: 1.
Examples This code is intended as a guide; you will want to customize it for your speci c needs. The code illustrates status checking techniques with emphasis on deadlock detection. Four generalized code segments are presented: A status checking routine to be used in conjunction with the other code segments. Using a single kept cursor with locks. Using multiple cursors and cursor stability. Avoiding locks on terminal reads. Common StatusCheck Procedure S100-SQL-STATUS-CHECK.
************************************************************** * For other fatal errors: * * PERFORM S200-SQLEXPLAIN to display messages * * RELEASE the DBE * * Stop the program * * * * Some errors which could be considered fatal are: * * -3040 DBA issued a STOP DBE command * * -3043 DBA issued a terminate user command * * -14046 log full error * * -14047 system clock/timestamp error * * -14074 DBCore internal error * * -14075 DBCore internal error * * -15048 DBCore internal error * ************************
S400-OPEN-C1-WITH-NOLOCKS. EXEC SQL OPEN C1 KEEP CURSOR WITH NOLOCKS END-EXEC. PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT. S400-EXIT. EXIT. S500-CLOSE-C1. EXEC SQL CLOSE C1 END-EXEC. PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT. S500-EXIT. EXIT. S600-COMMIT-WORK. EXEC SQL COMMIT WORK END-EXEC. PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT. S600-EXIT. EXIT.
Single Cursor WITH LOCKS A100-SINGLE-CURSOR. ************************************************************** * Declare cursor C1. * ************************************************************** EXEC SQL DECLARE C1 CURSOR FOR SELECT PartName, SalesPrice FROM PurchDB.Parts WHERE SalesPrice > 500.00 END-EXEC. ************************************************************** * Open cursor C1 using KEEP CURSOR WITH LOCKS option, * * testing for deadlocks.
A200-FETCH-AND-DISPLAY. ************************************************************** * BULK FETCH qualifying rows from the Parts table using * * cursor C1 until there is no more data, testing for * * deadlocks. * ************************************************************** MOVE "X" TO Deadlock-Flag. PERFORM A300-BULK-FETCH-C1 THRU A300-EXIT UNTIL Deadlock-Free OR EndOfScan. IF EndOfScan GOTO A200-EXIT.
Multiple Cursors and Cursor Stability B100-MULTI-CURSOR. ************************************************************** * Declare cursor C1 and cursor C2. * ************************************************************** EXEC SQL DECLARE C1 CURSOR FOR SELECT BranchNo FROM Tellers WHERE TellerNo > 15000 FOR UPDATE OF Status END-EXEC. EXEC SQL DECLARE C2 CURSOR FOR SELECT BranchNo FROM Branches FOR UPDATE OF Credit END-EXEC.
************************************************************** * Execute final COMMIT WORK to release all locks held by * * cursor C1. * ************************************************************** PERFORM S600-COMMIT-WORK THRU S600-EXIT. B100-EXIT. EXIT. B200-FETCH-C1-AND-UPDATE. ************************************************************** * FETCH data from Tellers table using cursor C1. * ************************************************************** EXEC SQL FETCH C1 INTO :HostBranchNo1 END-EXEC.
************************************************************** * Execute COMMIT WORK to: * * Save UPDATEs to Branches table using cursor C2 * * Release all page locks held by cursor C2 * * Save UPDATES to Tellers table using cursor C1 * * Release pages locked by cursor C1 except current page * ************************************************************** IF SQLCODE = 0 EXEC SQL COMMIT WORK END-EXEC. PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT. B200-EXIT. EXIT. B300-FETCH-C2-AND-UPDATE.
B400-BEGIN-WORK-OPEN-C1. ************************************************************** * Open cursor C1 using KEEP CURSOR WITH LOCKS option, * * testing for deadlocks. Use an explicit BEGIN WORK CS * * command in the loop to ensure that ALLBASE/SQL will use * * the CURSOR STABILITY isolation level if a deadlock occurs. * ************************************************************** EXEC SQL BEGIN WORK CS END-EXEC. IF SQLCODE = 0 EXEC SQL OPEN C1 KEEP CURSOR WITH LOCKS END-EXEC.
Avoiding Locks on Terminal Reads C100-NO-TERM-LOCK. ************************************************************** * Declare cursor C1. * ************************************************************** EXEC SQL DECLARE C1 CURSOR FOR SELECT PartName, SalesPrice FROM PurchDB.Parts END-EXEC. ************************************************************** * Open cursor C1 using KEEP CURSOR WITH NOLOCKS option, * * testing for deadlocks.
C200-FETCH-C1-AND-DISPLAY. ************************************************************** * FETCH data from the Parts table using cursor C1, testing * * for deadlocks. * ************************************************************** MOVE "X" TO Deadlock-Flag. PERFORM C300-FETCH THRU C300-EXIT UNTIL DeadlockFree. ************************************************************** * Execute COMMIT WORK to release all page locks held by * * cursor C1.
C400-SELECT-AND-UPDATE. ************************************************************** * Re-select data from the Parts table. * ************************************************************** EXEC SQL SELECT SalesPrice INTO :SalesPrice FROM PurchDB.Parts WHERE PartNumber = :PartNumber END-EXEC. PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT. IF EndOfScan DISPLAY "Part number no longer in database. Not updated." GOTO C500-EXIT. IF SalesPrice NOT = PresentSalesPrice DISPLAY "Current price has changed.
Program Using UPDATE WHERE CURRENT The ow chart in Figure 8-4 summarizes the functionality of program COBEX8. This program uses a cursor and the UPDATE WHERE CURRENT command to update column RECEIVEDQTY in table PURCHDB.ORDERITEMS. The runtime dialog for COBEX8 appears in Figure 8-5, and the source code in Figure 8-6. The program rst performs paragraph DECLARE-CURSOR 4 1 5, which contains the DECLARE CURSOR command 4 5 5. This command is a preprocessor directive and is not executed at run time.
Paragraph DISPLAY-UPDATE then asks the user whether he wants to update the current RECEIVEDQTY value 4 17 5. If so, the user is prompted for a new value. The value accepted is used in one of two UPDATE WHERE CURRENT commands, depending on whether the user wants to assign a null value to RECEIVEDQTY 4 18 5. If the user entered a zero, a null value is assigned to this column. The program then asks whether to FETCH another row 4 19 5. If so, the FETCH command is re-executed.
Figure 8-4.
:RUN COBEX8P Program to UPDATE OrderItems Table via a CURSOR - COBEX8 Event List: Connect to PartsDBE Prompt for Order Number Begin Work Open Cursor FETCH a row Display the retrieved row Prompt for new Received Quantity Update row within OrderItems Table FETCH the next row, if any, with the same Order Number Repeat the above five steps until there are no more rows Close Cursor End Transaction Repeat the above eleven steps until user enters 0 Release PartsDBE Declare Cursor Connect to PartsDBE Enter OrderNum
Do you want to change ReceivedQty (Y/N)? > Enter New ReceivedQty (0 for NULL)> Update PurchDB.OrderItems Table Y 15 Do you want to see another row (Y/N)? > Y OrderNumber: 30520 ItemNumber: 3 VendPartNumber: 9135 ReceivedQty: 3 Do you want to change ReceivedQty (Y/N)? > N Do you want to see another row (Y/N)? > Y Row Not Found or no more rows Close Cursor Do you want to save changes you made (Y/N)? > Y Commit Work 1 row(s) changed.
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * This program illustrates the use of UPDATE WHERE CURRENT * with a Cursor to update a single row at a time. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * IDENTIFICATION DIVISION. PROGRAM-ID. COBEX8. AUTHOR. JIM FRANCIS AND KAREN THOMAS. INSTALLATION. HP. DATE-WRITTEN. 13 MAY 1987. DATE-COMPILED. 13 MAY 1987. REMARKS. ILLUSTRATES UPDATE VIA A CURSOR. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SOURCE-COMPUTER. HP-3000.
77 DONE-FETCH-FLAG 88 NOT-DONE-FETCH 88 DONE-FETCH PIC X VALUE SPACE. VALUE SPACE. VALUE 'X'. 77 ABORT-FLAG 88 NOT-STOP 88 ABORT PIC X VALUE SPACE. VALUE SPACE. VALUE 'X'. 01 OK 01 NOTFOUND 01 DEADLOCK PIC S9(9) COMP VALUE 0. PIC S9(9) COMP VALUE 100. PIC S9(9) COMP VALUE -14024. 01 RESPONSE 01 RESPONSE1 01 ROWCOUNTER PIC S9(9) COMP VALUE 0. PIC X(3) VALUE SPACE. PIC S9(9) COMP VALUE 0. 01 ORDERNUMFORMAT 01 ITEMNUMFORMAT 01 QTYNUMFORMAT 01 ROWCOUNTFORMAT $PAGE PROCEDURE DIVISION. BEGIN.
PERFORM DECLARE-CURSOR. 41 5 OPEN OUTPUT CRT. PERFORM CONNECT-DBENVIRONMENT. 42 5 PERFORM FETCH-UPDATE THRU FETCH-UPDATE-EXIT UNTIL DONE. 43 5 PERFORM TERMINATE-PROGRAM. 44 5 TERMINATE-PROGRAM. EXEC SQL RELEASE END-EXEC. STOP RUN. DECLARE-CURSOR. DISPLAY "Declare EXEC SQL DECLARE CURSOR SELECT FROM WHERE AND FOR UPDATE OF END-EXEC. Cursor". ORDERREVIEW FOR ORDERNUMBER, ITEMNUMBER, VENDPARTNUMBER, RECEIVEDQTY PURCHDB.
DISPLAY "Open Cursor". EXEC SQL OPEN ORDERREVIEW END-EXEC. IF SQLCODE NOT = OK THEN PERFORM SQL-STATUS-CHECK MOVE "X" TO DONE-FLAG GO TO FETCH-UPDATE-EXIT. MOVE SPACES TO DONE-FETCH-FLAG. PERFORM FETCH-ROW THRU FETCH-ROW-EXIT UNTIL DONE-FETCH. 49 5 4 10 5 FETCH-UPDATE-EXIT. EXIT. DISPLAY-ROW. MOVE ORDERNUMBER TO ORDERNUMFORMAT. MOVE ITEMNUMBER TO ITEMNUMFORMAT. MOVE RECEIVEDQTY TO QTYNUMFORMAT. DISPLAY " ". DISPLAY " OrderNumber: " ORDERNUMFORMAT. DISPLAY " ItemNumber: " ITEMNUMFORMAT.
ELSE PERFORM SQL-STATUS-CHECK MOVE "X" TO DONE-FETCH-FLAG PERFORM CLOSE-CURSOR PERFORM ROLLBACK-WORK. FETCH-ROW-EXIT. EXIT. $PAGE LAST-ROW. MOVE "X" TO DONE-FETCH-FLAG. PERFORM CLOSE-CURSOR. IF ROWCOUNTER > 0 THEN MOVE "Do you want to save changes you made (Y/N)? > TO PROMPT5. MOVE SPACE TO RESPONSE1. WRITE PROMPT5 AFTER ADVANCING 1 LINE. ACCEPT RESPONSE1.
IF RECEIVEDQTY = 0 THEN 4 18 5 MOVE -1 TO RECEIVEDQTYIND ELSE MOVE 0 TO RECEIVEDQTYIND EXEC SQL UPDATE PURCHDB.ORDERITEMS SET RECEIVEDQTY = :RECEIVEDQTY :RECEIVEDQTYIND WHERE CURRENT OF ORDERREVIEW END-EXEC IF SQLCODE NOT = OK THEN PERFORM SQL-STATUS-CHECK ELSE ADD 1 TO ROWCOUNTER. MOVE "Do you want to see another row (Y/N)? > TO PROMPT4. MOVE SPACE TO RESPONSE1. WRITE PROMPT4 AFTER ADVANCING 1 LINE. ACCEPT RESPONSE1. IF RESPONSE1 = "N" OR RESPONSE1 = "n" THEN PERFORM LAST-ROW. $PAGE CLOSE-CURSOR.
CONNECT-DBENVIRONMENT. DISPLAY "Connect to PartsDBE". EXEC SQL CONNECT TO 'PartsDBE' END-EXEC. 4 24 5 IF SQLCODE NOT = OK THEN PERFORM SQL-STATUS-CHECK PERFORM TERMINATE-PROGRAM. BEGIN-TRANSACTION. DISPLAY "Begin Work". EXEC SQL BEGIN WORK END-EXEC. IF SQLCODE NOT = OK THEN PERFORM SQL-STATUS-CHECK PERFORM TERMINATE-PROGRAM. 4 25 5 COMMIT-WORK. DISPLAY "Commit Work". EXEC SQL COMMIT WORK END-EXEC. IF SQLCODE NOT = OK THEN PERFORM SQL-STATUS-CHECK PERFORM TERMINATE-PROGRAM. 4 26 5 ROLLBACK-WORK.
9 Bulk Table Processing BULK table processing is the programming technique you use to SELECT, FETCH, or INSERT multiple rows at a time . This chapter describes the following aspects of BULK processing: Variables Used in BULK Processing. SQL BULK Commands. Transaction Management for BULK Operations. Sample Program Using BULK Processing. Variables Used in BULK Processing Rows are retrieved into or inserted from host variables declared as an array of records.
You reference the name of the array in the BULK SQL command: EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 PARTSARRAY. 05 EACH-ROW OCCURS 25 TIMES. 10 PARTNUMBER PIC X(16). 10 PARTNAME PIC X(30). 10 PARTNAMEIND SQLIND. 01 SALESPRICE PIC S9(8)V99 COMP-3. . . . EXEC SQL BULK SELECT PARTNUMBER, PARTNAME INTO :PARTSARRAY FROM PURCHDB.PARTS WHERE SALESPRICE < :SALESPRICE END-EXEC.
EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 PARTSARRAY. 05 EACH-ROW OCCURS 25 TIMES. 10 PARTNUMBER PIC X(16). 10 PARTNAME PIC X(30). 10 PARTNAMEIND SQLIND. 01 SALESPRICE PIC S9(8)V99 COMP-3. 01 STARTINDEX PIC S9(4) COMP. 01 NUMBEROFROWS PIC S9(4) COMP. EXEC SQL END DECLARE SECTION END-EXEC. . . EXEC SQL BULK SELECT PARTNUMBER, PARTNAME INTO :PARTSARRAY, :STARTINDEX, :NUMBEROFROWSi FROM PURCHDB.PARTS WHERE SALESPRICE < :SALESPRICE END-EXEC.
In the following example, parts are counted at one of three frequencies or cycles: 30, 60, or 90 days. The host variable array needs to contain only three records, since the query result will never exceed three rows. EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 PARTSPERCYCLE. 05 EACH-ROW OCCURS 3 TIMES. 01 COUNTCYCLE PIC S9(4) COMP. 01 PARTCOUNT PIC S9(9) COMP. . . . EXEC SQL BULK SELECT COUNTCYCLE, COUNT(PARTNUMBER) INTO :PARTSPERCYCLE FROM PURCHDB.INVENTORY END-EXEC.
Multiple query results can be retrieved into the same host variable array by using StartIndex and NumberOfRows values and executing a BULK SELECT command multiple times: EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 PARTSPERCYCLE. 05 EACH-ROW OCCURS 3 TIMES. 01 COUNTCYCLE PIC S9(4) COMP. 01 PARTCOUNT PIC S9(9) COMP. 01 STARTINDEX PIC S9(4) COMP. 01 NUMBEROFROWS PIC S9(4) COMP. 01 LOWBINNUMBER PIC X(16). 01 HIGHBINNUMBER PIC X(16). . . . EXEC SQL END DECLARE SECTION END-EXEC. 01 COUNTCYCLEFORMAT PIC ZZZZZ9.
. . SELECT-ROWS. . . The user is prompted for a range of bin numbers or a 0. If bin numbers are entered, they are used in a BETWEEN predicate in the BULK SELECT command. This WHILE loop can be executed as many as ve times, at which time the array would be lled. MOVE "ENTER A LOW BIN NUMBER OR / TO STOP> " TO PROMPT. WRITE PROMPT AFTER ADVANCING 1 LINE. ACCEPT RESPONSE.
The following example illustrates the use of SQLERRD(3) to display rows stored in the host variable array. It also checks SQLCODE in conjunction with SQLERRD(3), to determine whether or not the BULK SELECT executed without error and whether there may be additional quali ed rows for which there was not room in the array. In each case, an appropriate message is displayed. The variable MAXIMUMROWS is set to the number of records in the host variable array. MOVE 25 TO MAXIMUMROWS.
BULK FETCH The BULK FETCH command is useful for reporting applications that operate on large query results or query results whose maximum size is unknown at programming time. The form of the BULK FETCH command is: BULK FETCH CursorName INTO ArrayName [,StartIndex [,NumberOfRows]] You use this command in conjunction with the following cursor commands: DECLARE CURSOR: de nes a cursor and associates with it a query.
EXEC SQL OPEN SUPPLIERINFO END-EXEC. MOVE SPACE TO FETCH-FLAG. PERFORM FETCH-ROWS UNTIL FETCH-DONE. EXEC-SQL CLOSE SUPPLIERINFO END-EXEC. FETCH-ROWS. EXEC SQL BULK FETCH SUPPLIERINFO INTO SUPPLIERBUFFER END-EXEC. IF SQLCODE = 0 THEN PERFORM DISPLAY-ROWS. IF SQLCODE = 100 THEN DISPLAY "No rows were found." MOVE "X" TO FETCH-FLAG. IF SQLCODE < 0 THEN PERFORM DISPLAY-ROWS PERFORM SQL-STATUS-CHECK MOVE "X" TO FETCH-FLAG. DISPLAY-ROWS. PERFORM SHOW-FETCH VARYING I FROM 1 BY 1 UNTIL I > SQLERRD(3).
BULK INSERT The BULK INSERT command is useful for multiple-row insert operations. The form of the BULK INSERT command is: BULK INSERT INTO TableName (ColumnNames) VALUES (ArrayName [,StartIndex [,NumberOfRows]] As in the case of the simple INSERT command you can omit ColumnNames when you provide values for all columns in the target table. ALLBASE/SQL attempts to assign a null value to any unnamed column. In the following example, a user is prompted for multiple rows.
PART-ENTRY. .
. . COMPUTE NUMBEROFROWS = NUMBEROFROWS + 1. MOVE "Do you want to specify another line item (Y/N)?> " TO PROMPT. WRITE PROMPT AFTER ADVANCING 1 LINE. ACCEPT RESPONSE. IF RESPONSE = "N" OR "n" THEN MOVE "X" TO ENTRY-DONE-FLAG PERFORM BULK-INSERT ELSE IF NUMBEROFROWS = 20 THEN PERFORM BULK-INSERT MOVE 0 TO NUMBEROFROWS. BULK-INSERT. EXEC SQL BULK INSERT INTO PURCHDB.PARTS (PARTNUMBER, PARTNAME, SALESPRICE) VALUES (:NEWPARTS, :STARTINDEX, :NUMBEROFROWS) END-EXEC.
Sample Program Using BULK Processing The ow chart in Figure 9-1 summarizes the functionality of program COBEX9. This program creates orders in the sample DBEnvironment, PARTSDBE. Each order is placed with a speci c vendor, to obtain one or more parts supplied by that vendor. The order header consists of data from a row in table PURCHDB.ORDERS: ORDERNUMBER (defined NOT NULL) VENDORNUMBER ORDERDATE An order usually also consists of one or more line items, represented by one or more rows in table PURCHDB.
To validate the vendor number, paragraph VALIDATE-VENDOR is executed 4 9 5. Paragraph VALIDATE-VENDOR starts a transaction by performing paragraph BEGIN-TRANSACTION 4 38 5, which executes the BEGIN WORK command 4 53 5. Then a SELECT command 4 39 5 is processed to determine whether the vendor number exists in column VENDORNUMBER of table PURCHDB.VENDORS: If the number exists in table PURCHDB.VENDORS, the vendor number is valid.
If the user wants to create line items, CREATE-ORDER-ITEMS performs paragraph ITEM-ENTRY through ITEM-ENTRY-EXIT until the DONE-ITEMS-FLAG contains an X 4 24 5, then performs paragraph BULK-INSERT 4 25 5: ITEM-ENTRY assigns values to host variable array ORDERITEMS 4 1 5; each row in the array corresponds to one line item, or row in table PURCHDB.ORDERITEMS. The paragraph rst assigns the order number and a line number to each row 4 26 5, beginning at one.
Figure 9-1.
:RUN COBEX9P Program to Create an Order - COBEX9 Event List: Connect to PartsDBE Prompt for VendorNumber Validate VendorNumber INSERT a row into PurchDB.Orders Prompt for line items Validate VendPartNumber for each line item BULK INSERT rows into PurchDB.OrderItems Repeat the above six steps until user enters 0 Release PartsDBE Connect to PartsDBE Enter VendorNumber or 0 to STOP> 9015 Begin Work Validating VendorNumber Commit Work Begin Work Calculating OrderNumber Calculating OrderDate INSERT INTO PurchDB.
Begin Work Validating VendPartNumber Commit Work The vendor has no part with the number you specified. You can specify as many as 25 line items. Enter data for ItemNumber VendPartNumber> 9050 2: Begin Work Validating VendPartNumber Commit Work PurchasePrice> 345 OrderQty> 2 ItemDueDate (YYYYMMDD)> 19870801 Do you want to specify another line item (Y/N)?> n Begin Work BULK INSERT INTO PurchDB.
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * This program illustrates the use of BULK INSERT * to insert multiple rows at a time. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * IDENTIFICATION DIVISION. PROGRAM-ID. COBEX9. AUTHOR. JIM FRANCIS AND KAREN THOMAS. INSTALLATION. HP. DATE-WRITTEN. 20 MAY 1987. DATE-COMPILED. 20 MAY 1987. REMARKS. ILLUSTRATES BULK INSERT. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SOURCE-COMPUTER. HP-3000. OBJECT-COMPUTER. HP-3000.
10 RECEIVEDQTY 10 RECEIVEDQTYIND 01 STARTINDEX 01 NUMBEROFROWS PIC S9(4) COMP. SQLIND. PIC S9(4) COMP. PIC S9(4) COMP. 01 SQLMESSAGE PIC X(132). EXEC SQL END DECLARE SECTION END-EXEC. * * * * * * END OF HOST VARIABLE DECLARATIONS * * * * * * * $PAGE 77 DONE-FLAG PIC X VALUE SPACE. 88 NOT-DONE VALUE SPACE. 88 DONE VALUE "X". 77 88 88 DONE-ITEMS-FLAG NOT-DONE-ITEMS DONE-ITEMS PIC X VALUE SPACE. VALUE SPACE. VALUE "X". 88 88 VENDOR-FLAG VENDOR-OK VENDOR-NOT-OK PIC X VALUE SPACE. VALUE SPACE. VALUE "X".
01 ITEMNUMFORMAT 01 QTYNUMFORMAT 01 DOLLARS 01 TODAY. 05 TMONTH 05 FILLER 05 TDAY 05 FILLER 05 TYEAR $PAGE PROCEDURE DIVISION. PIC ZZZZZ9. PIC ZZZZZ9. PIC $$$,$$$,$$$.99. PIC PIC PIC PIC PIC 42 5 X(2). X(1). X(2). X(1). X(2). BEGIN. DISPLAY DISPLAY DISPLAY DISPLAY DISPLAY DISPLAY DISPLAY DISPLAY DISPLAY DISPLAY DISPLAY "Program to Create an Order - COBEX9". " ". "Event List:". " Connect to PartsDBE". " Prompt for VendorNumber". " Validate VendorNumber". " INSERT a row into PurchDB.Orders".
WRITE PROMPT AFTER ADVANCING 1 LINE. ACCEPT RESPONSE FREE. IF RESPONSE IS ZERO THEN MOVE "X" TO DONE-FLAG GO TO CREATE-ORDER-EXIT ELSE MOVE RESPONSE TO VENDORNUMBER. 48 5 PERFORM VALIDATE-VENDOR. 49 5 IF VENDOR-OK THEN PERFORM CREATE-HEADER. 4 10 5 IF HEADER-OK THEN PERFORM CREATE-ORDER-ITEMS UNTIL DONE-ITEMS. 4 11 5 IF ITEMS-OK THEN PERFORM DISPLAY-ORDER. 4 12 5 CREATE-ORDER-EXIT. EXIT. CREATE-HEADER. DISPLAY " ". DISPLAY "Begin Work". PERFORM BEGIN-TRANSACTION.
DISPLAY "Calculating OrderNumber". COMPUTE ORDERNUMBER1 = MAXORDERNUMBER + 1. DISPLAY "Calculating OrderDate". MOVE CURRENT-DATE TO TODAY. 4 16 5 STRING "19", TYEAR, TMONTH, TDAY DELIMITED BY SIZE INTO ORDERDATE. 4 18 5 DISPLAY "INSERT INTO PurchDB.Orders". EXEC SQL INSERT INTO PURCHDB.ORDERS ( ORDERNUMBER, VENDORNUMBER, ORDERDATE ) VALUES (:ORDERNUMBER1, :VENDORNUMBER, :ORDERDATE ) END-EXEC.
ITEM-ENTRY. MOVE ORDERNUMBER1 TO ORDERNUMBER2(I). MOVE I TO ITEMNUMBER(I). MOVE I TO ITEMNUMFORMAT. DISPLAY " ". DISPLAY "You can specify as many as 25 line items.". DISPLAY " ". DISPLAY "Enter data for ItemNumber " ITEMNUMFORMAT ":". 4 26 5 MOVE " VendPartNumber> " TO PROMPT2. WRITE PROMPT2 AFTER ADVANCING 1 LINE. MOVE SPACES TO VENDPARTNUMBER(I). ACCEPT VENDPARTNUMBER(I). 4 27 5 PERFORM VALIDATE-PART.
ITEM-ENTRY-EXIT. EXIT. BULK-INSERT. DISPLAY " ". DISPLAY "Begin Work". PERFORM BEGIN-TRANSACTION. 4 33 5 MOVE I TO NUMBEROFROWS. MOVE 1 TO STARTINDEX. 4 34 5 DISPLAY "BULK INSERT INTO PurchDB.OrderItems". EXEC SQL BULK INSERT INTO PURCHDB.ORDERITEMS ( ORDERNUMBER, ITEMNUMBER, VENDPARTNUMBER, PURCHASEPRICE, ORDERQTY, ITEMDUEDATE, RECEIVEDQTY ) VALUES (:ORDERITEMS, :STARTINDEX, :NUMBEROFROWS) END-EXEC.
IF SQLCODE = OK THEN 4 40 5 PERFORM COMMIT-WORK MOVE SPACE TO VENDOR-FLAG ELSE IF SQLCODE = NOTFOUND 4 41 5 PERFORM COMMIT-WORK DISPLAY " " DISPLAY "No vendor has the VendorNumber you specified." MOVE "X" TO VENDOR-FLAG MOVE "X" TO HEADER-FLAG MOVE "X" TO ITEMS-FLAG ELSE PERFORM SQL-STATUS-CHECK 4 42 5 PERFORM COMMIT-WORK MOVE "X" TO VENDOR-FLAG MOVE "X" TO HEADER-FLAG MOVE "X" TO ITEMS-FLAG. VALIDATE-PART. DISPLAY " ". DISPLAY "Begin Work". DISPLAY "Validating VendPartNumber". PERFORM BEGIN-TRANSACTION.
DISPLAY-ORDER. PERFORM DISPLAY-HEADER. 4 47 5 DISPLAY " ". PERFORM DISPLAY-ITEMS VARYING J FROM 1 BY 1 UNTIL J > I.4 48 5 DISPLAY-HEADER. 4 49 5 DISPLAY " ". DISPLAY "The following order has been created:" DISPLAY " ". MOVE ORDERNUMBER1 TO ORDERNUMFORMAT DISPLAY " OrderNumber: " ORDERNUMFORMAT. MOVE VENDORNUMBER TO VENDORNUMFORMAT. DISPLAY " VendorNumber: " VENDORNUMFORMAT. DISPLAY " OrderDate: " ORDERDATE. DISPLAY-ITEMS. 4 50 5 DISPLAY " ". MOVE ITEMNUMBER(J) TO ITEMNUMFORMAT.
SQLEXPLAIN. EXEC SQL SQLEXPLAIN :SQLMESSAGE END-EXEC. DISPLAY SQLMESSAGE. CONNECT-DBENVIRONMENT. DISPLAY "Connect to PartsDBE". EXEC SQL CONNECT TO "PartsDBE" END-EXEC. 4 52 5 IF SQLCODE NOT = OK THEN PERFORM SQL-STATUS-CHECK PERFORM TERMINATE-PROGRAM. BEGIN-TRANSACTION. EXEC SQL BEGIN WORK END-EXEC. 4 53 5 IF SQLCODE NOT = OK THEN PERFORM SQL-STATUS-CHECK PERFORM TERMINATE-PROGRAM. COMMIT-WORK. DISPLAY "Commit Work". EXEC SQL COMMIT WORK END-EXEC.
10 Using Dynamic Operations Dynamic operations are used to execute SQL commands that are not preprocessed until run time. Such commands, known as dynamic SQL commands, are submitted to ALLBASE/SQL through several special SQL statements: PREPARE, DESCRIBE, EXECUTE, and EXECUTE IMMEDIATE. This chapter contrasts dynamic with non-dynamic operations and introduces the techniques used to handle dynamic operations from a program. In COBOL programs, you cannot dynamically preprocess a query (SELECT command).
These preprocessing events take place for all non-dynamic SQL commands when you run the ALLBASE/SQL preprocessor. Non-dynamic commands are fully de ned in the source code and are preprocessed before run time. So far, most of the examples in this manual have shown non-dynamic preprocessing. ALLBASE/SQL completes the preprocessing of dynamic commands at run time, in an event known as dynamic preprocessing.
Figure 10-1. Creation and Use of a Program that has a Stored Module Programs that contain only SQL commands that do not have permanently stored sections can be executed against any DBEnvironment without the prerequisite of storing a module in the DBEnvironment. Figure 10-2 illustrates how you create and use programs in this category. Note that the program must still be preprocessed in order to create compilable les and generate ALLBASE/SQL external procedure calls.
Figure 10-2. Creation and Use of a Program that has No Stored Module Examples of Non-Dynamic and Dynamic SQL Statements The following example shows an embedded SQL statement that is coded so as to generate a stored section before run time: EXEC SQL UPDATE STATISTICS FOR TABLE PurchDB.Parts; When you run the preprocessor on a source le containing this statement, a permanent section will be stored in the appropriate DBEnvironment.
In this case, the SQL statement is stored in a host variable which is passed to ALLBASE/SQL in the PREPARE statement at run time. A temporary section is then created and executed, and the section is not stored in the DBEnvironment. Why Use Dynamic Preprocessing? In some cases, it may be desirable to preprocess an SQL command at run time: You may need to code an application that permits SQL commands to be entered by the user at run time.
To pass a dynamic command that cannot be completely de ned at programming time, you use a host variable declared with PIC, as follows: 01 DYNAMICHOSTVAR PIC X(2048). . . EXEC SQL EXECUTE IMMEDIATE :DYNAMICHOSTVAR END-EXEC. Understanding the Types of Dynamic Operations Dynamic operations in ALLBASE/SQL are of two major types: Dynamic Non-Queries: dynamic operations that do not retrieve rows from the database. Note that dynamic non-queries either do or do not require the use of sections at execution time.
. . WORKING-STORAGE SECTION. . . DYNAMIC-CMD contains the SQL command to be executed by the subprogram. 01 NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN DYNAMIC-CMD PIC X(1014). SQLCA is the data structure that contains current information about a program's DBE session. NNNNNNNNNNNNNNNNN EXEC SQL INCLUDE SQLCA END-EXEC. . . PROCEDURE DIVISION. . . Connect to the DBEnvironment. . . Load DYNAMIC-CMD with the SQL command to be executed. . . CALL "performcommand" USING DYNAMIC-CMD, SQLCA. . .
. . Global variable declarations needed by the C routines for dynamic preprocessing: EXEC SQL BEGIN DECLARE SECTION; char DynamicCommand[1014] ; EXEC SQL END DECLARE SECTION; NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN NNNNNNNNNNNNNNNNN EXEC SQL INCLUDE SQLCA ; NNNNNNNNNNNNNNNNN EXEC SQL INCLUDE SQLDA ; . .
sourceptr = sqlcaparm; destptr = &sqlca; for (k = 1; k <= sizeof(sqlca); k++) { *destptr = *sourceptr; sourceptr++; destptr++; } Issue the SQL PREPARE and DESCRIBE commands. Parse the data bu er and display the rows fetched by the query. See the cex10a program in the ALLBASE/SQL C Application Programming Guide for more information. Before returning to the COBOL program copy SQLCA to sqlcaparm. This permits the COBOL program to access the information in the SQLCA.
Type Dynamic_Type = Packed Array [1..1014] of char; . . Global variable declarations needed by the Pascal routines for dynamic preprocessing: EXEC SQL BEGIN DECLARE SECTION; DynamicCommand : Packed Array [1..1014] of char; EXEC SQL END DECLARE SECTION; NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN NNNNNNNNNNNNNNNNN EXEC SQL INCLUDE SQLDA ; . . Procedure PerformCommand (Var DynamicParm Var SQLCA . .
How To Preprocess, Compile, Link and Run the Example Programs COBOL Calling a C Subprogram In the example below, the COBOL source code is in COBEXS, the C source code is in CEXS, and the DBEnvironment is PartsDBE. 1. Preprocess the COBOL source code. :PCOB COBEXS,PARTSDBE 2. Compile the COBOL source code generated by the preprocessor. :COB85XL SQLOUT,COBEXO,$NULL 3. Preprocess the C source code. :PC CEXS,PARTSDBE 4. Compile the C source code generated by the preprocessor. :CCXL SQLOUT,CEXO,$NULL 5.
Preprocessing of Dynamic Non-Queries There are two methods for dynamic preprocessing of a non-query: Using EXECUTE IMMEDIATE. Using PREPARE and EXECUTE. The rst method can be used with any non-query; the second is only for those non-query commands that use sections at execution time.
To handle a command entirely unknown at programming time, you accept the command into a host variable that can hold CHAR or VARCHAR data. In the following example, an SQL command is accepted into a host variable named DYNAMICCOMMAND , declared large enough to accommodate the maximum size dynamic command. User input is accepted into DYNAMICCLAUSE and concatenated in DYNAMICCOMMAND until the user enters only a semicolon in response to the input prompt. WORKING-STORAGE SECTION. .. .
To handle a command partially known at programming time, you prompt the user for information to complete the command. Then you concatenate this information with the prede ned part of the command: DATA DIVISION. FILE SECTION. FD CRT. 01 PROMPT PIC X(35). . . . WORKING-STORAGE SECTION. . . . EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 CMDLINE PIC X(80). EXEC SQL END DECLARE SECTION END-EXEC. 01 CMDLITERAL PIC X(28). 01 TABLENAME PIC X(42). 01 SQL-TERMINATOR PIC X(2) VALUE "; ". . . . PROCEDURE DIVISION. . . .
Sample Program Using EXECUTE IMMEDIATE To preprocess and execute a dynamic command in only one step, you use the EXECUTE IMMEDIATE command: EXEC SQL EXECUTE IMMEDIATE :DynamicCommand END-EXEC. Program COBEX10A, whose run time dialog is shown in Figure 10-3 and whose source code is given in Figure 10-4, can be used to execute the UPDATE STATISTICS command in any DBEnvironment. This program prompts for both the DBEnvironment name and the name of tables upon which to execute the UPDATE STATISTICS command.
:RUN COBX10AP Program to EXECUTE IMMEDIATE the UPDATE STATISTICS command - COBEX10A Event List: Prompt for DBE name Connect to DBE Begin Work Prompt for table name EXECUTE IMMEDIATE UPDATE STATISTICS command Commit Work Repeat the above three steps until user enters '/' Release Database Environment Enter name of DBEnvironment> Connect to DBE Begin Work PARTSDBE Enter table name or '/' to STOP> PURCHDB.VENDORS UPDATE STATISTICS FOR TABLE PURCHDB.
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * This program illustrates the use of SQL's EXECUTE * * IMMEDIATE Command. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * IDENTIFICATION DIVISION. PROGRAM-ID. COBEX10A. AUTHOR. JIM FRANCIS AND KAREN THOMAS. INSTALLATION. HP. DATE-WRITTEN. 17 MARCH 1987. DATE-COMPILED. 17 MARCH 1987. REMARKS. ILLUSTRATES EXECUTE IMMEDIATE ENVIRONMENT DIVISION. $CONTROL USLINIT CONFIGURATION SECTION. SOURCE-COMPUTER. HP-3000. OBJECT-COMPUTER.
77 ABORT-FLAG 88 NOT-STOP 88 ABORT PIC X VALUE SPACE. VALUE SPACE. VALUE 'X'. 01 OK 01 DEADLOCK PIC S9(9) COMP VALUE 0. PIC S9(9) COMP VALUE -14024. $PAGE PROCEDURE DIVISION. BEGIN. DISPLAY "Program to EXECUTE IMMEDIATE the " "UPDATE STATISTICS command - COBEX10A". DISPLAY " ". DISPLAY "Event List:". DISPLAY " Prompt for DBE name". DISPLAY " Connect to DBE". DISPLAY " Begin Work". DISPLAY " Prompt for table name". DISPLAY " EXECUTE IMMEDIATE UPDATE STATISTICS command". DISPLAY " Commit Work".
$PAGE EXECUTE-IMMEDIATE. MOVE SPACES TO CMDLINE. MOVE SPACES TO RESPONSE. MOVE "Enter table name or '/' to STOP> " TO PROMPT2. WRITE PROMPT2 AFTER ADVANCING 1 LINE. ACCEPT RESPONSE. IF RESPONSE-PREFIX = "/" THEN MOVE "X" TO DONE-FLAG GO TO EXECUTE-IMMEDIATE-EXIT ELSE MOVE "UPDATE STATISTICS FOR TABLE " TO CMDLITERAL STRING CMDLITERAL, RESPONSE, SQL-TERMINATOR DELIMITED BY SIZE INTO CMDLINE. DISPLAY CMDLINE. DISPLAY "EXECUTE IMMEDIATE UPDATE STATISTICS command". EXEC SQL EXECUTE IMMEDIATE :CMDLINE END-EXEC.
SQLEXPLAIN. 4 17 5 EXEC SQL SQLEXPLAIN :SQLMESSAGE END-EXEC. DISPLAY SQLMESSAGE. CONNECT-DBENVIRONMENT. MOVE "Enter name of DBEnvironment> " TO PROMPT1. WRITE PROMPT1 AFTER ADVANCING 1 LINE. ACCEPT DBENAME FREE. DISPLAY "Connect to DBE". EXEC SQL CONNECT TO :DBENAME END-EXEC. 4 18 5 4 19 5 4 20 5 IF SQLCODE NOT = OK THEN PERFORM SQL-STATUS-CHECK PERFORM TERMINATE-PROGRAM. BEGIN-TRANSACTION. DISPLAY "Begin Work". EXEC SQL BEGIN WORK END-EXEC.
Sample Program Using PREPARE and EXECUTE To prepare a dynamic command for execution later during the current transaction, you use the PREPARE command to dynamically preprocess the command. ALLBASE/SQL creates a temporary section for the command that you can execute one or more times in the same transaction by using the EXECUTE command: EXEC SQL PREPARE MyCommand FROM :DynamicCommand END-EXEC. . . EXEC SQL EXECUTE :DynamicCommand END-EXEC.
:RUN COBX10BP Program to PREPARE & EXECUTE SQL commands - COBEX10B Event List: Connect to PartsDBE Begin Work Prompt for SQL command PREPARE SQL Command EXECUTE SQL Command Repeat the above three steps until user enters '/' Commit Work Release PartsDBE Connect to PartsDBE Begin Work Enter an SQL command or clause; enter only a semicolon when done. > UPDATE STATISTICS FOR UPDATE STATISTICS FOR > TABLE PURCHDB.PARTS UPDATE STATISTICS FOR TABLE PURCHDB.
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * This program illustrates the use of SQL's PREPARE-EXECUTE * * Commands. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * IDENTIFICATION DIVISION. PROGRAM-ID. COBEX10B. AUTHOR. JIM FRANCIS AND KAREN THOMAS. INSTALLATION. HP. DATE-WRITTEN. 17 MARCH 1987. DATE-COMPILED. 17 MARCH 1987. REMARKS. ILLUSTRATES PREPARE-EXECUTE. ENVIRONMENT DIVISION. $CONTROL USLINIT CONFIGURATION SECTION. SOURCE-COMPUTER. HP-3000. OBJECT-COMPUTER.
01 INDEXER PIC S9(4) COMP. 77 DONE-FLAG 88 NOT-DONE 88 DONE PIC X VALUE SPACE. VALUE SPACE. VALUE 'X'. 77 COMMAND-DONE-FLAG 88 CMD-NOT-DONE 88 CMD-DONE PIC X VALUE SPACE. VALUE SPACE. VALUE 'X'. 77 ABORT-FLAG 88 NOT-STOP 88 ABORT PIC X VALUE SPACE. VALUE SPACE. VALUE 'X'. 01 OK 01 DEADLOCK PIC S9(9) COMP VALUE 0. PIC S9(9) COMP VALUE -14024. 41 5 42 5 $PAGE PROCEDURE DIVISION. BEGIN. DISPLAY "Program to PREPARE & EXECUTE SQL commands " "- COBEX10B". DISPLAY " ". DISPLAY "Event List:".
PERFORM TERMINATE-PROGRAM. TERMINATE-PROGRAM. EXEC SQL COMMIT WORK RELEASE END-EXEC. STOP RUN. $PAGE PREPARE-EXECUTE. PERFORM INITIALIZE-VARIABLES. 46 5 DISPLAY "Enter an SQL command or clause; enter only a" " semicolon when done.". MOVE SPACE TO COMMAND-DONE-FLAG. PERFORM ACCEPT-COMMAND THRU ACCEPT-COMMAND-EXIT UNTIL CMD-DONE.
IF RESPONSE-PREFIX = "/" THEN MOVE "X" TO DONE-FLAG MOVE 'X' TO COMMAND-DONE-FLAG GO TO ACCEPT-COMMAND-EXIT. IF RESPONSE-PREFIX = ";" THEN MOVE "X" TO COMMAND-DONE-FLAG GO TO ACCEPT-COMMAND-EXIT. 4 13 5 STRING RESPONSE DELIMITED BY " " INTO INPUT-CLAUSES WITH POINTER INDEXER; ON OVERFLOW DISPLAY "Command too long!" DISPLAY "Try again!" PERFORM INITIALIZE-VARIABLES GO TO ACCEPT-COMMAND. 4 14 5 MOVE SPACES TO RESPONSE. ADD 1 TO INDEXER. DISPLAY ' '.
SQL-STATUS-CHECK. IF SQLCODE < DEADLOCK THEN MOVE 'X' TO ABORT-FLAG. PERFORM SQLEXPLAIN UNTIL SQLCODE = 0. IF ABORT THEN PERFORM TERMINATE-PROGRAM. SQL-STATUS-CHECK-EXIT. EXIT. SQLEXPLAIN. EXEC SQL SQLEXPLAIN :SQLMESSAGE END-EXEC. DISPLAY SQLMESSAGE. CONNECT-DBENVIRONMENT. DISPLAY "Connect to PartsDBE". EXEC SQL CONNECT TO 'PartsDBE' END-EXEC. 4 21 5 IF SQLCODE NOT = OK THEN PERFORM SQL-STATUS-CHECK PERFORM TERMINATE-PROGRAM. BEGIN-TRANSACTION. DISPLAY "Begin Work". EXEC SQL BEGIN WORK END-EXEC.
11 Programming With Constraints This chapter explains the use of statement level integrity versus row level integrity. Also, methods of implementing schema level unique and referential integrity constraints in your database are highlighted. Integrity constraints allow you to have ALLBASE/SQL verify data integrity at the schema level. Thus you can avoid coding complex veri cation routines in application programs and avoid the increased execution time of additional queries.
Using Unique and Referential Integrity Constraints Any database containing tables with interdependent data is a good candidate for the use of integrity constraints. You can pro t from their use whether your data is volatile or stable in nature. For instance, your database might contain a table of employee and department data that is constantly changing, or it could contain a table of part number data that rarely changes even though it is frequently accessed.
Table 11-2. Constraint Test Matrix DML Operations UNIQUE or PRIMARY KEY Referenced Table [BULK] INSERT Must be unique in or Type 2 INSERT the table. UPDATE [WHERE CURRENT] DELETE [WHERE CURRENT] Referencing Table Must match a unique key in the referenced table. Must be unique in No foreign key can reference the unique key being the table. updated. Must match a unique key in the referenced table. No foreign key can reference the unique key being deleted.
Figure 11-1. Constraints Enforced on the Recreation Database Suppose you designed an application program providing a user interface to the recreation database. The interface gives choices for inserting, updating, and deleting data in any of the three tables. Your application is user friendly and guides the user with informational messages when their request is denied because it would violate data integrity.
Insert a Member in the Recreation Database The user chooses to insert a new member in the database. For this activity to complete, the foreign key (Club) which is being inserted into the Members table must exist in the primary key (ClubName) of the Clubs table. Execute subroutines to display and prompt for information needed in the Members table. Place user entered information in appropriate host variables. INSERT INTO RecDB.
Update an Event in the Recreation Database The user now wants to update information in the Events table. For this activity to complete, the SponsorClub and Coordinator being updated in the Events table must exist in the primary key composed of MemberName and Club in the Members table. Execute subroutines to display and prompt for information needed in the Events table. Place user entered information in appropriate host variables. UPDATE RecDB.
Delete a Club in the Recreation Database The user chooses to delete a club. For this activity to complete, no foreign key must reference the primary key (ClubName) that is being deleted. Execute subroutines to display and prompt for a ClubName in the Clubs table. Place user entered information in appropriate host variables. DELETE FROM RecDB.Clubs WHERE ClubName = :ClubName Check the sqlcode eld of the sqlca.
12 Programming with LONG Columns LONG columns in ALLBASE/SQL enable you to store a very large amount of binary data in your database, referencing that data via a table column name. You might use LONG columns to store text les, software application code, voice data, graphics data, facsimile data, or test vectors. You can easily SELECT or FETCH this data, and you have the advantages of ALLBASE/SQL's recoverability, concurrency control, locking strategies, and indexes on related columns.
General Concepts ALLBASE/SQL stores LONG column data in a database for later retrieval. LONG column data is not processed by ALLBASE/SQL. Any formatting, viewing, or other processing must be accomplished by means of your program. For example, you might use a graphics application to create an intricate graphic display (or set of graphic displays). You could then write a program in which you embed ALLBASE/SQL commands to store each graphics le in your database along with related data in a given row.
Figure 12-1. Flow of LONG Column Data and Related Information to the Database Figure 12-2.
Restrictions A LONG column can be referenced in a select list and/or a host variable declaration. Some restrictions do apply to LONG columns. However, related standard columns are not a ected by these restrictions. LONG columns cannot be used as follows: In a WHERE clause. In a Type 2 INSERT command. Remotely through ALLBASE/NET. As hash or B-tree index key columns. In a GROUP BY, ORDER BY, DISTINCT, or UNION clause. In an expression. In a subquery. In aggregate functions (AVG, SUM, MIN, MAX).
The next command speci es that data for new LONG column, PartModule, be stored in PartPictureSet. ALTER TABLE PartsTable ADD PartModule LONG VARBINARY(70000) IN PartPictureSet NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN See the \BINARY Data" section of the \Host Variables" chapter for more information on using BINARY and VARBINARY data types in long columns.
Putting Data into a LONG Column with a INSERT Command As with any column, use the INSERT command to initially put data into a LONG column. At the time of the insert, all input devices must be on the system in the locations you have speci ed. Should your insert operation fail, nothing is inserted, a relevant error message is returned to the program, and the transaction continues.
Retrieving LONG Column Data with a SELECT, FETCH, or REFETCH Command The following syntax represents the available subset when your select list includes one or more LONG columns. Remember, a LONG column can be referenced only in a select list and/or a host variable declaration. 8 * > > <2 3 9 > > =2 3 Owner. Table.* , ... CorrelationName.* > > > > ; : CorrelationName.ColumnName 39 2 3 3 82 2 Owner. FromTableName CorrelationName INTO HostVariableDeclaration FROM 2 3 , . ..
Table 12-2.
10 10 SIZE-IN-BYTES FILLER PIC S9(9) COMP. PIC X(44).
Using LONG Columns with a BULK SELECT Command The following code segments illustrate a declaration for the BULK SELECT command with long columns. Should an error occur before completion of the BULK SELECT command, any operating system les written before the error occurred remain on the system, and LONG column descriptors written to a host variable array remain. It is your responsibility to remove such les as appropriate. Example . . . EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 PARTSARRAY.
Changing a LONG Column with an UPDATE [WHERE CURRENT] Command When you issue an UPDATE command on a LONG column, you have the following options: Change the stored data as well as the output device name and/or options. Change the stored data only. Change the output device name and/or options only. Specify a LONG column I/O string (discussed earlier in this chapter) following the SET clause, for each LONG column to be updated. You must specify either the input device, the output device, or both.
File Naming Conventions When a LONG column is selected or fetched, data goes to the output device you have speci ed at insert or update time. In the case of a le, because this output device name can be completely de ned by you, partially de ned by you, or assigned by ALLBASE/SQL, you may want to consider whether or not naming conventions are necessary.
13 Programming with ALLBASE/SQL Functions This chapter highlights functions available in ALLBASE/SQL. The functions return values that can be used to access, search, update, and delete data. Refer to the \Expressions" chapter of the ALLBASE/SQL Reference Manual for a discussion of other available ALLBASE/SQL functions. The ALLBASE/SQL functions discussed in this chapter are as follows: Date/Time functions. Tuple Identi er (TID) function.
Note For all date/time functions, character input and output values are in Native-3000 format. Where Date/Time Functions Can Be Used Use date/time functions, as you would an expression, in the DML operations listed below: Table 13-1.
As for host variables containing input and output data, de ne them to be CHAR or VARCHAR compatible with one exception. The TO INTEGER function requires an INTEGER compatible host variable for its output. Reference the chapter on de ning host variables for additional information about de ning a host variable to be compatible with a speci c ALLBASE/SQL data type. Note that the declarations relate to the default format speci cation for each date/time data type.
Examples of TO DATETIME, TO DATE, TO TIME, and TO INTERVAL Functions Imagine a situation in which users will be inputting and retrieving date/time data in formats other than the default formats. (Refer to the ALLBASE/SQL Reference Manual for default format speci cations.) The data is located in the TestData table in the manufacturing database. (Reference appendix C in the ALLBASE/SQL Reference Manual .
Example Using the INSERT Command. Your application allows users to enter data in their desired formats with a minimum of e ort on your part. BEGIN DECLARE SECTION Declare input host variables (:BatchStamp, :BatchStamp-Format, :TestDate, :TestDate-Format, :TestStart, :LabTime, and LabTime-Format) to be compatible with data type CHAR or VARCHAR. Declare input indicator variables (:TestDateInd and :LabTimeInd). END DECLARE SECTION . . . INSERT INTO MANUFDB.
Example Using the UPDATE Command. These users want the capability of updating data based on the BatchStamp column. BEGIN DECLARE SECTION Declare input host variables (:TestDate, :TestDate-Format, :BatchStamp, and :BatchStamp-Format) to be compatible with data type CHAR or VARCHAR. Declare input indicator variable (:TestDateInd). END DECLARE SECTION . . . UPDATE MANUFDB.
Example Using the SELECT Command. The users are planning to select data from the TestData table based on the lab time interval between the start and end of a given set of tests. BEGIN DECLARE SECTION Declare input host variables (:BatchStamp, :BatchStamp-Format, LabTime, and LabTime-Format) to be compatible with data type CHAR or VARCHAR. END DECLARE SECTION . . .
NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN WHERE BatchStamp = TO_DATETIME (:BatchStamp, :BatchStamp-Format) Using Date/Time Output Functions Specify the output format of any type of date/time column by using a date/time output function. Use an output function with any DML operation listed in Table 13-2 with one exception.
BEGIN DECLARE SECTION Declare input host variables (:TwelveHourClockFormat, :BatchStamp, :ItalianFormat, and :Speci edInput) to be compatible with data type CHAR or VARCHAR. Declare output host variables (:TestStart and :TestEnd) to be compatible with data type CHAR or VARCHAR . Declare output indicator variables (:TestStartInd and :TestEndInd). END DECLARE SECTION . . .
BEGIN DECLARE SECTION Use the ALLBASE/SQL Reference Manual to determine your desired format speci cation. (In this case it is Q.) Declare the input host variable, :QuarterlyFormat, to be compatible with data types CHAR or VARCHAR. Declare an output host variable (:TestDateQuarter) to be compatible with data type INTEGER. Declare other output host variables (:BatchStamp, :LabTime, :PassQty, and :TestQty) to be compatible with data type CHAR or VARCHAR.
Using the Date/Time ADD MONTHS Function This function allows you to add an integer number of months to a DATE or DATETIME column. Do so by indicating the number of months as a positive, negative, or unsigned integer value. (An unsigned value is assumed positive.) Also, you can specify the integer in a host variable of type INTEGER. The ADD MONTHS function can be used in both input and output operations as shown in Table 13-1.
Program Examples for Date/Time Data The example programs in this section are based on the manufacturing database and the purchasing database that are a part of the sample database environment, PartsDBE. (Reference the ALLBASE/SQL Reference Manual , appendix C.) Informative comments and explanations are present throughout each listing. The following programs are included: COBEX30, using date/time functions to allow input and display of DATE and DATETIME columns in European format.
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * This program illustrates the use of DATE/TIME functions. * Simple data manipulation commands are used on the TestData * table (part of the sampleDB). Rows can be selected,deleted,* or updated on the basis of the BatchStamp column (defined in* the table as of DATETIME data type). Any column that can * contain null values (any column except BatchStamp) can be * updated. Rows can also be inserted.
WORKING-STORAGE SECTION. EXEC SQL INCLUDE SQLCA END-EXEC. * * * * * * BEGIN HOST VARIABLE DECLARATIONS * * * * * * * EXEC SQL BEGIN DECLARE SECTION END-EXEC. * DATETIME column, not null * 01 BATCHSTAMP PIC X(23). 01 BATCHSTAMP2 PIC X(23). 01 BATCHSTAMP3 PIC X(23). * DATE column, nulls allowed * 01 TESTDATE PIC X(10). 01 TESTDATEIND SQLIND. * TIME column, nulls allowed * 01 TESTSTART PIC X(8). 01 TESTSTARTIND SQLIND. * TIME column, nulls allowed * 01 TESTEND PIC X(8). 01 TESTENDIND SQLIND.
01 01 01 01 OK NOTFOUND DEADLOCK NOMEMORY 01 RESPONSE. 05 RESPONSE-PREFIX 05 RESPONSE-SUFFIX 01 RESPONSE1 01 COUNTER 01 NUMFORMAT PIC PIC PIC PIC S9(9) S9(9) S9(9) S9(9) COMP COMP COMP COMP VALUE 0. VALUE 100. VALUE -14024. VALUE -4008. PIC PIC PIC PIC PIC X(1) VALUE SPACE. X(22) VALUE SPACES. S9(9) COMP. S9(4) COMP. ZZZZZ9. PROCEDURE DIVISION. A100-MAIN. - DISPLAY "Program COBEX30." DISPLAY "Using Date/Time Functions to Allow Input and Display " of DATE and DATETIME".
A200-CONNECT-DBENVIRONMENT. DISPLAY "Connect to PartsDBE". EXEC SQL CONNECT TO 'PartsDBE' END-EXEC. IF SQLCODE NOT = OK PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT PERFORM A500-TERMINATE-PROGRAM THRU A500-EXIT. A200-EXIT. EXIT. A300-BEGIN-TRANSACTION. DISPLAY " ". DISPLAY "Begin Work". EXEC SQL BEGIN WORK END-EXEC. IF SQLCODE NOT = OK PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT PERFORM A500-TERMINATE-PROGRAM THRU A500-EXIT. A300-EXIT. EXIT. A400-COMMIT-WORK. DISPLAY " ". DISPLAY "Commit Work".
A500-TERMINATE-PROGRAM. EXEC SQL RELEASE END-EXEC. STOP RUN. A500-EXIT. EXIT. B100-DISPLAY-MENU. DISPLAY " ". DISPLAY " ". DISPLAY " 1 . . . DISPLAY " 2 . . . DISPLAY " 3 . . . DISPLAY " 4 . . . DISPLAY " ". MOVE "Enter choice SELECT UPDATE DELETE INSERT rows rows rows rows from ManufDB.TestData table.". in ManufDB.TestData table.". from ManufDB.TestData table.". into ManufDB.TestData table.". or 0 to STOP > " TO PROMPT-USER. WRITE PROMPT-USER AFTER ADVANCING 1 LINE. ACCEPT RESPONSE1 FREE.
- - IF RESPONSE1 = 3 DISPLAY " " DISPLAY " *** Procedure to DELETE rows from ManufDB.TestD "ata *** " DISPLAY " " PERFORM C300-DELETE-DATA THRU C300-EXIT UNTIL FUNC-DONE MOVE SPACES TO FUNC-DONE-FLAG GO TO B100-EXIT. IF RESPONSE1 = 4 DISPLAY " " DISPLAY " *** Procedure to INSERT rows into ManufDB.Vendo "rs *** " DISPLAY " " PERFORM C400-INSERT-DATA THRU C400-EXIT UNTIL FUNC-DONE MOVE SPACES TO FUNC-DONE-FLAG GO TO B100-EXIT.
C100-SELECT-DATA. MOVE "Enter BatchStamp or 0 for MENU> " TO PROMPT-USER. WRITE PROMPT-USER AFTER ADVANCING 1 LINE. ACCEPT RESPONSE FREE. IF RESPONSE-PREFIX = ZERO AND RESPONSE-SUFFIX = SPACES MOVE "X" TO FUNC-DONE-FLAG GO TO C100-EXIT ELSE MOVE RESPONSE TO BATCHSTAMP. PERFORM A300-BEGIN-TRANSACTION THRU A300-EXIT. PERFORM D200-SQL-SELECT THRU D200-EXIT.
IF SQLCODE PERFORM ELSE IF SQLCODE DISPLAY DISPLAY ELSE PERFORM = OK C250-DISPLAY-UPDATE THRU C250-EXIT = NOTFOUND " " "Row not found!" S100-SQL-STATUS-CHECK THRU S100-EXIT. PERFORM A400-COMMIT-WORK THRU A400-EXIT. C200-EXIT. EXIT. C250-DISPLAY-UPDATE. PERFORM D100-DISPLAY-ROW THRU D100-EXIT. MOVE SPACES TO TESTDATE. MOVE "Enter New TestDate (0 for NULL)> " TO PROMPT-USER. WRITE PROMPT-USER AFTER ADVANCING 1 LINE. ACCEPT TESTDATE FREE. MOVE SPACES TO TESTSTART.
MOVE ZERO TO TESTQTY. MOVE "Enter New TestQty (0 for NULL)> " TO PROMPT-USER. WRITE PROMPT-USER AFTER ADVANCING 1 LINE. ACCEPT TESTQTY FREE. IF TESTDATE = 0 MOVE -1 TO TESTDATEIND ELSE MOVE 0 TO TESTDATEIND. IF TESTSTART = 0 MOVE -1 TO TESTSTARTIND ELSE MOVE 0 TO TESTSTARTIND. IF TESTEND = 0 MOVE -1 TO TESTENDIND ELSE MOVE 0 TO TESTENDIND. IF LABTIME = 0 MOVE -1 TO LABTIMEIND ELSE MOVE 0 TO LABTIMEIND. IF PASSQTY = 0 MOVE -1 TO PASSQTYIND ELSE MOVE 0 TO PASSQTYIND.
C250-EXIT. EXIT. C300-DELETE-DATA. MOVE "Enter BatchStamp or 0 for MENU> " TO PROMPT-USER. WRITE PROMPT-USER AFTER ADVANCING 1 LINE. ACCEPT RESPONSE FREE. IF RESPONSE-PREFIX = ZERO AND RESPONSE-SUFFIX = SPACES MOVE "X" TO FUNC-DONE-FLAG GO TO C300-EXIT ELSE MOVE RESPONSE TO BATCHSTAMP. PERFORM A300-BEGIN-TRANSACTION THRU A300-EXIT. PERFORM D200-SQL-SELECT THRU D200-EXIT.
IF SQLCODE NOT = OK PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT. C350-EXIT. EXIT. C400-INSERT-DATA. MOVE "Enter BatchStamp or 0 for MENU> " TO PROMPT-USER. WRITE PROMPT-USER AFTER ADVANCING 1 LINE. ACCEPT RESPONSE FREE. IF RESPONSE-PREFIX = ZERO AND RESPONSE-SUFFIX = SPACES MOVE "X" TO FUNC-DONE-FLAG GO TO C400-EXIT ELSE MOVE RESPONSE TO BATCHSTAMP. MOVE "Enter TestDate (0 for null)> " TO PROMPT-USER. MOVE SPACES TO TESTDATE. WRITE PROMPT-USER AFTER ADVANCING 1 LINE. ACCEPT TESTDATE FREE.
MOVE "Enter LabTime (0 for null)> " TO PROMPT-USER. MOVE SPACES TO LABTIME. WRITE PROMPT-USER AFTER ADVANCING 1 LINE. ACCEPT LABTIME FREE. IF LABTIME = 0 MOVE -1 TO LABTIMEIND ELSE MOVE 0 TO LABTIMEIND. MOVE "Enter PassQuantity (0 for null)> " TO PROMPT-USER. MOVE ZERO TO PASSQTY. WRITE PROMPT-USER AFTER ADVANCING 1 LINE. ACCEPT PASSQTY FREE. IF PASSQTY = 0 MOVE -1 TO PASSQTYIND ELSE MOVE 0 TO PASSQTYIND. MOVE "Enter TestQuantity (0 for null)> " TO PROMPT-USER. MOVE ZERO TO TESTQTY.
DISPLAY "INSERT row into ManufDB.TestData". EXEC SQL INSERT INTO MANUFDB.TESTDATA (BATCHSTAMP, TESTDATE, TESTSTART, TESTEND, LABTIME, PASSQTY, TESTQTY) VALUES (TO_DATETIME (:BATCHSTAMP, :BATCHSTAMP-FORMAT), TO_DATE (:TESTDATE :TESTDATEIND, :TESTDATE-FORMAT), :TESTSTART :TESTSTARTIND, :TESTEND :TESTENDIND, :LABTIME :LABTIMEIND, :PASSQTY :PASSQTYIND, :TESTQTY :TESTQTYIND) END-EXEC. IF SQLCODE NOT = OK PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT. PERFORM A400-COMMIT-WORK THRU A400-EXIT. C400-EXIT. EXIT.
IF LABTIMEIND < 0 DISPLAY " LabTime is NULL." ELSE DISPLAY " LabTime: " LABTIME. IF PASSQTYIND < 0 DISPLAY " PassQuantity is NULL." ELSE MOVE PASSQTY TO NUMFORMAT DISPLAY " PassQuantity: " NUMFORMAT. IF TESTQTYIND < 0 DISPLAY " TestQuantity is NULL." ELSE MOVE TESTQTY TO NUMFORMAT DISPLAY " TestQuantity: " NUMFORMAT. D100-EXIT. EXIT. D200-SQL-SELECT. DISPLAY "SELECT * FROM ManufDB.TestData". EXEC SQL SELECT END-EXEC.
S100-SQL-STATUS-CHECK. MOVE SPACE TO ABORT-FLAG. IF SQLCODE <= DEADLOCK MOVE "X" TO ABORT-FLAG. IF SQLCODE = NOMEMORY MOVE "X" TO ABORT-FLAG. PERFORM S200-SQLEXPLAIN UNTIL SQLCODE = 0. IF ABORT PERFORM A500-TERMINATE-PROGRAM THRU A500-EXIT. S100-EXIT. EXIT. S200-SQLEXPLAIN. EXEC SQL SQLEXPLAIN :SQLMESSAGE END-EXEC. DISPLAY SQLMESSAGE. S200-EXIT. EXIT. Figure 13-1.
Example Program Converting a Column from CHAR to DATE Data Type The next data conversion program is intended as a guide should you decide to convert any character (CHAR) columns in an existing table to a date/time data type. Before running this program, you must create a new table, PurchDB.NewOrders, in PartsDBE. This table is similar to the PurchDB.Orders table already existing in PartsDBE, except that the OrderDate column is of the DATE data type. (Reference the ALLBASE/SQL Reference Manual , appendix C.
Example Program to Convert from CHAR to Default Data Type * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * This program uses BULK FETCH and BULK INSERT commands to select all rows from the Orders table (part of the sample DBEnvironment, PartsDBE), convert the order date column from the CHAR data type to the DATE data type default format, and write all Orders table information to another table called NewOrders table (created previously by you as described in this chapter)
WORKING-STORAGE SECTION. EXEC SQL INCLUDE SQLCA END-EXEC. * * * * * * BEGIN HOST VARIABLE DECLARATIONS * * * * * * * EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 ORDERS. 05 EACH-ROW OCCURS 25 TIMES. 10 ORDERNUMBER PIC S9(9) COMP. 10 VENDORNUMBER PIC S9(9) COMP. 10 VENDORNUMBERIND SQLIND. 10 ORDERDATE PIC X(8). 10 ORDERDATEIND SQLIND. 01 STARTINDEX 01 NUMBEROFROWS PIC S9(4) COMP. PIC S9(4) COMP. 01 NEW-ORDERS. 05 EACH-ROW OCCURS 25 TIMES.
01 DATE-TRANSFER PIC X(8). 01 DATE-TRANSFER-FROM REDEFINES DATE-TRANSFER. 10 YEAR PIC X(4). 10 MONTH PIC X(2). 10 DAY-FROM PIC X(2). 01 DATE-TRANSFER-TO. 10 YEAR-TO 10 DASH 10 MONTH-TO 10 DASH2 10 DAY-TO PIC PIC PIC PIC PIC X(4). X VALUE '-'. X(2). X VALUE '-'. X(2). 01 01 01 01 01 01 COUNTER1 I OK NOTFOUND DEADLOCK NOMEMORY PIC PIC PIC PIC PIC PIC S9(9) S9(9) S9(9) S9(9) S9(9) S9(9) COMP COMP COMP COMP COMP COMP VALUE VALUE VALUE VALUE VALUE VALUE 0. 0. 0. 100. -14024. -4008.
PERFORM A200-CONNECT-DBENVIRONMENT THRU A200-EXIT. MOVE SPACE TO DONE-CONVERT. MOVE "X" TO ORDERS-OK. PERFORM A300-BEGIN-TRANSACTION THRU A300-EXIT. EXEC SQL OPEN ORDERSCURSOR KEEP CURSOR WITH LOCKS END-EXEC. IF SQLCODE NOT = OK PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT PERFORM A450-ROLLBACK-WORK THRU A450-EXIT MOVE SPACE TO ORDERS-OK MOVE "X" TO DONE-CONVERT. PERFORM B100-FETCH-OLD THRU B100-EXIT UNTIL DONE.
IF SQLCODE NOT = OK PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT PERFORM A500-TERMINATE-PROGRAM THRU A500-EXIT. A200-EXIT. EXIT. A300-BEGIN-TRANSACTION. *********************************************************************** * Subroutine to begin the transaction with cursor stability specified.* *********************************************************************** EXEC SQL BEGIN WORK CS END-EXEC.
*********************************************************************** * Subroutine to rollback the transaction. * *********************************************************************** DISPLAY "Rollback Work". EXEC SQL ROLLBACK WORK END-EXEC. IF SQLCODE NOT = OK PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT. A450-EXIT. EXIT. A500-TERMINATE-PROGRAM. *********************************************************************** * Subroutine to release PartsDBE.
* Set COUNTER1 to the number of rows fetched. * MOVE SQLERRD(3) TO COUNTER1. IF SQLCODE = OK PERFORM A400-COMMIT-WORK THRU A400-EXIT ELSE IF SQLCODE = NOTFOUND DISPLAY 'There are no Orders Table rows to FETCH.' MOVE "X" TO DONE-CONVERT ELSE PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT PERFORM A450-ROLLBACK-WORK THRU A450-EXIT MOVE SPACE TO ORDERS-OK MOVE "X" TO DONE-CONVERT. IF NOT-DONE PERFORM B200-TRANSFER-DATA THRU B200-EXIT. IF NOT-DONE PERFORM B300-INSERT-NEW THRU B300-EXIT. B100-EXIT. EXIT.
C200. MOVE ORDERNUMBER(I) TO NEW-ORDERNUMBER(I). MOVE VENDORNUMBER(I) TO NEW-VENDORNUMBER(I). C200-EXIT. EXIT. C205. * Here the old orderdate column data is moved to a data item * * to break it into the component parts of the default DATE format.* MOVE ORDERDATE(I) TO DATE-TRANSFER. MOVE YEAR TO YEAR-TO. MOVE MONTH TO MONTH-TO. MOVE DAY-FROM TO DAY-TO. MOVE DATE-TRANSFER-TO TO NEW-ORDERDATE(I). C205-EXIT. EXIT. B300-INSERT-NEW.
S100-SQL-STATUS-CHECK. *********************************************************************** * Subroutine to display error messages and terminate the program when * * the transaction has been rolled back by ALLBASE/SQL. * *********************************************************************** MOVE SPACE TO ABORT-FLAG. IF SQLCODE <= DEADLOCK MOVE 'X' TO ABORT-FLAG. IF SQLCODE = NOMEMORY MOVE 'X' TO ABORT-FLAG. PERFORM S200-SQLEXPLAIN UNTIL SQLCODE = 0.
Programming with TID Data Access Each row (tuple) in an ALLBASE/SQL table is stored at a database address on disk. This unique address is called the tuple identi er or TID. When using a SELECT statement, you can obtain the TID of any row. In turn, you can use this TID to specify the target row for a SELECT, UPDATE, or DELETE statement. TID functionality provides the fastest possible data access to a single row at a time (TID access) in conjunction with maximum coding exibility.
SELECT TID(sp), TID(o) FROM PurchDB.SupplyPrice sp, PurchDB.Orders o WHERE sp.VendorNumber = :VendorNumber AND o.VendorNumber = :VendorNumber Using the TID Function in a WHERE Clause When using the TID function in a WHERE clause, you provide an input parameter. For application programs, this parameter can be speci ed as a host variable, a constant, or a question mark (?) representing a dynamic parameter. The input parameter is a constant. For example: DELETE FROM PurchDB.
An SQLTID host variable consists of eight bytes of binary data and has the following format: Table 13-4. SQLTID Data Internal Format Content Byte Range Version Number 1 through 2 File Number 3 through 4 Page Number 5 through 7 Slot Number 8 The SQLTID version number is an optional input parameter. If not speci ed, the version number defaults to 0. If you do specify the version, it must always be 0. If a version other than 0 is speci ed, no rows will qualify for the operation.
DELETE FROM PurchDB.Parts WHERE TID() = :PartsTID AND PartName = 'Winchester Drive' However, in the next statement TID access would not be used because it uses an OR: DELETE FROM PurchDB.Parts WHERE TID() = :PartsTID1 OR TID() = :PartsTID2 See the \Expressions" chapter of the ALLBASE/SQL Reference Manual for a full explanation about using TID access. Verifying Data that is Accessed by TID It is important to note that a TID in ALLBASE/SQL is unique and is valid until its related data is deleted.
Coding Strategies Suppose you are writing an application that will be executed by many simultaneous users in an online transaction processing environment. You want each user to be able to locate and update just a few rows in a table that is frequently accessed by many users. The following scenario illustrates the use of two transactions with di erent isolation levels.
When all user changes have been entered, use a loop to compare the previously fetched rows (in OrdersArray) with the same rows as they now exist in the database. Begin your transaction with the RR isolation level. No other transaction can access the locked data until this transaction ends, providing maximum data integrity. BEGIN WORK RR For each entry in NewOrdersArray, do the following: SELECT TID(), INTO FROM WHERE * :TIDvalue, :OrderNumber, :VendorNumber, :OrderDate PurchDB.
updates in the inner loop. This use of a single COMMIT WORK for the multiple updates in the inner loop reduces overhead.
De ne two arrays, one (PartsArray) to hold the qualifying rows of the Parts table and another (NewPartsArray) to hold the rows that the user wants to change. Be sure to de ne an element in each array to hold the TID value. Declare the cursor (BulkCursor) used by the BULK FETCH loads the PartsArray. 44 5 that DECLARE BulkCursor CURSOR FOR SELECT TID(), PartNumber, PartName, SalesPrice FROM PurchDB.Parts Declare the cursor (TidCursor) used to UPDATE on the TID value.
The following COMMIT WORK 4 5 5 statement commits the updates VerifyAndUpdate and releases the locks held. 4 11 5 in COMMIT WORK 45 5 End Loop CLOSE BulkCursor 46 5 The nal COMMIT WORK 4 7 5 statement ends the transaction started by the BEGIN WORK RC 4 2 5 . Any locks still held are released. COMMIT WORK Begin the VerifyAndUpdate routine. 47 5 48 5 Assign to HostPartTid the TID value in NewPartsArray.
Index A access optimization, 1-14 validation, 1-14 active set, 6-12 ADD MONTHS function example with BULK SELECT, 13-11 syntax, 13-11 aggregate function, 6-4 simple data manipulation, 7-2 ALTER TABLE command syntax for LONG columns, 12-4 ANSI SQL1 level 2 specifying a default value, 4-16 ANSI SQL86 level 2 oating point data, 4-9 application development, 2-1 arrays, 4-24, 6-20 BULK SELECT, 4-5 declarations of, 4-24 atomic operation de ned, 5-2 authorization, 1-6 and program maintenance, 1-20 changing, 1-21 d
use of NOLIST reserved word, 2-28 where used, 2-28 COBOL SET and IF statement use of NOMIXED reserved word, 2-30 COBOL SET and IF statements where used, 2-30 COBOL subprograms, 2-1 coding considerations for date/time functions, 13-11 for LONG columns, 12-11, 12-12 column speci cations for oating point data, 4-9 comments, ALLBASE/SQL, 3-10 comments, COBOL, 3-9 COMMIT WORK, 1-12 de ning transactions, 3-12 in transaction management, 7-7 with CLOSE, 8-11 with KEEP CURSOR, 8-11 comparison predicate, 6-3 compiler
compatibility, 4-17 data type conversion, 4-17 data types, 4-8 binary, 4-11 compatibility, 4-17 equivalency, 4-17 oating point, 4-9 used with LONG columns, 12-2 date/time ADD MONTHS function overview, 13-11 where to use, 13-11 date/time data conversion example program, 13-29 example programs, 13-28 date/time functions, 13-1 coding considerations, 13-11 data compatibility, 13-2, 13-3 example program, 13-12 example programs, 13-12 examples using ManufDB database, 13-4, 13-8, 13-11 example using default format
input device speci cation, 12-5 LONG column I/O string, 12-5 output device speci cation, 12-5 row level integrity, 11-1 DELETE and simple data manipulation, 7-7 DELETE command used with LONG columns, 12-11 with TO DATETIME function, 13-7 DELETE WHERE CURRENT, 6-13 current row, 8-7 restrictions, 8-7 syntax, 8-7 DELETE WHERE CURRENT command used with LONG columns, 12-11 delimiting SQL commands, 1-5 designing an application using statement level integrity, 11-3 directives, 1-2 DML operations used with date/tim
executing programs, 1-18 explicit status checking de ned, 5-1 introduction, 3-15, 5-12 uses of, 5-23 expression, 6-4 F FETCH, 6-13 and null values, 8-4 cursor processing, 8-3 FETCH command used dynamically with LONG columns, 12-10 used with LONG columns, 12-7 with TO INTEGER function, 13-10 le Database Environment Con guration, 1-7 DBECon, 1-7 le IO KEEP CURSOR, 8-15 le name fully quali ed, 1-7 relative, 1-7 FLOAT data host variables and, 4-9 FLOAT data declaration, 4-9 oating point data 4-byte, 4-9 8-byte,
example application using RecDB database, 11-3 in RecDB database, 11-3 introduction to, 11-1 restrictions, 11-2 unique and referential, 11-2 J job mode, 2-49 join condition, 6-5 joining tables, 6-5 join variable, 6-7 K KEEP CURSOR DECLAREing for UPDATE, 8-10 example code, 8-16 le IO, 8-15 terminal IO, 8-15 KEEP CURSOR WITH NOLOCKS command use with OPEN command , 8-3, 8-10 L language current language, 1-8 native language support, 1-8, 2-31 linker, 1-15 separate linked objects, 1-5 locking and cursors, 6-13 t
related to SQLCODE, 5-6 messages from SQLEXPLAIN when produced, 5-7 modi ed source creation, 1-2 inserted statements, 1-10 sample, 5-22 modi ed source le, 2-4, 2-15 module creation, 1-12 DBE leset, 2-35 de nition, 1-2 installation, 1-17 name, 1-12, 2-35, 2-41 owner, 1-6, 2-35, 2-41 ownership, 1-17 storage, 10-2 updating, 1-20 multiple rows not allowed in simple data manipulation, 7-2 multiple rows qualify runtime error, 7-2 multiple users of LONG columns, 12-12 multiple warnings SQLEXPLAIN, 5-9 N name quali
invoking, 2-39 job mode, 2-49 logging, 2-42 modes, 2-3 modi ed source le, 2-4 modifying output of, 1-12 output, 2-4, 2-15 transactions, 1-12 UDC's, 2-43 vs.
with TO CHAR function, 13-8 with TO DATETIME function, 13-7, 13-8 with TO INTERVAL function, 13-7 select list, 6-2 SELECT with CURSOR input host variables only, 8-2 self-joins, 6-6 sequential table processing, 6-1 overview, 6-17 serial scan, 6-13 SET and statements in COBOL where used, 2-30 shared memory problem status checking, 5-27 simple data manipulation commands, 7-1 DELETE, 7-7 INSERT, 7-4 multiple rows not allowed, 7-2 overview, 6-10 sample program, 7-11 SELECT, 7-1 techniques, 7-1 transaction manage
START DBE, 3-12 StartIndex variable usage, 9-2 statement level integrity and integrity constraints, 11-3 status checking deadlock, 5-27 explicit, 3-15, 5-23 explicit de ned, 5-1 implicit, 3-13, 5-12 implicit de ned, 5-1 information available, 5-1 introduction to explicit, 5-12 kinds of, 5-12 procedures, 5-15, 5-23 purposes of, 5-2 runtime techniques, 5-2 shared memory problem, 5-27 status codes runtime status checking, 5-1 storage LONG columns, 12-4 stored sections, 2-35 string data truncation SQLWARN1, 4-2
using default data values introduction to, 4-16 using host variables, 4-28 using indicator variables assigning null values, 7-6 V validation, 1-14 varbinary data using the LONG phrase with, 4-11 VARCHAR data, 4-8 views and DELETE, 6-9 and SELECT, 6-8 and UPDATE, 6-9 restrictions, 6-9, 6-14 W warning message and SQLCODE, 5-9 and SQLWARN0, 5-9 and SQLWARN1, 5-9 SQLWARN2, 5-10 warning messages, 2-33, 3-16 warnings runtime handling, 5-2 WHENEVER components of, 5-12 duration of command, 5-7 for di erent conditi
