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 Pascal ALLBASE/SQL program or subprogram with embedded SQL commands for one DBEnvironment. The file must be a fixed length ASCII file, numbered or unnumbered. The formal file designator for this input file is:
SQLIN
ALLBASE/SQL message catalog: a file containing preprocessor messages and ALLBASE/SQL error and warning messages. The formal file designator for the message catalog is as follows, with xxx being the numeric representation for the current native language:
SQLCTxxx.PUB.SYS
When you run the preprocessor in full preprocessing mode, also ensure that the DBEnvironment accessed by the program is available.

As Figure 2-4 “Compiling Preprocessor Output” points out, the Pascal preprocessor creates the following output files:

modified source file: a file containing a modified version of the source file. The formal file designator for this file is:
SQLOUT
After you use the preprocessor in full preprocessing mode, you use SQLOUT and the following include files as input files for the Pascal compiler, as shown in Figure 2-4.
include files: include files containing definitions of constants, types, variables, and external procedures used by Pascal constructs the preprocessor inserts into SQLOUT. The formal file designators for these files are, respectively:
SQLCONST SQLTYPE SQLVAR (or SQLVARn for subprograms) SQLEXTN
ALLBASE/SQL message file: a file containing the preprocessor banner, warning messages, and other messages. The formal file designator for this file is:
SQLMSG
installable module file: a file containing a copy of the module created by the preprocessor. The formal file designator for this file is:
SQLMOD

When you run the preprocessor in full preprocessing mode, the preprocessor also stores a module in the DBEnvironment accessed by your program. The module is used at run time to execute DBEnvironment operations.

If the source file is in a language other than ASCII, the modified source file, 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 Preprocessor Output

If you want to preprocess several ALLBASE/SQL application programs in the same group and account and compile and link the programs later, or you plan to compile a preprocessed program during a future session, you should do the following for each program:

Before running the preprocessor, equate SQLIN to the name of the file containing the application you want to preprocess:
:FILE SQLIN = InFile

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

   :SAVE SQLOUT

   :RENAME SQLOUT, OutFile

   :SAVE SQLMOD

   :RENAME SQLMOD, ModFile

   :SAVE SQLVAR

   :RENAME SQLVAR, VarFile

   :SAVE SQLTYPE

   :RENAME SQLTYPE, TypeFile

   :SAVE SQLEXTN

   :RENAME SQLEXTN, ExtnFile

   :SAVE SQLCONST

   :RENAME SQLCONST, ConstFile

When you are ready to compile the program, you must equate the include file names to their standard ALLBASE/SQL names. See "Preprocessor Generated Include Files" in this section for more information.

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.
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 after prefix EXEC SQL. These constructs follow the rules given in the chapter, "Embedding SQL Commands," 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. Both main and subprograms 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 modified source code file, SQLOUT. Host variables are described in Chapter 4.

The runtime dialog for a sample program that selects and displays data is shown in Figure 2-5. Figure 2-6 illustrates an SQLIN file of the sample program using the following SQL commands:

   INCLUDE SQLCA

   BEGIN DECLARE SECTION

   END DECLARE SECTION

   WHENEVER

   CONNECT

   BEGIN WORK

   SELECT

   COMMIT WORK

   SQLEXPLAIN

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

Figure 2-5 Interactive Runtime Dialog of Program PASEX2

 :RUN PASEX2P

 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> 1823-PT-01



 Begin Work

 SELECT PartNumber, PartName, SalesPrice



 Part Number:  1823-PT-01

 Part Name:    Graphics Printer

 Sales Price:          450.00

 Commit Work



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



 Release PartsDBE



 Terminating Program

Figure 2-6 Program PASEX2: Using Simple Select



$Heap_Dispose ON$

$Heap_Compact ON$

Standard_Level 'HP_Pascal$

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

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

    DeadLock     = -14024;



var



    EXEC SQL INCLUDE SQLCA;   (* SQL Communication Area *)



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



(* Directive to set SQL Whenever error checking *)



$PAGE $



EXEC SQL WHENEVER SQLERROR GOTO 1000;


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

begin



writeln('Connect to PartsDBE');

EXEC SQL CONNECT TO '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('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;

  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

The Pascal preprocessor output files are temporary files. When the SQLIN illustrated in Figure 2-6 is preprocessed, the attributes of the output files created are as follows:



:listftemp,2



TEMPORARY FILES FOR SOMEUSER.SOMEACCT,SOMEGRP



ACCOUNT=  SOMEACCT    GROUP=  SOMEGRP



FILENAME CODE --------LOGICAL RECORD-------  ----SPACE----

              SIZE  TYP    EOF    LIMIT R/B  SECTORS #X MX

SQLCONST       80B  FA       3     2048  16      256  1  8 (TEMP)

SQLEXTN        80B  FA     135     2048  16      256 26  8 (TEMP)

SQLMOD        250W  FB       3     1023   1      304 10  8 (TEMP)

SQLMSG         80B  FA      23     1023  16      128  1  8 (TEMP)

