Preprocessor Input and Output

Regardless of the mode you use, the following input files must be available when you invoke the FORTRAN preprocessor, as shown in Title not available:

source file: a file containing the source code of the FORTRAN program with embedded SQL commands for one or more DBEnvironments. The default input filename is:
SQLIN
An alternative name can be specified by using a file equation as shown later in this chapter.
ALLBASE/SQL message catalog: a file containing preprocessor messages and ALLBASE/SQL error and warning messages. The formal file designator for the message catalog is as follows, with xxx being the numeric representation for the current native language:
SQLCTxxx.PUB.SYS
When you run the preprocessor in full preprocessing mode, also ensure that the DBEnvironment accessed by the program is available.
As Title not available points out, the FORTRAN preprocessor creates the following output files:
modified source file: a file containing the modified version of the source code in the source file. The default filename for this file is:
SQLOUT
An alternative name can be specified by using a file equation.
variable include file: the name for this file, which contains variable declarations used by FORTRAN statements that the preprocessor inserts into the modified source file is:
SQLVAR
Both SQLOUT and SQLVAR are created as permanent files in order to invoke the FORTRAN compiler, as shown in Title not available.
ALLBASE/SQL message file: a file containing the preprocessor banner, error, and warning messages, and other messages. The file name for this file is:
SQLMSG
installable module file: a file containing a copy of the module created by the preprocessor. The file name for this file is:
SQLMOD
When you run the preprocessor in full preprocessing mode, the preprocessor also stores a module in the DBEnvironment accessed by your program. The module is used at runtime to execute DBEnvironment operations.

Figure 2-3 FORTRAN Preprocessor Input and Output

Figure 2-4 FORTRAN Compiler Input

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 file containing the application you want to preprocess:
:FILE SQLIN = InFile

After running the preprocessor, save and rename the output files if you do not want them overwritten. For example:


     :SAVE SQLOUT
     :RENAME SQLOUT, OutFile
     :SAVE SQLMOD
     :RENAME SQLMOD, ModFile
     :SAVE SQLVAR
     :RENAME SQLVAR, VarFile

When you are ready to compile the program, you must equate the include file name to its standard ALLBASE/SQL name (SQLVAR).

Source File

The preprocessor source file must contain at a minimum the following statements:


   PROGRAM Statement
   AnyStatement
   END

When parsing the source file, 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. Unless you specify a module name in the preprocessor invocation line, the preprocessor uses the PROGRAM Statement or the SUBROUTINE name to name the module it stores. A module name can contain as many as 20 bytes and must follow the rules governing ALLBASE/SQL basic names (given in the ALLBASE/SQL Reference Manual ).
Statements found after the prefix EXEC SQL. Follow the rules given in Chapter 3 for how and where to embed these statements.
Statements found between the BEGIN DECLARE SECTION and END DECLARE SECTION commands. These commands delimit a declare section, which contains FORTRAN data description entries for the host variables used in that program or subprogram unit. All program units (both main and subprogram) that contain SQL commands, regardless of whether or not they contain host variables, must include the BEGIN DECLARE SECTION and the END DECLARE SECTION commands in order to create the variable include file. Host variables are described in Chapter 4.
The FORTRAN compiler directives $SET, $IF, $ELSE, $ENDIF, and $INCLUDE are supported by the FORTRAN preprocessor. All other compiler directives are ignored.

Figure 2-6 “Program forex2 ” illustrates a source file containing a sample program using the following SQL commands, highlighted with shading:

INCLUDE SQLCA
BEGIN DECLARE SECTION
END DECLARE SECTION
WHENEVER
CONNECT
BEGIN WORK
COMMIT WORK
RELEASE
SQLEXPLAIN
SELECT

As the following interactive sample dialog illustrates, the program begins a DBE session for PartsDBE, the sample DBEnvironment. It prompts the user for a part number, then displays information about the part from the table PurchDB.Parts. Warning and error conditions are handled with WHENEVER and SQLEXPLAIN commands. The program continues to prompt for a part number until the user enters a slash (/) or until a serious error is encountered:

Figure 2-5 Runtime Dialog of Program forex2


   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.Parts table!
   COMMIT WORK

   Enter PartNumber from Parts table or / to STOP > 1323-D-01
   BEGIN WORK
   SELECT PartNumber, PartName, SalesPrice
     Part Number:  1323-D-01
     Part Name:    Floppy Diskette Drive
     Sales Price:      200.00
   Was retrieved from the PurchDB.Parts table!
   COMMIT WORK

   Enter PartNumber from Parts table or / to STOP > 1954-LP-01
   BEGIN WORK
   SELECT PartNumber, PartName, SalesPrice
   Row not found!
   COMMIT WORK

   Enter PartNumber from Parts Table or / to STOP > 1823-PT-01
   BEGIN WORK
   SELECT PartNumber, PartName, SalesPrice
     Part Number:  1823-PT-01
     Part Name:    Graphics Printer
     Sales Price:      450.00
   Was retrieved from the PurchDB.Parts table!
   COMMIT WORK

   Enter PartNumber from Parts table or / to STOP > /
   RELEASE PartsDBE
   END OF PROGRAM

