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 Figure 2-3 “FORTRAN Preprocessor Input and Output”:

Source File: a file containing the FORTRAN ALLBASE/SQL source code program and/or subprogram unit(s) with embedded SQL commands that access the same DBEnvironment. The .sql suffix in SourceFileName.sql is optional although its use is recommended for file name clarity.
ALLBASE/SQL Message Catalog: the ALLBASE/SQL message catalog, which contains preprocessor messages and ALLBASE/SQL error and warning messages. The fully qualified name for the default message catalog is:
/usr/lib/nls/n-computer/hpsqlcat
For native language users, the name of the catalog is:
/usr/lib/nls/$LANG/hpsqlcat
where $LANG is the name of the current language. If this catalog is not available, ALLBASE/SQL issues a warning and uses the default catalog instead.

Figure 2-3 FORTRAN Preprocessor Input and Output

Figure 2-3 “FORTRAN Preprocessor Input and Output” shows the output file created by the preprocessor. These files are as follows:

Modified Source File: a file containing the preprocessor modified version of the source code. If the ModifiedSourceFileName.f is entered by using the -p option in the options list, the FORTRAN compiler requires that the .f suffix be included. If the ModifiedSourceFileName is not entered in the option list when executing the preprocessor, the default file name syntax is the input source file name (SourceFileName.sql) with the .f appended file extension:
SourceFileName.f
After you use the preprocessor in full preprocessing mode, you see ModifiedSourceFileName.f and the following include file as input files for the FORTRAN compiler, as shown in Figure 2-4 “FORTRAN Compiler Input”.
Include File: an include file containing definitions of variables used by FORTRAN statements the preprocessor inserts into ModifiedSourceFileName.f. The default file name syntax is:
ModifiedSourceFileName.sqlv
ALLBASE/SQL Message File: a file containing the preprocessor banner, ALLBASE/SQL error and warning messages, and other messages. The default file name for this file is:
sqlmsg
Installable File: a file containing a copy of the module created by the preprocessor. The default file name syntax for this file is:
ModifiedSourceFileName.sqlm

When you run the preprocessor in full preprocessing mode, the preprocessor also stores a module in the DBEnvironment accessed by your program. The module is used at run time to execute DBEnvironment operations. If SourceFileName is in a language other than ASCII, the ModifiedSourceFileName and all generated files will have names in the native language and extensions in ASCII.

When you run the preprocessor in full preprocessing mode, also ensure that the DBEnvironment accessed by the program or subprogram unit is available.

Figure 2-4 FORTRAN Compiler Input

Preprocessor 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 ModifiedSourceFileName.sqlv 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:

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

As the interactive sample dialog in Figure 2-5 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 ../sampledb/PartsDBE

  BEGIN WORK

  SELECT specified row from the Parts table until user enters a "/"

  COMMIT WORK

  RELEASE ../sampledb/PartsDBE



CONNECT TO ../sampledb/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 ../sampledb/PartsDBE

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 (*,*) '  BEGIN WORK'

      WRITE (*,*) '  SELECT specified row from the Parts table until use

     1r enters a "/"'

      WRITE (*,*) '  COMMIT WORK'

      WRITE (*,*) '  RELEASE ../sampledb/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 ../sampledb/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 ../sampledb/PartsDBE'

      EXEC SQL CONNECT TO '../sampledb/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

C

      WRITE (*,*) 'COMMIT WORK'

C

C



      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 ../sampledb/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 ../sampledb/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             (* 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)

     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:

 $ ll forex2*

   -r--r--r--   1 bill     dbsupport    8974 Mar 10 12:41 forex2

   -rwxrwxr-x   1 bill     dbsupport   17275 Mar 10 12:41 forex2.f

   -rwxrwxr-x   1 bill     dbsupport    1500 Mar 10 12:41 forex2.sqlm

   -rwxrwxr-x   1 bill     dbsupport     210 Mar 10 12:41 forex2.sqlv



 $ ll sqlmsg

   -rwxrwxr-x   1 bill     dbsupport     405 Mar 10 12:41 sqlmsg



 $

Preprocessor 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 show the start of original code commented out, the preprocessor-generated code added, and some other key statements.

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 one include FORTRAN compiler directive after the Type Declaration Section. This directive references the preprocessor generated include file during compilation: ModifiedSourceFileName.sqlv.
Inserts a "Start SQL Preprocessor" comment before, and an "End SQL Preprocessor" comment after code that it modifies.
Places any comment you placed after an embedded command on the line following the last line generated for the embedded command. Note, for example, that the comment following the INCLUDE SQLCA command in the source file (refer to Figure 2-6 “Program forex2”) is in the same column, but on a different line in the modified source file (refer to Figure 2-7 “Modified Source File for Program forex2”).

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.

