|
|
|
ALLBASE/SQL C Application Programming Guide: HP 3000 MPE/iX Computer Systems > Chapter 7 BULK Table Processing
Sample Program Using BULK Processing |
|
The flow chart in Figure 7-1 summarizes the functionality of program cex9. This program creates orders in the sample DBEnvironment, PartsDBE. Each order is placed with a specific vendor, to obtain one or more parts supplied by that vendor. An order consists of a row in table PurchDB.Orders, which comprises the order header: OrderNumber (defined NOT NULL) VendorNumber OrderDate An order usually consists of one or more line items, represented by one or more rows in the table PurchDB.OrderItems: OrderNumber (defined NOT NULL) ItemNumber (defined NOT NULL) VendPartNumber PurchasePrice (defined NOT NULL) OrderQty ItemDueDate ReceivedQty Program cex9 uses a simple INSERT command to create the order header and, optionally, a BULK INSERT command to insert line items. The runtime dialog for program cex9 appears in Figure 7-2, and the source code in Figure 7-3. To establish a DBE session 54 , cex9 executes function ConnectDBE. This function evaluates to TRUE when the CONNECT command 5 is successfully executed. The program then executes function CreateOrder until the Done flag is set to TRUE 55 . Function CreateOrder prompts for a vendor number or a zero (0) to stop the program 48 . When the user enters a zero, Done is set to TRUE 53 and the program terminates. When the user enters a vendor number, program cex9:
To validate the vendor number, function ValidateVendor is executed 49 . Function ValidateVendor starts a transaction by invoking function BeginTransaction 9 , which executes the BEGIN WORK command 6 . Then a SELECT command 10 is processed to determine whether the vendor number exists in column VendorNumber of table PurchDB.Vendors:
If the vendor number is valid, program cex9 invokes function CreateHeader to create the order header 50 . The order header consists of a row containing the vendor number entered, plus two values computed by the program: OrderNumber and OrderDate. Function CreateHeader starts a transaction 34 , then obtains an exclusive lock on table PurchDB.Orders 35 . Exclusive access to this table ensures that when the row is inserted, no row having the same number will have been inserted by another transaction. The unique index that exists on column OrderNumber prevents duplicate order numbers in table PurchDB.Orders. Therefore an INSERT operation fails if it attempts to insert a row having an order number with a value already in column OrderNumber. In this case, the exclusive lock does not threaten concurrency. No operations conducted between the time the lock is obtained and the time it is released involve operator intervention:
To create line items, function CreateOrder executes function CreateOrderItems until the DoneItems flag is set to TRUE 51 . Function CreateOrderItems prompts for whether the user wants to specify line items 44 . If the user wants to create line items, function CreateOrderItems executes function ItemEntry until the DoneItems flag is set to TRUE 46 , then executes function BulkInsert 47 :
If the user does not want to create line items, function CreateOrderItems displays the order header by invoking function DisplayHeader 45 . Function DisplayHeader displays the row 18 that was created earlier in function CreateHeader 50 and inserted into PurchDB.Orders in the function InsertRow 33 . If line items were successfully inserted into the PurchDB.OrderItems table in function BulkInsert, then function CreateOrder invokes function DisplayOrder 52 to display the order created. Function DisplayOrder invokes function DisplayHeader 20 to display the order header. Then it executes function DisplayItems 21 to display each row inserted into the PurchDB.OrderItems table. Function DisplayItems displays values from the structure array OrderItems 19 . When the program user enters a 0 in response to the vendor number prompt in function CreateOrder, Done is set to TRUE 53 and the program terminates by executing function ReleaseDBE 56 , which executes the RELEASE command 3 . Figure 7-2 Runtime Dialog of Program cex9
Program to Create an Order - cex9
Event List:
CONNECT TO PartsDBE
Prompt for VendorNumber
Validate VendorNumber
BEGIN WORK
INSERT a row into PurchDB.Orders
Prompt for a line item
Validate the Vendor Part Number for each line item
BULK INSERT rows into PurchDB.OrderItems
Repeat the above six steps until the user enters a 0
RELEASE the DBEnvironment
Connect to PartsDBE
Enter a Vendor Number or a 0 to STOP> 9015
Begin Work
Validating VendorNumber
Commit Work
Begin Work
LOCK the PurchDB.Orders table.
Calculating OrderNumber
Calculating OrderDate
INSERT into PurchDB.Orders
Commit Work
Do you want to specify line items (Y/N)?> y
You can specify as many as 25 line items.
Enter data for ItemNumber: 1
Vendor Part Number > 9040
Begin Work
Validating VendPartNumber
Commit Work
Purchase Price > 1500
Order Quantity > 5
Item Due Date (yyyymmdd) > 19860630
Do you want to specify another line item (Y/N)? > y
You can specify as many as 25 line items.
Enter data for ItemNumber: 2
Vendor Part Number > 9055
Begin Work
Validating VendPartNumber
Commit Work
The vendor has no part with the number
you specified!
Do you want to specify another line item (Y/N)? > y
You can specify as many as 25 line items.
Enter data for ItemNumber: 2
Vendor Part Number > 9050
Begin Work
Validating VendPartNumber
Commit Work
Purchase Price > 345
Order Quantity > 2
Item Due Date (yyyymmdd) > 19860801
Do you want to specify another line item (Y/N)?> n
Begin Work
BULK INSERT into PurchDB.OrderItems
Commit Work
The following order has been created:
Order Number: 30524
Vendor Number: 9015
Order Date: 19860603
Item Number: 1
Vendor Part Number: 9040
Purchase Price: 1500.00
Order Quantity: 5
Item Due Date: 19860630
Received Quantity: is NULL
Item Number: 2
Vendor Part Number: 9050
Purchase Price: 345.00
Order Quantity: 2
Item Due Date: 19860801
Received Quantity: is NULL
Enter a Vendor Number or a 0 to STOP > 0
Release PartsDBE
Figure 7-3 Program cex9: Using BULK INSERT
/* Program cex9 */
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
/* This program illustrates the use of SQL's BULK INSERT */
/* command to insert mulitple rows or tuples at a time. */
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
typedef int boolean;
boolean Abort;
boolean Done;
boolean DoneItems;
boolean VendorOK;
boolean HeaderOK;
boolean PartOK;
boolean ItemsOK;
char response[2];
int counter1;
int counter2;
int i,j,k;
long sec;
char *Date;
#include <stdio.h>
#include <time.h>
#define OK 0
#define NotFound 100
#define MultipleRows -10002
#define DeadLock -14024
#define FALSE 0
#define TRUE 1
sqlca_type sqlca; /* SQL Communication Area */
/* Begin Host Variable Declarations */
EXEC SQL BEGIN DECLARE SECTION;
int OrderNumber1;
int VendorNumber;
char OrderDate[9];
char PartSpecified[17];
int MaxOrderNumber;
short StartIndex;
short NumberOfRows;
struct {
int OrderNumber2;
int ItemNumber;
char VendPartNumber[17];
double PurchasePrice;
short OrderQty;
char ItemDueDate[9];
short ReceivedQty;
sqlind ReceivedQtyInd;
} OrderItems[25]; 1
char SQLMessage[133];
EXEC SQL END DECLARE SECTION;
/* End Host Variable Declarations */
int SystemDate() /* Function to get the system date */ 32
{
sec = time(0);
printf("\n Calculating OrderDate");
ptr = localtime(&sec);
sprintf(OrderDate, "%2s%2.2d%2.2d%2.2d",
"19",ptr->tm_year,++ptr->tm_mon,ptr->tm_mday);
} /* End of SystemDate Function */
int SQLStatusCheck() /* Function to Display Error Messages */ 13
{
Abort = FALSE;
if (sqlca.sqlcode < DeadLock) Abort = TRUE;
do {
EXEC SQL SQLEXPLAIN :SQLMessage; 4
printf("\n");
printf("%s\n",SQLMessage);
} while (sqlca.sqlcode != 0);
if (Abort) {
ReleaseDBE();
}
} /* End SQLStatusCheck Function */
boolean ConnectDBE() /* Function to Connect to PartsDBE */
54
{
boolean ConnectDBE;
printf("\n Connect to PartsDBE");
EXEC SQL CONNECT TO 'PartsDBE'; 5
ConnectDBE = TRUE;
if (sqlca.sqlcode != OK) {
ConnectDBE = FALSE;
SQLStatusCheck();
} /* End if */
return (ConnectDBE);
} /* End of ConnectDBE Function */
int ReleaseDBE() /* Function to Release PartsDBE */ 56
{
printf("\n Release PartsDBE");
EXEC SQL RELEASE; 3
printf("\n");
Done = TRUE;
if (sqlca.sqlcode != OK)
SQLStatusCheck();
} /* End ReleaseDBE Function */
boolean BeginTransaction() /* Function to Begin Work */ 9
{
boolean BeginTransaction;
printf("\n");
printf("\n Begin Work");
EXEC SQL BEGIN WORK; 6
if (sqlca.sqlcode != OK) {
SQLStatusCheck();
ReleaseDBE();
}
} /* End BeginTransaction Function */
int EndTransaction() /* Function to Commit Work */ 11
{
printf("\n");
printf("\n Commit Work");
EXEC SQL COMMIT WORK; 7
if (sqlca.sqlcode != OK) {
SQLStatusCheck();
}
} /* End EndTransaction Function */
int RollBackWork() /* Function to RollBack work */ 28
{
printf("\n");
printf("\n RollBack Work");
EXEC SQL ROLLBACK WORK; 8
if (sqlca.sqlcode != OK) {
SQLStatusCheck();
ReleaseDBE();
}
} /* End of RollBack Function */
int ValidateVendor() /* Function that ensures vendor number is valid */
{ 49
BeginTransaction(); 9
printf("\n Validating VendorNumber");
EXEC SQL SELECT VendorNumber 10
INTO :VendorNumber
FROM PurchDB.Vendors
WHERE VendorNumber = :VendorNumber;
switch (sqlca.sqlcode) {
case OK: VendorOK = TRUE;
EndTransaction(); 11
break;
case NotFound: EndTransaction();
printf("\n");
printf("\n No vendor has the VendorNumber you");
printf("\n specified!"); 12
VendorOK = FALSE;
HeaderOK = FALSE;
ItemsOK = FALSE;
break;
default: SQLStatusCheck(); 13
EndTransaction();
VendorOK = FALSE;
HeaderOK = FALSE;
ItemsOK = FALSE;
break;
} /* End switch */
} /* End ValidateVendor Function */
\mf="Function"
int ValidatePart() /* Function that ensures vendor part number is valid */
{ 39
BeginTransaction(); 14
sscanf(OrderItems[i].VendPartNumber,"%s",PartSpecified);
printf("\n Validating VendPartNumber");
EXEC SQL SELECT VendPartNumber 15
INTO :PartSpecified
FROM PurchDB.SupplyPrice
WHERE VendorNumber = :VendorNumber
AND VendPartNumber = :PartSpecified;
switch (sqlca.sqlcode) {
case OK: EndTransaction(); 16
PartOK = TRUE;
break;
case NotFound: EndTransaction(); 17
printf("\n");
printf("\n The vendor has no part with the number"
printf("\n you specified!");
PartOK = FALSE;
break;
default: SQLStatusCheck();
EndTransaction();
PartOK = FALSE;
break;
} /* End switch */
} /* End ValidatePart Function */
int DisplayHeader() /* Function to Display row from PurchDB.Orders */
{ 45
printf("\n");
printf("\n The following order has been created.");
printf("\n");
printf("Order Number: %d\n", OrderNumber1); 18
printf("Vendor Number: %d\n", VendorNumber);
printf("Order Date: %s\n", OrderDate);
} /* End of DisplayRow Function */
int DisplayItems() /* Function to Display Rows from PurchDB.OrderItems */
{ 21
j = counter2;
printf("\n"); 19
printf("Item Number: %d\n", OrderItems[j].ItemNumber);
printf("Vendor Part Number: %s\n", OrderItems[j].VendPartNumber);
printf("Purchase Price: %10.2f\n", OrderItems[j].PurchasePrice)
printf("Order Quantity: %d\n", OrderItems[j].OrderQty);
printf("Item Due Date: %s\n", OrderItems[j].ItemDueDate);
printf("Received Quantity: is NULL \n");
counter2 = j + 1;
} /* End of DisplayRow */
int DisplayOrder() /* Function to Display Order Created */ 52
{
DisplayHeader(); 20
printf("\n");
i = counter1;
counter2 = 1;
do {
DisplayItems(); 21
j = j + 1;
} while (j < i);
} /* End of DisplayOrder Function */
int InsertRow() /* Function to insert row in PurchDB.Orders */ 33
{
printf("\n");
printf("\n INSERT into PurchDB.Orders");
EXEC SQL INSERT INTO PurchDB.Orders 22
(OrderNumber,
VendorNumber,
OrderDate)
VALUES (:OrderNumber1,
:VendorNumber,
:OrderDate);
if (sqlca.sqlcode != 0) {
SQLStatusCheck();
EndTransaction();
HeaderOK = FALSE; 23
}
else {
EndTransaction();
HeaderOK = TRUE; 24
}
} /* End of InsertRow Function */
int BulkInsert() /* Function to bulk insert into PurchDB.OrderItems */
{ 47
BeginTransaction(); 25
NumberOfRows = counter1; 26
StartIndex = 1;
printf("\n BULK INSERT into PurchDB.OrderItems");
EXEC SQL BULK INSERT INTO PurchDB.OrderItems 27
(OrderNumber,
ItemNumber,
VendPartNumber,
PurchasePrice,
OrderQty,
ItemDueDate,
ReceivedQty)
VALUES (:OrderItems,
:StartIndex,
:NumberOfRows);
if (sqlca.sqlcode != 0) {
SQLStatusCheck();
RollBackWork(); 28
ItemsOK = FALSE;
}
else {
EndTransaction(); 29
ItemsOK = TRUE;
}
} /* End of BulkInsert Function */
int ComputeOrderNumber() /* Function to assign a number to an order */
{ 36
EXEC SQL SELECT MAX(OrderNumber) 30
INTO :MaxOrderNumber
FROM PurchDB.Orders;
if (sqlca.sqlcode != 0) {
SQLStatusCheck();
EndTransaction();
HeaderOK = FALSE;
}
else {
printf("\n Calculating OrderNumber");
OrderNumber1 = MaxOrderNumber + 1; 31
SystemDate(); 32
InsertRow(); 33
}
} /* End ComputeOrderNumber Function */
int CreateHeader() /* Function to create order header */ 50
{
BeginTransaction(); 34
printf("\n LOCK the PurchDB.Orders table.");
EXEC SQL LOCK TABLE PurchDB.Orders IN EXCLUSIVE MODE; 35
if (sqlca.sqlcode != 0) {
SQLStatusCheck();
EndTransaction();
HeaderOK = FALSE;
}
else
ComputeOrderNumber(); 36
} /* End CreateHeader Function */
int ItemEntry() /* Function to put line items into OrderItems array */
{ 46
i = counter1;
OrderItems[i].OrderNumber2 = OrderNumber1; 37
OrderItems[i].ItemNumber = i;
printf("\n");
printf("\n You can specify as many as 25 line items.");
printf("\n");
printf("\n Enter data for ItemNumber: %d\n", OrderItems[i].ItemNumber);
printf("\n");
printf("\n Vendor Part Number > "); 38
scanf("%s%*c",OrderItems[i].VendPartNumber);
ValidatePart(); 39
DoneItems = FALSE;
if (PartOK != FALSE) {
printf("\n");
printf("\n Purchase Price > "); 40
scanf("%1f",&OrderItems[i].PurchasePrice);
printf("\n Order Quantity > "); 40
scanf("%5d%*c",&OrderItems[i].OrderQty);
printf("\n Item Due Date (yyyymmdd) > "); 40
scanf("%s%*c",OrderItems[i].ItemDueDate);
OrderItems[i].ReceivedQtyInd = -1; 41
counter1 = i + 1;
}
if (i < 25) {
printf("\n");
printf("\n Do you want to specify another line item (Y/N)? >");
scanf("%s%*c",response); 42
if ((response[0] == 'N') || (response[0] == 'n')) {
DoneItems = TRUE;
}
}
else
DoneItems = TRUE; 43
} /* End ItemEntry Function */
int CreateOrderItems() /* Function to create line items */ 51
{
ItemsOK = FALSE;
printf("\n");
printf("\n Do you want to specify line items (Y/N)? > "); 44
scanf("%s%*c",response);
if ((response[0] == 'N') || (response[0] == 'n')) {
DoneItems = TRUE;
DisplayHeader(); 45
}
else {
counter1 = 1;
do {
ItemEntry(); 46
} while (DoneItems == FALSE);
if (counter1 != 1) {
BulkInsert(); 47
ItemsOK = TRUE;
}
}
} /* End of CreateOrderItems Function */
int CreateOrder() /* Function to create an order */ 55
{
printf("\n");
printf("\n Enter a Vendor Number or a 0 to STOP > "); 48
scanf("%10d%*c",&VendorNumber);
if (VendorNumber != 0) {
ValidateVendor(); 49
if (VendorOK == TRUE) CreateHeader(); 50
if (HeaderOK == TRUE) {
DoneItems = FALSE;
do {
CreateOrderItems(); 51
} while (DoneItems == FALSE);
}
if (ItemsOK == TRUE) DisplayOrder(); 52
}
else
Done = TRUE; 53
} /* End CreateOrder Function */
main() /* Beginning of program */
{
printf("\n Program to CREATE an Order - cex9");
printf("\n");
printf("\n Event list:");
printf("\n CONNECT TO PartsDBE");
printf("\n Prompt for a Vendor Number");
printf("\n Validate the Vendor Number");
printf("\n BEGIN WORK");
printf("\n INSERT a row into PurchDB.Orders");
printf("\n Prompt for a line item");
printf("\n Validate the Vendor Part Number for each line item");
printf("\n BULK INSERT rows into PurchDB.OrderItems");
printf("\n Repeat the above six steps until the user enters a 0");
printf("\n RELEASE the DBEnvironment");
printf("\n");
if (ConnectDBE()) { 54
Done = FALSE;
do {
CreateOrder(); 55
} while (Done != TRUE);
ReleaseDBE(); 56
}
else
printf("\n Error: Cannot Connect to PartsDBE!\n");
} /* End of Program */
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
![[Flow Chart of Program cex9]](img/x0701.gif)