Figure 2-6 Program forex2


         PROGRAM forex2
   C
   C     *********************************************************
   C     *  This program illustrates the use of SQL's SELECT     *
   C     *  command to retrieve one row or tuple of data at      *
   C     *  a time. This program executes a BEGIN WORK command   *
   C     *  before the SELECT command, and a COMMIT WORK command *
   C     *  after executing the SELECT command. An indicator     *
   C     *  variable is also used for SalesPrice.                *
   C     *********************************************************
   C
         {{EXEC SQL INCLUDE SQLCA}}
   C
   C            (* Begin SQL Communication Area *)
   C
              CHARACTER           Done
              CHARACTER           Abort
              INTEGER             MultipleRows
              INTEGER             Deadlock
              CHARACTER*16        Response
   C
   C     ****************************************************
   C     *  Data Type Conversions :                         *
   C     *    Character         = SQL Char(1)               *
   C     *    Character*n       = SQL Char(n)               *
   C     *    Character*n       = SQL VarChar               *
   C     *    Double Precision  = SQL Float                 *
   C     *    Double Precision  = SQL Decimal               *
   C     *    Integer           = SQL Integer               *
   C     *    Integer*2         = SQL SmallInt              *
   C     ****************************************************
   C
   C             (* Begin Host Variable Declarations *)
   C
         {{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
   C            (* End Host Variable Declarations *)
   C
   C
   C
   C
   C
   C            (* Beginning of the Main Program *)
   C
         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 ../sampledb/PartsDBE'
         WRITE (*,*) '  BEGIN WORK'
         WRITE (*,*) '  SELECT specified row from the Parts table until use
        1r enters a "/"'
         WRITE (*,*) '  COMMIT WORK'
         WRITE (*,*) '  RELEASE PartsDBE'
   C
         CALL ConnectDBE
         CALL QueryTable
         CALL ReleaseDBE
   C
         STOP
         END
   C
   C     (* Beginning of the Sub-Routines *)
   C
         SUBROUTINE ConnectDBE
   C            (* Subroutine to Connect to PartsDBE *)
   C
         {{EXEC SQL INCLUDE SQLCA}}
   C
   C            (* Begin SQL Communication Area *)
   C
   C            (* Begin Host Variable Declarations *)
   C
         {{EXEC SQL BEGIN DECLARE SECTION}}
         {{EXEC SQL END DECLARE SECTION}}
   C
         {{EXEC SQL WHENEVER SQLERROR GOTO 500}}
   C
         WRITE (*,*) ' '
         WRITE (*,*) 'CONNECT TO PartsDBE'
         {{EXEC SQL CONNECT TO 'PartsDBE'}}
         GOTO 600
   500   CALL SQLStatusCheck
         CALL EndTransaction
         CALL ReleaseDBE
   C
   600   RETURN
         {{EXEC SQL WHENEVER SQLERROR CONTINUE}}
         END
   C     (* End of ConnectDBE Subroutine *)
   C
         SUBROUTINE BeginTransaction
   C            (* Subroutine to Begin Work *)
   C
         {{EXEC SQL INCLUDE SQLCA}}
   C
   C            (* Begin SQL Communication Area *)
   C
   C            (* Begin Host Variable Declarations *)
   C
         {{EXEC SQL BEGIN DECLARE SECTION}}
         {{EXEC SQL END DECLARE SECTION}}
   C
         {{EXEC SQL WHENEVER SQLERROR GOTO 500}}
   C
         WRITE (*,*) 'BEGIN WORK'
         {{EXEC SQL BEGIN WORK}}
         GOTO 600
   500   CALL SQLStatusCheck
         CALL EndTransaction
         CALL ReleaseDBE
   600   RETURN
         {{EXEC SQL WHENEVER SQLERROR CONTINUE}}
         END
   C     (* End BeginTransaction Subroutine *)
   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
         {{EXEC SQL BEGIN DECLARE SECTION}}
         {{EXEC SQL END DECLARE SECTION}}
   C
         {{EXEC SQL WHENEVER SQLERROR GOTO 500}}
         WRITE (*,*) 'COMMIT WORK'
         {{EXEC SQL COMMIT WORK}}
         GOTO 600
   500   CALL SQLStatusCheck
         CALL ReleaseDBE
   C
   600   RETURN
         {{EXEC SQL WHENEVER SQLERROR CONTINUE}}
         END
   C     (* End EndTransaction Subroutine *)
   C
         SUBROUTINE ReleaseDBE
   C     (* Subroutine to Release PartsDBE *)
   C
         {{EXEC SQL INCLUDE SQLCA}}

   C            (* Begin SQL Communication Area *)
   C
   C            (* Begin Host Variable Declarations *)
   C
         {{EXEC SQL BEGIN DECLARE SECTION}}
         {{EXEC SQL END DECLARE SECTION}}
   C
         {{EXEC SQL WHENEVER SQLERROR GOTO 500}}
   C
         WRITE (*,*) 'RELEASE PartsDBE'
         {{EXEC SQL RELEASE}}
         GOTO 600
   500   CALL SQLStatusCheck
         CALL EndTransaction
   C
   600   RETURN
         {{EXEC SQL WHENEVER SQLERROR CONTINUE}}
         END
   C     (* End ReleaseDBE Subroutine *)
   C
   C
   C
   C
   C
   C
   C
   C
   C
   C
   C
   C
   C
         SUBROUTINE DisplayRow (PartNumber,PartName,SalesPrice,
        1SalesPriceInd)
   C     (* Subroutine to Display a Selected Row *)
   C
         {{EXEC SQL INCLUDE SQLCA}}
   C
   C            (* Begin SQL Communication Area *)
   C
   C            (* Begin Host Variable Declarations *)
   C
         {{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
         IF (SalesPriceInd .LT. 0) THEN
         WRITE (*,*) 'Sales Price is NULL'
         ELSE
         WRITE(*,120) SalesPrice
         ENDIF
         WRITE (*,*) 'Was retrieved from the PurchDB.Parts table!'
   100   FORMAT('   Part Number:    ',A16)
   110   FORMAT('   Part Name:      ',A30)
   120   FORMAT('   SalesPrice:     ',F10.2)
   C
         RETURN
         END
   C     (* End DisplayRow Subroutine *)
   C
         SUBROUTINE SQLStatusCheck
   C     (* Subroutine to Check the Status of DeadLocks *)
   C
         {{EXEC SQL INCLUDE SQLCA}}
   C
   C            (* Begin SQL Communication Area *)
   C
         LOGICAL             Abort
         INTEGER             DeadLock
   C
   C
   C
   C            (* Begin Host Variable Declarations *)
   C
         {{EXEC SQL BEGIN DECLARE SECTION}}
         CHARACTER*80        SQLMessage
         {{EXEC SQL END DECLARE SECTION}}
   C
   C            (* End Host Variable Declarations *)
   C
         DeadLock = -14024
         Abort = .TRUE.
         WRITE (*,*) Abort
         IF (SQLCode .LT. DeadLock) THEN
   	Abort = .TRUE.
         ELSE
   	Abort = .FALSE.
         ENDIF
         DO WHILE (SQLCode .NE. 0)
         {{EXEC SQL SQLExplain :SQLMessage}}
         WRITE (*,*) SQLMessage
         END DO
         IF (Abort) THEN
   	CALL EndTransaction
   	CALL ReleaseDBE
         ENDIF
         RETURN
         END
   C     (* End of SQLStatusCheck Subroutine *)
   C
         SUBROUTINE QueryTable
   C     (* Subroutine to Query the Parts table *)
   C
         {{EXEC SQL INCLUDE SQLCA}}
   C
   C            (* Begin SQL Communication Area *)
   C
         INTEGER              DeadLock
         INTEGER              MultipleRows
         INTEGER              NotFound
         INTEGER              OK
   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}}
   C
   C            (* End Host Variable Declarations *)
   C
         MultipleRows = -10002
         DeadLock = -14024
         NotFound = 100
         OK = 0
   C
         DO WHILE (PartNumber .NE. '/')
   	 WRITE(*,100)
   100      FORMAT(/$,' Enter PartNumber from Parts table or / to STOP > ')
   	 READ(*,110) PartNumber
   110      FORMAT (A16)
   C
   	 IF (PartNumber .NE. '/' ) THEN
   C
   	    CALL BeginTransaction
   	    WRITE(*,*) 'SELECT PartNumber, PartName, SalesPrice'
   C
   	    {{EXEC SQL SELECT  PartNumber, PartName, SalesPrice\
        1                 INTO :PartNumber,\
        2                      :PartName,\
        3                      :SalesPrice :SalesPriceInd\
        4                 FROM  PurchDB.Parts\
        5                WHERE  PartNumber = :PartNumber}}
   C
   	    IF ((SQLWarn(3) .EQ. 'w') .OR. (SQLWarn(3) .EQ. 'W')) THEN
   	       WRITE (*,*) 'SQL WARNING has occurred. The following row'
   	       WRITE (*,*) 'of data may not be valid!'
   	       CALL DisplayRow (PartNumber,PartName,SalesPrice,
        1            SalesPriceInd)
   	    ENDIF
   C
   C
   C
   C
   C
   C
   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 *)

Output File Attributes

When the source file illustrated in Figure 2-6 “Program forex2 ” is preprocessed, the attributes of the output files are created as follows:


   :listftemp,2

     TEMPORARY FILES FOR SOMEUSER.SOMEACCT,SOMEGRP

     ACCOUNT=  SOMEACCT    GROUP=  SOMEGRP

     FILENAME  CODE  ----------LOGICAL RECORD---------  ----SPACE----
                     SIZE  TYP       EOF     LIMIT R/B  SECTORS #X MX

     SQLMOD          250W  FB         3       1023  1       208  1 10 (TEMP)
     SQLMSG          254B  VA        14       1023  1       128  1  8 (TEMP)
     SQLOUT           80B  FA       646      10000  16      384  3 32 (TEMP)
     SQLVAR           80B  FA         8       2048  16      128  1 26 (TEMP)

Modified Source File

As the FORTRAN preprocessor parses the source file, it copies lines from the source file into the modified source file, comments out embedded SQL commands, and inserts information around each embedded SQL command. Figure 2-7 illustrates the modified source file generated for the source file pictured in Figure 2-6 “Program forex2 ”. The shaded lines contain information generated by the FORTRAN preprocessor.

In both preprocessing modes, the FORTRAN preprocessor:

Inserts a C in column 1 on each line containing an embedded SQL command to comment out the SQL command for the FORTRAN compiler.
Inserts one include FORTRAN compiler directive after the Type Declaration Section. This directive references the preprocessor generated include file (variable include file) during compilation.
Inserts a "Start SQL Preprocessor" comment before, and an "End SQL Preprocessor" comment after code that it modifies.

In full preprocessing mode, the preprocessor also:

Generates a FORTRAN COMMON BLOCK declaration of SQLCA following the EXEC SQL INCLUDE SQLCA command.
Generates FORTRAN statements providing conditional instructions following SQL commands encountered after one of the following SQL commands: WHENEVER SQLERROR, WHENEVER SQLWARNING, and WHENEVER NOT FOUND.
Generates FORTRAN statements that call ALLBASE/SQL external procedures at runtime. These calls reference the module stored by the preprocessor in the DBEnvironment for execution at runtime. Variables used by these external calls are defined in the variable declaration include file.
Inserts a "Start Inserted Statements" comment before generated information.




	CAUTION: Although you can access the modified source file and the variable declaration file with an editor, you should never change the information generated by the FORTRAN preprocessor. Your DBEnvironment or other files on the system could be damaged at runtime if preprocessor generated statements are altered.

If you change non-preprocessor generated statements in the modified source file, make the changes to the source file, re-preprocess the source file, and re-compile the output files before putting the application program into production.

Figure 2-7 Modified Source File for Program forex2


   {{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)\
   $ALIAS SQLXID   = 'SQLXID' PASCAL       \ \
   $      (%REF,%REF,%REF,%VAL,%REF,%VAL,%VAL)\
   $ALIAS SQLXOPKF = 'SQLXOPKF' PASCAL     \ \
   $      (%REF,%REF,%REF,%VAL,%REF,%VAL,%VAL)\
   $ALIAS SQLXPLNF = 'SQLXPLNF' PASCAL     \ \
   $      (%REF,%REF,%VAL,%VAL)\
   $ALIAS SQLXPREF = 'SQLXPREF' PASCAL     \ \
   $      (%REF,%REF,%VAL,%REF,%VAL)\
   $ALIAS SQLXSECF = 'SQLXSECF' PASCAL     \ \
   $      (%REF,%REF,%REF,%VAL)\
   $ALIAS SQLXST   = 'SQLXST' PASCAL       \ \
   $      (%REF)\
   $ALIAS SQLXSVPF = 'SQLXSVPF' PASCAL     \ \
   $      (%REF,%VAL,%REF,%REF)\
   C**** End SQL Preprocessor   ****}}
         PROGRAM forex2
   C     *********************************************************
   C     *  This program illustrates the use of SQL's SELECT     *
   C     *  command to retrieve one row or tuple of data at      *
   C     *  a time. This program executes a BEGIN WORK command   *
   C     *  before the SELECT command, and a COMMIT WORK command *
   C     *  after executing the SELECT command. An indicator     *
   C     *  variable is also used for SalesPrice.                *
   C     *********************************************************
   {{C**** Start SQL Preprocessor ****\
   C     EXEC SQL INCLUDE SQLCA\
   C            (* Begin SQL Communication Area *)\
   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)}}
   {{      CHARCTER 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,SQLWARN(5)),\
        6            (SQLWARN6,SQLWARN(6)),\
        7            (SQLWARN7,SQLWARN(7))\
         COMMON /SQLCA/ SQLCAID,SQLCABC,SQLCODE,SQLERRL,\
        1               SQLERRM,SQLERRP,SQLERRD,SQLWARN,SQLEXT\
   C**** End SQL Preprocessor   ****}}
              CHARACTER           Done
              CHARACTER           Abort
              INTEGER             MultipleRows
              INTEGER             Deadlock
              CHARACTER*16        Response
   C
   C     ****************************************************
   C     *  Data Type Conversions :                         *
   C     *    Character         = SQL Char(1)               *
   C     *    Character*n       = SQL Char(n)               *
   C     *    Character*n       = SQL VarChar               *
   C     *    Double Precision  = SQL Float                 *
   C     *    Double Precision  = SQL Decimal               *
   C     *    Integer           = SQL Integer               *
   C     *    Integer*2         = SQL SmallInt              *
   C     ****************************************************
   C             (* Begin Host Variable Declarations *)
   C
   {{C**** Start SQL Preprocessor ****\
   C     EXEC SQL BEGIN DECLARE SECTION\
   C**** End SQL Preprocessor   ****\
         CHARACTER*16         PartNumber\
         CHARACTER*30         PartName\
         DOUBLE PRECISION     SalesPrice\
         INTEGER*2 SalesPriceInd\
   C     SQLIND               SalesPriceInd}}
         CHARACTER*80         SQLMessage
   {{C**** Start SQL Preprocessor ****\
   C     EXEC SQL END DECLARE SECTION}}
   C
   C            (* End Host Variable Declarations *)
   C
   C            (* Beginning of the Main Program *)
   C
   {{C**** End SQL Preprocessor   ****}}
         {{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'
   C
         CALL ConnectDBE
         CALL QueryTable
         CALL ReleaseDBE
   C
         STOP
         END
   C
   C     (* Beginning of the Sub-Routines *)
   C
         SUBROUTINE ConnectDBE
   C            (* Subroutine to Connect to PartsDBE *)
   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,\
        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,SQLWARN(5)),\
        6            (SQLWARN6,SQLWARN(6)),\
        7            (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 SQL Preprocessor ****\
   C     EXEC SQL END DECLARE SECTION\
   C\
   C**** End SQL Preprocessor   ****\
         INCLUDE 'SQLVAR'\
   C**** Start SQL Preprocessor ****\
   C     EXEC SQL WHENEVER SQLERROR GOTO 500\
   C\
   C**** Start Inserted Statements ****\
   C**** End SQL Preprocessor   ****}}
         WRITE (*,*) ' '
         WRITE (*,*) 'CONNECT TO PartsDBE'
   {{C**** Start SQL Preprocessor ****\
   C     EXEC SQL CONNECT TO 'PartsDBE'\
   C**** Start Inserted Statements ****\
         CALL SQLXCO(SQLCAID,264,'00AE0000506172747344424520202020202020202\
        1020202020202020202020202020202020202020202020202020202020202020202\
        2020202020202020202020202020202020202020202020202020202020202020202\
        3020202020202020202020202020202020202020202020202020202020202020202\
        40202020202020202020202020')\
         IF (SQLCODE .LT. 0) THEN\
           GO TO 500\
         END IF\
   C**** End SQL Preprocessor   ****}}
         GOTO 600
   500   CALL SQLStatusCheck
         CALL EndTransaction
         CALL ReleaseDBE
   C
   600   RETURN
   {{C**** Start SQL Preprocessor ****\
   C     EXEC SQL WHENEVER SQLERROR CONTINUE\
   C**** Start Inserted Statements ****\
   C**** End SQL Preprocessor   ****}}
         END
   C     (* End of ConnectDBE Subroutine *)
         SUBROUTINE BeginTransaction
   C            (* Subroutine to Begin Work *)
   {{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)\
         INTEGER   SQLEXT(2)\
         CHARACTER 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,SQLWARN(5)),\
        6            (SQLWARN6,SQLWARN(6)),\
        7            (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 SQL Preprocessor ****\
   C     EXEC SQL END DECLARE SECTION\
   C\
   C**** End SQL Preprocessor   ****\
         INCLUDE 'SQLVAR'\
   C**** Start SQL Preprocessor ****\
   C     EXEC SQL WHENEVER SQLERROR GOTO 500\
   C\
   C**** Start Inserted Statements ****\
   C**** End SQL Preprocessor   ****}}
         WRITE (*,*) 'BEGIN WORK'
   {{C**** Start SQL Preprocessor ****\
   C     EXEC SQL BEGIN WORK\
   C**** Start Inserted Statements ****\
         CALL SQLXCO(SQLCAID,16,'00A6007F00110061')}}
         {{IF (SQLCODE .LT. 0) THEN\
           GO TO 500\
         END IF\
   C**** End SQL Preprocessor   ****}}
         GOTO 600
   500   CALL SQLStatusCheck
         CALL EndTransaction
         CALL ReleaseDBE
   600   RETURN
   {{C**** Start SQL Preprocessor ****\
   C     EXEC SQL WHENEVER SQLERROR CONTINUE\
   C**** Start Inserted Statements ****\
   C**** End SQL Preprocessor   ****}}
         END
   C     (* End BeginTransaction Subroutine *)
   C
         SUBROUTINE EndTransaction
   C     (* Subroutine to Commit Work *)
   {{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)\
         INTEGER   SQLEXT(2)\
         CHARACTER 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,SQLWARN(5)),\
        6            (SQLWARN6,SQLWARN(6)),\
        7            (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 SQL Preprocessor ****\
   C     EXEC SQL END DECLARE SECTION\
   C\
   C**** End SQL Preprocessor   ****\
         INCLUDE 'SQLVAR'\
   C**** Start SQL Preprocessor ****\
   C     EXEC SQL WHENEVER SQLERROR GOTO 500\
   C\
   C**** Start Inserted Statements ****\
   C**** End SQL Preprocessor   ****\
         WRITE (*,*) 'COMMIT WORK'\
   C**** Start SQL Preprocessor ****\
   C     EXEC SQL COMMIT WORK\
   C**** Start Inserted Statements ****\
         CALL SQLXCO(SQLCAID,8,'00A10000')\
         IF (SQLCODE .LT. 0) THEN\
           GO TO 500\
         END IF\
   C**** End SQL Preprocessor   ****}}
         GOTO 600
   500   CALL SQLStatusCheck
         CALL ReleaseDBE
   600   RETURN
   {{C**** Start SQL Preprocessor ****\
   C     EXEC SQL WHENEVER SQLERROR CONTINUE\
   C**** Start Inserted Statements ****\
   C**** End SQL Preprocessor   ****}}
         END
   C     (* End EndTransaction Subroutine *)
   C
         SUBROUTINE ReleaseDBE
   C     (* Subroutine to Release PartsDBE *)
   {{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,\
        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,SQLWARN(5)),\
        6            (SQLWARN6,SQLWARN(6)),\
        7            (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 SQL Preprocessor ****\
   C     EXEC SQL END DECLARE SECTION\
   C\
   C**** End SQL Preprocessor   ****\
         INCLUDE 'SQLVAR'\
   C**** Start SQL Preprocessor ****\
   C     EXEC SQL WHENEVER SQLERROR GOTO 500\
   C**** Start Inserted Statements ****\
   C**** End SQL Preprocessor   ****\
         WRITE (*,*) 'RELEASE PartsDBE'\
   C**** Start SQL Preprocessor ****\
   C     EXEC SQL RELEASE\
   C**** Start Inserted Statements ****\
         CALL SQLXCO(SQLCAID,56,'00B200002020202020202020202020202020202020\
        1202020FFFFFFFF')\
         IF (SQLCODE .LT. 0) THEN\
           GO TO 500\
         END IF\
   C**** End SQL Preprocessor   ****}}
         GOTO 600
   500   CALL SQLStatusCheck
         CALL EndTransaction
   600   RETURN
   {{C**** Start SQL Preprocessor ****\
   C     EXEC SQL WHENEVER SQLERROR CONTINUE\
   C**** Start Inserted Statements ****\
   C**** End SQL Preprocessor   ****}}
         END
   C     (* End ReleaseDBE Subroutine *)
         SUBROUTINE DisplayRow (PartNumber,PartName,SalesPrice,
        1SalesPriceInd)
   C     (* Subroutine to Display a Selected Row *)
   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,\
        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,SQLWARN(5)),\
        6            (SQLWARN6,SQLWARN(6)),\
        7            (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   ****}}
         CHARACTER*16     PartNumber
         CHARACTER*30     PartName
         DOUBLE PRECISION SalesPrice
   {{      INTEGER*2 SalesPriceInd\
   C     SQLIND           SalesPriceInd}}
         CHARACTER*80     SQLMessage
   {{C**** Start SQL Preprocessor ****\
   C     EXEC SQL END DECLARE SECTION\
   C\
   C**** End SQL Preprocessor   ****\
         INCLUDE 'SQLVAR'\
         WRITE(6,100) PartNumber}}
         {{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!'
   100   FORMAT('   Part Number:    ',A16)
   110   FORMAT('   Part Name:      ',A30)
   120   FORMAT('   SalesPrice:     ',F10.2)
   C
         RETURN
         END
   C     (* End DisplayRow Subroutine *)
   C
         SUBROUTINE SQLStatusCheck
   C     (* Subroutine to Check the Status of DeadLocks *)
   C
   {{C**** Start SQL Preprocessor ****\
   C     EXEC SQL INCLUDE SQLCA\
   C\
   C            (* Begin SQL Communication Area *)\
   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,\
        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,SQLWARN(5)),\
        6            (SQLWARN6,SQLWARN(6)),\
        7            (SQLWARN7,SQLWARN(7))\
         COMMON /SQLCA/ SQLCAID,SQLCABC,SQLCODE,SQLERRL,\
        1               SQLERRM,SQLERRP,SQLERRD,SQLWARN,SQLEXT\
   C**** End SQL Preprocessor   ****}}
         LOGICAL             Abort
         INTEGER             DeadLock
   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. 0)
   {{C**** Start SQL Preprocessor ****\
   C     EXEC SQL SQLExplain :SQLMessage\
   C**** Start Inserted Statements ****\
          CALL SQLXPLNF(SQLCAID,SQLTMP,80,0)\
           READ(SQLTMP,'(A80)')SQLMessage\
   C**** End SQL Preprocessor   ****}}
         WRITE (*,*) SQLMessage
         END DO
         IF (Abort) THEN
           CALL EndTransaction
           CALL ReleaseDBE
         ENDIF
         RETURN
         END
   C     (* End of SQLStatusCheck Subroutine *)
   C
         SUBROUTINE QueryTable
   C     (* Subroutine to Query the Parts table *)
   C
   {{C**** Start SQL Preprocessor ****\
   C     EXEC SQL INCLUDE SQLCA\
   C\
   C            (* Begin SQL Communication Area *)\
   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,\
        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,SQLWARN(5)),\
        6            (SQLWARN6,SQLWARN(6)),\
        7            (SQLWARN7,SQLWARN(7))\
         COMMON /SQLCA/ SQLCAID,SQLCABC,SQLCODE,SQLERRL,\
        1               SQLERRM,SQLERRP,SQLERRD,SQLWARN,SQLEXT\
   C**** End SQL Preprocessor   ****}}
         INTEGER              DeadLock
         INTEGER              MultipleRows
         INTEGER              NotFound
         INTEGER              OK
   C             (* Begin Host Variable Declarations *)
   {{C**** Start SQL Preprocessor ****\
   C     EXEC SQL BEGIN DECLARE SECTION\
   C**** End SQL Preprocessor   ****}}
         CHARACTER*16         PartNumber
         CHARACTER*30         PartName
         DOUBLE PRECISION     SalesPrice
   {{      INTEGER*2 SalesPriceInd\
   C     SQLIND               SalesPriceInd}}
         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'}}
         MultipleRows = -10002
         DeadLock = -14024
         NotFound = 100
         OK = 0
         DO WHILE (PartNumber .NE. '/')
   {{       WRITE(6,100)}}
   100      FORMAT(/$,' Enter PartNumber from Parts table or / to STOP > ')
            READ(5,110) PartNumber
   110      FORMAT (A16)
   C
            IF (PartNumber .NE. '/' ) THEN
   C
               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. 0) THEN\
         READ(SQLTMP,'(A16,A30,A8,A2)')PartNumber,PartName,SalesPrice,Sales\
        1PriceInd\
         ELSE\
         END IF\
   C**** End SQL Preprocessor   ****}}
               IF ((SQLWarn(3) .EQ. 'w') .OR. (SQLWarn(3) .EQ. 'W')) THEN
                  WRITE (*,*) 'SQL WARNING has occured. The following row'
                  WRITE (*,*) 'of data may not be valid!'
                  CALL DisplayRow (PartNumber,PartName,SalesPrice,
        1            SalesPriceInd)
               ENDIF
   C
               IF (SQLCode .EQ. OK) THEN
                  CALL DisplayRow (PartNumber, PartName, SalesPrice,
        1           SalesPriceInd)
               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 *)

