There are two start stream statements; a Declared Start Stream
statement and an Undeclared Start Stream statement.
1 – 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
1.1 – Format
(B)0[m[4mSTART_STREAM[m qq> declared-stream-name qwqqqqqqq>qqqqqwqq>
mq> on-error qj
1.1.1 – 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.
1.1.2 – 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.
1.2 – 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
1.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.
2.1 – Format
(B)0[m[4mSTART_STREAM[m qqqqqwqqqqqqqqqqqqqqq>qqqqqqqqqqqqqwqqqqqk
mqqqqqq> handle-options qqqqqqj x
lqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mq> stream-name q> [4mUSING[m qqq> rse qqqwqqqqqqqq>qqqqqqwq>
mqq> on-error qqj
2.1.1 – handle-options
(B)0[mhandle-options =
qq> ( qwq> [4mREQUEST_HANDLE[m qqq> var qqqqqqqqqqqqqqqqqqqqqqqqqqqwq> ) qq>
tq> [4mTRANSACTION_HANDLE[m qqq> var qqqqqqqqqqqqqqqqqqqqqqqu
mq> [4mREQUEST_HANDLE[m q> var , [4mTRANSACTION_HANDLE[m q> var qj
2.1.1.1 – 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.
2.1.1.2 – 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.
2.1.2 – 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.
2.1.3 – 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.
2.1.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.
2.2 – 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
2.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.