by George Stachnik
In the previous article in this series, we saw how existing records in an IMAGE/SQL database can be updated using DBUPDATE. This time out, we're going to look at DBPUT, the intrinsic that is used to write entirely new records to a database. Figure 1 shows a fragment of a COBOL program that is using DBPUT. Let's walk through the code, line by line, in order to understand how this intrinsic might be used. The sample code is part of an application that might be used by an auto parts store. The database that is maintained by this particular code fragment contains information about the various parts that our company sells. One of the datasets in this database is a manual master dataset called PARTS. The PARTS dataset contains one record (i.e., one data entry) for each part that our company sells. Before we can begin stocking a new part, we must write a record into the PARTS dataset that contains information about the part. The top of the figure (lines 1890-2220) shows a few lines of code from the WORKING -STORAGE SECTION. This code defines the PARTS-BUFFER in which we will build the record image before writing it to the database. Note that each PARTS data entry consists of three data items:
Adding an Entire RecordLine 36000 initializes a variable called ITEMS-LIST to the value "@;". This is effectively telling DBPUT that the PARTS-BUFFER contains values for each and every data item (that is, each and every field) that exists in the data entry (that is, in the record). In other words, we want the record that we are about to write to the database to contain data in every data item. You might be wondering why we need to tell DBPUT something that seems so obvious--at least on the surface. But the fact is, it's not obvious at all. Remember that the PARTS dataset is not a file. It's part of an IMAGE/SQL database. One of the differences between records in a file and data entries in a database is the way security is implemented. If you have access to a record in a file, then you necessarily have access to the entire record. The file system does not manage security at the record level. But with an IMAGE/SQL database, the situation is very different. When you open an IMAGE/SQL database, you must provide a password. The password determines which parts of the database you can access (and how), using the user class number. This class number is assigned using rules that were defined in the database schema. (For a review of schemas, including user class numbers, refer back to part 20 of this series.) If your password allows you write access to a dataset, then you can use DBPUT to write new data entries to the dataset. If your password furthermore grants you write access to each and every data item in the data entry, then you can use a value of "@" in ITEMS-LIST, as shown in Figure 1. But suppose the database password that you specified grants you access only to certain data items (i.e., to certain fields) within each data entry (i.e., within each record). In that case, when you called DBPUT to insert a new data entry, you could specify the names of the specific data items that you wanted to write. The value of this will become apparent when we discuss concurrent database access in more detail in a future article. Figure 1: COBOL DBPUT to a Master Dataset001890 DATA DIVISION. 001900 WORKING-STORAGE SECTION. 001910 002170 002180 01 PARTS-BUFFER. 002190 05 PART-NO-VALUE PIC X(12). 002200 05 PART-DESCRIPTION-VALUE PIC X(20). 002210 05 QTY-IN-STOCK-VALUE PIC S9(9) COMP. 002220 033300 ADD-A-NEW-PART. 033600 033700 DISPLAY "Add A New Part.". 033800 DISPLAY SPACE. 034000 034100 DISPLAY 034200 "To add a new Part, please enter the following information:". 034300 DISPLAY SPACE. 034500 DISPLAY "Part Number: " WITH NO ADVANCING. 034600 ACCEPT PART-NO-VALUE OF PARTS-BUFFER FREE. 034800 DISPLAY "Description of Part (max 20 characters): " 034900 WITH NO ADVANCING. 035000 ACCEPT PART-DESCRIPTION-VALUE FREE. 035200 DISPLAY "Quantity In Stock: " WITH NO ADVANCING. 035300 ACCEPT QTY-IN-STOCK-VALUE FREE. 035500 DISPLAY SPACE. 035700 035800 MOVE "PARTS;" TO SET-NAME. 035900 MOVE 1 TO DB-MODE. 036000 MOVE "@;" TO ITEMS-LIST. 036300 036400 CALL INTRINSIC "DBPUT" USING DB-NAME, 036500 SET-NAME, 036600 DB-MODE, 036700 STATUS-ARRAY, 036800 ITEMS-LIST, 036900 PARTS-BUFFER. 037200 037300 IF CONDITION-CODE = 0 037500 DISPLAY "The Part was successfully added!" 037600 PERFORM GET-SET-INFO 037700 ELSE 037800 IF CONDITION-CODE = 16 038000 DISPLAY "There is no more room in the data set." 038100 ELSE 038200 IF CONDITION-CODE = 43 038300 038400 DISPLAY "That Part already exists." 038500 ELSE 038600 IF CONDITION-CODE = -53 038800 DISPLAY 038900 "No value was given for Part Number!" 039000 ELSE 039100 PERFORM IMAGE-ERROR. 039200 039300 DISPLAY SPACE. Error HandlingReturning to Figure 1, the actual call to DBPUT takes place beginning on line 36400. When DBPUT returns control to the calling application, the first thing we must do is to test the condition code (line 37300). A value of zero indicates that DBPUT has successfully added the record to the database, and we advise the user of this success in line 37500. Of course, there are a number of things that might go wrong. Some of these errors are things that the application program itself should trap and try to handle gracefully. For example, in line 37800 we test the condition code for a value of 16. DBPUT will return this value if the dataset that we were trying to write to is full. IMAGE/SQL was enhanced some years ago to permit the automatic expansion of datasets. This can effectively eliminate the possibility of error 16, but because many IMAGE/SQL applications were written before HP made the enhancement, you'll probably see code like this if you're maintaining older applications code. In the example shown in Figure 1, the PARTS dataset is a manual master, and PART-NO-VALUE is a search item. In a master dataset, every record must have a valid search item value, and there can be only one record with a given search item value. Consequently, our program tests for a condition code of -53 in line 38600. This condition code will be returned if the user tries to create a record with a null search item value (i.e., with no part number). In line 38200, we test for a condition-code value of 43. Error 43 is encountered when a search item already exists. (For example, there can be only one part with a part number of "12345"). By contrast, if we were writing to a detail dataset, the potential errors that we'd have to plan for would be slightly different. Figure 2 shows a code fragment that uses DBPUT to write a record to a detail dataset. The dataset is called "ORDER-DETAIL" (see line 42500). The most significant differences between the code in Figure 1 and that in Figure 2 are in the error processing. Some of the error processing is similar (note the test for error 16 in line 44500), but there are important differences. Note that there is no test for error 43 in Figure 2. That's because, unlike a master dataset, a detail dataset can contain multiple records that share a common search item value. Such a set of records is called a "chain." We have been discussing chains in great detail in the previous articles of this series. Figure 2: COBOL DBPUT to a Detail Dataset039800 ADD-A-NEW-ORDER. 040100 040200 DISPLAY "Option 4, Add A New Order, selected.". 040300 DISPLAY SPACE. 040500 040600 DISPLAY 040700 "To add a new Order, please enter the following information:". 040800 DISPLAY SPACE. 040900 DISPLAY "Purchase Order Number: " WITH NO ADVANCING. 041000 ACCEPT ORDER-NO-VALUE FREE. 041200 DISPLAY "Part Number: " WITH NO ADVANCING. 041300 ACCEPT PART-NO-VALUE OF ORDER-DETAIL-BUFFER FREE. 041500 DISPLAY "Quantity Ordered: " WITH NO ADVANCING. 041600 ACCEPT QUANTITY-VALUE FREE. 041800 DISPLAY "Shipment Date (YY/MM/DD format): " 041900 WITH NO ADVANCING. 042000 ACCEPT SHIPMENT-DATE-VALUE FREE. 042200 MOVE 1 TO ITEM-NO-VALUE. 042300 DISPLAY SPACE. 042400 042500 MOVE "ORDER-DETAIL;" TO SET-NAME. 042600 MOVE 1 TO DB-MODE. 042700 MOVE "@;" TO ITEMS-LIST. 042800 043100 CALL INTRINSIC "DBPUT" USING DB-NAME, 043200 SET-NAME, 043300 DB-MODE, 043400 STATUS-ARRAY, 043500 ITEMS-LIST, 043600 ORDER-DETAIL-BUFFER. 043700 044000 IF CONDITION-CODE = 0 044200 DISPLAY "The Order was successfully added!" 044300 PERFORM GET-SET-INFO 044400 ELSE 044500 IF CONDITION-CODE = 16 044700 DISPLAY "There is no more room in the data set." 044800 ELSE 044900 IF CONDITION-CODE >= 100 AND 045000 CONDITION-CODE <= 199 045200 DISPLAY "The Part Number given does not exist." 045300 ELSE 045400 IF CONDITION-CODE = -53 045600 DISPLAY 045700 "Values for Purchase Order Number and Part Number " 045800 DISPLAY "MUST be given." 045900 ELSE 046000 PERFORM IMAGE-ERROR. 046100 046200 DISPLAY SPACE.Lines 44900 and 45000 test for a range of condition codes between 100 and 199. Any value in this range will trigger the error message: "The Part Number given does not exist." These condition codes will be returned because ORDER-DETAIL is indexed by the manual master dataset called PARTS. Both ORDER-DETAIL and PARTS contain a common data item--the search item PART-NO-VALUE. This means that before you can add a record to ORDER-DETAIL that contains a given value in PART-NO-VALUE, there must already exist a corresponding record in PARTS. (In other words, you can't take an order for a part until it's listed in the PARTS dataset.) If you try to add a record to ORDER-DETAIL that has a part number that's not listed in PARTS, you'll generate an error between 100 and 199. Why are there multiple error codes associated with this error? Because each record in a detail dataset can have more than one search item (up to 100 of them). Each search item, therefore, has its own error code. If the records in this database had many search items, we could have tested for the error code associated with each search item separately. That way, if IMAGE/SQL rejected a record because a search item doesn't exist, we could use the error code to determine which search item was causing the problem. The database being used by the sample code shown in Figure 2 is not shown in the figure. Let's assume, however, that there are in fact two search items. PART-NO-VALUE is indexed by a manual master dataset, and SHIPMENT-DATE-VALUE is indexed by an automatic master dataset. Will SHIPMENT-DATE-VALUE ever return an error corresponding to a condition code between 100 and 199? No, it won't. The reason is because the master dataset that contains the search item SHIPMENT-DATE-VALUE is an automatic master. Way back in part 20 of this series, we learned that automatic masters are different from manual masters in precisely this respect. If a new value is entered for a search item in a detail dataset, and that search item is indexed by an automatic master, then IMAGE/SQL will automatically create a new record in the automatic master (hence the name, automatic master). By contrast, new records in manual masters must be entered using a separate call to DBPUT (that is to say, manually). Managing Multiuser EnvironmentsWe've seen how to use DBPUT to write records to a database. Now we're going to begin examining some of the things that can go wrong when you have multiple application programs that are writing to the same database at the same time. Before an application program can use DBPUT, it must first call DBOPEN to open the database. When you call DBOPEN, you must pass it a parameter containing a database password, and a second parameter called DB-MODE, which tells DBOPEN what kind of access you want. We've seen DB-MODE used with a number of different IMAGE/SQL intrinsics. In earlier articles in this series, we've seen it used with DBINFO and with DBUPDATE. DB-MODE is an integer value that tells the intrinsic what you want it to do for you. Each intrinsic expects different values in the DB-MODE parameter--so passing a value of 1 to DBINFO might mean something quite different than it would mean if you were to pass the same value to DBOPEN, DBUPDATE, or DBPUT. Let's review an example of DB-MODE that we covered in detail in an earlier part of this series. The DBOPEN intrinsic is not shown in Figure 1 or Figure 2, but we know that it must have been used by this program, because DBOPEN must be called by every IMAGE/SQL application. You cannot use DBGET, DBPUT, or DBUPDATE unless your application program has first called DBOPEN. DB-MODE is one of the parameters that must be passed to DBOPEN. Table 1 contains the values that can be passed to DBOPEN in the DB-MODE parameter. The value in DB-MODE tells DBOPEN how the application will access the database. For example, a value of 1, 3, or 4 in DB-MODE tells DBOPEN that you want modify access--the ability to write new records. A value of 2 tells DBOPEN that you want update access--the ability to update existing records--but without the ability to insert new records. A value of 5, 6, 7, or 8 is a request for read access--the ability to read records, without the ability to change them.
George Stachnik works in Hewlett-Packard's Commercial Systems Division in Cupertino, California. He is responsible for the development and delivery of e3000 training. |