 |
» |
|
|
|
The example program shown in Figure 11-1 is based on the
manufacturing database and the purchasing database that are a part of the
sample database environment, PartsDBE. (Reference the ALLBASE/SQL Reference Manual , appendix C.) The program shows how to convert a column data type from CHAR to DATE.
Informative comments and explanations are present throughout the listing. Example Program cex9a | |
Figure 11-1 Program cex9a: Using Date/Time Functions
/** * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
/*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). */
/** * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
#include <stdio.h>
typedef int boolean;
boolean DoneConvert;
boolean OrdersOK;
boolean Abort;
boolean ConnectDBE();
int counter1;
#define OK 0
#define NotFound 100
#define DeadLock -14024
#define NoMemory -4008
#define FALSE 0
#define TRUE 1
sqlca_type sqlca; /* SQL Communication Area */
/* Begin Host Variable Declarations */
EXEC SQL BEGIN DECLARE SECTION;
struct {
int OrderNumber;
int VendorNumber;
sqlind VendorNumInd;
char OrderDate[9]; /* Add a byte for end of char array. */
sqlind OrderDateInd;
} Orders[25];
short StartIndex;
short NumberOfRows;
struct {
int NewOrderNumber;
int NewVendorNumber;
sqlind NewVendorNumInd;
char NewOrderDate[11]; /*Add a byte for end of char array.*/
sqlind NewOrderDateInd;
} NewOrders[25];
short StartIndex2;
short NumberOfRows2;
char SQLMessage[133]; /*Add a byte for end of char array.*/
EXEC SQL END DECLARE SECTION;
/* End Host Variable Declarations */
/**************************************************************************/
/*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. */
/**************************************************************************/
boolean DeclareCursor(){
EXEC SQL DECLARE OrdersCursor
CURSOR FOR
SELECT *
FROM PurchDB.Orders;
}
/*************************************************************************/
/*Function to rollback the transaction. */
/*************************************************************************/
int RollBackWork(){
printf("Rollback Work\n");
EXEC SQL ROLLBACK WORK;
if (sqlca.sqlcode != OK){
SQLStatusCheck();
TerminateProgram();
}
} /* End RollBackWork Function */
/*************************************************************************/
/* Beginning of program. */
/*************************************************************************/
main() {
printf("Program to convert date from CHAR to DATE data type.\n");
printf("Event List:\n");
printf(" Connect to PartsDBE\n");
printf(" BULK FETCH all rows from Orders Table.\n");
printf(" Convert the date.\n");
printf(" BULK INSERT all fetched rows into NewOrders Table \n");
printf(" with converted date.\n");
printf(" Release PartsDBE\n\n");
if (ConnectDBE()) {
DoneConvert = FALSE;
OrdersOK = TRUE;
BeginTransaction();
EXEC SQL OPEN OrdersCursor KEEP CURSOR WITH LOCKS;
if (sqlca.sqlcode != OK) {
SQLStatusCheck();
RollBackWork();
OrdersOK = FALSE;
DoneConvert = TRUE;
}
do {
FetchOld();
} while (! 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) /* If there were no errors in processing, data */
CommitWork(); /* is committed to the database. */
TerminateProgram();
} /* END if */
} /* End of Main Program */
/*************************************************************************/
/* Function to release PartsDBE. */
/*************************************************************************/
int TerminateProgram() /* Function to Release PartsDBE */
{
EXEC SQL RELEASE;
} /* End TerminateProgram Function */
/*************************************************************************/
/*Function to display error messages and terminate the program when the */
/*transaction has been rolled back by ALLBASE/SQL. */
/*************************************************************************/
int SQLStatusCheck() /* Function to Display Error Messages */
{
Abort = FALSE;
if (sqlca.sqlcode <= DeadLock) Abort = TRUE;
if (sqlca.sqlcode = NoMemory) Abort = TRUE;
do {
EXEC SQL SQLEXPLAIN :SQLMessage;
printf(SQLMessage);
} while (sqlca.sqlcode != 0);
if (Abort) TerminateProgram();
} /* End SQLStatusCheck Function */
/*************************************************************************/
/*Function to connect to the sample database environment, PartsDBE. */
/*************************************************************************/
boolean ConnectDBE(){
boolean rv; /* return value */
printf("Connect to PartsDBE\n");
EXEC SQL CONNECT TO '../sampledb/PartsDBE';
rv = TRUE;
if (sqlca.sqlcode != OK){
rv = FALSE;
SQLStatusCheck();
} /* End if */
return(rv);
} /* End of ConnectDBE Function */
/*************************************************************************/
/*Function to begin the transaction with cursor stability specified. */
/*************************************************************************/
int BeginTransaction(){
EXEC SQL BEGIN WORK CS;
if (sqlca.sqlcode != OK){
SQLStatusCheck();
TerminateProgram();
}
} /* End BeginTransaction Function */
/*************************************************************************/
/*Function to commit work to the database OR save the cursor position. */
/*************************************************************************/
int CommitWork(){
printf("Commit Work\n");
EXEC SQL COMMIT WORK;
if (sqlca.sqlcode != OK){
SQLStatusCheck();
TerminateProgram();
}
} /* End CommitWork Function */
/*************************************************************************/
/*Function to BULK INSERT into PurchDB.NewOrders table. */
/*************************************************************************/
int InsertNew(){
NumberOfRows2 = counter1;
StartIndex2 = 0;
printf("BULK INSERT INTO PurchDB.NewOrders\n");
EXEC SQL BULK INSERT INTO PurchDB.NewOrders
VALUES (:NewOrders,
:StartIndex2,
:NumberOfRows2);
switch (sqlca.sqlcode){
case OK: break;
default: SQLStatusCheck();
RollBackWork();
OrdersOK = FALSE;
DoneConvert = TRUE;
} /* switch */
} /* End of Function InsertNew */
/*************************************************************************/
/*Function to convert OrderDate from CHAR to DATE data type and transfer */
/*data to an array in preparation for BULK INSERT into a new table. */
/*************************************************************************/
int TransferData()
{
int i,j;
NumberOfRows = counter1;
for (i = 0; i <= NumberOfRows; i){
NewOrders[i].NewOrderNumber = Orders[i].OrderNumber;
NewOrders[i].NewVendorNumber = Orders[i].VendorNumber;
}
/* Convert Date */
for (i = 0; i <= NumberOfRows; i){
for (j = 0; j < 4; j){
NewOrders[i].NewOrderDate[j] = Orders[i].OrderDate[j];
}
NewOrders[i].NewOrderDate[4] = '-';
for (j = 5; j < 7; j)
NewOrders[i].NewOrderDate[j] = Orders[i].OrderDate[j-1];
NewOrders[i].NewOrderDate[7] = '-';
for (j = 8; j < 10; j)
NewOrders[i].NewOrderDate[j] = Orders[i].OrderDate[j-2];
}
} /* End of Function TransferData */
/**************************************************************************/
/*Function to BULK FETCH Orders table data 25 rows at a time into an array*/
/**************************************************************************/
int FetchOld()
{
NumberOfRows = 25;
StartIndex = 0;
printf("BULK FETCH PurchDB.Orders\n");
EXEC SQL BULK FETCH OrdersCursor
INTO :Orders, :StartIndex, :NumberOfRows;
counter1 = sqlca.sqlerrd[2]; /* Set counter1 to number of rows fetched.*/
switch (sqlca.sqlcode){
case OK: CommitWork(); /* SAVE THE CURSOR POSITION */
break; /* Used in conjunction with */
/* cursor stability. */
case NotFound: CommitWork();
printf("\nThere are no Orders Table rows to FETCH.\n");
DoneConvert = TRUE;
break;
default: SQLStatusCheck();
RollBackWork();
OrdersOK = FALSE;
DoneConvert = TRUE;
} /* switch */
if (! DoneConvert)
TransferData();
if (! DoneConvert)
InsertNew();
} /* End of Function FetchOld */
|
|