Variable Declaration Include File

The preprocessor generated include file (SQLVAR), contains declarations for variables referenced in preprocessor generated statements in the modified source file. Figure 2-8 “Sample Variable Declaration Include File” illustrates the variable declaration include file that corresponds to the modified source file in Figure 2-7. Note in Figure 2-7 that just after inserting the EXEC SQL END DECLARE SECTION declaration into the modified source file, the preprocessor inserted the following FORTRAN compiler directive to reference the variable declaration include file:


   $INCLUDE 'SQLVAR'

This directive is always inserted after the Host Variable Type Declaration Section.

When you use file equations to redirect the include files, remember that the preprocessor always inserts the same $INCLUDE directive. Therefore, insure that the applicable file equations are in effect when you preprocess and when you compile. When the preprocessor is invoked, the following file equation must be in effect.


   :FILE SQLVAR = MYVAR

Then when the FORTRAN compiler is invoked, the following file equation must be in effect:


   :FILE SQLVAR = MYVAR
   :FTNC MYSQLPRG, $NEWPASS, $NULL

Figure 2-8 Sample Variable Declaration Include File


   C  temporary area
         CHARACTER*112 SQLTMP
   C  ownership information
         CHARACTER*20 SQLOWN
         CHARACTER*20 SQLMDN
         DATA SQLOWN /'JOANN@HPSQL         '/
         DATA SQLMDN /'FOREX2              '/
   C

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 file is not available. If the native language catalog is available, ALLBASE/SQL returns a warning message, indicating that the default catalog is being used. SQLMSG messages come in four four parts:

