Notes for RDMS SQL Statements

RDMS External Databases

To connect to an external database, an AT clause can be used in the EXEC RDMS statement.
The external database should be set in the Control Center under System Configuration > Architecture > Databases.
The database name or alias can then be used in the code using the following syntax:

EXEC RDMS AT <Database Name/Alias>

...
END-EXEC.

Parameter	Description
<Database Name/Alias>	The Database name or Alias as configured in the Control Center.

Example:

EXEC RDMS AT DEMO2DATABASE
   SELECT CUSTNO, FIRSTNAME, LASTNAME
   INTO :WS-CUSTNO, :WS-FIRSTNAME, :WS-LASTNAME
   FROM CUSTOMER_DEMO
   ORDER BY CUSTNO
END-EXEC.

RDMS Cursors in Queries

RDMS queries as shown in the following example can be very slow due to the use of the Cursor.

EXEC RDMS
    DECLARE CUSTOMER_CURSOR CURSOR FOR RETENTION
        SELECT * FROM CUSTOMER
        WHERE CUSTNO < :WS-SEARCHCUSTNO
END-EXEC.

EXEC RDMS
    OPEN CUSTOMER_CURSOR
END-EXEC.

EXEC RDMS
    FETCH CUSTOMER_CURSOR INTO
     :WSCUSTNO OF WSCUSTOMER01,
     :WSFIRSTNAME OF WSCUSTOMER01,
     :WSLASTNAME OF WSCUSTOMER01
END-EXEC.

EXEC RDMS
    CLOSE CUSTOMER_CURSOR
END-EXEC.

A special AMT version of the DECLARE statement has been created to make the use of the RDMS Cursor faster.

The use of the statement 'DECLARE CUSTOMER_CURSOR CURSOR FOR AMT' will speed up the query considerably.

The first part of the query example will then become:

EXEC RDMS
    DECLARE CUSTOMER_CURSOR CURSOR FOR AMT
        SELECT * FROM CUSTOMER
        WHERE CUSTNO < :WS-SEARCHCUSTNO
END-EXEC.

EXEC RDMS
    OPEN CUSTOMER_CURSOR
END-EXEC.

These two RDMS statements are internally translated into two SQL statements:

SELECT * FROM CUSTOMER WHERE CUSTNO < @WS-SN
SET @WS-SN = :WS-SEARCHCUSTNO

Handling of the FETCH statements

EXEC RDMS
    FETCH CUSTOMER_CURSOR INTO
     :WSCUSTNO OF WSCUSTOMER01,
     :WSFIRSTNAME OF WSCUSTOMER01,
     :WSLASTNAME OF WSCUSTOMER01
END-EXEC.

The first use of the RDMS FETCH ... statement opens the parameterized query and returns the first record of the result set.

Every next use of the RDMS FETCH ... statement is translated into a normal SQL FETCH ... statement and returns the next record of the result set.

The RDMS CLOSE command then closes the query.

EXEC RDMS
CLOSE CUSTOMER_CURSOR
END-EXEC.

Remarks

Restrictions:

Commit or Rollback is not possible while the query is active. Committing after the “CLOSE CUSTOMER_CURSOR” is allowed. The query can be opened after the commit.

Only FETCH NEXT or FETCH is allowed. FETCH FIRST, PRIOR, CURRENT AND LAST can’t be used in combination with the AMT cursor.

Error status codes RDMS Databases

The following RDMS error statuses are present in AMT-COBOL:

Error Code	Description
-6003	An attempt was made to insert or update a primary key that is a duplicate of an existing primary key.
-6010	A deadlock occurred.
-6012	An attempt was made to insert or update a unique column that is a duplicate of an existing unique column.