|
HP OpenVMS Systems Documentation |
|
Compaq ACMS for OpenVMS
|
| Previous | Contents | Index |
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 |
Do not make changes to the language source module created by the SQL precompiler and then use the language compiler directly to compile that source module. This rule applies even if you want to make source changes that do not affect SQL statements because the next precompilation of the original embedded SQL module overwrites the changes you make to the temporary language source module generated by the precompiler. |
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:
| 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.
|
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.
See Section 4.1.3.3 for information on transaction and lock mode specifications. See Section 4.1.3.4 for important information on transaction 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.
For a procedure that accesses an Rdb database to participate in a distributed transaction, the database transaction must start in the procedure, not in the task definition. |
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.
|
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:
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
|
| Previous | Next | Contents | Index |