A banner:


   					   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

Banners are displayed when ISQL, SQLUtil, or a preprocessor is invoked.

A summary of the preprocessor invocation conditions:


   SQLIN                = FOREX2.SOMEGROUP.SOMEACCT
   DBEnvironment        = PartsDBE
   Module Name          = FOREX2

Warnings and errors encountered during preprocessing:


       SELECT PartNumber, PartName, SalesPrice INTO :PartNumber, :SalesPrice
       :SalesPriceInd FROM PurchDB.Parts WHERE PartNumber = :PartNumber;

****** ALLBASE/SQL errors  (DBERR 10952)
****** in SQL statement ending in line 290
*** Selectlist has 3 items and host variable buffer has 2.  (DBERR 2762)

There are errors.  No sections stored.

A summary of the results of preprocessing:


    1 ERRORS   0 WARNINGS
   END OF PREPROCESSING.

Both the banner and the preprocessing summary output are echoed to the standard output, the terminal.

As illustrated in Figure 2-9 “Sample SQLMSG Showing Error”, a line number is often provided in SQLMSG. This line number references the line in the modified source file 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.

Figure 2-9 Sample SQLMSG Showing Error


   :EDITOR
   HP32201A.07.20  EDIT/3000  MON, JUL 10, 1990, 4:49 PM
   (C) HEWLETT-PACKARD CO. 1990
   /T SQLMSG; L ALL UNN
   FILE UNNUMBERED

   SQLIN                = FOREX2.SOMEGROUP.SOMEACCT
   DBEnvironment        = PartsDBE
   Module Name          = FOREX2


   	SELCT PartNumber, PartName, SalesPrice INTO :PartNumber,
   	:SalesPrice :SalesPriceInd FROM PurchDB.Parts WHERE PartNumber =
   	:PartNumber;

   ******  ALLBASE/SQL errors (DBERR 10952)
   ******  in SQL statement ending in line 290
   *** Selectlist has 3 items and host variable buffer has 2.  (DBERR 2762)

   There are errors.  No sections stored.
     1 ERRORS   0 WARNINGS
    END OF PROCESSING.
    :

