←Part 23 Part 25→
The HP 3000--For Complete Novices
Part 24: IMAGE/SQL Database Access and Intrinsics
 |
by George Stachnik
In the last few installments, we've learned how to perform a few simple but practical tasks using HP's IMAGE/SQL database management system. We've created an IMAGE/SQL database, and seen how to add a single record to it using QUERY. We saw how to use IMAGE/SQL's Database Access Mode number to specify how a database is to be shared among multiple users. This time around, we're going to begin looking at how to load our new database with records. It's true that we could load the database one record at a time using QUERY. But by contemporary standards, QUERY is pretty old and clunky. Using it to add hundreds or thousands of records to a database would be a very tedious and error-prone process. It's the kind of thing that a program could do much better. So in most cases, you'll want to load your database using an application program that was specially designed for that purpose. For that reason, this month's article will begin focusing on application programs that use database intrinsics. Let's begin by reviewing how we used QUERY to enter a record into our IMAGE/SQL database. You may recall the example shown in Figure 1. This example came straight out of part 21 of this series. Figure 2 shows us a somewhat more graphical representation of what the database looks like after we perform the operation shown in this example.
Figure 1:
Figure 2:
As you can see in Figure 2,
the database has three datasets--Customer, Date-Master, and Sales. (This is
exactly the structure that we created in part 21 of this series, and which we
will be using throughout our discussion of IMAGE/SQL.) The figure represents
each dataset as a table.
Return to Figure 1 for a moment. We used QUERY to add a record to the
Customer dataset, and then to add a matching record to the Sales dataset. You
can see those records in tabular form in Figure 2-- but notice that
Figure 2 also shows a third record in DATE-MASTER. Figure 1 did
not show us entering a record into the DATE-MASTER dataset, but it's there
nevertheless. Because Date-Master is an Automatic Master, the record
was created for us automatically when we entered the record into the SALES
detail dataset.
In addition to the user data that we actually typed into Query in Figure
1, our database also contains some system data that we'll need to discuss
briefly. Remember that master datasets act as indices to the records in the
detail datasets. In other words, every record in our master datasets contains
pointers to the corresponding records in the matching detail datasets.
In Figure 2, we've represented this in the form of a number in the
ACCOUNT column. Note the (1) that appears next to George's name in the
CUSTOMER master dataset: this is telling us that the corresponding detail
dataset (SALES) contains exactly one record that has the name GEORGE in the
ACCOUNT item.
Don't forget that DATE-MASTER is also a master dataset. Like ACCOUNT, it acts
as an index into the records in the SALES dataset. So, in Figure 2 we
see the number (1) next to the date. This tells us that there is only one
record in the SALES dataset that bears this date.
Suppose that our customer (George) were to phone in some more orders. What
would the database look like after they were added? Figure 3 shows us.
Figure 3:
The first thing we notice in Figure 3 is that the contents of the
Customer dataset haven't changed much. There's still only one record there,
and it seems (at least at first glance) to contain the same data that it
contained in Figure 2. But if you look closer, you'll see that there
has been one small change. The only thing that's different is that the (1)
that appeared next to George's name is now a (4). Remember that this number
represents the number of records in the SALES dataset that have this search
value. Remember too that, although it's not explicitly shown in the figure,
the record in ACCOUNT also contains a pointer to the first record in SALES
that is part of the George account.
Let's see how DATE-MASTER changed between Figure 2 and Figure 3.
There are now three records in the DATE-MASTER dataset; before there was only
one. At first glance this may seem wrong--didn't we say that DATE-MASTER
records are added automatically when we add records to the SALES detail
dataset?
Yes, they are. But these records are added only when the new SALES record
contains a value in the DATE field that we haven't seen before. In Figure
2 there are four records in the SALES dataset, but two of them have the
same date (11/12/99). So there are only three unique dates in SALES.
Consequently, the DATE-MASTER dataset shown in Figure 2 contains three
records--one for each of the three dates that have appeared in the SALES
dataset.
Each DATE-MASTER record contains a pointer and a record-counter to the first
SALES record with a matching date. In Figure 3, all the records have a
record-counter of "1"--except for the 11/12/99 record, which has a
record-counter of "2." This is because the SALES dataset contains
two records with the date 11/12/99. All the other dates appear only once in
SALES.
Here are some key things to remember about Figure 3. The CUSTOMER
dataset contains one record, with the value GEORGE in the ACCOUNT field. The
number (4) indicates that there are four records in the SALES dataset with
ACCOUNT=GEORGE. A set of detail dataset records that share a common
search-item value is sometimes referred to as a chain of records. So
the four records in SALES might be called the GEORGE chain. Note that
it's not enough for a bunch of records to share a common value in some field
to qualify as a chain. There's one more requirement: the field must be defined
as a search item. In this case, ACCOUNT is indeed a search item, which is why
this field appears in two datasets--the detail dataset (SALES) and the
corresponding manual master (CUSTOMER).
So far, our company has only one customer--George. Let's add three more
customers named Moe, Larry, and Curly. In this way, we'll create three more
chains of records--one for Moe, one for Larry, and one for Curly. Remember
that we cannot add SALES records for our new customers to the database until
we have first added records to the CUSTOMER dataset. This must be done
manually, because CUSTOMER is a manual-master.
By manually I mean that some piece of software must explicitly request
that the new CUSTOMER records be added. You could do this with QUERY (as we
saw with George in Figure 1). But it's far more likely that an
application program would do this for you with an intrinsic call. We
first encountered intrinsics in our discussion of the MPE/iX file system some
months back. In just a moment, we're going to begin looking at how programmers
can use intrinsics to add, delete, and update records in an IMAGE/SQL
database. But it doesn't make any difference to the structure of the database.
Whether we use intrinsics or QUERY (or any other software), the structure of
the database will be the same.
Figure 4 shows what our database might look like after we have added
three new records to the CUSTOMER dataset. Note that adding new customers to the
CUSTOMER master has no impact on the SALES detail dataset, or on the DATE-MASTER
dataset. All we've done is add records to CUSTOMER--all the pointers and
record-counters that we talked about earlier are (so far) zeroed out. That's
because there are no records in SALES for them to point to--at least, not yet.
Figure 4:
In Figure 4 our database contains four chains. One of them (GEORGE) has
four records. In spite of the fact that the other three (Moe, Larry, Curly)
have no records in them, they are still technically called chains.
Every chain begins in a master dataset (in this case CUSTOMER). So a chain is
created by inserting a record in a master dataset. Records are added to a
chain by adding matching records to the corresponding detail dataset--in this
case the SALES dataset. So, let's create some orders for Moe, Larry, and Curly
and develop those chains. Figure 5 shows what
our database might look like after adding a few orders for each of our three
new customers (two orders for Moe and one each for Larry and Curly). Take a
moment to verify that the records in each of the three datasets are as you
think they should be.
Figure 5:
I hope this walkthrough has helped you understand a little bit about how
records in an IMAGE/SQL database are organized. It may seem like a lot to
remember, but really, we can summarize what we've talked about in three
rules:
Figure 6:
We mentioned earlier that QUERY is a useful learning tool, because its
commands are very analogous to the intrinsics. Referring back to Figure
1 for a moment, note that the first QUERY command that we entered was
DATA-BASE=ORDERS. This command forces QUERY to call the DBOPEN intrinsic in
exactly the same way that a COBOL program that you write might call DBOPEN.
QUERY prompts us for values to use in the parameters (password, access-mode)
and then calls DBOPEN to open the database.
Once your program has opened the database, it may want to begin retrieving
records from it, especially if the program will be doing some kind of
transaction processing. Keep in mind that the records in IMAGE/ SQL databases
are organized into chains. A chain is a set of records having a common
search item. Each chain is represented by a record in a master dataset.
For example, in the database shown in Figure 5, there are a total of
four CUSTOMER chains: George, Moe, Larry, and Curly. When the programmer calls
DBFIND, he will pass it a string of parameters that tell it what search item
to look for. Based on the criteria in the parameters, DBFIND will return a
record from a master dataset. This record will contain a pointer to the first
record in the corresponding chain of records in a detail dataset.
If the idea of chains still seems a little confusing, think of it this way:
Imagine each dataset as a box of index cards, with each card representing a
record in the dataset. Suppose each card in the master datasets has a piece of
string attached to it. And each card in the detail datasets has a small hole
punched in the corner of the card just above the ACCOUNT field.
To understand how chains work, imagine that you threaded the string that's
attached to the "GEORGE" card from the CUSTOMER dataset through the
little hole in the corner of each "GEORGE" card in the SALES
dataset. Now pull the GEORGE card out of the CUSTOMER box and pull on that
string. The string would lead you to the GEORGE cards (and only the GEORGE
cards) from the SALES box.
Similarly, each record in DATE-MASTER would also have a string attached to
it--one that was threaded through a hole in each SALES card that bore the same
date. Pull on the 2/3/00 string, and you will be led directly to the records
in SALES that bear the date: 2/3/00.
DBFIND is the intrinsic that your programs use to find a chain. Figure 7
shows sample COBOL
code that calls DBFIND. Note that the first two parameters being passed to
DBFIND are the names of the database and dataset that we're searching. The
dataset name must specify a master dataset because every chain is represented
by a unique record in a master dataset. The item-name and item-value are two
parameters passed to DBFIND that tell it which chain to find. So, for example,
in the sample code in Figure 7, if we had moved the string
"GEORGE" to the COBOL data-item named SEARCH-ITEM-VALUE, and moved
the string "ACCOUNT" to the data-item named SEARCH-ITEM-NAME, we
would be telling IMAGE/SQL to find the string of records with ACCOUNT=GEORGE.
DBFIND returns information to the calling program, telling it whether or not a
chain of records with the specified value was found, and if so, how many
records are in the chain.
Figure 7:
If you wish to retrieve records from an IMAGE/SQL database using QUERY, you
will usually begin by using the FIND command to locate the beginning of a
chain. It's no coincidence that the QUERY command's name is so similar to the
name of the corresponding intrinsic. The FIND command is used to identify a
particular chain of records. For example, consider the following QUERY
command:
 FIND ACCOUNT=GEORGE
