For detailed information on SQL error handling, see the SQL documentation.

4.1.7 Compiling Procedures that Use SQL

You use the SQL precompiler, SQLPRE, when you compile a procedure containing embedded SQL statements. The SQL precompiler processes the embedded SQL statements in your program, producing an intermediate host-language source file, which it then submits to the host-language compiler to produce an object module.

The SQL precompiler command line includes both precompiler and host-language compiler qualifiers. For the precompiler, include a qualifier to specify in which host language the source is written; you can, optionally, include other qualifiers. On the command line, also include any language compiler qualifiers (such as LIST or DEBUG) that you want in effect when the precompiler submits the preprocessed source file to the language compiler. For more information on SQL precompiler qualifiers, see the SQL documentation.

You typically define a symbol to invoke the SQL precompiler. For example:

$ SCOB == "$SQLPRE/COBOL"

The following command line precompiles the procedure VR_COMPLETE_CHECKOUT_PROC:

$ SCOB/LIST VR_COMPLETE_CHECKOUT_PROC

Chapter 6 explains how to link procedures that use SQL.

4.1.8 COBOL Step Procedure Using SQL with Rdb

Example 4-7 contains a complete COBOL procedure that accesses an Rdb database using SQL.

The Complete Checkout Procedure, VR_COMPLETE_CHECKOUT_PROC, does the following:

• Uses the ACMS$GET_TID service to obtain the TID associated with the transaction
• Starts a database transaction (with the SET TRANSACTION statement)
• For the vehicle being checked out, gets the last odometer reading from the database
• Updates the car rental reservation in the database
• Updates the vehicle record in the database
• Writes a new vehicle rental history record to the database
• Returns a value to the calling task
• Handles any errors encountered in the execution of the procedure

Example 4-7 COBOL Procedure Using SQL with Rdb