As Figure 2-10 “Sample SQLMSG Showing Warning” illustrates, the preprocessor can terminate with the warning message:


   ****** ALLBASE/SQL warnings. (DBWARN 10602)

when the name of an object in the source file 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.

Figure 2-10 Sample SQLMSG Showing Warning


   :EDITOR
   HP32201A.07.20  EDIT/3000  MON, JUL 10, 1991, 4:49 PM
   (C) HEWLETT-PACKARD CO. 1990
   /T SQLMSG; L ALL UNN
   FILE UNNUMBERED

   	      .
   	      .
   	      .

   SQLIN                = FOREX2.SOMEGROUP.SOMEACCT
   DBEnvironment        = PartsDBE
   Module Name          = FOREX2

          SELECT ParNumber, PartName, SalesPrice INTO :PartNumber,
          :PartName :SalesPrice :SalesPriceInd FROM PurchDB.Parts WHERE
          ParNumber = :PartNumber;

   ****** ALLBASE/SQL warnings. (DBWARN 10602)
   ****** in SQL statement ending in line 290
   *** Column PARNUMBER not found. (DBERR 2211)

     1 Sections stored in DBEnvironment.

    0 ERRORS   1 WARNINGS
   END OF PREPROCESSING

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 file. The name of this file is SQLMOD. The module in this file can be installed into a DBEnvironment different from the DBEnvironment accessed at preprocessing time by using the INSTALL command in ISQL. For example:


     :RUN PSQLFOR.PUB.SYS;INFO = "DBEnvironmentName&
     (MODULE (InstalledModuleName) DROP)" 


	  If you want to preserve the SQLMOD file after
	  preprocessing, you must keep it as a permanent
	  file.  Rename SQLMOD after making it permanent.

     :SAVE SQLMOD
     :RENAME SQLMOD, MYMOD

	  Before invoking ISQL to install this module file,
	  you may have to transport it and its related
	  program file to the machine containing the target
	  DBEnvironment.  After all the files are restored
	  on the target machine, you invoke ISQL on the
	  machine containing the target DBEnvironment.


     : isql

	  In order to install the module, you need CONNECT
	  or DBA authority in the target DBEnvironment:

     isql=> CONNECT TO 'PartsDBE.SOMEGROUP.SOMEACCT';
     isql=> INSTALL;

     File name> MYMOD.SOMEGROUP.SOMEACCT;
     Name of module in this file:  JOANN@SOMEACCT.FOREX2
     Number of sections installed:  1
     COMMIT WORK to save to DBEnvironment.

     isql=> COMMIT WORK;
     isql=>

