The Relational Data Manipulation Language (RDML) is composed
of statements that can be embedded in C and Pascal programs.
These programs can be processed by the RDML preprocessor, which
converts the RDML statements into a series of equivalent DSRI
calls to the database. Following a successful precompilation,
the programmer can submit the resulting source code to the host
language compiler.
1 – More
The RDML C and Pascal preprocessors do not allow nested comments
and identify such comments with an information message. RDML does
not allow comments because they are not allowed by the ANSI/ISO C
standard or by VAC C (when you use the /STANDARD= PORTABLE qualifier).
2 – Distributed Transactions
You may use the two-phase commit protocol in RDML to distribute
transactions. To use the two-phase commit protocol with RDML,
you must either recompile any existing application programs that
were compiled with Oracle Rdb V3.1 or earlier, or you must write new
application programs. With RDML, the two-phase commit protocol is
not the default.
RDML provides the following ways for application programs to use
the two-phase commit protocol:
o By implicitly calling DECdtm system services calls in either
of two ways:
- Using the /DISTRIBUTED_TRANSACTION qualifier in the
precompiler command line
- Using the DISTRIBUTED_TRANSACTION keyword in the
START_TRANSACTION statement
o By explicitly calling the DECdtm system service calls
and using variables to pass the value of the distributed
transaction identifier (TID)
If your application starts a distributed transaction that
includes other read/write database management products that
support the two-phase commit protocol, your application
must explicitly invoke the DECdtm system service calls. For
example, if your application starts a distributed transaction
using Oracle Rdb and Oracle CODASYL DBMS, your application must
explicitly call SYS$START_TRANS and SYS$END_TRANS.
In addition, you must use the full DISTRIBUTED_TRANSACTION
clause in the START_TRANSACTION statement.
The implementation of RDML with distributed transactions is
very similar to using RDBPRE with distributed transactions. See
the "Oracle Rdb Guide to Distributed Transactions" for a complete
description of using RDBPRE with distributed transactions and the
"RDML Reference Manual" and the "Guide to Using RDO, RDBPRE, and
RDML" for more information.
3 – Preprocessor
The RDML preprocessor can operate on an OpenVMS system that has
Oracle Rdb installed.
To invoke the RDML preprocessor, you must first define a symbol.
For example:
$ RDML :== $RDML
$ RDML/PASCAL SALARY_PLAN.RPA
You must preprocess any programs that contain RDML statements
before processing them with the C or Pascal compiler.
3.1 – Language Selection
The RDML preprocessor will preprocess C and Pascal files that
contain RDML statements. There are two methods that allow you to
specify the language type of the source file:
1. Use a language qualifier when invoking the RDML preprocessor
2. If you do not specify a language qualifier, the RDML
preprocessor will attempt to determine what language the
source file contains by looking at the file extension supplied
in the command line. If the file extension is ".RC", RDML will
assume the file is a C file. If the file extension is ".RPA",
the RDML preprocessor will assume the file is a Pascal file.
An error message will be produced if the RDML preprocessor can
not determine what language is to be associated with a specified
input file.
3.2 /LISTING
Specifies a file for the error listing file. The default file
types are ".LC" for VAX C and DEC C, and ".LPA" for VAX Pascal
and DEC Pascal. If /LISTING is not specified, no listing file
will be created.
Format:
/LISTING[=file-spec]
/NOLISTING (Default)
Example:
$ RDML/LISTING EMPUPDATE.REP
3.3 /OUTPUT
Specifies a file for the host language output. The default file
types are ".C" for VAX C and DEC C and ".PAS" for VAX Pascal,
and DEC Pascal. The RDML preprocessor will create this file only
following a successful precompilation.
Format:
/OUTPUT[=file-spec] (Default)
/NOOUTPUT
Example:
$ RDML/OUTPUT=EMPUPDATE.PAS EMPUP.REP
3.4 /C
Specifies that the input file is a VAX C or DEC C source file.
If the /C qualifier is used on the command line, the RDML
preprocessor will assume a file extension of ".RC" unless you
specify otherwise in the file specification.
Format:
/C
Example:
$ RDML/C EMPUP
3.4.1 – More
The following restrictions affect the use of C host variables:
o Host variables in C of the form *host_variable that appear directly
after a host variable in an RSE are not detected correctly. For
instance, *year_ptr is interpreted incorrectly as part of the host
variable emp_id in the following example:
FOR D IN DEGREES WITH D.EMPLOYEE_ID = emp_id
*year_ptr = D.YEAR_GIVEN;
END_FOR;
The workaround for this restriction is to use braces around the host
language statements, or parentheses around the WITH clause. For
example, either of the following RSEs will preprocess correctly:
FOR D IN DEGREES WITH D.EMPLOYEE_ID = emp_id
{
*year_ptr = D.YEAR_GIVEN;
}
END_FOR;
FOR D IN DEGREES WITH (D.EMPLOYEE_ID = emp_id)
*year_ptr = D.YEAR_GIVEN;
END_FOR;
o Although host variables with parentheses are permitted in non-RDML
statements, they cannot be used as host variables in RDML statements.
For example, the following syntax is not permitted:
FOR E IN EMPLOYEES
WITH E.LAST_NAME = (name)[offset].element
.
.
.
END_FOR;
However, the following is permitted:
FOR E IN EMPLOYEES
WITH E.LAST_NAME = name[offset].element
.
.
.
END_FOR;
The C string continuation character (a backslash, \) in string
constants followed immediately by a new line is not recognized by
RDML. Do not use this method of continuation with this version of
RDML. RDML generates an error if it finds a string constant that does
not begin and end on the same line within quotation marks.
For example, the following C lines cause a syntax error:
printf ("abcdefg\
hijklmnopqrstuvwxyz");
3.5 /PASCAL
Specifies that the input file is a DEC Pascal or VAX Pascal
source file. If the /PASCAL qualifier is used on the command
line, the RDML preprocessor will assume a file extension of
".RPA" unless you specify otherwise in the file specification.
Format:
/PASCAL
Example:
$ RDML/PASCAL SALARY_PLAN
On Oracle Rdb for OpenVMS Alpha, Pascal users will need to compile
/ALIGN=VAX because RDML/PASCAL generates code that is byte aligned.
This is a permanent restriction.
3.5.1 – More
o RDML/Pascal does not generate the correct length for a character
string value expression in the form:
FOR E IN EMPLOYEES WITH E.LAST_NAME = ('T' | host_variable)
... some statements ...
END_FOR;
This statement generates an error from VAX Pascal, such as:
00470 0 0 RDB$PORT_FIELD_0 : VARYING[0] OF CHAR;
1
%PASCAL-E-MAXLENRNG, (1) Max-length must be in range 1..65535
%PASCAL-E-ENDDIAGS, Pascal completed with 1 diagnostic
To avoid this problem, construct the value needed before issuing the
query, using a method such as the following:
host_variable1 := 'T' + host_variable2;
FOR E IN EMPLOYEES WITH E.LAST_NAME = host_variable1
... some statements ...
END_FOR;
This method is recommended for all RDML statements when possible
because it generally improves the performance of the query.
o RDML/Pascal does not accept all possible valid Pascal host
language variables, and it issues the following error if one it
does not accept is encountered:
%RDML-W-HOST_VARIABLE, error detected in host variable syntax
The set of possible values is limited to the syntax described in the
RDML Reference Manual, with the restriction that the expression
allowed in an array index must be a simple name or expression.
The following illustrates an unacceptable statement:
FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID = emparray[empstruct.index]
... some statements...
END_FOR;
In the preceding example, empstruct.index is a reference to a
structure member. To avoid this problem, you would assign
empstruct.index to an intermediate variable and use that variable
in the FOR statement WITH clause.
3.6 /LINKAGE
This qualifier allows you to determine whether RDML will
communicate between separate modules with program sections or
global symbols by specifying either program section or global
symbol linkage methods.
Format:
/LINKAGE=PROGRAM_SECTIONS (Default)
/LINKAGE=GLOBAL_SYMBOLS
3.6.1 – PROGRAM_SECTIONS
When you use this qualifier, RDML communicates between separate
modules with program sections. Using this qualifier will allow
you to link RDML modules with SQLPRE and RDBPRE modules.
3.6.2 – GLOBAL_SYMBOLS
When you use this qualifier, RDML communicates between separate
modules with global symbols. Rdb recommends that you only
use this qualifier if you have problems linking with program
sections.
3.7 /DEFAULT_TRANSACTIONS
The /[NO]DEFAULT_TRANSACTIONS qualifier controls whether or not
the RDML preprocessor will generate code to support automatic
database attachment and automatic transactions by default.
Format:
/DEFAULT_TRANSACTIONS (Default)
/NODEFAULT_TRANSACTIONS (Recommended)
When you use the /DEFAULT_TRANSACTIONS qualifier, RDML allows you
to issue a query, without explicitly issuing a READY statement or
START_TRANSACTION statement first. If you do not issue these
statements explicitly, RDML will attach to the database and
start a READ_ONLY transaction for you when it encounters the
first DML statement. Furthermore, RDML allows you to detach
from a database without first completing (committing or rolling
back) any transaction that is attached to the database you are
finishing. RDML will commit any outstanding transaction for you.
The /DEFAULT_TRANSCTIONS qualifier causes the RDML preprocessor
to generate additional (often unnecessary) code that may incur
a significant amount of overhead because RDML must check the
state of the database and transactions as each DML statement is
processed.
The /NODEFAULT_TRANSACTIONS qualifier eliminates this overhead
by requiring you to explicitly READY a database and begin and end
transactions. When you use the /NODEFAULT_TRANSACTIONS qualifier
RDML does not check the state of the database and transactions as
each RDML statement is processed. In fact, if you do not close a
transaction prior to issuing a FINISH statement for the database
with which the transaction is associated, RDML will issue the
error message: %RDB-F-OPEN_TRANS.
Rdb recommends that you always use the
/NODEFAULT_TRANSACTIONS qualifier to reduce overhead, maximize
performance and enforce good programming practices. However,
the default for this qualifier is /DEFAULT_TRANSACTIONS, as this
causes the behavior that RDML has exhibited prior to Oracle Rdb
V3.0.
3.8 /INITIALIZE_HANDLES
The /[NO]INITIALIZE_HANDLES qualifier lets you control whether
RDML will initialize RDML-supplied database, transaction, and
request handles. Use of the /NOINITIALIZE_HANDLES qualifier
allows you to link a main image against a shareable image and
share handles between the two.
Format:
/INITIALIZE_HANDLES (Default)
/NOINITIALIZE_HANDLES
These qualifiers have no effect on whether or when handles are
cleared in the generated code; they only control initialization
of handles in declarations. Furthermore, they only affect
database, transaction and request handles that RDML declares.
User-specified transaction and request handles will not be
initialized when you use the /INITIALIZE_HANDLES qualifier.
RDML will initialize database handles when their scope is GLOBAL
or LOCAL. EXTERNAL scope database handles are never initialized.
3.9 /DISTRIBUTED_TRANSACTION
For application programs that were written under Oracle Rdb V3.1 or
earlier, use the two-phase commit protocol simply by recompiling
your programs. You must use the /DISTRIBUTED_TRANSACTION
qualifier in the precompiler command line. When you do this,
Oracle Rdb invokes the DECdtm system service calls for your
application. For example, to recompile the C program SAMPLE.RC
with the RDML preprocessor, use the following command:
$ RDML :== $RDML/C
$ RDML
SOURCE FILE> SAMPLE /DISTRIBUTED_TRANSACTION
3.10 /QUADWORD
The /QUADWORD qualifier allows RDML to translate the quadword
data type into a double or an unformatted buffer (RDML$QUAD_
TYPE).
Format:
/QUADWORD = DOUBLE (Default)
/QUADWORD = UNFORMATTED
The default is DOUBLE, which is the same as in previous versions.
3.11 /NEGSCALED_NUMERIC
The /NEGSCALED_NUMERIC qualifier enables RDML to convert the
negative scaled numeric to floating (REAL or DOUBLE), depending
upon the scale and data type, or to the original data type
ignoring negative scale.
Format:
/NEGSCALED_NUMERIC = FLOATING (Default)
/NEGSCALED_NUMERIC = UNSCALED
The default is FLOATING, which is the same as in previous
versions.
3.12 /DATE_TYPE
The /DATE_TYPE qualifier determines whether RDML generates the
DATE data type as an empty record or not as an empty record,
which is a pair of LONGWORD. This qualifier is used only when
preprocessing a Pascal program.
Format:
/DATE_TYPE = EMPTY_RECORDS (Default)
/DATE_TYPE = NOEMPTY_RECORDS
The default is EMPTY_RECORDS.
4 – Compiling
You can compile RDML/PASCAL and RDML/C preprocessed programs
like any other Pascal or C program. However, Rdb recommends
that you always use the /G_FLOATING qualifier or an equivalent
mechanism when you invoke the compiler to process RDML-generated
source files. For example:
DEC Pascal or VAX Pascal
$ PASCAL/G_FLOATING myprogram.pas
DEC C or VAX C
$ CC/G_FLOATING myprogram.c
5 – Linking
All RDML-generated applications must be linked with the RDML Run-
Time Library (SYS$LIBRARY:RDMLRTL.OLB). This library contains
code for various functions and procedures needed for the code
generated by RDML. To link RDML applications, use the following
line in an options file or command file.
SYS$LIBRARY:RDMLRTL.OLB/LIBRARY
For example, if you include the preceding line in an options file
named RDMLOPT.OPT, you can link a preprocessed and compiled RDML
/PASCAL or RDML/C program by entering:
$ LINK myprogram, rdmlopt/OPTIONS
6 – Release Notes
To see the current Release Notes for Oracle Rdb (which includes
information about RDML), type or print the following file:
SYS$HELP:RDBvvu.RELEASE_NOTES
where vv = version
u = update
For example:
SYS$HELP:RDB071.RELEASE_NOTES
7 – Statements
7.1 – ANY
Tests for the presence of any record in a record stream.
An ANY conditional expression is true if the record stream
specified by the record selection expression includes at least
one record. If you precede the ANY expression with the optional
NOT qualifier, the condition is true if there are no records in
the record stream.
7.1.1 – Examples
The following programs demonstrate the use of the ANY conditional
expression. These programs create a record stream containing
all the records from the SALARY_HISTORY relation that hold a
value greater than fifty thousand in the field SALARY_AMOUNT.
These programs then print an informational message if one or more
records are found that meet the above condition. Note that the
host language print statements do not have access to the context
created in the if statement.
7.1.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
int who;
main()
{
READY PERS;
START_TRANSACTION READ_ONLY;
GET
who = ANY SH IN SALARY_HISTORY WITH SH.SALARY_AMOUNT > 50000.00;
END_GET;
COMMIT;
if (who)
printf ("Someone is not underpaid \n");
FINISH;
}
7.1.1.2 – Pascal Example
program anycond (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
who : boolean;
begin
READY PERS;
START_TRANSACTION READ_WRITE;
GET
who = ANY SH IN SALARY_HISTORY WITH SH.SALARY_AMOUNT > 50000.00
END_GET;
COMMIT;
if (who) then
writeln ('Someone is not underpaid.');
FINISH;
end.
7.1.2 – Format
(B)0[many-clause =
qqqwqqq>qqqqqqqqwqq> [4mANY[m qqqwqq>qqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqk
mqqq> [4mNOT[m qqqj mqq> handle-options qj x
x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
x
mqqqqqqqqqqqqqqqqqqqqqq> rse qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq>
(B)0[mhandle-options =
q> ( qwqqqqqq> [4mREQUEST_HANDLE[m qqqqq> var qqqqqqqqwq> ) q>
tqqqqqq> [4mTRANSACTION_HANDLE[m qqq> var qqqqqqqqu
mqqqqqq> [4mREQUEST_HANDLE[m q> var q> , qqqqqqqk x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x
mqqqq> [4mTRANSACTION_HANDLE[m q> var qqqqqqqqqqqj
7.1.2.1 – Format arguments
rse A record selection expression. A phrase
that defines specific conditions that
individual records must meet before
Oracle Rdb includes them in a record stream.
handle-options A request handle, a transaction handle, or
both.
REQUEST_HANDLE var The REQUEST_HANDLE keyword followed by a
host language variable. A request handle
identifies a compiled Oracle Rdb request.
If you do not supply a request handle
explicitly, RDML associates a unique
request handle for the compiled request.
TRANSACTION_HANDLE The TRANSACTION_HANDLE keyword followed by
var a host language variable. A transaction
handle identifies a transaction. If
you do not supply a transaction handle
explicitly, RDML uses the default
transaction handle.
7.2 – Arithmetic Expr
Use an arithmetic expression to combine value expressions and
arithmetic operators. When you use an arithmetic expression in
a statement, Oracle Rdb calculates the value associated with the
expression and uses that value when executing the statement.
Therefore, an arithmetic expression must result in a value. If
either operand of an arithmetic expression is a missing value,
the resultant value also is a missing value.
7.2.1 – Examples
The following programs demonstrate the use of the multiplication
(*) arithmetic operator and the MODIFY statement. These programs
select the record of an employee in the SALARY_HISTORY relation
with a specified employee ID and that has no value for SALARY_
END. The purpose of specifying no value for SALARY_END is to
ensure that the only salary amount affected is the employee's
present salary. Next, the programs multiply the employee's
salary by 1.1 to produce an increase of ten percent in his or
her salary. The MODIFY statement replaces the old value in this
employee's SALARY_AMOUNT field with the new value.
7.2.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
main()
{
READY PERS;
START_TRANSACTION READ_WRITE;
FOR SH IN SALARY_HISTORY
WITH SH.EMPLOYEE_ID = "00164"
AND SH.SALARY_END MISSING
MODIFY SH USING
SH.SALARY_AMOUNT = SH.SALARY_AMOUNT * 1.1;
END_MODIFY;
END_FOR;
ROLLBACK;
FINISH;
}
7.2.1.2 – Pascal Example
program multiply (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
begin
READY PERS;
START_TRANSACTION READ_WRITE;
FOR SH IN SALARY_HISTORY
WITH SH.EMPLOYEE_ID = '00164'
AND SH.SALARY_END MISSING
MODIFY SH USING
SH.SALARY_AMOUNT := SH.SALARY_AMOUNT * 1.1;
END_MODIFY;
END_FOR;
ROLLBACK;
FINISH;
end.
7.2.2 – Format
(B)0[marith-expr =
qwq> numeric-value qwqqwqq> + qqwqqwq> numeric-value qwqqq>
tq> numeric-host-var qu tqq> - qqu tq> numeric-host-var qu
mq> numeric-db-field qj tqq> * qqu mq> numeric-db-field qj
mqq> / qqj
7.2.2.1 – Format arguments
numeric-value A numeric literal.
numeric-host-var A host language variable that holds a
numeric value.
numeric-db-field A database field (qualified with a context
variable) that holds a numeric value.
+ - * / Arithmetic operators. The following table
lists the arithmetic operators and their
functions.
Operator Function
--------------------------
+ Add
- Subtract
* Multiply
/ Divide
--------------------------
7.3 – AVERAGE
Determines the arithmetic mean of values for all records
specified by a record selection expression.
7.3.1 – Examples
The following programs demonstrate the use of the AVERAGE
function in a display statement. These programs:
o Use a record selection expression to form a record stream from
the view CURRENT_INFO, consisting of the records for which the
value in the SALARY field is greater than 50000.00
o Calculate the average salary for these selected records
o Print this average
7.3.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
double mean;
main()
{
READY PERS;
START_TRANSACTION READ_ONLY;
GET
mean = AVERAGE CI.SALARY OF CI IN CURRENT_INFO
WITH CI.SALARY > 50000.00;
END_GET;
COMMIT;
printf ("Average is: %f\n",mean);
FINISH;
}
7.3.1.2 – Pascal Example
program average_function (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
var
mean : double;
begin
READY PERS;
START_TRANSACTION READ_ONLY;
GET
mean = AVERAGE SH.SALARY_AMOUNT OF SH IN SALARY_HISTORY
WITH SH.SALARY_AMOUNT > 50000.00;
END_GET;
COMMIT;
writeln ('Average is: ', mean:10:2);
FINISH;
end.
7.3.2 – Format
(B)0[m
qq> [4mAVERAGE[m qqqqwqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqk
mqq> handle-options qqj x
x
lqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
x
mqqqqqqqqqqqq value-expr qqqqqqq> [4mOF[m qqqqqqqqq> rse qqqqqqqqqqq>
(B)0[mhandle-options =
q> ( qwqqqqqq> [4mREQUEST_HANDLE[m qqqqq> var qqqqqqqqwq> ) q>
tqqqqqq> [4mTRANSACTION_HANDLE[m qqq> var qqqqqqqqu
mqqqqqq> [4mREQUEST_HANDLE[m q> var q> , qqqqqqqk x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x
mqqqq> [4mTRANSACTION_HANDLE[m q> var qqqqqqqqqqqj
7.3.2.1 – Format arguments
value-expr A value expression. A symbol or a string
of symbols used to calculate a value. When
you use a value expression in a statement,
Oracle Rdb calculates the value associated
with the expression and uses that value
when executing the statement.
rse A record selection expression. A phrase
that defines specific conditions that
individual records must meet before
Oracle Rdb includes them in a record stream.
handle-options A request handle, a transaction handle, or
both.
REQUEST_HANDLE var The REQUEST_HANDLE keyword followed by a
host language variable. A request handle
identifies a compiled Oracle Rdb request.
If you do not supply a request handle
explicitly, RDML associates a unique
request handle for the compiled request.
TRANSACTION_HANDLE The TRANSACTION_HANDLE keyword followed by
var a host language variable. A transaction
handle identifies a transaction. If
you do not supply a transaction handle
explicitly, RDML uses the default
transaction handle.
7.4 – BASED_ON
The BASED ON clause lets you extract from the database the
data type and size of a field and then use it to declare host
language types. These are Pascal TYPE(s), and C typedef(s). When
you preprocess your program, the RDML preprocessor assigns the
data type and size attributes associated with the field to the
variable or function you declare using the the BASED ON clause.
See the DECLARE_VARIABLE statement for information on declaring
host language variables.
7.4.1 – Examples
The following programs demonstrate the use of the BASED ON clause
to declare types. Both the C and Pascal programs use the BASED
ON clause to declare the function, job_name. These programs pass
the value of the field JOB_CODE to the function. The function
determines the job title associated with the job code and passes
the job title back to the calling program. Note that in the C
program a program variable, temp_job_name, is required so that
the function can return the job title to the calling program.
In Pascal, function values are returned to the calling program
automatically.
7.4.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
typedef BASED ON JOBS.JOB_CODE job_code_type;
typedef BASED ON JOBS.JOB_TITLE job_title_type;
DECLARE_VARIABLE temp_job_name SAME AS JOBS.JOB_TITLE;
job_title_type job_name(job)
job_code_type job;
{ /* begin function */
READY PERS;
START_TRANSACTION READ_ONLY;
FOR FIRST 1 J IN JOBS
WITH J.JOB_CODE = job
strcpy (temp_job_name, J.JOB_TITLE);
END_FOR;
COMMIT;
FINISH;
return temp_job_name;
} /* end of function */
main ()
{
printf ("%s\n",job_name("APGM"));
}
7.4.1.2 – Pascal Example
program based_on_clause (INPUT,OUTPUT);
DATABASE PERS = FILENAME 'PERSONNEL';
type
job_code_type = BASED ON JOBS.JOB_CODE;
job_title_type = BASED ON JOBS.JOB_TITLE;
function job_name (job : JOB_CODE_TYPE ) : JOB_TITLE_TYPE;
begin {* function *}
READY PERS;
START_TRANSACTION READ_ONLY;
FOR FIRST 1 J IN JOBS
WITH J.JOB_CODE = job
job_name := J.JOB_TITLE;
END_FOR;
COMMIT;
FINISH;
end; {* function *}
begin {* main *}
writeln (job_name ('APGM'));
end.
7.4.2 – Format
(B)0[m[4mBASED[m [4mON[m qqqqqwqq>qqqqqqqqqqqqqqqqqqqqwqqqqqqqqk
mqq> db-handle qq> . qqqj x
x
lqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqj
x
mqqqqq> relation-name qqqq> . qqqq> field-name
7.4.2.1 – Format arguments
db-handle Database handle. A host variable used
to refer to a specific database you have
invoked. For more information see the
entry on the Database Handle clause.
relation-name The name of a relation in the database.
field-name The name of a field in a relation. For
example, once you have defined E as
the context variable for the EMPLOYEES
relation, E.LAST_NAME is a value
expression that refers to a value from
the LAST_NAME field of EMPLOYEES.
7.5 – BETWEEN
Creates a record stream containing records with values that fall
within a range you specify.
The BETWEEN conditional expression is true (records are included
in the stream) if the first value expression is between the
second and third value expression, or equal to one of them.
If you precede the BETWEEN expression with the optional NOT
qualifier, the condition is true if there are no records within
the range you specify with the second and third value expression.
The BETWEEN conditional expression sorts records in ascending
order by default. For information on sorting records see the
entry on the SORT statement.
7.5.1 – Examples
The following programs demonstrate the use of the BETWEEN
conditional expression with a numeric field. These programs form
a record stream consisting of all the records in the relation
CURRENT_SALARY where the field SALARY_AMOUNT contains a value
greater than or equal to 10,000, and less than or equal to
20,000. These programs print the last name and salary from of
each record in the record stream.
7.5.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
main()
{
READY PERS;
START_TRANSACTION READ_WRITE;
FOR CS IN CURRENT_SALARY
WITH CS.SALARY_AMOUNT
BETWEEN 10000.00 AND 20000.00
printf ("%s %f\n", CS.LAST_NAME, CS.SALARY_AMOUNT);
END_FOR;
COMMIT;
FINISH;
}
7.5.1.2 – Pascal Example
program between_numeric (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
begin
READY PERS;
START_TRANSACTION READ_ONLY;
FOR CS IN CURRENT_SALARY
WITH CS.SALARY_AMOUNT
BETWEEN 10000.00 AND 20000.00
writeln (CS.LAST_NAME, CS.SALARY_AMOUNT :10:2);
END_FOR;
COMMIT;
FINISH;
end.
7.5.2 – Format
(B)0[mbetween-clause =
qqqq> value-expr qqqqqwqq>qqqqqqqwqqq> [4mBETWEEN[m qqqqk
mqq> [4mNOT[m qqj x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqj
mqqq> value-expr qqq> [4mAND[m qqq> value-expr qqqqqqq>
7.5.2.1 – Format arguments
value-expr A value expression. A symbol or a string
of symbols used to calculate a value. When
you use a value expression in a statement,
Oracle Rdb calculates the value associated
with the expression and uses that value
when executing the statement.
7.6 – COMMIT
Ends a transaction and makes permanent all changes you made
during that transaction.
7.6.1 – Examples
The following programs demonstrate the use of the commit
statement to make permanent changes to a field value in a
database. These programs:
o Use a record selection expression to find an employee in the
EMPLOYEES relation with the ID number "00193"
o Use a MODIFY statement to change the field value of
E.LAST_NAME for this employee
Although this change is written to the database at the time of
the MODIFY, the change is not permanent until the programs issue
a COMMIT statement. After the programs issue the COMMIT statement
the old value for E.LAST_NAME is not available.
The C example uses the function pad_string to pad the name
"Smith-Fields" with blanks. Blanks are appended to the name so
that the length of the name matches the spaces reserved for it in
the database definition for LAST_NAME.
7.6.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
extern void pad_string();
main()
{
READY PERS;
START_TRANSACTION READ_WRITE;
FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID = '00193'
MODIFY E USING
pad_string ("Smith-Fields", E.LAST_NAME, sizeof(E.LAST_NAME));
END_MODIFY;
END_FOR;
COMMIT;
FINISH;
}
7.6.1.2 – Pascal Example
program commit_changes (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
begin
READY PERS;
START_TRANSACTION READ_WRITE;
FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID = '00193'
MODIFY E USING
E.LAST_NAME := 'Smith-Fields';
END_MODIFY;
END_FOR;
COMMIT;
FINISH;
end.
7.6.2 – Format
(B)0[m[4mCOMMIT[m qqwqq>qqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqwk
mqq> ( qq> [4mTRANSACTION_HANDLE[m qq> var qq>) qjx
x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
x
mqqqqqqqqqqqwqq>qqqqqqqqqqqqqqqwqqqqqqqqqqqqqq>
mqq> on-error qqqj
7.6.2.1 – Format arguments
TRANSACTION_HANDLE The TRANSACTION_HANDLE keyword followed by
var a host language variable. A transaction
handle identifies a transaction. If
you do not supply a transaction handle
explicitly, RDML uses the default
transaction handle.
on-error The ON ERROR clause. Specifies host
language statement(s) to be performed
if an error occurs during the COMMIT
operation. For more information see the
entry on ON ERROR.
7.7 – Cond Expr
Represents the relationship between two value expressions.
Sometimes called a Boolean expression, conditional expressions
are used in the WITH clause of the record selection expression.
The value of a conditional expression is true, false, or missing.
The two types of conditional expressions are:
o Those that express a relationship between two value
expressions, using a relational operator
For example, the expression SH.SALARY_AMOUNT > 50000 is true
if the value in the SALARY_AMOUNT field of the SALARY_HISTORY
record is greater than 50000. When Oracle Rdb evaluates this
expression, it examines the relationship between the two value
expressions, SH.SALARY_AMOUNT and 50000.
o Those that express a characteristic of a single value
expression
For example, E.EMPLOYEE_ID MISSING is true if there is no
value in the EMPLOYEE_ID field of an EMPLOYEES record.
The RDML conditional expressions are:
- ANY
- BETWEEN
- CONTAINING
- MATCHING
- MISSING
- Relational Operators
- STARTING WITH
- UNIQUE
For more information and the syntax diagram of each of these
expressions, see the individual HELP entries.
7.7.1 – Format
(B)0[mqqqwq> conditional-expr qqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqwqqqqqq>
tqqqqqqqqqqqqqqqqqqqqq> [4mNOT[m qq> conditional-expr qqqqu
tq> conditional-expr q> [4mAND[m qq> conditional-expr qqqqu
mq> conditional-expr q> [4mOR[m qq> conditional-expr qqqqj
(B)0[mconditional-expr =
qwqqq> value-expr qq> rel-operator qq> value-expr qqwqq>
tqqq> any-clause qqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqu
tqqq> between-clause qqqqqqqqqqqq>qqqqqqqqqqqqqqqqqu
tqqq> containing-clause qqqqqqqqq>qqqqqqqqqqqqqqqqqu
tqqq> matching-clause qqqqqqqqqqq>qqqqqqqqqqqqqqqqqu
tqqq> missing-clause qqqqqqqqqqqq>qqqqqqqqqqqqqqqqqu
tqqq> starting-with-clause qqqqqq>qqqqqqqqqqqqqqqqqu
mqqq> unique-clause qqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqj
(B)0[mrel-operator =
qqqqwqq> [4mEQ[m qqqwqqqqq>
tqq> = qqqu
tqq> [4mNE[m qqqu
tqq> <> qqqu
tqq> [4mGT[m qqqu
tqq> > qqqu
tqq> [4mGE[m qqqu
tqq> >= qqqu
tqq> [4mLT[m qqqu
tqq> < qqqu
tqq> [4mLE[m qqqu
mqq> <= qqqj
7.8 – CONTAINING
Tests for the presence of a specified string anywhere inside
a string expression. A conditional expression that includes a
CONTAINING clause is true if the string specified by the second
(pattern) string expression is found within the string specified
by the first (target) string expression. If either of the string
expressions in a CONTAINING conditional expression is missing,
the result is missing.
If you precede the CONTAINING clause with the optional NOT
qualifier, the condition is true if there are no records
containing the specified string. CONTAINING is not case
sensitive; it considers uppercase and lowercase forms of the
same character to be a match.
7.8.1 – Examples
The following programs demonstrate the use of the CONTAINING
conditional expression. These programs create a record stream
containing all the records in the EMPLOYEES relation in which
the LAST_NAME field contains the string "IACO" (in upper or lower
case letters). These programs print the employee ID and last name
from all the records contained in the record stream.
7.8.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
main()
{
READY PERS;
START_TRANSACTION READ_ONLY;
FOR E IN EMPLOYEES
WITH E.LAST_NAME CONTAINING "IACO"
printf ("%s %s\n", E.EMPLOYEE_ID,
E.LAST_NAME);
END_FOR;
COMMIT;
FINISH;
}
7.8.1.2 – Pascal Example
program containing (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
begin
READY PERS;
START_TRANSACTION READ_ONLY;
FOR E IN EMPLOYEES
WITH E.LAST_NAME CONTAINING 'IACO'
writeln (E.EMPLOYEE_ID, ' ', E.LAST_NAME);
END_FOR;
COMMIT;
FINISH;
end.
7.8.2 – Format
(B)0[mcontaining-clause =
qqq> value-expr qqqwqq>qqqqqqqwqq> [4mCONTAINING[m qqq> value-expr qqq>
mqq> [4mNOT[m qqj
7.8.2.1 – Format arguments
value-expr A value expression. A symbol or a string
of symbols used to calculate a value. When
you use a value expression in a statement,
Oracle Rdb calculates the value associated
with the expression and uses that value
when executing the statement.
7.9 – Context Variable
A temporary name that identifies a relation in a record stream
to Oracle Rdb. Once you have associated a context variable with a
relation, you use the context variable to refer to fields from
that relation. In this way, Oracle Rdb always knows which field from
which relation you are referring to.
You must use a context variable in every data manipulation
statement and in every data definition statement that uses a
record selection expression.
If you are accessing several record streams at once, the context
variable lets you distinguish between fields from different
record streams, even if different fields have the same name.
If you are accessing several record streams at once that consist
of the same relation and fields within that relation, context
variables let you distinguish between the two record streams.
7.9.1 – Examples
The following programs demonstrate the use of the context
variable "CS" for the CURRENT_SALARY view. These programs:
o Use "CS" to qualify field names in the record selection
expression, printf, and WRITELN statement
o Print the employee ID of all the employees who earn more than
forty thousand dollars
7.9.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
main()
{
READY PERS;
START_TRANSACTION READ_ONLY;
FOR CS IN CURRENT_SALARY WITH CS.SALARY_AMOUNT > 40000.00
printf ("%s\n",CS.EMPLOYEE_ID);
END_FOR;
COMMIT;
FINISH;
}
7.9.1.2 – Pascal Example
program context_var (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
begin
READY PERS;
START_TRANSACTION READ_ONLY;
FOR CS IN CURRENT_SALARY WITH CS.SALARY_AMOUNT > 40000.00
writeln (CS.EMPLOYEE_ID);
END_FOR;
COMMIT;
FINISH;
end.
7.9.2 – Format
(B)0[mcontext-variable = qqqqqqq> identifier qqqq>
7.9.2.1 – Format arguments
identifier A valid alphanumeric host language
identifier.
7.10 – COUNT
Returns the number of records in a record stream specified by
a record selection expression. The COUNT function differs from
other statistical functions because it operates on the record
stream defined by the record selection expression rather than on
the values in that record stream.
7.10.1 – Examples
The following programs demonstrate the use of the COUNT function
in a display statement. These programs:
o Use the COUNT function to compute the number of records stored
in the EMPLOYEES relation
o Print an informational message and this computed number
7.10.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
int num;
main()
{
READY PERS;
START_TRANSACTION READ_ONLY;
GET
num = COUNT OF E IN EMPLOYEES;
END_GET;
printf ("The number of employees is %d", num);
COMMIT;
FINISH;
}
7.10.1.2 – Pascal Example
program display_count (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
var
num : integer;
begin
READY PERS;
START_TRANSACTION READ_ONLY;
GET
num = COUNT OF E IN EMPLOYEES;
END_GET;
writeln ('The number of employees is', num);
COMMIT;
FINISH;
end.
7.10.2 – Format
(B)0[m
qqq> [4mCOUNT[m qqqqqqqqq>qqqqqqqwqq>qqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqk
mqq> handle-options qqqj x
x
lqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqqq> [4mOF[m qqqqqqq> rse qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq>
(B)0[mhandle-options =
q> ( qwqqqqqq> [4mREQUEST_HANDLE[m qqqqq> var qqqqqqqqwq> ) q>
tqqqqqq> [4mTRANSACTION_HANDLE[m qqq> var qqqqqqqqu
mqqqqqq> [4mREQUEST_HANDLE[m q> var q> , qqqqqqqk x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x
mqqqq> [4mTRANSACTION_HANDLE[m q> var qqqqqqqqqqqj
7.10.2.1 – Format arguments
rse A record selection expression. A phrase
that defines specific conditions that
individual records must meet before
Oracle Rdb includes them in a record stream.
handle-options A request handle, a transaction handle, or
both.
REQUEST_HANDLE var The REQUEST_HANDLE keyword followed by a
host language variable. A request handle
identifies a compiled Oracle Rdb request.
If you do not supply a request handle
explicitly, RDML associates a unique
request handle for the compiled request.
TRANSACTION_HANDLE The TRANSACTION_HANDLE keyword followed by
var a host language variable. A transaction
handle identifies a transaction. If
you do not supply a transaction handle
explicitly, RDML uses the default
transaction handle.
7.11 – CROSS
Lets you combine records from two or more record streams. You can
base such record combinations on the relationship between field
values in separate record streams.
7.11.1 – Examples
The following programs demonstrate the use of the CROSS clause to
join records from two relations (a non-equijoin). These programs
join the relations CURRENT_JOB and JOBS over their common field
JOB_CODE. This allows these programs to print a report that
contains fields from both relations. Specifically, these fields
are: LAST_NAME from the CURRENT_JOBS relation, JOB_CODE from the
JOBS relation, and JOB_TITLE from the JOBS relation.
7.11.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
main()
{
READY PERS;
START_TRANSACTION READ_ONLY;
FOR C IN CURRENT_JOB
CROSS J IN JOBS OVER JOB_CODE
printf ("%s",C.LAST_NAME);
printf (" %s",J.JOB_CODE);
printf (" %s\n", J.JOB_TITLE);
END_FOR;
COMMIT;
FINISH;
}
7.11.1.2 – Pascal Example
program person_job (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
begin
READY PERS;
START_TRANSACTION READ_ONLY;
FOR C IN CURRENT_JOB
CROSS J IN JOBS OVER JOB_CODE
writeln (C.LAST_NAME, ' ',J.JOB_CODE, ' ',J.JOB_TITLE);
END_FOR;
COMMIT;
FINISH;
end.
7.11.2 – Format
(B)0[mcross-clause =
qqwqq> [4mCROSS[m qqq> relation-clause qqwqq>qqqqqqqqqqqqqqqqqqqqqqqqqqqwqq>
x mqq> [4mOVER[m qwq> field-name qqwqwj
x mqqqqq , <qqqqqqqj x
x x
mqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
7.11.2.1 – Format arguments
relation-clause A clause that specifies a context
variable for a stream or a loop. For
more information see the entry on Context
Variables.
field-name The name of a field in a relation. For
example, once you have defined E as
the context variable for the EMPLOYEES
relation, E.LAST_NAME is a value
expression that refers to a value from
the LAST_NAME field of EMPLOYEES.
7.12 – DATABASE
Names the database to be accessed in a program or program module
and specifies to RDML which database to use and where to declare
variables. However, RDML does not attach to the database when it
encounters the DATABASE statement. The READY statement causes an
attach to the database.
The only required parameter for the DATABASE statement is the
database name. The name must be the file name that represents the
database file or a logical name that resolves to a file name.
You can also specify the following:
o A database handle
o The scope of the database handle
o Different sources of the database definition for compilation
and execution
o DBKEY scope
You can specify whether the scope of each record's database
key (dbkey) is valid through a COMMIT statement in each of
your transactions, or through a FINISH statement. See the
explanations for DBKEY SCOPE FINISH and DBKEY SCOPE COMMIT in
the Arguments section fro details.
7.12.1 – Examples
The following programs demonstrate how to specify a COMPILETIME
and RUNTIME database as the same database. Because a RUNTIME
database is not explicitly declared, the programs use the
COMPILETIME database at runtime also.
7.12.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
main ()
{
READY PERS;
START_TRANSACTION READ_ONLY;
/* perform some action on the database */
COMMIT;
FINISH;
}
7.12.1.2 – Pascal Example
program db (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
begin
READY PERS;
START_TRANSACTION READ_ONLY;
(* perform some actions on the database *)
COMMIT;
FINISH;
end.
7.12.2 – Format
(B)0[mINVOKE [4mDATABASE[m qqqqqqqqwqqqqqqqqqqqqq>qqqqqqqqqqqwqqqqqqqqqqqqqqqqk
mqq> db-handle-options qqqj x
x
lqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqwqqqqqqqqqq>qqqqqqqqqwqqwqq> [4mPATHNAME[m qqq> path-name qqwqqk
mqq> COMPILETIME qqqj mqq> [4mFILENAME[m qqq> file-spec qqj x
lqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqwqqqqqqqqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqqqqqqwqqk
mqq> [4mRUNTIME[m FILENAME qqqqwqqqq> file-spec qqqqqqqqwqqj x
mqqqq> host-variable qqqqj x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqqqqwqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqk
mq> [4mDBKEY[m [4mSCOPE[m IS qqwq> [4mCOMMIT[m qqqqqqqu x
mq> [4mFINISH[m qqqqqqqj x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqwqqqqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqwqqq>
mq> [4mREQUEST_HANDLE[m [4mSCOPE[m IS qwq> [4mDEFAULT[m qqwqqj
mq> [4mFINISH[m qqqj
(B)0[mdb-handle-options =
qqqqqqqwqqqq> db-handle q> = qwqqqqqqqqqqqqqqqqqqwwq>
x mq> handle-scope qqjx
x x
mqqwq>qqqqqqqqqqqqqqqqwqq> db-handle qq> = qqqj
x x
mq> handle-scope qqj
(B)0[mhandle-scope =
qqwqqqqqwqqwqqq> [4mGLOBAL[m qqqqqqqqwqqwqqqqqwq>
m> [ qj x x m> ] qj
tqqq> [4mEXTERNAL[m qqqqqqu
x x
mqqq> [4mLOCAL[m qqqqqqqqqj
7.12.2.1 – Format arguments
db-handle-options Database handle options. Allows you to
specify a database handle and its scope.
db-handle Database handle. A host variable used
to refer to a specific database you have
invoked. For more information see the
entry on the Database Handle clause.
handle-scope Specifies the scope of the database
handle. Note that you can enclose the
scope of a database handle in brackets,
for example: [GLOBAL].
GLOBAL Specifies that the database is global
to all modules in an image. This allows
you to have a single transaction across
multiple modules.
EXTERNAL Specifies that the database is actually
accessed through another module.
LOCAL Specifies that the database is local to
the module in which it is declared.
COMPILETIME The source of the database definitions
when the program is compiled. For Oracle
Rdb this can be either a CDD path name or
a file specification. If you specify only
the compiletime identifier and omit the
run-time identifier, Oracle Rdb uses the
compile-time identifier for both compiling
and running the program. For example, RDML
will mistakenly use 'CDD$TOP.PERSONNEL' as
the run-time database file name in the
following example:
DATABASE COMPILETIME PATHNAME 'CDD$TOP.PERSONNEL';
However, RDML will correctly interpret the
following:
DATABASE COMPILETIME PATHNAME 'CDD$TOP.PERSONNEL'
RUNTIME FILENAME 'PERSONNEL';
path-name A full or relative CDD path name, enclosed
in quotation marks, specifying the source
of the database definition. Use single
quotes (' ') when the host language is
Pascal. Use double quotes (" ") when the
host language is C.
file-spec File specification. A full or partial file
specification, or logical name enclosed in
quotation marks, specifying the source of
the database. Use single quotes (' ') when
the host language is Pascal. Use double
quotes (") when the host language is C.
RUNTIME FILENAME The source of the database definitions
when the program is run. This can be
either a file specification or a host
variable. If you do not specify this
parameter, Oracle Rdb uses the compile-
time identifier for both compiling and
running the program.
host-variable A valid host language variable that
equates to a database file specification.
DBKEY SCOPE COMMIT The DBKEY SCOPE clause controls when the
database key (dbkey) of an erased record
may be reused by Oracle Rdb. When the DBKEY
SCOPE is COMMIT, (the default), Oracle Rdb
cannot reuse the DBKEY of an erased
record (to store another record) until
the transaction that erased the original
record completes (by entering COMMIT). If
the user who erased the original record
enters a ROLLBACK statement, then the
dbkey cannot be reused by Oracle Rdb.
DBKEY SCOPE FINISH Controls when the dbkey of an erased
record may be reused by Oracle Rdb. When
DBKEY SCOPE IS FINISH, Oracle Rdb cannot
reuse the dbkey (to store another record)
until the user who erased the original
record unbinds from the database (usually,
by issuing a FINISH statement). With DBKEY
SCOPE FINISH, an RDML program can complete
one or several transactions and, while
still attached to the database, use the
dbkey obtained during a STORE operation to
directly access those records.
REQUEST HANDLE SCOPE RDML automatically initializes any request
DEFAULT handles it generates that are in the same
compilation unit as the FINISH statement.
RDML does not reinitialize any user-
specified request handles nor does it
reinitialize any request handles that are
outside of the compilation unit where the
request is initiated. With this option,
the value of the request handle is not set
to zero after the RDML FINISH statement
executes. REQUEST_HANDLE SCOPE DEFAULT is
the default.
REQUEST HANDLE SCOPE The value of the request handle is set
FINISH to zero after the RDML FINISH statement
executes.
The SQL FINISH statement initializes
all request handles in all compilation
units in a program. The RDBPRE and RDML
preprocessors allow programs to define
and manipulate request handles. If you
do not want your request handles to be
reinitialized, then you must use RDML or
RDBPRE (not SQL) to do the attach, and you
must use REQUEST_HANDLE SCOPE IS DEFAULT.
7.13 – DB Field
Use the database field value expression to reference database
fields in record selection expressions and in other value
expressions.
7.13.1 – Examples
The following programs demonstrate the use of the database field
value expression. These programs use the database field value
expression, FOR J IN JOBS, to declare the context variable J.
This allows the programs to use the clause, J.JOB_CODE, to mean
JOBS.JOB_CODE. The programs search the field JOB_CODE for the
string "APGM". Any record that contains the specified string
becomes part of the record stream. These programs then use J to
qualify the fields in the host language print statements. The job
title, minimum salary and the maximum salary for each record in
the record stream are printed.
7.13.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
main()
{
READY PERS;
START_TRANSACTION READ_ONLY;
FOR J IN JOBS WITH J.JOB_CODE = "APGM"
printf ("%s", J.JOB_TITLE);
printf (" $%f", J.MINIMUM_SALARY);
printf (" $%f\n", J.MAXIMUM_SALARY);
END_FOR;
COMMIT;
FINISH;
}
7.13.1.2 – Pascal Example
program fld_value (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
begin
READY PERS;
START_TRANSACTION READ_ONLY;
FOR J IN JOBS WITH J.JOB_CODE = 'APGM'
writeln (J.JOB_TITLE,
' $', J.MINIMUM_SALARY: 10 : 2,
' $', J.MAXIMUM_SALARY: 10 : 2);
END_FOR;
COMMIT;
FINISH;
end.
7.13.2 – Format
(B)0[mdb-field-expr
qqq> context-var qqq> . qqq> field-name qqq>
7.13.2.1 – Format arguments
context-var A context variable. A temporary name that
you associate with a relation. You define
a context variable in a relation clause.
For more information see the entry on
Context Variables.
field-name The name of a field in a relation. For
example, once you have defined E as
the context variable for the EMPLOYEES
relation, E.LAST_NAME is a value
expression that refers to a value from
the LAST_NAME field of EMPLOYEES.
7.14 – DB Handle
Oracle Rdb uses the database handle to identify the particular
database that is referenced by a database request. Note that the
database handle should not be thought of as a program variable.
The database handle provides context to any statement that uses
the database handle. When your program accesses a single database
you do not have to include database handles or scopes in the
DATABASE statement. Unlike transaction handles and request
handles, database handles do not have to be declared in your
programs. The RDML preprocessor automatically generates the data
declaration for the database handle.
The database handle is used in several RDML statements and
clauses to identify a database:
o DATABASE
o FINISH
o READY
o relation-clause of the record selection expression
o DEFINE_TYPE or DECLARE_VARIABLE (synonyms)
o BASED ON clause
Oracle Rdb lets you have more than one database active at a given
time. You can use the database handle to distinguish among these
different databases in RDML statements.
Both the default and named database handle are declared as GLOBAL by
default.
7.14.1 – More
The example program shown here illustrates a problem with RDML. It is
written in VAX C, and although the precompilation is clean, the C
compiler gives errors at the READY statement. This problem occurs only
when the READY statement contains a database handle that is incorrectly
specified as a variable rather than specified in a DATABASE statement.
This program works if a database handle specified in one of the database
statements is used in the READY statement, whether the READY statement
is used in a function or in a main module.
#include stdio
DATABASE first_db = FILENAME 'the_first';
DATABASE second_db = FILENAME 'the_second';
main()
{
one_ready(first_db);
one_ready(second_db);
printf("%d\n",first_db);
printf("%d\n",second_db);
START_TRANSACTION READ_WRITE;
COMMIT;
}
one_ready(the_handle)
unsigned long the_handle;
{
READY the_handle ON ERROR printf("an error\n"); END_ERROR;
return(the_handle);
}
The READY statement, as documented in the V3.0, V3.1, and V4.0 Oracle
Rdb RDML Reference Manual, states that the database handle (or multiple
database handles) used in the READY statement must be specified in a
DATABASE statement. Oracle does not support user-specified database
handles in RDML; database handles are automatically declared and used
in RDML as a result of their specification in a DATABASE statement
(which is really a declaration). This program attempts to use a
database handle that is declared explicitly (as opposed to being
specified in a DATABASE statement), and RDML therefore does not
recognize it as a database handle. Because a READY statement by itself
is valid, RDML simply recognizes that the READY statement syntax has
terminated at that point, and so it fails to detect the ON ERROR clause
later in the same line. (It assumes that the rest of the line was host
language syntax.)
To enable RDML to recognize your database handle, associate a unique
number with each database handle, and use it to identify which database
handle to use. The example shown here is a possible approach:
#include <stdio.h>
DATABASE first_db = FILENAME 'PERSONNEL';
DATABASE second_db = FILENAME 'PERSONNEL';
main()
{
one_ready(1);
one_ready(2);
printf("%d\n", first_db);
printf("%d\n", second_db);
START_TRANSACTION READ_WRITE;
COMMIT;
}
one_ready(int which_handle)
{
switch (which_handle)
{
case 1:
READY first_db ON ERROR printf("an error\n"); END_ERROR;
break;
case 2:
READY second_db ON ERROR printf("an error\n"); END_ERROR;
break;
}
}
7.14.2 – Examples
The following program segments demonstrate how to use a database
handle in a READY statement. These segments declare a COMPILETIME
database and ready it. Because a RUNTIME database is not
explicitly declared, the programs use the COMPILETIME database
at runtime also.
7.14.2.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
main ()
{
READY PERS;
START_TRANSACTION READ_ONLY;
/* perform some database actions */
COMMIT;
FINISH PERS;
}
7.14.2.2 – Pascal Example
program dbhandle (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
begin
READY PERS;
START_TRANSACTION READ_ONLY;
{* perform some actions on the database *}
COMMIT;
FINISH PERS;
end.
7.14.3 – Format
(B)0[mdb-handle = qqqqq> host-variable qqqqq>
7.14.3.1 – Format arguments
host-variable A valid alphanumeric host language
variable.
7.15 – DEFINE_TYPE
The DECLARE_VARIABLE and DEFINE_TYPE clauses have exactly the
same function. Oracle Rdb decided to rename the clause to clarify
that its function is to declare host language variables, not to
define host language types. Note that the DEFINE_TYPE clause
may still be used; however, Rdb recommends that all new
applications use the DECLARE_VARIABLE clause in place of the
DEFINE_TYPE clause. See the help file entry on DECLARE_VARIABLE
for more information.
7.16 – DECLARE_STREAM
Declares the context of a record stream and thereby is
able to associate a stream name with a RSE. This provides
Oracle Rdb with the context needed to place the elements of the
START_STREAM...FETCH...END_STREAM in a single programming module
in any order.
7.16.1 – Examples
The following programs demonstrate the use of the DECLARE_STREAM
statement to specify a record selection expression that limits
the records in the stream to those with a value of less than ten
thousand in the SALARY_AMOUNT field.
7.16.1.1 – C Example
#include <stdio.h>
#define TRUE 1
#define FALSE 0
DATABASE PERS = FILENAME "PERSONNEL";
DECLARE_STREAM sal USING SH IN SALARY_HISTORY
WITH SH.SALARY_AMOUNT LT 10000;
int end_of_stream;
main()
{
READY PERS;
START_TRANSACTION READ_WRITE;
START_STREAM sal;
FETCH sal
AT END
end_of_stream = TRUE;
END_FETCH;
while (! end_of_stream)
{
MODIFY SH USING
SH.SALARY_AMOUNT = SH.SALARY_AMOUNT * (1.5);
END_MODIFY;
FETCH sal
AT END
end_of_stream = TRUE;
END_FETCH;
}
END_STREAM sal;
COMMIT;
FINISH;
}
7.16.1.2 – Pascal Example
program anycond (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
var
end_of_stream : boolean;
DECLARE_STREAM sal USING SH IN SALARY_HISTORY
WITH SH.SALARY_AMOUNT LT 10000;
begin
READY PERS;
START_TRANSACTION READ_WRITE;
START_STREAM sal;
FETCH sal
AT END
end_of_stream := TRUE;
END_FETCH;
while not end_of_stream do
begin
MODIFY SH USING
SH.SALARY_AMOUNT := SH.SALARY_AMOUNT * (1.5);
END_MODIFY;
FETCH sal
AT END
end_of_stream := TRUE;
END_FETCH;
end;
END_STREAM sal;
COMMIT;
FINISH;
end.
7.16.2 – Format
(B)0[m
[4mDECLARE_STREAM[m qqqqqwqq>qqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqk
mqq> handle-options qj x
x
lqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
x
mqqqqqqqqqqqqqqqq> declared-stream-name qqqqq> [4mUSING[m qqqq> rse qq>
(B)0[mhandle-options =
q> ( qwqqqqqq> [4mREQUEST_HANDLE[m qqqqq> var qqqqqqqqwq> ) q>
tqqqqqq> [4mTRANSACTION_HANDLE[m qqq> var qqqqqqqqu
mqqqqqq> [4mREQUEST_HANDLE[m q> var q> , qqqqqqqk x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x
mqqqq> [4mTRANSACTION_HANDLE[m q> var qqqqqqqqqqqj
7.16.2.1 – Format arguments
declared-stream-name A name you give the stream you declare.
The stream name must be a valid operating
system name.
rse A record selection expression. A phrase
that defines the specific conditions
that individual records must meet before
Oracle Rdb includes them in a record stream.
handle-options A request handle, a transaction handle, or
both.
REQUEST_HANDLE var The REQUEST_HANDLE keyword followed by a
host language variable. A request handle
identifies a compiled Oracle Rdb request.
If you do not supply a request handle
explicitly, RDML associates a unique
request handle for the compiled request.
TRANSACTION_HANDLE The TRANSACTION_HANDLE keyword followed by
var a host language variable. A transaction
handle identifies a transaction. If
you do not supply a transaction handle
explicitly, RDML uses the default
transaction handle.
7.17 – Declared END STREAM
Ends a declared stream. You can issue several declared END_STREAM
statements in a module, and as long as you use the same declared
stream name in each END_STREAM statement, they will all refer to
the same stream.
7.17.1 – Examples
The following programs demonstrate the use of the declared
END_STREAM statement.
7.17.1.1 – C Example
#include <stdio.h>
#define TRUE 1
#define FALSE 0
DATABASE PERS = FILENAME "PERSONNEL";
DECLARE_STREAM sal USING SH IN SALARY_HISTORY
WITH SH.SALARY_AMOUNT LT 10000;
int end_of_stream;
main()
{
READY PERS;
START_TRANSACTION READ_WRITE;
START_STREAM sal;
FETCH sal
AT END
end_of_stream = TRUE;
END_FETCH;
while (! end_of_stream)
{
MODIFY SH USING
SH.SALARY_AMOUNT = SH.SALARY_AMOUNT * (1.5);
END_MODIFY;
FETCH sal
AT END
end_of_stream = TRUE;
END_FETCH;
}
END_STREAM sal;
COMMIT;
FINISH;
}
7.17.1.2 – Pascal Example
program anycond (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
var
end_of_stream : boolean;
DECLARE_STREAM sal USING SH IN SALARY_HISTORY
WITH SH.SALARY_AMOUNT LT 10000;
begin
READY PERS;
START_TRANSACTION READ_WRITE;
START_STREAM sal;
FETCH sal
AT END
end_of_stream := TRUE;
END_FETCH;
while not end_of_stream do
begin
MODIFY SH USING
SH.SALARY_AMOUNT := SH.SALARY_AMOUNT * (1.5);
END_MODIFY;
FETCH sal
AT END
end_of_stream := TRUE;
END_FETCH;
end;
END_STREAM sal;
COMMIT;
FINISH;
end.
7.17.2 – Format
(B)0[m
[4mEND_STREAM[m qqqqqq> stream-name qqqqqqqqqk
lqqqqqqqqq<qqqqqqqqqqqqqqqj
mqwqqqqqqqqqqqqqqwqqqqqqqqqq>
mq> on-error qqj
7.17.2.1 – Format arguments
stream-name The name you gave to the stream when you
issued the DECLARE_STREAM statement.
on-error The ON ERROR clause. Specifies host
language statements or Oracle Rdb statements,
or both, to be performed if an error
occurs during the END_STREAM operation.
7.18 – Declared START STREAM
Starts a stream that has been declared earlier in the module with
a 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 are executed in the
order: START_STREAM, FETCH, END_STREAM.
7.18.1 – Examples
The following programs demonstrate the use of the declared
START_STREAM statement to open a stream declared with the
DECLARE_STREAM statement.
7.18.1.1 – C Example
#include <stdio.h>
#define TRUE 1
#define FALSE 0
DATABASE PERS = FILENAME "PERSONNEL";
DECLARE_STREAM sal USING SH IN SALARY_HISTORY
WITH SH.SALARY_AMOUNT LT 10000;
int end_of_stream;
main()
{
READY PERS;
START_TRANSACTION READ_WRITE;
START_STREAM sal;
FETCH sal
AT END
end_of_stream = TRUE;
END_FETCH;
while (! end_of_stream)
{
MODIFY SH USING
SH.SALARY_AMOUNT = SH.SALARY_AMOUNT * (1.5);
END_MODIFY;
FETCH sal
AT END
end_of_stream = TRUE;
END_FETCH;
}
END_STREAM sal;
COMMIT;
FINISH;
}
7.18.1.2 – Pascal Example
program anycond (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
var
end_of_stream : boolean;
DECLARE_STREAM sal USING SH IN SALARY_HISTORY
WITH SH.SALARY_AMOUNT LT 10000;
begin
READY PERS;
START_TRANSACTION READ_WRITE;
START_STREAM sal;
FETCH sal
AT END
end_of_stream := TRUE;
END_FETCH;
while not end_of_stream do
begin
MODIFY SH USING
SH.SALARY_AMOUNT := SH.SALARY_AMOUNT * (1.5);
END_MODIFY;
FETCH sal
AT END
end_of_stream := TRUE;
END_FETCH;
end;
END_STREAM sal;
COMMIT;
FINISH;
end.
7.18.2 – Format
(B)0[m
[4mSTART_STREAM[m qqqqqqqq> declared-stream-name qqqqqqk
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqqqqqqwqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqq>
mq> on-error qqj
7.18.2.1 – Format arguments
declared-stream-name The name you gave to the stream when you
issued the DECLARE_STREAM statement.
on-error The ON ERROR clause. Specifies host
language statements or Oracle Rdb statements,
or both, to be performed if an error
occurs during the START_STREAM operation.
7.19 – DECLARE_VARIABLE
The DECLARE_VARIABLE clause lets you declare a host language
variable by referring to a field associated with a database
relation. The variable inherits the data type and size
attributes associated with the field. See the BASED ON clause
for information on declaring program functions: Pascal TYPE(s),
and C typedef(s).
The DECLARE_VARIABLE and DEFINE_TYPE clauses have exactly the
same function. Oracle Rdb decided to rename the clause to clarify
that its function is to declare host language variables, not to
define host language types. Note that the DEFINE_TYPE clause
may still be used; however, Rdb recommends that all new
applications use the DECLARE_VARIABLE clause in place of the
DEFINE_TYPE clause.
7.19.1 – Examples
The following programs demonstrate the use of the
DECLARE_VARIABLE clause to declare a program variable. These
programs:
o Declare the variable, badge, to have the same data type and
size attributes as EMPLOYEE_ID in the EMPLOYEES relation.
o Use this variable for interactive processing. Note that the
interactive portion of the programs appear before the READY
statement. This keeps locks on the database to a minimum.
o Select the record from the EMPLOYEES relation that has the
same value for EMPLOYEE_ID as is stored in badge.
o Modify the STATUS_CODE field of this record
Note that the C program uses the read_string function to prompt
for and receive a value for badge. For more information on this
function see Appendix B of the "RDML Reference Manual".
7.19.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
extern void read_string();
static DECLARE_VARIABLE badge SAME AS EMPLOYEES.EMPLOYEE_ID;
main()
{
read_string ("Employee ID: ", badge, sizeof(badge));
READY PERS;
START_TRANSACTION READ_WRITE;
FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID = badge
MODIFY E USING
strcpy(E.STATUS_CODE,"1");
END_MODIFY;
END_FOR;
ROLLBACK;
FINISH;
}
7.19.1.2 – Pascal Example
program modify_with_host (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
var
DECLARE_VARIABLE badge SAME AS EMPLOYEES.EMPLOYEE_ID;
begin
write ('Employee ID: ');
readln (badge);
READY PERS;
START_TRANSACTION READ_WRITE;
FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID = badge
MODIFY E USING
E.STATUS_CODE := '1';
END_MODIFY;
END_FOR;
ROLLBACK;
FINISH;
end.
7.19.2 – Format
(B)0[m[4mDECLARE_VARIABLE [m qqqqqqqqqqqqqqqqqq> host-variable qqqk
x
lqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqqwqq>qqqqqqqwqq> [4mAS[m qqwqq>qqqqqqqqqqqqqqqqqqqwqqk
mqq> SAME qj mqq> db-handle qq> . qqj x
x
lqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqqqqqqqqqqqq> relation-name qqq> . qqq> field-name qqq>
7.19.2.1 – Format arguments
host-variable A valid host language variable.
db-handle Database handle. A host variable used
to refer to a specific database you have
invoked. For more information see the
entry on the Database Handle clause.
relation-name The name of a relation in the database.
field-name The name of a field in a relation. For
example, once you have defined E as
the context variable for the EMPLOYEES
relation, E.LAST_NAME is a value
expression that refers to a value from
the LAST_NAME field of EMPLOYEES.
7.20 – ERASE
Deletes a record from a relation or open stream.
7.20.1 – Examples
The following programs demonstrate the use of the ERASE statement
to delete records from a relation. These programs:
o Start a READ_WRITE transaction
o Find the records in COLLEGES with the college code "PURD"
o Delete those records from the COLLEGES relation
7.20.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
main()
{
READY PERS;
START_TRANSACTION READ_WRITE;
FOR C IN COLLEGES WITH C.COLLEGE_CODE = "PURD"
ERASE C;
END_FOR;
ROLLBACK;
FINISH;
}
7.20.1.2 – Pascal Example
program erase_record (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
begin
READY PERS;
START_TRANSACTION READ_WRITE;
FOR C IN COLLEGES WITH C.COLLEGE_CODE = 'PURD'
ERASE C;
END_FOR;
ROLLBACK;
FINISH;
end.
7.20.2 – Format
(B)0[m[4mERASE[m qqqq> context-var qqqwqq>qqqqqqqqqqqqqwqq>
mqq> on-error qqqj
7.20.2.1 – Format arguments
context-var A context variable. A temporary name that
you associate with a relation. You define
a context variable in a relation clause.
For more information see the entry on
Context Variables.
on-error The ON ERROR clause. Specifies host
language statement(s) to be performed
if an error occurs during the ERASE
operation. For more information see the
entry on ON ERROR.
7.21 – FETCH
Retrieves the next record from a record stream. The FETCH
statement is used:
o After a START_STREAM statement
o Before any other RDML statements that affect the context
established by the START_STREAM statement
The FETCH statement advances the pointer for a record stream to
the next record of a relation. Unlike the FOR statement, which
advances to the next record automatically, the FETCH statement
allows you explicit control of the record stream. For instance,
you might use the FETCH statement to print a report where the
first six rows have five columns, and the seventh row only three.
7.21.1 – Examples
The following programs demonstrate the use of the FETCH statement
to advance a pointer in an open stream. These programs
o Fetch a record from a stream specified by the DECLARE_STREAM
statement
o If at least one record is found, enter a "while" loop
o Modify the record
o Fetch and modify all the records in the stream
o End the stream
7.21.1.1 – C Example
#include <stdio.h>
#define TRUE 1
#define FALSE 0
DATABASE PERS = FILENAME "PERSONNEL";
DECLARE_STREAM sal USING SH IN SALARY_HISTORY
WITH SH.SALARY_AMOUNT LT 10000;
int end_of_stream;
main()
{
READY PERS;
START_TRANSACTION READ_WRITE;
START_STREAM sal;
FETCH sal
AT END
end_of_stream = TRUE;
END_FETCH;
while (! end_of_stream)
{
MODIFY SH USING
SH.SALARY_AMOUNT = SH.SALARY_AMOUNT * (1.5);
END_MODIFY;
FETCH sal
AT END
end_of_stream = TRUE;
END_FETCH;
}
END_STREAM sal;
COMMIT;
FINISH;
}
7.21.1.2 – Pascal Example
program anycond (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
var
end_of_stream : boolean;
DECLARE_STREAM sal USING SH IN SALARY_HISTORY
WITH SH.SALARY_AMOUNT LT 10000;
begin
READY PERS;
START_TRANSACTION READ_WRITE;
START_STREAM sal;
FETCH sal
AT END
end_of_stream := TRUE;
END_FETCH;
while not end_of_stream do
begin
MODIFY SH USING
SH.SALARY_AMOUNT := SH.SALARY_AMOUNT * (1.5);
END_MODIFY;
FETCH sal
AT END
end_of_stream := TRUE;
END_FETCH;
end;
END_STREAM sal;
COMMIT;
FINISH;
end.
7.21.2 – Format
(B)0[m[4mFETCH[m qq> stream-name qqqqqqqwqq>qqqqqqqqqqqqqwqqqqqk
mqq> on-error qqqj x
lqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqwqq>qqqqqqqqqqqqq qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqq>
tqq> [4mAT[m [4mEND[m qqwqq> statement qqwq> [4mEND_FETCH[m qqu
x mqqqqqqq<qqqqqqqqj x
mqq> [4mEND_FETCH[m qqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqj
7.21.2.1 – Format arguments
stream-name The stream from which you want to FETCH
the next record.
on-error The ON ERROR clause. Specifies host
language statement(s) to be performed
if an error occurs during the FETCH
operation. For more information see the
entry on ON ERROR.
statement Any valid RDML or host language statement
to be executed when your program reaches
the end of a record stream. Use a
semicolon (;) at the end of each RDML,
Pascal, or C statement.
7.22 – FINISH
Explicitly declares a database closed. By default, the FINISH
statement with no parameters also commits all transactions that
have not been committed or rolled back. When used in conjunction
with the /NODEFAULT_TRANSACTIONS qualifier on the RDML command
line, issuing the FINISH statement before you explicitly end a
transaction (with the COMMIT or ROLLBACK statement) will result
in an error.
7.22.1 – Examples
The following program fragments:
o Declare a database
o Enter an RDML FOR loop, implicitly opening the database
o Print the last name of each employee in EMPLOYEES
o Commit the transaction
o Close the database
7.22.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
main()
{
FOR E IN EMPLOYEES
printf ("%s\n", E.LAST_NAME);
END_FOR;
COMMIT;
FINISH;
}
7.22.1.2 – Pascal Example
program empupdate (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
begin
FOR E IN EMPLOYEES
writeln (E.LAST_NAME);
END_FOR;
COMMIT;
FINISH;
end.
7.22.2 – Format
(B)0[m[4mFINISH[m qqqwqqq>qqqqqqqqqqqqqqqwqwqq>qqqqqqqqqqqqqwqq>
mqwq> db-handle qwqqj mqq> on-error qqqj
mqqqqq , <qqqqqj
7.22.2.1 – Format arguments
db-handle Database handle. A host variable used
to refer to a specific database you have
invoked. For more information see the
entry on the Database Handle clause.
on-error The ON ERROR clause. Specifies host
language statement(s) to be performed
if an error occurs during the FINISH
operation. For more information see the
entry on ON ERROR.
7.23 – FIRST Clause
The FIRST clause specifies the maximum number of records in a
record stream formed by a record selection expression.
7.23.1 – Examples
The following programs demonstrate the use of the FIRST clause
and the SORT clause. These programs sort the employees relation
in ascending order based on EMPLOYEE_ID. The FIRST 50 statement
creates a record stream that contains the first 50 records from
the sorted employees relation. These programs print the employee
ID and last name of these fifty employee records.
7.23.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
main ( )
{
READY PERS;
START_TRANSACTION READ_ONLY;
FOR FIRST 50 E IN EMPLOYEES
SORTED BY E.EMPLOYEE_ID
printf ("%s ",E.EMPLOYEE_ID);
printf ("%s\n",E.LAST_NAME);
END_FOR;
COMMIT;
FINISH;
}
7.23.1.2 – Pascal Example
program first_clause (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
begin
READY PERS;
START_TRANSACTION READ_ONLY;
FOR FIRST 50 E IN EMPLOYEES
SORTED BY E.EMPLOYEE_ID
writeln (E.EMPLOYEE_ID, ' ', E.LAST_NAME);
END_FOR;
COMMIT;
FINISH;
end.
7.23.2 – Format
(B)0[mfirst-clause =
qqqqqq> [4mFIRST[m qqqq> value-expr qqqq>
7.23.2.1 – Format arguments
value-expr A value expression. A symbol or a string
of symbols used to calculate a value. When
you use a value expression in a statement,
Oracle Rdb calculates the value associated
with the expression and uses that value
when executing the statement.
7.24 – FIRST_FROM
The FIRST FROM expression forms a record stream as specified
by the record selection expression. If at least one record in
the stream matches the record selection expression, Oracle Rdb
uses the values stored in the first record of the record stream
to evaluate the value expression. If there are no matches, you
receive a runtime error.
The FIRST FROM value expression can perform the equivalent of a
table lookup when you are sure that the value you want to find is
unique in a relation.
7.24.1 – Examples
The following programs demonstrate the use of the FIRST FROM
clause. The programs find the first record in the JOBS relation
with the value "Company President" in the field JOB_TITLE. Using
this record's value for JOB_CODE, these programs create a record
stream containing the records in the CURRENT_JOB relation that
have this same job code. The programs print the value that the
first record from this record stream holds in the LAST_NAME
field.
7.24.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
DECLARE_VARIABLE name SAME AS PERS.CURRENT_JOB.LAST_NAME;
main()
{
READY PERS;
START_TRANSACTION READ_ONLY;
GET
name = FIRST C.LAST_NAME FROM C IN CURRENT_JOB
WITH C.JOB_CODE = FIRST J.JOB_CODE FROM J IN JOBS
WITH J.JOB_TITLE = "Company President"
SORTED BY C.JOB_CODE;
END_GET;
printf ("Last name is %s", name);
COMMIT;
FINISH;
}
7.24.1.2 – Pascal Example
program first_val (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
DECLARE_VARIABLE name SAME AS PERS.CURRENT_JOB.LAST_NAME;
begin
READY PERS;
START_TRANSACTION READ_ONLY;
GET
name = FIRST C.LAST_NAME FROM C IN CURRENT_JOB
WITH C.JOB_CODE = FIRST J.JOB_CODE FROM J IN JOBS
WITH J.JOB_TITLE = 'Company President'
SORTED C.JOB_CODE;
END_GET;
writeln ('Last name is: ', name);
COMMIT;
FINISH;
end.
7.24.2 – Format
(B)0[mfirst-from-expr =
qqq> [4mFIRST[m qwqqqqqqqqq>qqqqqqqqqqwq> value-expr qqq> [4mFROM[m qqqk
mq> handle-options qqj x
x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
x
mqqqqqq> rse qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq>
(B)0[mhandle-options =
q> ( qwqqqqqq> [4mREQUEST_HANDLE[m qqqqq> var qqqqqqqqwq> ) q>
tqqqqqq> [4mTRANSACTION_HANDLE[m qqq> var qqqqqqqqu
mqqqqqq> [4mREQUEST_HANDLE[m q> var q> , qqqqqqqk x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x
mqqqq> [4mTRANSACTION_HANDLE[m q> var qqqqqqqqqqqj
7.24.2.1 – Format arguments
value-expr A value expression. A symbol or a string
of symbols used to calculate a value. When
you use a value expression in a statement,
Oracle Rdb calculates the value associated
with the expression and uses that value
when executing the statement.
rse A record selection expression. A phrase
that defines specific conditions that
individual records must meet before
Oracle Rdb includes them in a record stream.
handle-options A request handle, a transaction handle, or
both.
REQUEST_HANDLE var The REQUEST_HANDLE keyword followed by a
host language variable. A request handle
identifies a compiled Oracle Rdb request.
If you do not supply a request handle
explicitly, RDML associates a unique
request handle for the compiled request.
TRANSACTION_HANDLE The TRANSACTION_HANDLE keyword followed by
var a host language variable. A transaction
handle identifies a transaction. If
you do not supply a transaction handle
explicitly, RDML uses the default
transaction handle.
7.25 – FOR Statement
The FOR statement executes a statement or group of statements
once for each record in a record stream formed by a record
selection expression. You can nest FOR statements within other
FOR statements to establish relationships for outer joins.
Your program can use either FOR statements or START_STREAM
statements to establish record streams. You can use both methods
in one program. However, you cannot use the FETCH statement
to advance the pointer in a record stream established by a FOR
statement. The FOR statement automatically advances to the next
record.
7.25.1 – Examples
The following programs demonstrate the use of the FOR statement
to create a record stream. These programs:
o Declare a variable dept_code
o Prompt for a value for dept_code
o Start a READ_ONLY transaction
o Create a record stream defined by a record selection
expression that uses the value of dept_code
o Display the department name for each record in that stream
The C program uses the read_string function to prompt for and
receive a value for dept_code. For more information on this
function, see Appendix B of the "RDML Reference Manual".
7.25.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
extern void read_string ();
DECLARE_VARIABLE dept_code SAME AS DEPARTMENTS.DEPARTMENT_CODE;
main ()
{
read_string ("Department Code: ",dept_code, sizeof(dept_code));
READY PERS;
START_TRANSACTION READ_ONLY;
FOR D IN DEPARTMENTS
WITH D.DEPARTMENT_CODE = dept_code
printf ("Department name = %s\n ", D.DEPARTMENT_NAME);
END_FOR;
COMMIT;
FINISH;
}
7.25.1.2 – Pascal Example
program for_in_rse (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
var
DECLARE_VARIABLE dept_code SAME AS DEPARTMENTS.DEPARTMENT_CODE;
begin
write ('Department Code: ');
readln (dept_code);
READY PERS;
START_TRANSACTION READ_ONLY;
FOR D IN DEPARTMENTS
WITH D.DEPARTMENT_CODE = dept_code
writeln ('Department name = ', D.DEPARTMENT_NAME);
END_FOR;
COMMIT;
FINISH;
end.
7.25.2 – Format
(B)0[m[4mFOR[m qqqqqqqqqwqq>qqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqk
mqq> handle-options qqqj x
x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
x
mqqq> rse qqwq>qqqqqqqqqqqwqqqwqq> statement qqwqq> [4mEND_FOR[m
mq>on-error qqj mqq<qqqqqqqqqqqqqqqj
(B)0[mhandle-options =
q> ( qwqqqqqq> [4mREQUEST_HANDLE[m qqqqq> var qqqqqqqqwq> ) q>
tqqqqqq> [4mTRANSACTION_HANDLE[m qqq> var qqqqqqqqu
mqqqqqq> [4mREQUEST_HANDLE[m q> var q> , qqqqqqqk x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x
mqqqq> [4mTRANSACTION_HANDLE[m q> var qqqqqqqqqqqj
7.25.2.1 – Format arguments
rse A record selection expression. A phrase
that defines specific conditions that
individual records must meet before
Oracle Rdb includes them in a record stream.
on-error The ON ERROR clause. Specifies host
language statement(s) to be performed
if an error occurs during the FOR loop.
For more information see the entry on ON
ERROR.
statement Any valid RDML or host language statement
to be executed within the FOR loop. Use
a semicolon (;) at the end of each RDML,
Pascal, or C statement.
handle-options A request handle, a transaction handle, or
both.
REQUEST_HANDLE var The REQUEST_HANDLE keyword followed by a
host language variable. A request handle
identifies a compiled Oracle Rdb request.
If you do not supply a request handle
explicitly, RDML associates a unique
request handle for the compiled request.
TRANSACTION_HANDLE The TRANSACTION_HANDLE keyword followed by
var a host language variable. A transaction
handle identifies a transaction. If
you do not supply a transaction handle
explicitly, RDML uses the default
transaction handle.
7.26 – FOR Segmented Strings
Sets up a record stream consisting of segments from a segmented
string field. Because a single segmented string field value is
made up of multiple segments, a record stream that includes
a segmented string field is nested. The outer loop retrieves
records that include the segmented string field and the inner
loop retrieves the segments from this field, one at a time.
Therefore, a FOR statement that retrieves segmented strings must
be nested within a START_STREAM or FOR statement.
7.26.1 – Examples
The following programs demonstrate the use of the FOR statement
to retrieve segmented strings. Since the PERSONNEL database does
not have any segmented strings stored, the programs first store
three strings in the RESUME field of the RESUMES relation (for
more information on storing segmented strings, see the help entry
on STORE Statement with segmented strings). The programs retrieve
the segmented strings using a nested FOR statement. The outer FOR
statement selects a record based on EMPLOYEE_ID. The inner FOR
statement prints each segmented string stored in RESUME for the
selected employee.
7.26.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
main()
{
int line;
char *document[3];
document[0] = "first line of resume ";
document[1] = "second line of resume ";
document[2] = "last line of resume ";
READY PERS;
START_TRANSACTION READ_WRITE;
STORE R IN RESUMES USING
strcpy (R.EMPLOYEE_ID,"12345");
for (line = 0; line <= 2; line++)
STORE SEG IN R.RESUME
strcpy(SEG.VALUE,document[line]);
SEG.LENGTH = strlen(SEG.VALUE);
END_STORE;
END_STORE;
FOR R IN RESUMES WITH R.EMPLOYEE_ID = "12345"
FOR SEG IN R.RESUME
printf("%s\n",SEG.VALUE);
END_FOR;
END_FOR;
COMMIT;
FINISH;
}
7.26.1.2 – Pascal Example
program segstr (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
type lines = varying [80] of char;
var linecnt : integer;
document : array [0..2] of lines;
begin
document[0] := 'first line of resume ';
document[1] := 'second line of resume ';
document[2] := 'last line of resume ';
READY PERS;
START_TRANSACTION READ_WRITE;
STORE R IN RESUMES USING
R.EMPLOYEE_ID:= '12345';
for linecnt := 0 to 2 do
STORE SEG IN R.RESUME
SEG := document[linecnt];
SEG.LENGTH := length(document[linecnt]);
END_STORE;
END_STORE;
FOR R IN RESUMES WITH R.EMPLOYEE_ID = '12345'
FOR SEG IN R.RESUME
writeln (SEG);
END_FOR;
END_FOR;
COMMIT;
FINISH;
end.
7.26.2 – Format
(B)0[m[4mFOR[m qq> ss-handle qqq> [4mIN[m qqqq> ss-field qqqqqwq>qqqqqqqqqqqqwqk
mq> on-error qqj x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqqqq> assignment qqqqqqqqq> [4mEND_FOR[m
7.26.2.1 – Format arguments
ss-handle A name that identifies the segmented
string.
ss-field A qualified field name that refers to a
field defined with the SEGMENTED STRING
data type. Note that this field name, like
all field names in a FOR statement, must
be qualified by its own context variable.
This second context variable must match
the variable declared in the outer FOR
statement. See the Examples entry.
on-error The ON ERROR clause. Specifies host
language statement(s) to be performed
if an error occurs during the FOR loop.
For more information see the entry on ON
ERROR.
assignment Associates the two database variables with
a value expression. The database variables
refer to the segment of a segmented string
and its length. The special name for
the segment can be either "VALUE" or
"RDB$VALUE". The special name for the
segment length can be either "LENGTH"
or "RDB$LENGTH". You cannot assign any
other database variables to the value
expressions for segmented strings. The
assignment operator for RDML Pascal is
":=" and for RDML C is "=" or strcpy.
7.27 – GET
Assigns values from data records in a record stream to host
variables in RDML programs. You can use the GET statement in
three different ways:
o When you establish a record stream with the FOR or
START_STREAM statement, you use the GET statement to assign
values from the current record in the stream to variables in
your program. In the case of the START_STREAM statement, you
also need a FETCH statement to indicate the current record in
the stream.
o You can use GET within a STORE operation to retrieve the value
of the record currently being stored. This includes the use of
GET ... RDB$DB_KEY in a STORE ... END_STORE block to retrieve
the database key (dbkey) of a record just stored.
o You can also use the GET statement alone, without a FOR,
FETCH, or STORE statement to retrieve the result of a
statistical expression. The record stream is formed by the
record selection expression within the statistical expression.
7.27.1 – Examples
The following examples demonstrate the use of the GET statement
with a statistical function. The examples store the value of the
statistical function in the host language variable maxi, then
print this value.
7.27.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
DECLARE_VARIABLE maxi SAME AS PERS.CURRENT_INFO.SALARY;
main()
{
READY PERS;
START_TRANSACTION READ_ONLY;
GET
maxi = MAX CI.SALARY OF CI IN CURRENT_INFO;
END_GET;
printf ("%f",maxi);
COMMIT;
FINISH;
}
7.27.1.2 – Pascal Example
program max_function (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
DECLARE_VARIABLE maxi SAME AS PERS.CURRENT_INFO.SALARY;
begin
READY PERS;
START_TRANSACTION READ_ONLY;
GET
maxi = MAX CI.SALARY OF CI IN CURRENT_INFO;
END_GET;
writeln (maxi:10:2);
COMMIT;
FINISH;
end.
7.27.2 – Format
(B)0[mget-statement =
[4mGET[m qqwqqqq>qqqqqqqqwqqwq> get-item qqwqqqqq> [4mEND_GET[m qqq>
mq> on-error qj mqqqqq ; <qqqqqqj
(B)0[mget-item =
qqqqwqq> host-var qqqqqqqq> = qqqqq> value-expr qqqqqqqqqqwqqqq>
tqq> record-descr qqqq> = qqqqq> context-var.* qqqqqqqu
mqq> host-var qqqqqqqq> = qqqqq> statistical-expr qqqqj
7.27.2.1 – Format arguments
on-error The ON ERROR clause. Specifies host
language or RDML statement(s) to be
performed if an error occurs during the
GET operation. For more information see
the entry on ON ERROR.
get-item The GET statement includes an assignment
statement specifying a host variable
and a database value. The database
value is assigned to the host variable
from the Oracle Rdb value expression or
statistical expression. Note that the
GET statement must be the last statement
before END_STORE when it is used in a
STORE ... END_STORE block.
host-var A valid variable name declared in the host
program.
value-expr A valid RDML value expression. The value
expression can include the "<context-
variable>.RDB$DB_KEY" expression.
record-descr A valid host language record descriptor
that contains an entry for each field in
the relation. Each field of the record
descriptor must match exactly the field
names and data types of the fields in the
Oracle Rdb relation referenced by the context
variable.
context-var A context variable. A temporary name that
you associate with a relation. You define
a context variable in a relation clause.
For more information see the entry on
Context Variables.
statistical-expr A statistical expression; calculates
values based on a value expression for
every record in the record stream.
7.28 – Host Variable
Use a host variable value expression to pass data between a
calling program and Oracle Rdb. A host variable is a program
variable.
7.28.1 – Examples
The following programs demonstrate the use of the
DECLARE_VARIABLE clause to declare a program variable. These
programs:
o Declare the variable, badge, to have the same data type and
size attributes as EMPLOYEE_ID in the EMPLOYEES relation.
o Use this variable for interactive processing. Note that the
interactive portion of the programs appear before the READY
statement. This keeps locks on the database to a minimum.
o Select the record from the EMPLOYEES relation that has the
same value for EMPLOYEE_ID as is stored in badge.
o Modify the STATUS_CODE field of this record.
Note that the C program uses the read_string function to prompt
for and receive a value for badge. For more information on this
function see Appendix B of the "RDML Reference Manual".
7.28.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
extern void read_string();
static DECLARE_VARIABLE badge SAME AS EMPLOYEES.EMPLOYEE_ID;
main()
{
read_string ("Employee ID: ", badge, sizeof(badge));
READY PERS;
START_TRANSACTION READ_WRITE;
FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID = badge
MODIFY E USING
strcpy(E.STATUS_CODE,"1");
END_MODIFY;
END_FOR;
ROLLBACK;
FINISH;
}
7.28.1.2 – Pascal Example
program modify_with_host (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
var
DECLARE_VARIABLE badge SAME AS EMPLOYEES.EMPLOYEE_ID;
begin
write ('Employee ID: ');
readln (badge);
READY PERS;
START_TRANSACTION READ_WRITE;
FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID = badge
MODIFY E USING
E.STATUS_CODE := '1';
END_MODIFY;
END_FOR;
ROLLBACK;
FINISH;
end.
7.28.2 – Format
(B)0[mC-host-variable =
VMS-name qwqqqqqqwwwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwwq>
mq> * qjxtqqq> . qqq> field-identifier qqqqqux
xx xx
xtqqq> [ qwq> expression qwqq> ] qqqux
xx mqqqqqqq , <qqqqqj xx
xx xx
xmqqq> "->" qqqq> field-identifier qqqqjx
mqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqj
The C pointer operator is shown in quotes to distinguish it from
the arrows that show the logical flow of the syntax. Do not use
quotes around the pointer operator in your programs.
(B)0[mPascal-host-variable =
VMS-name qwqqwqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqwqqwqqq>
x tqqq> . qqq> field-identifier qqqqqqqqu x
x x x x
x tqqq> [ qwq> expression qqqwqq> ] qqu x
x x mqqqqqqqq , <qqqqqj x x
x x x x
x mqqq> ^ qwqqqqqqqqqqqqq>qqqqqqqqqwqqqqqqj x
x mq> field-identifier qqj x
x x
mqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqj
7.28.2.1 – Format arguments
VMS-name A valid OpenVMS name.
field-indentifier A valid host language field identifier.
expression An expression that evaluates to a valid
host language array element.
"->" The C pointer symbol. It is shown in
quotes to distinguish it from the arrows
that show the logical flow of the syntax.
Do not use quotes around the pointer
symbol in your program.
7.29 – MATCHING
Lets you use the asterisk (*) and percent (%) pattern matching
characters in combination with other symbols to test for
the presence of a specified string anywhere inside a string
expression.
You do not have to use the pattern matching characters if the
string you specify matches the data stored in the database
exactly. (If the database field is defined as TEXT 10, the string
you specify must be exactly ten characters.)
Records are included in the record stream if the string specified
by match expression is found within the string specified by the
value expression. The character * matches any string that maps
onto its position. The character % matches any character that
maps onto its position. When MATCHING is preceded by the optional
qualifier NOT, a record is included in the stream if the pattern
string is not found within the target string.
MATCHING is not case sensitive; it considers uppercase and
lowercase forms of the same character to be a match.
7.29.1 – Examples
The following programs demonstrate the use of the MATCHING
conditional expression and the SORTED clause. These programs
create a record stream containing all the records in the
EMPLOYEES relation in which the field LAST_NAME begins with the
letter "R". Then the programs sort the record stream in ascending
numerical order of the employee IDS. These programs print, in
numerical order, the employee ID, followed by the last name and
first name for all the records in the record stream.
7.29.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
DECLARE_VARIABLE match_string SAME AS EMPLOYEES.LAST_NAME;
main()
{
read_string(match_string,"R*",sizeof(match_string));
READY PERS;
START_TRANSACTION READ_ONLY;
FOR E IN EMPLOYEES
WITH E.LAST_NAME MATCHING match_string
SORTED BY E.EMPLOYEE_ID
printf ("%s %s %s",E.EMPLOYEE_ID,
E.LAST_NAME,
E.FIRST_NAME);
END_FOR;
COMMIT;
FINISH;
}
7.29.1.2 – Pascal Example
program matching (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
var
match_string: VARYING [10] OF CHAR;
begin
match_string := 'R*';
READY PERS;
START_TRANSACTION READ_ONLY;
FOR E IN EMPLOYEES
WITH E.LAST_NAME MATCHING match_string
SORTED BY E.EMPLOYEE_ID
writeln (E.EMPLOYEE_ID,' ', E.LAST_NAME, E.FIRST_NAME);
END_FOR;
COMMIT;
FINISH;
end.
7.29.2 – Format
(B)0[mmatching-clause =
qqq> value-expr qqqwqq>qqqqqqqwq> [4mMATCHING[m qqq> match-expr qqqq>
mqq> [4mNOT[m qqj
7.29.2.1 – Format arguments
value-expr A value expression. A symbol or a string
of symbols used to calculate a value. When
you use a value expression in a statement,
Oracle Rdb calculates the value associated
with the expression and uses that value
when executing the statement.
match-expr A match expression. An expression in
quotation marks that is used to match a
pattern. Use the double quote character
(") in C programs. Use the single quote
character (') in Pascal programs. The
match expression can include the following
special symbols (called wildcards):
o * Matches a string of zero or more
characters that maps onto its position
o % Matches a single character that maps
onto its position
7.30 – MAX
Returns the highest value for a value expression for all records
specified by a record selection expression.
7.30.1 – Examples
The following programs demonstrate the use of the MAX function in
an assignment statement. These programs:
o Declare a host variable, latest_degree
o Use the MAX function to compute the highest number stored in
YEAR_GIVEN in the DEGREES relation
o Assign this computed value to the host variable
o Print an informational message and the value computed by the
MAX function
7.30.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
main()
{
DECLARE_VARIABLE latest_degree SAME AS DEGREES.YEAR_GIVEN;
READY PERS;
START_TRANSACTION READ_ONLY;
GET
latest_degree = MAX D.YEAR_GIVEN OF D IN DEGREES;
END_GET;
printf ("Latest Degree was awarded in: %d\n", latest_degree);
COMMIT;
FINISH;
}
7.30.1.2 – Pascal Example
program assignmax (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
var
DECLARE_VARIABLE latest_degree SAME AS DEGREES.YEAR_GIVEN;
begin
READY PERS;
START_TRANSACTION READ_ONLY;
GET
latest_degree = MAX D.YEAR_GIVEN OF D IN DEGREES;
END_GET;
writeln ('Latest Degree was awarded in: ', latest_degree);
COMMIT;
FINISH;
end.
7.30.2 – Format
(B)0[m
q> [4mMAX[m qqqqqqqqqwq>qqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqk
mq> handle-options qqj x
x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
x
mqqqqqqqq> value-expr qqqq> [4mOF[m qqqq> rse qqqqqqqqqqqqq>
(B)0[mhandle-options =
q> ( qwqqqqqq> [4mREQUEST_HANDLE[m qqqqq> var qqqqqqqqwq> ) q>
tqqqqqq> [4mTRANSACTION_HANDLE[m qqq> var qqqqqqqqu
mqqqqqq> [4mREQUEST_HANDLE[m q> var q> , qqqqqqqk x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x
mqqqq> [4mTRANSACTION_HANDLE[m q> var qqqqqqqqqqqj
7.30.2.1 – Format arguments
value-expr A value expression. A symbol or a string
of symbols used to calculate a value. When
you use a value expression in a statement,
Oracle Rdb calculates the value associated
with the expression and uses that value
when executing the statement.
rse A record selection expression. A phrase
that defines specific conditions that
individual records must meet before
Oracle Rdb includes them in a record stream.
handle-options A request handle, a transaction handle, or
both.
REQUEST_HANDLE var The REQUEST_HANDLE keyword followed by a
host language variable. A request handle
identifies a compiled Oracle Rdb request.
If you do not supply a request handle
explicitly, RDML associates a unique
request handle for the compiled request.
TRANSACTION_HANDLE The TRANSACTION_HANDLE keyword followed by
var a host language variable. A transaction
handle identifies a transaction. If
you do not supply a transaction handle
explicitly, RDML uses the default
transaction handle.
7.31 – MIN
Returns the lowest value for a value expression for all records
specified by a record selection expression.
7.31.1 – Examples
The following programs demonstrate the use of the MIN function in
an assignment statement. These programs:
o Store a literal value into all fields for a record in the JOBS
relation, except the field MINIMUM_SALARY
o Cross JOBS over itself
o Use the MIN function to compute the lowest salary in the
existing JOBS records for which the wage class is "1"
o Assign this computed value to the record currently being
stored
Note that the C program uses the pad_string function to read
in the values for the STORE statement. This function pads the
values stored in each field with the correct number of trailing
blanks to ensure that the length of the values stored match the
text size of the field. For more information on pad_string, see
Appendix B of the "RDML Reference Manual".
7.31.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
DECLARE_VARIABLE min SAME AS PERS.JOBS.MINIMUM_SALARY;
extern void pad_string();
main()
{
READY PERS;
START_TRANSACTION READ_WRITE;
GET
min = MIN J2.MINIMUM_SALARY OF J2 IN JOBS
WITH J2.WAGE_CLASS = "1";
END_GET;
STORE J IN JOBS USING
pad_string ("SWPR", J.JOB_CODE, sizeof(J.JOB_CODE));
pad_string ("1", J.WAGE_CLASS, sizeof(J.WAGE_CLASS));
pad_string ("Sweeper", J.JOB_TITLE, sizeof(J.JOB_TITLE));
J.MAXIMUM_SALARY = 10000.00;
J.MINIMUM_SALARY = min;
END_STORE;
ROLLBACK;
FINISH;
}
7.31.1.2 – Pascal Example
program store_with_min (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
DECLARE_VARIABLE mini SAME AS PERS.JOBS.MINIMUM_SALARY;
begin
READY PERS;
START_TRANSACTION READ_WRITE;
GET
mini = MIN J2.MINIMUM_SALARY OF J2 IN JOBS
WITH J2.WAGE_CLASS = '1';
END_GET;
STORE J IN JOBS USING
J.JOB_CODE := 'SWPR';
J.WAGE_CLASS := '1';
J.JOB_TITLE := 'Sweeper';
J.MINIMUM_SALARY := mini;
J.MAXIMUM_SALARY := 10000.00;
END_STORE;
ROLLBACK;
FINISH;
end.
7.31.2 – Format
(B)0[m
qq> [4mMIN[m qqqqqqqqqqqqqwqq>qqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqk
mqq> handle-options qj x
x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
x
mqqqqqqqq> value-expr qqqqqq> [4mOF[m qqqqqq> rse qqqqqqqqqq>
(B)0[mhandle-options =
q> ( qwqqqqqq> [4mREQUEST_HANDLE[m qqqqq> var qqqqqqqqwq> ) q>
tqqqqqq> [4mTRANSACTION_HANDLE[m qqq> var qqqqqqqqu
mqqqqqq> [4mREQUEST_HANDLE[m q> var q> , qqqqqqqk x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x
mqqqq> [4mTRANSACTION_HANDLE[m q> var qqqqqqqqqqqj
7.31.2.1 – Format arguments
value-expr A value expression. A symbol or a string
of symbols used to calculate a value. When
you use a value expression in a statement,
Oracle Rdb calculates the value associated
with the expression and uses that value
when executing the statement.
rse A record selection expression. A phrase
that defines specific conditions that
individual records must meet before
Oracle Rdb includes them in a record stream.
handle-options A request handle, a transaction handle, or
both.
REQUEST_HANDLE var The REQUEST_HANDLE keyword followed by a
host language variable. A request handle
identifies a compiled Oracle Rdb request.
If you do not supply a request handle
explicitly, RDML associates a unique
request handle for the compiled request.
TRANSACTION_HANDLE The TRANSACTION_HANDLE keyword followed by
var a host language variable. A transaction
handle identifies a transaction. If
you do not supply a transaction handle
explicitly, RDML uses the default
transaction handle.
7.32 – MISSING
Tests for the absence of a field value. A conditional expression
that includes MISSING is true if the value specified by the
dbfield-expression is missing. If you precede the MISSING
expression with the optional NOT qualifier, the condition is
true if the field contains a value.
7.32.1 – Examples
The following programs demonstrate the use of the MISSING and
NOT MISSING conditional expressions. These programs form a record
stream containing the records in the COLLEGES relation that have
nothing stored in the field STATE, but do have a college code
stored in the field COLLEGE_CODE. Each record in the COLLEGES
relation is tested for the above condition; if a record meets the
condition these programs print an informational message and the
college code of the newest record added to the record stream.
7.32.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
main()
{
READY PERS;
START_TRANSACTION READ_WRITE;
FOR C IN COLLEGES
WITH C.STATE MISSING
AND C.COLLEGE_CODE NOT MISSING;
printf ("State Missing for COLLEGE: %s\n", C.COLLEGE_CODE);
END_FOR;
COMMIT;
FINISH;
}
7.32.1.2 – Pascal Example
program missing (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
begin
READY PERS;
START_TRANSACTION READ_WRITE;
FOR C IN COLLEGES
WITH C.STATE MISSING
AND C.COLLEGE_CODE NOT MISSING;
writeln ('State Missing for COLLEGE:', C.COLLEGE_CODE);
END_FOR;
COMMIT;
FINISH;
end.
7.32.2 – Format
(B)0[mmissing-cond-expr
qqq> db-field-expr qqq>qqqqwqq>qqqqqqqwqq> [4mMISSING[m qqq>
mqq> [4mNOT[m qqj
7.32.2.1 – Format arguments
db-field-expr A database field value expression. A field
name qualified with a context variable.
7.33 – MODIFY
Changes the value in a field or fields in one or more records
from a relation in an open stream.
Before you use a MODIFY statement, you must:
o Start a READ_WRITE transaction
o Establish a record stream with a FOR statement or START_STREAM
statement
The context variables referenced in a MODIFY statement must be
the same as those defined in the FOR or START_STREAM statement.
7.33.1 – Examples
The following programs demonstrate the use of the MODIFY
statement with a host variable. These programs:
o Declare a host variable, badge, with the same data type and
attributes as EMPLOYEES.EMPLOYEE_ID
o Prompt for a value for badge
o Change the status code for the employee with the specified
badge
The C program uses the read_string function to prompt for and
receive a value for badge. For more information on read_string,
see Appendix B of the "RDML Reference Manual".
7.33.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
extern void read_string();
static DECLARE_VARIABLE badge SAME AS EMPLOYEES.EMPLOYEE_ID;
main()
{
read_string ("Employee ID: ", badge, sizeof(badge));
READY PERS;
START_TRANSACTION READ_WRITE;
FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID = badge
MODIFY E USING
strcpy(E.STATUS_CODE,"1");
END_MODIFY;
END_FOR;
ROLLBACK;
FINISH;
}
7.33.1.2 – Pascal Example
program modify_with_host (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
var
DECLARE_VARIABLE badge SAME AS EMPLOYEES.EMPLOYEE_ID;
begin
write ('Employee ID: ');
readln (badge);
READY PERS;
START_TRANSACTION READ_WRITE;
FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID = badge
MODIFY E USING
E.STATUS_CODE := '1';
END_MODIFY;
END_FOR;
ROLLBACK;
FINISH;
end.
7.33.2 – Format
(B)0[m[4mMODIFY[m qqq> context-var qqqqq> [4mUSING[m qqqqwqqqqqqqqqqqqqqwqqqqk
mqq> on-error qj x
x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
x
mqwqwqq> statement qqqqqqqqqqqqqqqqqqqqqqqqqqqwqwq> [4mEND_MODIFY[m
x x x x
x mqq> context-var.* qq> = qq> record-descr qqj x
x x
mqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqj
7.33.2.1 – Format arguments
context-var A context variable. A temporary name that
you associate with a relation. You define
a context variable in a relation clause.
For more information see the entry on
Context Variables.
on-error The ON ERROR clause. Specifies host
language statement(s) to be performed
if an error occurs during the MODIFY
operation. For more information see the
entry on ON ERROR.
statement Any valid RDML or host language statement
to be executed within the MODIFY
operation. Use a semicolon (;) at the
end of each RDML, Pascal, or C statement.
record-descr A valid host language record descriptor
that matches all the fields of the
relation. Each field of the record
descriptor must match exactly the field
names and data types of the fields in the
Oracle Rdb relation referenced by the
context variable.
7.34 – ON_ERROR
The ON ERROR clause specifies the statement(s) the host
language performs if an error occurs during the execution of
the associated RDML statement.
You can use the ON ERROR clause in all RDML statements except the
DATABASE statement.
7.34.1 – Examples
The following programs demonstrate the use of the ON ERROR
clause to trap lock errors that occur during execution of the
READY statement. The programs start a transaction using the
NOWAIT option. This means that execution of the READY statement
causes a lock error if anyone else has a lock on the EMPLOYEES
relation when you run the programs. In this case, the programs
will print the message "database unavailable right now". The
programs will try to access the database up to 100 more times
before terminating the programs.
If the error is not a lock error, the programs print the message
"Unexpected Error, Application Terminating".
To illustrate this application, build it, and then run it
simultaneously from two different terminals.
7.34.1.1 – C Example
globalvalue RDB$_LOCK_CONFLICT;
globalvalue RDB$_DEADLOCK;
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
void handle_error()
{
if (RDB$STATUS == RDB$_LOCK_CONFLICT)
printf("database unavailable right now\n");
else
{
printf("Unexpected Error, Application Terminating\n");
RDML$SIGNAL_ERROR(RDB$MESSAGE_VECTOR);
}
return;
}
void access_employees()
{
READY PERS
ON ERROR
handle_error();
return;
END_ERROR;
START_TRANSACTION READ_WRITE NOWAIT
RESERVING EMPLOYEES FOR EXCLUSIVE WRITE
ON ERROR
handle_error();
return;
END_ERROR;
/* perform some read_write operation on the EMPLOYEES relation */
printf ("Accessing EMPLOYEES...\n");
COMMIT;
FINISH;
}
main()
{
int i;
for (i=0; i<=100; i++)
access_employees();
}
7.34.1.2 – Pascal Example
program onerror (output);
DATABASE PERS = FILENAME 'PERSONNEL';
var
RDB$_LOCK_CONFLICT : [value,external] integer;
i : integer;
error : boolean;
procedure handle_error;
begin
if RDB$STATUS = RDB$_LOCK_CONFLICT
then
writeln ('database unavailable right now')
else
begin
writeln ('Unexpected Error, Application Terminating');
RDML$SIGNAL_ERROR(RDB$MESSAGE_VECTOR)
end;
end;
begin
for i := 1 to 100 do
begin
error := FALSE;
READY PERS;
START_TRANSACTION READ_WRITE NOWAIT
RESERVING EMPLOYEES FOR EXCLUSIVE WRITE
ON ERROR
handle_error;
error := TRUE;
END_ERROR;
if not error then
begin
{perform some read_write operation on the EMPLOYEES relation}
writeln ('Accessing EMPLOYEES...');
COMMIT;
FINISH;
end;
end;
end.
7.34.2 – Format
(B)0[mon-error =
[4mON[m [4mERROR[m qqwqqq> statement qqqqwqqq> [4mEND_ERROR[m
mqqqqqqqqq<qqqqqqqqqj
7.34.2.1 – Format arguments
statement Any valid RDML or host language statement
to be executed when an RDML error occurs.
Use a semicolon (;) at the end of each
RDML, Pascal, or C statement.
7.35 – RDB$DB_KEY
RDB$DB_KEY lets you retrieve a specific record from the database
using a logical pointer called a database key (dbkey). A dbkey
indicates a specific record in the database. You can retrieve
this key as though it were a field in the record. Once you
have retrieved the database key, you can use it to retrieve
its associated record directly, as part of a record selection
expression. The database key gives you the ability to keep
track of a subset of records in the database and retrieve them
repeatedly, without using data manipulation syntax.
7.35.1 – Examples
The following programs demonstrate the use of the RDB$DB_KEY
value expression in a record selection expression. The programs
sort the EMPLOYEES relation in ascending order of employee ID.
Then, using the first 100 records from the sorted EMPLOYEES
relation, the programs build two arrays: rdb_key_array and rdb_
name_array. In building these arrays within a FOR statement,
these programs create a one-to-one correspondence between the
elements in the rdb_key_array and the rdb_name_array. Each time a
new element is added to each of these arrays the next EMPLOYEES
record from the sorted stream is printed.
This one-to-one correspondence allows the programs to step
through the EMPLOYEES records indirectly. This is demonstrated in
the second FOR statement. The second FOR statement loops through
the rdb_key_array in reverse order; each time the address of
an array element in rdb_key_array is incremented, an EMPLOYEES
record is accessed (also in reverse sorted order) and the
employee's last name is printed.
7.35.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
main()
{
DECLARE_VARIABLE rdb_key_array[100] SAME AS EMPLOYEES.RDB$DB_KEY;
DECLARE_VARIABLE rdb_name_array[100] SAME AS EMPLOYEES.LAST_NAME;
int cnt = 0;
READY PERS;
START_TRANSACTION READ_ONLY;
FOR FIRST 100 E IN EMPLOYEES SORTED BY E.EMPLOYEE_ID
rdb_key_array[cnt] = E.RDB$DB_KEY;
strcpy (rdb_name_array[cnt], E.LAST_NAME);
printf("%s - 1st pass\n", E.LAST_NAME);
++cnt;
END_FOR;
for ( cnt = --cnt; cnt >= 0; --cnt)
FOR E IN EMPLOYEES
WITH E.RDB$DB_KEY = rdb_key_array[cnt]
if ( strcmp( E.LAST_NAME, rdb_name_array[cnt]) != 0 )
printf("%s DOES NOT MATCH %s\n",
E.LAST_NAME, rdb_name_array[cnt]);
else printf("%s - 2nd pass\n", E.LAST_NAME);
END_FOR;
COMMIT;
FINISH;
}
7.35.1.2 – Pascal Example
program db_key (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
type
Rdb_Key_Type = BASED ON EMPLOYEES.RDB$DB_KEY;
Rdb_Name_Type = BASED ON EMPLOYEES.LAST_NAME;
var
Rdb_Key_Array : ARRAY [1..101] OF Rdb_Key_Type;
Rdb_Name_Array : ARRAY [1..101] OF Rdb_Name_Type;
Cnt : INTEGER := 1;
begin
READY PERS;
START_TRANSACTION READ_ONLY;
FOR FIRST 100 E IN EMPLOYEES SORTED BY E.EMPLOYEE_ID
Rdb_Key_Array[Cnt] := E.RDB$DB_KEY;
Rdb_Name_Array[Cnt] := E.LAST_NAME;
WRITELN(E.LAST_NAME, ' - 1st pass');
Cnt := Cnt + 1;
END_FOR;
for Cnt := Cnt - 1 downto 1 do
FOR E IN EMPLOYEES
WITH E.RDB$DB_KEY = Rdb_Key_array[Cnt]
if E.LAST_NAME <> Rdb_Name_Array[Cnt]
then
writeln (E.LAST_NAME, 'DOES NOT MATCH',
Rdb_Name_Array[Cnt])
else
writeln (E.LAST_NAME, ' - 2nd pass');
END_FOR;
COMMIT;
FINISH;
end.
7.35.2 – Format
(B)0[mdb-key =
qqq> context-var qqq> . qqq> [4mRDB$DB_KEY[m qq>
7.35.2.1 – Format arguments
context-var A context variable. A temporary name that
you associate with a relation. You define
a context variable in a relation clause.
For more information see the entry on
Context Variables.
7.36 – RDB$MISSING
Lets you store the missing value for a field with the STORE
statement or the MODIFY statement. When a field is referred to
as missing, you can think of the field as empty, a field in which
no value is stored.
In order to use RDB$MISSING, you must have previously defined
a missing value for the field when you defined the database.
If a field is left blank, or you use RDB$MISSING without having
defined a missing value for that field in its field definition,
RDML returns an error.
7.36.1 – Format
(B)0[mmissing-value =
qqq> [4mRDB$MISSING[m qqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqk
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
x
mq> ( qwq> context-var . field-name qqqqqqqqqqqqqqqwqq> ) qqq>
tq> relation-name . field-name qqqqqqqqqqqqqu
mq> db-handle . relation-name . field-name qj
7.36.1.1 – Format arguments
context-var A context variable. A temporary name that
you associate with a relation. You define
a context variable in a relation clause.
For more information see the entry on
Context Variables.
field-name The name of a field in a relation. For
example, once you have defined E as
the context variable for the EMPLOYEES
relation, E.LAST_NAME is a value
expression that refers to a value from
the LAST_NAME field of EMPLOYEES.
relation-name The name of a relation in the database.
db-handle Database handle. A host variable used
to refer to a specific database you have
invoked. For more information see the
entry on the Database Handle clause.
7.36.2 – Examples
The following programs demonstrate the use of the RDB$MISSING
value expression with the STORE clause. The programs store the
specified values for the fields in the DEGREES relation. In these
programs, a value for DEGREE_FIELD is not specified; instead, the
RDB$MISSING value expression is specified. This does not actually
assign a value to the degree field; RDML marks the DEGREE_FIELD
as empty and stores nothing in this field.
Note that the C program uses the pad_string function to read
in the values for the STORE statement. This function pads the
values stored in each field with the correct number of trailing
blanks to ensure that the length of the values stored match the
text size of the field. For more information on pad_string, see
Appendix B of the "RDML Reference Manual".
7.36.2.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
extern void pad_string();
main()
{
READY PERS;
START_TRANSACTION READ_WRITE;
STORE D IN DEGREES USING
pad_string ("76156", D.EMPLOYEE_ID, sizeof(D.EMPLOYEE_ID));
pad_string ("HVDU" , D.COLLEGE_CODE, sizeof(D.COLLEGE_CODE));
D.YEAR_GIVEN = 1978;
pad_string ("BA", D.DEGREE, sizeof(D.DEGREE));
pad_string (RDB$MISSING(D.DEGREE_FIELD),D.DEGREE_FIELD,
sizeof(D.DEGREE_FIELD));
END_STORE;
ROLLBACK;
FINISH;
}
7.36.2.2 – Pascal Example
program store_missing (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
begin
READY PERS;
START_TRANSACTION READ_WRITE;
STORE D IN DEGREES USING
D.EMPLOYEE_ID := '76156';
D.COLLEGE_CODE := 'HVDU';
D.YEAR_GIVEN := 1978;
D.DEGREE := 'BA';
D.DEGREE_FIELD := RDB$MISSING(D.DEGREE_FIELD);
END_STORE;
ROLLBACK;
FINISH;
end.
7.37 – READY
Explicitly declares your intention to access one or more
databases and causes an attach to the database.
7.37.1 – Examples
The following program fragments demonstrate the use of the READY
statement to open a database. These program fragments:
o Use the DATABASE statement to declare the PERSONNEL database
o Declare a database handle PERS for PERSONNEL
o Open the PERSONNEL database with the READY statement
7.37.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
.
.
.
main ()
{
READY PERS;
.
.
.
FINISH PERS;
}
7.37.1.2 – Pascal Example
program empupdate;
DATABASE PERS = FILENAME 'PERSONNEL';
.
.
.
begin
READY PERS;
.
.
.
FINISH PERS;
end.
7.37.2 – Format
(B)0[m[4mREADY[m qqqwqqqq>qqqqqqqqqqqqqqwqqqwqqqq>qqqqqqqqqqqqqwqqqqq>
mqwqq> db-handle qwqj mqqqq> on-error qqqj
mqqqqqq , <qqqqqj
7.37.2.1 – Format arguments
db-handle Database handle. A host variable used
to refer to a specific database you have
invoked. For more information see the
entry on the Database Handle clause.
on-error The ON ERROR clause. Specifies host
language statement(s) to be executed if an
error occurs during the READY operation.
For more information see the entry on ON
ERROR.
7.38 – REDUCED_TO
The REDUCED TO clause lets you eliminate duplicate values for
fields in a record stream. You can use this expression to
eliminate redundancy in the results of a query and to group
records in a relation according to unique field values.
7.38.1 – Examples
The following programs demonstrate the use of the REDUCED TO
clause and the SORTED clause with a single relation. These
programs sort the records in the EMPLOYEES relation on the basis
of STATE. The REDUCED TO clause limits the record stream so that
each record in the stream has a different value for the field
STATE. The programs then display the list of states represented
in the EMPLOYEES relation.
7.38.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
main ()
{
READY PERS;
START_TRANSACTION READ_ONLY;
FOR E IN EMPLOYEES
REDUCED TO E.STATE
SORTED BY E.STATE
printf("%s\n", E.STATE);
END_FOR;
COMMIT;
FINISH;
}
7.38.1.2 – Pascal Example
program reduced_one_rel (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
begin
READY PERS;
START_TRANSACTION READ_ONLY;
FOR E IN EMPLOYEES
REDUCED TO E.STATE
SORTED BY E.STATE
writeln (E.STATE);
END_FOR;
COMMIT;
FINISH;
end.
7.38.2 – Format
(B)0[mreduce-clause =
qqqqq> [4mREDUCED[m TO qqqwqqqqqq> db-field qqqwqqq>
mqqqqqq<qqqq , <qqqqqj
7.38.2.1 – Format arguments
db-field A database field value expression. A
database field value expression is a field
name qualified with a context variable.
The database field must be defined in
the current context of the statement.
For example, the database field must be
defined in the context of the START_STREAM
or DECLARE_STREAM statements or the FOR
loop. See the DB_Field help entry for more
information.
7.39 – Relation Clause
Lets you declare a context variable for a stream or a loop.
Once you have associated a context variable with a relation,
you can use only that context variable to refer to records from
that relation in the record stream you created. Each relation
(including multiple uses of the same relation) in the record
stream must have a unique context variable. For more information
see the entry on Context Variables.
7.39.1 – Examples
The following programs demonstrate the use of the relation clause
with a FOR loop. These programs declare a context variable E for
EMPLOYEES. This allows the programs to reference records from the
EMPLOYEES relation by using the variable E in the host language
print statements.
7.39.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
main()
{
READY PERS;
START_TRANSACTION READ_ONLY;
FOR E IN EMPLOYEES
printf ("%s %s %s\n", E.LAST_NAME,
E.EMPLOYEE_ID,
E.SEX);
END_FOR;
COMMIT;
FINISH;
}
7.39.1.2 – Pascal Example
program context_variable (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
begin
READY PERS;
START_TRANSACTION READ_ONLY;
FOR E IN EMPLOYEES
writeln (E.LAST_NAME, ' ', E.EMPLOYEE_ID, ' ', E.SEX);
END_FOR;
COMMIT;
FINISH;
end.
7.39.2 – Format
(B)0[mrelation-clause =
qq> context-var qqq> [4mIN[m qqwqq>qqqqqqqqqqqqqqqqqqwqq> relation-name qq>
mqq> db-handle qq> . qj
7.39.2.1 – Format arguments
context-var A context variable. A temporary name that
you associate with a relation. You define
a context variable in a relation clause.
For more information see the entry on
Context Variables.
db-handle Database handle. A host variable used
to refer to a specific database you have
invoked. For more information see the
entry on the Database Handle clause.
relation-name The name of a relation in the database.
7.40 – Rel Operators
Compare value expressions. Relational operators are used in
conditional expressions. See the table in the list of subtopics
for a summary of the RDML relational operators.
7.40.1 – Table
Relational Value
Operator
-------------------------------------------------------------
EQ = True if the two value expressions are
equal.
NE <> True if the two value expressions are
not equal.
GT > True if the first value expression is
greater than the second.
GE >= True if the first value expression is
greater than or equal to the second.
LT < True if the first value expression is
less than the second.
LE <= True if the first value expression is
less than or equal to the second.
-------------------------------------------------------------
Note: In all cases, if either value expression is
missing, the value of the condition is missing.
7.40.2 – Examples
The following programs demonstrate the use of the LE (less than
or equal to) operator in a FOR statement. The programs create a
record stream of all those employees who have an employee ID less
than or equal to 00400. The programs then print the employee IDs
from the records in the record stream.
7.40.2.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
main()
{
READY PERS;
START_TRANSACTION READ_WRITE;
FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID LE "00400"
printf ("%s\n", E.EMPLOYEE_ID);
END_FOR;
COMMIT;
FINISH;
}
7.40.2.2 – Pascal Example
program relation (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
begin
READY PERS;
START_TRANSACTION READ_ONLY;
FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID LE '00400'
writeln (E.EMPLOYEE_ID);
END_FOR;
COMMIT;
FINISH;
end.
7.41 – Request Handle
Identifies a compiled Oracle Rdb request. RDML generates request
handles for statements that contain record selection expressions.
In almost all cases, it is unnecessary for you to explicitly
specify request handles.
You can use a request handle in the following RDML statements:
o GET
o FOR
o START_STREAM
o STORE
o Statistical Functions (AVERAGE, COUNT, MAX, MIN, TOTAL)
7.41.1 – More
RDML allows the syntax (REQUEST_HANDLE rh) to go on each statistical
expression in the GET ... END_GET block, and there is one request per
statistical expression. For example,
GET
a = COUNT
(request_handle r1)
...;
b = MAX
(request_handle r2)
...;
END_GET
7.41.2 – Examples
The following programs demonstrate the use of the REQUEST_HANDLE
clause in a FOR statement. These programs:
o Declare the variable, REQ1, for a request handle and the local
variable, "name"
o Initialize REQ1 to zero
o Assign a value to "name"
o Start a transaction
o Use the request handle in the first FOR statement
o Assign a new value to "name"
o Use the same request handle again in the second FOR statement
By using the same request handle in the second (identical)
request the program can reuse the code generated in the first
FOR statement for the second FOR statement; this enhances overall
performance.
7.41.2.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
DECLARE_VARIABLE name SAME AS PERS.EMPLOYEES.LAST_NAME;
extern long RDB$RELEASE_REQUEST();
RDML$HANDLE_TYPE REQ1;
main()
{
REQ1 = 0;
strcpy(name,"Gray");
READY PERS;
START_TRANSACTION READ_ONLY;
FOR (REQUEST_HANDLE REQ1) E IN PERS.EMPLOYEES
WITH E.LAST_NAME = name
printf("%s\n",E.FIRST_NAME);
END_FOR;
if ((RDB$RELEASE_REQUEST(RDB$MESSAGE_VECTOR, &REQ1) & 1) == 0)
RDML$SIGNAL_ERROR(RDB$MESSAGE_VECTOR);
COMMIT;
FINISH;
}
7.41.2.2 – Pascal Example
program request (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
DECLARE_VARIABLE OF name SAME AS PERS.EMPLOYEES.LAST_NAME;
REQ1 : RDML$HANDLE_TYPE;
begin
REQ1 := 0;
name := 'Gray';
READY PERS;
START_TRANSACTION READ_ONLY;
FOR (REQUEST_HANDLE REQ1) E IN PERS.EMPLOYEES
WITH E.LAST_NAME = name
writeln (E.FIRST_NAME);
END_FOR;
if not RDB$RELEASE_REQUEST(RDB$MESSAGE_VECTOR, REQ1)
then RDML$SIGNAL_ERROR(RDB$MESSAGE_VECTOR);
COMMIT;
FINISH;
end.
7.41.3 – Format
(B)0[mrequest-handle = qq>( q> [4mREQUEST_HANDLE[m qqqqqqq> host-variable qqqqq> ) qq>
7.41.3.1 – Format arguments
host-variable A valid host language variable.
7.42 – ROLLBACK
Terminates a transaction and undoes all changes made to the
database since the program's most recent START_TRANSACTION
statement or since the start of the specified transaction.
7.42.1 – Examples
The following programs demonstrate the use of the ROLLBACK
statement with a transaction handle to undo changes to the
database made with the STORE statement. These programs:
o Start a READ_WRITE transaction, SAL_INCREASE
o Store a new JOBS record using the SAL_INCREASE transaction
o Use the ROLLBACK statement to undo the changes made to the
database during the SAL_INCREASE increase transaction, that
is, the new record is not stored in the database
Note that the C program uses the pad_string function to read
in the values for the STORE statement. This function pads the
values stored in each field with the correct number of trailing
blanks to ensure that the length of the values stored match the
text size of the field. For more information on pad_string, see
Appendix B of the "RDML Reference Manual".
7.42.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
extern void pad_string();
main()
{
int SAL_INCREASE = 0;
READY PERS;
START_TRANSACTION (TRANSACTION_HANDLE SAL_INCREASE) READ_WRITE;
STORE (TRANSACTION_HANDLE SAL_INCREASE) J IN JOBS USING
pad_string ("TYPS", J.JOB_CODE, sizeof(J.JOB_CODE));
pad_string ("1", J.WAGE_CLASS, sizeof(J.WAGE_CLASS));
pad_string ("TYPIST", J.JOB_TITLE, sizeof(J.JOB_TITLE));
J.MINIMUM_SALARY = 10000;
J.MAXIMUM_SALARY = 17000;
END_STORE;
ROLLBACK (TRANSACTION_HANDLE SAL_INCREASE);
FINISH;
}
7.42.1.2 – Pascal Example
program rollback_trans (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
var sal_increase : [volatile] integer := 0;
begin
READY PERS;
START_TRANSACTION (TRANSACTION_HANDLE SAL_INCREASE) READ_WRITE;
STORE (TRANSACTION_HANDLE SAL_INCREASE) J IN JOBS USING
J.JOB_CODE := 'TYPS';
J.WAGE_CLASS := '1';
J.JOB_TITLE := 'Typist';
J.MINIMUM_SALARY := 10000;
J.MAXIMUM_SALARY := 17000;
END_STORE;
ROLLBACK (TRANSACTION_HANDLE SAL_INCREASE);
FINISH;
end.
7.42.2 – Format
(B)0[m[4mROLLBACK[m qqwqq>qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqk
mqq> ( qq> [4mTRANSACTION_HANDLE[m qq> var qq> )qj x
x
lqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqj
x
mqqqqqqqqwqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqq>
mq> on-error qqqqj
7.42.2.1 – Format arguments
TRANSACTION_HANDLE The TRANSACTION_HANDLE keyword followed by
var a host language variable. A transaction
handle identifies a transaction. If
you do not supply a transaction handle
explicitly, RDML uses the default
transaction handle.
on-error The ON ERROR clause. Specifies host
language statement(s) to be performed
if an error occurs during the ROLLBACK
operation. For more information see the
entry on ON ERROR.
7.43 – RSE
A record selection expression (RSE) defines specific conditions
individual records must meet before Oracle Rdb includes them in a
record stream. RSE clauses let you:
o Include all records in the relation
o Eliminate duplicate records
o Limit the number of records returned
o Test for values and conditions
o Sort the records in the record stream
o Combine records from the same or different relations
The RDML rse clauses are:
o CROSS
o FIRST
o REDUCED
o SORTED
o WITH
For more information and the syntax diagrams see the HELP entry
for each of these clauses.
7.43.1 – Format
(B)0[mrse =
qqwqqqqqqqqqqqqqqqqqqqwqqq> relation-clause qqwqqqqqqqqqqqqqqqqqqwqk
mqq> first-clause qqj mq> cross-clause qqj x
x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mwqqqqqqqqqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqwqq>
mqq> with-clause qqj mqq> reduce-clause qqj mqq> sort-clause qqj
7.44 – SORTED_BY
Lets you sort the records in the record stream by the values of
specific fields. You sort on a database field value expression,
called a sort key. The sort key determines the order in which
Oracle Rdb returns the records in the record stream. The default
sorting order is ascending order.
7.44.1 – Examples
The following programs demonstrate the use of the SORT clause
using the default sort order, ascending. These programs:
o Sort the records in CURRENT_INFO using SALARY as the sort key
o Sort in ascending order because no sort order is specified
o Print the last names and salaries stored in the sorted records
7.44.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
main()
{
READY PERS;
START_TRANSACTION READ_ONLY;
FOR CI IN CURRENT_INFO
SORTED BY CI.SALARY
printf ("%s $%f\n",CI.LAST_NAME, CI.SALARY);
END_FOR;
COMMIT;
FINISH;
}
7.44.1.2 – Pascal Example
program sort_single_field (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
begin
READY PERS;
START_TRANSACTION READ_ONLY;
FOR CI IN CURRENT_INFO
SORTED BY CI.SALARY
writeln (CI.LAST_NAME, ' $', CI.SALARY :10:2);
END_FOR;
COMMIT;
FINISH;
end.
7.44.2 – Format
(B)0[msort-clause =
qqq> [4mSORTED[m [4mBY[m qqqwqwqqqqqqqqqqqqqqqqwqqqq> db-field qqqqqqwqq>
x tqq> [4mASCENDING[m qqu x
x mqq> [4mDESCENDING[m qj x
mqqqqqqqqqqqqqq , <qqqqqqqqqqqqqqqqqqqqqqj
7.44.2.1 – Format arguments
ASCENDING The default sorting order. Oracle Rdb sorts
the records in ascending order ("A"
precedes "B", 1 precedes 2, and so on).
Missing values appear as the last items
in this list of sorted values. You can
abbreviate the ASCENDING keyword to ASC.
DESCENDING Oracle Rdb sorts the records in descending
order ("A" follows "B", 1 follows 2, and
so on). Missing values appear as the first
items in this list of sorted values. You
can abbreviate the DESCENDING keyword to
DESC.
db-field A database field value expression. A
database field value expression is a field
name qualified with a context variable.
For more information see the entry on the
Database Field value expression.
7.45 – START_TRANSACTION
START_TRANSACTION initiates a transaction. All the statements
that modify records within a transaction become permanent when
the transaction is completed, or none of them do. If you end
the transaction with the COMMIT statement, all the statements
within the transaction execute. If you end the transaction with a
ROLLBACK statement, none of the statements take effect.
To use the two-phase commit protocol for new application
programs that invoke only Oracle Rdb databases use the
DISTRIBUTED_TRANSACTION keyword in the START_TRANSACTION
statement. When you do this, Oracle Rdb invokes the DECdtm system
service calls for your application.
7.45.1 – Examples
The following statement starts a transaction with the following
characteristics:
o Default transaction (no user-specified transaction handle)
o READ_WRITE access
o CONSISTENCY mode
o WAIT option (by default)
o Will evaluate the constraint JOB_CODE_REQUIRED when the DML
statement is executed
o Will attempt to reserve the relation JOBS for SHARED_WRITE
access
7.45.1.1 – C Statement
START_TRANSACTION READ_WRITE ON pers USING (READ_WRITE CONSISTENCY
EVALUATING pers.JOB_CODE_REQUIRED AT VERB_TIME
RESERVING JOBS FOR SHARED WRITE);
7.45.1.2 – Pascal Statement
START_TRANSACTION READ_WRITE ON pers USING (READ_WRITE CONSISTENCY
EVALUATING pers.JOB_CODE_REQUIRED AT VERB_TIME
RESERVING JOBS FOR SHARED WRITE);
7.45.2 – Format
(B)0[m[4mSTART_TRANSACTION[m qwqqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqwqk
mq> ( qq> [4mTRANSACTION_HANDLE[m var qq> ) qj x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqj
mwqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqwqwqqqqqqq>qqqqqqqqwqk
mq> distributed-transaction-flag qj tq> tx-options qqu x
mq> on-clause qqqj x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqqwqqqqqqqq>qqqqqqqwqq>
mqq> on-error qqqj
(B)0[mdistributed-transaction-flag =
q> [4mDISTRIBUTED_TRANSACTION[m qwqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqwq>
m> [4mDISTRIBUTED_TID[m distributed-tid qj
(B)0[mtx-options =
qqwq> [4mBATCH_UPDATE[m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>
x x
mqqwqqqqqqqqqqqqqqqqqwqqwqqqqqqqqqqqwqqwqqqqqqqqqqqqqqqqqwqqqqqk x
tq> [4mREAD_ONLY[m qqqu tq> [4mWAIT[m qqqu tq> [4mCONCURRENCY[m qqu x x
mq> [4mREAD_WRITE[m qqqj mq> [4mNOWAIT[m qj mq> [4mCONSISTENCY[m qqj x x
x x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x
x x
mqqqwqq>qqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqq>qqqqqk x
mqq> [4mEVALUATING[m qqwq> evaluating-clause qqqwj x x
mqqqqqqqq , <qqqqqqqqqqqj x x
x x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x
x x
mqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqq>qqqqqqqj
mqq> [4mRESERVING[m qqqwq> reserving-clause qqwqqj
mqqqqqqqqqqq , <qqqqqqqj
(B)0[mon-clause =
qwq> ON qwq> db-handle qwq> USING qqq> ( qwq> tx-options qwq> )qwq>
x x x mq> DEFAULTS qqqqqj x
x mqqqq , <qqqqqj x
x x
mqqqqqqqqqqqqqqqqqqqqqqqqq<qqq AND <qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
(B)0[mevaluating-clause =
qqwqqqqqqqqqqqqqqq>qqqqqqqqwqq constraint qqk
mq> db-handle qq> . qq> qj x
x
lqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqj
x
mqqqqq> [4mAT[m qqqwqq> [4mVERB_TIME[m qqqwqqqq>
mqq> [4mCOMMIT_TIME[m qj
(B)0[mreserving-clause =
qqqwqqwqqqqqqqq>qqqqqqqqqqqqwqqq> relation-name qqqqqqqqqqqwqqk
x mq> db-handle qq> . <qj x x
x x x
mqqqqqqqqqqqqqqqqqqq , <qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x
x
lqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
x
mqq> [4mFOR[m qqwq>qqqqqqqqqqqqqqqwqqqwqq> [4mREAD[m qqqwqqqqqqqqqqqqqqk
tq> [4mEXCLUSIVE[m qqqqu mqq> [4mWRITE[m qqj x
tq> [4mPROTECTED[m qqqqu x
mq> [4mSHARED[m qqqqqqqj x
x
lqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
x
mqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqq>
tq> [4mWITH[m [4mAUTO_LOCKING[m qqqu
mq> [4mWITH[m [4mNOAUTO_LOCKING[m qj
7.45.2.1 – Format arguments
TRANSACTION_HANDLE The TRANSACTION_HANDLE keyword followed by
var a host language variable. A transaction
handle identifies a transaction. If
you do not supply a transaction handle
explicitly, RDML uses the default
transaction handle.
DISTRIBUTED_ You use this clause to specify the
TRANSACTION distributed transaction of which your
transaction will be a part.
DISTRIBUTED_TID A keyword followed by a host language
distributed-tid variable. You use the distributed-
tid variable to hold the value of the
distributed TID that DECdtm services
generate and return to the application.
DECdtm services use the TID to distinguish
the databases involved in a particular
distributed transaction.
tx-options Transaction options. Allow you to specify
the type of transaction you want, when you
want constraints to be evaluated and which
relations you intend to access.
on-clause Allows you to specify which database you
intend to access.
If you do not specify the ON clause, the
default Oracle Rdb behavior is to attempt
to start a transaction on all declared
databases (even if the RESERVING clause of
the START_TRANSACTION statement explicitly
specifies only tables in a particular
database).
DEFAULTS Allows you to specify that you want to
accept the default transaction options.
on-error The ON ERROR clause. Specifies
host language statement(s) to be
performed if an error occurs during the
START_TRANSACTION operation. For more
information see the entry on ON ERROR.
BATCH_UPDATE
READ_ONLY
READ_WRITE Declares what you intend to do with the
transaction as a whole. READ_ONLY is the
default.
WAIT
NOWAIT Specifies what your transaction will do
if it needs resources that are locked by
another transaction:
o WAIT is the default. It causes your
transaction to wait until the necessary
recourses are released or until Oracle Rdb
detects a deadlock.
o With NOWAIT, Oracle Rdb will return an
error if the resources you need are not
immediately available, thereby forcing
you to roll back your transaction.
CONCURRENCY
CONSISTENCY These options specify the consistency mode
of the transaction:
o CONCURRENCY is equivalent to SQL
ISOLATION LEVEL READ COMMITTED
(formerly called CONSISTENCY LEVEL
2).
o CONSISTENCY is the default. Consistency
guarantees that when all transactions
complete by committing or rolling back,
the effect on the database is the
same as if all transactions were run
sequentially.
evaluating-clause Allows you to specify the point at which
the named constraint(s) are evaluated. If
you specify VERB_TIME, they are evaluated
when the data manipulation statement is
issued. If you specify COMMIT_TIME, they
are evaluated when the COMMIT statement
executes. The evaluating clause is allowed
syntactically, but is ignored, with
READ_ONLY transactions.
db-handle Database handle. A host variable used
to refer to a specific database you have
invoked. For more information see the
entry on the Database Handle clause.
constraint The name of an Oracle Rdb constraint.
VERB_TIME
COMMIT_TIME VERB_TIME states when the data
manipulation statement is issued. COMMIT_
TIME reflects when the COMMIT statement
executes. VERB_TIME is the default.
reserving-clause Allows you to specify the relations you
plan to use and attempts to lock those
relations for your access. If you use the
WITH AUTO_LOCKING option (the default),
constraints and triggers defined on the
reserved relations will be able to access
additional relations that do not appear
in the list of reserved relations. The
WITH_AUTOLOCKING option will not work
for other relations not referenced in the
reserving clause.
relation-name The name of a relation in the database.
EXCLUSIVE
PROTECTED
SHARED The Oracle Rdb share modes. The keyword
you choose determines what operations
you allow others to perform on the
relations you are reserving. For READ_ONLY
transactions, EXCLUSIVE and PROTECTED are
syntactically allowed, but are ignored.
SHARED is the default.
READ
WRITE Declares what you intend to do with the
relations you have reserved:
o READ reserves the specified relation(s)
for read-only access
o WRITE reserves the specified
relation(s) for read and write access
WITH AUTO_LOCKING
WITH NOAUTO_LOCKING Oracle Rdb automatically locks any relations
referenced within a transaction unless you
specify the optional WITH NOAUTO_LOCKING
clause. WITH AUTO_LOCKING is the default.
7.46 – STARTING_WITH
The STARTING WITH conditional expression tests for the presence
of a specified string at the beginning of a string expression. A
STARTING WITH conditional expression is true if the second string
expression begins with the characters specified in the first
string expression.
If you precede the STARTING WITH expression by the optional NOT
qualifier, the condition is true if the first value expression
does not begin with the characters specified by the second value
expression.
The STARTING WITH conditional expression is case-sensitive; it
considers uppercase and lowercase forms of the same character to
be different.
7.46.1 – Examples
The following programs demonstrate the use of the STARTING WITH
clause. These programs create a record stream containing the
records in the EMPLOYEES relation in which the field LAST_NAME
has a name that begins with the string "IACO" or "Iaco". These
programs print the employee ID and last name contained in each
record in the record stream.
7.46.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
main()
{
READY PERS;
START_TRANSACTION READ_ONLY;
FOR E IN EMPLOYEES CROSS D1 IN DEGREES OVER EMPLOYEE_ID
WITH (UNIQUE D2 IN DEGREES WITH D2.EMPLOYEE_ID = E.EMPLOYEE_ID)
AND D1.DEGREE_FIELD = "Arts"
AND D1.COLLEGE_CODE = "STAN"
printf ("%s\n", E.EMPLOYEE_ID);
END_FOR;
COMMIT;
FINISH;
}
7.46.1.2 – Pascal Example
program multiple_cond (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
begin
READY PERS;
START_TRANSACTION READ_ONLY;
FOR E IN EMPLOYEES CROSS D1 IN DEGREES OVER EMPLOYEE_ID
WITH (UNIQUE D2 IN DEGREES WITH D2.EMPLOYEE_ID = E.EMPLOYEE_ID)
AND D1.DEGREE_FIELD = 'Arts'
AND D1.COLLEGE_CODE = 'STAN'
writeln (E.EMPLOYEE_ID);
END_FOR;
COMMIT;
FINISH;
end.
7.46.2 – Format
(B)0[mstarting-with-clause =
qqq> value-expr qqwqq>qqqqqqqqwqq> [4mSTARTING[m [4mWITH[m qqq> value-expr qq>
mqq> [4mNOT[m qqqj
7.46.2.1 – Format arguments
value-expr A value expression. A symbol or a string
of symbols used to calculate a value. When
you use a value expression in a statement,
Oracle Rdb calculates the value associated
with the expression and uses that value
when executing the statement.
7.47 – Statistical funcs
Calculate values based on a value expression for every record
in a record stream. You do not specify a value expression for
the COUNT statistical function because it operates on the record
stream formed by the RSE, not on a value expression. When you
use the AVERAGE, MAX, MIN, and TOTAL statistical functions, you
specify a value expression and a record selection expression
(RSE). Oracle Rdb then:
o Evaluates the value expression for each record in the record
stream formed by the RSE
o Calculates a single value based on the results of the first
step
The RDML Statistical functions are:
o AVERAGE
o COUNT
o MIN
o MAX
o TOTAL
For more information and the syntax diagrams see the HELP entry
on each of these functions.
7.47.1 – Format
(B)0[mstatistical-expr =
qqqwqq> [4mMAX[m qqqqqwqq> value-expr qwqqqqqqqqqqqqqqqqqqqqqk
tqq> [4mMIN[m qqqqqu x x
tqq> [4mTOTAL[m qqqu x x
tqq> [4mAVERAGE[m qj x x
mqq> [4mCOUNT[m qqqqqqqqqqqqqqqqqqqqj x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqqqqqqqqqqqqqqqqqq>qqqqqqqwqq>qqqqqqqqqqqqqqqqqqqqwqqqk
mqq> handle-options qqqj x
x
lqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqj
mqqq> [4mOF[m qqqqqqq> rse qqqqqqqqqqqqqqqqqqqqqqqqqqqqq>
7.47.1.1 – Format arguments
value-expr A value expression. A symbol or a string
of symbols used to calculate a value. When
you use a value expression in a statement,
Oracle Rdb calculates the value associated
with the expression and uses that value
when executing the statement.
handle-options A transaction handle, a request handle, or
both.
rse A record selection expression. A phrase
that defines specific conditions that
individual records must meet before
Oracle Rdb includes them in a record stream.
7.48 – TOTAL
Returns the sum of the values specified by a record selection
expression. The value expression must be a numeric data type.
7.48.1 – Examples
The following programs demonstrate the use of the TOTAL function
in an assignment statement. These programs:
o Use the TOTAL function to compute the total amount budgeted
for all departments in the DEPARTMENTS relation
o Print this computed value
7.48.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
DECLARE_VARIABLE all SAME AS PERS.DEPARTMENTS.BUDGET_ACTUAL;
main()
{
READY PERS;
START_TRANSACTION READ_ONLY;
GET
all = TOTAL D.BUDGET_ACTUAL OF D IN DEPARTMENTS;
END_GET;
printf ("%f", all);
COMMIT;
FINISH;
}
7.48.1.2 – Pascal Example
program total_function (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
all : double;
begin
READY PERS;
START_TRANSACTION READ_ONLY;
GET
all = TOTAL D.BUDGET_ACTUAL OF D IN DEPARTMENTS;
END_GET;
writeln (all:10:2);
COMMIT;
FINISH;
end.
7.48.2 – Format
(B)0[m q> [4mTOTAL[m qqqqqwq>qqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqk
mq> handle-options qqj x
x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqj
x
mqq> value-expr qqqqqq> [4mOF[m qqqqqq> rse qqqqqqqqqqqq>
(B)0[mhandle-options =
q> ( qwqqqqqq> [4mREQUEST_HANDLE[m qqqqq> var qqqqqqqqwq> ) q>
tqqqqqq> [4mTRANSACTION_HANDLE[m qqq> var qqqqqqqqu
mqqqqqq> [4mREQUEST_HANDLE[m q> var q> , qqqqqqqk x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x
mqqqq> [4mTRANSACTION_HANDLE[m q> var qqqqqqqqqqqj
7.48.2.1 – Format arguments
value-expr A value expression. A symbol or a string
of symbols used to calculate a value. When
you use a value expression in a statement,
Oracle Rdb calculates the value associated
with the expression and uses that value
when executing the statement.
rse A record selection expression. A phrase
that defines specific conditions that
individual records must meet before
Oracle Rdb includes them in a record stream.
handle-options A request handle, a transaction handle, or
both.
REQUEST_HANDLE var The REQUEST_HANDLE keyword followed by a
host language variable. A request handle
identifies a compiled Oracle Rdb request.
If you do not supply a request handle
explicitly, RDML associates a unique
request handle for the compiled request.
TRANSACTION_HANDLE The TRANSACTION_HANDLE keyword followed by
var a host language variable. A transaction
handle identifies a transaction. If
you do not supply a transaction handle
explicitly, RDML uses the default
transaction handle.
7.49 – STORE Statement
Inserts a record into an existing relation. You can add a
record to only one relation with a single STORE statement.
The statements between the keywords STORE and END_STORE form a
context block. You cannot store records into views defined by any
of the following record selection expression (RSE) clauses:
o WITH
o CROSS
o REDUCED
o FIRST
Trying to store into views that were defined with any of the
above clauses could cause unexpected results in your database.
You can use the GET statement within the bounds of the STORE ...
END_STORE block. Note that any valid format of the GET statement
is permitted within this block.
You may find it particularly useful to use the GET statement to
place the database key (dbkey) of the record you are storing into
a host variable. Use the GET...RDB$DB_KEY construct to assign the
value of the dbkey to the host variable.
7.49.1 – Examples
The following programs demonstrate the use of the STORE
statement. The programs:
o Prompt the user for information to store in the COLLEGES
relation
o Start a READ_WRITE transaction
o Use a STORE statement to store the user input into the
COLLEGES relation
o Use the GET statement to store the value of the dbkey for
the newly stored record into the host variable my_db_key.
(Retrieving the dbkey is not a required part of the STORE
statement syntax, but may be helpful in some applications.)
7.49.1.1 – C Example
DATABASE PERS = FILENAME "PERSONNEL";
extern void read_string();
static DEFINE_TYPE coll_code SAME AS COLLEGES.COLLEGE_CODE;
static DEFINE_TYPE coll_name SAME AS COLLEGES.COLLEGE_NAME;
static DEFINE_TYPE coll_city SAME AS COLLEGES.CITY;
static DEFINE_TYPE coll_state SAME AS COLLEGES.STATE;
static DEFINE_TYPE post_code SAME AS COLLEGES.POSTAL_CODE;
static DEFINE_TYPE my_db_key SAME AS COLLEGES.RDB$DB_KEY;
main()
{
read_string ("Enter College Code: ", coll_code, sizeof(coll_code));
read_string ("Enter College Name: ", coll_name, sizeof(coll_name));
read_string ("Enter College City: ", coll_city, sizeof(coll_city));
read_string ("Enter College State: ",coll_state, sizeof(coll_state));
read_string ("Enter Postal Code: ", post_code, sizeof(post_code));
READY PERS;
START_TRANSACTION READ_WRITE;
STORE C IN COLLEGES USING
strcpy (C.COLLEGE_CODE, coll_code);
strcpy (C.COLLEGE_NAME, coll_name);
strcpy (C.CITY, coll_city);
strcpy (C.STATE, coll_state);
strcpy (C.POSTAL_CODE, post_code);
GET
my_db_key = C.RDB$DB_KEY;
END_GET;
END_STORE;
COMMIT;
FINISH;
}
7.49.1.2 – Pascal Example
program store_with_host_lang (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
var
DECLARE_VARIABLE coll_code SAME AS COLLEGES.COLLEGE_CODE;
DECLARE_VARIABLE coll_name SAME AS COLLEGES.COLLEGE_NAME;
DECLARE_VARIABLE coll_city SAME AS COLLEGES.CITY;
DECLARE_VARIABLE coll_state SAME AS COLLEGES.STATE;
DECLARE_VARIABLE post_code SAME AS COLLEGES.POSTAL_CODE;
DECLARE_VARIABLE my_db_key SAME AS COLLEGES.RDB$DB_KEY;
begin
writeln ('Enter College Code:');
readln (coll_code);
writeln ('Enter College Name:');
readln (coll_name);
writeln ('Enter College City:');
readln (coll_city);
writeln ('Enter College State:');
readln (coll_state);
writeln ('Enter College Postal Code:');
readln (post_code);
READY PERS;
START_TRANSACTION READ_WRITE;
STORE C IN COLLEGES USING
C.COLLEGE_CODE := coll_code;
C.COLLEGE_NAME := coll_name;
C.CITY := coll_city;
C.STATE := coll_state;
C.POSTAL_CODE := post_code;
GET
my_db_key = C.RDB$DB_KEY;
END_GET;
END_STORE;
COMMIT;
FINISH;
end.
7.49.2 – Format
(B)0[mSTORE qqqqwqq>qqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqk
mqq> handle-options qj x
x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqq> context-var qqq> [4mIN[m qqqqqqqqqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqk
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqqwq>qqqqqqqqqqqqqqqqqqwqq> relation-name qqqqq> USING qqqqqqqk
mq> db-handle qq> . qj x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqqwq>qqqqqqqqqqqwqqwq> statement qqqqqqqqqqqqqqqqqqqqqqqqqqqqwk
mq> on-error qj tq> context-var.* qq> = qq> record-descr qux
mqqqqqqqqqqqqqqqqqq <qqqqqqqqqqqqqqqqqqqqqqjx
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqqqqqqqqwqqqqqqqqqqqqqqqqqqwqqqqqqqqqq> [4mEND_STORE[m
mq> get-statement qj
(B)0[mhandle-options =
q> ( qwqqqqqq> [4mREQUEST_HANDLE[m qqqqq> var qqqqqqqqwq> ) q>
tqqqqqq> [4mTRANSACTION_HANDLE[m qqq> var qqqqqqqqu
mqqqqqq> [4mREQUEST_HANDLE[m q> var q> , qqqqqqqk x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x
mqqqq> [4mTRANSACTION_HANDLE[m q> var qqqqqqqqqqqj
(B)0[mget-statement =
[4mGET[m qqwqqqq>qqqqqqqqwqqwq> get-item qqwqqqqq> [4mEND_GET[m qqq>
mq> on-error qj mqqqqq ; <qqqqqqj
(B)0[mget-item =
qqqqwqq> host-var qqqqqqqq> = qqqqq> value-expr qqqqqqqqqqwqqqq>
tqq> record-descr qqqq> = qqqqq> context-var.* qqqqqqqu
mqq> host-var qqqqqqqq> = qqqqq> statistical-expr qqqqj
7.49.2.1 – Format arguments
handle-options A request handle, a transaction handle, or
both.
REQUEST_HANDLE var The REQUEST_HANDLE keyword followed by a
host language variable. A request handle
identifies a compiled Oracle Rdb request.
If you do not supply a request handle
explicitly, RDML associates a unique
request handle for the compiled request.
TRANSACTION_HANDLE The TRANSACTION_HANDLE keyword followed by
var a host language variable. A transaction
handle identifies a transaction. If
you do not supply a transaction handle
explicitly, RDML uses the default
transaction handle.
context-var A context variable. A temporary name that
you associate with a relation. You define
a context variable in a relation clause.
For more information see the entry on
Context Variables.
db-handle Database handle. A host variable used
to refer to a specific database you have
invoked. For more information see the
entry on the Database Handle clause.
relation-name The name of a relation in the database.
on-error The ON ERROR clause. The first occurrence
specifies host language or RDML
statement(s) to be performed if an error
occurs during the STORE operation. The
second occurrence specifies host language
or RDML statement(s) to be performed if
an error occurs during the GET operation.
For more information see the entry on ON
ERROR.
statement Any valid RDML or host language statement,
except the GET statement, to be executed
during the STORE operation. Use a
semicolon (;) at the end of each RDML,
Pascal, or C statement.
get-item The GET statement includes an assignment
statement specifying a host variable and
a database value. The database value is
assigned to the host variable from the
Oracle Rdb value expression or statistical
expression. Note that the GET statement
must be the last statement to appear
before the END_STORE statement.
host-var A valid variable name declared in the host
program.
value-expr A valid RDML value expression. The value
expression can include the "<context-
variable>.RDB$DB_KEY" expression.
record-descr A valid host language record descriptor
that contains an entry for each field in
fields of the relation. Each field of the
record descriptor must match exactly the
field names and data types of the fields
in the Oracle Rdb relation referenced by
the context variable.
statistical-expr A statistical expression; calculates
values based on a value expression for
every record in the record stream.
7.50 – STORE Seg Strings
Stores a segmented string into a segmented string field. Must be
nested within a regular STORE statement.
7.50.1 – Examples
The following programs demonstrate the use of the STORE statement
to store segmented strings in a record. These programs:
o Declare an array to hold the segmented strings to be stored.
o Assign values to the array.
o Use a STORE operation to store the employee ID in the RESUMES
record.
o Use an inner STORE operation to store the segmented strings.
This ensures that the employee ID and the segmented strings
are stored in the same record.
o Store the values from the array into the RESUME field of
RESUMES.
o Complete the STORE operation.
o Retrieve the segmented strings (just stored) using a nested
FOR statement.
For more information on retrieving segmented strings, see the
entry on FOR Seg_String.
7.50.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
main()
{
int line;
char *document[3];
document[0] = "first line of resume ";
document[1] = "second line of resume ";
document[2] = "last line of resume ";
READY PERS;
START_TRANSACTION READ_WRITE;
STORE R IN RESUMES USING
strcpy (R.EMPLOYEE_ID,"12345");
for (line = 0; line <= 2; line++)
STORE LINE IN R.RESUME
strcpy(LINE.VALUE,document[line]);
LINE.LENGTH = strlen(LINE.VALUE);
END_STORE;
END_STORE;
FOR R IN RESUMES WITH R.EMPLOYEE_ID = "12345"
FOR LINE IN R.RESUME
printf("%s\n",LINE.VALUE);
END_FOR;
END_FOR;
COMMIT;
FINISH;
}
7.50.1.2 – Pascal Example
program segstr (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
type lines = varying [80] of char;
var linecnt : integer;
document : array [0..2] of lines;
begin
document[0] := 'first line of resume ';
document[1] := 'second line of resume ';
document[2] := 'last line of resume ';
READY PERS;
START_TRANSACTION READ_WRITE;
STORE R IN RESUMES USING
R.EMPLOYEE_ID:= '12345';
for linecnt := 0 to 2 do
STORE SEG IN R.RESUME
SEG := document[linecnt];
SEG.LENGTH := length(document[linecnt]);
END_STORE;
END_STORE;
COMMIT;
START_TRANSACTION READ_WRITE;
FOR R IN RESUMES WITH R.EMPLOYEE_ID = '12345'
FOR SEG IN R.RESUME
writeln(SEG);
END_FOR;
END_FOR;
COMMIT;
FINISH;
end.
7.50.2 – Format
(B)0[m[4mSTORE[m qqqqqwq>qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq>qqwqqqqqqqk
mq> ( qq> [4mTRANSACTION_HANDLE[m qq var qq> )qj x
lqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqj
mqq> ss-handle qqq> [4mIN[m qqqq> ss-field qqqqqqqqqqqqqqqqqqk
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqj
mqq> [4mUSING[m qqqwq>qqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqk
mq> on-error qqj x
lqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqqqqqqqqqqqqqqqq assignment qqqqqqqqqqqq>qqqqqqk
x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqj
mqqqq> [4mEND_STORE[m qqqqq>
7.50.2.1 – Format arguments
TRANSACTION_HANDLE The TRANSACTION_HANDLE keyword followed by
var a host language variable. A transaction
handle identifies a transaction. If
you do not supply a transaction handle
explicitly, RDML uses the default
transaction handle.
ss-handle A segmented string handle. A name that
identifies the segmented string.
ss-field A qualified field name that refers to a
field defined with the SEGMENTED STRING
data type. Note that this field name, like
all field names in a FOR statement, must
be qualified by its own context variable.
This second context variable must match
the variable declared in the outer FOR
statement. See the Examples entry.
on-error The ON ERROR clause. Specifies host
language statement(s) to be performed
if an error occurs during the STORE
operation. For more information see the
entry on ON ERROR.
assignment Associates the two database variables with
a value expression. The database variables
refer to the segment of a segmented string
and its length. The special name for
the segment can be either "VALUE" or
"RDB$VALUE". The special name for the
segment length can be either "LENGTH"
or "RDB$LENGTH". You cannot assign any
other database variables to the value
expressions for segmented strings. The
assignment operator for RDML Pascal is
":=" and for RDML C is "=" or strcpy.
7.51 – Trans Handle
A host language variable that allows you to associate a name
with a particular transaction. If you do not supply a handle name
explicitly, RDML defines a default transaction handle for the
transaction.
7.51.1 – Examples
The following programs demonstrate the use a host variable value
expression as a transaction handle. These programs declare the
host variable, EMP_UPDATE. The programs use EMP_UPDATE to qualify
the transaction in the START_TRANSACTION expression, the record
selection expression, and the COMMIT statement. The record
selection expression modifies the record with the specified ID
number in the EMPLOYEES relation. The COMMIT statement, also
qualified with the transaction handle, ensures that the modified
record is stored in the database.
Note that the C program uses the pad_string function to read
in the values for the STORE statement. This function pads the
values stored in each field with the correct number of trailing
blanks to ensure that the length of the values stored match the
text size of the field. For more information on pad_string, see
Appendix B of the "RDML Reference Manual".
7.51.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
extern void pad_string();
main()
{
int EMP_UPDATE = 0;
READY PERS;
START_TRANSACTION (TRANSACTION_HANDLE EMP_UPDATE) READ_WRITE;
FOR (TRANSACTION_HANDLE EMP_UPDATE) E IN EMPLOYEES
WITH E.EMPLOYEE_ID = "00178"
MODIFY E USING
pad_string("Brannon", E.LAST_NAME, sizeof(E.LAST_NAME));
END_MODIFY;
END_FOR;
COMMIT(TRANSACTION_HANDLE EMP_UPDATE);
FINISH;
}
7.51.1.2 – Pascal Example
program trhand (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
var EMP_UPDATE : [volatile] integer := 0;
begin
READY PERS;
START_TRANSACTION (TRANSACTION_HANDLE EMP_UPDATE) READ_WRITE;
FOR (TRANSACTION_HANDLE EMP_UPDATE) E IN EMPLOYEES
WITH E.EMPLOYEE_ID = '00178'
MODIFY E USING
E.LAST_NAME := 'Brannon';
END_MODIFY;
END_FOR;
COMMIT (TRANSACTION_HANDLE EMP_UPDATE);
FINISH;
end.
7.51.2 – Format
(B)0[mtransaction-handle = qq> ( qq> [4mTRANSACTION_HANDLE[m qqq> host-var q> ) qqq>
7.51.2.1 – Format arguments
host-var A valid host language variable.
7.52 – Undeclared START STREAM
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 and other RDML statements
(for example, MODIFY and ERASE) to manipulate each record.
7.52.1 – Examples
The following programs:
o Create a record stream, CURRENT_INF_STREAM, consisting of the
CURRENT_INFO record sorted by highest salary first
o Fetch the first record, thereby fetching the CURRENT_INFO
record with the highest salary
o Display a message about that record
7.52.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
main()
{
READY PERS;
START_TRANSACTION READ_ONLY;
START_STREAM CURRENT_INF_STREAM USING
CI IN CURRENT_INFO SORTED BY DESC CI.SALARY;
FETCH CURRENT_INF_STREAM;
printf ("%s makes the largest salary!\n", CI.LAST_NAME);
END_STREAM CURRENT_INF_STREAM;
COMMIT;
FINISH;
}
7.52.1.2 – Pascal Example
program record_stream (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
begin
READY PERS;
START_TRANSACTION READ_ONLY;
START_STREAM CURRENT_INF_STREAM USING
CI IN CURRENT_INFO SORTED BY DESC CI.SALARY;
FETCH CURRENT_INF_STREAM;
writeln (CI.LAST_NAME, ' makes the largest salary!');
END_STREAM CURRENT_INF_STREAM;
COMMIT;
FINISH;
end.
7.52.2 – Format
(B)0[m[4mSTART_STREAM[m qqqwqq>qqqqqqqqqqqqqqqqqqwqqq> stream-name qqqqqqk
mqq> handle-options qqj x
x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
x
mqqqqqqqq> [4mUSING[m qqqq> rse qqqqwqqqqqqqqqqqqqqqwqqqqqqqqqq>
mqq> on-error qqj
(B)0[mon-error =
[4mON[m [4mERROR[m qqwqqq> statement qqqqwqqq> [4mEND_ERROR[m
mqqqqqqqqq<qqqqqqqqqj
(B)0[mhandle-options =
q> ( qwqqqqqq> [4mREQUEST_HANDLE[m qqqqq> var qqqqqqqqwq> ) q>
tqqqqqq> [4mTRANSACTION_HANDLE[m qqq> var qqqqqqqqu
mqqqqqq> [4mREQUEST_HANDLE[m q> var q> , qqqqqqqk x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x
mqqqq> [4mTRANSACTION_HANDLE[m q> var qqqqqqqqqqqj
7.52.2.1 – Format arguments
stream-name The stream that you create. The stream-
name must be a valid operating system
name.
rse A record selection expression. A phrase
that defines specific conditions that
individual records must meet before
Oracle Rdb includes them in a record stream.
on-error The ON ERROR clause. Specifies host
language statement(s) to be performed
if an error occurs during the START_STREAM
operation. For more information see the
entry on ON ERROR.
handle-options A request handle, a transaction handle, or
both.
REQUEST_HANDLE var The REQUEST_HANDLE keyword followed by a
host language variable. A request handle
identifies a compiled Oracle Rdb request.
If you do not supply a request handle
explicitly, RDML associates a unique
request handle for the compiled request.
TRANSACTION_HANDLE The TRANSACTION_HANDLE keyword followed by
var a host language variable. A transaction
handle identifies a transaction. If
you do not supply a transaction handle
explicitly, RDML uses the default
transaction handle.
7.53 – UNIQUE
Tests for the presence of a single record in a record stream.
A UNIQUE conditional expression is true if the record stream
specified by the record selection expression consists of only one
record.
If you precede the UNIQUE expression with the optional NOT
qualifier, the condition is true if there is more than one record
in the record stream or if the stream is empty.
7.53.1 – Examples
The following programs demonstrate the use of the UNIQUE
conditional expression. These programs join the relations
EMPLOYEES and DEGREES over their common field, EMPLOYEE_ID. The
UNIQUE expression limits the record stream to those records in
the EMPLOYEES relation that have only one corresponding record
in the DEGREES relation. These programs print an informational
message and the selected employees first and last name in
alphabetical order, based on the last name.
7.53.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
main()
{
READY PERS;
START_TRANSACTION READ_ONLY;
FOR E IN EMPLOYEES
SORTED BY E.FIRST_NAME
WITH UNIQUE D IN DEGREES WITH D.EMPLOYEE_ID = E.EMPLOYEE_ID
printf("%s %s has one and only one college degree.\n",
E.FIRST_NAME, E.LAST_NAME);
END_FOR;
COMMIT;
FINISH;
}
7.53.1.2 – Pascal Example
program unique_expr (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
begin
READY PERS;
START_TRANSACTION READ_ONLY;
FOR E IN EMPLOYEES
WITH UNIQUE D IN DEGREES WITH D.EMPLOYEE_ID = E.EMPLOYEE_ID
writeln (E.FIRST_NAME, ' ', E.LAST_NAME,
' has one and only one college degree.');
END_FOR;
COMMIT;
FINISH;
end.
7.53.2 – Format
(B)0[munique-clause =
qqqwqq>qqqqqqqwqqqqqqqqqq> [4mUNIQUE[m qqqqqqwq>qqqqqqqqqqqqqqqqqqwqqqqk
mqq> [4mNOT[m qqj mq> handle-options qqj x
x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqqqqqqqqqqqqqq> rse qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq>
(B)0[mhandle-options =
q> ( qwqqqqqq> [4mREQUEST_HANDLE[m qqqqq> var qqqqqqqqwq> ) q>
tqqqqqq> [4mTRANSACTION_HANDLE[m qqq> var qqqqqqqqu
mqqqqqq> [4mREQUEST_HANDLE[m q> var q> , qqqqqqqk x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x
mqqqq> [4mTRANSACTION_HANDLE[m q> var qqqqqqqqqqqj
7.53.2.1 – Format arguments
rse A record selection expression. A phrase
that defines specific conditions that
individual records must meet before
Oracle Rdb includes them in a record stream.
handle-options A request handle, a transaction handle, or
both.
REQUEST_HANDLE var The REQUEST_HANDLE keyword followed by a
host language variable. A request handle
identifies a compiled Oracle Rdb request.
If you do not supply a request handle
explicitly, RDML associates a unique
request handle for the compiled request.
TRANSACTION_HANDLE The TRANSACTION_HANDLE keyword followed by
var a host language variable. A transaction
handle identifies a transaction. If
you do not supply a transaction handle
explicitly, RDML uses the default
transaction handle.
7.54 – Value expr
A value expression is a symbol or string of symbols that you
use to calculate a value. When you use a value expression in
a statement, Oracle Rdb calculates the value associated with the
expression and uses it when executing the statement.
The RDML value expressions are:
o Arithmetic
o Database Field Value
o FIRST FROM
o Host Variable
o RDB$DB_KEY
o RDB$MISSING
For more information and the syntax diagrams see the HELP entries
for each of these expressions.
7.54.1 – Format
(B)0[mvalue-expr =
qqqwqqq> host-variable qqqqqqqqqqqqwqqq>
tqqq> db-field qqqqqqqqqqqqqqqqqu
tqqq> quoted-string qqqqqqqqqqqqu
tqqq> numeric-literal qqqqqqqqqqu
tqqq> statistical-expr qqqqqqqqqu
tqqq> arithmetic-expr qqqqqqqqqqu
tqqq> missing-value qqqqqqqqqqqqu
tqqq> first-expr qqqqqqqqqqqqqqu
tqqq> concat-expr qqqqqqqqqqqqqqu
tqqq> dbkey qqqqqqqqqqqqqqqqqqqu
tqqq> ( qq> value-expr qq> ) qqqu
tqqq> - qq> value-expr qqqqqqqqu
mqqq> + qq> value-expr qqqqqqqqj
The concat-expr consists of the concatenate operator ( | ) and
two value expressions. The concat-expr joins the second value
expression to the first value expression.
7.55 – WITH
Contains a conditional expression that lets you specify
conditions that must be true for a record to be included in a
record stream.
7.55.1 – Examples
The following programs demonstrate the use of the WITH clause in
a record selection expression. These programs:
o Create a record stream of all those records in the employee
relation with an employee ID of "00169"
o Print the employee ID and last name from the records in the
record stream
7.55.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
main()
{
READY PERS;
START_TRANSACTION READ_ONLY;
FOR E IN EMPLOYEES
WITH E.EMPLOYEE_ID = "00169"
printf ("%s ", E.EMPLOYEE_ID);
printf ("%s", E.LAST_NAME);
END_FOR;
COMMIT;
FINISH;
}
7.55.1.2 – Pascal Example
program with_clause (input,output);
DATABASE PERS = FILENAME 'PERSONNEL';
begin
READY PERS;
START_TRANSACTION READ_ONLY;
FOR E IN EMPLOYEES
WITH E.EMPLOYEE_ID = '00169'
writeln (E.EMPLOYEE_ID, ' ', E.LAST_NAME);
END_FOR;
COMMIT;
FINISH;
end.
7.55.2 – Format
(B)0[mwith-clause =
qqqqqq> [4mWITH[m qqq> conditional-expr qqq>
7.55.2.1 – Format arguments
conditional-expr Conditional expression. An expression
that evaluates to true or false. For more
information see the entry on Conditional
expressions.
8 – Query governor
Using the Oracle Rdb query governor feature, you can specify the
following for RDML queries:
o The maximum elapsed time that the query optimizer can spend
compiling a query
o The maximum number of rows delivered during query processing
There is no RDML interface for the query governor feature, but
this feature can be enabled for RDML queries by defining the
RDMS$BIND_QC_REC_LIMIT and RDMS$BIND_QC_TIMEOUT logical names.
See the top-level topic "Logical_Names" in the RDO interface
help file for information on how to define and set values for the
RDMS$BIND_QC_REC_LIMIT and RDMS$BIND_QC_TIMEOUT logical names.
9 – Errors
Explanations and suggested user actions about errors returned by
the RDML preprocessor are available online in three forms:
o Under the topic RDML_ERRORS
o In the file SYS$HELP:RDML_MSG.DOC that contains message
information specific to the RDML preprocessor. You can TYPE,
PRINT, or SEARCH this file for detailed information.
o In the file SYS$HELP:RDB_MSG.DOC that contains message
information for errors that may be encountered during
application execution.
10 – RDML_ERRORS
message_file RDML message file
10.1 – AMBIG_HANDLE
Database handle '<str>', qualifying constraint '<str>', found multiple times in ON list Explanation: The database handle used to qualify the constraint exists more than once in the ON list of the START_TRANSACTION statement. User Action: Correct the database handle name or correct the list of handles in the ON list.
10.2 – AMBIG_REL_NAME
Relation '<str>' exists in multiple databases; the name must be qualified Explanation: The named relation exists in more than one database. User Action: Use a database context variable to qualify the relation.
10.3 – ATLINE
at line <num> in the file <str> Explanation: The previous error was detected at the stated line in the stated file. User Action: Use this information to help find an error in source file.
10.4 – AUTO_LOCK_ONLY
SQL does not support [NO]AUTO_LOCKING syntax; always uses AUTOLOCKING Explanation: A WITH AUTO_LOCKING or WITH NOAUTO_LOCKING clause was seen in a START_TRANSACTION statement. SQL does not support the syntax for either clause. SQL behaves as though WITH AUTO_LOCKING was specified as its default behavior. There is no way to mimic WITH NOAUTO_LOCKING. User Action: Eliminate WITH NOAUTO_LOCKING from your source code. The warning is generated for WITH AUTO_LOCKING also to make you aware that an explicit clause has been removed.
10.5 – BADDMLPARSE
Error parsing file specification '<str>' Explanation: The file specification provided cannot be used. User Action: Correct the file specification.
10.6 – BAD_PROTOCOL
CDD record description is malformed <str> <str> Explanation: The CDD record is malformed. User Action: It may be necessary to integrate the database metadata in the CDD using the Rdb INTEGRATE command.
10.7 – BETWEENORD
BETWEEN operands must be in order in SQL Explanation: RDML interprets the BETWEEN operator differently from SQL. In RDML, "A BETWEEN B OR C" is equivalent to "A BETWEEN C OR B". Although the converter could have generated a functionally equivalent disjunction, this would have been sloppy. If the converter can evaluate the expressions, it does and places the expressions in the correct order. If not, it produces a warning. The diagnostic file can be used to easily locate and modify this code. User Action: Make sure that the second and third operands of a BETWEEN expression are always properly ordered; or alternatively, OR the BETWEEN clause generated with another with the second and third operands in reverse order.
10.8 – BLR_GENERATION
Error occurred during BLR generation Explanation: An error was detected during the BLR generation phase of precompilation. Additional messages identify the specific problem more precisely. User Action: Follow the actions suggested for the messages that identify the specific problem.
10.9 – CDD_ERROR
error accessing the CDD <str> <str> Explanation: RDML detected an unexpected error while loading metadata. User Action: insure that the CDD metadata is correct.
10.10 – CODE_GENERATION
Error occurred during code generation Explanation: An error was detected during the code generation phase of precompilation. Additional messages identify the specific problem more precisely. User Action: Follow the actions suggested for the messages that identify the specific problem.
10.11 – CVINUSE
Context variable '<str>' has already been defined Explanation: The named symbol has been previously defined and cannot be redefined in a new context. User Action: Use a unique name for the conflicting symbol.
10.12 – DATE_CONVERT
date/time string can not be converted Explanation: The date/time string literal can not be converted to VMS binary date format. The string literal format does not match the format defined by the logical name LIB$DT_INPUT_FORMAT, or the default system format which is DD-MMM-YYYY HH:MM:SS.CC. User Action: Check the definition of this logical name and the string literal in the RDML source code.
10.13 – DATE_EMPTY
/DATE_TYPE=EMPTY_RECORDS specified; use /DATE_TYPE=NOEMPTY_RECORDS Explanation: You explicitly specified the obsolete value EMPTY_RECORDS using the /DATE_TYPE qualifier. This causes RDML to generate a date type for Pascal using an empty record. The Pascal compiler may not handle assignments for dates appropriately in this case. User Action: Either specify /DATE_TYPE=NOEMPTY_RECORDS or omit the /DATE_TYPE qualifier from the RDML command line.
10.14 – DDTMHANDLES
DDTM transaction handles generated for this module; use /CONTEXT=ALL qualifier when compiling SQL module Explanation: The converter generated extra arguments to SQL-Module Language procedures to pass transaction information to the Digital Database Transaction Manager. User Action: Be sure to COMPILE and LINK your SQL Module Language files with the correct compilation switches and libraries.
10.15 – DIC_DB_CHG
A dictionary definition used by CDD/Plus database <str> has changed Explanation: A dictionary definition used by the database has changed. The actual information in the provided CDD pathname may not be consistent and you may receive unexpected results. User Action: Use the INTEGRATE statement to resolve any differences between the dictionary and the database, insure that the CDD metadata is correct.
10.16 – DMLSYNTAX
Syntax error: found '<str>' when expecting '<str>' Explanation: A syntax error has been detected; the symbol found does not match the expected symbol. User Action: Corrent the syntax error.
10.17 – DUP_CDD_NAME
Record type <str> has conflicting CDD definitions Explanation: Two relations from different databases have the same name. Normally, the preprocessor disambiguates these relations by prefixing their names with the database handle. However, when the databases come from CDD and the converter is generating references to the data dictionary, the dictionary chooses the names which will be used to refer to a relation structured type. CDD will not choose unique names, hence your program will not compile. User Action: If the relation referred to by the message is identical to the relation which was declared (perhaps because it came from a different link to the same database), then nothing needs to be done. If the relation is different you will have to take steps to disambiguate the Orelation names yourself or re-translate using the /NOCDD_NAMES switch.
10.18 – DUP_REDUCE_CLSE
Duplicate REDUCED Clause found in this RSE. It was ignored Explanation: A REDUCED clause has been specified twice in a record selection expression. User Action: Remove the incorrect or duplicate entry from the record selection expression.
10.19 – DUP_REQ_OPTION
Duplicate Request Option found and ignored Explanation: A request or transaction handle has been specified twice in a RDML statement. User Action: Remove the incorrect or duplicate entry from the statement.
10.20 – DUP_SORT_CLAUSE
Duplicate SORT Clause Found in this RSE. It was ignored Explanation: A SORT clause has been specified twice in a record selection expression. User Action: Remove the incorrect or duplicate entry from the record selection expression.
10.21 – DUP_WITH_CLAUSE
Duplicate WITH Clause found in this RSE. It was ignored Explanation: A WITH clause has been specified twice in a record selection expression. User Action: Remove the incorrect or duplicate entry from the record selection expression.
10.22 – EOFERR
Unexpected end-of-file encountered while processing '<str>' Explanation: A premature end of file was encountered during the processing of a statement. User Action: Correct the incomplete statement.
10.23 – ERROR_CREATING
Cannot create file '<str>' Explanation: Cannot create the named file. User Action: Make sure the file can be created as specified.
10.24 – ERROR_OPENING
Cannot open file '<str>' Explanation: Cannot open the named file. User Action: Make sure the file can be accessed as specified.
10.25 – FINISH
FINISH behaves differently in SQL Explanation: FINISH closes and detaches from all databases in SQL. User Action: Check code to see if your usage will be affected.
10.26 – FLD_IN_MUL_REL
Field '<str>' (used in an OVER clause) is defined in more than two relations Explanation: The OVER clause is ambiguous because the named field appears in more than two relations in the current query. User Action: Use a WITH clause to express an unambiguous query.
10.27 – FLD_NOT_DEFINED
Field '<str>' is not defined in relation '<str>' Explanation: The named field is not defined in the named relation. User Action: Correct the field reference.
10.28 – FLD_ONLY_IN_ONE
Field '<str>' (used in an OVER clause) is only defined in relation '<str>' Explanation: The OVER clause is ambiguous because the named field does not appear in two relations in the current query. User Action: Use a WITH clause to express an unambiguous query.
10.29 – GFLOATING
G_Floating datatypes detected in this module; use /G_FLOATING qualifier when compiling Explanation: A double precision floating point number was detected in this module during processing. User Action: Be sure to COMPILE and LINK all modules in the application with the correct compilation switches and libraries.
10.30 – HOST_VARIABLE
Error detected in host variable syntax Explanation: The current token, which appears to be a host variable, does not fit the recognized syntax for host variables. User Action: Correct the host variable syntax.
10.31 – ILLMODIFY
Record modification must be done in a record stream Explanation: An attempt is being made to modify a record outside an established record stream. User Action: Place the modify statement in an established record using a FOR or START_STREAM statement.
10.32 – ILLPREFIX
Illegal characters in global prefix
Explanation: The value specified for the /GLOBAL_PREFIX
qualifier, or the file name if /GLOBAL_PREFIX was not specified,
contains a character which is neither alphanumeric nor the
underscore character. The converter will still operate, but the
resulting code may not compile or link properly.
User Action: Specify a value for /GLOBAL_PREFIX which contains
only the upper or lowercase letters, the digits 0-9, or
underscore ('_').
10.33 – ILLRETRIEVE
Segmented string retrieval is not permitted from within a STORE Explanation: An attempt is being made to retrieve a segmented string from within a STORE statement. User Action: Remove the incorrect statement.
10.34 – INVINGET
Invalid within GET statement Explanation: The GET statement contains an invalid value expression. User Action: Correct the value expression.
10.35 – INVPRESTRHDL
<str> handle '<str>' specified in START_STREAM for declared stream -- handle ignored Explanation: A START_STREAM statement for a declared stream specified an explicit handle. The handle was ignored by RDML. Any explicit handle for a declared stream must be specified on the DECLARE_STREAM declaration, rather than on the START_STREAM statement. User Action: Remove the handle specification, or move it to the DECLARE_STREAM statement.
10.36 – INVWILD
Assigning values to database fields using wildcards is only valid once within STORE and MODIFY statements Explanation: An invalid wildcard assignment was recognized. Either it was not inside a STORE or MODIFY statement, or it was specified more than once in such a statement. User Action: Remove the wildcard assignment, or enclose it inside a STORE or a MODIFY statement.
10.37 – INV_HANDLE
Database handle '<str>', qualifying constraint '<str>', not found in ON list Explanation: The database handle used to qualify the constraint does not exist in the ON list of the START_TRANSACTION statement. User Action: Correct the database handle name or correct the list of handles in the ON list.
10.38 – JOIN_ATTRIBUTE
Relation '<str>' is part of join cannot be updated Explanation: You tried to modify or erase records from a relation that is joined (using the CROSS clause) with other relations. User Action: Redefine the query so that the relation you are trying to update is not directly joined to other relations. i.e. do not use a CROSS clause, but instead represent the other relation references as subqueries.
10.39 – LIKECONFL
Letters and underscore detected in a LIKE string Explanation: The converter converts MATCHING string constants to use the LIKE wildcard characters. There is no way to have both letters and an underscore in a LIKE target string that will still have the same semantics as the original, because SQL only permits either IGNORE CASE or an ESCAPE character, but not both. The converter will generate IGNORE CASE andthe underscore will act as a wild card, matching ANY single character (as '%' does in MATCHING. User Action: Change the target pattern.
10.40 – LIKETOOBIG
Conversion to LIKE string overflows buffer Explanation: When converting a MATCHING string to a LIKE string, additional escape characters were added which overflow the character string buffer. User Action: Break the comparison up into smaller segments.
10.41 – LIKE_TEXT
LIKE strings have different wildcards in SQL Explanation: The RDML wild card pattern matching characters '*' and '%' are written as '%' and '_', respectively, in SQL. User Action: When constructing the character string to be passed to the SQL module language matching function, use '%' and '_'.
10.42 – LINETOOLONG
Line number <num> is longer than <num> characters Explanation: The line mentioned is longer than the maximum allowed length. User Action: Shorten the line or split the line into smaller lines.
10.43 – MISSCLSQUOTE
Missing close quote; inserted at end-of-line Explanation: A quoted string is missing a closing quote. User Action: Place a closing quote at the end of the quoted string.
10.44 – MOD2RELS
SQL does not allow <str> on streams with multiple relations Explanation: You have used MODIFY or ERASE on a stream whose record selection expression references more than one relation (i.e, contains a CROSS clause). SQL only permits its equivalent of MODIFY (UPDATE) and ERASE (DELETE) to operate on streams which reference no more than one relation as these are considered read only. User Action: Rewrite your code so it does not attempt to modify or erase from these relations.
10.45 – MODREDUCE
SQL does not allow <str> on reduced streams Explanation: You have used MODIFY or ERASE on a stream whose record selection expression contains a REDUCED TO clause. SQL does not permit its equivalent of MODIFY (UPDATE) and ERASE (DELETE) to operate on these streams as these are considered read only. User Action: Rewrite your code so it does not attempt to modify or erase from these relations.
10.46 – MODSORT
SQL does not allow <str> on sorted streams Explanation: You have used MODIFY or ERASE on a stream whose record selection expression contains a SORTED BY clause. SQL does not permit its equivalent of MODIFY (UPDATE) and ERASE (DELETE) to operate on ordered streams as these are considered read only. User Action: Rewrite your code so it does not attempt to modify or erase from these relations.
10.47 – MULTI_DB_ACCESS
Multiple database access is not supported in a single request Explanation: The query as stated requires more than one database to be referenced in the same generated request. User Action: Correct the query.
10.48 – NESTEDCOMMENT
Nested comment encountered -- ignored Explanation: A nested comment was encountered. Nesting of comments is not supported. User Action: Remove the nested comment.
10.49 – NESTED_MODIFY
Nested modifies on the same context variable are not allowed Explanation: The MODIFY statement references a context variable already being modified. User Action: Correct the MODIFY statement.
10.50 – NODMLOUTPUT
No output file generated due to errors Explanation: No host language output file was generated due to errors detected during processing. User Action: Correct problems associated with any previous errors.
10.51 – NOENDERROR
No END_ERROR was found in this block Explanation: An END_ERROR was expected before this point. User Action: Add the missing END_ERROR.
10.52 – NOENDSTMT
Statement was not terminated by a matching end statement Explanation: A FETCH, FOR, GET, MODIFY, STORE, or ON ERROR was encountered but no matching END_FETCH, END_FOR, END_GET, END_MODIFY, END_STORE, or END_ERROR was found. User Action: Supply a matching end statement.
10.53 – NOMISSVAL
No missing value has been defined for field '<str>' Explanation: A missing value was not found for the named field. User Action: Define a missing value in the database field's definition.
10.54 – NOREQHANDLE
Request handles are not supported by SQL Explanation: Currently SQL maintains the information contained in request handles on its own. There is no equivalent to RDML request handles in SQL. User Action: Programs which have multiple transactions must be rewritten without request handles.
10.55 – NOSUCHREL
Relation '<str>' does not exist Explanation: The named relation does not exist in the currently referenced databases. User Action: Correct the relation reference.
10.56 – NOSUCHSTREAM
Stream name '<str>' was not declared Explanation: The stream name specified in the START_STREAM statement was expected to be a declared stream name but had not previously been declared using a DECLARE_STREAM statement. User Action: Correct the stream name, or add the appropriate DECLARE_STREAM statement.
10.57 – NOTATRANSHANDLE
Transaction handle '<str>' has not been defined Explanation: A transaction handle has not been defined correctly. User Action: Correct the transaction handle.
10.58 – NOTIMPLEMENTED
'<str>' is not implemented Explanation: The named feature is not currently implemented. User Action: Contact your Oracle support representative for assistance.
10.59 – NOTINREDUCE
<str>.<str> does not appear in the REDUCED TO clause. Explanation: When a REDUCED TO clause appears in a record selection expression, the results of referencing any of the fields not contained in the REDUCED TO clause are not defined. Although the preprocessor does not disallow this, the results are not guaranteed. SQL does not permit such usage. User Action: Either add the missing fields to the REDUCED TO clause, or eliminate the reference to these fields.
10.60 – NOTLOCAL
Field referenced '<str>' in SORTED BY or REDUCED TO is not in the context of query Explanation: The database field referenced with a SORTED BY or REDUCED TO clause is not defined within this query. This will have the effect of sorting, or projecting to a constant value which is of little value. User Action: Examine the query and ensure that you have used the correct context variable, and field name.
10.61 – NOTRHANDLE
Transaction handles are not supported by SQL Explanation: Currently SQL does not support multiple transactions. User Action: Programs which have multiple transactions must be rewritten so that all concurrent database requests occur in the same transaction.
10.62 – NOTRHANDLES
No user transaction handles detected in this module; consider retranslation with /NOTRANS_HANDLES Explanation: You have converted an RDML Module with no user-specified transaction handles. This message only appears if the /TRANS_HANDLES qualifier is in effect by default. User Action: You may want to re-convert your code using the /NOTRANS_HANDLES qualifier. This will eliminate some of the overhead of using DDTM with your database code. If you do want the extra DDTM arguments and procedure calls, this message can be suppressed by specifying /TRANS_HANDLES explicitly in the command line.
10.63 – NOTSSFIELD
'<str>' is not a segmented string field name Explanation: The preprocessor expected a field of type segmented string and the specified field was not of that type. User Action: Change the field name to one of type segmented string, or change the syntax so that a segmented string is not expected.
10.64 – NOTSTORECV
'<str>' is not a STORE or MODIFY context variable Explanation: The context handle must be one specified on an enclosing STORE or MODIFY statement. User Action: Change the context handle to the one specified in the enclosing STORE or MODIFY statement.
10.65 – NOWORKINBLK
No items stored or modified in STORE or MODIFY block Explanation: No fields are stored or modified by the current STORE or MODIFY statement. User Action: Add fields to be modified or stored if appropriate.
10.66 – NO_CDD_META_DATA
unable to load metadata from the CDD <str> <str> Explanation: An unexpected error was detected while accessing the CDD and attempting to load metadata. The provided CDD pathname may be incorrect or the actual information in the CDD may be inconsistent with the expected protocol. User Action: use a correct CDD pathname, insure that the CDD metadata is correct.
10.67 – NO_DATABASE
No DATABASE statement has been processed prior to this point Explanation: A DATABASE statement must be the first statement processed. User Action: Place the DATABASE statement in the appropriate position in the source code.
10.68 – NO_LANGUAGE
No Language has been specified on the command line or input file Explanation: A language must be specified as a command line qualifier or by using the default file extensions. User Action: Use a command qualifier or file extension to specify what language is to be processed.
10.69 – NO_META_DATA
Cannot access compile-time metadata in file '<str>' Explanation: An unexpected error was detected while accessing the database and attempting to load metadata. The provided filename may be incorrect or the actual information in the database may be inconsistent with the expected format. User Action: Use a correct database filename, insure that the database metadata is correct.
10.70 – ON_ERROR
ON_ERROR should be ON ERROR Explanation: An ON_ERROR was found when the correct syntax is ON ERROR, with no underscore. RDML issues the warning message, but assumes that an ON ERROR clause was encountered. User Action: Change the ON_ERROR to ON ERROR, removing the extraneous underscore, and preprocess the source again.
10.71 – PARSING
Error occurred during parsing Explanation: An error was detected during the parsing phase of precompilation. Additional messages identify the specific problem more precisely. User Action: Follow the actions suggested for the messages that identify the specific problem.
10.72 – PREPARE
PREPARE statement not translated to VAX SQL Explanation: The PREPARE statement has no effect in RDML, and is not converted to SQL. User Action: Although not necessary, the statement may be removed from the source code along with ON ERROR clauses.
10.73 – PSECTNOEFF
Linkage type PROGRAM_SECTIONS is not supported with EPASCAL -- using linkage type GLOBAL_SYMBOLS Explanation: The /LINKAGE=PROGRAM_SECTIONS qualifier only applies to VAX Pascal and VAX C. With RDML/EPASCAL, the GLOBAL_SYMBOLS linkage type is always used, regardless of what is specified on the command line. User Action: This is an informational message to remind the EPASCAL user that only linkage type GLOBAL_SYMBOLS is available with EPASCAL. To suppress the message, simply specify the /LINKAGE=GLOBAL_SYMBOLS qualifier.
10.74 – RDML_ABORT
Fatal Preprocessor Utility Error Aborted because: <str> Explanation: A fatal internal error has occurred. User Action: Contact your Oracle support representative for assistance. You will need to provide adequate information to reproduce the problem, including, but not limited to: a complete description of the problem, a complete description of the database, the source code of the module that is causing the problem. and any related output files such as listings that may provide further information.
10.75 – READY
READY statement has no VAX SQL equivalent Explanation: The READY statement has no SQL equivalent, and is not converted to SQL. User Action: Remove the statement from the source.
10.76 – READ_ONLY
Invalid attempt to update a read only field '<str>' Explanation: A COMPUTED BY field can only be read and can not be updated in STORE, or MODIFY statement. User Action: Do not reference the read only field in the STORE, or MODIFY statements.
10.77 – REL_NOT_IN_DB
Relation '<str>' does not exist in database '<str>' Explanation: The named relation does not exist in the named database. User Action: Correct the relation name or add the relation to the database.
10.78 – SEGSTRACCESS
Segmented Strings Should be accessed through FOR or STORE Explanation: Detected access to a segmented string field outside a normal segmented string FOR or STORE statement. User Action: Check to make sure that segmented string fields are being correctly accessed.
10.79 – SIGARGS
Signal arguments: <str> PC = <num>, PSL = <num> Explanation: This message contains information related to the RDML_ABORT message, when an unexpected preprocessor exception is encountered. It displays the signal argument vector contents for the exception. User Action: See the RDML_ABORT message code.
10.80 – SORTSTAT
SORTED BY found in a boolean or statistical expression Explanation: A SORTED BY clause was found in a record selection expression associated with any of the operators ANY, AVERAGE, COUNT, MAX, MIN, TOTAL, and UNIQUE. Although strictly not incorrect, the order of records in a stream does not affect the value of these operations. User Action: In most cases, this message results from the SORTED BY clause being incorrectly associated with a nested subexpression. Placing parentheses around the subexpression will help RDML properly group the clause with the primary expression. Because of SQL syntax restrictions, the side effects of sort clauses on statistical expressions in RDML cannot be reproduced.
10.81 – SOURCE_REQUIRED
A Source file specification is required on the command line Explanation: A source file is required for further processing. User Action: Provide a source file specification on the command line.
10.82 – SUMMARY
Completed with <num> Error(s), <num> warning(s), and
<num> informational message(s)
Explanation: A brief summary of messages generated during
processing.
User Action: None.
10.83 – SYNTAX
Syntax Error Explanation: A syntax error has been detected. User Action: Correct the syntax error.
10.84 – TRUNCPREFIX
Prefix too long; truncated to <str> Explanation: The value specified for the /GLOBAL_PREFIX qualifier, or the file name if /GLOBAL_PREFIX was not specified, is longer than the maximum allowable size. If the prefix is not truncated, global symbols are likely to share their first 31 characters and the linker will see them as the same symbols. User Action: Specify a shorter value for /GLOBAL_PREFIX.
10.85 – TYPE_CONFLICT
There is a type conflict between elements of this RSE Explanation: A potential type conflict has been detected, but the database system may be able to perform the conversion. User Action: Make sure that the statement causing this message is performing the function that is intended.
10.86 – UNBALSTRM
Undeclared stream '<str>' has no END_STREAM statement Explanation: A START_STREAM statement was seen for an undeclared stream, but a matching END_STREAM statement was not seen. User Action: Use a declared stream, or balance the START_STREAM statement for the undeclared stream with an END_STREAM statement.
10.87 – UNTERMPASNUMLIT
Missing closing apostrophe in Pascal binary, octal or hexadecimal numeric literal -- inserted Explanation: A Pascal binary (%b'010101'), octal (%o'1234'), or hexadecimal (%x'12AB') numeric literal was found not to have been terminated properly. User Action: Properly terminate the numeric literal with an apostrophe.
10.88 – WILDHASSEGSTR
This wild-card assignment includes a segmented string field Explanation: When a wild-card assignment occurs in RDML, all fields in the relation are copied. In SQL, it is not possible to retrieve segmented string handles from the database, therefore these fields will not have valid values. The converter generates an assignment for the field, but it will have no effect. User Action: It is recommended that the program be rewritten without segmented string identifiers directly accessed.