HP 3000 Manuals HP 3000 Manuals
Chapter 3 Embedding SQL Commands
In every ALLBASE/SQL Pascal program, you embed SQL commands in the
declaration section and the procedure section of your program to:
1 Declare the SQL Communications Area (SQLCA).
2 Declare host variables.
3 Start a DBE session by connecting to the
DBEnvironment.
4 5 Define transactions.
6 Implicitly check the status of SQL command
execution.
7 Terminate the DBE session.
8 Define or manipulate data in the DBEnvironment.
9 Explicitly check the status of SQL command
execution.
10 Obtain error and warning messages from the
ALLBASE/SQL message catalog.
The program listing shown in Figure 3-1 illustrates where in a main
program you can embed SQL commands to accomplish the activities listed
above. In a subprogram, host variable declarations cannot be in the
global declaration part.
This chapter is a high-level road map to the logical and physical aspects
of embedding SQL commands in a program. It addresses the reasons for
embedding commands to perform the above activities. It also gives
general rules for how and where to embed SQL commands for these
activities. First however, it shows a program containing commands for
the basic SQL functions listed above. Then it describes the general
rules that apply when you embed any SQL command, referring to the
numbered statements in the program.
____________________________________________________________________________
| |
| $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 *) 1 |
| |
| (* Begin Host Variable Declarations *) |
| EXEC SQL Begin Declare Section; 2 |
| 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; |
| |
____________________________________________________________________________
Figure 3-1. Sample Program pasex2
________________________________________________________________________
| |
| Procedure ConnectDBE; (* Procedure to Connect to PartsDBE *) |
| begin |
| |
| writeln('Connect to PartsDBE'); |
| EXEC SQL CONNECT TO 'PartsDBE'; 3 |
| |
| end; (* End of ConnectDBE Procedure *) |
| |
| |
| Procedure BeginTransaction; (* Procedure to Begin Work *) |
| begin |
| |
| writeln; |
| writeln('Begin Work'); |
| EXEC SQL BEGIN WORK; 4 |
| |
| end; (* End BeginTransaction Procedure *) |
| |
| |
| procedure EndTransaction; (* Procedure to Commit Work *) |
| begin |
| |
| |
| writeln('Commit Work'); |
| EXEC SQL COMMIT WORK; 5 |
| |
| end; (* End EndTransaction Procedure *) |
| |
| |
| (* Directive to reset SQL Whenever error checking *) |
| EXEC SQL Whenever SqlError CONTINUE; 6 |
| |
| |
| procedure TerminateProgram; (* Procedure to Release PartsDBE *)|
| begin |
| |
| writeln('Release PartsDBE'); |
| EXEC SQL COMMIT WORK RELEASE; 7 |
| |
| writeln; |
| writeln('Terminating Program'); |
| Goto 9999; (* Goto exit point of main program *) |
| |
| end; (* End TerminateProgram Procedure *) |
| |
| |
| $PAGE $ |
________________________________________________________________________
Figure 3-1. Sample Program pasex2 (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 8 |
| 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 3-1. Sample Program pasex2 (page 3 of 5)
___________________________________________________________________________
| |
| case SQLCA.SQLCODE of 9 |
| OK : DisplayRow; |
| NotFound : begin |
| writeln; |
| writeln('Row not found!'); |
| end; |
| 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; 10 |
| writeln(SQLMessage); |
| until SQLCA.SQLCODE = 0; |
| |
| if Abort then |
| begin |
| |
| TerminateProgram; |
| |
| end; |
| |
| end; (* End SQLStatusCheck Procedure *) |
| |
| |
| |
| |
| |
| |
| |
| |
___________________________________________________________________________
Figure 3-1. Sample Program pasex2 (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 3-1. Sample Program pasex2 (page 5 of 5)