IDENTIFICATION DIVISION. ************************************************************** PROGRAM-ID. VR-COMPLETE-CHECKOUT-PROC. * * * Version: 01 * * Edit: 00 * * Edit dates: 1-MAR-1991 * * Authors: DIGITAL * * Called from: VR_COMPLETE_CHECKOUT_TASK * * * ************************************************************** ************************************************************** * F U N C T I O N A L D E S C R I P T I O N * * * * This procedure is called from the VR_COMPLETE_CHECKOUT_ * * TASK and is used to write a completed reservation record * * an updated vehicle record and a new vehicle_rental_his- * * tory record to the VEHICLE_RENTALS database. * ************************************************************** ENVIRONMENT DIVISION. ************************************************************** DATA DIVISION. ************************************************************** WORKING-STORAGE SECTION. * * Return status to pass to ACMS * 01 RET-STAT PIC S9(9) COMP. 01 RSTAT PIC S9(9) COMP. * * External variables * 01 RDMS$_DEADLOCK PIC S9(9) COMP VALUE IS EXTERNAL RDMS$_DEADLOCK. 01 RDB$_DEADLOCK PIC S9(9) COMP VALUE IS EXTERNAL RDB$_DEADLOCK. 01 RDMS$_LCKCNFLCT PIC S9(9) COMP VALUE IS EXTERNAL RDMS$_LCKCNFLCT. 01 RDB$_LOCK_CONFLICT PIC S9(9) COMP VALUE IS EXTERNAL RDB$_LOCK_CONFLICT. 01 RDMS$_TIMEOUT PIC S9(9) COMP VALUE IS EXTERNAL RDMS$_TIMEOUT. 01 ACMS$_TRANSTIMEDOUT PIC S9(9) COMP VALUE IS EXTERNAL ACMS$_TRANSTIMEDOUT. 01 RDB$_SYS_REQUEST_CALL PIC S9(9) COMP VALUE IS EXTERNAL RDB$_SYS_REQUEST_CALL. 01 RDB$_BAD_TRANS_HANDLE PIC S9(9) COMP VALUE IS EXTERNAL RDB$_BAD_TRANS_HANDLE. 01 RDB$_DISTABORT PIC S9(9) COMP VALUE IS EXTERNAL RDB$_DISTABORT. 01 LIB$SIGNAL PIC S9(5) COMP VALUE IS EXTERNAL LIB$SIGNAL. * * Indicator array for null values * 01 VR_VRH_IND_ARRAY. 05 VR_VRH_IND OCCURS 6 TIMES PIC S9(4) COMP. 01 VR_VRH_IND1 PIC S9(4) COMP. * External status variables for VR messages. * EXEC SQL INCLUDE 'AVERTZ_SOURCE:VR_MESSAGES_INCLUDE.LIB' END-EXEC. * * Literals * EXEC SQL INCLUDE 'AVERTZ_SOURCE:VR_LITERALS_INCLUDE.LIB' END-EXEC. * * Define the SQL return status * EXEC SQL INCLUDE 'AVERTZ_SOURCE:VR_SQL_STATUS_INCLUDE.LIB' END-EXEC. * * Declare the global transaction context structure. This is required for SQLPRE * EXEC SQL INCLUDE 'AVERTZ_SOURCE:VR_CONTEXT_STRUCTURE_INCLUDE.LIB' END-EXEC. * * Get structure for SQLCODE and RDB$MESSAGE_VECTOR. * EXEC SQL INCLUDE SQLCA END-EXEC. * * Declare the database schema * * * Copy VEHICLE_RENTAL_HISTORY from the CDD * EXEC SQL INCLUDE FROM DICTIONARY 'AVERTZ_CDD_WKSP:VR_VEHICLE_RENTAL_HISTORY_WKSP' END-EXEC. * EXEC SQL DECLARE EXTERNAL SCHEMA FILENAME AVERTZ_DATABASE:VEHICLE_RENTALS END-EXEC. ************************************************************** LINKAGE SECTION. * * Copy RESERVATIONS from the CDD * EXEC SQL INCLUDE FROM DICTIONARY 'AVERTZ_CDD_WKSP:VR_RESERVATIONS_WKSP' END-EXEC. * * Copy VEHICLES from the CDD * EXEC SQL INCLUDE FROM DICTIONARY 'AVERTZ_CDD_WKSP:VR_VEHICLES_WKSP' END-EXEC. * Copy CONTROL workspace from the CDD - used to handle DDTM timeout * EXEC SQL INCLUDE FROM DICTIONARY 'AVERTZ_CDD_WKSP:VR_CONTROL_WKSP' END-EXEC. ************************************************************** PROCEDURE DIVISION USING VR_RESERVATIONS_WKSP, VR_VEHICLES_WKSP, VR_CONTROL_WKSP GIVING RET-STAT. ************************************************************** MAIN-PROGRAM. SET RET-STAT TO SUCCESS. IF INCREMENT_RETRY_COUNT OF VR_CONTROL_WKSP = "Y" THEN ADD 1 TO RETRY_COUNT OF VR_CONTROL_WKSP END-IF. CALL "ACMS$GET_TID" USING CS-TID GIVING RET-STAT. IF RET-STAT IS NOT SUCCESS THEN CALL "ACMS$RAISE_NONREC_EXCEPTION" USING BY REFERENCE RET-STAT GO TO EXIT-PROGRAM END-IF. * * Update the required record fields and DECLARE The appropriate null indicators * MOVE VEHICLE_CHECKOUT_SITE_ID OF VR_RESERVATIONS_WKSP TO CHECKOUT_SITE_ID OF VR_VEHICLE_RENTAL_HISTORY_WKSP. MOVE VEHICLE_ID OF VR_VEHICLES_WKSP TO VEHICLE_ID OF VR_VEHICLE_RENTAL_HISTORY_WKSP. MOVE RESERVATION_ID OF VR_RESERVATIONS_WKSP TO RESERVATION_ID OF VR_VEHICLE_RENTAL_HISTORY_WKSP. MOVE NEG-ONE TO VR_VRH_IND(5). MOVE NEG-ONE TO VR_VRH_IND(6). * Set up to trap errors returned by SQL; the precompiler will generate the * necessary tests * EXEC SQL WHENEVER SQLERROR GO TO SQL-ERROR-HANDLER END-EXEC. * Start the database transaction * EXEC SQL USING CONTEXT: CONTEXT_STRUCTURE SET TRANSACTION READ WRITE EVALUATING RS_VALID_BILL_RENTAL_CLASS AT VERB TIME, RS_VALID_VEHICL_CHKOUT_SITE_ID AT VERB TIME, VE_VALID_CURRENT_SITE_ID AT VERB TIME, VRH_VALID_VEHICLE_ID AT VERB TIME, VRH_VALID_PRIMARY_KEY AT VERB TIME RESERVING RESERVATIONS,VEHICLES,VEHICLE_RENTAL_HISTORY FOR SHARED WRITE, RENTAL_CLASSES,SITES,REGIONS FOR SHARED READ END-EXEC. * * GET-ODOMETER-READING. * Get the last return odometer reading for the vehicle being checked out * from the database. If not found, ignore it. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL USING CONTEXT :CONTEXT-STRUCTURE SELECT MAX(RETURN_ODOMETER_READING)INTO :VR_VEHICLE_RENTAL_HISTORY_WKSP.CHECKOUT_ODOMETER_READING INDICATOR :VR_VRH_IND1 FROM VEHICLE_RENTAL_HISTORY WHERE VEHICLE_ID = :VR_VEHICLES_WKSP.VEHICLE_ID END-EXEC. UPDATE-RESERVATION. * Update the reservation in the database * EXEC SQL USING CONTEXT :CONTEXT-STRUCTURE UPDATE RESERVATIONS SET CREDIT_CARD_NO = :VR_RESERVATIONS_WKSP.CREDIT_CARD_NO, CREDIT_CARD_TYPE_ID = :VR_RESERVATIONS_WKSP.CREDIT_CARD_TYPE_ID, RESERV_STATUS_FLAG = :C-ONE, RESERV_MODIFIC_FLAG = :VR_RESERVATIONS_WKSP.RESERV_MODIFIC_FLAG, BILL_RENTAL_CLASS_ID = :VR_RESERVATIONS_WKSP.BILL_RENTAL_CLASS_ID, VEHICLE_EXPECTED_RETURN_DATE = :VR_RESERVATIONS_WKSP.VEHICLE_EXPECTED_RETURN_DATE WHERE RESERVATION_ID = :VR_RESERVATIONS_WKSP.RESERVATION_ID END-EXEC. * Update the vehicle record in the database * UPDATE-VEHICLES. * EXEC SQL USING CONTEXT :CONTEXT-STRUCTURE UPDATE VEHICLES SET CURRENT_SITE_ID = :VR_RESERVATIONS_WKSP.VEHICLE_CHECKOUT_SITE_ID, AVAILABLE_FLAG = :C-ZERO WHERE VEHICLE_ID = :VR_VEHICLES_WKSP.VEHICLE_ID END-EXEC. * Write a new vehicle_rental_history record to the database * EXEC SQL USING CONTEXT :CONTEXT-STRUCTURE INSERT INTO VEHICLE_RENTAL_HISTORY VALUES (:VR_VEHICLE_RENTAL_HISTORY_WKSP:VR_VRH_IND) END-EXEC. * All database activity was successful; commit the transaction in the task * MOVE CHKOUTCOM TO RET-STAT. GO TO EXIT-PROGRAM. * Error handling SQL-ERROR-HANDLER. EVALUATE TRUE WHEN ( ( Rdb$LU_STATUS = RDB$_DEADLOCK ) OR ( Rdb$LU_STATUS = RDMS$_DEADLOCK ) OR ( Rdb$LU_STATUS = RDB$_LOCK_CONFLICT ) OR ( Rdb$LU_STATUS = RDMS$_LCKCNFLCT ) OR ( Rdb$LU_STATUS = RDMS$_TIMEOUT ) ) CALL "ACMS$RAISE_TRANS_EXCEPTION" USING BY REFERENCE ACMS$_TRANSTIMEDOUT WHEN ( ( RdB$LU_STATUS = RDB$_SYS_REQUEST_CALL ) OR ( Rdb$LU_STATUS = RDB$_BAD_TRANS_HANDLE ) OR ( Rdb$LU_STATUS = RDB$_DISTABORT ) OR ( Rdb$LU_STATUS = RDB$_REQ_SYNC ) OR ( Rdb$LU_STATUS = RDB$_READ_ONLY_TRANS ) ) CALL "ACMS$RAISE_TRANS_EXCEPTION" USING BY REFERENCE Rdb$LU_STATUS WHEN OTHER CALL "LIB$CALLG" USING BY REFERENCE Rdb$MESSAGE_VECTOR, BY VALUE LIB$SIGNAL CALL "ACMS$RAISE_NONREC_EXCEPTION" USING BY REFERENCE Rdb$LU_STATUS END-EVALUATE. EXIT-PROGRAM. EXIT PROGRAM.