Stored Sections

In full preprocessing mode, the preprocessor stores a section for each embedded command except:


   BEGIN DECLARE SECTION      OPEN
   BEGIN WORK                 PREPARE
   CLOSE                      RELEASE
   COMMIT WORK                ROLLBACK WORK
   CONNECT                    SAVEPOINT
   DECLARE                    START DBE
   DELETE WHERE CURRENT       STOP DBE
   END DECLARE SECTION        SQLEXPLAIN
   EXECUTE                    TERMINATE USER
   EXECUTE IMMEDIATE          UPDATE WHERE CURRENT
   FETCH                      WHENEVER
   INCLUDE

The commands listed above either require no authorization to execute or are executed based on information contained in the compilable preprocessor output files.

When the preprocessor stores a section, it actually stores what are known as an input tree and a run tree. The input tree consists of an uncompiled command. The run tree is the compiled, executable form of the command.

If at runtime a section is valid, ALLBASE/SQL executes the appropriate run tree when the SQL command is encountered in the application program. If a section is invalid, ALLBASE/SQL determines whether the objects referenced in the sections exist and whether current authorization criteria are satisfied. When an invalid section can be validated, ALLBASE/SQL dynamically recompiles the input tree to create an executable run tree and executes the command. When a section cannot be validated, the command is not executed, and an error condition is returned to the program.

