HP 3000 Manuals

ALLBASE/SQL Advanced Application Programming Guide

Programming with Dynamic Parameters (contd.) 

Using a BULK INSERT Statement with Dynamic Parameters (contd.) 

Example in Pascal Using a BULK INSERT.   

         :

     Define a host variable array to hold dynamic parameter values.  Be sure each 
     host variable data type matches (or is compatible with) its ALLBASE/SQL default 
     data type: 

         EXEC SQL BEGIN DECLARE SECTION;

         NewOrders              : array[1..25]
           of record
             NewOrderNumber     : integer;
             NewVendorNumber    : integer;
             NewVendorNumberInd : sqlind;
             NewOrderDate       : packed array[1..10] of char;
             NewOrderDateInd    : sqlind;
           end;

     If the dynamic parameter represents data for a column that can contain nulls, and 
     it is possible that input data will contain null values, be sure to define a null 
     indicator host variable immediately following the related host variable. 

     If you are using other than the default values for the starting index and number of 
     rows in the array, define host variables for these as well: 

         StartIndex            : SmallInt;
         NumberOfRows          : SmallInt;      (* Maximum possible rows to bulk *)
                                                (* insert. *)

         OrderNumber           : integer;       (* Host variables for user input.*)
         VendorNumber          : integer;
         VendorNumberInd       : sqlind;
         OrderDate             : packed array[1..10] of char;
         OrderDateInd          : sqlind;

         :

         SQLMessage            : packed array[1..132] of char;

         EXEC SQL END DECLARE SECTION;

         sqlca                 : SQLCA_type;    (* SQL Communication Area *)

         OrdersOK              : boolean;

         :

     Use the PREPARE statement to preprocess the dynamic statement, in this case, from 
     a string: 

     procedure PrepareIt;
     begin

      EXEC SQL PREPARE CMD from 'BULK INSERT INTO PurchDB.Orders VALUES (?,?,?);'; 

     if SQLCA.SQLCODE
     OK then         (* Check for processing errors.           *)
       begin
       SQLStatusCheck;
       RollBackWork;
       OrdersOK := FALSE;
       end;

     end;                               (* End PrepareIt Procedure.               *)

     Load up to 25 rows of new orders for the BULK INSERT.  This data could originate 
     from an interactive user or from a file: 

     procedure CreateOrders;

     var
     i:integer;

     begin

     NumberOfRows := 25;
     StartIndex   := 1;

     Count rows as they are loaded into the NewOrders array up to a maximum of 25: 

     for i := 1 to NumberOfRows do
        begin

        Read a file record or accept a row of data from the user into 
        the appropriate host variables. 

        Load host variable data into the bulk insert array. 

        NewOrders[i].NewOrderNumber := OrderNumber;
        NewOrders[i].NewVendorNumber := VendorNumber;
        NewOrders[i].NewVendorNumberInd := VendorNumberInd;
        NewOrders[i].NewOrderDate := OrderDate;
        NewOrders[i].NewOrderDateInd := OrderDateInd;

        end;                           (* End for.                                *)
     end;                              (* End procedure CreateOrders.             *)

     Execute the prepared CMD command specifying the array where data for the BULK
     INSERT is located: 

     procedure InsertNew;
     begin

      EXEC SQL EXECUTE CMD USING :NewOrders, :StartIndex, :NumberOfRows; 

     if SQLCA.SQLCODE
     OK then         (* Check for processing errors.           *)
       begin
       SQLStatusCheck;
       RollBackWork;
       OrdersOK := FALSE;
       end;
     end;                               (* End of procedure InsertNew.            *)

     :

     begin                              (* Begin the program.                     *)

     if ConnectDBE then                 (* If the application is successfully     *)
                                        (* connected to a DBEnvironment, proceed. *)
        begin

        OrdersOK := TRUE;

        BeginTransaction;
        PrepareIt;
        CreateOrders;
        InsertNew;

     if OrdersOK then                   (* If there were no errors in processing, *)
        CommitWork;                     (* data is committed to the database.     *)
        end;

     TerminateProgram;

     end.                               (* End the Program.                       *)

MPE/iX 5.0 Documentation