4.2 Using Precompiled RDO or RDML with Rdb

This section describes how to write step procedures that access an Rdb database using precompiled RDO or RDML.

A step procedure that accesses an Rdb database on behalf of an ACMS task is similar to any Rdb program that accesses a database. This section explains how to start an Rdb database transaction and how to access data in an Rdb database using RDO.

4.2.1 Using RDO Statements in Step Procedures

You use the &RDB& flag to distinguish Rdb RDO statements from host language statements. See the Rdb documentation for more information on using RDO and RDML statements in a host language program. You name the Rdb database that your step procedure accesses using the INVOKE DATABASE statement. You must use the INVOKE DATABASE statement before you can use other RDO statements to access data in the database. For example:

&RDB& INVOKE DATABASE FILENAME "avertz_database:vehicle_rentals"

The database you name must be the same in all the step procedures, and in the initialization and termination procedures and cancel procedures in the server.

4.2.2 Starting and Ending RDO Database Transactions

You start an RDO database transaction by using a START_TRANSACTION statement. However, the way in which you start the database transaction depends on whether the database transaction is part of a distributed transaction.

This section describes how to start a database transaction that is part of a distributed transaction and how to start and end an independent database transaction. When you start an Rdb database transaction, you can also specify transaction, lock, and wait modes.