There are three types of sections:

Sections for executing the SELECT command associated with a DECLARE CURSOR command.
Sections for executing the SELECT command associated with a CREATE VIEW command.
Sections for all other commands for which the preprocessor stores a section.

Figure 2-11 “Information in SYSTEM.SECTION on Stored Sections” illustrates the kind of information in the system catalog that describes each type of stored section. The query result illustrated was extracted from the system view named SYSTEM.SECTION by using ISQL. The columns in Figure 2-11 “Information in SYSTEM.SECTION on Stored Sections” have the following meanings:

NAME: This column contains the name of the module to which a section belongs. You specify a module name when you invoke the preprocessor; the module name is by default the program name from the PROGRAM Statement. If you are supplying a module name in a language other than NATIVE-3000 (ASCII), be sure it is in the same language as that of the DBEnvironment.
OWNER: This column identifies the owner of the module. You specify an owner name when you invoke the preprocessor; the owner name is by default the userid associated with the preprocessing session. If you are supplying an owner name in a native language other than NATIVE-3000 (ASCII), be sure it is in the same language as that of the DBEnvironment.
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 identifies the type of section:
- 1 = SELECT associated with a cursor.
- 2 = SELECT defining a view.
- 0 = All other sections.
VALID: This column identifies whether a section is valid or invalid:
- 0 = invalid
- 1 = valid