The following modified source file is the result of preprocessing program forex2 (shown previously).

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)

      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   ****

           CHARACTER           Done

           CHARACTER           Abort

           INTEGER             MultipleRows

           INTEGER             Deadlock

           CHARACTER*16        Response

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         

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            (* Beginning of the Main Program *)

C         

C**** End SQL Preprocessor   ****



      INCLUDE 'forex2.sqlv'

      WRITE (*,*) CHAR(27), 'U'

      WRITE (*,*) 'Program to SELECT specified rows from the Parts Table

     1 -- forex2'

      WRITE (*,*) ' '

      WRITE (*,*) 'Event List:'

      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 ../sampledb/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 ../sampledb/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 'forex2.sqlv'

C**** Start SQL Preprocessor ****

C     EXEC SQL WHENEVER SQLERROR GOTO 500

C

C**** Start Inserted Statements ****

C**** End SQL Preprocessor   ****

      WRITE (*,*) ' '

      WRITE (*,*) 'CONNECT TO ../sampledb/PartsDBE'

C**** Start SQL Preprocessor ****

C     EXEC SQL CONNECT TO '../sampledb/PartsDBE'

C**** Start Inserted Statements ****

      CALL SQLXCO(SQLCAID,264,'00AE00002E2E2F73616D706C6564622F506172747

 1344424520202020202020202020202020202020202020202020202020202020202

 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

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            (* Begin Host Variable Declarations *)

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 'forex2.sqlv'

C**** Start SQL Preprocessor ****

C     EXEC SQL WHENEVER SQLERROR GOTO 500

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

C

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

C**** Start SQL Preprocessor ****

C     EXEC SQL INCLUDE SQLCA

C            (* Begin SQL Communication Area *)

C            (* Begin Host Variable Declarations *)

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**** End SQL Preprocessor   ****

      INCLUDE 'forex2.sqlv'

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 ../sampledb/PartsDBE *)

C**** Start SQL Preprocessor ****

C     EXEC SQL INCLUDE SQLCA

C            (* Begin SQL Communication Area *)

C            (* Begin Host Variable Declarations *)

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**** End SQL Preprocessor   ****

      INCLUDE 'forex2.sqlv'

C**** Start SQL Preprocessor ****

C     EXEC SQL WHENEVER SQLERROR GOTO 500

C**** Start Inserted Statements ****

C**** End SQL Preprocessor   ****

      WRITE (*,*) 'RELEASE ../sampledb/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**** End SQL Preprocessor   ****

      INCLUDE 'forex2.sqlv'

      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 'forex2.sqlv'

      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**** 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**** End SQL Preprocessor   ****

      INCLUDE 'forex2.sqlv'

      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)

            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

ModifiedSourceFileName.sqlv, the preprocessor-creator include file, contains delcarations for variables referenced in preprocessor-generated statements in the modified source file. Figure 2-8 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 'forex2.sqlv'

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

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 /'BILL                '/

        DATA SQLMDN /'FOREX2              '/

  C

ALLBASE/SQL Message File

Messages placed in sqlmsg come from the ALLBASE/SQL message catalog. The default catalog is /usr/lib/nls/n-computer/hpsqlcat. For native language users, the name of the catalog is /usr/lib/nls/$LANG/hpsqlcat, where $LANG is the current language. If this catalog is not available, ALLBASE/SQL uses the default instead.

Sqlmsg messages contain four parts:

A banner:

                               MON, JUL 10, 1991,  4:48 PM

HP36217-02A.E1.00   FORTRAN preprocessor/9000  ALLBASE/SQL

(C) COPYRIGHT HEWLETT-PACKARD CO.  1982,1983,1984,1985, 

1986,1987,1988,1989,1990,1991.  ALL RIGHTS RESERVED.

300/400:

                   MON, JUL 10, 1991,  4:48 PM

HP79725A.E1.00    FORTRAN preprocessor/300    ALLBASE/SQL 

(C) COPYRIGHT HEWLETT-PACKARD CO.  1982,1983,

1984,1985,1986,1987,1988,1989,1990,1991.  ALL RIGHTS RESERVED.

A summary of the preprocessor invocation conditions:

    DBEnvironment       = ../sampledb/PartsDBE



    Module Name         = FOREX2