For an RDO database transaction to participate in a distributed transaction, you must specify the Transaction ID (TID) on the START_TRANSACTION statement.

The following code segment in COBOL illustrates how to start an RDO database transaction that is part of a distributed transaction. The procedure allocates a structure to hold the TID in the Working-Storage Section. In the Procedure Division, the procedure calls ACMS$GET_TID to retrieve the TID. If an error occurs, the procedure raises a nonrecoverable exception and exits. If there is no error, the procedure starts the database transaction, specifying the TID using the DISTRIBUTED_TID phrase. A common error handler, described in Section 4.2.5, is used to check the status from the START_TRANSACTION statement.

. . . WORKING-STORAGE SECTION. &RDB& INVOKE DATABASE FILENAME "avertz_database:vehicle_rentals" 01 dist_tid. 03 tid_data PIC X(16). 01 sts PIC S9(5) COMP. . . . PROCEDURE DIVISION. MAIN SECTION. . . . CALL "ACMS$GET_TID" USING BY REFERENCE dist_tid GIVING sts. IF sts IS FAILURE THEN CALL "ACMS$RAISE_NONREC_EXCEPTION" USING BY REFERENCE sts GO TO 999_end END-IF. &RDB& START_TRANSACTION DISTRIBUTED_TRANSACTION &RDB& DISTRIBUTED_TID dist_tid READ_WRITE &RDB& RESERVING reservations FOR SHARED WRITE, &RDB& rental_classes,sites,regions FOR SHARED READ &RDB& ON ERROR GO TO 900-rdb-error &RDB& END_ERROR. . . .

The following code segment in BASIC illustrates how to start an RDO database transaction that is part of a distributed transaction. The procedure first defines and allocates a structure to hold the TID. The procedure next calls ACMS$GET_TID to retrieve the TID. If an error occurs, the procedure raises a nonrecoverable exception and exits. If there is no error, the procedure starts the database transaction, specifying the TID using the DISTRIBUTED_TID phrase. A common error handler is used to check the status from the START_TRANSACTION statement.

. . . &RDB& INVOKE DATABASE FILENAME "avertz_database:vehicle_rentals" RECORD dist_tid_structure STRING tid_data = 16 END RECORD DECLARE dist_tid_structure dist_tid sts = ACMS$GET_TID( dist_tid ) IF ( sts AND 1% ) = 0% THEN CALL ACMS$RAISE_NONREC_EXCEPTION( sts ) EXIT FUNCTION sts END IF &RDB& START_TRANSACTION DISTRIBUTED_TRANSACTION &RDB& DISTRIBUTED_TID dist_tid READ_WRITE &RDB& RESERVING reservations FOR SHARED WRITE, &RDB& rental_classes,sites,regions FOR SHARED READ &RDB& ON ERROR GOSUB check_rdb_error EXIT FUNCTION Rdb$LU_STATUS &RDB& END_ERROR . . .

Because the RDO database transaction is participating in a distributed transaction, Rdb automatically commits or rolls back the database transaction when the distributed transaction ends. Therefore, you must not use the COMMIT or ROLLBACK verbs to end the database transaction in the step procedure.

4.2.2.2 Starting and Ending an Independent RDO Database Transaction

You start an independent database transaction by using the START_TRANSACTION statement. For example:

&RDB& START_TRANSACTION &RDB& RESERVING reservations FOR SHARED WRITE, &RDB& rental_classes,sites,regions FOR SHARED READ &RDB& ON ERROR . . . &RDB& END_ERROR

Because the RDO database transaction is not participating in a distributed transaction, you must commit or roll back the database transaction in the procedure. For example:

IF sts IS SUCCESS THEN &RDB& COMMIT ELSE &RDB& ROLLBACK END-IF.

4.2.3 Reading from a Database

The examples in this section illustrate how to read a record from a database and retrieve the data from the record.

