 |
» |
|
|
|
This section contains examples based on the recreation
database, RecDB, which is supplied as part of the ALLBASE/SQL software
package. The schema files used to create the database are found
in appendix C of the ALLBASE/SQL Reference Manual . The recreation database is made up of three tables (Clubs, Members, and Events).
Two primary key constraints and two referential constraints were
specified when the tables were created to secure the data integrity
of these tables. Figure 11-1 “Constraints Enforced on the Recreation Database” illustrates these constraint relationships by
showing the name of each constraint and its referencing or referenced columns.
Referencing columns are shaded. Referenced columns are clear white.
Figure 11-1 Constraints Enforced on the Recreation Database
![[Constraints Enforced on the Recreation Database]](img/145_007.gif)
Suppose you designed an application program providing a user interface
to the recreation database.
The interface gives choices for inserting, updating, and deleting
data in any of the three tables.
Your application is user friendly and guides the user with
error messages when their request is denied because it would
violate data integrity. The main interface menu might look like this:
Main Menu for Recreation Database Maintenance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1. INSERT a Club 4. INSERT a Member 7. INSERT an Event
2. UPDATE a Club 5. UPDATE Member Info. 8. UPDATE Event Info.
3. DELETE a Club 6. DELETE a Member 9. DELETE an Event
|
When users make a selection (by number or by tabbing to a field),
a screen displaying all the appropriate information allows them to
insert, update, or delete. The next sections provide generic examples of how you can code such an
application.
The error checking in these examples deals with constraint enforcement
errors only. (For complete explanation of these errors, see the ALLBASE/SQL Message Manual .)
Your error checking routine should also include a method of handling multiple
errors per command and errors not related to constraint enforcement.
(For more information on error coding techniques, see the chapter, "Runtime
Status Checking and the SQLCA.")
Insert a Member in the Recreation Database | |
The user chooses to insert a new member in the database.
For this activity to complete, the foreign key (Club) which is
being inserted into the Members table must exist in the primary key
(ClubName) of the Clubs table.
Execute routines to display and prompt for information needed in the
Members table.
Place user entered information in appropriate host variables.
INSERT INTO RecDB.Members
VALUES (:MemberName,
:Club,
:MemberPhone :MemberPhoneInd)
Check the sqlcode field of the sqlca.
If sqlcode equals -2293, indicating no primary key match, display the error
message and prompt the user to indicate whether or not to insert
a new ClubName in the Clubs table, to reenter the Club for the new member,
or to exit to the main menu. Execute the appropriate subroutine.
If sqlcode equals -2295, indicating that the user tried to insert a non-unique
primary key, display the error message and prompt the user to enter a
unique MemberName/Club combination or to exit to the main menu.
Execute the appropriate subroutine.
Else, if sqlcode = 0, tell the user the member was inserted successfully,
and prompt for another new member or a return to the main menu display.
|
Update an Event in the Recreation Database | |
The user now wants to update information in the Events table. For this
activity to complete, the SponsorClub and Coordinator being
updated in the Events table must exist in the primary key composed of
MemberName and Club in the Members table.
Execute subroutines to display and prompt for information needed in the
Events table.
Place user entered information in appropriate host variables.
UPDATE RecDB.Events
SET SponsorClub = :SponsorClub :SponsorClubInd,
Event = :Event :EventInd,
Date = :Date DateInd,
Time = :Time TimeInd,
Coordinator = :Coordinator CoordinatorInd
WHERE Event = :Event
Check the sqlcode field of the sqlca.
If sqlcode equals -2293, indicating no primary key match, display the error
message and prompt the user to indicate whether or not to insert a new
MemberName/Club primary key in the Members table, to reenter update
information for the Events table, or to exit to the main menu. Execute
the appropriate subroutine.
Else, if sqlcode = 0, tell the user the event was updated successfully,
and prompt for another event or a return to the main menu display.
|
Delete a Club in the Recreation Database | |
The user chooses to delete a club.
For this activity to complete, no foreign key must reference the
primary key (ClubName) that is being deleted.
Execute subroutines to display and prompt for a ClubName in the Clubs table.
Place user entered information in appropriate host variables.
DELETE FROM RecDB.Clubs
WHERE ClubName = :ClubName
Check the sqlcode field of the sqlca.
If sqlcode equals -2293, indicating that referencing data exists for ClubName,
display the error message and prompt the user to indicate whether or not
to delete the Members table row or rows that reference the ClubName,
to reenter the ClubName to be deleted, or to exit to the main menu.
Execute the appropriate subroutine.
(If you execute the subroutine to delete those rows in the Members table
which reference the Clubs table, be sure to test sqlcode.
Depending on the result, you can prompt the user to delete referencing
Events table rows, to reenter the Members table information, or to exit
to the main menu. Execute the appropriate subroutine.)
Else, if sqlcode = 0, tell the user the club was deleted successfully,
and prompt for another club or a return to the main menu display.
|
Delete an Event in the Recreation Database | |
The user chooses to delete an event. Because no primary key or
unique constraints are defined in the Events table, no constraint
enforcement is necessary.
Execute subroutines to display and prompt for an Event in the Events table.
Place user entered information in appropriate host variables.
DELETE FROM RecDB.Clubs
WHERE Event = :Event
Check the sqlcode field of the sqlca.
If sqlcode = 0, tell the user the event was deleted successfully, and
prompt for another event or a return to the main menu display.
|
|
