900 Series HP 3000 Computer Systems ALLBASE/SQL FORTRAN Application Programming Guide ABCDE HP Part No. 36216-90030 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 FORTRAN language source code. This manual is intended as a learning tool and a reference guide for FORTRAN programmers.
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 FORTRAN ALLBASE/SQL FORTRAN Programs . . . . . . . . . . . . Program Structure . . . . . . . . . . . . . . . . . . . . DBEnvironment Access . . . . . . . . . . . . . . . . . . Authorization . . . . . . . . . . . . . . . . . . . . . . File Referencing . . . . . . . . . . . . . . . . . . . . . Native Language Support . . . . . . . . . . . . . . . . . . The ALLBASE/SQL FORTRAN Preprocessor . . . . . . . . . E ect of Preprocessing on Source Code . . .
Preprocessor Invocation Errors . . . . . . . . . . . . . . . . . . . . Source File Errors . . . . . . . . . . . . . . . . . . . . . . . . . . DBEnvironment Errors . . . . . . . . . . . . . . . . . . . . . . . 3. Embedding SQL Commands General Rules for Embedding SQL . Location of SQL Commands . . . Pre x . . . . . . . . . . . . . FORTRAN Comments . . . . . . SQL Comments . . . . . . . . . Continuation Lines . . . . . . . Declaring the SQLCA . . . . . . . Declaring Host Variables . . . . . .
DBEnvironment Name . . . . . . . . . . . . . . . . . . Data Type Compatibility . . . . . . . . . . . . . . . . . Character Data Conversion . . . . . . . . . . . . . . . . Character Data Truncation . . . . . . . . . . . . . . . . Numeric Data Conversion . . . . . . . . . . . . . . . . Declaring Host Variables for Data Values and Indicator Variables Declaring Host Variables for Dynamic Commands . . . . . . . Declaring Host Variables for Savepoint Numbers . . . . . . .
7. Simple Data Manipulation Simple Data Manipulation Commands . . . . . . . . . . The SELECT Command . . . . . . . . . . . . . . . The INSERT Command . . . . . . . . . . . . . . . The DELETE Command . . . . . . . . . . . . . . The UPDATE Command . . . . . . . . . . . . . . Transaction Management for Simple Operations . . . . . Program Using SELECT, UPDATE, DELETE, and INSERT Select Function . . . . . . . . . . . . . . . . . . . Update Function . . . . . . . . . . . . . . . . . . Delete Function . . . . . .
10. Programming with Constraints Comparing Statement Level and Row Level Integrity . . . . . Using Unique and Referential Integrity Constraints . . . . . . Designing an Application Using Statement Level Integrity Checks Insert a Member in the Recreation Database . . . . . . . . Update an Event in the Recreation Database . . . . . . . . Delete a Club in the Recreation Database . . . . . . . . . Delete an Event in the Recreation Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Understanding TID Function Input and Output . . . Using the TID Function in a Select List . . . . . . Using the TID Function in a WHERE Clause . . . . Declaring TID Host Variables . . . . . . . . . . Understanding the SQLTID Data Format . . . . . Transaction Management with TID Access . . . . . . . Comparing TID Access to Other Types of Data Access Verifying Data that is Accessed by TID . . . . . . . Considering Interactive User Applications . . . . . . Index Contents-6 . . . . . . . . . . . . . . . . .
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. 6-1. 6-2. 7-1. 7-2. 7-3. 8-1. 8-2. 8-3. 8-4. 8-5. 8-6. Creating an ALLBASE/SQL FORTRAN Application Program . . . Preprocess-Time Events . . . . . . . . . . . . . . . . . . . . Compile-Time and Link-Time Events . . . . . . . . . . . . . . Runtime Events . . . . . . . . . . . . . . . . . . . . . . . Developing a FORTRAN Program . . . . . . .
9-1. 9-2. 9-3. 9-4. 9-5. 9-6. 9-7. 10-1. 11-1. 11-2. Creation and Use of a Program that has a Stored Module . . . . . . . Creation and Use of a Program that has No Stored Module . . . . . . Procedure Hosting Dynamic Non-Query Commands . . . . . . . . . . Runtime Dialog for Program forex9a . . . . . . . . . . . . . . . . Program forex9a: Sample Program Using EXECUTE IMMEDIATE . . . Runtime Dialog of Program forex9b . . . . . . . . . . . . . . . . . Program forex9b: Sample Program Using PREPARE and EXECUTE .
Tables 4-1. 4-2. 4-3. 4-4. 6-1. 10-1. 10-2. 11-1. 11-2. 12-1. 12-2. 12-3. 12-4. Data Description Entries for Host Variables . . . . . . . . . Program Element Declarations . . . . . . . . . . . . . . . ALLBASE/SQL Floating Point Column Speci cations . . . . . Data Type Equivalency and Compatibility . . . . . . . . . . How Data Manipulation Commands May Be Used . . . . . . Commands Used with Integrity Constraints . . . . . . . . . Constraint Test Matrix . . . . . . . . . . . . . . . . . .
Getting Started with ALLBASE/SQL Programming in 1 FORTRAN The steps in creating a FORTRAN application program that accesses an ALLBASE/SQL DBEnvironment are summarized in Figure 1-1. Figure 1-1.
Using your favorite editor, create FORTRAN source code. The source code is a compilable FORTRAN program or subprogram that contains SQL commands. The SQL commands contained within the FORTRAN program are said to be embedded. Refer to the ALLBASE/SQL Reference Manual for SQL terminology and usage rules. Before compiling the source code, it must be preprocessed using the ALLBASE/SQL FORTRAN preprocessor. The FORTRAN preprocessor: Checks the syntax of the SQL commands.
Program Structure The following skeleton program illustrates the relationship between FORTRAN statements and embedded SQL commands in an application program. SQL commands may appear in a program at locations indicated by boldface notations. The SQLCA Common Block Declaration may appear either before or after the FORTRAN type declaration section.
DBEnvironment Access You must always specify a DBEnvironment at preprocessing time. The preprocessor needs to access the DBEnvironment you specify in the INFO string. It does so in order to store a module containing permanent sections used by your application program at runtime. In this example, the environment is SomeDBE which is in the group and account SomeGroup.SomeAcct. : RUN PSQLFOR.PUB.SYS; INFO = "SomeDBE.SomeGroup.
To preprocess or to use an already preprocessed ALLBASE/SQL application program, you must satisfy the authorization requirements for each DBEnvironment accessed. Authorization ALLBASE/SQL authorization governs who can preprocess, execute, and maintain a program that accesses an ALLBASE/SQL DBEnvironment. To preprocess a program for the rst time, you need CONNECT or DBA authority in the DBEnvironment your program accesses.
the name stored in the DBECon le is ignored while creating this le. The user then needs to fully qualify this le name each time the le is referenced. Remember, a le, group, or account name can contain a maximum of 8 bytes. Fully quali ed le names, enclosed in quotes, are restricted to a maximum length of 36 bytes.
Run the MPE XL utility program NLUTIL.PUB.SYS to determine which native languages are supported on your system.
Figure 1-2. Preprocess-Time Events Effect of Preprocessing on Source Code The FORTRAN preprocessor scans the source code for SQL commands. If the syntax of an SQL command is valid, the preprocessor converts the command to compilable FORTRAN statements that call ALLBASE/SQL external procedures at runtime. During preprocessing, for example, the SQL command: 1 2 3 4 5 1-8 EXEC SQL SELECT PartNumber, PartName, SalesPrice INTO :PartNumber, :PartName, :SalesPrice FROM Purchdb.
is converted into the following modi ed source code statements: C**** Start SQL Preprocessor **** C EXEC SQL SELECT PartNumber, PartName, SalesPrice C 1 INTO :PartNumber, C 2 :PartName, C 3 :SalesPrice :SalesPriceInd C 4 FROM PurchDB.Parts C 5 WHERE PartNumber = :PartNumber C C**** Start Inserted Statements **** WRITE(SQLTMP,'(A16)')PartNumber CALL SQLXFE(SQLCAID,SQLOWN,SQLMDN,1,SQLTMP,16,56,1) IF (SQLCODE .EQ.
Effect of Preprocessing on DBEnvironments When you invoke the preprocessor, you name an ALLBASE/SQL DBEnvironment. The preprocessor starts a DBE session for that DBEnvironment when preprocessing begins and terminates that session when preprocessing ends. When the preprocessor encounters a syntactically correct SQL command, it creates a section and stores that section in the system catalog of the DBEnvironment being accessed.
Section Validity A section is assigned one of two states at preprocessing time: valid or invalid. A section is valid when access validation criteria are satis ed. If the SQL command references objects that exist at preprocessing time and the individual doing the preprocessing is authorized to issue the command, the stored section is marked as valid. A section is invalid when access validation criteria are not satis ed.
Figure 1-3. Compile-Time and Link-Time Events You must use native mode to compile and link your program. You submit to the FORTRAN compiler a modi ed source code le and the related include le(s) created by the preprocessor. The compiler then generates an object code le. To convert one or more object code les into an executable program, you link them by invoking the linker. This step creates an executable program le.
:HELLO PGMR1.ACCTDB.GROUP1 . . . :RUN PSQLFOR.PUB.SYS; INFO = 'SOMEDBE.GROUP2 (MODULE(SOMEMOD))' . . . :CCXLLK Modi edSourceFile,SOMEPROG,$NULL The Executable Program 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.
ISQL copies the module from the installable module le named SOMEMOD.GROUP2.ACCTDB into a DBEnvironment named SOMEDBE.GROUP3.ACCTDB. During installation, ALLBASE/SQL marks each section in the module valid or invalid, depending on the current objects and authorities in SOMEDBE.GROUP3.ACCTDB. To use the INSTALL command, you need to be able to start a DBE session in the DBEnvironment that is to contain the new module.
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=> isql=> isql=> CONNECT TO 'SOMEDBE.GROUP3.
Once you identify the ALLBASE/SQL message catalog and appropriate DBEnvironment, you can run the program: :RUN SOMEPROG.GROUP1.ACCTDB You must specify the name of an executable program le as SOMEPROG. Do not specify a module name in the RUN command. At runtime, an ALLBASE/SQL program interacts with the DBEnvironment as illustrated in Figure 1-4.
Figure 1-4.
Maintaining ALLBASE/SQL Programs After FORTRAN programs are in production use, changes in applications, personnel, or databases may necessitate: Updating application programs. Changing program-related authorizations. Obsoleting application programs. Updating Application Programs Minor modi cations to programs in use can often be made right on the production machine and production DBEnvironment, during hours the production DBEnvironment use is minimal.
Changing Program-Related Authorization Once a program is in production use, granting and revoking RUN and CONNECT authority may be necessary as program users change. Revoking CONNECT authority requires DBA authorization: isql=> REVOKE CONNECT FROM OLD@USER; Revoking RUN authority requires either module OWNER or DBA authority: isql=> REVOKE RUN ON PGMR1@GROUPC.
2 Using The ALLBASE/SQL FORTRAN Preprocessor ALLBASE/SQL FORTRAN application programs have the same stages of development as any application program. They originate as FORTRAN source code les that are subsequently compiled with the FORTRAN compiler and linked by the system linker to create an executable program le. The development of ALLBASE/SQL programs, however, requires that you preprocess those portions of the program that contain SQL commands before compilation .
Figure 2-1. Developing a FORTRAN Program In other cases, the ALLBASE/SQL application program might consist of a main program unit and one or more subprogram units in separate les. In these cases, only source les containing embedded SQL code need to be preprocessed, as illustrated in Figure 2-2. However, each program unit which contains SQL commands must be preprocessed and compiled before the next program unit is preprocessed.
Figure 2-2. Developing a FORTRAN Program with Subprograms During preprocessing, the FORTRAN preprocessor actually accesses the same DBEnvironment to be used by your main program or subprogram unit at runtime. The preprocessor stores a module in the DBEnvironment which is executed at runtime. The module is used at runtime to optimize and validate DBEnvironment operations.
FORTRAN Preprocessor Compiler Directives The FORTRAN preprocessor supports the following compiler directives: $[option ]SET (identi erlist ) $[option ]IF (conditionlist ) $[option ]ELSE $[option ]ENDIF $[option ]INCLUDE ' lename ' The $IF directive does not support logical operators. (Note that the compiler and preprocessor may not support the same things.) Only simple TRUE and FALSE logical variables are supported.
Preprocessor Input and Output Regardless of the mode you use, the following input les must be available when you invoke the FORTRAN preprocessor, as shown in Figure 2-3: source le: a le containing the source code of the FORTRAN program with embedded SQL commands for one or more DBEnvironments. The default input lename is: SQLIN An alternative name can be speci ed by using a le equation as shown later in this chapter.
Figure 2-3. FORTRAN Preprocessor Input and Output Figure 2-4.
If you want to preprocess several ALLBASE/SQL application programs in the same group and account and compile and link the programs later, or you plan to compile a preprocessed program during a future session, you should do the following for each program: Before running the preprocessor, equate SQLIN to the name of the le containing the application you want to preprocess: :FILE SQLIN = InFile After running the preprocessor, save and rename the output les if you do not want them overwritten.
Source File The preprocessor source le must contain at a minimum the following statements: PROGRAM Statement AnyStatement END When parsing the source le, the FORTRAN preprocessor ignores all FORTRAN statements and any FORTRAN compiler directives that are not supported. Only the following information is parsed by the FORTRAN preprocessor: The PROGRAM Statement or SUBROUTINE name.
Program to SELECT specified rows from the Parts table -- forex2 Event List: CONNECT TO PartsDBE BEGIN WORK SELECT specified row from the Parts table until user enters a "/" COMMIT WORK RELEASE PartsDBE CONNECT TO PartsDBE Enter PartNumber from Parts table or / to STOP > 1123-P-01 BEGIN WORK SELECT PartNumber, PartName, SalesPrice Part Number: 1123-P-01 Part Name: Central Processor Sales Price: 500.00 Was retrieved from the PurchDB.
C C C C C C C C C C C C C C C C C C C C C C C C C C C PROGRAM forex2 ********************************************************* * This program illustrates the use of SQL's SELECT * * command to retrieve one row or tuple of data at * * a time. This program executes a BEGIN WORK command * * before the SELECT command, and a COMMIT WORK command * * after executing the SELECT command. An indicator * * variable is also used for SalesPrice.
C C C C C C C C C C C C C C C (* Beginning of the Main Program *) WRITE (*,*) CHAR(27), 'U' WRITE (*,*) 'Program to SELECT specified rows from the Parts Table 1 -- forex2' WRITE (*,*) ' ' WRITE (*,*) 'Event List:' WRITE (*,*) ' CONNECT TO PartsDBE' WRITE (*,*) ' CONNECT TO ..
600 C C RETURN EXEC SQL WHENEVER SQLERROR CONTINUE END (* End of ConnectDBE Subroutine *) NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN SUBROUTINE BeginTransaction (* Subroutine to Begin Work *) C C NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN EXEC SQL INCLUDE SQLCA C C C C C (* Begin SQL Communication Area *) (* Begin Host Variable Declarations *) NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN
NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 500 C 600 C C C C C C C C EXEC GOTO CALL CALL SQL COMMIT WORK 600 SQLStatusCheck ReleaseDBE RETURN EXEC SQL WHENEVER SQLERROR CONTINUE END (* End EndTransaction Subroutine *) NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN SUBROUTINE ReleaseDBE (* Subroutine to Release PartsDBE *) NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN EXEC SQL INCLUDE SQLCA (* Begin
C SUBROUTINE DisplayRow (PartNumber,PartName,SalesPrice, 1SalesPriceInd) (* Subroutine to Display a Selected Row *) C C NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN EXEC SQL INCLUDE SQLCA C C C C C (* Begin SQL Communication Area *) (* Begin Host Variable Declarations *) NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN EXEC SQL BEGIN DECLARE SECTION CHARACTER*16 PartNumber CHARACTER*30 PartName DOUBLE PRECISION SalesPrice SQLIND Sale
C C C C (* Begin Host Variable Declarations *) NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN EXEC SQL BEGIN DECLARE SECTION CHARACTER*80 SQLMessage EXEC SQL END DECLARE SECTION NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN C C C (* End Host Variable Declarations *) DeadLock = -14024 Abort = .TRUE. WRITE (*,*) Abort IF (SQLCode .LT. DeadLock) THEN Abort = .TRUE. ELSE Abort = .FALSE. ENDIF DO WHILE (SQLCode .NE.
C C (* Begin Host Variable Declarations *) EXEC SQL BEGIN DECLARE SECTION CHARACTER*16 PartNumber CHARACTER*30 PartName DOUBLE PRECISION SalesPrice SQLIND SalesPriceInd CHARACTER*80 SQLMessage EXEC SQL END DECLARE SECTION NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN C C C C (* End Host Variable Declarations *) MultipleRows = -10002 DeadLock = -14024 NotFound = 100 OK =
C IF (SQLCode .EQ. OK) THEN CALL DisplayRow (PartNumber, PartName, SalesPrice, 1SalesPriceInd) ELSEIF (SQLCode .EQ. NotFound) THEN WRITE (*,*) 'Row not found!' ELSEIF (SQLCode .EQ. MultipleRows) THEN WRITE(*,*) 'WARNING: More than one row qualifies!' ELSE CALL SQLStatusCheck ENDIF CALL EndTransaction ENDIF END DO RETURN END C (* End QueryTable Subroutine *) Figure 2-6.
Output File Attributes When the source le illustrated in Figure 2-6 is preprocessed, the attributes of the output les are created as follows: :listftemp,2 TEMPORARY FILES FOR SOMEUSER.
Caution Although you can access the modi ed source le and the variable declaration le with an editor, you should never change the information generated by the FORTRAN preprocessor. Your DBEnvironment or other les on the system could be damaged at runtime if preprocessor generated statements are altered.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx C**** Start SQL Preprocessor **** $ALIAS SQLXCNHF = 'SQLXCNHF' PASCAL \ $ (%REF,%REF,%VAL,%VAL) $ALIAS SQLXCO = 'SQLXCO' PASCAL \ $ (%REF,%VAL,%REF) $ALIAS SQLXEXIF = 'SQLXEXIF' PASCAL \ $ (%REF,%REF,%VAL) $ALIAS SQLXEXUF = 'SQLXEXUF' PASCAL \ $ (%REF,%REF,%VAL,%REF,%VAL,%VAL,%REF,%VAL) $ALIAS SQLXFE = 'SQLXFE' PASCAL \ $ (%REF,%REF,%REF,%VAL,%REF,%VAL,%VAL,%VAL)
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx CHARCTER SQLWARN0,SQLWARN1,SQLWARN2,SQLWARN3, SQLWARN4,SQLWARN5,SQLWARN6,SQLWARN7 EQUIVALENCE (SQLWARN0,SQLWARN(0)), 1 (SQLWARN1,SQLWARN(1)), 2 (SQLWARN2,SQLWARN(2)), 3 (SQLWARN3,SQLWARN(3)), 4 (SQLWARN4,SQLWARN(4)), 5 (SQLWARN5,SQLWARN(5)), 6 (SQLWARN6,SQLWARN(6)), 7 (SQLWARN7,SQLWARN(7)) COMMON /SQLCA/ SQLCAID,SQLCABC,SQLCODE,SQ
NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN C C C C C INCLUDE 'SQLVAR' WRITE (*,*) CHAR(27), 'U' WRITE (*,*) 'Program to SELECT specified rows from the Parts Table 1 -- forex2' WRITE (*,*) ' ' WRITE (*,*) 'Event List:' WRITE (*,*) ' CONNECT TO PartsDBE' WRITE (*,*) ' BEGIN WORK' WRITE (*,*) ' SELECT specified row from the Parts table until use 1r enters a "/"' WRITE (*,*) ' COMMIT WORK' WRITE (*,*) ' RELEASE PartsDBE' CALL ConnectDBE CALL QueryTable CALL ReleaseDBE STOP END (* Beginning of the Sub
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 4 5 6 7 (SQLWARN4,SQLWARN(4)), (SQLWARN5,SQLWARN(5)), (SQLWARN6,SQLWARN(6)), (SQLWARN7,SQLWARN(7)) COMMON /SQLCA/ SQLCAID,SQLCABC,SQLCODE,SQLERRL, 1 SQLERRM,SQLERRP,SQLERRD,SQLWARN,SQLEXT C**** End SQL Preprocessor **** C**** Start SQL Preprocessor **** C EXEC SQL BEGIN DECLARE SECTION C**** End SQL Preprocessor **** C**** Start
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx C**** Start SQL Preprocessor **** C EXEC SQL INCLUDE SQLCA C C (* Begin SQL Communication Area *) C C (* Begin Host Variable Declarations *) C C**** Start Inserted Statements **** CHARACTER SQLCAID*8 INTEGER SQLCABC INTEGER SQLCODE INTEGER SQLERRL CHARACTER SQLERRM*256 CHARACTER SQLERRP*8 INTEGER SQLERRD(6) CHARACTER SQLWARN(0:7)
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx IF (SQLCODE .LT.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx C**** C C**** C**** C C C**** Start SQL Preprocessor **** EXEC SQL BEGIN DECLARE SECTION End SQL Preprocessor **** Start SQL Preprocessor **** EXEC SQL END DECLARE SECTION xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx INTEGER CHARACTER INTEGER CHARACTER SQLERRD(6) SQLWARN(0:7) SQLEXT(2) SQLWARN0,SQLWARN1,SQLWARN2,SQLWARN3, 1 SQLWARN4,SQLWARN5,SQLWARN6,SQLWARN7 EQUIVALENCE (SQLWARN0,SQLWARN(0)), 1 (SQLWARN1,SQLWARN(1)), 2 (SQLWARN2,SQLWARN(2)), 3 (SQLWARN3,SQLWARN(3)), 4 (SQLWARN4,SQLWARN(4)), 5 (SQLWARN5,
SUBROUTINE DisplayRow (PartNumber,PartName,SalesPrice, 1SalesPriceInd) (* Subroutine to Display a Selected Row *) C C C**** Start SQL Preprocessor **** C EXEC SQL INCLUDE SQLCA C C (* Begin SQL Communication Area *) C (* Begin Host Variable Declarations *) C C**** Start Inserted Statements **** CHARACTER SQLCAID*8 INTEGER SQLCABC INTEGER SQLCODE INTEGER SQLERRL CHARACTER SQLERRM*256 CHARACTER SQLERRP*8 INTEGER SQLERRD(6) CHARACTER SQLWARN(0:7) INTEGER SQLEXT(2) CHARACTER SQLWARN0,SQLWARN1,SQLWARN2,SQLWARN3
NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN WRITE(6,110) PartName IF (SalesPriceInd .LT. 0) THEN WRITE (*,*) 'Sales Price is NULL' ELSE WRITE(6,120) SalesPrice ENDIF WRITE (*,*) 'Was retrieved from the PurchDB.Parts table!' FORMAT(' Part Number: ',A16) FORMAT(' Part Name: ',A30) FORMAT(' SalesPrice: ',F10.
C (* Begin Host Variable Declarations *) C**** Start SQL Preprocessor **** C EXEC SQL BEGIN DECLARE SECTION C**** End SQL Preprocessor **** CHARACTER*80 SQLMessage C**** Start SQL Preprocessor **** C EXEC SQL END DECLARE SECTION C (* End Host Variable Declarations *) C C**** End SQL Preprocessor **** INCLUDE 'SQLVAR' DeadLock = -14024 Abort = .TRUE. WRITE (*,*) Abort IF (SQLCode .LT. DeadLock) THEN Abort = .TRUE. ELSE Abort = .FALSE. ENDIF DO WHILE (SQLCode .NE.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx INTEGER CHARACTER CHARACTER INTEGER CHARACTER INTEGER CHARACTER SQLERRL SQLERRM*256 SQLERRP*8 SQLERRD(6) SQLWARN(0:7) SQLEXT(2) SQLWARN0,SQLWARN1,SQLWARN2,SQLWARN3, 1 SQLWARN4,SQLWARN5,SQLWARN6,SQLWARN7 EQUIVALENCE (SQLWARN0,SQLWARN(0)), 1 (SQLWARN1,SQLWARN(1)), 2 (SQLWARN2,SQLWARN(2)), 3 (SQLWARN3,SQLWARN(3)), 4 (SQLWARN4,SQLWAR
110 C C FORMAT (A16) IF (PartNumber .NE. '/' ) THEN CALL BeginTransaction WRITE(*,*) 'SELECT PartNumber, PartName, SalesPrice' C C**** Start SQL Preprocessor **** C EXEC SQL SELECT PartNumber, PartName, SalesPrice C 1 INTO :PartNumber, C 2 :PartName, C 3 :SalesPrice :SalesPriceInd C 4 FROM PurchDB.Parts C 5 WHERE PartNumber = :PartNumber C C**** Start Inserted Statements **** WRITE(SQLTMP,'(A16)')PartNumber CALL SQLXFE(SQLCAID,SQLOWN,SQLMDN,1,SQLTMP,16,56,1) IF (SQLCODE .EQ.
Variable Declaration Include File The preprocessor generated include le (SQLVAR), contains declarations for variables referenced in preprocessor generated statements in the modi ed source le. Figure 2-8 illustrates the variable declaration include le that corresponds to the modi ed source le in Figure 2-7.
ALLBASE/SQL Message File Messages placed in SQLMSG come from the ALLBASE/SQL message catalog. The default catalog is SQLCTxxx.PUB.SYS. For native language users, the name of the catalog is SQLCT000.PUB.SYS, where NATIVE-3000 is the message catalog. If the default catalog cannot be opened, ALLBASE/SQL returns an error message indicating that the catalog le is not available. If the native language catalog is available, ALLBASE/SQL returns a warning message, indicating that the default catalog is being used.
Both the banner and the preprocessing summary output are echoed to the standard output, the terminal. As illustrated in Figure 2-9, a line number is often provided in SQLMSG. This line number references the line in the modi ed source le containing the command in question. A message accompanied by a number may also appear. You can refer to the ALLBASE/SQL Message Manual for additional information on the exception condition when these numbered messages appear. :EDITOR HP32201A.07.
As Figure 2-10 illustrates, the preprocessor can terminate with the warning message: ****** ALLBASE/SQL warnings. (DBWARN 10602) when the name of an object in the source le does not match the name of any object in the system catalog. Although a section is stored for the semantically incorrect command, the section is marked as invalid and will not execute at runtime if it cannot be validated. :EDITOR HP32201A.07.20 EDIT/3000 MON, JUL 10, 1991, 4:49 PM (C) HEWLETT-PACKARD CO.
Installable Module File When the FORTRAN 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. The name of this le is 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 PSQLFOR.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 DELETE WHERE CURRENT 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 contained in the compi
DBEFILESET: This column indicates the DBEFileSet with which DBEFiles housing the section are associated. 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. TYPE: This column identi es the type of section: 1 = SELECT associated with a cursor. 2 = SELECT de ning a view. 0 = All other sections.
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. FOREX2 contains only one section, for executing the SELECT command in the program illustrated in Figure 2-5.
Syntax Checking Mode You use the following command to only check the syntax of the SQL commands embedded in the source code le. Syntax :RUN PSQLFOR.PUB.SYS;INFO="(SYNTAX)" 1. The preprocessor does not access a DBEnvironment when it is run in this mode. 2. When performing only syntax checking, the preprocessor does not convert the SQL commands into FORTRAN constructs. Therefore the modi ed source code le does not contain any preprocessor generated calls to ALLBASE/SQL external procedures. 3.
Example :FILE SQLIN = FOREX2 :RUN PSQLFOR.PUB.SYS; INFO= "(SYNTAX)" MON, JUL 10, 1989, 4:48 PM HP36216-02A.E1.16 FORTRAN 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 HP32201A.07.20 EDIT/3000 TUE, JUN 21, 1991, 2:00 PM (C) HEWLETT-PACKARD CO. 1990 /T SQLMSG; L ALL UNN FILE UNNUMBERED .. .
Full Preprocessing Mode You use the following command to both check the embedded SQL command syntax and create compilable output les that can be processed by the FORTRAN compiler. This command also stores a module in the DBEnvironment named and creates a le containing an installable version of the module. Syntax :RUN PSQLFOR.PUB.SYS; INFO="DBEnvironmentName 9 2 8 MODULE(ModuleName) > > > > 6 > > > OWNER (OwnerName ) > 6 <8 9= 6( < PRESERVE = | 6 > DROP > > 4 > > REVOKE > > ; :: ;> 3 . ..
Description 1. When the program being preprocessed already has a stored module, be sure to use the DROP option, or else an error will result. Also, be sure that no one is currently executing the module when you invoke the preprocessor. To avoid con icts, do your preprocessing in single-user mode, during o hours. 2. The preprocessor starts a DBE session in the DBEnvironment named in the preprocessor command by issuing a CONNECT TO 'DBEnvironmentName ' command.
Example :FILE SQLIN=FOREX2 :RUN PSQLFOR.PUB.SYS;INFO=& "PartsDBE (MODULE(FOREX2) OWNER(OwnerP@SomeAcct) REVOKE DROP)" MON, JUL 10, 1991, 4:48 PM HP36216-02A.E1.16 FORTRAN 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 HP32201A.07.20 EDIT/3000 TUE, JUN 21, 1991, 2:00 PM (C) HEWLETT-PACKARD CO. 1990 /T SQLMSG; L ALL UNN FILE UNNUMBERED . . .
Using the Preprocessor UDC's Two UDC's for invoking the FORTRAN preprocessor are provided with ALLBASE/SQL in the HPSQLUDC.PUB.SYS le: PFOR, 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). :PFOR SourceFileName,DBEnvironment The PFOR UDC uses the following preprocessor INFO string parameters: ModuleName is the name of the source le.
PFOR srcfile,dbefile,msgfile=$stdlist continue setvar _savefence hpmsgfence setvar hpmsgfence 2 continue purge !msgfile purge sqlout purge sqlmod purge sqlvar 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 continue run psqlfor.pub.
PPFOR srcfile,dbefile,pgmfile,msgfile=$stdlist continue setvar _savefence hpmsgfence setvar hpmsgfence 2 continue purge !msgfile purge sqlout purge sqlmod purge sqlvar 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 continue run psqlfor.pub.
The example in Figure 2-14 illustrates the use of PPFOR on an SQLIN that could be successfully preprocessed, compiled, and linked. :PPFOR FOREX2,PARTSDBE,FOREX2R MON, JUL 10, 1989, 3:43 HP36216-02A.03.01 FORTRAN Preprocessor/3000 ALLBASE/SQL (C) COPYRIGHT HEWLETT-PACKARD CO., 1982,1983,1984,1985,1986,1987,1988, 1989,1990,1991. ALL RIGHTS RESERVED SQLIN DBEnvironment = FOREX2.SOMEGRP.SOMEACCT = partsdbe Module Name = FOREX2 1 Sections stored in DBEnvironment. 0 ERRORS 0 WARNINGS END OF PREPROCESSING.
Running the Preprocessor in Job Mode You can preprocess FORTRAN programs in job mode. Figure 2-15 illustrates a job le that uses the PPFOR UDC to preprocess several sample programs. !JOB JOANN,MGR.HPDB,FORTRAN;OUTCLASS=,1 !ppfor forp01,PartsDBE,forp01r !ppfor forp01a,PartsDBE,forp01ar !ppfor forp02,PartsDBE,forp02r . . !ppfor for50,PartsDBE,for50r !TELL JOANN,MGR.HPDB; FORTRAN Preprocessing is complete! !EOJ Figure 2-15.
Preprocessor Invocation Errors If the source le speci ed is currently being accessed, or if the source le named cannot be found, preprocessing terminates with the following messages: Input source file not found. (DBERR 10921) 1 ERRORS 0 WARNINGS END OF PREPROCESSING. If the invocation line names a DBEnvironment that does not exist or contains erroneous syntax, preprocessing terminates as follows: ERRORS Processing terminated prematurely.
3 Embedding SQL Commands In every FORTRAN program, you embed SQL commands in the main program unit and/or in a subprogram unit in order to: 415 Declare the SQL Communications Area (SQLCA). 425 Declare host variables. 435 Start a DBE session. 445 455 De ne transactions. 465 Implicitly check the status of SQL command execution. 475 Terminate a DBE session. 485 De ne or manipulate data in a DBEnvironment. 495 Explicitly check the status of SQL command execution.
C C C C C C C C C C C C C C C C C C C C C C C C C C C C C C C C C PROGRAM forex2 ********************************************************* * This program illustrates the use of SQL's SELECT * * command to retrieve one row or tuple of data at * * a time. This program executes a BEGIN WORK command * * before the SELECT command, and a COMMIT WORK command * * after executing the SELECT command. An indicator * * variable is also used for SalesPrice.
C C C C C C C C C C C C C C C C C 500 3-4 (* Beginning of the Main Program *) WRITE WRITE 1Parts WRITE WRITE WRITE WRITE WRITE 1table WRITE WRITE (*,*) (*,*) Table (*,*) (*,*) (*,*) (*,*) (*,*) until (*,*) (*,*) CHAR(27), 'U' 'Program to SELECT specified rows from the 1 -- forex2' ' ' 'Event List:' ' CONNECT TO PartsDBE' ' BEGIN WORK' ' SELECT specified row from the Parts use 1r enters a "/"' ' COMMIT WORK' ' RELEASE PartsDBE' CALL ConnectDBE CALL QueryTable CALL ReleaseDBE STOP END (* Beginning of
600 C C C C C C C C C C C 500 C 600 C C C C C C C C C C C C C RETURN EXEC SQL WHENEVER SQLERROR CONTINUE END (* End of ConnectDBE Subroutine *) SUBROUTINE BeginTransaction (* Subroutine to Begin Work *) EXEC SQL INCLUDE SQLCA (* Begin SQL Communication Area *) (* Begin Host Variable Declarations *) EXEC SQL BEGIN DECLARE SECTION EXEC SQL END DECLARE SECTION EXEC SQL WHENEVER SQLERROR GOTO 500 WRITE (*,*) 'BEGIN WORK' EXEC SQL BEGIN WORK GOTO 600 CALL SQLStatusCheck CALL EndTransaction CALL ReleaseDBE 445
500 C 600 C C C C C C C C C C 500 C 600 3-6 EXEC GOTO CALL CALL SQL COMMIT WORK 600 SQLStatusCheck ReleaseDBE 455 RETURN EXEC SQL WHENEVER SQLERROR CONTINUE END (* End EndTransaction Subroutine *) SUBROUTINE ReleaseDBE (* Subroutine to Release PartsDBE *) EXEC SQL INCLUDE SQLCA (* Begin SQL Communication Area *) (* Begin Host Variable Declarations *) EXEC SQL BEGIN DECLARE SECTION EXEC SQL END DECLARE SECTION EXEC SQL WHENEVER SQLERROR GOTO 500 WRITE (*,*) 'RELEASE PartsDBE' EXEC SQL RELEASE GOTO 600
C C C C C C C C C C 100 110 120 C C C C C C C C C C C SUBROUTINE DisplayRow (PartNumber,PartName,SalesPrice, 1SalesPriceInd) (* Subroutine to Display a Selected Row *) EXEC SQL INCLUDE SQLCA (* Begin SQL Communication Area *) (* Begin Host Variable Declarations *) EXEC SQL BEGIN DECLARE SECTION CHARACTER*16 PartNumber CHARACTER*30 PartName DOUBLE PRECISION SalesPrice SQLIND SalesPriceInd CHARACTER*80 SQLMessage EXEC SQL END DECLARE SECTION WRITE(*,100) PartNumber WRITE(*,110) PartName IF (SalesPriceInd .
C C C C C C C C C C C C C 3-8 (* Begin Host Variable Declarations *) EXEC SQL BEGIN DECLARE SECTION CHARACTER*80 SQLMessage EXEC SQL END DECLARE SECTION (* End Host Variable Declarations *) DeadLock = -14024 Abort = .TRUE. WRITE (*,*) Abort IF (SQLCode .LT. DeadLock) THEN Abort = .TRUE. ELSE Abort = .FALSE. ENDIF DO WHILE (SQLCode .NE.
C C C C C (* Begin Host Variable Declarations *) EXEC SQL BEGIN DECLARE SECTION CHARACTER*16 PartNumber CHARACTER*30 PartName DOUBLE PRECISION SalesPrice SQLIND SalesPriceInd CHARACTER*80 SQLMessage EXEC SQL END DECLARE SECTION (* End Host Variable Declarations *) MultipleRows = -10002 DeadLock = -14024 NotFound = 100 OK = 0 DO WHILE (PartNumber .NE. '/') WRITE(*,100) 100 FORMAT(/$,' Enter PartNumber from Parts table 1or / to STOP > ') READ(*,110) PartNumber 110 FORMAT (A16) C IF (PartNumber .NE.
C 3-10 IF (SQLCode .EQ. OK) THEN CALL DisplayRow (PartNumber, PartName, SalesPrice) ELSEIF (SQLCode .EQ. NotFound) THEN WRITE (*,*) 'Row not found!' ELSEIF (SQLCode .EQ. MultipleRows) THEN WRITE(*,*) 'WARNING: More than one row qualifies!' ELSE CALL SQLStatusCheck ENDIF CALL EndTransaction ENDIF END DO RETURN END (* End QueryTable Subroutine *) Embedding SQL Commands Figure 3-1.
General Rules for Embedding SQL Embedded SQL commands must appear in certain locations within the FORTRAN program. Each embedded SQL command must be accompanied by the pre x EXEC SQL. Comments may be placed within an embedded command, and an embedded SQL command may continue for several lines. An embedded SQL command has no maximum length . A dynamic SQL command can be no longer than 2048 bytes.
FORTRAN Comments You may insert FORTRAN comment lines within or between embedded SQL commands. Denote comment lines by placing the letter C in column 1 and entering the comment in columns 7 through 72: C C C EXEC SQL SELECT PartNumber, PartName put the data into the following host variables 1 INTO :PartNumber, :PartName find the data in the following table 2 FROM PurchDB.
Declaring the SQLCA The SQL Communication Area (SQLCA) is an ALLBASE/SQL data structure that contains current information about a program's DBE session. Every ALLBASE/SQL FORTRAN program unit that contains embedded SQL statements must contain an SQLCA declaration. When a main program unit or a subprogram unit starts a DBE session, the SQLCA declaration must be in its Type Declaration Section.
Declaring Host Variables Variables used in SQL commands are known as host variables. All host variables used in either a main program unit or a subprogram unit must be declared in the Type Declaration Section of the program unit where the host variable is used. The host variable declarations must be the last declarations in the Type Declaration Section of the program unit which contains the embedded SQL statements and must appear between the two following SQL commands: EXEC SQL BEGIN DECLARE SECTION . .
Starting a DBE Session In most application programs you embed the CONNECT command to start a DBE session in a program: EXEC SQL CONNECT TO DBEnvironmentName If autostart mode is ON at runtime, 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.
Defining Transactions You de ne transactions in a program unit to control what changes get committed to a DBEnvironment and when they get committed. A transaction consists of all the SQL commands that are executed between a BEGIN WORK command and either a COMMIT WORK command or a ROLLBACK WORK command. When a COMMIT WORK command is successfully executed, all operations performed by the transaction it ends have a permanent e ect on the DBEnvironment.
Implicit Status Checking You can use the WHENEVER command, as at 465 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 for each subsequent SQL command, if an error or warning condition occurs during execution of the SQL command.
Terminating a DBE Session As illustrated at 475 in Figure 3-1, you can terminate a DBE session with the RELEASE option of the COMMIT WORK command. The program in Figure 3-1 terminates its DBE session whenever: The user enters a slash (/) in response to the prompt in subprogram unit QueryTable . The program encounters an error serious enough to set Abort to .TRUE. in subprogram unit SQLStatusCheck . The program encounters an error when processing the CONNECT, BEGIN WORK, or COMMIT WORK commands.
Data Manipulation SQL has four basic data manipulation commands: SELECT: retrieves data. INSERT: adds rows. DELETE: deletes rows. UPDATE: changes column values. These four commands can be used for various types of data manipulation operations: Simple data manipulation: operations that retrieve single rows, insert single rows, or delete or update a limited number of rows. Processing with cursors: operations that use a cursor to operate on a row at a time within a set of rows.
Explicit Status Checking In explicit status checking, shown at 9 in Figure 3-1, you explicitly examine an SQLCA eld for a particular value, then perform an operation depending on the eld's value. In this example the SQLCA eld named SQLCode is examined to determine whether it contains a value of: 0, indicating no error occurred. 100, indicating no rows quali ed for the SELECT operation. -10002, indicating more than one row quali ed for the SELECT operation.
4 Host Variables Host variables are data items used in SQL commands in both the main program unit and in subprogram units. They are used to pass the following information between an application program and ALLBASE/SQL: Data values. Null value indicators. Dynamic commands. Savepoint numbers. Messages from the ALLBASE/SQL message catalog.
To pass dynamic commands at runtime, achieved by using the following commands: PREPARE EXECUTE IMMEDIATE To hold savepoint numbers , which are used in the following commands: SAVEPOINT ROLLBACK WORK TO To hold messages from the ALLBASE/SQL message catalog, obtained by using the SQLEXPLAIN command. This chapter provides examples illustrating where, in the commands itemized above, the SQL syntax supports host variables.
Data Values and Null Indicators Host variables containing data values can be input or output host variables. In the following SELECT command, the INTO clause contains two output host variables : PartNumber and PartName ; ALLBASE/SQL puts data from the PurchDB.Parts table into these host variables. The WHERE clause contains one input host variable : PartNumber ; ALLBASE/SQL reads data from this host variable to determine which row to retrieve.
Any column not de ned with the NOT NULL attribute may contain null values. In the PurchDB.Parts table, ALLBASE/SQL prevents the PartNumber column from containing null values, because it was de ned as NOT NULL. In the other two columns, however, null values may occur: CREATE PUBLIC TABLE PurchDB.Parts 1 (PartNumber CHAR(16) NOT NULL, 2 PartName CHAR(30), 3 SalesPrice DECIMAL(10,2)) 4 IN WarehFS Null values have certain properties that you need to remember when manipulating data that may be null.
Declaring Host Variables You must declare all host variables in the Host Variable Declaration Section of the program unit where the host variable is used. In other words, a host variable used in the main program unit must be de ned in the Host Variable Declaration Section of the main program unit. A host variable used in a subprogram unit must be de ned in the Host Variable Declaration Section of that subprogram unit.
PROGRAM Main . . . EXEC SQL INCLUDE SQLCA . . . EXEC SQL BEGIN DECLARE SECTION . . Declarations for host variables . EXEC SQL END DECLARE SECTION . . Embedded SQL commands . END SUBROUTINE Query . . . EXEC SQL INCLUDE SQLCA . . . EXEC SQL BEGIN DECLARE SECTION . . Declarations for host variables . EXEC SQL END DECLARE SECTION . . Embedded SQL commands . RETURN END Figure 4-1.
Data Types Table 4-1 summarizes how to write data descriptions for host variables holding each type of ALLBASE/SQL data. It also illustrates how to declare indicator variables, and host variables that hold dynamic commands, savepoint numbers, message catalog messages, and DBEnvironment names. Only the type declarations shown in Table 4-1 are supported by the FORTRAN preprocessor. The preprocessor does not support user de ned data types.
Table 4-1. Data Description Entries for Host Variables SQL DATA TYPES FORTRAN DATA DECLARATIONS CHAR(1) CHARACTER DataName CHAR(n) CHARACTER*n DataName VARCHAR(n) CHARACTER*n DataName * SMALLINT INTEGER*2 DataName INTEGER INTEGER DataName REAL REAL DataName or REAL*4 DataName FLOAT(1..24) REAL DataName or REAL*4 DataName FLOAT(1..
Table 4-2. Program Element Declarations PROGRAM ELEMENT FORTRAN DATA DECLARATIONS Indicator variable SQLIND or INTEGER*2 IndVarName Dynamic commands CHARACTER*n CommandName Savepoint numbers INTEGER SavepointName Message catalog messages CHARACTER*n MessageName DBEnvironment name CHARACTER*n DBEnvironmentName FLOAT Data ALLBASE/SQL o ers the option of specifying the precision of oating point data. You have the choice of a 4-byte or an 8-byte oating point number.
Floating Point Data Compatibility. Floating point data types are compatible with each other and with other ALLBASE/SQL numeric data types (DECIMAL, INTEGER, and SMALLINT). All arithmetic operations and comparisons and aggregate functions are supported. 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.
DATE, TIME, DATETIME, and INTERVAL Data DATE, TIME, DATETIME, and INTERVAL data types are declared as character strings. (See the previous section, \CHARACTER Data.
Your default speci cation options are: NULL. USER (this indicates the current DBEUser ID). A constant. The result of the CURRENT DATE function. The result of the CURRENT TIME function. The result of the CURRENT DATETIME function. Complete syntax for the CREATE TABLE and ALTER TABLE commands as well as de nitions of the above options are found in the ALLBASE/SQL Reference Manual .
CREATE PUBLIC TABLE PurchDB.Orders ( OrderNumber INTEGER DEFAULT NULL NOT NULL , VendorNumber INTEGER, OrderDate CHAR(8)) IN OrderFS NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN Indicator Variable Declaration Each indicator variable must be declared immediately following the host variable it describes.
Table 4-4. Data Type Equivalency and Compatibility CHARACTER*n INTEGER DOUBLE PRECISION REAL*8 CHAR E I I VARCHAR E I I BINARY C I I VARBINARY C I I DATE C I I TIME C I I DATETIME C I I INTERVAL C I I SMALLINT I E C INTEGER I E C DECIMAL I C C REAL I C C FLOAT I C C ALLBASE/SQL DATA TYPE In some cases, data conversion may lead to truncation or over ow.
Character Data Truncation If the target host variable used in a SELECT or FETCH operation is too small to hold an entire string, the string is truncated. You can use an indicator variable to determine the actual length of the string in bytes before truncation: SUBROUTINE Select . . . EXEC SQL BEGIN DECLARE SECTION CHARACTER*40 LittleString SQLIND LittleStringInd EXEC SQL END DECLARE SECTION . . . EXEC SQL SELECT BigString 1 INTO :LittleString :LittleStringInd . . .
Numeric Data Conversion When you use numeric data of di erent types in an expression or comparison operation, data of the lesser type is converted into data of the greater type, and the result is expressed in the greater type. ALLBASE/SQL numeric types available in FORTRAN have the following precedence: DOUBLE PRECISION INTEGER The following example illustrates numeric type conversion: SUBROUTINE Select . . .
Declaring Host Variables for Data Values and Indicator Variables As the following example illustrates, the INFO command available in ISQL provides the information you need to declare host variables compatible with or equivalent to ALLBASE/SQL data types. It also provides the information you need to determine whether an indicator variable is needed to handle null values: isql=> INFO PurchDB.
SUBROUTINE Select . . . EXEC SQL BEGIN DECLARE SECTION DOUBLE PRECISION Discount DOUBLE PRECISION PurchasePrice INTEGER*2 OrderQty SQLIND OrderQtyInd INTEGER OrderNumber EXEC SQL END DECLARE SECTION . . . EXEC SQL SELECT PurchasePrice * :Discount, 1 OrderQty 2 INTO :PurchasePrice, 3 :OrderQty :OrderQtyInd 4 FROM PurchDB.OrderItems 5 WHERE OrderNumber = :OrderNumber . . . RETURN END Figure 4-2.
OrderNumber is an INTEGER variable because the column whose data it holds, OrderNumber , is INTEGER. LowValue and HighValue are both declared as INTEGER host variables because they hold data compared with that in a column de ned as INTEGER. GroupCriterion is declared as an INTEGER host variable because its value is compared in the HAVING clause with the result of a COUNT function, which is always an INTEGER value. SUBROUTINE Select . . .
Declaring Host Variables for Dynamic Commands The command illustrated in Figure 4-4 names a host variable, DynamicCommand , for receiving an SQL command at runtime. This host variable should be declared as a CHARACTER variable. SUBROUTINE Prepare . . . EXEC SQL BEGIN DECLARE SECTION CHARACTER*1024 DynamicCommand EXEC SQL END DECLARE SECTION . . . EXEC SQL PREPARE CommandOnTheFly 1 FROM :DynamicCommand . . . RETURN END Figure 4-4.
Declaring Host Variables for Savepoint Numbers The command illustrated in Figure 4-5 below sets a savepoint. The number associated with the savepoint is the number ALLBASE/SQL places in the host variable named SavePoint1 . This host variable should be declared as INTEGER. SUBROUTINE SavePoint1 . . . EXEC SQL BEGIN DECLARE SECTION INTEGER SavePoint1 EXEC SQL END DECLARE SECTION . . . EXEC SQL SAVEPOINT :SavePoint1 . . . RETURN END Figure 4-5.
Declaring Host Variables for Message Catalog Messages The command illustrated in Figure 4-6 below puts a message from the ALLBASE/SQL message catalog into a host variable named SQLMessage . The following example illustrates how the host variable for holding the message might be declared. 10 . . . SUBROUTINE SQLStatusCheck . . . EXEC SQL BEGIN DECLARE SECTION CHARACTER*132 SQLMessage EXEC SQL END DECLARE SECTION . . .
Declaring Host Variables Passed from Subprograms The example illustrated in Figure 4-7 below illustrates how to pass a host variable value between one subprogram unit and another subprogram unit. The passed host variable must be declared in both the CallingProgram's and the CalledProgram's type declaration section. Variables that are not used in an SQL command in the program need to be declared outside the Type Declaration Section for Host Variables. SUBPROGRAM CallingProgram . . .
1 2 3 4 5 6 SUBROUTINE INSERTSubpgm (PartNumber,PartName,SalesPrice) . . . EXEC SQL INCLUDE SQLCA . . . EXEC SQL BEGIN DECLARE SECTION CHARACTER*16 PartNumber CHARACTER*30 PartName DOUBLE PRECISION SalesPrice EXEC SQL END DECLARE SECTION . . . EXEC SQL INSERT INTO PurchDB.Parts (PartNumber, PartName, SalesPrice) VALUES (:PartNumber, :PartName :SalesPrice) . . . RETURN END Figure 4-7.
SUBROUTINE Dates EXEC SQL INCLUDE SQLCA EXEC SQL BEGIN DECLARE SECTION CHARACTER*8 OrderDate INTEGER OrderNumber INTEGER ItemNumber CHARACTER*30 PartName CHARACTER*16 PartNumber DOUBLE PRECISION PurchasePrice EXEC SQL END DECLARE SECTION . . . OPEN (10, FILE = 'OrderDateFile', 1 ACCESS = 'sequential', STATUS = 'old') READ (10,103) OrderDate 103 FORMAT(A8) CLOSE (10) 1 2 3 4 5 6 7 8 9 1 2 3 4 5 6 EXEC SQL SELECT INTO FROM . . . RETURN END WHERE AND AND AND A.OrderNumber, A.ItemNumber, C.PartName, A.
Declaring Host Variables for DBEnvironment Names The DBEnvironment whose name is stored in the host variable named SomeDBE is declared and initialized as illustrated in Figure 4-9. 101 102 EXEC SQL BEGIN DECLARE SECTION . . . CHARACTER*128 SomeDBE . . . EXEC SQL END DECLARE SECTION . . . WRITE (6,101) 'Enter DBEnvironment name >' FORMAT (A80) READ (5,102) SomeDBE FORMAT (A128) . . . EXEC SQL CONNECT to :SomeDBE; Figure 4-9.
5 Runtime Status Checking and the SQLCA When an SQL command is executed, ALLBASE/SQL returns information describing how the command executed. This information signals one or more of the following conditions: The command was successfully executed. The command could not be executed because an error condition occurred, but the current transaction may continue. No rows quali ed for a data manipulation operation. A speci c number of rows were placed into output host variables.
These data items can be used in several ways to perform runtime status checking: You can use the WHENEVER command to perform implicit status checking. When you use this command, ALLBASE/SQL checks the SQLCode and SQLWarn(0) values for you, then takes an action based on information you provide in the WHENEVER command. You can write FORTRAN code that explicitly examines one or more of the SQLCA data items, then proceeds on the basis of their values.
Maintaining Data Consistency Two or more data values, rows, or tables are said to be consistent if they agree in some way. Changes to such interdependent values are either committed or rolled back at the same time in order to retain data consistency. In other words, the set of operations that form a transaction are considered as an Atomic Operation; either all or none of the operations are performed on the database.
Determining Number of Rows Processed Knowing such information as the following about rows your program handles helps determine the action to take in the program: No rows qualify for a data retrieval or change operation. A certain number of rows were retrieved by ALLBASE/SQL and placed in output host variables. A certain number of rows were inserted, deleted, or updated. When no rows qualify for an SQL command that retrieves, inserts, or changes rows, ALLBASE/SQL sets SQLCode to 100.
When more than one row quali es for a SELECT operation, SQLCode is set to -10002, and ALLBASE/SQL returns none of the rows. Your program can warn the user that no rows could be displayed: 102 SUBROUTINE SQLStatusCheck . . . IF (SQLCode .EQ. -10002) THEN WRITE(6,102) 'More than one row qualified for ' WRITE(6,102) 'this operation; none of the rows ' WRITE(6,102) 'can be displayed.
1 SQLERRP*8 SQLERRD(6) SQLWARN(0:7) SQLEXT(2) SQLWARN0,SQLWARN1,SQLWARN2,SQLWARN3, 1 SQLWARN4,SQLWARN5,SQLWARN6,SQLWARN7 EQUIVALENCE (SQLWARN0,SQLWARN(0)), 1 (SQLWARN1,SQLWARN(1)), 1 (SQLWARN2,SQLWARN(2)), 1 (SQLWARN3,SQLWARN(3)), 1 (SQLWARN4,SQLWARN(4)), 1 (SQLWARN5,SQLWARN(5)), 1 (SQLWARN6,SQLWARN(6)), 1 (SQLWARN7,SQLWARN(7)) COMMON /Sqlca/ SQLCAID,SQLCABC,SQLCODE,SQLERRL, 1 SQLERRM,SQLERRP,SQLERRD,SQLWARN,SQLEXT C**** End SQL Preprocessor **** INTEGER CHARACTER INTEGER CHARACTER The following elds in t
Note that when you execute UPDATE or DELETE commands dynamically and no rows qualify for the operation, SQLCode is not set to 100. You can use SQLErrd(3) to detect this condition as discussed later in this chapter. Negative SQLCode values are the same as the numbers associated with their corresponding messages in the ALLBASE/SQL message catalog. For example, the error message associated with an SQLCode of -2613 is: Precision digits lost in decimal operation multiply.
The subroutine named SQLStatusCheck is executed when SQLCode is a negative number . Before executing SQLEXPLAIN for the rst time, the program has access to the rst SQLCode returned. Each time SQLEXPLAIN is subsequently executed, the next SQLCode becomes available to the program, and so on until SQLCode equals zero. If the user needs to have further access to a SQLCode value, the SQLCode value needs to be saved into another data variable.
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 an INSERT, UPDATE, or DELETE command, 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 application.
This warning occurs when a program submits a RELEASE command without rst terminating a transaction with a COMMIT WORK or ROLLBACK WORK command. If the transaction performed no UPDATE, INSERT, or DELETE operations, this situation causes no work to be lost. If the transaction did perform UPDATE, INSERT, or DELETE operations, the database changes are rolled back when the RELEASE command is processed. An error and a warning condition may exist at the same time.
When you use the SQLWARNING option of the WHENEVER command, ALLBASE/SQL checks for a W in SQLWarn(0). You can use the WHENEVER command to do implicit status checking equivalent to that done explicitly above as follows: EXEC SQL WHENEVER SQLWARNING GOTO 3000 EXEC SQL WHENEVER SQLERROR GOTO 2000 When a warning condition that sets SQLWarn(0) occurs, SQLCode does not contain a value that describes the warning.
If any OrderQty values are null: :SQLWarn(2) is set to W. SQLCode is set to 0. SQLEXPLAIN retrieves the message: NULL values eliminated from the argument of an aggregate function. (DBWARN 2041) SQLWARN(6) When an error exists so serious that ALLBASE/SQL has to roll back the current transaction, SQLWarn(6) is set to W .
In this example, the program executes subprogram unit SQLStatusCheck when an error occurs. The program terminates whenever SQLWarn(6) is W , but continues if SQLWarn(6) is not W . If a deadlock or a shared memory problem occurs, the contention that caused it may not exist if the transaction is restarted. In this case, you may want to examine both SQLWarn(6) and SQLCode and terminate the program only when SQLCode is less than -14024: . . . 100 C CONTINUE This is the RESTART POINT . . . IF (SQLCode .GT.
If one or more errors are detected before an automatic rollback occurs, the rst SQLCode available to your program will not be equal to -4008 or greater than or equal to -14024. However, should one of these conditions occur, the corresponding SQLCode is guaranteed to be the last SQLCode available to your program, since ALLBASE/SQL rolls back the current transaction and does not continue to look for additional errors.
At this point, a SELECT command is executed. If an error occurs, and if the rst error detected was a deadlock or a shared memory problem, the SELECT command is automatically re-executed as many as three times before the user is noti ed of the situation. If other errors occurred before the deadlock or shared memory problem, the transaction is not automatically reapplied. If an error with an SQLCode less then -14024 occurred, the program is terminated after the error messages are displayed.
BEGIN DECLARE SECTION DECLARE CURSOR END DECLARE SECTION INCLUDE SQLEXPLAIN WHENEVER 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 also three actions: STOP.
. . CALL SubprogramUnit1 CALL SubprogramUnit2 . . SUBROUTINE SubprogramUnit2 . . EXEC SQL SQLCOMMAND2 . . RETURN END SUBROUTINE SubprogramUnit1 . . EXEC SQL WHENEVER SQLERROR GOTO 2000 EXEC SQL WHENEVER SQLWARNING GOTO 3000 EXEC SQL WHENEVER NOT FOUND GOTO 4000 . . EXEC SQL SQLCOMMAND1 . . 2000 CALL ErrorHandler . 3000 CALL WarningHandler . 4000 CALL NotFoundHandler . .
SUBROUTINE SubprogramUnit1 . . C**** Start SQL Preprocessor **** C EXEC SQL WHENEVER SQLERROR GOTO 2000 C EXEC SQL WHENEVER SQLWARNING GOTO 3000 C EXEC SQL WHENEVER NOT FOUND GOTO 4000 C**** Start Inserted Statements **** C**** End SQL Preprocessor **** . . C **** Start SQL Preprocessor *** C EXEC SQL SQLCOMMAND1 C **** Start Inserted Statements **** IF (SQLCODE .EQ. 0) THEN CALL SQLXCO(SQLCAID,Statements for executing 1 SQLCOMMAND1 appear here) IF (SQLWARN(0) .EQ.
RETURN END Runtime Status Checking and the SQLCA 5-19
As this example illustrates, you can pass control with a WHENEVER command to an exception-handling code routine within the same program unit where the error condition occurred. Because you use a GOTO statement rather than a CALL statement, after the exception-handling subprogram unit is executed, control cannot automatically return to the statement which caused the error to occur.
Implicitly Invoking Status-Checking Subprogram Units The program illustrated in Figure 5-1 contains ve WHENEVER commands: The WHENEVER command numbered 1 handles errors associated with the following commands: CONNECT BEGIN WORK COMMIT WORK The WHENEVER command numbered 2 turns o the previous WHENEVER command. The WHENEVER commands numbered 3 through 5 handle warnings and errors associated with the SELECT command. The WHENEVER commands numbered 6 turns o the previous WHENEVER commands.
If SQLCode contains a value less than -14024, the program terminates after all available warnings and error messages from the ALLBASE/SQL message catalog have been displayed. In the case of any other errors, the program displays all available messages, then returns to subprogram unit SelectQuery and prompts the user for another PartNumber. The code routine located at Label 3000 is executed when only a warning condition results during execution of the SELECT command.
C C C C C C C C C C C C C C C C C C C C C C C C C C C C C C C C C PROGRAM forex5 ****************************************************** * This program illustrates the use of SQL's SELECT * * command to retrieve one row or tuple of data at * * a time. BEGIN WORK is executed before the SELECT * * and COMMIT WORK is executed after the SELECT. An * * indicator variable is used for SalesPrice. * * This program is like forex2 except this program * * handles deadlocks and error handling differently.
C C C C C C C C C C C C C C CALL ConnectDBE CALL SelectQuery CALL TerminateProgram STOP END (* Beginning of the Sub-Routines *) SUBROUTINE ConnectDBE (* Subroutine to Connect to PartsDBE *) EXEC SQL INCLUDE SQLCA (* Begin SQL Communication Area *) (* Begin Host Variable Declarations *) EXEC SQL BEGIN DECLARE SECTION EXEC SQL END DECLARE SECTION EXEC SQL WHENEVER SQLERROR GOTO 1000 WRITE (*,*) ' ' WRITE (*,*) 'CONNECT TO PartsDBE' EXEC SQL CONNECT TO 'PartsDBE' GOTO 1100 C 1000 CALL SQLStatusCheck CALL Te
C C C C (* Begin Host Variable Declarations *) EXEC SQL BEGIN DECLARE SECTION EXEC SQL END DECLARE SECTION EXEC SQL WHENEVER SQLERROR GOTO 1000 WRITE (*,*) 'BEGIN WORK' EXEC SQL BEGIN WORK GOTO 1100 C 1000 CALL SQLStatusCheck CALL TerminateProgram C 1100 RETURN EXEC SQL WHENEVER SQLERROR CONTINUE END C (* End BeginTransaction Subroutine *) C C SUBROUTINE EndTransaction C (* Subroutine to Commit Work *) C EXEC SQL INCLUDE SQLCA C C (* Begin SQL Communication Area *) C C (* Begin Host Variable Declarations
C C C C C C C C C C C C C C C C C C C C C SUBROUTINE TerminateProgram (* Subroutine to Release PartsDBE *) EXEC SQL INCLUDE SQLCA (* Begin SQL Communication Area *) (* Begin Host Variable Declarations *) EXEC SQL BEGIN DECLARE SECTION EXEC SQL END DECLARE SECTION WRITE (*,*) 'RELEASE PartsDBE' EXEC SQL RELEASE WRITE (*,*) 'Terminating Program' RETURN END (* End ReleaseDBE Subroutine *) SUBROUTINE SelectQuery (* Subroutine to prompt user for Query Input *) EXEC SQL INCLUDE SQLCA (* Begin SQL Communication
C C EXEC SQL WHENEVER SQLERROR GOTO 2000 EXEC SQL WHENEVER SQLWARNING GOTO 3000 EXEC SQL WHENEVER NOT FOUND GOTO 4000 trycounter = 0 multiplerows = -10002 1000 CONTINUE DO WHILE (PartNumber .NE. '/') SQLCommandDone = .TRUE. WRITE (*,100) 100 FORMAT(/$,' Enter PartNumber from Parts table or / to STOP > ') READ (5,110) PartNumber 110 FORMAT(A16) IF (PartNumber .NE.
2000 IF (SQLCode .EQ. multiplerows) THEN WRITE (*,*) 'WARNING: More than one row qualifies!' ENDIF CALL SQLStatusCheck (trycounter) CALL DisplayRow (PartNumber,PartName,SalesPrice,SalesPriceInd) CALL EndTransaction GOTO 1000 C 3000 WRITE (*,*) 'An SQL WARNING has occurred.
C C C C C C C C C C C C C C SUBROUTINE SQLStatusCheck (trycounter) (* Subroutine to Check for DeadLocks *) EXEC SQL INCLUDE SQLCA (* Begin SQL Communication Area *) LOGICAL LOGICAL INTEGER INTEGER INTEGER SQLCommandDone Abort deadlock trycounter trycounterlimit (* Begin Host Variable Declarations *) EXEC SQL BEGIN DECLARE SECTION CHARACTER*80 SQLMessage EXEC SQL END DECLARE SECTION deadlock = -14024 trycounterlimit = 3 SQLCommandDone = .FALSE. IF (SQLCode .EQ. deadlock) THEN IF (trycounter .EQ.
C C SUBROUTINE DisplayRow (PartNumber,PartName,SalesPrice, 1SalesPriceInd) (* Subroutine to Display a Selected Row *) C C EXEC SQL INCLUDE SQLCA C C C C C (* Begin SQL Communication Area *) (* Begin Host Variable Declarations *) EXEC SQL BEGIN DECLARE SECTION CHARACTER*16 PartNumber CHARACTER*30 PartName DOUBLE PRECISION SalesPrice SQLIND SalesPriceInd CHARACTER*80 SQLMessage EXEC SQL END DECLARE SECTION C WRITE(*,100) PartNumber WRITE(*,110) PartName C C 100 110 120 C C C IF (SalesPriceInd .LT.
Explicit Status Checking The example examined under \Implicit Status Checking" has already illustrated several uses for explicit status checking: C 600 602 C 102 PROGRAM SQLError . . . (* Restart/Reentry point *) CONTINUE . . SQL SELECT Command . IF (SQLCode .EQ. MultipleRows) THEN WRITE(6,602) 'WARNING: More than one row qualifies.' FORMAT(A80) ELSE CALL SQLStatusCheck (trycounter) ENDIF CALL DisplayRow (PartNumber,PartName,SalesPrice,SalesPriceInd) CALL EndTransaction GOTO 600 . . .
SQLCA values are explicitly examined in this example in order to: Isolate errors so critical that they caused ALLBASE/SQL to rollback the current transaction. Control the number of times SQLEXPLAIN is executed. Detect when more than one row quali es for the SELECT operation. Detect when a deadlock condition exists and control program execution. This section examines when you may want to invoke such status-checking code routines explicitly rather than implicitly .
PROGRAM Main . . . CALL SelectActivity . . . STOP END SUBROUTINE SelectActivity This subprogram unit prompts for a number that indicates whether the user wants to SELECT, UPDATE, DELETE, or INSERT rows, then invokes the subprogram unit that accomplishes the selected activity. The DONE ag is set when the user enters a slash. . . . RETURN END SUBROUTINE InsertData . . . Statements that accept data from the user appear here. 1 2 3 4 5 6 EXEC SQL INSERT INTO PurchDB.
. . . 1 2 3 4 5 102 103 . This subprogram unit veri es that the row(s) to be changed exist, then invokes subprogram unit DisplayUpdate to accept new data from the user. EXEC SQL SELECT PartNumber, PartName, SalesPrice INTO :PartNumber, :PartName, :SalesPrice FROM PurchDB.Parts WHERE PartNumber = :PartNumber IF (SQLCode .EQ. OK) THEN CALL DisplayUpdate ELSE IF (SQLCode .EQ. MultipleRows) THEN WRITE(6,102) 'Warning; more than one row qualifies!' FORMAT (A80) CALL DisplayUpdate ELSE IF (SQLCode .EQ.
. . RETURN END SUBROUTINE DeleteData . . . This subprogram unit veri es that the row(s) to be deleted exist, then invokes subprogram unit DisplayDelete to delete the row(s). 1 2 3 4 5 102 103 EXEC SQL SELECT PartNumber, PartName, SalesPrice INTO :PartNumber, :PartName, :SalesPrice FROM PurchDB.Parts WHERE PartNumber = :PartNumber IF (SQLCode .EQ. OK) THEN CALL DisplayDelete ELSE IF (SQLCode .EQ.
Statements that verify that the deletion should actually occur appear here. 1 EXEC SQL DELETE FROM PurchDB.Parts WHERE PartNumber = :PartNumber IF (SQLCode .NE. OK) THEN CALL SQLStatusCheck ENDIF . . . RETURN END 435 SUBROUTINE SelectData . . . Statements that prompt for a partnumber appear here. 1 2 3 4 5 102 103 EXEC SQL SELECT PartNumber, PartName, SalesPrice INTO :PartNumber, :PartName, :SalesPrice FROM PurchDB.Parts WHERE PartNumber = :PartNumber IF (SQLCode .EQ.
SUBROUTINE SQLStatusCheck . . . IF (SQLCode .EQ. DeadLock) THEN IF (trycounter .EQ. trycounterlimit) THEN WRITE(6,102) 'Deadlock occurred; you may want to try again.' 102 FORMAT(A80) CALL EndTransaction ELSE trycounter = trycounter + 1 ENDIF ENDIF Abort = .FALSE. IF (SQLCode .LT. DeadLock) THEN Abort = .TRUE. ENDIF DO WHILE (SQLCode .NE. 0) EXEC SQL SQLEXPLAIN :SQLMessage CALL WriteOut (SQLMessage) END DO IF (Abort) THEN CALL TerminateProgram ENDIF . . . RETURN END Figure 5-2.
Explicitly Checking for Number of Rows SQLErrd(3) is useful in determining how many rows were processed in one of the following operations when the operation could be executed without error: SELECT INSERT UPDATE DELETE FETCH UPDATE WHERE CURRENT DELETE WHERE CURRENT The SQLErrd(3) value can be used in these cases only when SQLCode does not contain a negative number. When SQLCode is 0, SQLErrd(3) is always equal to 1 for SELECT, FETCH, UPDATE WHERE CURRENT, and DELETE WHERE CURRENT operations.
SUBROUTINE DisplayUpdate . . EXEC SQL INCLUDE SQLCA C INTEGER INTEGER C OK NumberOfRows EXEC SQL BEGIN DECLARE SECTION CHARACTER*16 PartNumber CHARACTER*30 PartName DOUBLE PRECISION SalesPrice EXEC SQL END DECLARE SECTION . . Statements that prompt user for new data appear here. 1 2 3 102 103 EXEC SQL UPDATE PurchDB.Parts SET PartName = :PartName, SalesPrice = :SalesPrice, WHERE PartNumber = :PartNumber IF (SQLCode .EQ.
SUBROUTINE DisplayDelete . . EXEC SQL INCLUDE SQLCA C INTEGER INTEGER CHARACTER C OK NumberOfRows response EXEC SQL BEGIN DECLARE SECTION CHARACTER*16 PartNumber CHARACTER*30 PartName DOUBLE PRECISION SalesPrice EXEC SQL END DECLARE SECTION . . Statements that verify that the deletion should actually occur appear here. 1 102 103 104 EXEC SQL DELETE FROM PurchDB.Parts WHERE PartNumber = :PartNumber IF (SQLCode .EQ.
If the DELETE command is successfully executed, SQLCode equals 0 and SQLErrd(3) contains the number of rows deleted. If the DELETE command cannot be successfully executed, SQLCode contains a negative number and SQLErrd(3) contains a 0. The programs already examined 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.
102 WRITE(6,102) 'Row not found or no more rows!' FORMAT(A80) ELSE CALL DisplayError ENDIF ENDIF . . RETURN END In this example, the active set is de ned when the OPEN command is executed. The cursor is then positioned before the rst row of the active set. When the FETCH command is executed, the rst row in the active set is placed into the program's host variables, then displayed.
INTEGER INTEGER INTEGER LOGICAL C OK NotFound MultipleRows donefetch EXEC SQL BEGIN DECLARE SECTION CHARACTER*16 PartNumber CHARACTER*30 PartName DOUBLE PRECISION SalesPrice EXEC SQL END DECLARE SECTION C OK = 0 NotFound = 100 MultipleRows = -10002 . . This subprogram unit veri es that the row(s) to be changed exists, then invokes subprogram unit DisplayUpdate to accept new data from the user. . . 1 2 3 4 5 102 103 .
. RETURN END 5-44 Runtime Status Checking and the SQLCA
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.
The remainder of this chapter brie y examines each of the three data manipulation techniques (each technique is discussed in detail in Chapters 7 through 9) and introduces the use of a cursor for data manipulation. First, however, this chapter addresses the query, or the description of data you want to retrieve. Queries are fundamental to ALLBASE/SQL data manipulation because some of the elements of a query are also used to describe and limit data when you update or delete it.
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. Then, if the tables or views referenced are altered, your program will still retrieve only the data its host variables are designed to accommodate: 1 2 3 4 5 6 EXEC SQL SELECT PartNumber, PartName, SalesPrice INTO :PartNumber, :PartName, :SalesPrice FROM PurchDB.
In the UPDATE and DELETE commands, you may need a WHERE clause to limit the rows ALLBASE/SQL changes or deletes. In the following case, the sales price of parts priced lower than $1000 is increased 10 percent; the WHERE clause in this case illustrates the comparison predicate: EXEC SQL UPDATE PurchDB.Parts 1 SET SalesPrice = SalesPrice * 1.1 2 WHERE SalesPrice < 1000.00 The ALLBASE/SQL Reference Manual details the syntax and semantics for these and other predicates.
The examples shown so far have all included queries where results would most likely contain more than one row. The sequential table processing technique using cursors could also be used to handle multiple-row query results. Later in this chapter you'll nd examples of this technique, as well as examples illustrating simple data manipulation, in which only one-row query results are expected.
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 pairs of 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: The query: 1 2 3 4 5 6 7 8 9 1 2 3 4 EXEC SQL SELECT X.PartNumber, X.VendorNumber, X.UnitPrice, Y.
Join variables can be used in any query as a shorthand way of referring to a table, but they must be used in queries that join a table to itself so that ALLBASE/SQL can distinguish between the two copies of the table. 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.
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: 1 2 3 4 5 EXEC SQL SELECT VendorNumber, SUM(TotalPrice) INTO :VendorNumber, :Sum FROM PurchDB.
Query Efficiency Three clauses in the SELECT command have an e ect on the execution speed of queries: WHERE GROUP BY ORDER BY As discussed earlier, the WHERE clause consists of one or more predicates. Predicates can be evaluated more quickly when they can be optimized by ALLBASE/SQL. The following predicates are optimizable when all the data types within them are the same (in the case of DOUBLE PRECISION data, the precisions and scales of the di erent values must be the same).
The following predicates are not optimizable, and an index is never used: Predicates containing arithmetic expressions: WHERE Column1 > Column2 * :HostVariable LIKE predicates: WHERE Column1 LIKE :HostVariable Predicates joined by the logical operator OR: WHERE Column1 = Column2 OR Column1 > Constant When a query does not contain a WHERE clause, an index is never used, because all rows from tables in the FROM clause containing columns in the select list qualify: 1 2 3 4 5 EXEC SQL SELECT * INTO :HostVar
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. The duration of locks can be minimized by making each data manipulation operation a separate transaction.
EXEC SQL BEGIN DECLARE SECTION CHARACTER*16 PartNumber CHARACTER*30 PartName SQLIND PartNameInd DOUBLE PRECISION SalesPrice SQLIND SalesPriceInd EXEC SQL END DECLARE SECTION . . . SUBROUTINE DoQuery . This procedure accepts a part number from the user, . then executes a query to determine whether one or . more rows containing that value actually exist. 1 2 3 4 5 EXEC SQL SELECT PartNumber, PartName, SalesPrice INTO :PartNumber, :PartName :PartNameInd, :SalesPrice :SalesPriceInd FROM PurchDB.
SUBROUTINE DisplayDelete . The qualifying row is displayed for the user to . verify that it should be deleted before the following . command is executed: 1 EXEC SQL DELETE FROM PurchDB.Parts WHERE PartNumber = :PartNumber . . . RETURN END 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 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.
If a serial scan will be used to retrieve the active set, ALLBASE/SQL locks the table(s) when the OPEN command is executed. If an index scan will be used, locks are placed when rows are fetched. Any locks obtained are held until the transaction terminates or the CLOSE command is executed. Both the OPEN and the FETCH commands position the cursor: The OPEN command positions the cursor before the rst row of the active set.
EXEC SQL DECLARE Cursor2 CURSOR FOR SELECT PartName, SalesPrice FROM PurchDB.Parts WHERE PartNumber BETWEEN :LowValue AND :HighValue FOR UPDATE OF SalesPrice . . Because the DECLARE CURSOR command is not . executed at runtime, no status checking code . needs to appear here. . EXEC SQL OPEN Cursor2 . . The OPEN command examines any input host . variables and determines the active set. . Then the program fetches one row at a time. .
The restrictions that govern deletions and updates using a view also govern deletions and updates using a cursor. You cannot delete or update a row using a cursor if the cursor declaration contains any of the following: Join operation Aggregate function DISTINCT GROUP BY ORDER BY UNION 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.
Figure 6-2.
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.
CALL SQLStatusCheck CALL ReleaseDBE ELSE CALL GetRow ENDIF . . . RETURN END SUBROUTINE GetRow . . . DO WHILE (SQLCode .EQ. OK) EXEC SQL FETCH PriceCursor 1 INTO :PartNumber, 2 :PartName :PartNameInd, 3 :SalesPrice :SalesPriceInd IF (SQLCode .EQ. OK) THEN CALL DisplayRow ELSEIF (SQLCode .EQ. NotFound) THEN CALL WriteOut ('No More Rows!') ELSE CALL SQLStatusCheck ENDIF END DO . . . RETURN END SUBROUTINE DisplayRow . Each row fetched is displayed. Depending on the user's response .
ELSEIF (response .EQ. 'U') THEN . . A new SalesPrice is accepted. . EXEC SQL UPDATE PurchDB.
. . Status checking code appears here. . ENDIF . . . RETURN END More on sequential table processing can be found in Chapter 8. Dynamic Operations Dynamic operations o er a way to execute SQL commands that cannot be completely de ned until runtime.
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: Are not executed in conjunction with a cursor. Therefore additional SQL commands such as FETCH and OPEN are not required. Are not dynamically preprocessed.
You may omit the WHERE clause from certain queries when the select list contains only aggregate functions: 1 2 EXEC SQL SELECT AVG(SalesPrice) INTO :AvgSalesPrice FROM PurchDB.Parts A WHERE clause may be used, however, to qualify the rows over which the aggregate function is applied: EXEC SQL SELECT AVG(SalesPrice) 1 INTO :AvgSalesPrice 2 FROM 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.
character following an escape character must be either a wild card character or the escape character itself. Complete syntax is presented in the ALLBASE/SQL Reference Manual . 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.
1 2 3 EXEC SQL INSERT INTO PurchDB.Parts VALUES (:PartNumber, :PartName :PartNameInd, :SalesPrice :SalesPriceInd) 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.
If the WHERE clause is omitted, all rows in the table are deleted.
The UPDATE Command In simple data manipulation, you use the UPDATE command to change data in one or more columns: UPDATE TableName SET Columname = :ColumnValue :ColumnValueInd [,...] WHERE SearchCondition As in the case of the DELETE command, if you omit the WHERE clause, the value of any column speci ed is changed in all rows of the table. If the WHERE clause is speci ed, all rows satisfying the search condition are changed: EXEC SQL UPDATE 1 SET 2 3 4 5 WHERE PurchDB.
First retrieve all columns from the row to be updated. 1 2 3 4 5 6 7 EXEC SQL SELECT PartNumber, PartName, SalesPrice INTO :PartNumber, :PartName :PartNameInd, :SalesPrice :SalesPriceInd FROM :PurchDB.Parts WHERE PartNumber = :PartNumber Prompt the user for new values. If the user wants to set a column to null, set the indicator variable for that column to -1. WRITE (*,*) 'Enter new PartName (or 0 for NULL)> ' READ(6,100) PartName 100 FORMAT (A30) IF (PartName .EQ.
EXEC SQL DELETE FROM PurchDB.Parts WHERE PartNumber = :PartNumber 1 2 3 4 5 6 EXEC SQL INSERT INTO PurchDB.Parts (PartNumber, PartName, SalesPrice) VALUES (:PartNumber, :PartName :PartNameInd, :SalesPrice :SalesPriceInd) Transaction Management for Simple Operations The major objectives of transaction management are to minimize the contention for locks and to ensure logical data consistency. Minimizing lock contention implies short transactions and/or locking small, unique parts of a database.
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: If you combine SELECT and DELETE operations within one transaction, when the DELETE command is executed, the row deleted is guaranteed to be the same row retrieved and displayed for the user.
In the case of some multi-command transactions, you must execute multiple data manipulation commands within a single transaction for the sake of logical data consistency: 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; The DELETE command is executed.
Program Using SELECT, UPDATE, DELETE, and INSERT The ow chart shown in Figure 7-1 summarizes the functionality of program forex7, which uses the four simple data manipulation commands to operate on the PurchDB.Vendors table. Forex7 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.
Update Function Function Update 4125 lets the user UPDATE the value of a column only if it contains a null value. The function prompts for a vendor number or a zero. If a zero is entered, the function menu is re-displayed. If a vendor number is entered, subroutine BeginTransaction 455 is executed. Then a SELECT command is executed to retrieve data from PurchDB.Vendors for the vendor speci ed.
If more than one row quali es for the SELECT operation, a di erent message is displayed and subroutine CommitWork 465 terminates the transaction by executing the COMMIT WORK command. The user is then re-prompted for a vendor number or a zero. If the SELECT command execution results in an error condition, subroutine SQLStatusCheck 425 is executed. Then subroutine CommitWork 465 terminates the transaction by executing the COMMIT WORK command. The user is then re-prompted for a vendor number or a zero.
Figure 7-1.
Figure 7-1.
: run forex7 Program for Simple Data Manipulation of the Vendors Table -- forex7 Event List: CONNECT TO PartsDBE Prompt for type of transaction Prompt for VendorNumber BEGIN WORK Display row Perform specified function COMMIT WORK or ROLLBACK WORK Repeat the above five steps until user enters 0 Repeat the above seven steps until user enters 0 RELEASE PartsDBE CONNECT TO PartsDBE 1....SELECT 2....UPDATE 3....DELETE 4....INSERT rows rows rows rows from with from into PurchDB.Vendors null values in PurchDB.
Enter new Vendor City > San Jose Enter new Vendor State > CA Enter new Vendor Zip Code > 90016 Enter new VendorRemarks (0 for NULL) > 0 BEGIN WORK INSERT new row into PurchDB.Vendors COMMIT WORK Enter Vendor Number to INSERT or a 0 to STOP > 0 1....SELECT 2....UPDATE 3....DELETE 4....INSERT rows rows rows rows from with from into PurchDB.Vendors table null values in PurchDB.Vendors table PurchDB.Vendors table PurchDB.
Enter Vendor Number to SELECT or a 0 to STOP > 0 1....SELECT 2....UPDATE 3....DELETE 4....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 your choice or a 0 to STOP > 2 Enter Vendor Number to UPDATE or a 0 to STOP > 9016 BEGIN WORK SELECT * from PurchDB.
Enter Vendor Number to DELETE or a 0 to STOP > 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:can expedite shipments Is it OK to DELETE this row (N/Y)? > Y DELETE row from PurchDB.Vendors! COMMIT WORK Enter Vendor Number to DELETE or a 0 to STOP > 0 1....SELECT 2....UPDATE 3....DELETE 4....
C C C C C C C C PROGRAM forex7 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 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.
IF (ConnectDBE()) THEN Done = .FALSE. DO WHILE (.NOT.Done) WRITE (*,*) ' ' WRITE (*,*) '1....SELECT rows from PurchDB.Vendors table' WRITE (*,*) '2....UPDATE rows with null values in PurchDB.Vend 1ors table' WRITE (*,*) '3....DELETE rows from PurchDB.Vendors table' WRITE (*,*) '4....INSERT rows into PurchDB.Vendors table' WRITE (*,*) ' ' WRITE (*,100) 100 FORMAT($, ' Enter your choice or a 0 to STOP > ') READ (*,110) Response 110 FORMAT(A1) IF (Response .EQ. '0') THEN Done = .TRUE. ELSEIF (Response .EQ.
SUBROUTINE SQLStatusCheck C**** SUBROUTINE SQLStatusCheck checks status of SQL commands C**** and print HPSQL error messages. C 425 (* Begin SQL Communication Area *) EXEC SQL INCLUDE SQLCA LOGICAL*2 Abort, Check INTEGER DeadLock PARAMETER (DeadLock = C -14024) (* Begin Host Variable Declarations *) EXEC SQL BEGIN DECLARE SECTION CHARACTER*120 SQLMessage EXEC SQL END DECLARE SECTION C (* End Host Variable Declarations *) Abort = .FALSE. IF (SQLCode .LT. DeadLock) THEN Abort = .TRUE.
LOGICAL*2 FUNCTION ConnectDBE() C**** FUNCTION to connect to PartsDBE 435 INTEGER*2 OK PARAMETER (OK = 0) C (* Begin SQL Communication Area *) EXEC SQL INCLUDE SQLCA EXEC SQL BEGIN DECLARE SECTION EXEC SQL END DECLARE SECTION C WRITE (*,*) 'CONNECT TO PartsDBE' EXEC SQL CONNECT TO 'PartsDBE' ConnectDBE = .TRUE. IF (SQLCode .NE. OK) THEN ConnectDBE = .FALSE.
SUBROUTINE BeginTransaction C**** SUBROUTINE to begin work INTEGER*2 OK PARAMETER (OK = 0) C 455 (* Begin SQL Communication Area *) EXEC SQL INCLUDE SQLCA EXEC SQL BEGIN DECLARE SECTION EXEC SQL END DECLARE SECTION C WRITE (*,*) ' ' WRITE (*,*) 'BEGIN WORK' EXEC SQL BEGIN WORK IF (SQLCode .NE.
SUBROUTINE RollBackWork C**** SUBROUTINE to RollBack Work INTEGER*2 OK PARAMETER (OK = 0) C 475 (* Begin SQL Communication Area *) EXEC SQL INCLUDE SQLCA EXEC SQL BEGIN DECLARE SECTION EXEC SQL END DECLARE SECTION C WRITE(*,*) 'ROLLBACK WORK' EXEC SQL ROLLBACK WORK IF (SQLCode .NE.
C ELSE AnyNulls = .TRUE.
C IF (PhoneNumberInd .LT. 0) THEN WRITE(*,*) ' PhoneNumber is NULL' ELSE WRITE(*, '('' PhoneNumber: '',A16)') PhoneNumber ENDIF WRITE(*, '('' VendorStreet: '',A30)') VendorStreet WRITE(*, '('' VendorCity: '',A20)') VendorCity WRITE(*, '('' VendorState: '',A2)') VendorState WRITE(*, '('' VendorZipCode:'',A10)') VendorZipCode IF (VendorRemarksInd .LT.
CHARACTER*2 VendorState CHARACTER*10 VendorZipCode CHARACTER*50 VendorRemarks SQLIND VendorRemarksInd CHARACTER*120 SQLMessage EXEC SQL END DECLARE SECTION C (* End Host Variable Declarations *) Select = .FALSE. VendorNumber = 1 DO WHILE (VendorNumber .NE. 0) WRITE (*,*) ' ' WRITE (*,100) 100 FORMAT($, ' Enter Vendor Number to SELECT or a 0 to STOP > ') READ (*,110) VendorNumber 110 FORMAT(I4) IF (VendorNumber .NE. 0) THEN CALL BeginTransaction WRITE (*,*) ' ' WRITE (*,*) 'SELECT * from PurchDB.
IF (SQLCode .EQ. OK) THEN CALL DisplayRow(VendorNumber,VendorName,ContactName, 1 PhoneNumber,VendorStreet,VendorCity, 2 VendorState,VendorZipCode,VendorRemarks, 3 ContactNameInd,PhoneNumberInd,VendorRemarksInd) ELSEIF (SQLCode .EQ. NotFound) THEN WRITE (*,*) ' ' WRITE (*,*) 'Row not found!' ELSEIF (SQLCode .EQ.
C (* Begin Host Variable Declarations *) EXEC SQL BEGIN DECLARE SECTION INTEGER*4 VendorNumber CHARACTER*30 VendorName CHARACTER*30 ContactName SQLIND ContactNameInd CHARACTER*16 PhoneNumber SQLIND PhoneNumberInd CHARACTER*30 VendorStreet CHARACTER*20 VendorCity CHARACTER*2 VendorState CHARACTER*10 VendorZipCode CHARACTER*50 VendorRemarks SQLIND VendorRemarksInd CHARACTER*120 SQLMessage EXEC SQL END DECLARE SECTION CALL DisplayRow(VendorNumber, VendorName, ContactName, PhoneNumber, VendorStreet, VendorCit
IF (ContactName .EQ. '0') THEN ContactNameInd = -1 ELSE ContactNameInd = 0 ENDIF IF (PhoneNumber .EQ. '0') THEN PhoneNumberInd = -1 ELSE PhoneNumberInd = 0 ENDIF IF (VendorRemarks .EQ. '0') THEN VendorRemarksInd = -1 ELSE VendorRemarksInd = 0 ENDIF 1 2 3 4 WRITE (*,*) 'UPDATE the PurchDB.Vendors table' EXEC SQL UPDATE PurchDB.
C (* Begin SQL Communication Area *) EXEC SQL INCLUDE SQLCA C (* Begin Host Variable Declarations *) EXEC SQL BEGIN DECLARE SECTION INTEGER*4 VendorNumber CHARACTER*30 VendorName CHARACTER*30 ContactName SQLIND ContactNameInd CHARACTER*16 PhoneNumber SQLIND PhoneNumberInd CHARACTER*30 VendorStreet CHARACTER*20 VendorCity CHARACTER*2 VendorState CHARACTER*10 VendorZipCode CHARACTER*50 VendorRemarks SQLIND VendorRemarksInd CHARACTER*120 SQLMessage EXEC SQL END DECLARE SECTION C 100 110 (* End Host Varia
IF (VendorNumber .NE. 0) THEN CALL BeginTransaction WRITE (*,*) ' ' WRITE (*,*) 'SELECT * from PurchDB.Vendors' EXEC SQL SELECT VendorNumber, 1 VendorName, 2 ContactName, 3 PhoneNumber, 4 VendorStreet, 5 VendorCity, 6 VendorState, 7 VendorZipCode, 8 VendorRemarks 9 INTO :VendorNumber, 1 :VendorName, 2 :ContactName :ContactNameInd, 3 :PhoneNumber :PhoneNumberInd, 4 :VendorStreet, 5 :VendorCity, 6 :VendorState, 7 :VendorZipCode, 8 :VendorRemarks :VendorRemarksInd 9 FROM PurchDB.
C**** SUBROUTINE to Display and Delete a row C**** from the PurchDB.
WRITE (*,100) 100 FORMAT($, ' Is it OK to DELETE this row (N/Y)? >') READ (*, 110) Response 110 FORMAT(A1) IF ((Response .EQ. 'Y') .OR. (Response .EQ. 'y')) THEN WRITE (*,*) ' ' WRITE (*,*) 'DELETE row from PurchDB.Vendors!' EXEC SQL DELETE FROM PurchDB.Vendors 1 WHERE VendorNumber = :VendorNumber IF (SQLCode .NE. OK) THEN CALL SQLStatusCheck ENDIF ELSE WRITE (*,*) ' ' WRITE (*,*) 'Row not deleted from PurchDB.
CHARACTER*20 VendorCity CHARACTER*2 VendorState CHARACTER*10 VendorZipCode CHARACTER*50 VendorRemarks SQLIND VendorRemarksInd CHARACTER*120 SQLMessage EXEC SQL END DECLARE SECTION C (* End Host Variable Declarations *) Delete = .FALSE. VendorNumber = 1 DO WHILE (VendorNumber .NE. 0) WRITE (*,*) ' ' WRITE (*,100) 100 FORMAT($, ' Enter Vendor Number to DELETE or a 0 to STOP > ') READ (*,110) VendorNumber 110 FORMAT(I4) IF (VendorNumber .NE.
CALL DisplayDelete (VendorNumber, VendorName, ContactName, 1 PhoneNumber, VendorStreet, VendorCity, 2 VendorState, VendorZipCode, VendorRemarks, 3 ContactNameInd, PhoneNumberInd, VendorRemarksInd) ELSEIF (SQLCode .EQ. NotFound) THEN WRITE (*,*) ' ' WRITE (*,*) 'Row not found!' ELSEIF (SQLCode .EQ.
EXEC SQL BEGIN DECLARE SECTION INTEGER*4 VendorNumber CHARACTER*30 VendorName CHARACTER*30 ContactName SQLIND ContactNameInd CHARACTER*16 PhoneNumber SQLIND PhoneNumberInd CHARACTER*30 VendorStreet CHARACTER*20 VendorCity CHARACTER*2 VendorState CHARACTER*10 VendorZipCode CHARACTER*50 VendorRemarks SQLIND VendorRemarksInd CHARACTER*120 SQLMessage EXEC SQL END DECLARE SECTION C 100 110 120 130 140 150 (* End Host Variable Declarations *) Insert = .FALSE. VendorNumber = 1 DO WHILE (VendorNumber .NE.
160 170 WRITE (*,160) FORMAT($,' Enter new PhoneNumber (0 for NULL) > ') READ(*,170) PhoneNumber FORMAT(A16) IF (PhoneNumber .EQ.
C CALL BeginTransaction WRITE (*,*)'INSERT new row into PurchDB.Vendors' EXEC SQL INSERT 1 INTO PurchDB.Vendors 2 (VendorNumber, VendorName, ContactName, 3 PhoneNumber, VendorStreet, VendorCity, 4 VendorState, VendorZipCode, VendorRemarks) 5 VALUES (:VendorNumber, 6 :VendorName, 6 :ContactName :ContactNameInd, 7 :PhoneNumber :PhoneNumberInd, 8 :VendorStreet, 9 :VendorCity, 1 :VendorState, 2 :VendorZipCode, 3 :VendorRemarks :VendorRemarksInd) IF (SQLCode .NE.
8 Processing with Cursors Sequential table processing is the programming technique you use to operate 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.
The DECLARE CURSOR Command 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...]] Note that the DECLARE CURSOR command has two optional clauses: The IN clause de nes the DBEFileSet in which the section generated by the preprocessor for this command is stored. If no IN clause is speci ed, le space in the SYSTEM DBEFileSet is used.
The DECLARE CURSOR command is actually a preprocessor directive. When the FORTRAN preprocessor parses this command, it stores a section in the target DBEnvironment. At runtime, the section is not executed when the DECLARE CURSOR command is encountered, but when the OPEN command is executed. Because the DECLARE CURSOR command is not executed at runtime, you do not need to perform status checking in your program following this command.
SUBROUTINE GetARow . . OK = 0 NotFound = 100 DoFetch = .TRUE. DO WHILE (DoFetch) . . The FETCH command appears here. . IF (SQLCode .EQ. OK) THEN CALL DisplayRow ELSEIF (SQLCode .EQ. NotFound) THEN DoFetch = .FALSE. CALL CloseCursor CALL CommitWork ELSE DoFetch = .FALSE. CALL SQLStatusCheck CALL CloseCursor CALL RollBackWork ENDIF END DO RETURN END When a row is current, you can update it by using the UPDATE WHERE CURRENT command or delete it by using the DELETE WHERE CURRENT command.
The UPDATE WHERE CURRENT Command This command can be used to update 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. 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 [,...
EXEC SQL FETCH AdjustQtyOnHand INTO :PartNumber, :BinNumber, :QtyOnHand, :AdjustmentQty . . . EXEC SQL UPDATE PurchDB.Inventory 1 SET QtyOnHand = :QtyOnHand + :AdjustmentQty, 2 AdjustmentQty = 0 3 WHERE CURRENT OF AdjustQtyOnHand 1 2 3 4 In this example, the order of the rows in the query result is not important. Therefore the SELECT command associated with cursor AdjustQtyOnHand does not need to contain an ORDER BY clause and the UPDATE WHERE CURRENT command can be used.
. . . . . . update AdjustmentQty. The value(s) entered, as well as the current PartNumber and BinNumber, are saved until all rows have been fetched and other values accepted from the user. Then one of the following UPDATE commands is executed for each UPDATE requested by the user: . EXEC SQL UPDATE PurchDB.Inventory 1 SET AdjustmentQty = :AdjustmentQty 2 WHERE PartNumber = :PartNumber 3 AND BinNumber = :BinNumber . . . EXEC SQL UPDATE PurchDB.
After the DELETE WHERE CURRENT command is executed, there is no current row. You must re-execute the FETCH command to obtain another current row. As in the case of the UPDATE WHERE CURRENT command, if the SELECT command associated with the cursor contains an ORDER BY clause or other components listed earlier, you can use the DELETE command to delete a row: EXEC SQL DELETE FROM PurchDB.
Cursor Inventory is used to update information in table PurchDB.Inventory.
Transaction Management for Cursor Operations The time at which ALLBASE/SQL obtains locks during cursor processing depends on whether an index scan or a sequential scan is used to retrieve the query result. When a cursor is based on a SELECT command for which an index scan is used, locks are obtained when the FETCH command is executed.
Using KEEP CURSOR Cursor operations in an application program let you manipulate data in an active set associated with a SELECT command. The cursor is a pointer to a row in the active set. The KEEP CURSOR option of the OPEN command lets you maintain the cursor position in an active set beyond transaction boundaries. This means you can scan and update a large table without holding locks for the duration of the entire scan. You can also design transactions that avoid holding any locks around terminal reads.
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, shared 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 on the page that contains the current cursor position are released.
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. If another transaction deletes the table being accessed, the user will see the message TABLE NOT FOUND (DBERR 137) Figure 8-3 shows the e ect of KEEP CURSOR WITH NOLOCKS. Figure 8-3.
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.
automatic deadlock handling routines. Simply repeat the operation until deadlock does not occur. Writing Keep Cursor Applications Because of the potential for deadlock, you must be careful to test for that condition frequently in applications using KEEP CURSOR. Use the following steps to create your code: 1. Declare all cursors to be used in the application. 2. Use a loop to test for a deadlock condition as you open all cursors that will use the KEEP CURSOR option.
Program Using UPDATE WHERE CURRENT The ow chart in Figure 8-4 summarizes the functionality of program forex8. This program uses a cursor and the UPDATE WHERE CURRENT command to update column ReceivedQty in table PurchDB.OrderItems . The runtime dialog for forex8 appears in Figure 8-5, and the source code in Figure 8-6. The main program 415 rst executes subroutine DeclareCursor 495, which contains the DECLARE CURSOR command. This command is a preprocessor directive and is not executed at runtime.
DisplayUpdate Subroutine Subroutine DisplayUpdate 4125 executes subroutine DisplayRow 485 to display the current row. If column ReceivedQty in the current row contains a null value, the message ReceivedQty is NULL is displayed. The user is then asked whether he wants to update the current ReceivedQty value 412A5. If so, the user is prompted for a new entry.
Figure 8-4.
:run forex8 Program to UPDATE OrderItems table via a CURSOR -- forex8 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 no more rows qualify CLOSE Cursor COMMIT WORK or ROLLBACK WORK Repeat the above eleven steps until user enters 0 RELEASE PartsDBE Declare Cursor OrderReview CONNECT TO
OrderNumber: ItemNumber: VendPartNumber: ReceivedQty: 9135 3 30520 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! Do you want to save your changes (Y/N)? > n CLOSE the Declared Cursor OrderReview ROLLBACK WORK No Rows Changed! Enter Order Number or a 0 to STOP > 30510 BEGIN WORK OPEN the Declared Cursor OrderReview OrderNumber: ItemNumber: VendPartNumber: ReceivedQty: 1001 3 30510 1 Do you want to change ReceivedQty (Y/N)? > n Do
C* C* C* C* PROGRAM forex8 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * This program illustrates the use of UPDATE WHERE CURRENT with a Cursor to update a single row at a time.
C C ELSE WRITE (*,*) 'Cannot Connect to your DBEnvironment!' ENDIF STOP END (* End of Main Program *) (* Beginning of the Sub-Routines *) SUBROUTINE SQLStatusCheck C**** SUBROUTINE SQLStatusCheck checks status of SQL commands C**** and print HPSQL error messages.
C END IF RETURN END (* End of Subroutine SQLStatusCheck *) LOGICAL*2 FUNCTION ConnectDBE() C**** FUNCTION to connect to PartsDBE 435 INTEGER*2 OK PARAMETER (OK = 0) C (* Begin SQL Communication Area *) EXEC SQL INCLUDE SQLCA EXEC SQL BEGIN DECLARE SECTION EXEC SQL END DECLARE SECTION WRITE (*, *) 'CONNECT TO PartsDBE' C EXEC SQL CONNECT TO 'PartsDBE' ConnectDBE = .TRUE. IF (SQLCode .NE. OK) THEN ConnectDBE = .FALSE.
SUBROUTINE BeginTransaction C**** SUBROUTINE to begin work INTEGER*2 OK PARAMETER (OK = 0) C 455 (* Begin SQL Communication Area *) EXEC SQL INCLUDE SQLCA EXEC SQL BEGIN DECLARE SECTION EXEC SQL END DECLARE SECTION C WRITE (*,*) 'BEGIN WORK' EXEC SQL BEGIN WORK IF (SQLCode .NE.
SUBROUTINE RollBackWork C**** SUBROUTINE to RollBack Work INTEGER*2 OK PARAMETER (OK = 0) C 475 (* Begin SQL Communication Area *) EXEC SQL INCLUDE SQLCA EXEC SQL BEGIN DECLARE SECTION EXEC SQL END DECLARE SECTION C WRITE(*,*) 'ROLLBACK WORK' EXEC SQL ROLLBACK WORK IF (SQLCode .NE.
C WRITE(*,*) ' ' WRITE(*, '('' OrderNumber: '',I10)') OrderNumber WRITE(*, '('' ItemNumber: '',I10)') ItemNumber WRITE(*, '('' VendPartNumber: '',A16)') VendPartNumber IF (ReceivedQtyInd .LT.
1 2 3 4 5 6 7 8 9 C CURSOR FOR SELECT OrderNumber, ItemNumber, VendPartNumber, ReceivedQty FROM PurchDB.
SUBROUTINE CloseCursor C**** SUBROUTINE to close the Cursor 4115 INTEGER OK PARAMETER (OK = 0) C (* Begin SQL Communication Area *) EXEC SQL INCLUDE SQLCA C (* Begin Host Variable Declarations *) EXEC SQL BEGIN DECLARE SECTION EXEC SQL END DECLARE SECTION C (* End Host Variable Declarations *) WRITE (*,*) ' ' WRITE (*,*) 'CLOSE the Declared Cursor OrderReview' WRITE (*,*) ' ' EXEC SQL CLOSE OrderReview IF (SQLCode .NE.
EXEC SQL INCLUDE SQLCA C (* Begin Host Variable Declarations *) EXEC SQL BEGIN DECLARE SECTION INTEGER OrderNumber INTEGER ItemNumber CHARACTER*16 VendPartNumber INTEGER ReceivedQty SQLIND ReceivedQtyInd CHARACTER*120 SQLMessage EXEC SQL END DECLARE SECTION 1 100 110 120 130 CALL DisplayRow(OrderNumber, ItemNumber, VendPartNumber, ReceivedQty, ReceivedQtyInd) WRITE (*,100) FORMAT (/$, ' Do you want to change ReceivedQty (Y/N)? > ') READ (*, 110) Response FORMAT(A1) IF ((Response .EQ. 'Y') .OR.
140 150 160 170 WRITE (*,140) FORMAT (/$, 'Do you want to see another row (Y/N)? > ') READ (*, 150) Response FORMAT (A1) IF ((Response .EQ. 'N') .OR. (Response .EQ. 'n')) THEN IF (RowCounter .GT. 0) THEN WRITE (*,160) FORMAT (/$, 'Do you want to save your changes (Y/N)? >') READ (*, 170) Response FORMAT (A1) IF ((Response .EQ. 'N') .OR. (Response .EQ. 'n')) THEN CALL CloseCursor CALL RollBackWork Fetch = .FALSE. WRITE (*,*) 'No Row(s) Changed! ' ELSE CALL CloseCursor CALL CommitWork Fetch = .FALSE.
C (* Begin SQL Communication Area *) EXEC SQL INCLUDE SQLCA C (* Begin Host Variable Declarations *) EXEC SQL BEGIN DECLARE SECTION INTEGER OrderNumber INTEGER ItemNumber CHARACTER*16 VendPartNumber INTEGER ReceivedQty SQLIND ReceivedQtyInd CHARACTER*120 SQLMessage EXEC SQL END DECLARE SECTION C (* End Host Variable Declarations *) RowCounter = 0 FetchUpdate = .TRUE. WRITE (*,100) 100 FORMAT(/$, 'Enter Order Number or a 0 to stop > ') READ (*,110) OrderNumber 110 FORMAT(I10) IF (OrderNumber .NE.
120 130 C IF (RowCounter .GT. 0) THEN WRITE (*,120) FORMAT (/$, 'Do you want to save your changes (Y/N)? > ' ) READ (*,130) Response FORMAT(A1) IF ((Response .EQ. 'N') .OR. (Response .EQ. 'n')) THEN CALL CloseCursor CALL RollBackWork WRITE (*,*) 'No Row(s) Changed! ' ELSE CALL CloseCursor CALL CommitWork WRITE (*, '('' Row(s) Changed: '',I2)') RowCounter ENDIF ELSEIF (RowCounter .EQ. 0) THEN CALL CloseCursor CALL CommitWork ENDIF ELSEIF (SQLCode .EQ. MultipleRows) THEN Fetch = .FALSE.
9 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. It then focuses on dynamic non-queries and queries.
BEGIN DECLARE SECTION CLOSE CURSOR DECLARE CURSOR DELETE WHERE CURRENT DESCRIBE END DECLARE SECTION EXECUTE EXECUTE IMMEDIATE FETCH INCLUDE OPEN CURSOR PREPARE SELECT SQLEXPLAIN UPDATE WHERE CURRENT WHENEVER Dynamic commands that are not queries can be preprocessed at run time using the PREPARE and EXECUTE statements or the EXECUTE IMMEDIATE statement. Dynamic queries are preprocessed using the PREPARE and DESCRIBE commands in conjunction with the SQLDA or SQL Description Area and other data structures.
Figure 9-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 9-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 9-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 not be desirable to preprocess an SQL command before run time: You may need to code an application that permits ad hoc queries requiring that SQL commands 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 as a string of characters: CHARACTER*2048 DynamicHostVar . . EXECUTE IMMEDIATE :DynamicHostVar Understanding Dynamic Operations Dynamic Non-Queries are the only type of dynamic operations available to FORTRAN programmers. Non-queries 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.
SUBROUTINE UpdateStatistics CHARACTER*50 415 TableName EXEC SQL BEGIN DECLARE SECTION CHARACTER*1024 CmdLine EXEC SQL END DECLARE SECTION DO WHILE (TableName .NE. '/') 100 WRITE (*,*) 'Enter table name or a / to stop > ' READ(6,100) TableName FORMAT(A50) IF (TableName .NE. '/') THEN CmdLine ='UPDATE STATISTICS FOR TABLE '// TableName // 425 ;' EXEC SQL EXECUTE IMMEDIATE :CmdLine 445 IF (SQLCode .EQ.
EXEC SQL PREPARE MyNonQuery FROM :DynamicCommand; I = MaxIterations DO WHILE (I .NE. 0) EXEC SQL EXECUTE MyNonQuery; I = I - 1 END DO As soon as you process a COMMIT WORK or ROLLBACK WORK command, the temporary section is deleted. The program examined later in this chapter under \Sample Program Using PREPARE and EXECUTE" illustrates how to handle PREPARE and EXECUTE.
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 Program forex9a, whose runtime dialog is shown in Figure 9-4 and whose source code is given in Figure 9-5, can be used to execute the UPDATE STATISTICS command on any table in any DBEnvironment. This program prompts for both the DBEnvironment name and the name of tables for which to execute the UPDATE STATISTICS command.
Program to illustrate the EXECUTE IMMEDIATE command -- forex9a Event List: Prompt for the DBEnvironment Name CONNECT TO the DBEnvironment BEGIN WORK Prompt for the table name EXECUTE IMMEDIATE UPDATE STATISTICS command COMMIT WORK Repeat the above 3 steps until the user enters a / RELEASE from DBEnvironment Repeat the above 8 steps until the user enters a / Terminate the Program Enter DBEnvironment to CONNECT TO or a / to STOP > PartsDBE CONNECT TO DBEnvironment Successful CONNECT BEGIN WORK Enter Table Nam
PROGRAM forex9a C C C C C C * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * This program illustrates the use of SQL dynamic non-query commands executed from a FORTRAN program. This program demonstrates the use of the EXECUTE IMMEDIATE command.
END DO WRITE (*,*) 'Connect was Unsuccessful!' C END (* Beginning of the Subroutines *) C LOGICAL*2 FUNCTION ConnectDBE() (**** Subroutine to connect to user entered DBEnvironment ****) C C (* Begin Communication Area *) EXEC SQL INCLUDE SQLCA C (**** Begin Host Variable Declarations ****) EXEC SQL BEGIN DECLARE SECTION CHARACTER*80 DBEnvironment CHARACTER*80 SQLMessage EXEC SQL END DECLARE SECTION 425 EXEC SQL WHENEVER SQLERROR GOTO 1000 EXEC SQL WHENEVER SQLWARNING GOTO 1000 EXEC SQL WHENEVER NO
1000 2000 C CALL SQLStatusCheck ConnectDBE = .FALSE.
1000 2000 C WRITE (*,*) 'COMMIT WORK' EXEC SQL COMMIT WORK GOTO 2000 CALL SQLStatusCheck CALL ReleaseDBE RETURN EXEC SQL WHENEVER SQLERROR CONTINUE EXEC SQL WHENEVER SQLWARNING CONTINUE EXEC SQL WHENEVER NOT FOUND CONTINUE END SUBROUTINE ReleaseDBE (**** Subroutine to Release the DBEnvironment ****) C (* Begin Communication Area *) EXEC SQL INCLUDE SQLCA C (**** Begin Host Variable Declarations ****) EXEC SQL BEGIN DECLARE SECTION EXEC SQL END DECLARE SECTION 455 WRITE (*,*) 'RELEASE DBEnvironment'
C (**** Begin SQL Communication Area ****) EXEC SQL INCLUDE SQLCA CHARACTER*30 C Static (**** Begin Host Variable Declarations ****) EXEC SQL BEGIN DECLARE SECTION CHARACTER*50 TableName CHARACTER*81 Command CHARACTER*80 SQLMessage EXEC SQL END DECLARE SECTION EXEC SQL WHENEVER SQLERROR GOTO 1000 EXEC SQL WHENEVER SQLWARNING GOTO 1000 EXEC SQL WHENEVER NOT FOUND GOTO 1000 100 110 1000 2000 C C WRITE (*,100) FORMAT (/$, 'Enter Table Name or a / to Stop > ') READ (*,110) TableName FORMAT(A50) IF (Tabl
1 2 DeadLock = NotFound = -14024, 100) C (**** Begin Communication Area ****) EXEC SQL INCLUDE SQLCA C (**** Begin Host Variable Declarations ****) EXEC SQL BEGIN DECLARE SECTION CHARACTER*80 SQLMessage EXEC SQL END DECLARE SECTION Abort = .FALSE. Check = .TRUE. IF (SQLWarn(0) .EQ. 'W') THEN WRITE (*,*) 'HPSQL Warning!' ELSEIF (SQLCode .EQ. NotFound) THEN WRITE (*,*) 'No record found for this PartNumber!' ELSEIF (SQLCode .EQ.
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 . .
Program to illustrate the PREPARE and EXECUTE commands -- forex9b Event List: CONNECT TO PartsDBE BEGIN WORK Prompt for SQL command PREPARE SQL command EXECUTE SQL command Repeat the above 3 steps until the user enters a / COMMIT WORK RELEASE from DBEnvironment CONNECT TO PartsDBE Successful CONNECT BEGIN WORK Successful BEGIN Enter an SQL non-query command or a / to stop: >UPDATE STATISTICS FOR TABLE PurchDB.Parts; Dynamic command to PREPARE is: UPDATE STATISTICS FOR TABLE PurchDB.Parts PREPARE successful.
C C C C C C * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * This program illustrates the use of SQL dynamic non-query commands executed from a FORTRAN program. This program demonstrates the use of the PREPARE and EXECUTE commands.
C C (* Beginning of the Subroutines *) LOGICAL*2 FUNCTION ConnectDBE() (**** Subroutine to CONNECT TO PartsDBE ****) 425 INTEGER*2 OK PARAMETER (OK = 0) C (* Begin Communication Area *) EXEC SQL INCLUDE SQLCA C (**** Begin Host Variable Declarations ****) EXEC SQL BEGIN DECLARE SECTION EXEC SQL END DECLARE SECTION WRITE (*,*) 'CONNECT TO PartsDBE' EXEC SQL CONNECT TO 'PartsDBE' ConnectDBE = .TRUE. IF (SQLCode .NE. OK) THEN ConnectDBE = .FALSE.
C WRITE (*,*) 'BEGIN WORK' EXEC SQL BEGIN WORK IF (SQLCode .NE. OK) THEN CALL SQLStatusCheck CALL ReleaseDBE ELSE WRITE (*,*) 'Successful BEGIN' ENDIF RETURN END SUBROUTINE CommitWork (**** Subroutine to COMMIT WORK ****) 445 INTEGER*2 OK PARAMETER (OK = 0) C (* Begin Communication Area *) EXEC SQL INCLUDE SQLCA C (**** Begin Host Variable Declarations ****) EXEC SQL BEGIN DECLARE SECTION EXEC SQL END DECLARE SECTION WRITE (*,*) 'COMMIT WORK' EXEC SQL COMMIT WORK IF (SQLCode .NE.
WRITE (*,*) 'RELEASE DBEnvironment' EXEC SQL ROLLBACK WORK RELEASE IF (SQLCode .NE.
100 110 120 C C WRITE (*,100) FORMAT(/'Enter an SQL non-query command or / to STOP ' 1 ,//$,' > ') READ (*,110) DynamicCommand FORMAT(A80) IF (DynamicCommand .EQ. '/') THEN WRITE (*,*) 'No more commands.' PrepareExecute = .FALSE. ELSE WRITE (*, 120) DynamicCommand FORMAT (/'The dynamic command to PREPARE is: '//, A80) EXEC SQL PREPARE CMD1 FROM :DynamicCommand IF (SQLCode .NE. OK) THEN CALL SQLStatusCheck WRITE (*,*) 'PREPARE failed.' ELSE WRITE (*,*) 'PREPARE successful.
C (**** Begin Host Variable Declarations ****) EXEC SQL BEGIN DECLARE SECTION CHARACTER*80 SQLMessage EXEC SQL END DECLARE SECTION Abort = .FALSE. Check = .TRUE. IF (SQLWarn(0) .EQ. 'W') THEN WRITE (*,*) 'HPSQL Warning!' ELSEIF (SQLCode .EQ. NotFound) THEN WRITE (*,*) 'No record found for this PartNumber!' ELSEIF (SQLCode .EQ. MultipleRows) THEN WRITE (*,*) 'Multiple records exit for this PartNumber!' ELSEIF (SQLCode .EQ. DeadLock) THEN Abort = .TRUE.
10 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 contraints 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 10-2. Constraint Test Matrix DML Operations UNIQUE or PRIMARY KEY Referenced Table INSERT or Type 2 Must be unique in the table. INSERT 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 10-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.
11 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 11-1. Flow of LONG Column Data and Related Information to the Database Figure 11-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 an 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 11-2.
Using LONG Columns with a SELECT Command Should an error occur before completion of the 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.
Removing LONG Column Data with a DELETE [WHERE CURRENT] Command Syntax for the DELETE and DELETE WHERE CURRENT commands is unchanged for use with LONG columns. It is limited for the DELETE command in that a LONG column cannot be used in the WHERE clause. In the following example, any rows in PartsTable with the PartName of hammer are deleted. DELETE FROM PartsTable WHERE PartName = 'hammer' When LONG column data is deleted, the space it occupied in the DBEnvironment is released when your transaction ends.
Considering Multiple Users With multiple users reading the same LONG column data, it is preferable for each user to run the application in a local area. This can prevent le access problems. If several users must access the same data from the same group, you might want to use the wildcard option ($) and avoid using the overwrite option (!). Deciding How Much Space to Allocate and Where Remember to consider the space requirements of any DBEFileSet used for LONG column data.
12 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.
For all date/time functions, character input and output values are in Native-3000 format. Note Where Date/Time Functions Can Be Used Use date/time functions, as you would an expression, in the DML operations listed below: Table 12-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 .
INSERT INTO MANUFDB.
NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN WHERE BatchStamp = TO_DATETIME (:BatchStamp, :BatchStamp-Format) NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 12-6 Programming with ALLBASE/SQL Functions
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 12-2 with one exception.
NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN WHERE TO_DATETIME(:BatchStamp, :ItalianFormat) = :SpecifiedInput Note the use of indicator variables in the above example. Because the TO CHAR function is used in the select list, no need exists to specify an indicator variable as part of the function.
Example TO INTEGER Function The TO INTEGER format speci cation is mandatory and di ers from that of other date/time functions in that it must consist of a single element only. See the ALLBASE/SQL Reference Manual for detailed format speci cations. Perhaps you are writing a management report that indicates the quarter of the year in which tests were performed. (As in the previous example, data is located in the TestData table in the manufacturing database.
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 12-1.
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 FROM WHERE AND TID(sp), TID(o) PurchDB.SupplyPrice sp, PurchDB.Orders o sp.VendorNumber = :VendorNumber 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, or a constant. 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 12-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.
Comparing TID Access to Other Types of Data Access When using TID functionality, data access speed is always improved compared to the speed of other ALLBASE/SQL access methods, for the following reasons: Index access must lock more pages (i.e. index pages). Relation access locks more pages to nd the TID of any qualifying row. Hash access employs more search overhead. Note that use of the TID function in a WHERE clause does not guarantee that TID access will be chosen by the optimizer.
Considering Interactive User Applications Some transaction management basics that apply to TID functionality when used in interactive applications are listed below: Be sure to avoid holding locks against the database within a transaction driven by interactive user input. This is sometimes termed \holding locks around terminal reads." It means that the speed at which the user enters required data determines the execution time of your transaction and thus the time span of transaction locks.
Index Special characters * in SELECT, 6-3 A active set and cursors, 6-15 and sequential processing, 6-1 in sequential processing, 8-1 using the CLOSE command before re-accessing, 8-8 ADD MONTHS function example with SELECT, 12-11 syntax, 12-11 aggregate function, 6-3, 6-9 and WHERE clause, 7-2 null values in, 4-4 ALTER TABLE command syntax for LONG columns, 11-4 ANSI SQL1 level 2 specifying a default value, 4-11 ANSI SQL86 level 2 oating point data, 4-9 atomic operation transaction as, 5-3 authority DBA
embedded SQL, 1-1 embedding SQL, 3-1 END DECLARE SECTION, 3-11, 3-14 EXECUTE, 6-23 EXECUTE IMMEDIATE, 6-23 FETCH, 5-42, 6-16, 8-1, 8-3 INCLUDE SQLCA, 3-11, 3-13, 4-24, 5-5 INSERT, 6-1, 7-4 OPEN, 6-16, 8-1, 8-3 PREPARE, 6-23 ROLLBACK WORK, 3-16 SELECT, 6-1, 6-2, 7-1 SQLEXPLAIN, 3-20, 5-2, 5-7, 5-15 UPDATE, 6-1, 7-7 UPDATE WHERE CURRENT, 8-1, 8-5 WHENEVER, 3-17, 5-8, 5-10 WHENEVER SQLERROR, 3-17 comments in embedded SQL les, 3-12 comments in ALLBASE/SQL commands, 3-12 COMMIT WORK, 2-40, 3-16 in transaction ma
data structure, 3-13 data type compatibility, 4-13 data type conversion, 4-13 data types, 4-8 binary, 4-10 compatibility with ALLBASE/SQL types, 4-1 oating point, 4-9 for host variables, 4-7 used with LONG columns, 11-2 data values, 4-1 data visibility, 6-8 date/time ADD MONTHS function overview, 12-11 where to use, 12-11 date/time functions coding considerations, 12-11 data compatibility, 12-2, 12-3 examples using ManufDB database, 12-4, 12-8, 12-11 example using default format speci cations, 12-5 how used
not used with LONG VARBINARY data, 4-12 NULL, 4-11 result of CURRENT DATE function, 4-11 result of CURRENT DATETIME function, 4-11 result of CURRENT TIME function, 4-11 USER, 4-11 default format speci cation example date/time functions, 12-5 de ning integrity constraints, 10-2 de ning LONG columns in a table, 11-4 input and output speci cation, 11-5 with the LONG column I/O string, 11-5 de nitions input device speci cation, 11-5 LONG column I/O string, 11-5 output device speci cation, 11-5 row level integri
SELECT command with TO INTERVAL function, 12-7 UPDATE command with TO DATE function, 12-5 UPDATE command with TO DATETIME function, 12-5 example application design using integrity constraints, 10-3 example data le INSERT command with LONG columns, 11-6 examples of date/time input functions, 12-4 examples of date/time output functions, 12-8, 12-10 EXEC SQL, 3-11 command delimiter, 1-3 pre x, 3-11 executable program, 1-2 in program development cycle, 1-1 EXECUTE, 6-23 non-dynamic queries, 9-7 EXECUTE IMMEDIAT
used for LONG column I/O strings, 11-6 used with date/time functions, 12-2 using, 4-1 with SELECT, 6-2 I IF compiler directive, 2-4 implicit status checking, 5-2, 5-8, 5-15 embedded commands for, 3-17 IN clause in DECLARE CURSOR, 8-2 INCLUDE compiler directive, 2-4 include le, 2-1 as input le, 2-5 created by preprocessor, 1-2 in program development cycle, 1-1 overwritten, 2-2 SQLVAR, 2-33 INCLUDE SQLCA, 3-11, 3-13, 4-24, 5-5 index, 6-11 count, 6-10 preventing duplicate values, 7-3 index scan, 6-10 and lock
locks releasing after a CLOSE, 8-8 log le space use during preprocessing, 2-44 logical data consistency, 5-9 LONG binary data compatibility, 4-10 de nition, 4-10 how stored, 4-10 LONG binary versus LONG varbinary data usage, 4-10 LONG column de nition in a table, 11-4 input and output speci cation , 11-5 with the LONG column I/O string, 11-5 LONG column descriptor contents of, 11-7 example declarationtitle;, 11-8 general concept, 11-2 how used, 11-7 introduction to, 11-5 LONG column I/O string general conce
module owner name, 2-38 multiple rows detecting with SQLCode, 5-42 multiple users of LONG columns, 11-11 multiuser mode commands with, 3-15 N name module, 2-43 module owner, 2-38 naming conventions for LONG column les, 11-10 NATIVE-3000 de ned, 1-6 native language and SQLEXPLAIN, 3-20 current language, 1-6 defaults, 1-6 native language support message catalog, 2-34 overview, 1-6 SQLMSG, 2-34 NODROP option full preprocessing mode, 2-43 non-dynamic commands, 9-1 NOT FOUND condition with WHENEVER, 5-16 NOT NU
full preprocessing mode, 2-4, 2-43, 2-44 functions, 1-2 in program development cycle, 1-1 input, 2-5 invocation, 2-40 modes and invocation, 2-41 options, 2-43 output, 2-5 output le attributes, 2-18 parsing SQLIN, 2-8 permanent les, 2-18 running in job mode, 2-50 syntax checking mode, 2-4, 2-41 temporary les, 2-18 UDC's, 2-46 using, 2-1 preprocess-time events, 1-5 PRESERVE option full preprocessing mode, 2-43 procedure external, 1-9 program DBEnvironment access, 1-4 development cycle, 1-1 executable, 1-2 exe
speci ed by WHERE clause, 6-3 section components of, 2-38 creation, 1-10 de ned, 1-10 dynamic vs. non-dynamic, 9-2 identifying number, 1-10 number, 2-39 permanently stored, 9-2 purpose, 1-10 stored, 2-38 system catalog information, 2-38 SYSTEM.
determining number of rows with, 5-38 in creating a warning for users, 5-5 possible values of, 5-9 set to number of rows processed, 5-4 SQLERRD(3) error checking with FORTRAN, 10-1 SQLERROR WHENEVER, 3-17 SQLERROR condition with WHENEVER, 5-15 SQLEXPLAIN, 3-20, 5-2 and approaches to status checking, 5-15 and message catalog, 1-15 obtaining multiple SQLCodes, 5-7 SQLIN preprocessor input, 2-8 SQLIND, 3-14 host variables and, 4-13 type description, 4-8 SQLMOD, 2-5 SQLMSG, 2-5 defaults, 2-34 line numbers, 2-35
example with DELETE command, 12-7 example with INSERT command, 12-4 example with SELECT command, 12-7, 12-8 example with UPDATE command, 12-5 syntax, 12-3 TO INTEGER function example with FETCH command, 12-10 syntax, 12-8 TO INTERVAL function example with INSERT command, 12-4 example with SELECT command, 12-7 syntax, 12-3 TO TIME function example with INSERT command, 12-4 syntax, 12-3 transaction, 3-16 embedded commands for, 3-16 transaction management cursor processing, 8-10 simple data manipulation, 7-9 t