Each example shows how to read a record from the SITES relation in the AVERTZ database. The procedure first initializes the return status to a failure status; it next uses a FOR statement to locate the target record and a GET statement to retrieve the data from the record. If a record is successfully located, a success value is stored in the procedure's return status. If the record is not found, the return status remains set to the failure status, indicating that the record does not exist. For example, in COBOL:

. . . MOVE vr_sirecnotfnd TO return_status. &RDB& FOR s IN sites WITH s.site_id = site_id &RDB& ON ERROR GO TO 900-rdb-error &RDB& END_ERROR &RDB& GET &RDB& ON ERROR GO TO 900-rdb-error &RDB& END_ERROR &RDB& vr_sites_wksp = s.* &RDB& END_GET SET return_status TO SUCCESS &RDB& END_FOR. . . .

For example, in BASIC:

. . . vr_find_site_proc = vr_sirecnotfnd &RDB& FOR s IN sites WITH s.site_id = sites::site_id &RDB& ON ERROR GOSUB check_rdb_error EXIT FUNCTION Rdb$LU_STATUS &RDB& END_ERROR &RDB& GET &RDB& ON ERROR GOSUB check_rdb_error EXIT FUNCTION Rdb$LU_STATUS &RDB& END_ERROR &RDB& sites = s.* &RDB& END_GET vr_find_site_proc = 1% &RDB& END_FOR . . .

The error handlers used in these examples are shown in Section 4.2.5.

4.2.4 Writing to a Database

The examples in this section illustrate how to modify an existing record in a database using the MODIFY statement and how to store a new record in a database using the STORE statement.

When new customers are added to the AVERTZ database, each one must be allocated a customer ID number and a new record stored in the CUSTOMERS relation. A relation called CU_ID_INC_CONTROL contains a single control record that holds the next available customer ID number. Each example in this section:

• Reads the customer ID control record
• Saves the next available customer ID number
• Increments the ID number and updates the control record
• Stores the new record in the CUSTOMERS relation

The following code segment shows how a new customer is added to the database using COBOL:

. . . * * Obtain new customer ID number * &RDB& FOR i IN cu_id_inc_control &RDB& ON ERROR GO TO 900-rdb-error &RDB& END_ERROR * * Retrieve next available ID number from control record * &RDB& GET &RDB& ON ERROR GO TO 900-rdb-error &RDB& END_ERROR &RDB& customer_id = i.max_customer_id &RDB& END_GET * * Increment next available ID number and update control record * &RDB& MODIFY i USING &RDB& ON ERROR GO TO 900-rdb-error &RDB& END_ERROR &RDB& i.max_customer_id = i.max_customer_id + 1 &RDB& END_MODIFY &RDB& END_FOR * * Store new customer record * &RDB& STORE c IN customers USING &RDB& ON ERROR GO TO 900-rdb-error &RDB& END_ERROR &RDB& c.last_name = cu_last_name; &RDB& c.first_name = cu_first_name; . . . &RDB& c.driver_license_region_id = &RDB& cu_driver_license_region_id; &RDB& c.driver_license_country_id = &RDB& cu_driver_license_country_id &RDB& END_STORE . . .

The following code segment illustrates how a new customer is added to the database using BASIC:

. . . ! ! Obtain new customer ID number ! &RDB& FOR i IN cu_id_inc_control &RDB& ON ERROR GOSUB check_rdb_error EXIT FUNCTION Rdb$LU_STATUS &RDB& END_ERROR ! ! Retrieve next available ID number from control record ! &RDB& GET &RDB& ON ERROR GOSUB check_rdb_error EXIT FUNCTION Rdb$LU_STATUS &RDB& END_ERROR &RDB& cust::customer_id = i.max_customer_id &RDB& END_GET ! ! Increment next available ID number and update record ! &RDB& MODIFY i USING &RDB& ON ERROR GOSUB check_rdb_error EXIT FUNCTION Rdb$LU_STATUS &RDB& END_ERROR &RDB& i.max_customer_id = i.max_customer_id + 1% &RDB& END_MODIFY &RDB& END_FOR ! ! Store new customer record ! &RDB& STORE c IN customers USING &RDB& ON ERROR GOSUB check_rdb_error EXIT FUNCTION Rdb$LU_STATUS &RDB& END_ERROR &RDB& c.last_name = cust::cu_last_name; &RDB& c.first_name = cust::cu_first_name; . . . &RDB& c.driver_license_region_id = &rdb& cust::cu_driver_license_region_id; &RDB& c.driver_license_country_id = &rdb& cust::cu_driver_license_country_id &RDB& END_STORE