HP 3000 Manuals HP 3000 Manuals
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 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 .
![[]](../../pic3k/M011754/FFN533c50.gif)
Figure 5-1. Flow Chart of Program cex7
![[]](../../pic3k/M011754/FFN543c50.gif)
Figure 5-1. Flow Chart of Program cex7 (page 2 of 2)
____________________________________________________________________________
| |
| 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 |
| |
| |
| |
____________________________________________________________________________
Figure 5-2. Runtime Dialog of Program cex7
______________________________________________________________________________
| |
| *** 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 |
______________________________________________________________________________
Figure 5-2. Runtime Dialog of Program cex7 (page 2 of 3)
____________________________________________________________________________
| |
| 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-2. Runtime Dialog of Program cex7 (page 3 of 3)
____________________________________________________________________________
| |
| /* 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 */ |
| |
| |
____________________________________________________________________________
Figure 5-3. Program cex7: Using INSERT, UPDATE, SELECT and DELETE
___________________________________________________________________________
| |
| 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 */ |
| |
| |
| |
___________________________________________________________________________
Figure 5-3. Program cex7: Using INSERT, UPDATE, SELECT and DELETE (page 2 of 12)
_________________________________________________________________________________
| |
| 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 */ |
| |
| |
| |
_________________________________________________________________________________
Figure 5-3. Program cex7: Using INSERT, UPDATE, SELECT and DELETE (page 3 of 12)
______________________________________________________________________________
| |
| 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 |
| |
| |
| |
______________________________________________________________________________
Figure 5-3. Program cex7: Using INSERT, UPDATE, SELECT and DELETE (page 4 of 12)
___________________________________________________________________________________
| |
| 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 */ |
| |
| |
| |
| |
| |
___________________________________________________________________________________
Figure 5-3. Program cex7: Using INSERT, UPDATE, SELECT and DELETE (page 5 of 12)
___________________________________________________________________________
| |
| 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 */ |
| |
| |
| |
___________________________________________________________________________
Figure 5-3. Program cex7: Using INSERT, UPDATE, SELECT and DELETE (page 6 of 12)
___________________________________________________________________________________
| |
| 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 |
| |
| |
___________________________________________________________________________________
Figure 5-3. Program cex7: Using INSERT, UPDATE, SELECT and DELETE (page 7 of 12)
___________________________________________________________________________________
| |
| 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 |
| |
| |
| |
| |
| |
| |
| |
| |
___________________________________________________________________________________
Figure 5-3. Program cex7: Using INSERT, UPDATE, SELECT and DELETE (page 8 of 12)
___________________________________________________________________________
| |
| 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"); |
| |
___________________________________________________________________________
Figure 5-3. Program cex7: Using INSERT, UPDATE, SELECT and DELETE (page 9 of 12)
___________________________________________________________________________________
| |
| 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 |
| |
| |
| |
___________________________________________________________________________________
Figure 5-3. Program cex7: Using INSERT, UPDATE, SELECT and DELETE (page 10 of 12)
______________________________________________________________________________
| |
| 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 |
| |
| |
| |
| |
| |
| |
| |
| |
| |
______________________________________________________________________________
Figure 5-3. Program cex7: Using INSERT, UPDATE, SELECT and DELETE (page 11 of 12)
___________________________________________________________________________________
| |
| 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 */ |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
___________________________________________________________________________________
Figure 5-3. Program cex7: Using INSERT, UPDATE, SELECT and DELETE (page 12 of 12)