HP 3000 Manuals HP 3000 Manuals
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;
__________________________________________________________________________________
| |
| (* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *) |
| (* 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. *)|
| (*************************************************************************)|
| 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; |
__________________________________________________________________________________
Figure 13-1. Sample Program Converting Column from CHAR to DATE
___________________________________________________________________________________
| |
| 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 *) |
| |
| |
| |
___________________________________________________________________________________
Figure 13-1. Sample Program Converting Column from CHAR to DATE (page 2 of 8)
___________________________________________________________________________________
| |
| (*************************************************************************) |
| (* 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 '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; |
| |
| |
| |
___________________________________________________________________________________
Figure 13-1. Sample Program Converting Column from CHAR to DATE (page 3 of 8)
___________________________________________________________________________________
| |
| 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 *) |
| |
| |
| |
___________________________________________________________________________________
Figure 13-1. Sample Program Converting Column from CHAR to DATE (page 4 of 8)
___________________________________________________________________________________
| |
| (**************************************************************************)|
| (* 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; |
___________________________________________________________________________________
Figure 13-1. Sample Program Converting Column from CHAR to DATE (page 5 of 8)
___________________________________________________________________________________
| |
| (* 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; |
___________________________________________________________________________________
Figure 13-1. Sample Program Converting Column from CHAR to DATE (page 6 of 8)
___________________________________________________________________________________
| |
| 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; |
| |
| |
| |
| |
___________________________________________________________________________________
Figure 13-1. Sample Program Converting Column from CHAR to DATE (page 7 of 8)
__________________________________________________________________________________
| |
| 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 *) |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
__________________________________________________________________________________
Figure 13-1. Sample Program Converting Column from CHAR to DATE (page 8 of 8)