←Part 26 Part 28→
The HP 3000--For Complete Novices
Part 27: IMAGE/SQL Application Logic--Using DBFIND and DBGET Together
 |
by George Stachnik
In the previous article in this series, we continued to explore how IMAGE/SQL applications work. We saw examples of how IMAGE/SQL uses the DBFIND and DBGET intrinsics to find chains of records. This time around, we're going to take a closer look at the capabilities of DBGET. The last installment ended with a first look at DBGET. DBGET is used to read records (or data entries, if you prefer the IMAGE/SQL terminology) from a dataset in a database. DBGET is one of the most powerful tools in the IMAGE/SQL programmer's arsenal, because it offers several different ways to access your data. Here is a list of DBGET's most useful capabilities:
Sample DatabaseFigure 1 is a representation of a very simple IMAGE/SQL database. This database is made up of just two datasets:
Figure 1: Database
The CUSTOMER-MASTER dataset is shown on the left side of the figure. As its
name implies, it is a master dataset--(a manual master, to be absolutely
specific). It contains one record for each customer that we are currently
doing business with. The figure shows that our company has just four
customers, named GEORGE, KEN, ANDREA, and GREG. (Presumably, we're one of
those dot-com startups. No matter--the profits will come when they're
needed.)
Each customer record contains information pertinent to that particular
customer. For the sake of simplicity, all we're showing in the figure is the
customer name, but we can assume that additional information such as address,
phone, e-mail address, and so forth would also be stored in these records.
CUSTOMER-NAME is an important data item in the CUSTOMER-MASTER dataset because
it's a search item. This means that IMAGE/SQL has built the database
so that we can quickly find records in CUSTOMER-MASTER using CUSTOMER-NAME as
a search argument. For example, we can find the record with CUSTOMER-NAME =
GREG.
The ORDER-NUMBER-DETAIL dataset is shown on the right side of the figure.
Every time our company takes an order, one record is added to this dataset.
Again, for the sake of simplicity, the figure shows only one data item in each
record--the order number. But assume that each record also contains other
information you might need to know about that order, including the particular
products or parts that were ordered, the name of the customer who placed the
order, and so forth.
One of the fields in ORDER-NUMBER-DETAIL is CUSTOMER-NAME. As we'll see, this
data-item is the link between the two datasets, and the building block from
which IMAGE/SQL will create chains of records.
 Chains of RecordsWe've been discussing the concept ofchainsof records in the last few installments of this series of articles. Figure 1 illustrates this concept in more detail than we've seen up until now. Each of the four records in CUSTOMER-MASTER is associated with a chainof related records in ORDER-NUMBER-DETAIL. For example, the GREG record in CUSTOMER-MASTER is associated with a set of records in ORDER-NUMBER-DETAIL. These records represent all of the orders Greg has placed with our company. Every time Greg calls in an order, a new record representing that order is placed in the ORDER-NUMBER DETAIL dataset. Then, the record is also added to the Greg chain. In Figure 1, we've colored all of GREG's records red, to make them easier to find. The Greg Chainis nothing more than the set of records in ORDER-NUMBER-DETAIL that correspond to GREG's orders. Every chain of records is arranged in a particular sequence or order. The GREG chain begins (or is rooted) with Greg's record in the CUSTOMER-MASTER dataset. Figure 1 represents the linkages among Greg's records using red arrows. If you follow those arrows from the GREG record in CUSTOMER-MASTER to the records in ORDER-NUMBER-DETAIL, you'll find that the GREG chain is made up of three records. They have order numbers 0001, 0004 and 0009, in that order. Note, that in Greg's case, the sequence of records in the GREG chain is the same sequence in which the records are physically stored in the database. That is, the GREG chain is made of order numbers 0001, 0004, and 0009, and the chain seems to be sorted in order number sequence. In practice, chains don't have to be sorted in any particular sequence. For example, look at the Andrea chain of records in Figure 1. The ANDREA records are all shown in green, and the arrows that show the linkages in the ANDREA chain are also green in color. Like Greg's chain, Andrea's chain is rooted in the ANDREA record in CUSTOMER-MASTER. This record contains a pointer to the corresponding chain of records in ORDER-NUMBER-DETAIL. If you follow the green arrows, you'll see that that the ANDREA chain in ORDER-NUMBER-DETAIL begins with order number 0006, continuing with 0012, and ending with 0002. As you can see, the ANDREA records are in no particular order. When this IMAGE/SQL database was first defined in a schema, the chains were also defined in that file. We began our discussion of IMAGE/SQL several months ago by showing you how to create and compile a schema. There was very little discussion of chainsat that time, so we'll review the concept now, as it applies to the schema. There are sections in every schema to define database passwords, data items, and datasets. But there is no chainssection. That's because the chains are implied by the relationships among the items in the schema. Recall that every schema contains a SETS: section in which we define the various datasets that make up the database. Then, for each dataset, the various data items (or fields, if you prefer) that make up that dataset are defined in the schema. Some of these data items are identified as search items. Each search item that is defined in the schema results in a corresponding chain of records. In our example in Figure 1, CUSTOMER-NAME is a search item. In the schema that defined this database, the CUSTOMER-MASTER and ORDER-NUMBER-DETAIL datasets were defined in the SETS portion of the schema, as shown in Figure 2. Figure 2: SETS Part of Schema
