HP 3000 Manuals HP 3000 Manuals
Program Using BULK INSERT
The flow chart 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.
The order header consists of data from a row in table PurchDB.Orders:
OrderNumber (defined NOT NULL)
VendorNumber
OrderDate
An order usually also consists of one or more line items, represented by
one or more rows in table PurchDB.OrderItems:
OrderNumber (defined NOT NULL)
ItemNumber (defined NOT NULL)
VendPartNumber
PurchasePrice (defined NOT NULL)
OrderQty
ItemDueDate
ReceivedQty
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:
* 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 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 Pascal function CALENDAR 2 to
retrieve the current date. The date retrieved is converted into
YYYYMMDD format, the format in which dates are stored in the
sample DBEnvironment. 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 for 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 .
![[]](../../pic3k/M012400/FFN963c50.gif)
Figure 9-1. Flow Chart 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 |
___________________________________________________________
Figure 9-2. Runtime Dialog of Program pasex9
____________________________________________________________
| |
| 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-2. Runtime Dialog of Program pasex9 (page 2 of 2)
_____________________________________________________________________________
| |
| $Heap_Dispose ON$ |
| $Heap_Compact ON$ |
| Standard_Level 'HP_Pascal$ |
| (* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *) |
| (* 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 |
| TimeType = packed array[1..8] of char; |
| |
| calendrec = packed record |
| year: 0..127; |
| day : 0..511; |
| end; |
| |
| calend_type = record |
| case integer of |
| 0: ( i : smallint); |
| 1: (yydd : calendrec); |
| end; |
| |
| jultype = array[0..12] of integer; |
| |
| const |
| |
| jultable = jultype[0,31,59,90,120,151,181,212,243,273,304,334,365];|
| ljultable = jultype[1,31,60,91,121,152,182,213,244,274,305,335,366];|
| CodeYear = 70; |
| |
| var |
| (* Begin Host Variable Declarations *) |
| EXEC SQL BEGIN DECLARE SECTION; |
| OrderNumber1 : integer; |
| VendorNumber : integer; |
| OrderDate : packed array[1..8] of char; |
| |
| |
| |
| |
_____________________________________________________________________________
Figure 9-3. Program pasex9: Using BULK INSERT
_____________________________________________________________________________
| |
| PartSpecified : packed array[1..16] of char; |
| MaxOrderNumber : integer; |
| |
| OrderItems : packed array[1..25] 1 |
| of packed 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; |
| |
| calend : calend_type; |
| i,j : integer; |
| leap : boolean; |
| cent, |
| yr : integer; |
| |
| (* Intrinsic to get today's date from the system *) |
| |
| |
| |
| |
_____________________________________________________________________________
Figure 9-3. Program pasex9: Using BULK INSERT (page 2 of 12)
_________________________________________________________________________________
| |
| function CALENDAR: SmallInt; INTRINSIC; (* Get today's date from system *)|
| procedure SystemDate; |
| begin |
| calend.i := CALENDAR; 2 |
| if calend.yydd.year < CodeYear then (* compute century *) |
| cent := 20 |
| else cent := 19; |
| |
| (* convert year to ASCII by adding decimal 48 *) |
| OrderDate[1] := chr(48 + cent div 10); |
| OrderDate[2] := chr(48 + cent mod 10); |
| |
| (* compute year, as indicated, so a test for leap year can be made *) |
| yr := cent * 100 + calend.yydd.year; |
| |
| (* most significant year digit *) |
| OrderDate[3] := chr(48 + calend.yydd.year div 10); |
| (* least significant year digit *) |
| OrderDate[4] := chr(48 + calend.yydd.year mod 10); |
| |
| i := 1; |
| leap := true; |
| if (yr mod 4) <> 0 then |
| leap := false |
| else |
| if (yr mod 400) = 0 then |
| leap := false; |
| if leap then (* i = month of year, j = day of month *) |
| begin |
| while calend.yydd.day > ljultable[i] do |
| i := i + 1; |
| j := (calend.yydd.day -ljultable[i - 1]) |
| end |
| else |
| begin |
| while calend.yydd.day > jultable[i] do |
| i := i + 1; |
| j := (calend.yydd.day - jultable[i - 1]) |
| end; |
| (* convert month of year to ASCII *) |
| OrderDate[5] := chr(48 + i div 10); (* most significant digit *) |
| OrderDate[6] := chr(48 + i mod 10); (* least significant digit *) |
| |
| (* convert day of month to ASCII *) |
| OrderDate[7] := chr(48 + j div 10); (* most significant digit *) |
| OrderDate[8] := chr(48 + j mod 10); (* least significant digit *) |
| |
| end; (* SystemDate procedure *) |
_________________________________________________________________________________
Figure 9-3. Program pasex9: Using BULK INSERT (page 3 of 12)
_____________________________________________________________________________
| |
| 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 '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 *) |
_____________________________________________________________________________
Figure 9-3. Program pasex9: Using BULK INSERT (page 4 of 12)
__________________________________________________________________________________
| |
| 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 that ensures vendor number 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; |
| |
__________________________________________________________________________________
Figure 9-3. Program pasex9: Using BULK INSERT (page 5 of 12)
__________________________________________________________________________________
| |
| Otherwise begin |
| SQLStatusCheck; 13 |
| CommitWork; |
| VendorOK := FALSE; |
| HeaderOK := FALSE; |
| ItemsOK := FALSE; |
| end; |
| end; (* case *) |
| end; (* End of Procedure ValidateVendor *) |
| |
| procedure ValidatePart; (*procedure to ensure vendor part 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 *) |
__________________________________________________________________________________
Figure 9-3. Program pasex9: Using BULK INSERT (page 6 of 12)
___________________________________________________________________________________
| |
| procedure DisplayHeader; (* Procedure to display 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 to Display 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: is 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 *) |
___________________________________________________________________________________
Figure 9-3. Program pasex9: Using BULK INSERT (page 7 of 12)
_________________________________________________________________________________
| |
| 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 ); |
_________________________________________________________________________________
Figure 9-3. Program pasex9: Using BULK INSERT (page 8 of 12)
_______________________________________________________________________________
| |
| 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 |
_______________________________________________________________________________
Figure 9-3. Program pasex9: Using BULK INSERT (page 9 of 12)
________________________________________________________________________________
| |
| else |
| ComputeOrderNumber; 36 |
| end; (* End of CreateHeader Procedure *) |
| procedure ItemEntry; (* procedure to put line items into 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 *) |
________________________________________________________________________________
Figure 9-3. Program pasex9: Using BULK INSERT (page 10 of 12)
_____________________________________________________________________________
| |
| 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 *) |
_____________________________________________________________________________
Figure 9-3. Program pasex9: Using BULK INSERT (page 11 of 12)
_____________________________________________________________________________
| |
| $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 *) |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
_____________________________________________________________________________
Figure 9-3. Program pasex9: Using BULK INSERT (page 12 of 12)