 |
» |
|
|
|
The flow chart shown in Figure 5-1 summarizes the functionality
of program cex7. This program uses the four simple data
manipulation commands to operate on
the PurchDB.Vendors table.
A function menu determines whether to execute one or more
SELECT, UPDATE, DELETE, or INSERT operations. Each execution of a
simple data manipulation command is done in a separate transaction. The runtime dialog for program cex7 appears in Figure 5-2, and
the source code in Figure 5-3. Function ConnectDBE starts a
DBE session 51 . This function
executes the CONNECT command 2 for the sample DBEnvironment,
PartsDBE.
The operation performed next depends on the number
entered when a function menu is displayed 52 : The program terminates if 0 is entered. Function Select is executed if 1 is entered.
Function Update is executed if 2 is entered.
Function Delete is executed if 3 is entered.
Function Insert is executed if 4 is entered.
The Select function 9 prompts
for a vendor number or a 0 10 .
If a 0 is entered, the function menu is re-displayed. If a vendor
number is entered, function BeginTransaction
is executed 11 to
issue the BEGIN WORK command 4 . Then
a SELECT command is executed to
retrieve all data for the vendor specified
from PurchDB.Vendors 12 .
The sqlca.sqlcode returned is examined to determine the next action: If no rows qualify for the SELECT operation, a message 14
is displayed and the transaction terminated 17 .
Function EndTransaction terminates the transaction by executing the COMMIT WORK command 5 . The user is then re-prompted for a vendor number or a 0.
If more than one row qualifies for the SELECT operation, a different
message 15 is displayed and the transaction is terminated
17 . The user is then re-prompted for a vendor number or a 0.
If the SELECT command execution results in an error condition,
function SQLStatusCheck is executed 16 . This function executes
SQLEXPLAIN 1 to display all
error messages. Then the transaction is terminated 17 and the user re-prompted for a vendor number or a 0.
If the SELECT command can be successfully executed,
the DisplayRow function 13 is executed
to display the row. This function
examines the null indicators for each of the three potentially null
columns (ContactName, PhoneNumber, and VendorRemarks).
If any null indicator contains a value
not equal to 0 8 , a message
indicating that the value is null is displayed. After the row
is completely displayed, the transaction
is terminated 17 and the
user re-prompted for a vendor number or a 0.
The Update function 23 lets
the user UPDATE the value of a column
only if it contains a null value. The function
prompts 24 for a vendor number or a 0. If
a 0 is entered, the function menu is re-displayed. If a vendor
number is entered, function BeginTransaction
is executed 25 .
Then a SELECT command is executed 26 to
retrieve data from PurchDB.Vendors for the vendor
specified.
The sqlca.sqlcode returned is examined to determine the next action: If no rows qualify for the SELECT operation, a message 28
is displayed and the transaction is terminated 31 .
The user is then re-prompted for a vendor number or a 0.
If more than one row qualifies for
the SELECT operation, a different
message 29 is displayed
and the transaction is terminated 31 .
The user is then re-prompted for
a vendor number or a 0.
If the SELECT command execution results in an error condition,
function SQLStatusCheck is executed 30 .
Then the transaction is
terminated 31 and the user re-prompted for a
vendor number or a 0. If the SELECT command can be successfully executed, function
DisplayUpdate 27 is executed. This function
executes function DisplayRow to
display the row retrieved 18 .
Function AnyNulls is then executed to determine whether the row
contains any null values. This boolean function
evaluates to TRUE 6 if the indicator variable for any of the three
potentially null columns contains a non-zero value. If function AnyNulls evaluates
to FALSE, a message is displayed 7
and the transaction is terminated 31 ;
the user is then re-prompted for
a vendor number or a 0. If function AnyNulls evaluates to TRUE,
the null indicators are examined to determine which of them contain
a negative value 19 . A negative null indicator
means the column contains a null value, and the user
is prompted for a new value 20 . If the user enters a 0, the
program assigns a -1 to the null indicator
21 so that when the UPDATE
command 22 is executed, a null
value is assigned to that column. If
a non-zero value is entered, the program assigns a 0 to the null
indicator so that the value specified is assigned to that column.
After the UPDATE 22 command is executed,
the transaction is terminated 31
and the user re-prompted for a vendor number or a 0.
Function Delete 35 lets the user DELETE one row.
The function
prompts for a vendor number or a 0 36 . If
a 0 is entered, the function menu is re-displayed. If a vendor
number is entered, function BeginTransaction is
executed 37 .
Then a SELECT command is executed to
retrieve all data for the vendor
specified from PurchDB.Vendors 38 .
The sqlca.sqlcode returned is examined to determine the next action: If no rows qualify for the SELECT
operation, a message 40 is displayed
and the transaction is terminated 43 .
The user is then re-prompted for a vendor number or a 0.
If more than one row qualifies
for the SELECT operation, a different
message 41 is displayed
and the transaction is terminated 43 .
The user is then re-prompted for
a vendor number or a 0.
If the SELECT command execution results in an error condition,
function SQLStatusCheck is executed 42 .
Then the transaction is
terminated 43 and the user re-prompted for a
vendor number or a 0.
If the SELECT command can be successfully executed, the
DisplayDelete function 39 is executed. This function
executes function DisplayRow to
display the row retrieved 32 .
Then the user is asked whether she wants to actually delete the
row 33 . If not, the transaction
is terminated 43 and the user
re-prompted for a vendor number or a 0. If so, the DELETE
command 34 is executed before
the transaction is terminated 43
and the user re-prompted.
The Insert function 44 lets the user INSERT one row.
The function
prompts for a vendor number or a 0 45 . If
a 0 is entered, the function menu is re-displayed. If a vendor
number is entered, the user is prompted for values for each column.
The user can enter a 0 to specify a null value for potentially
null columns 46 ; to assign a null
value, the program assigns a -1
to the appropriate null indicator 47 .
After a transaction is started 48 ,
an INSERT command 49 is used to
insert a row containing the specified values.
After the INSERT operation, the
transaction is terminated 50 , and
the user re-prompted for a vendor number or a 0. When the user enters a 0 in response to the function menu
display, the program terminates by executing function
ReleaseDBE 53 . This function executes the
RELEASE command 3 . Figure 5-1 Flow Chart of Program cex7
![[Flow Chart of Program cex7]](img/x0501a.gif)
![[Flow Chart of Program cex7]](img/x0501b.gif)
Figure 5-2 Runtime Dialog of Program cex7
Program for Simple Data Manipulation of Vendors Table - cex7
Connect to PartsDBE
1 . . . . SELECT rows from PurchDB.Vendors table
2 . . . . UPDATE rows with null values in PurchDB.Vendors table
3 . . . . DELETE rows from PurchDB.Vendors table
4 . . . . INSERT rows into PurchDB.Vendors table
Enter choice or 0 to STOP > 4
*** Function to INSERT rows into PurchDB.Vendors ***
Enter Vendor Number to INSERT into Vendors Table or 0 for MENU > 9016
Enter Vendor Name > Wolfe Works
Enter Contact Name or a 0 for null > Stanley Wolfe
Enter Phone Number or a 0 for null > 408 975 6061
Enter Vendor Street > 7614 Canine Way
Enter Vendor City > San Jose
Enter Vendor State > CA
Enter Vendor Zip Code > 90016
Enter Vendor Remarks or a 0 for null > 0
Begin Work
INSERT row into PurchDB.Vendors
Commit Work
Enter Vendor Number to INSERT into Vendors Table or 0 for MENU > 0
1 . . . SELECT rows from PurchDB.Vendors table
2 . . . UPDATE rows with null values in PurchDB.Vendors table
3 . . . DELETE rows from PurchDB.Vendors table
4 . . . INSERT rows into PurchDB.Vendors table
Enter choice or 0 to STOP > 1
*** Function to SELECT a row from the Vendors Table ***
Enter Vendor Number to SELECT from Vendors Table or 0 for MENU > 9016
Begin Work
SELECT * from PurchDB.Vendors
VendorNumber: 9016
VendorName: Wolfe Works
ContactName: Stanley Wolfe
PhoneNumber: 408 975 6061
VendorStreet: 7614 Canine Way
VendorCity: San Jose
VendorState: CA
VendorZipCode: 90016
VendorRemarks is NULL
Commit Work
Enter Vendor Number to SELECT from Vendors Table or 0 for MENU > 0
1 . . . SELECT rows from PurchDB.Vendors table
2 . . . UPDATE rows with null values in PurchDB.Vendors table
3 . . . DELETE rows from PurchDB.Vendors table
4 . . . INSERT rows into PurchDB.Vendors table
Enter choice or 0 to STOP > 2
*** Function to UPDATE rows in PurchDB.Vendors ***
Enter Vendor Number to UPDATE within Vendors Table or 0 for MENU > 9016
Begin Work
SELECT * from PurchDB.Vendors
VendorNumber: 9016
VendorName: Wolfe Works
ContactName: Stanley Wolfe
PhoneNumber: 408 975 6061
VendorStreet: 7614 Canine Way
VendorCity: San Jose
VendorState: CA
VendorZipCode: 90016
VendorRemarks is NULL
Enter new VendorRemarks or 0 for null > can expedite shipments
Commit Work
Enter Vendor Number to UPDATE within Vendors Table or 0 for MENU > 0
1 . . . SELECT rows from PurchDB.Vendors table
2 . . . UPDATE rows with null values in PurchDB.Vendors table
3 . . . DELETE rows from PurchDB.Vendors table
4 . . . INSERT rows into PurchDB.Vendors table
Enter choice or 0 to STOP > 3
*** Function to DELETE rows from PurchDB.Vendors ***
Enter Vendor Number to DELETE from Vendors Table or 0 for MENU > 9016
Begin Work
SELECT * from PurchDB.Vendors
VendorNumber: 9016
VendorName: Wolfe Works
ContactName: Stanley Wolfe
PhoneNumber: 408 975 6061
VendorStreet: 7614 Canine Way
VendorCity: San Jose
VendorState: CA
VendorZipCode: 90016
VendorRemarks: can expedite shipments
Is it OK to DELETE this row (N/Y)? > Y
DELETE row from PurchDB.Vendors
Commit Work
Enter Vendor Number to DELETE from Vendors Table or 0 for MENU > 0
1 . . . SELECT rows from PurchDB.Vendors table
2 . . . UPDATE rows with null values in PurchDB.Vendors table
3 . . . DELETE rows from PurchDB.Vendors table
4 . . . INSERT rows into PurchDB.Vendors table
Enter choice or 0 to STOP > 0
|
Figure 5-3 Program cex7: Using INSERT, UPDATE, SELECT and DELETE
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
/* This program illustrates the use of SQL's data manipulation */
/* commands for simple operations. It uses the UPDATE */
/* command with indicator variables to update any row in the */
/* Vendors table that contains null values. It also uses indicator */
/* variables in conjunction with SELECT and INSERT commands. */
/* The DELETE command is also illustrated. */
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
typedef int boolean;
boolean Abort;
int response1;
char response2[2];
#include <stdio.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 VendorNumber;
char VendorName[31];
char ContactName[31];
sqlind ContactNameInd;
char PhoneNumber[16];
sqlind PhoneNumberInd;
char VendorStreet[31];
char VendorCity[21];
char VendorState[3];
char VendorZipCode[11];
char VendorRemarks[61];
sqlind VendorRemarksInd;
char SQLMessage[133];
EXEC SQL END DECLARE SECTION;
/* End Host Variable Declarations */
int getline(linebuff) /* Function to get a line of characters */
char linebuff[80];
{
while (strlen(gets(linebuff)) == 0);
} /* End of function to get a line of characters */
int SQLStatusCheck() /* Function to Display Error Messages */ 16
{
Abort = FALSE;
if (sqlca.sqlcode < DeadLock) Abort = TRUE;
do {
EXEC SQL SQLEXPLAIN :SQLMessage; 1
printf("%s\n",SQLMessage);
} while (sqlca.sqlcode != 0);
if (Abort) ReleaseDBE();
} /* End SQLStatusCheck Function */
boolean ConnectDBE() /* Function to Connect to PartsDBE */
51
{
boolean ConnectDBE;
printf("\n Connect to PartsDBE");
EXEC SQL CONNECT TO 'PartsDBE'; 2
ConnectDBE = TRUE;
if (sqlca.sqlcode != OK) {
ConnectDBE = FALSE;
SQLStatusCheck();
} /* End if */
return (ConnectDBE);
} /* End of ConnectDBE Function */
int ReleaseDBE() /* Function to Release PartsDBE */
53
{
printf("\n Release PartsDBE");
printf("\n");
EXEC SQL RELEASE; 3
if (sqlca.sqlcode != OK) SQLStatusCheck();
} /* End ReleaseDBE Function */
boolean BeginTransaction() /* Function to Begin Work */ 11
{
boolean BeginTransaction;
printf("\n");
printf("\n Begin Work");
EXEC SQL BEGIN WORK; 4
if (sqlca.sqlcode != OK) {
BeginTransaction = FALSE;
SQLStatusCheck();
ReleaseDBE();
}
else
BeginTransaction = TRUE;
return (BeginTransaction);
} /* End BeginTransaction Function */
int EndTransaction() /* Function to Commit Work */ 17
{
printf("\n");
printf("\n Commit Work");
EXEC SQL COMMIT WORK; 5
if (sqlca.sqlcode != OK) {
SQLStatusCheck();
ReleaseDBE();
}
} /* End EndTransaction Function */
boolean AnyNulls() /* Function to test a row for null values */
{
boolean AnyNulls;
AnyNulls = TRUE;
if ((ContactNameInd == 0)&(PhoneNumberInd == 0)&(VendorRemarksInd == 0)) {
/* All columns that might be null contain non-null values */ 6
printf("\n No null values exist for this vendor!"); 7
AnyNulls = FALSE;
}
return (AnyNulls);
} /* End of AnyNulls Function */
int DisplayRow() /* Function to Display Parts Table Rows */ 13
{
printf("\n");
printf("Vendor Number: %10d\n", VendorNumber);
printf("Vendor Name: %s\n", VendorName);
if (ContactNameInd != 0) { 8
printf("Contact Name: is NULL \n");
}
else
printf("Contact Name: %s\n", ContactName);
if (PhoneNumberInd != 0) { 8
printf("Phone Number: is NULL \n");
}
else
printf("PhoneNumber: %s\n", PhoneNumber);
printf("VendorStreet: %s\n", VendorStreet);
printf("VendorCity: %s\n", VendorCity);
printf("VendorState: %s\n", VendorState);
printf("VendorZipCode: %s\n", VendorZipCode);
if (VendorRemarksInd != 0) { 8
printf("Vendor Remarks: is NULL \n");
}
else
printf("VendorRemarks: %s\n", VendorRemarks);
} /* End of DisplayRow */
int Select() /* Function to Query Parts Table */ 9
{
printf("\n");
printf("\n *** Function to SELECT a row from the Vendors table. ***");
printf("\n");
do {
printf("\nEnter Vendor Number to SELECT from Vendors Table or 0 for
MENU>");
while (scanf("%d%*c", &VendorNumber) == 0;| 10
if (VendorNumber != 0) {
BeginTransaction(); 11
printf("\n");
printf("\n SELECT * from PurchDB.Vendors");
EXEC SQL SELECT VendorNumber, 12
VendorName,
ContactName,
PhoneNumber,
VendorStreet,
VendorCity,
VendorState,
VendorZipCode,
VendorRemarks
INTO :VendorNumber,
:VendorName,
:ContactName :ContactNameInd,
:PhoneNumber :PhoneNumberInd,
:VendorStreet,
:VendorCity,
:VendorState,
:VendorZipCode,
:VendorRemarks :VendorRemarksInd
FROM PurchDB.Vendors
WHERE VendorNumber = :VendorNumber;
switch(sqlca.sqlcode) {
case OK: DisplayRow(); 13
break;
case NotFound: printf("\n");
printf("\n Row not found!"); 14
break;
case MultipleRows: printf("\n");
printf("\nWARNING: More than one row qualifies!");
break; 15
default: SQLStatusCheck(); 16
break;
} /* End switch */
EndTransaction(); 17
} /* End if */
} while (VendorNumber != 0);
} /* End of Select Function */
int DisplayUpdate() /* Display & Update row in Parts Table */ 27
{
DisplayRow(); 18
if (AnyNulls) {
if (ContactNameInd != 0) { 19
printf("\n");
printf("\n Enter new Contact Name or 0 for NULL > "); 20
getline(ContactName);
}
if (PhoneNumberInd != 0) { 19
printf("\n");
printf("\n Enter new Phone Number of 0 for NULL > "); 20
getline(PhoneNumber);
}
if (VendorRemarksInd != 0) { 19
printf("\n");
printf("]\n Enter new Vendor Remarks or 0 for NULL > "); 20
getline(VendorRemarks);
}
if (ContactName[0] == '0') ContactNameInd = -1; 21
else
ContactNameInd = 0;
if (PhoneNumber[0] == '0') PhoneNumberInd = -1; 21
else
PhoneNumberInd = 0;
if (VendorRemarks[0] == '0') VendorRemarksInd = -1; 21
else
VendorRemarksInd = 0;
printf("\n");
printf("\n UPDATE the Vendors Table");
EXEC SQL UPDATE PurchDB.Vendors 22
SET ContactName = :ContactName :ContactNameInd,
PhoneNumber = :PhoneNumber :PhoneNumberInd,
VendorRemarks = :VendorRemarks :VendorRemarksInd
WHERE VendorNumber = :VendorNumber;
if (sqlca.sqlcode != OK ) SQLStatusCheck();
} /* End if AnyNulls */
} /* End of DisplayUpdate Function */
int Update() /* Update a row within the Parts Table */ 23
{
printf("\n");
printf("\n *** Function to UPDATE rows in PurchDB.Vendors ***");
printf("\n");
do {
printf("\n Enter Vendor Number to UPDATE in Vendors Table or 0 for MENU >");
while (scanf("%d%*c", &VendorNumber) == 0);| 24
if (VendorNumber != 0) {
BeginTransaction(); 25
printf("\n");
printf("\n SELECT * from PurchDB.Vendors");
EXEC SQL SELECT VendorNumber, 26
VendorName,
ContactName,
PhoneNumber,
VendorStreet,
VendorCity,
VendorState,
VendorZipCode,
VendorRemarks
INTO :VendorNumber,
:VendorName,
:ContactName :ContactNameInd,
:PhoneNumber :PhoneNumberInd,
:VendorStreet,
:VendorCity,
:VendorState,
:VendorZipCode,
:VendorRemarks :VendorRemarksInd
FROM PurchDB.Vendors
WHERE VendorNumber = :VendorNumber;
if (sqlca.sqlcode == OK)
DisplayUpdate(); 27
else if (sqlca.sqlcode == NotFound)
printf("\n Row not found!"); 28
else if (sqlca.sqlcode == MultipleRows)
printf("\n WARNING: More than one row qualifies!"); 29
else
SQLStatusCheck(); 30
EndTransaction(); 31
} /* End if */
} while (VendorNumber != 0);
} /* End of Update Function */
int DisplayDelete() /* Display & optionally Delete Rows */ 39
{
DisplayRow(); 32
printf("\n");
printf("\n Is it OK to DELETE this row (N/Y) ? > "); 33
scanf("%s",response2);
if ((response2[0] == 'Y') || (response2[0] == 'y')) {
printf("\n");
printf("\n DELETE Row from PurchDB.Vendors Table");
EXEC SQL DELETE FROM PurchDB.Vendors 34
WHERE VendorNumber = :VendorNumber;
if (sqlca.sqlcode != OK) SQLStatusCheck();
}
} /* End of DisplayDelete */
int Delete() /* Function to Delete a row from the Parts Table */ 35
{
printf("\n");
printf("\n *** Function to DELETE rows from PurchDB.Vendors ***");
printf("\n");
do {
printf("\nEnter Vendor Number to DELETE from Vendors Table or 0 for MENU>");
while(scanf("%d%*c", &VendorNumber) == 0);| 36
if (VendorNumber != 0) {
BeginTransaction(); 37
printf("\n");
printf("\n SELECT * from PurchDB.Vendors");
EXEC SQL SELECT VendorNumber, 38
VendorName,
ContactName,
PhoneNumber,
VendorStreet,
VendorCity,
VendorState,
VendorZipCode,
VendorRemarks
INTO :VendorNumber,
:VendorName,
:ContactName :ContactNameInd,
:PhoneNumber :PhoneNumberInd,
:VendorStreet,
:VendorCity,
:VendorState,
:VendorZipCode,
:VendorRemarks :VendorRemarksInd
FROM PurchDB.Vendors
WHERE VendorNumber = :VendorNumber;
if (sqlca.sqlcode == OK)
DisplayDelete(); 39
else if (sqlca.sqlcode == NotFound)
printf("\n Row not found!"); 40
else if (sqlca.sqlcode == MultipleRows)
printf("\n WARNING: More than one row qualifies!"); 41
else
SQLStatusCheck(); 42
EndTransaction (); 43
} /* End if */
} while (VendorNumber != 0);
} /* End of Delete */
int Insert() /* Insert a row into the Parts Table */ 44
{
printf("\n");
printf("\n *** Function to INSERT rows into PurchDB.Vendors ***");
printf("\n");
do {
VendorNumber = 0;
printf("\nEnter Vendor Number to INSERT into Vendors Table or 0 for MENU>");
while(scanf("%d%*c", &VendorNumber) == 0);| 45
if (VendorNumber != 0) {
printf("\n Enter Vendor Name > ");
getline(VendorName);
printf("\n Enter Contact Name or a 0 for NULL > "); 46
getline(ContactName);
if (ContactName[0] == '0') ContactNameInd = -1; 47
else
ContactNameInd = 0;
printf("\n Enter Phone Number or a 0 for NULL > "); 46
getline(PhoneNumber);
if (PhoneNumber[0] == '0') PhoneNumberInd = -1 47
else
PhoneNumberInd = 0;
printf("\n Enter Vendor Street > ");
getline(VendorStreet);
printf("\n Enter Vendor City > ");
getline(VendorCity);
printf("\n Enter Vendor State > ");
getline(VendorState);
printf("\n Enter Vendor Zip Code > ");
getline(VendorZipCode);
printf("\n Enter Vendor Remarks or a 0 for NULL > "); 46
getline(VendorRemarks);
if (VendorRemarks[0] == '0') VendorRemarksInd = -1; 47
else
VendorRemarksInd = 0;
BeginTransaction(); 48
printf("\n");
printf("\n INSERT row into PurchDB.Vendors");
EXEC SQL INSERT 49
INTO PurchDB.Vendors
(VendorNumber,
VendorName,
ContactName,
PhoneNumber,
VendorStreet,
VendorCity,
VendorState,
VendorZipCode,
VendorRemarks)
VALUES (:VendorNumber,
:VendorName,
:ContactName :ContactNameInd,
:PhoneNumber :PhoneNumberInd,
:VendorStreet,
:VendorCity,
:VendorState,
:VendorZipCode,
:VendorRemarks :VendorRemarksInd);
if (sqlca.sqlcode != OK) SQLStatusCheck();
EndTransaction(); 50
} /* End if */
} while (VendorNumber != 0);
} /* End of Insert */
main() /* Beginning of program */
{
printf("\n Program for Simple Data Manipulation of Vendors Table-cex7")
printf("\n");
if (ConnectDBE()) { 51
do {
printf("\n"); 52
printf("\n 1 . . .SELECT rows from PurchDB.Vendors Table");
printf("\n 2 . . .UPDATE rows with NULL values in PurchDB.Vendors Table");
printf("\n 3 . . .DELETE rows from PurchDB.Vendors Table");
printf("\n 4 . . .INSERT rows into PurchDB.Vendors Table");
printf("\n");
printf("\n Enter choice or 0 to STOP > ");
scanf("%2d%*c", &response1);
if (response1 != 0) {
switch (response1) { 50
case 1: Select(); 9
break;
case 2: Update(); 23
break;
case 3: Delete(); 35
break;
case 4: Insert(); 44
break;
default: printf("\n Enter 0-4 only, please!");
break;
} /* switch */
} /* End if response1 */
} while ( response1 != 0);
ReleaseDBE(); 53
} /* End if Connect */
else
printf("\n Error: Cannot Connect to PartsDBE!\n");
} /* End of Program */
|
|
