Program Example for Date/Time Data

The next data conversion program is intended as a guide should you decide to convert any character (CHAR) columns in an existing table to a date/time data type.

Before running this program, you must create a new table, PurchDB.NewOrders, in PartsDBE. This table is similar to the PurchDB.Orders table already existing in PartsDBE, except that the OrderDate column is of the DATE data type. You can create the table by issuing the following command from ISQL:

   CREATE PUBLIC TABLE PurchDB.NewOrders

                       (OrderNumber   INTEGER NOT NULL,

                       VendorNumber  INTEGER,

                       OrderDate     DATE)

                    IN OrderFS;

Figure 13-1 Sample Program Converting Column from CHAR to DATE

(* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *)

(* This program uses BULK FETCH and BULK INSERT commands to select all *)

(* rows from the Orders table (part of the sample DBEnvironment,       *) 

(*PartsDBE), convert the order date column from the CHAR data type to  *) 

(*the DATE data type default format, and write all Orders table        *) 

(*information to another table called NewOrders table (created         *) 

(*previously by you as described in this chapter).                     *)

(* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *)



Program pasex9a(input, output);



const

    OK           =      0;

    NotFound     =    100;

    DeadLock     = -14024;

    NoMemory     =  -4008; 



var 



          (* Begin Host Variable Declarations *)

    EXEC SQL BEGIN DECLARE SECTION;



(*************************************************************************)

(* Arrays are NOT packed, although elements within the arrays can be.    *)

(* This prevents possible data allignment problems on Series 300 systems.*)

(*************************************************************************)

    Orders                : array[1..25]

      of record

        OrderNumber       : integer;

        VendorNumber      : integer;

        VendorNumberInd   : sqlind;

        OrderDate         : packed array[1..8] of char;

        OrderDateInd      : sqlind;

      end;

    StartIndex            : SmallInt;

    NumberOfRows          : SmallInt;



    NewOrders             : array[1..25]

      of record

        NewOrderNumber    : integer;

        NewVendorNumber   : integer;

        NewVendorNumberInd : sqlind;

        NewOrderDate      : packed array[1..10] of char;

        NewOrderDateInd   : sqlind;

      end;

    StartIndex2           : SmallInt;

    NumberOfRows2         : SmallInt;




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

    EXEC SQL END DECLARE SECTION;

          (* End Host Variable Declarations *)



    SQLCA : SQLCA_type;   (* SQL Communication Area *)



    DoneConvert           : boolean;

    OrdersOK              : boolean;

    Abort                 : boolean;



    counter1              : integer;

   

(**************************************************************************)

(*                      Procedure to release PartsDBE.                    *)

(**************************************************************************)



procedure TerminateProgram;   (* Procedure to Release PartsDBE *)

begin



EXEC SQL RELEASE;



end;  (* End TerminateProgram Procedure *)



(**************************************************************************)

(* Procedure to display error messages and terminate the program when the *)

(* transaction has been rolled back by ALLBASE/SQL.                       *)

(**************************************************************************)



procedure SQLStatusCheck;  (* Procedure to Display Error Messages *)

begin



Abort := FALSE;



if SQLCA.SQLCODE <= DeadLock then Abort := TRUE;

if SQLCA.SQLCODE = NoMemory then Abort := TRUE;



repeat

   EXEC SQL SQLEXPLAIN :SQLMessage;

   writeln(SQLMessage);

until SQLCA.SQLCODE = 0;

if Abort then TerminateProgram;



end;  (* End SQLStatusCheck Procedure *)






(*************************************************************************)

(* The cursor for the BULK FETCH is declared in a function that is never *)

(* executed at run time.  The section for this cursor is created and     *)

(* stored in the program module at preprocess time.                      *)

(*************************************************************************)



procedure DeclareCursor;    

                                   

begin

  EXEC SQL DECLARE OrdersCursor

               CURSOR FOR

		   SELECT * 

	             FROM PurchDB.Orders; 

end;



(**************************************************************************)

(* Function to connect to the sample database environment, PartsDBE.      *)

(**************************************************************************)



function ConnectDBE: boolean;  

begin



writeln('Connect to PartsDBE');

EXEC SQL CONNECT TO '../sampledb/PartsDBE';



ConnectDBE := TRUE;

if SQLCA.SQLCODE <> OK then

  begin



  ConnectDBE := FALSE;

  SQLStatusCheck;



  end;  (* End if *)

end;  (* End of ConnectDBE Function *)



(**************************************************************************)

(* Procedure to begin the transaction with cursor stability specified.    *)

(**************************************************************************)



procedure BeginTransaction;             

begin



EXEC SQL BEGIN WORK CS;  






if SQLCA.SQLCODE <> OK then

  begin



  SQLStatusCheck;

  TerminateProgram;



  end;



end;  (* End BeginTransaction Procedure *)



(**************************************************************************)

(* Procedure to commit work to the database OR save the cursor position.  *)

(**************************************************************************)



procedure CommitWork;  

begin



writeln('Commit Work');

EXEC SQL COMMIT WORK;

