←Part 25 Part 27→
The HP 3000--For Complete Novices
Part 26: IMAGE/SQL Application Logic--the Status Array
 |
by George Stachnik
In the previous article in this series, we began to explore the way IMAGE/SQL application programs work. We saw examples of how IMAGE/SQL uses a status array to tell a calling application program when something has gone wrong. We also saw how the DBEXPLAIN intrinsic can be used to display error messages, based on the contents of the status array. This time around, we're going to look at more IMAGE/SQL intrinsics, and see how they can be used in concert to produce an IMAGE/SQL application program. Let's begin with a brief review of DBOPEN, an intrinsic that we've studied in at least two earlier articles in this series. Figure 1 shows the starting point for a typical IMAGE/SQL application program. It contains two pieces of sample COBOL code. At the top is the definition of the STATUS-ARRAY from the working storage section. An array of data items in exactly this format is used by virtually every IMAGE/SQL intrinsic, including DBOPEN. The bottom half of Figure 1 shows a piece of the procedure division of a COBOL program that uses DBOPEN to open an IMAGE/SQL database. Figure 1: Calling DBOPEN 002380 01 STATUS-ARRAY. 002390 05 CONDITION-CODE PIC S9(4) COMP. 002400 05 ENTRY-LENGTH PIC S9(4) COMP. 002410 05 RECORD-NUMBER PIC S9(9) COMP. 002420 05 CHAIN-LENGTH PIC S9(9) COMP. 002430 05 BACKWARD-POINTER PIC S9(9) COMP. 002440 05 FORWARD-POINTER PIC S9(9) COMP. 002450 =================================================== 024100 024500 MOVE " ORDERS;" TO DB-NAME. 024600 MOVE "ORIOLES;" TO DB-PASSWORD. 024700 MOVE 1 TO DB-MODE. 024800 025000 CALL INTRINSIC "DBOPEN" USING DB-NAME, 025100 DB-PASSWORD, 025200 DB-MODE, 025300 STATUS-ARRAY. 025600 025700 IF CONDITION-CODE NOT= 0 025800 PERFORM IMAGE-ERROR. 026000 026100 DISPLAY "ORDERS Database successfully opened.". 026200 DISPLAY SPACE.Managing Database Passwords The sample code shown in Figure 1 should be pretty familiar to you if you've been following this series of articles for the last issue or two. For the moment, I'd like to focus on one particular parameter that must always be passed to DBOPEN--the database password. (In the figure, the password is stored in a variable called DB-PASSWORD). A thorough discussion of IMAGE/SQL security is well beyond the scope of this article. But since we're discussing the DBOPEN intrinsic, it's worth taking a few minutes to discuss a few options for managing database passwords. The code shown in Figure 1 includes the database password ORIOLES. In this sample, the database password has been hard-coded into the source code of the application program itself. This may or may not be a desirable practice, depending on the security policies and practices at your particular installation. Every IMAGE/SQL application should have security policies that determine how to manage passwords. With IMAGE/SQL database passwords, you have three basic options from which to choose:
trustedentity. You are assuming that the program cannot do any harm, by virtue of the way you've designed it and coded it. Of course, endowing your application programs with this kind of power means you must have good security procedures in place. It becomes more important than ever to ensure that no unauthorized users are able to access or abuse the program that contains hard-coded database passwords. If this assumption works for you, then you don't need to have this same level of trust in your users. But by hard-coding database passwords into your program source code you create some administrative problems, even as you are solving some security problems. For one thing, it's a good security practice to change all the passwords from time to time--just in case a password falls into the wrong hands. Changing hard-coded passwords like those shown in Figure 1 entails making changes (and potentially recompiling) every application program that references a database. The second option removes the passwords from the programs. In this scenario, the application programs prompt the end users for the database passwords. Even if an unauthorized user obtains access to one of your application programs, it won't do him any good unless he also knows the (current) database password. Effectively, you are putting two barriers between unauthorized users and your database: First, they must obtain access to an application program, and second, they must also know the database passwords. The advantages gained in this way may be offset by the fact that end users must now know database passwords in order to do their jobs. This creates two potential problems. First of all, changes to the passwords must be communicated to the end users (which could be leaked to unauthorized users). And second, whenever users know a database password, you must take steps to ensure that they cannot use the password with other programs (such as Query or a 4GL) to do things to the database that your application program might not permit. To operate a truly secure environment with IMAGE/SQL, you should consider option three carefully. This third option puts the management of database passwords into the hands of a specialist called a database administrator (or DBA) and provides this person with tools for managing database security. For example, some IMAGE/SQL applications keep database passwords in a separate security database that is accessible only to the DBA. Application programs that want to open a database must retrieve the current password from the security database. If you are in the process of creating a new IMAGE/SQL application, or creating a new security architecture for an existing application, you should consider creating a security architecture of this type. You may even want to purchase a third-party product that enhances basic IMAGE/SQL security along these lines. Condition Codes and Status Arrays After calling DBOPEN, the sample code shown in Figure 1 immediately checks the condition code to ensure that the database has been opened successfully. As shown in the top part of Figure 1, the condition code is part of a larger array of binary numbers called the status array. DBOPEN (like most of the intrinsics) expects that the calling program will pass it an array of integer items like the one shown at the top of Figure 1. The status array must be made up of two sixteen-bit integers (in the figure they are named CONDITION CODE and ENTRY-LENGTH) followed by four 32-bit integers (in the figure these are named RECORD-NUMBER, CHAIN-LENGTH, BACKWARD-POINTER and FORWARD-POINTER). In your COBOL program, you can call these integers by whatever names you please, but the order and size of each of them is mandatory. If the DBOPEN intrinsic detects an error condition, it will place a non-zero integer value in the first 16 bits of the status array (CONDITION CODE). It is then the responsibility of the calling program to detect this non-zero value and act accordingly. Let's see how it's handled by the code in Figure 1. Line 025700 of the COBOL code shown in Figure 1 checks CONDITION CODE to see if it contains a zero value. If not, it calls a routine called IMAGE-ERROR. This routine (which is not shown in the figure) will call the DBEXPLAIN intrinsic. We discussed DBEXPLAIN in the previous article in this series. DBEXPLAIN's job is to interpret the value found in CONDITION CODE and display an appropriate error message. Then the IMAGE-ERROR routine will terminate the program, since there's not much left for it to do if the database cannot be successfully opened. If, on the other hand, the condition code is indeed zero (meaning that the database has been opened successfully), then the code in Figure 1 displays a message to that effect in line 026100. At this point, we could use other IMAGE/SQL intrinsics to access the database in a variety of ways. But for the purposes of this article, we're going to focus first on the simplest thing that we could do. Let's suppose that we want our application program to display information about the database (without actually accessing any of the data that's stored there). Obtaining Information About a Database Figure 2 shows some sample COBOL code calling another IMAGE/SQL intrinsic. The intrinsic called DBINFO retrieves information about a database that you have opened with DBOPEN. Take a look at the parameters that we are passing to DBINFO. There are five of them: The first two are DB-NAME (an ASCII variable containing the name of the database) and SET-NAME (an ASCII variable containing the name of the dataset that you're interested in). Figure 2: Calling DBINFO 030400 030500 DISPLAY SPACE. 030600 030700 MOVE 202 TO DB-MODE. 031000 031100 CALL INTRINSIC "DBINFO" USING DB-NAME, 031200 SET-NAME, 031300 DB-MODE, 031400 STATUS-ARRAY, 031500 SET-INFO-BUFFER. 031800 031900 IF CONDITION-CODE NOT= 0 032000 PERFORM IMAGE-ERROR. 032200 032205 MOVE TOTAL-ENTRIES TO DISPLAY-NUM-ENTRIES. 032206 MOVE CAPACITY TO DISPLAY-CAPACITY. 032208 DISPLAY DSET-NAME, " now has ", 032209 DISPLAY-NUM-ENTRIES " data entries.". 032210 DISPLAY "The capacity of the set is ", DISPLAY-CAPACITY, ".". 032700 DISPLAY SPACE. 033200So far, DBINFO doesn't seem all that interesting. The new wrinkle with the DBINFO intrinsic involves the third parameter, DB-MODE. We've seen database access modes before. DBOPEN uses the database access mode to determine what kind of access you want to have to this particular database. (In Figure 1, we moved a value of 1to the DB-MODE variable before calling DBOPEN. This tells DBOPEN that you want to give the calling program exclusive access to the database.) The meaning of the various values of DB-MODE used by DBOPEN were discussed in the previous article in this series, and they are summarized in Table 1.
202into the DB-MODE variable. The only valid values shown in Table 1 are 1 through 8. A value of 202 is nowhere to be found. But remember that Table 1. is meant to be used only with DBOPEN (and, coincidentally, by QUERY, which uses the same values as DBOPEN). The DBINFO intrinsic has its own table of valid DB-MODE values and these are shown in Table 2.
002260 01 SET-INFO-BUFFER. 002270 05 DSET-NAME PIC X(16). 002280 05 DSET-TYPE PIC X(02). 002290 05 SET-ENTRY-LENGTH PIC S9(4) COMP. 002300 05 BLOCKING-FACTOR PIC S9(4) COMP. 002310 05 ZERO1 PIC S9(4) COMP. 002320 05 ZERO2 PIC S9(4) COMP. 002330 05 TOTAL-ENTRIES PIC S9(9) COMP. 002340 05 CAPACITY PIC S9(9) COMP. 002350 002360 01 SET-NAME PIC X(16). 002370 002380 01 STATUS-ARRAY. 002390 05 CONDITION-CODE PIC S9(4) COMP. 002400 05 ENTRY-LENGTH PIC S9(4) COMP. 002410 05 RECORD-NUMBER PIC S9(9) COMP. 002420 05 CHAIN-LENGTH PIC S9(9) COMP. 002430 05 BACKWARD-POINTER PIC S9(9) COMP. 002440 05 FORWARD-POINTER PIC S9(9) COMP. 002450As in the case of the STATUS-ARRAY, the format of SET-INFO-BUFFER is predefined and must be made up of variables of a predefined size, and organized in a predefined order. The format of the SET-INFO-BUFFER depends on the value being passed to DBINFO in DB-MODE. So in this case, we are seeing the format that goes with a value of 202 (see line 030700). This value effectively tells DBINFO that we want it to return specific information about a particular dataset:
051800 DISPLAY "Please enter the Order Number to retrieve: " 051900 WITH NO ADVANCING. 052000 ACCEPT SEARCH-ITEM-VALUE FREE. 052200 DISPLAY SPACE. 052300 052500 MOVE 1 TO DB-MODE. 052600 MOVE "ORDER-DETAIL;" TO SET-NAME. 052700 MOVE "ORDER-NO;" 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 Order 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 "No more entries for that Order." 056400 ELSE 056500 PERFORM IMAGE-ERROR 056600 ELSE 056700 DISPLAY "Purchase Order Number = ",ORDER-NO-VALUE 056800 DISPLAY "Line Item Number = ",ITEM-NO-VALUE 056900 DISPLAY "Part Number = ",PART-NO-VALUE 057000 OF ORDER-DETAIL-BUFFER 057100 DISPLAY "Quantity Ordered = ",QUANTITY-VALUE 057200 DISPLAY "Date Part Shipped = ",SHIPMENT-DATE-VALUE.The first intrinsic that we're calling in Figure 4 is DBFIND (see line 053000). To understand what DBFIND is doing in this example, we must first understand some structural basics about this particular IMAGE/SQL database. This database contains a detail dataset called ORDER-DETAIL. This dataset contains entries (or records if you prefer) that describe the orders that have been taken. Each order may be made up of one or more entries. This means that for a given order, there may be many records in the ORDER-DETAIL dataset. In order to process an order, therefore, we must first find all the records in ORDER-DETAIL that belong to this particular order. That is, effectively, what DBFIND is doing in Figure 4. It's important to understand that DBFIND isn't retrieving the order from the database. It's only determining whether it exists (that is, whether there are any records that match the specified order number) and, if so, how many matching records exist. Let's walk through the code in Figure 4 to see exactly how this is done. Beginning on line 051800, we ask the user what order-number he is interested in. The value that the user types is placed in a variable called SEARCH-ITEM-VALUE. Then, beginning on line 052500, we start getting set up to call DBFIND. The call actually happens on line 053000, but there's a lot of housekeeping that takes place first. On line 052600, we tell DBFIND that we want it to search a particular dataset (ORDER-DETAIL) and that we want it to look for records that have a particular value in a data item (or field, if you prefer) that is named ORDER-NO (see line 052700). The ASCII name of this data item is passed to DBFIND in a variable called SEARCH-ITEM-NAME (see line 053400), and the value that we want DBFIND to search for is the one that we placed in SEARCH-ITEM-VALUE in line 52000. Remember that DBFIND doesn't actually retrieve the records that make up the order. Its sole purpose is to find out how many records (if any) match the specified criteria. In earlier articles in this series, we said that the set of records that contain matching values in a search item is called a chain. DBFIND's job, in the simplest terms, is to find chains of records. Before we see what happens when a chain exists that matches our order number, let's pause to see what happens when no such order exists. If there are no records that match the criteria, DBFIND will return a value of 17 in the condition code. As you can see in Figure 4, (line 053900), our application program traps that error and handles it gracefully. Any other non-zero value in the condition code is treated as a fatal error to be handled by DBEXPLAIN in the IMAGE-ERROR routine (see line 054200). On the other hand, if there are one or more entries that match the order number specified in SEARCH-ITEM-VALUE, then the code in Figure 4 will fall through to line 054600. Here we will begin retrieving the records in this chain, one at a time, using another intrinsic, DBGET. The DBGET intrinsic, when called as shown in the figure, will retrieve the first item in the chain that was found by DBFIND. In the sample code shown in Figure 4, the call to DBGET is followed by code that displays the results (lines 056700 through 057200). Of course, in a real application program, you would probably want to do considerably more than that--but keep in mind that this is just sample code being used to illustrate a point, not a real application. The logic found in typical IMAGE/SQL applications would call DBFIND once--to locate the chain of records that match the search criteria. Then the program would call DBGET repeatedly at this point, until all the records in the chain were exhausted. Remember that each call to DBGET retrieves one (and only one) record (or data entry, if you prefer) from the database. These records are not being read in sequential order. They are being read in chainedorder. That is, the chain of records that matches a particular search item value is being read in the order in which they appear in the chain. When the last record in the chain is retrieved, DBGET will return a condition code value of 15, which indicates that the end of the chain has been reached. (Note that the sample code in Figure 4 includes a trap for this condition so that the program can handle it gracefully, rather than treating it as a fatal error by calling the IMAGE-ERROR routine.) In this article, we've taken a much closer look at how IMAGE/SQL applications use the intrinsic interface to read data from a database, and handle certain kinds of errors. Next time, we'll learn more about IMAGE/SQL applications. George Stachnik works in technical training in HP's Network Server Division. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
![[3khat]](/img/3khat16.ico)
3kRanger