Preprocessor Input and Output [ HP SQL/V PASCAL Application Programming Guide ]
HP SQL/V PASCAL Application Programming Guide
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:
* SQLIN: a file containing the Pascal HP SQL program or subprogram with
embedded SQL commands for one DBEnvironment. The formal file
designator for this input file is:
SQLIN
* HPSQLCAT: the HP SQL message catalog, which contains preprocessor
messages and HP SQL error and warning messages. The formal file
designator for the message catalog is:
HPSQLCAT.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-3 points out, the Pascal preprocessor creates the following temporary
output files:
* SQLOUT: a file containing a modified version of the source code in
SQLIN. 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.
* SQLCONST=, SQLTYPE, SQLVAR, and SQLEXTN: 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
* SQLMSG: a file containing the preprocessor banner, error and warning
messages, and other messages. The formal file designator for this
file is:
SQLMSG
* SQLMOD: 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.
Click here to view figure.
Figure 2-3. Pascal Preprocessor Input and Output
Click here to view figure.
Figure 2-4. Compiling Preprocessor Output
If you want to preprocess several HP 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 HP SQL names. See "Preprocessor-Generated
Include Files" in this section for more information.
SQLIN
SQLIN must be an ASCII file (numbered or unnumbered) that contains at a
minimum the following constructs:
(* PROGRAM HEADING *)
Program ProgramName(input, output);
begin
AnyStatement;
end.
When parsing SQLIN, the Pascal preprocessor ignores Pascal statements and
most Pascal compiler directives in SQLIN. 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. A module name can contain as
many as 20 characters and must follow the rules governing HP SQL
basic names (given in the 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 Chapter 4.
Figure 2-6 illustrates an SQLIN file containing a sample program using
the following SQL commands highlighted in boldface in the figure:
INCLUDE SQLCA
BEGIN DECLARE SECTION
END DECLARE SECTION
WHENEVER
CONNECT
BEGIN WORK
COMMIT WORK
SELECT
SQLEXPLAIN
As the 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 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 (/).
_____________________________________________________________________
| |
| :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> 1199-M-01|
| |
| Begin Work |
| SELECT PartNumber, PartName, SalesPrice |
| |
| Part Number: 1199-M-01 |
| Part Name: Modem |
| Sales Price is NULL |
| Commit Work |
| |
| Enter Part Number within Parts Table or "/" to STOP> / |
| |
| Release PartsDBE |
| |
| Terminating Program |
_____________________________________________________________________
Figure 2-5. Execution of Program PASEX2
____________________________________________________________________________
| |
| (* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *)|
| (* 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; |
| |
| $PAGE $ |
| |
| (* Directive to set SQL Whenever error checking *) |
| EXEC SQL WHENEVER SQLERROR GOTO 1000; |
____________________________________________________________________________
Figure 2-6. Program PASEX2: Using Simple Select (Page 1 of 5)
________________________________________________________________________
| |
| 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 $ |
________________________________________________________________________
Figure 2-6. Program PASEX2: Using Simple Select (page 2 of 5)
__________________________________________________________________________
| |
| 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; |
__________________________________________________________________________
Figure 2-6.Program PASEX2: Using Simple Select (page 3 of 5)
___________________________________________________________________________
| |
| 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 $ |
___________________________________________________________________________
Figure 6-2. Program PASEX2: Using Simple Select (page 4 of 5)
__________________________________________________________
| |
| 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 *) |
__________________________________________________________
Figure 6-2. Program PASEX2: Using Simple Select (page 5 of 5)
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 254B VA 3 1023 1 256 1 8 (TEMP)
SQLEXTN 254B VA 79 1023 1 256 1 8 (TEMP)
SQLMOD 270W FB 3 1023 1 384 1 8 (TEMP)
SQLMSG 254B VA 13 1023 1 256 1 8 (TEMP)
SQLOUT 254B VA 313 1023 1 256 1 8 (TEMP)
SQLTYPE 254B VA 56 1023 1 256 1 8 (TEMP)
SQLVAR 254B VA 7 1023 1 256 1 8 (TEMP)
When you invoke the preprocessor using the UDCs provided with HP SQL, the
output file attributes are slightly different:
:pppas pasex2,PartsDBE,pasex2p,msg
.
.
.
: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 1000 16 40 1 8 (TEMP)
SQLEXTN 80B FA 79 1000 16 40 1 8 (TEMP)
SQLMOD 270W FB 3 500 1 189 1 8 (TEMP)
SQLOUT 80B FA 313 10000 16 200 2 32 (TEMP)
SQLTYPE 80B FA 56 1000 16 40 1 8 (TEMP)
SQLVAR 80B FA 7 1000 16 40 1 8 (TEMP)
The UDCs are described later in this chapter under "Invoking the Pascal
Preprocessor". The UDCs have the following effects on the attributes of
all the preprocessor output files:
* The files are 80-byte fixed-length files, which can be accessed by
most editors.
* Disc space is efficiently utilized by the files.
Modified Source File
As the Pascal preprocessor parses SQLIN, it copies lines from SQLIN and
any file(s) included from SQLIN into SQLOUT, comments out embedded SQL
commands, and inserts information around each embedded SQL command.
Figure 2-7 illustrates the SQLOUT generated for the SQLIN pictured in
Figure 2-6. The boldface lines contain information generated by the
Pascal preprocessor. 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
part. 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 SQLIN is in the same column, but on a different
line, in SQLOUT.
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.
These statements follow any SQL commands encountered after one of the
following SQL commands: WHENEVER SQLERROR, WHENEVER SQLWARNING, and
WHENEVER NOT FOUND.
* Generates Pascal statements that call HP 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 need to 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.
____________________________________________________________________________
| |
| (* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *)|
| (* 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; |
____________________________________________________________________________
Figure 2-7. Sample Modified Source File (page 1 of 6)
____________________________________________________________________________
| |
| $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 :='00AB0180506172747344424520202020202020202020202020202020'+|
| '20202020202020202020202020202020202020202020202020202020'+ |
| '20202020202020202020202020202020202020202020202020202020'+ |
| '20202020202020202020202020202020202020202020202020202020'+ |
| '2020202020202020202020202020202020202020'; |
| 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 := '00A0007F00117274'; |
| SQLXCON(Waddress(SQLCA), Sqlvar2); |
____________________________________________________________________________
Figure 2-7. Sample Modified Source File (page 2 of 6)
________________________________________________________________________
| |
| 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 := '00A1007F'; |
| 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 |
| SQLvar4 := '00A1007F'; |
| SQLXCON(Waddress(SQLCA), Sqlvar4); |
| End; |
| Begin |
| SQLvar5 :='00AC007F2020202020202020202020202020202020202020'; |
| SQLXCON(Waddress(SQLCA), Sqlvar5); |
| End; |
________________________________________________________________________
Figure 2-7. Sample Modified Source File (page 3 of 6)
__________________________________________________________________________
| |
| 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$ |
__________________________________________________________________________
Figure 2-7. Sample Modified Source File (page 4 of 6)
___________________________________________________________________________
| |
| Begin |
| SQLTEMPV.REC1.PARTNUMBER1:= PARTNUMBER; |
| SQLXFET(Waddress(SQLCA),SQLOWNER,SQLMODNAME,1,Waddress(SQLTEMPV), |
| 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 |
| ; |
| 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; |
___________________________________________________________________________
Figure 2-7. Sample Modified Source File (page 5 of 6)
______________________________________________________________
| |
| 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(' Release 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 *) |
______________________________________________________________
Figure 2-7. Sample Modified Source File (page 6 of 6)
Preprocessor-Generated Include Files
SQLCONST, SQLTYPE, SQLVAR, SQLVARn, and SQLEXTN are preprocessor-created
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.
_____________________________________________
| |
| const |
| SQLOWNER = 'SOMEUSER@SOMEACCT ';|
| SQLMODNAME = 'PASEX2 ';|
_____________________________________________
Figure 2-8. Sample SQLCONST
_____________________________________________________
| |
| type |
| OwnerName_Type = String[20]; |
| ModuleName_Type = String[20]; |
| SmallInt = -32768..32767; |
| SqlInd = -32768..23767; |
| 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, SqlCode: Integer; |
| LEN : SmallInt |
| MSG : Packed Array[1..254] of Char; |
| SqlErrP: Packed Array[1..8] of Char; |
| SqlErrD: Array [1..6] of Integer; |
| SqlWarn: Packed Array [0..7] of Char; |
| SqlExt1: SmallInt; |
| SqlExt2: SmallInt; |
| end; |
_____________________________________________________
Figure 2-9. Sample SQLTYPE (page 1 of 2)
_______________________________________________________
| |
| 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: SmallInt; |
| SqlNRow : Integer; |
| SqlRRow : Integer; |
| SqlRowLen: Integer; |
| SqlBufLen: Integer; |
| SqlRowBuf: SmallInt; |
| 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-9. Sample SQLTYPE (page 2 of 2)
_____________________________________
| |
| Var |
| SQLvar1 : String[264]; |
| SQLvar2 : String[16]; |
| SQLvar3 : String[8]; |
| SQLvar4 : String[8]; |
| SQLvar5 : String[48]; |
| sqlTempV : SqlTempV_Type; |
_____________________________________
Figure 2-10. Sample SQLVAR
_________________________________________________________________
| |
| procedure SQLXCON( sqlcap : SmallInt; |
| var stmt : String); external; |
| procedure SQLXSTP( sqlcap : SmallInt); external;|
| procedure SQLXFET( sqlcap : SmallInt; |
| owner : OwnerName_Type; |
| xmodule : ModuleName_Type; |
| section : Integer; |
| parms : SmallInt; |
| inParms : Integer; |
| outParms : Integer; |
| isSelect : boolean); external; |
| procedure SQLXOPN( sqlcap : SmallInt; |
| owner : OwnerName_Type; |
| xmodule : ModuleName_Type; |
| section : Integer; |
| parms : SmallInt; |
| inParms : Integer); external; |
| procedure SQLXIDU( sqlcap : SmallInt; |
| owner : OwnerName_Type; |
| xmodule : ModuleName_Type; |
| section : Integer; |
| parms : SmallInt; |
| inParms : Integer; |
| isBulk : boolean); external; |
| procedure SQLXPRE( sqlcap : SmallInt; |
| queryPtr : SMallInt; |
| querySize : Integer; |
| xmodule : ModuleName_Type; |
| section : Integer); external; |
| procedure SQLXEXS( sqlcap : SmallInt; |
| xmodule : ModuleName_Type; |
| section : Integer); external; |
| procedure SQLXEXI( sqlcap : SmallInt; |
| queryPtr : SmallInt; |
| querySize : Integer); external; |
| procedure SQLXDES( sqlcap : SmallInt; |
| sqldap : SmallInt; |
| xmodule : ModuleName_Type; |
| section : Integer); external; |
| procedure SQLXDFE( sqlcap : SmallInt; |
| sqldap : SmallInt; |
| xmodule : ModuleName_Type; |
| section : Integer); external; |
_________________________________________________________________
Figure 2-11. Sample SQLEXTN (page 1 of 2)
_________________________________________________________________
| |
| procedure SQLXBFE( sqlcap : SmallInt; |
| owner : OwnerName_Type; |
| xmodule : ModuleName_Type; |
| section : Integer; |
| parms : SmallInt; |
| inParms : Integer; |
| outArray : SmallInt; |
| entrySize : Integer; |
| nEntry : Integer; |
| firstRow : Integer; |
| nRow : Integer); external; |
| procedure SQLXBIN( sqlcap : SmallInt; |
| owner : OwnerName_Type; |
| xmodule : ModuleName_Type; |
| section : Integer; |
| inArray : SmallInt; |
| entrySize : Integer; |
| nEntry : Integer; |
| firstRow : Integer; |
| nRow : Integer); external; |
| Procedure SQLXPLN( sqlcap : SmallInt; |
| MsgStrp : SmallInt; |
| xStrLen : integer; |
| IsVarChar : integer);External; |
| procedure SQLXSECT( sqlca : SmallInt; |
| owner : OwnerName_Type; |
| modul : ModuleName_Type; |
| section : integer); external; |
| procedure SQLXSVPT( sqlca : SmallInt; |
| xstrLen : integer; |
| var hexStr : string; |
| svptrec : SmallInt); external;|
| procedure SQLXCNH( sqlcap : SmallInt; |
| MsgStrp : SmallInt; |
| xStrLen : integer; |
| IsVarChar : integer); external; |
_________________________________________________________________
Figure 2-11. Sample SQLEXTN (page 2 of 2)
SQLMSG
Messages placed in SQLMSG come from the HP SQL message catalog, named
HPSQLCAT.PUB.SYS by default. Ensure that the message catalog is
available when you invoke the Pascal preprocessor. SQLMSG messages
contain four parts: 1. A banner:
WED, JUL 22, 1987, 1:38 PM
HP36215B.01.00 PASCAL Preprocessor/3000 HP SQL/V
(C) COPYRIGHT HEWLETT-PACKARD CO., 1982,1983,1984,1985,1986,1987
2. A summary of the preprocessor invocation conditions:
SQLIN = PASEX2.SomeGrp.SomeAcct
DBEnvironment = PartsDBE
Module Name = PASEX2
3. Warnings and errors encountered during preprocessing:
30 SalesPriceInd : SQLID;
|
****** Unsupported type syntax for host variable. (DBERR 10933)
.
.
.
There are errors. No sections stored.
4. 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, 1987, 1:38 PM
HP36215B.01.00 PASCAL Preprocessor/3000 HP SQL/V
(C) COPYRIGHT HEWLETT-PACKARD CO., 1982,1983,1984,1985,1986,1987
2 ERRORS 0 WARNINGS
END OF PREPROCESSING.
PROGRAM TERMINATED IN AN ERROR STATE. (CIERR 976)
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 HP SQL Message Manual for additional information on the
exception condition when these numbered messages appear.
________________________________________________________________________
| |
| :EDITOR |
| HP32201A.07.17 EDIT/3000 FRI, JUL 24, 1987, 10:20 AM |
| (C) HEWLETT-PACKARD CO. 1985 |
| /T MyMsg;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 |
________________________________________________________________________
Figure 2-12. Sample SQLMSG Showing Errors
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.
_____________________________________________________________________________
| |
| :RUN PSQLPAS.PUB.SYS;INFO="PartsDBE" |
| |
| FRI, JUL 24, 1987, 10:15 AM |
| HP36216-02A.01.00 PASCAL Preprocessor/3000 HP SQL/V |
| (C) COPYRIGHT HEWLETT-PACKARD CO., 1982,1983,1984,1985,1086,1987 |
| |
| SQLIN = PASEX2.SOMEGRP.SOMEACCT |
| DBEnvironment = PartsDBE |
| |
| Module Name = PASEX2 |
| |
| 0 ERRORS 1 WARNINGS |
| END OF PREPROCESSING |
| |
| :EDITOR |
| HP32201A.07.17 EDIT/3000 FRI, JUL 24 1987, 10:20 AM |
| (C) HEWLETT-PACKARD CO. 1985 |
| /T SQLMSG;L ALL UNN |
| FILE UNNUMBERED |
| . |
| . |
| 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. |
_____________________________________________________________________________
Figure 2-13. Sample SQLMSG Showing Warning
SQLMOD
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
SQLMOD. The module in this file can be installed into a DBEnvironment
different from the DBEnvironment accessed at preprocessing time by using
the INSTALL command in ISQL:
If you want to preserve SQLMOD after preprocessing,
you must keep it as a permanent file. Rename SQLMOD
after making it permanent:
:SAVE SQLMOD
:RENAME SQLMOD,MYMOD
Before invoking ISQL to install the module contained
in SQLMOD, you may have to transport it and its related
program file to the machine containing the target
DBEnvironment. 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;
Name 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 command except:
BEGIN DECLARE SECTION OPEN CURSOR
BEGIN WORK PREPARE
CLOSE CURSOR RELEASE
COMMIT WORK ROLLBACK WORK
CONNECT SAVEPOINT
END DECLARE SECTION START DBE
EXECUTE STOP DBE
EXECUTE IMMEDIATE TERMINATE USER
INCLUDE 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 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, HP SQL executes the appropriate run
tree when the SQL command is encountered in the application program. If
a section is invalid, HP SQL determines whether the objects referenced in
the sections exist and whether current authorization criteria are
satisfied. When an invalid section can be validated, HP 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:
1. Sections for executing the SELECT command associated with a
DECLARE CURSOR command
2. Sections for executing the SELECT command associated with a CREATE
VIEW command
3. 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.
* 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 log-on UserName@AccountName associated with the
preprocessing session.
* 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
____________________________________________________________________________________
| |
| 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 |
| 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]> |
____________________________________________________________________________________
Figure 2-14. Information in SYSTEM.SECTION on Stored Sections
The first eleven rows in this query result describe the sections stored
for the system views. The next two rows describe the two views in the
sample database: PurchDB.PartInfo and PurchDB.VendorStatistics. Views
are always stored as invalid sections, because the run tree is always
generated at run time.
The remaining rows describe sections associated with two preprocessed
programs. 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:
* 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, HP 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.
Click here to view figure.
Figure 2-3. Pascal Preprocessor Input and Output
Click here to view figure.
Figure 2-4. Compiling Preprocessor Output
If you want to preprocess several HP 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 HP SQL names. See "Preprocessor-Generated
Include Files" in this section for more information.
SQLIN
SQLIN must be an ASCII file (numbered or unnumbered) that contains at a
minimum the following constructs:
(* PROGRAM HEADING *)
Program ProgramName(input, output);
begin
AnyStatement;
end.
When parsing SQLIN, the Pascal preprocessor ignores Pascal statements and
most Pascal compiler directives in SQLIN. 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. A module name can contain as
many as 20 characters and must follow the rules governing HP SQL
basic names (given in the 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 Chapter 4.
Figure 2-6 illustrates an SQLIN file containing a sample program using
the following SQL commands highlighted in boldface in the figure:
INCLUDE SQLCA
BEGIN DECLARE SECTION
END DECLARE SECTION
WHENEVER
CONNECT
BEGIN WORK
COMMIT WORK
SELECT
SQLEXPLAIN
As the 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 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 (/).
_____________________________________________________________________
| |
| :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> 1199-M-01|
| |
| Begin Work |
| SELECT PartNumber, PartName, SalesPrice |
| |
| Part Number: 1199-M-01 |
| Part Name: Modem |
| Sales Price is NULL |
| Commit Work |
| |
| Enter Part Number within Parts Table or "/" to STOP> / |
| |
| Release PartsDBE |
| |
| Terminating Program |
_____________________________________________________________________
Figure 2-5. Execution of Program PASEX2
____________________________________________________________________________
| |
| (* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *)|
| (* 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; |
| |
| $PAGE $ |
| |
| (* Directive to set SQL Whenever error checking *) |
| EXEC SQL WHENEVER SQLERROR GOTO 1000; |
____________________________________________________________________________
Figure 2-6. Program PASEX2: Using Simple Select (Page 1 of 5)
________________________________________________________________________
| |
| 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 $ |
________________________________________________________________________
Figure 2-6. Program PASEX2: Using Simple Select (page 2 of 5)
__________________________________________________________________________
| |
| 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; |
__________________________________________________________________________
Figure 2-6.Program PASEX2: Using Simple Select (page 3 of 5)
___________________________________________________________________________
| |
| 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 $ |
___________________________________________________________________________
Figure 6-2. Program PASEX2: Using Simple Select (page 4 of 5)
__________________________________________________________
| |
| 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 *) |
__________________________________________________________
Figure 6-2. Program PASEX2: Using Simple Select (page 5 of 5)
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 254B VA 3 1023 1 256 1 8 (TEMP)
SQLEXTN 254B VA 79 1023 1 256 1 8 (TEMP)
SQLMOD 270W FB 3 1023 1 384 1 8 (TEMP)
SQLMSG 254B VA 13 1023 1 256 1 8 (TEMP)
SQLOUT 254B VA 313 1023 1 256 1 8 (TEMP)
SQLTYPE 254B VA 56 1023 1 256 1 8 (TEMP)
SQLVAR 254B VA 7 1023 1 256 1 8 (TEMP)
When you invoke the preprocessor using the UDCs provided with HP SQL, the
output file attributes are slightly different:
:pppas pasex2,PartsDBE,pasex2p,msg
.
.
.
: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 1000 16 40 1 8 (TEMP)
SQLEXTN 80B FA 79 1000 16 40 1 8 (TEMP)
SQLMOD 270W FB 3 500 1 189 1 8 (TEMP)
SQLOUT 80B FA 313 10000 16 200 2 32 (TEMP)
SQLTYPE 80B FA 56 1000 16 40 1 8 (TEMP)
SQLVAR 80B FA 7 1000 16 40 1 8 (TEMP)
The UDCs are described later in this chapter under "Invoking the Pascal
Preprocessor". The UDCs have the following effects on the attributes of
all the preprocessor output files:
* The files are 80-byte fixed-length files, which can be accessed by
most editors.
* Disc space is efficiently utilized by the files.
Modified Source File
As the Pascal preprocessor parses SQLIN, it copies lines from SQLIN and
any file(s) included from SQLIN into SQLOUT, comments out embedded SQL
commands, and inserts information around each embedded SQL command.
Figure 2-7 illustrates the SQLOUT generated for the SQLIN pictured in
Figure 2-6. The boldface lines contain information generated by the
Pascal preprocessor. 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
part. 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 SQLIN is in the same column, but on a different
line, in SQLOUT.
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.
These statements follow any SQL commands encountered after one of the
following SQL commands: WHENEVER SQLERROR, WHENEVER SQLWARNING, and
WHENEVER NOT FOUND.
* Generates Pascal statements that call HP 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.