Warnings and errors encountered during preprocessing:

 SELECT PartNumber, PartName, SalesPrice INTO :PartNumber,

 :PartName, :SalesPrice, :SalesPriceIND FROM PurchDB.Parts

 WHERE PartNumber = :PartNumber;

      

 ******   ALLBASE/SQL errors  (DBERR 10977)

 ******   in SQL statement ending in line 133

 ***      Syntax error.   (DBERR 1001)



 There are errors.  No sections stored.  (DBERR 10114)

A summary of the results of preprocessing:

      1 ERRORS   0 WARNINGS

     END OF PREPROCESSING.

By default, both the banner and the summary of preprocessing output is echoed to the standard output, the terminal, by default. In addition, all sqlmsg messages are written to the file named sqlmsg.

As illustrated om Figure 2-9, a line number is often provided in sqlmsg after the error or warning message. This line number references the last line in the 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 Errors

$ more sqlmsg

                                MON, JUL 10, 1991,  4:48 PM

HP36217-02A.E1.00   FORTRAN Preprocessor/9000   ALLBASE/SQL 

(C) COPYRIGHT HEWLETT-PACKARD CO.  1982,1983,1984,1985,1986,

1987,1988,1989,1990,1991.  ALL RIGHTS RESERVED.



DBEnvironment         = ../sampledb/PartsDBE



Module Name           = FOREX2



 SELECT PartNumber, PartName, SalesPrice INTO :PartNumber, :PartName

 :SalesPrice :SalesPriceInd FROM PurchDB.Parts WHERE PartNumber =

 :PartNumber;



******  ALLBASE/SQL errors (DBERR 10977)

******  in SQL statement ending in line 133

***     Syntax error.  (DBERR 1001)



There are errors.  No sections stored.  (DBERR 10114)



 1 ERRORS   0 WARNINGS

END OF PREPROCESSING.



$

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

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

This happens 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 syntactically correct but semantically incorrent command, the section is marked as invalid and will not execute at run time if it cannot be validated. Refer to the ALLBASE/SQL Reference Manual for a description of how to delete a stored section.

Figure 2-10 Sample sqlmsg Showing Warning








$ more sqlmsg



                                MON, JUL 10, 1991,  4:48 PM

HP36217-02A.E1.00   FORTRAN Preprocessor/9000   ALLBASE/SQL 

(C) COPYRIGHT HEWLETT-PACKARD CO.  1982,1983,1984,1985,1986,

1987,1988,1989,1990,1991.  ALL RIGHTS RESERVED.



DBEnvironment        = PartsDBE



Module Name          = FOREX2



   SELECT PARNUMBER, PartName, SalesPrice INTO :PartNumber, :PartName

   :SalesPrice :SalesPriceInd FROM PurchDB.Parts WHERE PartNumber =

   :PartNumber;



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

****** in SQL statement ending in line 133

***    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 by default is ModifiedSourceFileName.sqlm. If at preprocessing time ModifiedSourceFileName.sqlm already exists. it is overwritten with the new module. 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:

   $ psqlfor DBEnvironmentName -i SourceFileName.sql -d

If you want to preserve the SourceModifiedFileName.sqlm file after preprocessing, you must rename ModifiedSourceFileName.sqlm so it is not over written the next time the preprocessor is invoked to preprocess the same source code:

   $ mv ModifiedSourceFileName.sqlm mymod.sqlmod

Before invoking ISQL to install the module contained in ModifiedSourceFileName.sqlm, 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 '../sampledb/PartsDBE';

   isql=> INSTALL;



   File name> mymod.sqlmod;

   Name of module in this file:  BILL.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             INCLUDE

   BEGIN WORK                        OPEN

   CLOSE                             PREPARE

   COMMIT WORK                       RELEASE

   CONNECT                           ROLLBACK WORK

   DECLARE CURSOR                    SAVEPOINT

   DELETE WHERE CURRENT              START DBE

   DESCRIBE                          STOP DBE

   END DECLARE SECTION               SQLEXPLAIN

   EXECUTE                           TERMINATE USER

   EXECUTE IMMEDIATE                 UPDATE WHERE CURRENT

                                     WHENEVER

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 n-computer (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 n-computer (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

FEXP01D2         |BILL     |SYSTEM     |      1 |    0|    1

FEXP11           |BILL     |SYSTEM     |      1 |    1|    1

FEXP11           |BILL     |SYSTEM     |      2 |    0|    1

The first eighteen 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. FEXP01D2 contains only one section, for executing the SELECT command in the program illustrated in Figure 2-6 “Program forex2”. FEXP11 contains 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



   $ psqlfor ../sampledb/PartsDBE -m forex -i forex2.sql -d

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.