SQLOUT         80B  FA     308    10000  16      256 32  8 (TEMP)

SQLTYPE        80B  FA      61     2048  16      256 26  8 (TEMP)

SQLVAR         80B  FA       7     2048  16      256 26  8 (TEMP)



:

Preprocessor Modified Source File

As the Pascal preprocessor parses the source file (SQLIN), it copies lines from the source file and any file(s) included from it into the modified source file (SQLOUT), comments out embedded SQL commands, and inserts information around each embedded SQL command.

In both preprocessing modes, the Pascal preprocessor:

Inserts a $Skip_Text ON$ and a $Skip_Text OFF$ compiler directive around the embedded SQL command to comment out the SQL command.
Inserts $INCLUDE Pascal compiler directives within the declaration section. These directives reference the four preprocessor generated include files: SQLCONST, SQLTYPE, SQLVAR, and SQLEXTN. SQLCONST and SQLTYPE are included after the program header. SQLVAR and SQLEXTN are included at the end of the global declaration part of a main program.
Keeps comments that follow an embedded command. These comments appear after the preprocessor generated code associated with the 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.
In full preprocessing mode, the preprocessor also:
Generates a Pascal declaration for the SQLCA and the SQLDA in the SQLTYPE include file.
Generates Pascal statements providing conditional instructions following SQL commands encountered after one of the following SQL commands: WHENEVER SQLERROR, WHENEVER SQLWARNING, and WHENEVER NOT FOUND.
Generates Pascal statements that call ALLBASE/SQL external procedures at run time. 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 SQLVAR, SQLCONST, and SQLTYPE.




	CAUTION: Although you can access SQLOUT, SQLVAR, SQLVARn, SQLTYPE, SQLCONST, and SQLEXTN 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 change non-preprocessor-generated constructs in SQLOUT, make the changes to SQLIN, re-preprocess SQLIN, and re-compile the output files before putting the application program into production.

The following modified source file is the result of preprocessing program pasex2 (shown previously). In the listing, the boundaries of code that has been changed or added by the preprocessor is shaded for easy reference.

Figure 2-7 Modified Source File for Program PASEX2

$set 'XOPEN_SQLCA=false'$ 

$Heap_Dispose ON$

$Heap_Compact ON$

$Standard_Level 'HP_Pascal'$

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

