|
|
|
ALLBASE/SQL C Application Programming Guide: HP 3000 MPE/iX Computer Systems > Chapter 5 Simple Data Manipulation
Sample Program Using Simple DML Commands |
|
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 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:
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:
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:
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-3 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-4 Program cex7: Using INSERT, UPDATE, SELECT and DELETE
/* Program cex7 */
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
/* 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 */
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
![[Flow Chart of Program cex7]](img/x0501a.gif)
![[Figure 5-1. Flow Chart of Program cex7 (page 2 of 2)]](img/x0501b.gif)