The flowchart in Figure 9-1 summarizes the functionality of
program pasex9. This program creates orders in the sample
DBEnvironment, PartsDBE. Each order is placed with a
specific vendor, to obtain one or more parts supplied by that vendor.
An order consists of
a row in table PurchDB.Orders, which comprises the order header:
OrderNumber (defined NOT NULL)
An order usually also consists of one or more line items, represented
by one or more rows in table PurchDB.OrderItems, such as the following:
OrderNumber (defined NOT NULL)
ItemNumber (defined NOT NULL)
PurchasePrice (defined NOT NULL)
Program pasex9 uses a simple INSERT command to create the order header and, optionally, a BULK INSERT command to insert line items.
The runtime dialog for pasex9 appears in Figure 9-2, and the
source code in Figure 9-3.
To establish a DBE session, pasex9 executes function
ConnectDBE 54 . This function evaluates to TRUE
when the CONNECT command 5 is successfully executed.
The program then executes procedure CreateOrder
until the Done flag is set to TRUE 55 .
Procedure CreateOrder prompts for a vendor number or
a 0 48 . When the user enters a 0, Done is set to
TRUE 53 and the program terminates. When the user enters a vendor number, pasex9 performs the tasks in the following list.
Validates the number entered.
Creates an order header if the vendor number is valid.
Optionally inserts line items if the order header has
been successfully created; the part number for each line item is validated to ensure the vendor actually supplies the part.
Displays the order created.
To validate the vendor number, procedure
ValidateVendor is executed 49 .
Procedure ValidateVendor
starts a transaction by invoking procedure BeginTransaction 9 ,
which executes the BEGIN WORK command 6 .
Then a SELECT command 10 is processed to determine whether the
vendor number exists in column VendorNumber of table
PurchDB.Vendors:
If the number exists in table PurchDB.Vendors, the vendor number
is valid. Flag
VendorOK is set to TRUE, and the transaction is terminated by invoking procedure CommitWork 11 .
CommitWork executes the COMMIT WORK command 7 .
If the vendor number is not found, COMMIT WORK is executed and a message is displayed to inform the user that the number entered is
invalid 12 . Several flags
are set to FALSE so that when control
returns to procedure CreateOrder, the user is again
prompted for a vendor number.
If the SELECT command fails, procedure SQLStatusCheck is invoked 13 to display any error messages 4 .
Then the COMMIT WORK
command is executed, and the appropriate flags set to FALSE.
If the vendor number is valid, pasex9 invokes procedure
CreateHeader to create the
order header 50 . The order header
consists of a row containing
the vendor number entered, plus two values computed
by the program: OrderNumber and OrderDate.
Procedure CreateHeader starts
a transaction 34 , then obtains
an exclusive lock on table PurchDB.Orders 35 . Exclusive access to this table ensures that when the row is inserted, no
row having the same number will have been inserted by another transaction. The unique index that
exists on column OrderNumber prevents
duplicate order numbers in table
PurchDB.Orders. Therefore an INSERT
operation fails if it attempts to insert a row having an order number
with a value already in column OrderNumber.
In this case, the exclusive lock does not threaten concurrency. No
operations conducted between the
time the lock is obtained and the time
it is released involve operator intervention:
Procedure CreateHeader invokes procedure
ComputeOrderNumber 36 to
compute the order number and the order
date.
Procedure ComputeOrderNumber executes a SELECT command to retrieve the highest order number in PurchDB.Orders 30 . The number retrieved is incremented
by one 31 to assign a number to
the order.
Procedure ComputeOrderNumber then executes procedure
SystemDate 32 . This procedure uses the HP-UX system call (time) and library call (nl_time) 2
to retrieve and format the current
date. The date retrieved is converted
into YYYYMMDD format, the format in which dates are stored in the sample DBEnvironment.
Procedure ComputeOrderNumber then executes procedure
InsertRow 33 . This procedure executes a simple INSERT command 22
to insert a row into PurchDB.Orders.
If the INSERT command succeeds,
the transaction is terminated with a COMMIT WORK command, and the HeaderOK flag is set to TRUE 24 .
If the INSERT command fails,
the transaction is terminated with COMMIT WORK, but the HeaderOK
flag is set to FALSE 23 so
that the user is prompted for another
vendor number when control returns to procedure CreateOrder.
To create line items, procedure CreateOrder
executes procedure CreateOrderItems
until the DoneItems flag is set to TRUE 51 . Procedure
CreateOrderItems asks the user whether she wants to specify line items 44 .
If the user wants to create line items,
CreateOrderItems executes
procedure ItemEntry until the DoneItems flag is set to TRUE 46 , then executes procedure BulkInsert 47 :
ItemEntry assigns values to host variable
array OrderItems 1 ; each
record in the array corresponds to one line item, or row in
PurchDB.OrderItems. The procedure
first assigns the order number and
a line number to each row 37 ,
beginning at one. ItemEntry then prompts fo
a vendor part number 38 , which
is validated by invoking procedure
ValidatePart 39 .
ValidatePart starts a transaction
14 . Then it executes a SELECT
command 15 to determine whether
the part number entered matches any
part number known to be supplied by the vendor.
If the part number is valid, the
COMMIT WORK command is executed 16
and the PartOK flag set to TRUE.
If the part number is invalid, COMMIT WORK is executed 17 , and
the user informed that the vendor does not supply any part having the number specified; then the PartOK flag is set to FALSE so that the user is prompted for another part number when control
returns to procedure ItemEntry.
If the part number is valid, procedure
ItemEntry completes the line item. It prompts for values to
assign to columns PurchasePrice, OrderQty, and
ItemDueDate 40 . The
procedure then assigns a negative value
to the indicator variable for column ReceivedQty 41
in preparation for inserting a null value into this column.
ItemEntry terminates when the user indicates that
she does not want to specify any more line items 42 or when the host variable array is full 43 .
Procedure BulkInsert starts a transaction 25 , then executes the BULK INSERT command 27 . The
line items in array OrderItems are inserted into table
PurchDB.OrderItems, starting with the first record and
continuing for as many records as there were line items
specified 26 . If the BULK INSERT command succeeds, the COMMIT WORK command is executed 29 and the ItemsOK flag set to
TRUE. If the BULK INSERT command fails,
procedure RollBackWork is executed 28 to process
the ROLLBACK WORK
command 8 so that any rows inserted prior
to the failure are rolled back.
If the user does not want to create line items,
procedure CreateOrderItems displays the order header by
invoking procedure DisplayHeader 45 . DisplayHeader
displays the row inserted earlier in PurchDB.Orders 18 .
If line items were inserted into PurchDB.OrderItems,
procedure DisplayOrder is invoked 52 to display the order created.
DisplayOrder invokes procedure
DisplayHeader 20 to display
the order header. Then it executes
procedure DisplayItems 21
to display each row inserted into PurchDB.OrderItems.
DisplayItems displays values
from array OrderItems 19 .
When the program user enters a 0 in response to the vendor
number prompt, the program terminates by executing
procedure TerminateProgram 56 , which executes
the RELEASE command 3 .
Figure 9-1 Flow Chart of Program pasex9
![[Flow Chart of Program pasex9]](img/x0701.gif)
Figure 9-2 Runtime Dialog of Program pasex9
Program to Create an Order - pasex9
Event List:
Connect to PartsDBE
Prompt for VendorNumber
Validate VendorNumber
INSERT a row into PurchDB.Orders
Prompt for line items
Validate VendPartNumber for each line item
BULK INSERT rows into PurchDB.OrderItems
Repeat the above six steps until the user enters 0
Release PartsDBE
Connect to PartsDBE
Enter VendorNumber or 0 to STOP> 9015
Begin Work
Validating VendorNumber
Commit Work
Begin Work
Calculating OrderNumber
Calculating OrderDate
INSERT INTO PurchDB.Orders
Commit Work
Do you want to specify line items (Y/N)?> y
You can specify as many as 25 line items.
Enter data for ItemNumber 1:
VendPartNumber> 9040
Begin Work
Validating VendPartNumber
Commit Work
PurchasePrice> 1500
OrderQty> 5
ItemDueDate (YYYYMMDD)> 19870630
Do you want to specify another line item (Y/N)?> y
You can specify as many as 25 line items.
Enter data for ItemNumber 2:
VendPartNumber> 9055
Begin Work
Validating VendPartNumber
Commit Work
The vendor has no part with the number you specified.
You can specify as many as 25 line items.
Enter data for ItemNumber 2:
VendPartNumber> 9050
Begin Work
Validating VendPartNumber
Commit Work
PurchasePrice> 345
OrderQty> 2
ItemDueDate (YYYYMMDD)> 19870801
Do you want to specify another line item (Y/N)?> n
Begin Work
BULK INSERT INTO PurchDB. OrderItems
Commit Work
The following order has been created:
OrderNumber: 30524
VendorNumber: 9015
OrderDate: 19870603
ItemNumber: 1
VendPartNumber: 9040
PurchasePrice: 1500.00
OrderQty: 5
ItemDueDate: 19870630
ReceivedQty: NULL
ItemNumber: 2
VendPartNumber: 9050
PurchasePrice: 345.00
OrderQty: 2
ItemDueDate: 19870801
ReceivedQty: NULL
Enter VendorNumber or 0 to STOP> 0
|
Figure 9-3 Program pasex9: Using BULK INSERT
(* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *)
(* This program illustrates the use of BULK INSERT *)
(* to insert multiple rows at a time. *)
(* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *)
Program pasex9(input, output);
const
OK = 0;
NotFound = 100;
DeadLock = -14024;
type
DateType = packed array[1..8] of char;
DatePtr = DateType;
FormType = packed array[1..10] of char;
var
(* Begin Host Variable Declarations *)
EXEC SQL BEGIN DECLARE SECTION;
OrderNumber1 : integer;
VendorNumber : integer;
OrderDate : packed array[1..8] of char;
PartSpecified : packed array[1..16] of char;
MaxOrderNumber : integer;
OrderItems : array[1..25] 1
of record
OrderNumber2 : integer;
ItemNumber : integer;
VendPartNumber : packed array [1..16] of char;
PurchasePrice : longreal;
OrderQty : SmallInt;
ItemDueDate : packed array[1..8] of char;
ReceivedQty : SmallInt;
ReceivedQtyInd : SqlInd;
end;
StartIndex : SmallInt;
NumberOfRows : SmallInt;
SQLMessage : packed array[1..132] of char;
EXEC SQL END DECLARE SECTION;
(* End Host Variable Declarations *)
SQLCA : SQLCA_type; (* SQL Communication Area *)
Done : boolean;
DoneItems : boolean;
VendorOK : boolean;
HeaderOK : boolean;
PartOK : boolean;
ItemsOK : boolean;
Abort : boolean;
Response : packed array [1..4] of char;
counter1 : integer;
counter2 : integer;
i,j : integer;
(* Intrinsic to get today's date from the system *)
procedure time (var sec:integer);external;
function nl_ctime(var clock:integer; var Format:FormType;
Lang:integer):DatePtr; external;
procedure SystemDate; 2
var
sec : integer;
Date : DatePtr;
Format : FormType;
begin
Time(sec)
Format := '19%y%m%d';
Format[10] := chr(0);
Date := nl_ctime(sec,Format,0);
OrderDate := Date;
end; (* SystemDate procedure *)
procedure TerminateProgram; (* Procedure to Release PartsDBE *)
begin
EXEC SQL RELEASE; 3
Done := TRUE;
end; (* End TerminateProgram Procedure *)
$PAGE $
procedure SQLStatusCheck; (*Procedure to Display Error Messages*) 4
begin
Abort := FALSE;
if SQLCA.SQLCODE < DeadLock then Abort := TRUE;
repeat
EXEC SQL SQLEXPLAIN :SQLMessage;
writeln(SQLMessage);
until SQLCA.SQLCODE = 0;
if Abort then TerminateProgram;
end; (* End SQLStatusCheck Procedure *)
$PAGE $
function ConnectDBE: boolean; (* Function to Connect to PartsDBE *)
begin
writeln('Connect to PartsDBE');
EXEC SQL CONNECT TO '../sampledb/PartsDBE'; 5
ConnectDBE := TRUE;
if SQLCA.SQLCODE <> OK then
begin
ConnectDBE := FALSE;
SQLStatusCheck;
end; (* End if *)
end; (* End of ConnectDBE Function *)
procedure BeginTransaction; (* Procedure to Begin Work *)
begin
EXEC SQL BEGIN WORK; 6
if SQLCA.SQLCODE <> OK then
begin
SQLStatusCheck;
TerminateProgram;
end;
end; (* End BeginTransaction Procedure *)
procedure CommitWork; (* Procedure to Commit Work *)
begin
writeln('Commit Work');
EXEC SQL COMMIT WORK; 7
if SQLCA.SQLCODE <> OK then
begin
SQLStatusCheck;
TerminateProgram;
end;
end; (* End CommitWork Procedure *)
procedure RollBackWork; (* Procedure to RollBack Work *)
begin
writeln('Rollback Work');
EXEC SQL ROLLBACK WORK; 8
if SQLCA.SQLCODE <> OK then
begin
SQLStatusCheck;
TerminateProgram;
end;
end; (* End RollBackWork Procedure *)
procedure ValidateVendor;(* procedure ensures VendorNumber is valid*)
begin
writeln;
writeln('Begin Work');
writeln('Validating VendorNumber');
BeginTransaction; 9
EXEC SQL SELECT VendorNumber 10
INTO :VendorNumber
FROM PurchDB.Vendors
WHERE VendorNumber = :VendorNumber;
case SQLCA.SQLCODE of
OK : begin
CommitWork; 11
VendorOK := TRUE;
end;
NotFound : begin
CommitWork; 12
writeln;
writeln('No vendor has the VendorNumber you specified.')
VendorOK := FALSE;
HeaderOK := FALSE;
ItemsOK := FALSE;
end;
Otherwise begin
SQLStatusCheck; 13
CommitWork;
VendorOK := FALSE;
HeaderOK := FALSE;
ItemsOK := FALSE;
end;
end; (* case *)
end; (* End of Procedure ValidateVendor *)
procedure ValidatePart;(*procedure ensures VendorPart number is valid*)
var
i : integer;
begin
writeln;
writeln('Begin Work');
writeln('Validating VendPartNumber');
BeginTransaction; 14
i := counter1;
PartSpecified := OrderItems[i].VendPartNumber;
EXEC SQL SELECT VendPartNumber 15
INTO :PartSpecified
FROM PurchDB.SupplyPrice
WHERE VendorNumber = :VendorNumber
AND VendPartNumber = :PartSpecified;
case SQLCA.SQLCODE of
OK : begin
CommitWork; 16
PartOK := TRUE;
end;
NotFound : begin
CommitWork; 17
writeln;
write('The vendor has no part with the number ');
writeln('you specified.');
PartOK := FALSE;
end;
Otherwise begin
SQLStatusCheck;
CommitWork;
PartOK := FALSE;
end;
end; (* case *)
end; (* End of Procedure ValidatePart *)
procedure DisplayHeader; (*Procedure displays row from PurchDB.Orders*)
begin
writeln;
writeln('The following order has been created:');
writeln;
writeln(' OrderNumber: ' ,OrderNumber1); 18
writeln(' VendorNumber: ' ,VendorNumber);
writeln(' OrderDate: ' ,OrderDate);
end; (* End of Procedure DisplayHeader *)
procedure DisplayItems;
(*Procedure displays rows from PurchDB.OrderItems*)
var
j : integer;
begin
j := counter2;
writeln;
writeln(' ItemNumber: ' ,OrderItems[j].ItemNumber); 19
writeln(' VendPartNumber: ' ,OrderItems[j].VendPartNumber);
writeln(' PurchasePrice: ' ,OrderItems[j].PurchasePrice:10:2);
writeln(' OrderQty: ' ,OrderItems[j].OrderQty);
writeln(' ItemDueDate: ' ,OrderItems[j].ItemDueDate);
writeln(' ReceivedQty: NULL');
counter2 := j + 1;
end; (* End of Procedure DisplayItems *)
procedure DisplayOrder; (* Procedure to Display Order Created *)
var
i : integer;
j : integer;
begin
DisplayHeader; 20
writeln;
i := counter1;
counter2 := 1;
for j := 1 to i do DisplayItems; 21
end; (* End of Procedure DisplayOrder *)
procedure InsertRow; (* procedure to insert row in PurchDB.Orders *)
begin
writeln('INSERT INTO PurchDB.Orders');
EXEC SQL INSERT INTO PurchDB.Orders 22
( OrderNumber,
VendorNumber,
OrderDate )
VALUES (:OrderNumber1,
:VendorNumber,
:OrderDate );
if SQLCA.SQLCODE <> 0 then
begin
SQLStatusCheck; 23
CommitWork;
HeaderOK := FALSE;
end
else
begin
CommitWork; 24
HeaderOK := TRUE;
end;
end; (* End of Procedure InsertRow *)
procedure BulkInsert;
(* procedure to BULK INSERT into PurchDB.OrderItems *)
begin
writeln;
writeln('Begin Work');
BeginTransaction; 25
NumberOfRows := counter1; 26
StartIndex := 1;
writeln('BULK INSERT INTO PurchDB. OrderItems');
EXEC SQL BULK INSERT INTO PurchDB.OrderItems 27
( OrderNumber,
ItemNumber,
VendPartNumber,
PurchasePrice,
OrderQty,
ItemDueDate,
ReceivedQty )
VALUES (:OrderItems,
:StartIndex,
:NumberOfRows );
if SQLCA.SQLCODE <> 0 then
begin
SQLStatusCheck;
RollBackWork; 28
ItemsOK := FALSE;
end
else
begin
CommitWork; 29
ItemsOK := TRUE;
end;
end; (* End of Procedure BulkInsert *)
procedure ComputeOrderNumber; (* procedure to assign number to order *)
begin
EXEC SQL SELECT MAX(OrderNumber) 30
INTO :MaxOrderNumber
FROM PurchDB.Orders;
if SQLCA.SQLCODE <> 0 then
begin
SQLStatusCheck;
CommitWork;
HeaderOK := FALSE;
end
else
begin
writeln('Calculating OrderNumber');
OrderNumber1 := MaxOrderNumber + 1; 31
writeln('Calculating OrderDate');
SystemDate; 32
InsertRow; 33
end;
end; (* End of ComputeOrderNumber Procedure *)
procedure CreateHeader; (* procedure to create order header *)
begin
writeln;
writeln('Begin Work');
BeginTransaction; 34
EXEC SQL LOCK TABLE PurchDB.Orders IN EXCLUSIVE MODE; 35
if SQLCA.SQLCODE <> OK then
begin
SQLStatusCheck;
CommitWork;
HeaderOK := FALSE;
end
else
ComputeOrderNumber; 36
end; (* End of CreateHeader Procedure *)
procedure ItemEntry; (* procedure puts line items in OrderItems array
var
i : integer;
begin
i := counter1;
OrderItems[i].OrderNumber2 := OrderNumber1; 37
OrderItems[i].ItemNumber := i;
writeln;
writeln('You can specify as many as 25 line items.');
writeln;
writeln('Enter data for ItemNumber ',OrderItems[i].ItemNumber:2 ,':');
writeln;
prompt(' VendPartNumber> '); 38
readln(OrderItems[i].VendPartNumber);
ValidatePart; 39
if PartOK then
begin
writeln;
prompt(' PurchasePrice> '); 40
readln(OrderItems[i].PurchasePrice);
prompt(' OrderQty> ');
readln(OrderItems[i].OrderQty);
prompt(' ItemDueDate (YYYYMMDD)> ');
readln(OrderItems[i].ItemDueDate);
OrderItems[i].ReceivedQtyInd := -1; 41
if i < 25 then
begin
writeln;
prompt('Do you want to specify another line item (Y/N)?> '); 42
readln(Response);
if Response[1] in ['N','n'] then
DoneItems := TRUE
else
counter1 := i + 1;
end (* end if i < 25 *)
else
DoneItems := TRUE; (* host variable array is full *) 43
end; (* end if PartOK *)
end; (* End of Procedure ItemEntry *)
procedure CreateOrderItems; (* procedure to create line items *)
begin
writeln;
prompt('Do you want to specify line items (Y/N)?> '); 44
readln(Response);
if Response[1] in ['N','n'] then
begin
DoneItems := TRUE;
ItemsOK := FALSE;
DisplayHeader; 45
end
else
begin
counter1 := 1;
repeat 46
ItemEntry
until DoneItems;
BulkInsert; 47
end;
end; (* End of procedure CreateOrderItems *)
procedure CreateOrder; (* Procedure to create an order *)
begin
writeln;
prompt('Enter VendorNumber or 0 to STOP> '); 48
readln(VendorNumber);
if VendorNumber <> 0 then
begin
ValidateVendor; 49
if VendorOK then CreateHeader; 50
if HeaderOK then
begin
DoneItems := FALSE;
while DoneItems = FALSE do 51
begin
CreateOrderItems;
end; (* while *)
end; (* if HeaderOK *)
if ItemsOK then DisplayOrder; 52
end (* end if VendorNumber *)
else
Done := TRUE; 53
end; (* end of CreateOrder Procedure *)
$PAGE $
begin (* Beginning of Program *)
writeln('Program to Create an Order - pasex9');
writeln('Event List:');
writeln(' Connect to PartsDBE');
writeln(' Prompt for VendorNumber');
writeln(' Validate VendorNumber');
writeln(' INSERT a row into PurchDB.Orders');
writeln(' Prompt for line items');
writeln(' Validate VendPartNumber for each line item');
writeln(' BULK INSERT rows into PurchDB.OrderItems');
writeln(' Repeat the above six steps until the user enters 0');
writeln(' Release PartsDBE');
writeln;
if ConnectDBE then 54
begin
Done := FALSE;
repeat
CreateOrder 55
until Done;
end;
TerminateProgram; 56
end. (* End of Program *)
|