(* 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 'sqlconst'$ 

$include 'sqltype'$

const

    OK           =      0;

    NotFound     =    100;

    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 'sqlvar'$ 

$include 'sqlextn'$

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 'PartsDBE';

$SKIP_TEXT OFF$

begin 

SQLVAR1 := 

   '00AE00005061727473444245202020202020202020202020202020202020'

   '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('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 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; 

  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

SQLCONST, SQLTYPE, SQLVAR, SQLVARn, and SQLEXTN are preprocessor generated include files which contain declarations for constants, types, variables, and external procedures for the preprocessor generated statements in SQLOUT. Figure 2-8 through Figure 2-11 illustrate, respectively, the SQLCONST, SQLTYPE, SQLVAR, and SQLEXTN files that correspond to the SQLOUT file in Figure 2-7. Note that the preprocessor inserts the following four Pascal compiler directives to reference SQLCONST, SQLTYPE, SQLVAR, and SQLEXTN:

   $INCLUDE 'sqlconst'$

   $INCLUDE 'sqltype'$

          .

          .

   $INCLUDE 'sqlvar'$

   $INCLUDE 'sqlextn'$

These four directives are always inserted into the global declaration part of a main program. For each declare section in a subprogram, an SQLVARn include file is generated and the compiler directive $INCLUDE 'sqlvarn``'$ is inserted in the local declaration part. The value of n is from 01 through 99.

Even if you use file equations to redirect the include files, the preprocessor still inserts the same $INCLUDE directives. Therefore when you compile preprocessor output, ensure that the preprocess-time file equations are in effect so the correct include files are compiled:

   :FILE SQLCONST=MYCONST

   :FILE SQLTYPE=MYTYPE

   :FILE SQLVAR=MYVAR

   :FILE SQLEXTN=MYEXTN

   :FILE SQLIN=MYPROG

   :FILE SQLOUT=MYSQLPRG



    .  Then the Pascal preprocessor is invoked

    .  in full preprocessing mode.  Later, when the

    .  Pascal compiler is invoked, the following

       file equations must be in effect:



   :FILE SQLCONST=MYCONST

   :FILE SQLTYPE=MYTYPE

   :FILE SQLVAR=MYVAR

   :FILE SQLEXTN=MYEXTN

   :PASCAL MYSQLPRG, $NEWPASS, $NULL

For each SQLVARn file of a subprogram specify:

   :FILE SQLVARn=MYVARn

and the reverse after preprocessing.

Figure 2-8 Sample Constant Include File



const

  SQLOWNER   = 'SOMEUSER@SOMEACCT   ';

  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 External Procedures Include File

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 formal file designator for the message catalog is:

SQLCTxxx.PUB.SYS

where xxx is the numerical value for the current language. If this catalog cannot be opened, ALLBASE/SQL looks for the default NATIVE-3000 message catalog:

SQLCT000.PUB.SYS

If the default catalog cannot be opened, ALLBASE/SQL returns an error message saying that the catalog file is not available. If the NATIVE-3000 catalog is available, the user sees a warning message indicating that the default catalog is being used. SQLMSG messages contain four parts:

A banner:

                                WED, OCT 25, 1991,  1:38 PM

HP36216-02A.E1.00   PASCAL Preprocessor/3000    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:

SQLIN                = PASEX2.SomeGrp.SomeAcct

DBEnvironment        = 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 ParNumber =

       :PartNumber;



****** ALLBSE/SQL errors  (DBERR 10952)

****** in SQL statement ending in line 127

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

   PROGRAM TERMINATED IN AN ERROR STATE.  (CIERR 976)

When you equate SQLMSG to $STDLIST, all these messages appear at the terminal during a session or in the job stream listing. When SQLMSG is not equated to $STDLIST, parts 1 and 4 are still sent to $STDLIST, and all parts appear in the file equated to SQLMSG:

:FILE SQLMSG=MyMsg;Rec=-80,16,f,ASCII

:FILE SQLIN=PASEX2

:RUN PSQLPAS.PUB.SYS;INFO="PartsDBE"

                                WED, JUL 22, 1991,  1:38 PM

HP36216-02A.E1.00   PASCAL Preprocessor/3000    ALLBASE/SQL

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

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



 2 ERRORS    0 WARNINGS

END OF PREPROCESSING.

If you want to keep the message file, you should save the file you equate to SQLMSG. It is created as a temporary file.

As illustrated in Figure 2-12, a line number is often provided in SQLMSG. This line number references the line in SQLIN 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

:EDITOR

HP32201A.07.00 EDIT/3000 FRI, OCT 27, 1991, 10:20 AM

(C) HEWLETT-PACKARD CO. 1990

/T SQLMSG;L ALL UNN

FILE UNNUMBERED

        .

        .





    29      SalesPriceInd    : SQLID;

                                   |

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

There are errors.  No sections stored.

       .

       .

  2 ERRORS    0 WARNINGS

END OF PREPROCESSING

As Figure 2-13 illustrates, the preprocessor can terminate with a warning message. 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 Warning

:EDITOR

HP32201A.07.00 EDIT/3000 FRI, OCT 27 1991, 10:20 AM

(C) HEWLETT-PACKARD CO. 1990

/T SQLMSG;L ALL UNN

FILE UNNUMBERED



 SQLIN                = PASEX2.SOMEGRP.SOMEACCT

 DBEnvironment        = PartsDBE

 Module Name          = PASEX2



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

  :SalesPrice :SalesPriceInd FROM PurchDB.Parts WHERE ParNumber =

  :PartNumber;

             |

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

****** in SQL statement ending in line 125

***  Column PARNUMBER 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 by default is SQLMOD. If at preprocessing time SQLMOD 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:

   :RUN PSQLPAS.PUB.SYS;INFO = "DBEnvironmentName&

   (MODULE (InstalledModuleName) DROP)"

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

   :SAVE SQLMOD

   :RENAME SQLMOD, MYMOD

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

   :ISQL

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

   isql=> CONNECT TO 'PARTSDBE.SomeGrp.SomeAcct';

   isql=> INSTALL;



  File name> MYMOD.SOMEGRP.SOMEACCT;

   Na me of module in this file:  SomeUser@SomeAcct.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

   FETCH                             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 language other than NATIVE-3000 (ASCII), be sure it is in the same language as that of the DBEnvironment.
OWNER: This column identifies the owner of the module. You specify an owner name when you invoke the preprocessor; the owner name is by default the logon UserName@AccountName associated with the preprocessing session. If you are supplying an owner name in a language other than NATIVE-3000 (ASCII), be sure it is in the same language as that of the DBEnvironment.
DBEFILESET: This column indicates the DBEFileSet with which DBEFiles housing the section are associated.
SECTION: This column gives the section number. Each section associated with a module is assigned a number by the preprocessor as it parses the related SQL command at preprocessing time.

TYPE: This column identifies the type of section:

   1 = SELECT associated with a cursor

   2 = SELECT defining a view

   0 = All other sections

VALID: This column identifies whether a section is valid or invalid:
0 = invalid 1 = valid

Figure 2-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           |KAREN@THOMAS |SYSTEM      |        1|     0|    1

EXP11            |KAREN@THOMAS |SYSTEM      |        1|     1|    1

EXP11            |KAREN@THOMAS |SYSTEM      |        2|     0|    1

--------------------------------------------------------------------

Number of rows selected is 16.

U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]>

The first eleven selected 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. PASEX2 contains only one section, for executing the SELECT command in the program illustrated in Figure 2-6. EXP11 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 PASEX2;



               or



   :RUN PSQLPAS.PUB.SYS;INFO="PartsDBE (MODULE(PASEX2) DROP)"

Stored sections are marked invalid when any of 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 revalidate 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.