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.
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.
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;
}
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.
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
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.
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.
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.
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;
}
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.
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
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
--------------------------
3 – AVERAGE
Determines the arithmetic mean of values for all records
specified by a record selection expression.
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
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;
}
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.
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
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.
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.
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.
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"));
}
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.
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
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.
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.
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.
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;
}
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.
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>
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.
6 – COMMIT
Ends a transaction and makes permanent all changes you made
during that transaction.
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.
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;
}
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.
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
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 – 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.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
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.
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.
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;
}
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.
8.2 – Format
(B)0[mcontaining-clause =
qqq> value-expr qqqwqq>qqqqqqqwqq> [4mCONTAINING[m qqq> value-expr qqq>
mqq> [4mNOT[m qqj
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.
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.
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
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;
}
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.
9.2 – Format
(B)0[mcontext-variable = qqqqqqq> identifier qqqq>
9.2.1 – Format arguments
identifier A valid alphanumeric host language
identifier.
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.
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
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;
}
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.
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
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.
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.
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.
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;
}
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.
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
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.
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.
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.
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;
}
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.
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
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.
13 – DB Field
Use the database field value expression to reference database
fields in record selection expressions and in other value
expressions.
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.
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;
}
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.
13.2 – Format
(B)0[mdb-field-expr
qqq> context-var qqq> . qqq> field-name qqq>
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.
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.
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;
}
}
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.
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;
}
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.
14.3 – Format
(B)0[mdb-handle = qqqqq> host-variable qqqqq>
14.3.1 – Format arguments
host-variable A valid alphanumeric host language
variable.
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.
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.
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.
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;
}
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.
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
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.
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.
17.1 – Examples
The following programs demonstrate the use of the declared
END_STREAM statement.
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;
}
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.
17.2 – Format
(B)0[m
[4mEND_STREAM[m qqqqqq> stream-name qqqqqqqqqk
lqqqqqqqqq<qqqqqqqqqqqqqqqj
mqwqqqqqqqqqqqqqqwqqqqqqqqqq>
mq> on-error qqj
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.
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.
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.
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;
}
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.
18.2 – Format
(B)0[m
[4mSTART_STREAM[m qqqqqqqq> declared-stream-name qqqqqqk
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqqqqqqwqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqq>
mq> on-error qqj
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.
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.
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".
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;
}
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.
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>
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.
20 – ERASE
Deletes a record from a relation or open stream.
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
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;
}
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.
20.2 – Format
(B)0[m[4mERASE[m qqqq> context-var qqqwqq>qqqqqqqqqqqqqwqq>
mqq> on-error qqqj
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.
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.
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
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;
}
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.
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
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.
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.
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
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;
}
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.
22.2 – Format
(B)0[m[4mFINISH[m qqqwqqq>qqqqqqqqqqqqqqqwqwqq>qqqqqqqqqqqqqwqq>
mqwq> db-handle qwqqj mqq> on-error qqqj
mqqqqq , <qqqqqj
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.
23 – FIRST Clause
The FIRST clause specifies the maximum number of records in a
record stream formed by a record selection expression.
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.
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;
}
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.
23.2 – Format
(B)0[mfirst-clause =
qqqqqq> [4mFIRST[m qqqq> value-expr qqqq>
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.
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.
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.
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;
}
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.
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
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.
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.
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".
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;
}
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.
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
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.
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.
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.
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;
}
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.
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
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.
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.
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.
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;
}
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.
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
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.
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.
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".
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;
}
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.
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
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.
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.
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.
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;
}
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.
29.2 – Format
(B)0[mmatching-clause =
qqq> value-expr qqqwqq>qqqqqqqwq> [4mMATCHING[m qqq> match-expr qqqq>
mqq> [4mNOT[m qqj
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
30 – MAX
Returns the highest value for a value expression for all records
specified by a record selection expression.
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
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;
}
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.
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
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.
31 – MIN
Returns the lowest value for a value expression for all records
specified by a record selection expression.
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".
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;
}
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.
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
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.
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.
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.
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;
}
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.
32.2 – Format
(B)0[mmissing-cond-expr
qqq> db-field-expr qqq>qqqqwqq>qqqqqqqwqq> [4mMISSING[m qqq>
mqq> [4mNOT[m qqj
32.2.1 – Format arguments
db-field-expr A database field value expression. A field
name qualified with a context variable.
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.
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".
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;
}
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.
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
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.
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.
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.
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();
}
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.
34.2 – Format
(B)0[mon-error =
[4mON[m [4mERROR[m qqwqqq> statement qqqqwqqq> [4mEND_ERROR[m
mqqqqqqqqq<qqqqqqqqqj
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.
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.
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.
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;
}
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.
35.2 – Format
(B)0[mdb-key =
qqq> context-var qqq> . qqq> [4mRDB$DB_KEY[m qq>
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.
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.
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
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.
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".
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;
}
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.
37 – READY
Explicitly declares your intention to access one or more
databases and causes an attach to the database.
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
37.1.1 – C Example
#include <stdio.h>
DATABASE PERS = FILENAME "PERSONNEL";
.
.
.
main ()
{
READY PERS;
.
.
.
FINISH PERS;
}
37.1.2 – Pascal Example
program empupdate;
DATABASE PERS = FILENAME 'PERSONNEL';
.
.
.
begin
READY PERS;
.
.
.
FINISH PERS;
end.
37.2 – Format
(B)0[m[4mREADY[m qqqwqqqq>qqqqqqqqqqqqqqwqqqwqqqq>qqqqqqqqqqqqqwqqqqq>
mqwqq> db-handle qwqj mqqqq> on-error qqqj
mqqqqqq , <qqqqqj
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.
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.
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.
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;
}
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.
38.2 – Format
(B)0[mreduce-clause =
qqqqq> [4mREDUCED[m TO qqqwqqqqqq> db-field qqqwqqq>
mqqqqqq<qqqq , <qqqqqj
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.
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.
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.
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;
}
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.
39.2 – Format
(B)0[mrelation-clause =
qq> context-var qqq> [4mIN[m qqwqq>qqqqqqqqqqqqqqqqqqwqq> relation-name qq>
mqq> db-handle qq> . qj
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.
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.
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.
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.
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;
}
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.
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)
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
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.
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;
}
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.
41.3 – Format
(B)0[mrequest-handle = qq>( q> [4mREQUEST_HANDLE[m qqqqqqq> host-variable qqqqq> ) qq>
41.3.1 – Format arguments
host-variable A valid host language variable.
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.
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".
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;
}
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.
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
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.
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.
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
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.
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
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;
}
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.
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
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.
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.
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
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);
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);
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
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.
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.
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.
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;
}
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.
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
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.
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.
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>
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.
48 – TOTAL
Returns the sum of the values specified by a record selection
expression. The value expression must be a numeric data type.
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
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;
}
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.
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
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.
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.
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.)
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;
}
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.
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
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.
50 – STORE Seg Strings
Stores a segmented string into a segmented string field. Must be
nested within a regular STORE statement.
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.
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;
}
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.
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>
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.
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.
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".
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;
}
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.
51.2 – Format
(B)0[mtransaction-handle = qq> ( qq> [4mTRANSACTION_HANDLE[m qqq> host-var q> ) qqq>
51.2.1 – Format arguments
host-var A valid host language variable.
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.
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
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;
}
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.
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
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.
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.
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.
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;
}
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.
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
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.
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.
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.
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.
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
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;
}
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.
55.2 – Format
(B)0[mwith-clause =
qqqqqq> [4mWITH[m qqq> conditional-expr qqq>
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.