if SQLCA.SQLCODE <> OK then

  begin

  SqlStatusCheck;

  TerminateProgram;

  end;



end;  (* End CommitWork Procedure *)



(**************************************************************************)

(* Procedure to rollback the transaction.                                 *)

(**************************************************************************)



procedure RollBackWork;  

begin



writeln('Rollback Work');

EXEC SQL ROLLBACK WORK;

if SQLCA.SQLCODE <> OK then

  begin

  SqlStatusCheck;

  TerminateProgram;

  end;



end;  (* End RollBackWork Procedure *)






(**************************************************************************)

(* Procedure to BULK INSERT into PurchDB.NewOrders table.                 *)

(**************************************************************************)



procedure InsertNew;  

begin



NumberOfRows2 := counter1; 

StartIndex2   := 1;

 

writeln('BULK INSERT INTO  PurchDB.NewOrders');



EXEC SQL BULK INSERT INTO  PurchDB.NewOrders

                   VALUES (:NewOrders,

                           :StartIndex2,

                           :NumberOfRows2);

case SQLCA.SQLCODE of

OK       : ; 



Otherwise   begin

	      SQLStatusCheck;

	      RollBackWork;

	      OrdersOK := FALSE;      

              DoneConvert := TRUE;

            end;



end;     (* case *)

end;                 (* End of Procedure InsertNew *)

(**************************************************************************)

(* Procedure to convert OrderDate from CHAR to DATE data type and transfer*)

(* data to an array in preparation for BULK INSERT into a new table.      *)

(**************************************************************************)



   procedure TransferData;  

                            

   var



    i,j:integer;



   begin

 

   NumberOfRows := counter1;



   for i := 1 to NumberOfRows do 

   begin

     NewOrders[i].NewOrderNumber := Orders[i].OrderNumber;

     NewOrders[i].NewVendorNumber := Orders[i].VendorNumber;

   end;




                            (* Convert Date *)

   for i := 1 to NumberOfRows do

   begin

      for j := 1 to 4 do

         begin

            NewOrders[i].NewOrderDate[j] := Orders[i].OrderDate[j];

         end;

      NewOrders[i].NewOrderDate[5] := '-';

      for j := 6 to 7 do

        NewOrders[i].NewOrderDate[j] := Orders[i].OrderDate[j-1];

        NewOrders[i].NewOrderDate[8] := '-';

      for j := 9 to 10 do

        NewOrders[i].NewOrderDate[j] := Orders[i].OrderDate[j-2];

   end;



end;   (* End of Procedure TransferData *)



(**************************************************************************)

(* Procedure to BULK FETCH Orders table data 25 rows at a time            *) 

(* into an array.                                                         *)

(**************************************************************************)



procedure FetchOld;  



begin;



NumberOfRows := 25;

StartIndex  := 1;



writeln('BULK FETCH PurchDB.Orders');



EXEC SQL BULK FETCH OrdersCursor 

	       INTO :Orders, :StartIndex, :NumberOfRows;

	     

counter1 := SQLCA.SQLERRD[3];



case SQLCA.SQLCODE of

OK       :  begin

	      CommitWork;               (* SAVE THE CURSOR POSITION *)

              end;                      (* Used in conjunction with *)

                                        (* cursor stability.        *) 

NotFound :  begin   

              CommitWork;

	      writeln;

	      writeln('There are no Orders Table rows to FETCH.');

              DoneConvert := TRUE;

            end;


Otherwise   begin

	      SQLStatusCheck;

	      RollBackWork;

	      OrdersOK := FALSE;

              DoneConvert := TRUE;

            end;



end;     (* case *)



if not DoneConvert then

TransferData;



if not DoneConvert then

InsertNew;



end;     (* End of procedure FetchOld *)



(**************************************************************************)

(*                          Beginning of program.                         *)

(**************************************************************************)



begin  



writeln('Program to convert date from CHAR to DATE data type.');

writeln('Event List:');

writeln('  Connect to PartsDBE');

writeln('  BULK FETCH all rows from Orders Table.');

writeln('  Convert the date.');

writeln('  BULK INSERT all fetched rows into NewOrders Table' );

writeln('  with converted date.');

writeln('  Release PartsDBE');

writeln;





if ConnectDBE then



   begin



   DoneConvert := FALSE;

   OrdersOK := TRUE;



   BeginTransaction;

   EXEC SQL OPEN OrdersCursor KEEP CURSOR WITH LOCKS;




   if SQLCA.SQLCODE <> OK then

      begin

      SQLStatusCheck;

      RollBackWork;

      OrdersOK := FALSE;

      DoneConvert := TRUE;

      end;



   repeat

     FetchOld

   until DoneConvert;    (* DoneConvert is TRUE when all data has been   *)

                         (* converted and inserted or when an error      *)

                         (* condition not serious enough for ALLBASE/SQL *)

                         (* to rollback work was encountered.            *)



if OrdersOK then         (* If there were no errors in processing, data  *)

   CommitWork;           (* is committed to the database.                *) 



   end;



TerminateProgram;



end.  (* End of Program *)

Program Example for Date/Time Data

Technical documentation

» Table of Contents

» Index