Preprocessor Input and Output

Regardless of the mode you use, the following files must be available when you invoke the Pascal preprocessor, as shown in Figure 2-3 “Pascal Preprocessor Input and Output”:

Source file: A file containing the source code of the Pascal ALLBASE/SQL program with embedded SQL commands for the DBEnvironments used. The name is specified by using the -i option as explained later in this chapter.
ALLBASE/SQL Message Catalog: The ALLBASE/SQL message catalog, which contains preprocessor messages and ALLBASE/SQL error and warning messages. The message catalog can actually be accessed through two different pathnames which each point to the same file. The fully qualified names that point to the default catalog are:
/usr/lib/hpsqlcat or /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.

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

ALLBASE/SQL Message File: File containing the preprocessor banner, error and warning messages, and other messages. The file name for this file is as follows:
sqlmsg
Installable module file: File containing a copy of the module created by the preprocessor. The file name syntax for this file is as follows:
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.
As Figure 2-4 “Compiling Pascal Preprocessor Output” points out, the Pascal preprocessor creates the following output files:
Modified source file: File containing the modified version of the source code in the source file. The default filename syntax for this file is as follows:
ModifiedSourceFileName.p
An alternative name can be specified by using the -p option as described later in this chapter.
Include files: After you use the preprocessor in full preprocessing mode, you use the modified source code file and the following four include files as input files for the Pascal compiler, as shown in Figure 2-4 “Compiling Pascal Preprocessor Output”. These include files contain declarations and definitions used by Pascal constructs the preprocessor inserts into the modified source code file. The syntax is as follows:
- Constant include file: File containing constant definitions, is as follows:
  ModifiedSourceFileName.sqlc
- Type include file: File containing type declarations, is as follows:
  ModifiedSourceFileName.sqlt
- Variable include file: File containing variable declarations, is as follows:
  ModifiedSourceFileName.sqlv (or ModifiedSourceFileName.svnn)
- External include file: File containing external procedure declarations, is as follows:
  ModifiedSourceFileName.sqle
  Any extension on the ModifiedSourceFileName is ignored when the preprocessor assigns names to the include files.

If SourceFileName is in a language other than n-computer (ASCII), the ModifiedSourceFileName and all generated files will have names in the native language and extensions in ASCII.

Figure 2-3 Pascal Preprocessor Input and Output

Figure 2-4 Compiling Pascal Preprocessor Output

Source File

The source file must be a file that contains at a minimum the following constructs:

   (* PROGRAM HEADING *)

   Program  ProgramName(input, output);



   begin

   AnyStatement;

   end.

When parsing the source file, the Pascal preprocessor ignores Pascal statements and most Pascal compiler directives in it. Only the following information is parsed by the Pascal preprocessor:

The Pascal compiler directives $Skip_Text ON$, $Skip_Text OFF$, $Set, $If, $Else, $Endif, and $Include.
NOTE: 300/400 Series 300 and 400 systems comment out SQL commands, rather than using a $Skip_text compiler directive.
The program name. Unless you specify a module name in the preprocessor invocation line, the preprocessor uses the program name as the name for the module it stores. The name may optionally have the suffix .sql to distinguish it from non-SQL programs. 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 ).
Constructs found between the prefix EXEC SQL and the suffix ;. These constructs follow the rules given in Chapter 3 for how and where to embed these constructs.
Constructs found between the BEGIN DECLARE SECTION and END DECLARE SECTION commands. These commands delimit a declare section, which contains Pascal data declarations for the host variables used in the program. Host variables are described in the chapter, "Host Variables."

Figure 2-6 “Program pasex2: Using Simple Select ” illustrates a source file containing a sample program using the following SQL commands, highlighted in shading in Figure 2-7 “Sample Modified Source File ”:

   INCLUDE SQLCA

   BEGIN DECLARE SECTION

   END DECLARE SECTION

   WHENEVER

   CONNECT

   BEGIN WORK

   SELECT

   COMMIT WORK

   SQLEXPLAIN

As the sample dialog in Figure 2-5 “Runtime Dialog of Program pasex2” 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 with the exception of explicit error checking after the SELECT command. The program continues to prompt for a part number until a serious error is encountered or until the user enters a slash (/).

Figure 2-5 Runtime Dialog of Program pasex2



Program to SELECT specified rows from the Parts Table - PASEX2



