 |
» |
|
|
|
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. Figure 3-1 Sample 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;
MultipleRows = -10002;
DeadLock = -14024;
var
EXEC SQL INCLUDE SQLCA; 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;
$PAGE $
(* Directive to set SQL Whenever error checking *)
EXEC SQL Whenever SqlError goto 1000;
Procedure ConnectDBE; (* Procedure to Connect to PartsDBE *)
begin
writeln('Connect to PartsDBE');
EXEC SQL CONNECT TO '../sampledb/PartsDBE'; 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;
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 $
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;
case SQLCA.SQLCODE of 9
OK : DisplayRow;
NotFound : begin
writeln;
writeln('Row not found!');
end;
MultipleRows : begin
writeln;
writeln('WARNING: More than one row qualifies.');
end;
otherwise begin
SQLStatusCheck;
end;
end; (* case *)
EndTransaction;
end; (* End if *)
until PartNumber[1] = '/';
end; (* End of SelectData Procedure *)
procedure SQLStatusCheck; (* Procedure to Display Error Messages *)
begin
Abort := FALSE;
if SQLCA.SQLCODE < DeadLock then Abort := TRUE;
repeat
EXEC SQL SQLEXPLAIN :SQLMessage; 10
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 *)
|
|
