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