HP 3000 Manuals HP 3000 Manuals
Program Example for Date/Time Data
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
___________________________________________________________________________________
| |
| /* Program cex9a */ |
| |
| /** * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */|
| /*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; |
___________________________________________________________________________________
Figure 11-1. Program cex9a: Using Date/Time Functions
___________________________________________________________________________________
| |
| 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 */ |
___________________________________________________________________________________
Figure 11-1. Program cex9a: Using Date/Time Functions (2 of 7)
___________________________________________________________________________________
| |
| /*************************************************************************/ |
| /* 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 */ |
___________________________________________________________________________________
Figure 11-1. Program cex9a: Using Date/Time Functions (3 of 7)
__________________________________________________________________________________
| |
| /*************************************************************************/|
| /* 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 'PartsDBE'; |
| |
| rv = TRUE; |
| if (sqlca.sqlcode != OK){ |
| rv = FALSE; |
__________________________________________________________________________________
Figure 11-1. Program cex9a: Using Date/Time Functions (4 of 7)
__________________________________________________________________________________
| |
| 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); |
__________________________________________________________________________________
Figure 11-1. Program cex9a: Using Date/Time Functions (5 of 7)
__________________________________________________________________________________
| |
| 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 */ |
__________________________________________________________________________________
Figure 11-1. Program cex9a: Using Date/Time Functions (6 of 7)
___________________________________________________________________________________
| |
| /**************************************************************************/|
| /*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 */ |
| |
___________________________________________________________________________________
Figure 11-1. Program cex9a: Using Date/Time Functions (7 of 7)