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.