Program Using BULK INSERT

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)
VendorNumber
OrderDate

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)
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 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

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 *)

Program Using BULK INSERT

Technical documentation

» Table of Contents

» Index