Event List:

 Connect to PartsDBE

 Begin Work

 SELECT specified row from Parts Table

  until user enters "/"

 Commit Work

 Disconnect from PartsDBE



Connect to PartsDBE



Enter Part Number within Parts Table or "/" to STOP>

1243-P-01



Begin Work

SELECT PartNumber, PartName, SalesPrice



Row not found!



Commit Work



Enter Part Number within 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



Commit Work



Enter Part Number within Parts Table or "/" to STOP> /



Release PartsDBE



Terminating Program

Figure 2-6 Program pasex2: Using Simple Select

(* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *)

(* This program illustrates the use of SQL's SELECT command to     *)

(* retrieve one row or tuple at a time.                            *)

(* BEGIN WORK is executed before the SELECT and a COMMIT WORK      *)

(* after the SELECT.  An indicator variable is also used for       *)

(* SalesPrice.                                                     *)

(* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *)



Program pasex2(input, output);



label

    1000,

    9999;



const

    OK           =      0;

    NotFound     =    100;

    MultipleRows = -10002;

    DeadLock     = -14024;



var



    EXEC SQL INCLUDE SQLCA;



	  (* Begin Host Variable Declarations *)

    EXEC SQL  Begin Declare Section;

    PartNumber       : packed array[1..16] of char;

    PartName         : packed array[1..30] of char;

    SalesPrice       : longreal;

    SalesPriceInd    : SQLIND;

    SQLMessage       : packed array[1..132] of char;

    EXEC SQL  End Declare Section;

	  (* End Host Variable Declarations *)



    Abort             : boolean;



procedure SQLStatusCheck;  (* Procedure to Display Error Messages *)

   Forward;



$PAGE $



(* Directive to set SQL Whenever error checking *)

EXEC SQL Whenever SQLError GOTO 1000;




Procedure ConnectDBE;  (* Procedure to Connect to PartsDBE *)

begin



writeln('Connect to PartsDBE');

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



end;  (* End of ConnectDBE Procedure *)





Procedure BeginTransaction;  (* Procedure to Begin Work *)

begin



writeln;

writeln('Begin Work');

EXEC SQL BEGIN WORK;



end;  (* End BeginTransaction Procedure *)





procedure EndTransaction;  (* Procedure to Commit Work *)

begin



writeln;

writeln('Commit Work');

EXEC SQL COMMIT WORK;



end;  (* End EndTransaction Procedure *)





(* Directive to reset SQL Whenever error checking *)

EXEC SQL WHENEVER SQLERROR CONTINUE;





procedure TerminateProgram;   (* Procedure to Release PartsDBE *)

begin



writeln('Release PartsDBE');

EXEC SQL COMMIT WORK RELEASE;



writeln;

writeln('Terminating Program');

Goto 9999;  (* Goto exit point of main program *)



end;  (* End TerminateProgram Procedure *)

$PAGE $






procedure DisplayRow;   (* Procedure to Display Parts Table Rows *)

begin



writeln;

writeln('Part Number: ', PartNumber);

writeln('Part Name:   ', PartName);

if SalesPriceInd < 0 then

   writeln('Sales Price is NULL')

else

   writeln('Sales Price: ', SalesPrice:10:2);



end;  (* End of DisplayRow *)

$PAGE $



procedure SelectData; (* Procedure to Query Parts Table *)

begin



repeat



writeln;

prompt('Enter Part Number within Parts Table or "/" to STOP> ');

readln(PartNumber);

writeln;



if PartNumber[1] <> '/' then

  begin



  BeginTransaction;



  writeln('SELECT PartNumber, PartName, SalesPrice');

  EXEC SQL SELECT PartNumber, PartName, SalesPrice

	    INTO :PartNumber,

		 :PartName,

		 :SalesPrice  :SalesPriceInd

	     FROM PurchDB.Parts

	    WHERE PartNumber = :PartNumber;



  if SQLCA.SQLWARN[0] in ['W','w'] then

     begin

     write('SQL WARNING has occurred. The following row');

     writeln('of data may not be valid.');

     end;










  case SQLCA.SQLCODE of

  OK           : DisplayRow;

  NotFound     : begin

		   writeln;

		   writeln('Row not found!');

		 end;

  MultipleRows : begin

		   writeln;

		   writeln('WARNING: More than one row qualifies.');

		 end;

  otherwise      begin

		   SQLStatusCheck;

		   end;



  end;  (* case *)



  EndTransaction;



end;  (* End if *)

until PartNumber[1] = '/';



end;      (* End of SelectData Procedure *)



procedure SQLStatusCheck;  (* Procedure to Display Error Messages *)

begin



Abort := FALSE;

if SQLCA.SQLCODE < DeadLock then Abort := TRUE;



repeat

EXEC SQL SQLEXPLAIN :SQLMessage;

writeln(SQLMessage);

until SQLCA.SQLCODE = 0;



if Abort then

  begin



  TerminateProgram;



  end;



end;  (* End SQLStatusCheck Procedure *)

$PAGE $








begin  (* Beginning of Program *)



write('Program to SELECT specified rows from ');

writeln('the Parts Table - pasex2');

writeln;

writeln('Event List:');

writeln('  Connect to PartsDBE');

writeln('  Begin Work');

writeln('  SELECT specified row from Parts Table');

writeln('   until user enters "/" ');

writeln('  Commit Work');

writeln('  Disconnect from PartsDBE');

writeln;



ConnectDBE;

SelectData;

TerminateProgram;



(* Whenever Routine - Serious DBE Error *)

(* SQL Whenever SQLError Entry Point *)

1000:



  (* Begin *)

  SQLStatusCheck;

  TerminateProgram;

  (* End *)



(* Exit Point for the main program *)

9999:



end.   (* End of Program *)

Output File Attributes

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

   $ ll pasex2*



     -r--r--r--   1 thomas     dbsupport   4714 Sep 16 11:25 pasex2

     -rwxrwxr-x   1 thomas     dbsupport   6450 Sep 18 11:02 pasex2.p

     -rwxrwxr-x   1 thomas     dbsupport    149 Sep 18 11:02 pasex2.sqlc

     -rwxrwxr-x   1 thomas     dbsupport   3504 Sep 18 11:02 pasex2.sqle

     -rwxrwxr-x   1 thomas     dbsupport   1500 Sep 18 11:02 pasex2.sqlm

     -rwxrwxr-x   1 thomas     dbsupport   1536 Sep 18 11:02 pasex2.sqlt

     -rwxrwxr-x   1 thomas     dbsupport    156 Sep 18 11:02 pasex2.sqlv



   $ ll sqlmsg



     -rwxrwxr-x   1 thomas     dbsupport    403 Sep 18 11:02 sqlmsg

Modified Source File

As the Pascal preprocessor parses the source file, it copies lines from it and any include file(s) into the modified source file, comments out embedded SQL commands, and inserts information around each embedded SQL command. Figures 2-7 and 2-8 illustrate the modified source file generated for the source file pictured in Figure 2-6. The shaded lines highlight areas of original SQL commands commented out and boundaries of begin blocks of preprocessor-generated code.

In both preprocessing modes (which are discussed later in this chapter), the Pascal preprocessor inserts a $Skip_Text ON$ and $Skip_Text OFF$ compiler directive around the embedded SQL command to comment out the SQL command.




	NOTE: 300/400 In Series 300 and 400 systems, it comments out all SQL commands and delimits any comments within SQL commands with the braces characters ( { and } ).

Inserts four include Pascal compiler directives within the declaration part. These directives reference the four preprocessor-generated include files. The constant and type include files are included after the program header. The variable and externals include files are included at the end of the global declaration part.
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 is in the same column, but on a different line, in the modified source file.

NOTE: 300/400 To ensure portability between Series 300 or 400 and 700 or 800 systems, the Series 300 and 400 Pascal preprocessors additionally insert the following three compiler directives at the beginning of the program:

   $SYSPROG ON$

   $LONGSTRINGS ON$

   $UNDERSCORE ON$

If you are preprocessing code written for a previous version of Series 300 SQL, the following information may need to be considered.

The $UNDERSCORE ON$ directive was not available in previous compiler versions. Thus, $ALIAS$ references to the symbol table which were coded with an underscore as the first character are preceded by two underscores after preprocessing, causing an unresolved symbol table error at link time. To avoid this problem, remove the original underscore in the $ALIAS$ directive.

Use of the $LONGSTRINGS ON$ directive requires that all linked code use the same directive. If any of your programs that are to be linked together use the default string size, the program should be changed to include the $LONGSTRINGS ON$ directive.

In full preprocessing mode, the preprocessor also generates the following:

Pascal declaration of the SQLCA and the SQLDA in the type include file.
Pascal statements providing conditional instructions following SQL commands encountered after one of the following SQL commands: WHENEVER SQLERROR, WHENEVER SQLWARNING, and WHENEVER NOT FOUND.
Pascal statements that call ALLBASE/SQL external procedures at runtime. These calls reference the module stored by the preprocessor in the DBEnvironment for execution at run time. Parameters used by these external calls are defined in the variable, constant, type, and external include files.




	CAUTION: Although you can access the preprocessor output files with an editor, you should `never` change the information generated by the Pascal preprocessor. Your DBEnvironment could be damaged at run time if preprocessor-generated constructs are altered.

If you need to change nonpreprocessor-generated constructs in the modified source file, make the changes to the source file, preprocess it again, and re-compile the output files before putting the application program into production.

Figure 2-7 Sample Modified Source File

$set 'XOPEN_SQLCA=false'$

(* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *)

(* This program illustrates the use of SQL's SELECT command to     *)

(* retrieve one row or tuple at a time.                            *)

(* BEGIN WORK is executed before the                               *)

(* SELECT and a COMMIT WORK after the SELECT.  An indicator        *)

(* variable is also used for SalesPrice.                           *)

(* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *)



Program pasex2(input, output);



label

    1000,

    9999;



$include 'pasex2.sqlc'$ 

$include 'pasex2.sqlt'$

const

    OK           =      0;

    NotFound     =    100;

    MultipleRows = -10002;

    DeadLock     = -14024;



var



$SKIP_TEXT ON$

    EXEC SQL INCLUDE SQLCA;

$SKIP_TEXT OFF$

SQLCA : SQLCA_TYPE;



          (* Begin Host Variable Declarations *)

    

$SKIP_TEXT ON$

    EXEC SQL  Begin Declare Section;

$SKIP_TEXT OFF$



    PartNumber       : packed array[1..16] of char;

    PartName         : packed array[1..30] of char;

    SalesPrice       : longreal;

    SalesPriceInd    : SQLIND;

    SQLMessage       : packed array[1..132] of char;

    

$SKIP_TEXT ON$

    EXEC SQL  End Declare Section;

$SKIP_TEXT OFF$



          (* End Host Variable Declarations *)




    Abort             : boolean;



$include 'pasex2.sqlv'$ 

$include 'pasex2.sqle'$ 

procedure SQLStatusCheck;  (* Procedure to Display Error Messages *)

   Forward;



$PAGE $



(* Directive to set SQL Whenever error checking *)





$SKIP_TEXT ON$

EXEC SQL Whenever SqlError goto 1000;

$SKIP_TEXT OFF$



Procedure ConnectDBE;  (* Procedure to Connect to PartsDBE *)

begin



writeln('Connect to PartsDBE');



$SKIP_TEXT ON$

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

$SKIP_TEXT OFF$

begin

SQLVAR1 := '00AE00002E2E2F73616D706C6564622F5061727473444245202020202020' + 

         '202020202020202020202020202020202020202020202020202020202020' + 

         '202020202020202020202020202020202020202020202020202020202020' + 

         '202020202020202020202020202020202020202020202020202020202020' + 

         '202020202020202020202020'; 

SQLXCON(waddress(SQLCA), SQLVAR1);

if SQLCA.SQLCODE < 0 then

  goto 1000;

end;



end;  (* End of ConnectDBE Procedure *)



Procedure BeginTransaction;  (* Procedure to Begin Work *)

begin



writeln;

writeln('Begin Work');



$SKIP_TEXT ON$

EXEC SQL BEGIN WORK;

$SKIP_TEXT OFF$


begin

SQLVAR2 := '00A6007F00110061';

SQLXCON(waddress(SQLCA), SQLVAR2);

if SQLCA.SQLCODE < 0 then

  goto 1000;

end;

end;  (* End BeginTransaction Procedure *)



procedure EndTransaction;  (* Procedure to Commit Work *)

begin

writeln;

writeln('Commit Work');



$SKIP_TEXT ON$

EXEC SQL COMMIT WORK;

$SKIP_TEXT OFF$

begin

SQLVAR3 := '00A10000';

SQLXCON(waddress(SQLCA), SQLVAR3);

if SQLCA.SQLCODE < 0 then

  goto 1000;

end;



end;  (* End EndTransaction Procedure *)



(* Directive to reset SQL Whenever error checking *)



$SKIP_TEXT ON$

EXEC SQL WHENEVER SQLERROR CONTINUE;

$SKIP_TEXT OFF$



procedure TerminateProgram;   (* Procedure to Release PartsDBE *)

begin

writeln('Release PartsDBE');



$SKIP_TEXT ON$

EXEC SQL COMMIT WORK RELEASE;

$SKIP_TEXT OFF$

begin

begin

SQLVAR4 := '00A10000';

SQLXCON(waddress(SQLCA), SQLVAR4);

end;

begin

SQLVAR5 := '00B200002020202020202020202020202020202020202020FFFFFFFF'; 

SQLXCON(waddress(SQLCA), SQLVAR5);




end;

end;



writeln;

writeln('Terminating Program');

Goto 9999;  (* Goto exit point of main program *)



end;  (* End TerminateProgram Procedure *)

$PAGE $



procedure DisplayRow;   (* Procedure to Display Parts Table Rows *)

begin



writeln;

writeln('Part Number: ', PartNumber);

writeln('Part Name:   ', PartName);

if SalesPriceInd < 0 then

   writeln('Sales Price is NULL')

else

   writeln('Sales Price: ', SalesPrice:10:2);



end;  (* End of DisplayRow *)

$PAGE $



procedure SelectData; (* Procedure to Query Parts Table *)

begin



repeat



writeln;

prompt('Enter Part Number within Parts Table or "/" to STOP> ');

readln(PartNumber);

writeln;



if PartNumber[1] <> '/' then

  begin

  BeginTransaction;



  writeln('SELECT PartNumber, PartName, SalesPrice');

  

$SKIP_TEXT ON$

  EXEC SQL SELECT PartNumber, PartName, SalesPrice

            INTO :PartNumber,

                 :PartName,

                 :SalesPrice  :SalesPriceInd

             FROM PurchDB.Parts

            WHERE PartNumber = :PartNumber;

$SKIP_TEXT OFF$




begin

SQLTEMPV.REC1.PartNumber1 := PartNumber;

SQLXFET(waddress(SQLCA),SQLOWNER,SQLMODNAME,1,waddress(SQLTEMPV),

        16,64,TRUE);

if SQLCA.SQLCODE = 0 then

  begin

  PartNumber := SQLTEMPV.REC2.PartNumber1;

  PartName := SQLTEMPV.REC2.PartName2;

  if SQLTEMPV.REC2.SalesPriceInd4 >= 0 then

  SalesPrice := SQLTEMPV.REC2.SalesPrice3;

  SalesPriceInd := SQLTEMPV.REC2.SalesPriceInd4;

  end

else

  begin

  end;

end;



  if SQLCA.SQLWARN[0] in ['W','w'] then

     begin

     write('SQL WARNING has occured. The following row');

     writeln('of data may not be valid.');

     end;



  case SQLCA.SQLCODE of

  OK           : DisplayRow;

  NotFound     : begin

                   writeln;

                   writeln('Row not found!');

                 end;

  MultipleRows : begin

                   writeln;

                   writeln('WARNING: More than one row qualifies.');

                 end;

  otherwise      begin

                   SQLStatusCheck;

                   end;

  end;  (* case *)

  EndTransaction;



end;  (* End if *)

until PartNumber[1] = '/';

end;      (* End of SelectData Procedure *)



procedure SQLStatusCheck;  (* Procedure to Display Error Messages *)

begin



Abort := FALSE;

if SQLCA.SQLCODE < DeadLock then Abort := TRUE;




repeat



$SKIP_TEXT ON$

EXEC SQL SQLEXPLAIN :SQLMessage;

$SKIP_TEXT OFF$

begin

SQLXPLN(waddress(SQLCA),waddress(SQLTEMPV.REC4),132,0);

SQLMessage := '';

strmove(132,SQLTEMPV.REC4,1,SQLMessage, 1);

end;



writeln(SQLMessage);

until SQLCA.SQLCODE = 0;



if Abort then

  begin

  TerminateProgram;

  end;

end;  (* End SQLStatusCheck Procedure *)

$PAGE $



begin  (* Beginning of Program *)



write('Program to SELECT specified rows from ');

writeln('the Parts Table - PASEX2');

writeln;

writeln('Event List:');

writeln('  Connect to PartsDBE');

writeln('  Begin Work');

writeln('  SELECT specified row from Parts Table');

writeln('   until user enters "/" ');

writeln('  Commit Work');

writeln('  Disconnect from PartsDBE');

writeln;

ConnectDBE;

SelectData;

TerminateProgram;



(* Whenever Routine - Serious DBE Error *)

(* SQL Whenever SQLError Entry Point *)

1000:

  (* Begin *)

  SQLStatusCheck;

  TerminateProgram;

  (* End *)

(* Exit Point for the main program *)

9999:

end.   (* End of Program *)

Preprocessor-Generated Include Files

The preprocessor creates a set of include files (with extensions .sqlc, .sqlt, .sqlv, .svnn, and .sqle) for the constant, type, variable, and external declarations in your application. In .svnn, nn are two numerical digits. Figure 2-8 “Sample Constant Include File” through Figure 2-11 “Sample Externals Include Files” illustrate the generated files that correspond to the modified source files in Figure 2-7 “Sample Modified Source File ”. The constant file is specific to the user and application.

The preprocessor inserts four Pascal compiler directives in the following syntax to reference the include files.

   $include 'ModifiedSourceFileName.sqlc'$

   $include 'ModifiedSourceFileName.sqlt'$

   $include 'ModifiedSourceFileName.sqlv'$

   $include 'ModifiedSourceFileName.sqle'$

These four directives are always inserted into the global declaration section of a main program. For each declare section in a subprogram, an include file of ModifiedSourceFileName.svnn syntax is generated, and the compiler directive $include ModifiedSourceFileName.svnn$ is inserted in the local declaration section. The value of nn is from 01 through 99.

Figure 2-8 Sample Constant Include File

const

  SQLOWNER   = 'THOMAS@DARBY        ';

  SQLMODNAME = 'PASEX2              ';

Figure 2-9 Sample Type Include File

type

  ownername_type  = string[20];

  modulename_type = string[20];

  smallint = shortint;

  SQLIND = shortint;

  SQLREC1 = record

    PartNumber1 : packed array [1..16] of char;

    end;

  SQLREC2 = record

    PartNumber1 : packed array [1..16] of char;

    PartName2 : packed array [1..30] of char;

    SalesPrice3 : longreal;

    SalesPriceInd4 : sqlind;

    end;

  SQLREC3 = record

    DUMMY1, DUMMY2             : SQLREC2

    end;

  SQLREC4 = packed array[1..132] of char;

  SQLCASES = 0..4;

  SQLCA_TYPE = record

    SQLCAID : packed array [1..8] of char;

    SQLCABC : integer;

    SQLCODE : integer;

    SQLERRM : string[255];

    SQLERRP : packed array [1..8] of char;

    SQLERRD : array [1..6] of integer;

$if 'XOPEN_SQLCA'$

    SQLWARN0, SQLWARN1, SQLWARN2,

    SQLWARN3, SQLWARN4, SQLWARN5,

    SQLWARN6, SQLWARN7 : char;

$else$

    SQLWARN : packed array [0..7] of char;

$endif$

    SQLEXT  : packed array [1..8] of char;

    end;

  SQLFORMAT_TYPE = packed record

    SQLNTY, SQLTYPE, SQLPREC, SQLSCALE : smallint;

    SQLTOTALLEN, SQLVALLEN, SQLINDLEN  : integer;

    SQLVOF, SQLNOF : integer;

    SQLNAME : packed array [1..20] of char;

    end;

  SQLDA_TYPE = record

    SQLDAID   : packed array [1..8] of char;

    SQLDABC   : integer;

    SQLN      : integer;

    SQLD      : integer;




    SQLFMTARR : integer;

    SQLNROW   : integer;

    SQLRROW   : integer;

    SQLROWLEN : integer;

    SQLBUFLEN : integer;

    SQLROWBUF : integer;

    end;

  SQLTEMPV_TYPE_P = @SQLTEMPV_TYPE;

  SQLTEMPV_TYPE = record case SQLCASES of

           0 : (dummy : integer);

           1: (REC1 : SQLREC1);

           2: (REC2 : SQLREC2);

           3: (REC3 : SQLREC3);

           4: (REC4 : SQLREC4);

    end;

Figure 2-10 Sample Variable Include File

var

  SQLVAR1  : string[264];

  SQLVAR2  : string[16];

  SQLVAR3  : string[8];

  SQLVAR4  : string[8];

  SQLVAR5  : string[56];

  SQLTEMPV : SQLTEMPV_TYPE;

Figure 2-11 Sample Externals Include Files

procedure SQLXBFE

    (SQLCAP      : integer;

     owner       : ownername_type;

     xmodule     : modulename_type;

     section     : integer;

     parms       : integer;

     inparms     : integer;

     outarray    : integer;

     entrysize   : integer;

     nentry      : integer;

     firstrow    : integer;

     nrow        : integer); external;

procedure SQLXBIN

    (SQLCAP      : integer;

     owner       : ownername_type;

     xmodule     : modulename_type;

     section     : integer;

     inarray     : integer;

     entrysize   : integer;

     nentry      : integer;

     firstrow    : integer;

     nrow        : integer); external;

procedure SQLXCNH

    (SQLCAP      : integer;

     msgstrp     : integer;

     xstrlen     : integer;

     isvarchar   : integer); external;

procedure SQLXCON

    (SQLCAP      : integer;

     var stmt    : string);  external;

procedure SQLXDDU

    (SQLCAP      : integer;

     xmodule     : modulename_type;

     section     : integer;

     parms       : integer;

     inparms     : integer;

     var stmt    : string);  external;

procedure SQLXDEX

    (SQLCAP      : integer;

     SQLDAP      : integer;

     xmodule     : modulename_type;

     section     : integer); external;

procedure SQLXDFE

    (SQLCAP      : integer;

     SQLDAP      : integer;

     xmodule     : modulename_type;

     section     : integer); external;



     

     


procedure SQLXDOPK

    (SQLCAP      : integer;

     SQLDAP      : integer;

     owner       : ownername_type;

     xmodule     : modulename_type;

     section     : integer;

     parms       : integer;

     inparms     : integer;

     kpcval      : integer); external;

procedure SQLXDSB

    (SQLCAP      : integer;

     SQLDAP      : integer;

     xmodule     : modulename_type;

     section     : integer;

     ifinput     : integer); external;

procedure SQLXEXI

    (SQLCAP      : integer;

     queryptr    : integer;

     querysize   : integer); external;

procedure SQLXEXU

    (SQLCAP      : integer;

     xmodule     : modulename_type;

     section     : integer;

     parms       : integer;

     inparms     : integer;

     var formats : string;

     nhv         : integer;

     nentry      : integer;

     firstrow    : integer;

     nrow        : integer); external;

procedure SQLXFET

    (SQLCAP      : integer;

     owner       : ownername_type;

     xmodule     : modulename_type;

     section     : integer;

     parms       : integer;

     inparms     : integer;

     outparms    : integer;

     isselect    : boolean); external;

procedure SQLXIDU

    (SQLCAP      : integer;

     owner       : ownername_type;

     xmodule     : modulename_type;

     section     : integer;

     parms       : integer;

     inparms     : integer;

     isbulk      : boolean); external;






procedure SQLXOPK

    (SQLCAP      : integer;

     owner       : ownername_type;

     xmodule     : modulename_type;

     section     : integer;

     parms       : integer;

     inparms     : integer;

     kpcval      : integer); external;

procedure SQLXOPU

    (SQLCAP      : integer;

     owner       : ownername_type;

     xmodule     : modulename_type;

     section     : integer;

     parms       : integer;

     inparms     : integer;

     var formats : string;

     nhv         : integer;

     kpcval      : integer); external;

procedure SQLXPLN

    (SQLCAP      : integer;

     msgstrp     : integer;

     xstrlen     : integer;

     isvarchar   : integer); external;

procedure SQLXPRE

    (SQLCAP      : integer;

     queryptr    : integer;

     querysize   : integer;

     xmodule     : modulename_type;

     section     : integer); external;

procedure SQLXSECT

    (SQLCAP      : integer;

     owner       : ownername_type;

     modul       : modulename_type;

     section     : integer); external;

procedure SQLXSTP

    (SQLCAP      : integer); external;

procedure SQLXSVPT

    (SQLCAP      : integer;

     xstrlen     : integer;

     var hexstr  : string;

     svptrec     : integer); external;

ALLBASE/SQL Message File

Messages placed in the ALLBASE/SQL message file (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 the following four parts:

A banner:

                                              MON, JUL 10, 1991  4:48 PM

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

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

   1989,1990,1991. ALL RIGHTS RESERVED.

NOTE: 300/400 The banner for a Series 300 preprocessor is as follows:

                                              MON, JUL 10, 1991  4:48 PM

   HP79725A.E1.00         PASCAL 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          = PASEX2

Warnings and errors encountered during preprocessing:

      32       SalesPriceInd    : SQLID;

             |

   ****** Unsupported type syntax for host variable. (DBERR 10933)





          SELECT PartNumber, PartName, SalesPrice INTO :PartNumber,

          :PartName, :SalesPrice :SalesPriceInd FROM PurchDB.Parts

          WHERE PartNumber = :PartNumber;



   ****** ALLBASE/SQL errors.  (DBERR 10952)

   ****** in SQL statement ending in line 128

   *** ALLBASE/SQL alignment error on column 3 in buffer 5.  (DBERR 4200)



   There are errors.  No sections stored.

A summary of the results of preprocessing:

    2 ERRORS   0 WARNINGS

   END OF PREPROCESSING.

Both the banner and the preprocessing summary results are also echoed to the terminal.

As illustrated in Figure 2-14, 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-12 Sample sqlmsg Showing Errors

$ more sqlmsg

                                          MON, JUL 10, 1991  4:48 PM

HP36217-02A.E1.00      PASCAL 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           = PASEX2



     32       SalesPriceInd   : SQLID

				|

******  Unsupported type syntax for host variable.  (DBERR 10933)





	SELECT PartNumber, PartName, SalesPrice INTO :PartNumber,

	:PartName, :SalesPrice :SalesPriceInd FROM PurchDB.Parts

	WHERE PartNumber= :PartNumber;



******  HP SQL query processing errors (DBERR 10952)

******  in SQL statement ending in line 128

*** HP SQL alignment error on column 3 in buffer 5.  (DBERR 4200)



There are errors.  No sections stored.

  2 ERRORS   0 WARNINGS

 END OF PROCESSING.

As Figure 2-15 illustrates, the preprocessor can terminate with the warning message:

   ****** HP 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 run time if it cannot be validated.

Figure 2-13 Sample sqlmsg Showing Warnings




$ more sqlmsg



                                          MON, JUL 10, 1991  4:48 PM

HP36217-02A.E1.00      PASCAL 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         = PASEX2



       SELECT PartNumber, PartName, SalesPrice INTO :PartNumber,

       :PartName, :SalesPrice :SalesPriceInd FROM PurchDB.Parts

       WHERE PartNumber = :PartNumber;



****** HP SQL warnings. (DBWARN 10602)

****** in SQL statement ending in line 128

*** Column PARTNUMBER not found. (DBERR 2211)



  1 Sections stored in DBEnvironment.



  0 ERRORS   1 WARNINGS

END OF PREPROCESSING

Installable Module File

When the Pascal 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 ModifiedSourceFileName.sqlm. 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:

   $ 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> pasex2.sqlm;

   Name of module in this file:  THOMAS.PASEX2

   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 SQL 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. Note that if the DELETE WHERE CURRENT or UPDATE WHERE CURRENT command is dynamically preprocessed, a section does exist in the module.

When the preprocessor stores a section, it actually stores what is 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 run time 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.

ALLBASE/SQL creates the following 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-14 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-14 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 Pascal program. If you are supplying a module name in a native 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-14 Information in SYSTEM.SECTION on Stored Sections

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



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

PASEX2            |THOMAS       |SYSTEM           |        1|     0|    1

PASEX7            |THOMAS       |SYSTEM           |        1|     1|    1

PASEX7            |THOMAS       |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. Program pasex2 contains only one section, for executing the SELECT command in the program illustrated in Figure 2-6. Program pasex7 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 -d option:

   isql=> DROP MODULE PASEX2;



          or



   $ psqlpas ../sampledb/PartsDBE -i pasex2 -d

You must have proper table access authority to drop a module.

Stored sections are marked invalid when the following occur:

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 validate the section again the next time the program is executed. For this reason, you should embed COMMIT WORK commands even following SELECT commands, since the COMMIT WORK command may be needed even when data is not changed by a program.