1 START_STREAM There are two start stream statements; a Declared Start Stream statement and an Undeclared Start Stream statement. 2 Declared Opens a stream that has been declared previously with the DECLARE_STREAM statement. A declared START_STREAM statement allows you to place the elements of the START_STREAM statement in any order within the program as long as they appear after the DECLARE_ STREAM statement and are executed in the order: START_ STREAM, FETCH, END_STREAM. Example RDO> START_STREAM EMP_STREAM 3 Format (B)0START_STREAM qq> declared-stream-name qwqqqqqqq>qqqqqwqq> mq> on-error qj 4 declared-stream-name The name of the stream that you create. This name must be the same name you use in the associated DECLARE_STREAM statement. 4 on-error The ON ERROR clause. This clause specifies the action to be taken if an Oracle Rdb error occurs during the START_STREAM operation. 3 More You must have the Oracle Rdb READ access to the specified relations to use this statement. Because the declared START_STREAM statement and the undeclared START_STREAM statement both begin with the keyword START_ STREAM followed by a stream name, make sure you do not split the undeclared START_STREAM stream statement over two lines in such a way that Oracle Rdb will interpret it as a declared START_STREAM statement. For example, ending the first line of the following undeclared START_STREAM statement with the keyword USING indicates to Oracle Rdb that the statement is not complete: RDO> START_STREAM EMP_STREAM USING cont> E IN EMPLOYEES SORTED BY E.LAST_NAME You could also use a continuation character to indicate to RDO that the undeclared START_STREAM statement is not complete. Without the continuation character, the following declared START_ STREAM statement could be interpreted as a declared START_STREAM statement: RDO> START_STREAM EMP_STREAM - cont> USING E IN EMPLOYEES SORTED BY E.LAST_NAME Because an association is made between the DECLARE_STREAM statement and the START_STREAM statement by the declared stream name clause in both statements, it is not permissible to specify the RSE in the declared START_STREAM statement. Instead, include the RSE in the DECLARE_STREAM statement. You can issue several declared START_STREAM statements in a module. As long as you use the same declared stream name, they will all refer to the same stream. RDO does not allow a record stream from which data values cannot be fetched by DBKEY (views that retrieve values from streams defined using the SQL GROUP BY or UNION clauses) to be declared or started. Such attempts produce the following exception: VWNOFETCH view 'view-name' cannot be fetched within a stream 3 Example The following program fragment shows how to use the DECLARE_ STREAM statement with a declared START_STREAM statement. Note that although the START_STREAM, FETCH, and END_STREAM statements must come after the DECLARE_STREAM statement, they can be placed in any order within the program, as long as they are executed in the following order: START_STREAM, FETCH, END_STREAM. DATA DIVISION. WORKING-STORAGE SECTION. . . . &RDB& INVOKE DATABASE FILENAME 'PERSONNEL'. &RDB& DECLARE_STREAM EMPL_STREAM USING E IN EMPLOYEES - SORTED BY E.LAST_NAME 01 LAST_NAME PIC X(14). 01 FIRST_NAME PIC X(10). 01 EMPLOYEE_ID PIC X(5). PROCEDURE DIVISION. INIT SECTION. INIT-PARAGRAPH. &RDB& START_TRANSACTION READ_WRITE. PERFORM START-STREAM. PERFORM FETCH-STREAM. PERFORM GET-STREAM. DISPLAY LAST_NAME. DISPLAY FIRST_NAME. DISPLAY EMPLOYEE_ID. PERFORM FETCH-STREAM. PERFORM GET-STREAM. DISPLAY LAST_NAME. DISPLAY FIRST_NAME. DISPLAY EMPLOYEE_ID. PERFORM END_STREAM. &RDB& FINISH. GOTO END-PROGRAM. END-STREAM. &RDB& END_STREAM EMPL_STREAM. GET-STREAM. &RDB& GET - LAST_NAME = E.LAST_NAME; - FIRST_NAME = E.FIRST_NAME; - EMPLOYEE_ID = E.EMPLOYEE_ID; - END_GET. FETCH-STREAM. &RDB& FETCH EMPL_STREAM. START-STREAM. &RDB& START_STREAM EMPL_STREAM. END-PROGRAM. STOP RUN. 2 Undeclared Declares and opens a record stream. The undeclared START_STREAM statement: o Forms a record stream from one or more relations. The record selection expression determines the records in the record stream. o Places a pointer for that stream just before the first record in this stream. You must then use the FETCH statement to advance the pointer one record at a time through the stream. You can use other Oracle Rdb statements (GET, MODIFY, and ERASE) to manipulate each record. 3 Format (B)0START_STREAM qqqqqwqqqqqqqqqqqqqqq>qqqqqqqqqqqqqwqqqqqk mqqqqqq> handle-options qqqqqqj x lqqqqqqqqqqqqqqqqqqqqqqqqq stream-name q> USING qqq> rse qqqwqqqqqqqq>qqqqqqwq> mqq> on-error qqj 4 handle-options (B)0handle-options = qq> ( qwq> REQUEST_HANDLE qqq> var qqqqqqqqqqqqqqqqqqqqqqqqqqqwq> ) qq> tq> TRANSACTION_HANDLE qqq> var qqqqqqqqqqqqqqqqqqqqqqqu mq> REQUEST_HANDLE q> var , TRANSACTION_HANDLE q> var qj 5 REQUEST_HANDLE A keyword followed by a host language variable. A request handle points to the location of a compiled Oracle Rdb request. If you do not supply a request handle explicitly, Oracle Rdb associates a default request handle with the compiled request. Your must use a request handle when you want to make an identical query to two different databases. In Callable RDO, use !VAL as a marker for host language variables. You can put parentheses around the host language variable name. 5 TRANSACTION_HANDLE A keyword followed by a host language variable. A transaction handle identifies each instance of a transaction. If you do not declare the transaction handle explicitly, Oracle Rdb attaches an internal identifier to the transaction. In Callable RDO, use !VAL as a marker for host language variables. You can put parentheses around the host language variable name. Normally, you do not need to use this argument. The ability to declare a transaction handle is provided for compatibility with other database products and future releases of Oracle Rdb. 4 stream-name The name of the stream that you create. You refer to the stream name only when you want to move the stream pointer (FETCH) or terminate the stream (END_STREAM). Use a context variable for all other purposes. 4 on-error The ON ERROR clause. This clause specifies the action to be taken if an Oracle Rdb error occurs while Oracle Rdb is compiling the RSE in the START_STREAM statement. 4 rse A record selection expression. This RSE specifies the records included in the record stream. Any context variables that you define with the START_STREAM statement are valid for the life of that stream only. Once you have defined a context variable in the record selection expression, you cannot redefine that context variable elsewhere until you have ended the stream. 3 More You must have the Oracle Rdb READ privilege to the specified relations to use this statement. Because the declared START_STREAM statement and the undeclared START_STREAM statement both begin with the keyword START_ STREAM followed by a stream name, make sure you do not split the undeclared START_STREAM stream statement over two lines in such a way that Oracle Rdb will interpret it as a declared START_STREAM statement. For example, ending the first line of the following undeclared START_STREAM statement with the keyword USING indicates to Oracle Rdb that the statement is not complete: RDO> START_STREAM EMP_STREAM USING cont> E IN EMPLOYEES SORTED BY E.LAST_NAME You could also use a continuation character to indicate to RDO that the undeclared START_STREAM statement is not complete. Without the continuation character, the following declared START_ STREAM statement could be interpreted as a declared START_STREAM statement: RDO> START_STREAM EMP_STREAM - cont> USING E IN EMPLOYEES SORTED BY E.LAST_NAME Use the START_STREAM statement instead of the FOR statement to establish a record stream in Callable RDO. You can also use START_STREAM in a preprocessed program to control the processing of each record in a stream. You can process streams only in the forward direction. If you want to move the stream pointer back to a record that you already processed, you must close the stream and reopen it. The order of the stream is not predictable unless the record selection expression contains a SORTED BY clause. Oracle Rdb examines the contents of any input host language variables when you use the START_STREAM statement. It cannot evaluate the host language variables again until you close and reopen the stream. Therefore, you cannot change the value of a host language variable in the middle of a START_STREAM operation. Once you have named the stream, you should only refer to the stream-name when you want to: o Move the stream pointer with a FETCH statement o Terminate the stream with the END_STREAM clause For all other purposes, you should use a context variable. Any context variables that you define with the START_STREAM statement are valid for the life of that stream only. Once you have defined a context variable in the record selection expression, you cannot redefine that context variable elsewhere inside the START_STREAM...END_ STREAM block. The statements following a START_STREAM statement must include at least one FETCH statement before you access any record in the stream. RDO does not allow a record stream from which data values cannot be fetched by DBKEY (views that retrieve values from streams defined using the SQL GROUP BY or UNION clauses) to be declared or started. Such attempts produce the following exception: VWNOFETCH view 'view-name' cannot be fetched within a stream 3 Example The following example creates a record stream in a BASIC program using Callable RDO: RDMS_STATUS = RDB$INTERPRET ('INVOKE DATABASE PATHNAME ' + & '"PERSONNEL"') RDMS_STATUS = RDB$INTERPRET ('START_STREAM EMP USING ' + & 'E IN EMPLOYEES') RDMS_STATUS = RDB$INTERPRET ('FETCH EMP') DML_STRING = 'GET ' + & '!VAL = E.EMPLOYEE_ID;' + & '!VAL = E.LAST_NAME;' + & '!VAL = E.FIRST_NAME' + & 'END_GET' RDMS_STATUS = RDB$INTERPRET (DML_STRING, EMP_ID, & LST_NAM, FRST_NAM) This BASIC program fragment shows how to display three field values from the EMPLOYEES relation in a Callable RDO program: o The first three calls to RDB$INTERPRET invoke the database, start a stream called EMP, and move the pointer to the first record in the stream. o The assignment statement builds a command string to perform the GET operation. o The final call sends the command string to Oracle Rdb, which assigns the database field values from the first record in the stream to the program's host variables.