You typically write an error handler to process errors returned by Rdb when starting and ending a database transaction and when accessing data in the database. Handling error conditions using RDO with Rdb is very similar to using SQL with Rdb. This section describes the differences between RDO and SQL when using distributed transactions. See Section 4.1.6 for information on handling errors using SQL.

A distributed transaction can abort at any time. If a transaction aborts while a step procedure is executing, Rdb automatically rolls back an active database transaction. The next time the step procedure executes an RDO statement, Rdb starts a new, default database transaction with read-only access to the database. The RDO statement completes successfully only if it retrieves information from the database. However, Rdb returns an error if the RDO statement writes to the database. Therefore, the error handler in a procedure using RDO must check for additional Rdb error codes.

The following example shows how to write an error handler in BASIC for use in a procedure using RDO:

EXTERNAL LONG CONSTANT RDB$_LOCK_CONFLICT . . . EXTERNAL LONG FUNCTION LIB$SIGNAL . . . check_rdb_error: SELECT Rdb$LU_STATUS CASE RDB$_DEADLOCK, & RDMS$_DEADLOCK, & RDB$_LOCK_CONFLICT, & RDMS$_LCKCNFLCT, & RDMS$_TIMEOUT CALL ACMS$RAISE_TRANS_EXCEPTION( ACMS$_TRANSTIMEDOUT ) CASE RDB$_SYS_REQUEST_CALL, & RDB$_BAD_TRANS_HANDLE, & RDB$_DISTABORT, & RDB$_READ_ONLY_TRANS, & RDB$_REQ_SYNC CALL ACMS$RAISE_TRANS_EXCEPTION( Rdb$LU_STATUS ) CASE ELSE CALL LIB$CALLG( Rdb$MESSAGE_VECTOR, & LOC( LIB$SIGNAL ) BY VALUE ) CALL ACMS$RAISE_NONREC_EXCEPTION( Rdb$LU_STATUS ) END SELECT RETURN . . .

4.2.6 Compiling Rdb Procedures that Use RDO

You use the RDO precompiler, RDBPRE, when you compile a procedure containing embedded RDO statements. The RDO precompiler processes the embedded RDO 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 RDO 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 RDO precompiler qualifiers, see the Rdb documentation.

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

$ RCOB == "$RDBPRE/COBOL"

The following command line precompiles a procedure called VR_FIND_SITE_RDO_PROC:

$ RCOB/LIST VR_FIND_SITE_RDO_PROC

Chapter 6 explains how to link procedures that use RDO.

4.3 Using DBMS

A procedure for an ACMS task that accesses a DBMS database is similar to any DBMS program that accesses a database. For example, the procedure uses the same DBMS DML (Data Manipulation Language) statements, such as MOVE and STORE. It handles errors by testing against DBMS error conditions.

4.3.1 Using DBMS DML Statements in Step Procedures

COBOL supports DBMS DML statements as part of the COBOL language. However, if you are using DBMS with another language, such as BASIC, you use the DBMS DML precompiler to process the DML statements in your program in much the same way as you do when you use SQL or RDO with Rdb.

