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:
Validates the number entered.
Creates an order header if the vendor number is valid.
optionally inserts line items if the order header has
been successfully created; the part number for each line item
is validated to ensure the vendor actually supplies the part.
Displays the order created.
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 number exists in table PurchDB.Vendors, the vendor number
is valid. Flag
VendorOK is set to TRUE, and the transaction is terminated
by invoking function EndTransaction 11 .
EndTransaction executes the COMMIT WORK command 7 .
If the vendor number is not found, COMMIT WORK is executed and
a message is displayed to inform the user that the number entered is
invalid 12 . Several flags
are set to FALSE so that when control
returns to function CreateOrder, the user is again
prompted for a vendor number.
If the SELECT command fails, function SQLStatusCheck is
invoked 13 to display any error messages 4 .
Then the COMMIT WORK
command is executed, and the appropriate flags set to FALSE.
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:
Function CreateHeader invokes function
ComputeOrderNumber 36 to
compute the order number and the order
date.
Function ComputeOrderNumber executes a SELECT command to
retrieve the highest order number in PurchDB.Orders 30 .
The number retrieved is incremented
by one 31 to assign a number to
the order.
Function ComputeOrderNumber then executes function
SystemDate 32 . This function calls the system
time library 2
to retrieve and format the current
date. The date retrieved is converted
into YYYYMMDD format, the format in which dates are stored in the
sample DBEnvironment.
Function ComputeOrderNumber then executes function
InsertRow 33 . This function executes a simple INSERT
command 22
to insert a row into PurchDB.Orders.
If the INSERT command succeeds,
the transaction is terminated with a COMMIT WORK command, and the
HeaderOK flag is set to TRUE 24 .
If the INSERT command fails,
the transaction is terminated with a COMMIT WORK command, but the HeaderOK
flag is set to FALSE 23 so
that the user is prompted for another
vendor number when control returns to function CreateOrder.
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 :
Function ItemEntry assigns values to the host variable
structure array OrderItems 1 ; each
record in the array corresponds to one line item, or row in
PurchDB.OrderItems. The function
first assigns the order number and
a line number to each row 37 ,
beginning at one. ItemEntry then prompts for
a vendor part number 38 , which
is validated by invoking function
ValidatePart 39 .
Function ValidatePart starts a transaction
14 . Then it executes a SELECT
command 15 to determine whether
the part number entered matches any
part number known to be supplied by the vendor.
If the part number is valid, the
COMMIT WORK command is executed 16
and the PartOK flag set to TRUE.
If the part number is invalid, COMMIT WORK is executed 17 , and
the user informed that the vendor does not supply any part having
the number specified; then the PartOK flag is set to FALSE
so that the user is prompted for another part number when control
returns to function ItemEntry.
If the part number is valid, function
ItemEntry completes the line item. It prompts for values to
assign to columns PurchasePrice, OrderQty, and
ItemDueDate 40 . The
function then assigns a negative value
to the indicator variable for column ReceivedQty 41
in preparation for inserting a null value into this column.
Function ItemEntry terminates when the user indicates that
the user does not want to specify any more line items 42 or when
the host variable array is full 43 .
Function BulkInsert starts a transaction 25 , then
executes the BULK INSERT command 27 . The
line items in array OrderItems are inserted into table
PurchDB.OrderItems, starting with the first record and
continuing for as many records as there were line items
specified 26 . If the BULK INSERT command succeeds, the
COMMIT WORK command is executed 29 and the ItemsOK flag set to
TRUE. If the BULK INSERT command fails,
function RollBackWork is executed 28 to process
the ROLLBACK WORK
command 8 so that any rows inserted prior
to the failure are rolled back.
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-1 Flow Chart of Program cex9
![[Flow Chart of Program cex9]](img/x0701.gif)
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
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
/* 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 */
|