SETS:
NAME: CUSTOMER-MASTER, MANUAL(4,8,15/8,15);
ENTRY: CUSTOMER-NAME(1),
STREET,
CITY,
EMAIL-ADDRESS
ZIPCODE;
CAPACITY: 201;
NAME: ORDER-NUMBER-DETAIL, DETAIL(4,8,15/8,15);
ENTRY: CUSTOMER-NAME(CUSTOMER-MASTER),
ORDER-NUMBER,
DATE;
PART-NO,
DATE,
The first three lines of the code fragment shown in Figure 2 tell us
that we're defining a manual master dataset called CUSTOMER-MASTER. (The
parenthesized list of numbers following the word MANUALis the read/write list of user classes.) The ENTRY section begins on the fourth line shown in the figure. The ENTRY keyword signals that we're about to see a list of data entries (or fields) that will make up this particular dataset. Note that the data item CUSTOMER-NAME is followed by a 1in parentheses. This 1in the schema identifies CUSTOMER-NAME as a search item, and furthermore says that for every unique CUSTOMER-NAME that appears in CUSTOMER-MASTER, there will be a pointer to exactly one detail dataset containing a chain of records for that customer. So, in a schema, a number next to an item name in a master dataset effectively defines a chain. In the example shown in Figure 1, for every record in CUSTOMER-MASTER, there is (or at least could be) a chain of one or more records in ORDER-NUMBER-DETAIL that corresponds to that record. The number that appears in the schema tells how many detail datasets will contain chains of records corresponding to this item. Note that it doesn't tell which detail datasets; to find that out, we have to look elsewhere in the schema. Returning to Figure 2, let's take a look at the definition of the ORDER-NUMBER-DETAIL dataset. The first data item in this dataset is CUSTOMER-NAME, and the data item name is followed in the schema by the words CUSTOMER-MASTER in parenthesis. In a detail dataset definition, each search item name must be followed by the name of the corresponding master dataset name in parenthesis. The schema's definition of a chain, then, has two parts. The first part is in the description of the master dataset that will hold the root of the chain. There, a number must be coded next to the search item name, which tells how many detail datasets contain chains that are rooted in this master. The second part is in the description of the detail dataset (or datasets). There, the name of the corresponding master dataset is coded next to the search item name. ENTRY: CUSTOMER-NAME(CUSTOMER-MASTER),The above line of code from Figure 2 is saying that CUSTOMER-NAME is a search item. IMAGE/SQL will link all the records in this dataset that share a common value in CUSTOMER-NAME (for example, all the GREG records--see Figure 1) into a chain. The rootof this chain will reside in the specified master dataset, in this case, CUSTOMER-MASTER. Sorted and Unsorted ChainsChains can be defined to be either sorted or unsorted. When a record is added to an unsorted chain, it is always linked to the end of any chains that the record might be part of. Consequently, unsorted chains are in no particular order (other than temporal)--that is, the records in the chain appear in the order in which they were added to the chains. When a record is added to a sorted chain, IMAGE/SQL will insert the record in the appropriate place in the chain in order to maintain the chain in the order that was specified in the schema. In order to define a sorted chain, you name the sort-item in the schema. For example, suppose we wanted the CUSTOMER-NAME chains to be sorted by delivery date. When we defined that chain, we'd specify the sort field as follows:
ENTRY: CUSTOMER-NAME(CUSTOMER-MASTER(DATE)),
ORDER-NUMBER,
DATE;
This would tell IMAGE/SQL that chains of records that share a common
CUSTOMER-NAME must be sorted by DATE. However, bear in mind that there is a
lot of overhead associated with the maintenance of sorted chains. The impact
on database performance may be significant; most experienced IMAGE/SQL
database administrators avoid them unless they are absolutely necessary.
Picturing A DatabaseLet's go back to Figure 1 for a moment, and wrap up this conceptual explanation of IMAGE/SQL databases, and make sure we're clear on how chains work. One way of thinking of an IMAGE/SQL database is to imagine each record in CUSTOMER-MASTER as an index card with a piece of string tied to it. That piece of string represents a chain. Imagine that each record in ORDER-NUMBER-DETAIL is also represented by an index card, but this time, each card has a hole punched in the corner. The string that's tied to the GREG card in CUSTOMER-MASTER is threaded through the holes in each of the cards in ORDER-NUMBER-DETAIL that represent one of GREG's orders. It may be helpful to think of chained access this way. If you wanted to find all of GREG's orders, you'd pull the GREG card from the CUSTOMER-MASTER box. This card would tell you everything you needed to know about GREG--but nothing about his orders. The information about GREG's orders would be mixed in with all the other orders in the ORDER-NUMBER-DETAIL cards. By pulling the string, you could pull GREG'S orders quickly and easily. In the example shown in Figure 1, thestringwould be threaded from the GREG record in CUSTOMER-MASTER through the cards in ORDER-NUMBER-DETAIL with order numbers 0001, 0004, and 0009. (Remember, the figure represents the fact that these are GREG's orders by color coding them red, to match GREG's record in CUSTOMER-MASTER. Similarly, ANDREA'S records are color-coded green, Ken's are yellow, and George's are Olive.) Accessing Chains from a ProgramSuppose we wanted an application program to access all the orders associated with a particular customer--GREG. We would begin by having the program use the DBFIND intrinsic to locate the chain of records in the ORDER-NUMBER-DETAIL dataset with a search-item value of CUSTOMER-NAME = GREG. Figure 3 contains some sample COBOL code that demonstrates one way that this might be done. The call to DBFIND is shown at the top of the figure--with the actual procedure call occupying lines 53000 through 53500. This is quite similar to the sample code that we used last time, so we're not going to walk through this code in detail now. Rather, we want to focus on how DBFIND is used together with DBGET. At the bottom of Figure 3 (line 55000 through 55600) we see a call to the DBGET intrinsic. This intrinsic call will retrieve the first record in the GREG chain. Figure 3: Accessing Chains052300 MOVE "GREG" TO SEARCH-ITEM-VALUE. 052500 MOVE 1 TO DB-MODE. 052600 MOVE "ORDER-NUMBER-DETAIL;" TO SET-NAME. 052700 MOVE "CUSTOMER-NAME;" TO SEARCH-ITEM-NAME. 052900 053000 CALL INTRINSIC "DBFIND" USING DB-NAME, 053100 SET-NAME, 053200 DB-MODE, 053300 STATUS-ARRAY, 053400 SEARCH-ITEM-NAME, 053500 SEARCH-ITEM-VALUE. 053600 053800 IF CONDITION-CODE NOT= 0 053900 IF CONDITION-CODE = 17 054000 DISPLAY "The Customer does not exist." 054100 ELSE 054200 PERFORM IMAGE-ERROR 054300 ELSE 054500 054600 MOVE "@;" TO ITEMS-LIST 054700 MOVE 5 TO DB-MODE 054900 055000 CALL INTRINSIC "DBGET" USING DB-NAME, 055100 SET-NAME, 055200 DB-MODE, 055300 STATUS-ARRAY, 055400 ITEMS-LIST, 055500 ORDER-DETAIL-BUFFER, 055600 IGNORED-PARM 055700 056000 IF CONDITION-CODE NOT= 0 056200 IF CONDITION-CODE = 15 056300 DISPLAY "There are no more entries for that customer." 056400 ELSE 056500 PERFORM IMAGE-ERROR 056600 ELSE 056700 DISPLAY "Customer = ",CUSTOMER-NAME 056800 DISPLAY "Order Number = ",ORDER-NO-VALUE 056900 DISPLAY "Part Number = ",PART-NO-VALUE 057000 OF ORDER-DETAIL-BUFFERThis business of chaining records together can get pretty confusing, so we're going to refer back to Figure 1 and see a picture of the records we're actually accessing as we execute each intrinsic. DBFIND located the beginning of the GREG chain. Each chain in ORDER-NUMBER-DETAIL has a corresponding record (sometimes called the root of the chain) in CUSTOMER-MASTER. Each record in CUSTOMER-MASTER will contain the address of the first record in that chain. These addresses are the pointers that we represented as arrows in Figure 1. This way of structuring things allows DBGET to retrieve the first record in the chain quickly. The addresses of chained records are placed in the datasets automatically by IMAGE/SQL--your program doesn't need to contain any logic to calculate them or to maintain them. In Figure 1, the GREG record in CUSTOMER-MASTER contains the address of the first GREG record in ORDER-NUMBER-DETAIL. The address is represented in Figure 1 by the arrow pointing from the GREG record CUSTOMER-MASTER to the 0001 record in ORDER-NUMBER-DETAIL. When we call DBGET, as shown in Figure 3, it will retrieve the 0001 record in ORDER-NUMBER-DETAIL, because that's the first record in the GREG chain. Following the Greg chain shown in Figure 1, a second call to DBGET would retrieve the next record in the chain. The arrow in Figure 1 shows that this next record is order number 0004. There are two or three important points that must be made here.
"There are no more entries for that customer."
Other Uses for DBGETThroughout this discussion, we've been using DBGET to read the records in a particular chain. If that's all that DBGET could do, it would be a useful tool. But DBGET contains many more capabilities than that. Refer once again to Figure 3, and let's take a closer look at the sample COBOL code that calls DBGET. Earlier, we talked about the fact that DBGET can be used to access data in several ways--serially, using direct access, keyed access, or chained access. Application programs must use the parameters that are passed to the DBGET intrinsic to tell it which kind of access to use. In Figure 3, the statement that calls DBGET begins on 55000 and continues through 55600. In preparation for the CALL statement, our sample code establishes an access mode of 5 (line 54700). The integer value 5 is telling DBGET that we want to do chained access. Table 1 shows the various values that can be passed to DBGET in this parameter. Note that a value of 5 literally tells DBGETRead the next record in a chain previously located by DBFIND.
Read the contents of the specified hash location.We'll return to this in a future article. Updating a DatabaseWe will briefly visit a new intrinsic in this installment called DBUPDATE. DBUPDATE is used to make changes to an existing record in an IMAGE/SQL database. We'll wind up this installment by quickly glancing through an example of how you might use DBUPDATE to make changes to an existing record in a database. Figure 4 contains some sample COBOL code that makes use of this intrinsic. Note that this sample code does not use the same database that we've been using up until now, so the data definitions are different. The sample code begins with some DISPLAY and ACCEPT statements (lines 58500 through 59600). This is standard COBOL logic that is prompting the user to enter a part number. On line 59600, we use the ACCEPT verb so that the user can specify which part number he wants to update. Lines 59900 through 61200 all support the calling of the DBGET intrinsic. Remember, before you can use DBUPDATE to change the contents of a record, you must first use DBGET to retrieve the record that you want to update. In this way, your application program can be sure that you are updating the right record. It can also examine the contents of the record before it changes them. In preparation for the CALL statement on 60400, our sample code establishes an access mode of 7 (line 59900). Referring to Table 1, we can see that mode 7 forces DBGET to retrieve the record using keyed access. So the code that appears in Figure 4 is retrieving the record in the PARTS dataset that has a part-number matching the one entered by the end user. Next, the code in Figure 4 checks for a variety of error conditions, and then prompts the user for changes to the record that it retrieved (lines 62100 through 63500). Figure 4: Using DBUPDATE058500 UPDATE-PART-INFORMATION. 058800 059400 DISPLAY "Please enter the Part Number to update: " 059500 WITH NO ADVANCING. 059600 ACCEPT SEARCH-ITEM-VALUE FREE. 059800 059900 MOVE 7 TO DB-MODE. 060000 MOVE "@;" TO ITEMS-LIST. 060100 MOVE "PARTS;" TO SET-NAME. 060200 060400 CALL INTRINSIC "DBGET" USING DB-NAME, 060500 SET-NAME, 060600 DB-MODE, 060700 STATUS-ARRAY, 060800 ITEMS-LIST, 060900 PARTS-BUFFER, 061000 SEARCH-ITEM-VALUE. 061200 061300 IF CONDITION-CODE NOT = 0 061500 IF CONDITION-CODE = 17 061600 DISPLAY "The Part does not exist." 061700 ELSE 061800 PERFORM IMAGE-ERROR 061900 ELSE 062100 DISPLAY "Current Part Number = ",PART-NO-VALUE 062200 OF PARTS-BUFFER 062300 DISPLAY " New Value : " 062400 WITH NO ADVANCING 062500 ACCEPT PART-NO-VALUE OF PARTS-BUFFER FREE 062700 DISPLAY "Current Description = ",PART-DESCRIPTION-VALUE 062800 DISPLAY " New Value : " 062900 WITH NO ADVANCING 063000 ACCEPT PART-DESCRIPTION-VALUE FREE 063200 DISPLAY "Current Quantity in Stock = ",QTY-IN-STOCK-VALUE 063300 DISPLAY " New Value : " 063400 WITH NO ADVANCING 063500 ACCEPT QTY-IN-STOCK-VALUE FREE 063700 063800 MOVE 1 TO DB-MODE 063900 064100 CALL INTRINSIC "DBUPDATE" USING DB-NAME, 064200 SET-NAME, 064300 DB-MODE, 064400 STATUS-ARRAY, 064500 ITEMS-LIST, 064600 PARTS-BUFFER 064800 064900 IF CONDITION-CODE NOT= 0 065100 IF CONDITION-CODE = 41 065200 DISPLAY "The value of the Part Number", 065201 " cannot be changed" 065210 ELSE 065300 IF CONDITION-CODE = 42 065400 DISPLAY "You do not have the ability to", 065500 " update the data item." 065600 ELSE 065700 PERFORM IMAGE-ERROR 065800 ELSE 065900 DISPLAY "The Part was successfully updated". 066000 066100 DISPLAY SPACE.Finally, starting at line 64100, we call DBUPDATE to apply the changes to the database. In the next article, we'll take a closer look at DBUPDATE and see how it differs from DBPUT--the other intrinsic that can be used to write to a database. George Stachnik works in technical training in HP's Network Server Division. |
![[3khat]](/img/3khat16.ico)
3kRanger