The DBMS DML precompiler uses a prefix character to distinguish DML statements from host language statements. The default prefix character is the pound-sign (#) character. For information on DML statements, refer to the DBMS documentation.

In COBOL, you name the database that your step procedure accesses in the SUBSCHEMA section in the Data Division. The subschema name is required, even if you are using the default subschema. The schema name is also required. For example:

DB DEFAULT_SUBSCHEMA WITHIN "PERS_CDD.PERS_DBMS_SCHEMA" FOR "PERS_DB:PERS_DBMS".

In other languages, you name the database that your step procedure accesses using the INVOKE statement. In this BASIC example, the procedure uses record-type structures in the user work area:

# INVOKE DEFAULT_SUBSCHEMA - WITHIN PERS_CDD.PERS_DBMS_SCHEMA - FOR PERS_DB:PERSONNEL - ( RECORDS )

The database and subschema you name must be the same in all the procedures linked into the server.

4.3.2 Starting and Ending a DBMS Database Transaction

You start a DBMS database transaction by using a READY statement. However, the way in which you start the database transaction depends on whether the database transaction is part of a distributed transaction. In the READY statement, you can specify the realms the step procedure accesses in the database. If you do not specify one or more realms, DBMS readies all the realms in the subschema.

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. In addition, it discusses various access modes that you can specify when starting a database transaction.

4.3.2.1 Starting a DBMS Database Transaction that Is Part of a Distributed Transaction

For a DBMS database transaction to participate in a distributed transaction, you must specify the transaction ID (TID) on the READY statement.

The following code segment illustrates how to start a DBMS database transaction that is part of a distributed transaction in COBOL. In the Working-Storage Section, the procedure allocates a structure to hold the TID. 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 by calling DBQ$INTERPRET. It specifies the FOR TRANSACTION phrase in the READY statement and passes the TID as an argument to DBQ$INTERPRET.

CALL "DBQ$INTERPRET" USING BY DESCRIPTOR "READY CONCURRENT UPDATE FOR TRANSACTION", BY REFERENCE dist_tid GIVING return_status. IF return_status IS FAILURE THEN CALL "DBM$SIGNAL" END-IF

See the COBOL and DBMS documentation for more information on starting a database transaction in COBOL.

The following code segment illustrates how to start a DBMS database transaction that is part of a distributed transaction in BASIC. The procedure first defines and allocates a structure to hold the TID. It 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 FOR TRANSACTION phrase on the READY statement.

. . . RECORD dist_tid_structure STRING tid_data = 16 END RECORD DECLARE dist_tid_structure dist_tid . . . sts = ACMS$GET_TID( dist_tid BY REF ) IF ( sts AND 1% ) = 0% & THEN CALL ACMS$RAISE_NONREC_EXCEPTION( sts ) END IF # READY CONCURRENT UPDATE FOR TRANSACTION dist_tid . . .

Because the DBMS database transaction is participating in a distributed transaction, DBMS 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.

You start an independent database transaction by using the READY statement. For example, in COBOL:

READY CONCURRENT RETRIEVAL.

The following example starts an independent, read-only database transaction using BASIC:

# READY CONCURRENT RETRIEVAL

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

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

For example, in BASIC:

. . . IF ( sts AND 1% ) = 1% THEN # COMMIT ELSE # ROLLBACK END IF . . .

4.3.2.3 Using DBMS Access and Allow Mode Specifications

You can specify the access mode and allow mode when you start a DBMS database transaction. The access mode specifies how the step procedure accesses the database. If the step procedure only reads records from the database, specify RETRIEVAL when you start the database transaction. Specify UPDATE in step procedures that read, write, and modify records in the database. If you do not specify an access mode, the default is RETRIEVAL access, which means that you can only read records from the database. Using RETRIEVAL mode in a procedure that does not update records reduces database contention.

The allow mode specifies how you will allow other processes to access the database. The default is PROTECTED, which means that other processes can read records from the database, but they cannot modify existing records or store new records in the database. Specify PROTECTED if a single process only needs write-access to the database. If multiple processes need write-access to the database, specify CONCURRENT mode. (DBMS also supports BATCH and EXCLUSIVE allow modes.)

Refer to the DBMS documentation for more information on database access and allow modes.

4.3.2.4 Using a DBMS Wait Mode Specification

When you create or modify a database, you can specify how DBMS handles the situation if it encounters a locked record while accessing the database. If you use the /WAIT_RECORD_LOCKS qualifier on the DBO/CREATE or DBO/MODIFY command, DBMS waits until the lock can be granted before continuing. If you use the /NOWAIT_RECORD_LOCKS qualifier, DBMS immediately returns an error if it encounters a lock. You can override the wait-mode specification at run time by using the DBM$BIND_WAIT_RECORD_LOCKS logical name.

If you choose to wait for locks, you can specify the maximum time you are prepared to wait until a lock is granted. If the lock is not granted in the specified time limit, DBMS returns the DBM$_LCKCNFLCT or DBM$_TIMEOUT errors. Specify the time limit by defining the DBM$BIND_LOCK_TIMEOUT_INTERVAL logical name in a logical name table that is accessible to the server. Define the DBM$BIND_LOCK_TIMEOUT_INTERVAL logical name in one of the following ways:

• As a server logical name in the application definition
• In an application-specific logical name table
• In the system logical name table
• In a group logical name table

For example, the following server logical name definition specifies that DBMS should wait no more than 10 seconds for a lock to be granted:

LOGICAL NAME IS DBM$BIND_LOCK_TIMEOUT_INTERVAL = "10";

See the DBMS documentation for more information on specifying the wait mode and lock timeout interval.

The examples in this section illustrate how to read a record from a DBMS database and return the data to the task in a workspace.

Example 4-8 illustrates a simple step procedure written in COBOL that reads a record from a personnel database. The procedure first moves the record key from the task workspace into the user work area (UWA). It next starts a database transaction and reads the record from the database. If the employee's record does not exist or is locked, the procedure returns a failure status. If any other error occurs, the procedure calls DBM$SIGNAL to signal the error and then calls ACMS$RAISE_NONREC_EXCEPTION to ensure that the task is canceled. If the record is read successfully, the procedure moves the data from the UWA into the task workspaces and returns a success status. Finally, the procedure ends the transaction.

Example 4-8 Step Procedure in COBOL that Reads a DBMS Record

IDENTIFICATION DIVISION. PROGRAM-ID. pers_find_employee_proc. ENVIRONMENT DIVISION. DATA DIVISION. SUB-SCHEMA SECTION. DB DEFAULT_SUBSCHEMA WITHIN "PERS_CDD.PERS_DBMS_SCHEMA" FOR "PERS_DB:PERS_DBMS". WORKING-STORAGE SECTION. 01 return_status PIC S9(5) COMP. 01 error_cond PIC S9(5) COMP. COPY "pers_files:pers_common_defns". LINKAGE SECTION. COPY "pers_cdd.employee_record" FROM DICTIONARY REPLACING ==employee_record== BY ==emp_wksp_record==. PROCEDURE DIVISION USING emp_wksp_record GIVING return_status. MAIN SECTION. 000-start. MOVE emp_badge_number OF emp_wksp_record TO emp_badge_number OF employee_record. READY CONCURRENT RETRIEVAL. FETCH FIRST WITHIN ALL_EMPLOYEES USING emp_badge_number OF employee_record ON ERROR CALL "LIB$MATCH_COND" USING BY REFERENCE DB-CONDITION, BY REFERENCE DBM$_END, BY REFERENCE DBM$_DEADLOCK, BY REFERENCE DBM$_LCKCNFLCT, BY REFERENCE DBM$_TIMEOUT GIVING error_cond EVALUATE error_cond WHEN 1 MOVE persmsg_empnotfound TO return_status WHEN 2 THRU 4 MOVE persmsg_emplocked TO return_status WHEN OTHER CALL "DBM$SIGNAL" CALL "ACMS$RAISE_NONREC_EXCEPTION" USING BY REFERENCE DB-CONDITION END-EVALUATE NOT ON ERROR MOVE employee_record TO emp_wksp_record MOVE persmsg_success TO return_status END-FETCH. COMMIT. 999-end. EXIT PROGRAM.

Example 4-9 illustrates a simple step procedure written in BASIC that reads a record from a personnel database. The procedure extends the previous COBOL example by including logic to retry the transaction automatically if the employee's record is locked. The procedure first initializes a transaction retry-counter and moves the record key from the task workspace into the user work area (UWA). It then starts a database transaction, reads the record from the database, and ends the transaction. Finally, the procedure moves the employee's record from the UWA into the task workspaces and returns a success status.

The procedure uses an error handler to trap errors signaled by DBMS. Because DBMS always signals severe OpenVMS status codes, the procedure uses the HANDLE=SEVERE option to trap the errors in the handler. If the employee's record does not exist, the error handler returns a failure status. If the error handler detects a record-locked or lock-timeout error, it retries the transaction up to 5 times before return a failure status. If any other error occurs, the error handler uses the EXIT HANDLER statement to resignal the error condition. Finally, the error handler ends the current transaction, trapping and ignoring all errors.

Example 4-9 Step Procedure in BASIC that Reads a DBMS Record

FUNCTION LONG pers_find_employee_proc( employee_record emp_wksp ) OPTION HANDLE=SEVERE %INCLUDE "pers_files:pers_common_defns" %INCLUDE %FROM %CDD "pers_cdd.employee_record" DECLARE LONG retry_count # INVOKE DEFAULT_SUBSCHEMA - WITHIN PERS_CDD.PERS_DBMS_SCHEMA - FOR PERS_DB:PERS_DBMS - ( RECORDS ) WHEN ERROR IN retry_count = 0% employee_record::emp_badge_number = emp_wksp::emp_badge_number start_db_trans: # READY CONCURRENT RETRIEVAL # FETCH FIRST WITHIN ALL_EMPLOYEES USING emp_badge_number # COMMIT emp_wksp = employee_record pers_find_employee_proc = persmsg_success USE SELECT LIB$MATCH_COND( DBM_COND, DBM$_END, & DBM$_DEADLOCK, & DBM$_LCKCNFLCT, & DBM$_TIMEOUT ) CASE 1 ! DBM$_END pers_find_employee_proc = persmsg_empnotfound CASE 2, 3, 4 ! DBM$_DEADLOCK, DBM$_LCKCNFLCT, DBM$_TIMEOUT IF retry_count < 5% & THEN retry_count = retry_count + 1% # ROLLBACK ( TRAP ERROR ) CONTINUE start_db_trans ELSE pers_find_employee_proc = persmsg_emplocked END IF CASE ELSE EXIT HANDLER END SELECT # ROLLBACK ( TRAP ERROR ) END WHEN END FUNCTION

4.3.4 Writing to a Database

This section illustrates how to store a new record in a DBMS database and how to update a record in a DBMS database.

Example 4-10 explains how to store a new record in a DBMS database. The PERS_ADD_EMPLOYEE_PROC procedure stores a new record in the employee set using the information that is entered by the user and passed to the procedure in a task workspace.

The procedure first calls ACMS$GET_TID to obtain the current transaction ID (TID). The procedure next calls DBQ$INTERPRET to start the database transaction. If successful, it copies the new employee record data from the task workspace to the user work area (UWA) and stores the current time in the employee record; the time-stamp field is used for consistency-checking by the update procedure. The procedure then stores the record in the database. If all is successful, the procedure returns a success status to the task. The error handler for this procedure is described in Section 4.3.5.

Example 4-10 Step Procedure in COBOL that Updates a DBMS Record

IDENTIFICATION DIVISION. PROGRAM-ID. pers_add_employee_proc. ENVIRONMENT DIVISION. DATA DIVISION. SUB-SCHEMA SECTION. DB DEFAULT_SUBSCHEMA WITHIN "PERS_CDD.PERS_DBMS_SCHEMA" FOR "PERS_DB:PERS_DBMS". WORKING-STORAGE SECTION. 01 return_status PIC S9(5) COMP. 01 error_cond PIC S9(5) COMP. 01 dist_tid. 03 tid_data PIC X(16). COPY "pers_files:pers_common_defns". LINKAGE SECTION. COPY "pers_cdd.employee_record" FROM DICTIONARY REPLACING ==employee_record== BY ==emp_wksp_record==. PROCEDURE DIVISION USING emp_wksp_record GIVING return_status. DECLARATIVES. dml-failure SECTION. USE FOR DB-EXCEPTION. 010-dbm-failure. . . . EXIT PROGRAM. END DECLARATIVES. MAIN SECTION. 000-start. CALL "ACMS$GET_TID" USING BY REFERENCE dist_tid GIVING return_status. IF return_status IS FAILURE THEN CALL "ACMS$RAISE_NONREC_EXCEPTION" USING BY REFERENCE return_status EXIT PROGRAM END-IF. CALL "DBQ$INTERPRET" USING BY DESCRIPTOR "READY CONCURRENT UPDATE FOR TRANSACTION", BY REFERENCE dist_tid GIVING return_status. IF return_status IS FAILURE THEN CALL "DBM$SIGNAL" END-IF MOVE emp_wksp_record TO employee_record. CALL "SYS$GETTIM" USING BY REFERENCE emp_last_update OF employee_record GIVING return_status. IF return_status IS FAILURE THEN CALL "LIB$STOP" USING BY VALUE return_status END-IF. STORE employee_record. MOVE persmsg_success TO return_status. 999-end. EXIT PROGRAM.

Example 4-11 illustrates how to update a record in a DBMS database. The PERS_CHANGE_EMPLOYEE_PROC procedure updates a record in the employee set using the information that is entered by the user and passed to the procedure in a task workspace. To conserve resources, the task does not retain server context while the user is modifying the employee's information. Therefore, the procedure must ensure that the information in the record has not changed while the user was updating the information on the screen.

The procedure first rereads the original record in the file and then uses a time stamp stored in the record to ensure that the version read in this procedure is the same as the version read previously by the PERS_FIND_EMPLOYEE_PROC procedure. If the record has been updated, the procedure returns an error and unlocks the record. If the record has not been changed, the procedure copies the data from the task workspace record to the user workspace area (UWA), calls SYS$GETTIM to retrieve the current system time, and updates the current record. The error handling in this procedure is described in Section 4.3.5.

Example 4-11 Step Procedure in BASIC that Updates a DBMS Record

FUNCTION LONG pers_change_employee_proc( employee_record emp_wksp ) OPTION HANDLE=SEVERE %INCLUDE "pers_files:pers_common_defns" %INCLUDE %FROM %CDD "pers_cdd.employee_record" RECORD dist_tid_structure STRING tid_data = 16 END RECORD DECLARE dist_tid_structure dist_tid DECLARE LONG sts # INVOKE DEFAULT_SUBSCHEMA - WITHIN PERS_CDD.PERS_DBMS_SCHEMA - FOR PERS_DB:PERS_DBMS - ( RECORDS ) sts = ACMS$GET_TID( dist_tid BY REF ) IF ( sts AND 1% ) = 0% & THEN CALL ACMS$RAISE_NONREC_EXCEPTION( sts ) END IF WHEN ERROR IN employee_record::emp_badge_number = emp_wksp::emp_badge_number # READY CONCURRENT UPDATE FOR TRANSACTION dist_tid # FETCH FIRST WITHIN ALL_EMPLOYEES - USING emp_badge_number IF employee_record::emp_last_update = & emp_wksp::emp_last_update & THEN employee_record = emp_wksp sts = SYS$GETTIM( employee_record::emp_last_update BY REF ) IF ( sts AND 1% ) = 0% & THEN CALL LIB$STOP( sts ) END IF # MODIFY employee_record pers_change_employee_proc = persmsg_success ELSE pers_change_employee_proc = persmsg_empchanged END IF USE . . . END WHEN END FUNCTION