Figure 2-11 Information in SYSTEM.SECTION on Stored Sections


   isql=> SELECT NAME,OWNER,DBEFILESET,SECTION,TYPE,VALID FROM SYSTEM.SECTION;

   ---------------------------------------------------------------------------
   NAME                |OWNER       |DBEFILESET       |SECTION  |TYPE  |VALID
   --------------------|------------|-----------------|---------|------|------
   TABLE               |SYSTEM      |SYSTEM           |       0 |     2|     0
   COLUMN              |SYSTEM      |SYSTEM           |       0 |     2|     0
   INDEX               |SYSTEM      |SYSTEM           |       0 |     2|     0
   SECTION             |SYSTEM      |SYSTEM           |       0 |     2|     0
   DBEFILESET          |SYSTEM      |SYSTEM           |       0 |     2|     0
   DBEFILE             |SYSTEM      |SYSTEM           |       0 |     2|     0
   SPECAUTH            |SYSTEM      |SYSTEM           |       0 |     2|     0
   TABAUTH             |SYSTEM      |SYSTEM           |       0 |     2|     0
   COLAUTH             |SYSTEM      |SYSTEM           |       0 |     2|     0
   MODAUTH             |SYSTEM      |SYSTEM           |       0 |     2|     0
   GROUP               |SYSTEM      |SYSTEM           |       0 |     2|     0
   VIEWDEF             |SYSTEM      |SYSTEM           |       0 |     2|     0
   HASH                |SYSTEM      |SYSTEM           |       0 |     2|     0
   CONSTRAINT          |SYSTEM      |SYSTEM           |       0 |     2|     0
   CONSTRAINTCOL       |SYSTEM      |SYSTEM           |       0 |     2|     0
   CONSTRAINTINDEX     |SYSTEM      |SYSTEM           |       0 |     2|     0
   COLDEFAULT          |SYSTEM      |SYSTEM           |       0 |     2|     0
   TEMPSPACE           |SYSTEM      |SYSTEM           |       0 |     2|     0
   PARTINFO            |PURCHDB     |SYSTEM           |       0 |     2|     0
   VENDORSTATISTICS    |PURCHDB     |SYSTEM           |       0 |     2|     0
   FOREX2              |JOANN@ACCT  |SYSTEM           |       1 |     0|     1
   FOREX7              |BILL@SOMEACT|SYSTEM           |       1 |     1|     1
   FOREX7              |BILL@SOMEACT|SYSTEM           |       2 |     0|     1
   ---------------------------------------------------------------------------
   Number of rows selected is 16.
   U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]>
   ---------------------------------------------------------------------------

The first 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 “Runtime Dialog of Program forex2”. Another program may contain two sections, one for executing the SELECT command associated with a DECLARE CURSOR command and one for executing a FETCH command.

Stored sections remain in the system catalog until they are deleted with the DROP MODULE command or by invoking the preprocessor with the DROP option:


   isql=> DROP MODULE FOREX2;

   	    or

   : RUN PSQLFOR.PUB.SYS;INFO = "PartsDBE (MODULE (FOREX2) DROP)

Stored sections are marked invalid when:

The UPDATE STATISTICS command is executed.
Tables accessed in the program are dropped, altered, or assigned new owners.
Indexes or DBEFileSets related to tables accessed in the program are changed.
Module owner authorization changes occur that affect the execution of embedded commands.

When an invalid section is validated at run time, the validated section is committed when the program issues a COMMIT WORK command. If a COMMIT WORK command is not executed, ALLBASE/SQL must re-validate the section again the next time the program is executed. For this reason, you should embed COMMIT WORK commands following SELECT commands since COMMIT WORK may be needed to commit a section, even when data is not changed by a program.