This command causes QUERY to call the DBFIND intrinsic, locating the chain of
records that bear the string GEORGE in the ACCOUNT field.
Once a chain of records has been located by DBFIND, the next step is to
retrieve the first record in the chain. This can be done using an intrinsic
called DBGET.
Referring to Figure 5, once we've done a DBFIND on the GEORGE chain, a
subsequent call to DBGET will retrieve the first GEORGE record in the SALES
dataset (in this case, the first record in SALES). If we had done a DBFIND on
Moe (instead of GEORGE), then our DBGET would have retrieved the first Moe
record in SALES instead (that is, the fifth record in the SALES dataset--the
one that has Moe ordering a pie). The next two intrinsics in Table 1
(DBUPDATE and DBDELETE) can be used, respectively, to update or delete a
record that you've just retrieved with DBGET. We'll discuss those--and all the
IMAGE intrinsics--in more detail in a future article.
We've taken a quick look at all the intrinsics in Table 1 except the
one that is most likely to be used to load a new database. Loading a database
is mostly a matter of inserting new records into a database. This is achieved
using DBPUT.
A call to DBPUT can be used to insert a new record into a manual master
dataset. We've seen that this must be done before writing corresponding
records to the detail datasets. Once the manual-master records are in place,
DBPUT can also be used to add the records to the detail datasets.
Keep in mind that you cannot use DBPUT to explicitly add a record to an
automatic master. Automatic-master records are inserted automatically when the
detail dataset records are added using DBPUT.
And finally, DBCLOSE is used to close the database when you're finished with
it.
That wraps up this quick look at how IMAGE/SQL databases are structured, and
how some of the most frequently used intrinsics interact with one another.
Clearly, we have not explained these intrinsics in any detail--nor was that
our intent. At least not yet. Next time, we'll take a closer look at COBOL
programming with IMAGE/SQL databases.
George Stachnik works in technical training in HP's Network Server Division. |
![[3khat]](/img/3khat16.ico)
3kRanger