! Copyright © Oracle Corporation 1995. All Rights Reserved. 1 RDML72 The Relational Data Manipulation Language (RDML) is composed of statements that can be embedded in C and Pascal programs. These programs can be processed by the RDML preprocessor, which converts the RDML statements into a series of equivalent DSRI calls to the database. Following a successful precompilation, the programmer can submit the resulting source code to the host language compiler. 2 More The RDML C and Pascal preprocessors do not allow nested comments and identify such comments with an information message. RDML does not allow comments because they are not allowed by the ANSI/ISO C standard or by VAC C (when you use the /STANDARD= PORTABLE qualifier). 2 Distributed_Transactions You may use the two-phase commit protocol in RDML to distribute transactions. To use the two-phase commit protocol with RDML, you must either recompile any existing application programs that were compiled with Oracle Rdb V3.1 or earlier, or you must write new application programs. With RDML, the two-phase commit protocol is not the default. RDML provides the following ways for application programs to use the two-phase commit protocol: o By implicitly calling DECdtm system services calls in either of two ways: - Using the /DISTRIBUTED_TRANSACTION qualifier in the precompiler command line - Using the DISTRIBUTED_TRANSACTION keyword in the START_TRANSACTION statement o By explicitly calling the DECdtm system service calls and using variables to pass the value of the distributed transaction identifier (TID) If your application starts a distributed transaction that includes other read/write database management products that support the two-phase commit protocol, your application must explicitly invoke the DECdtm system service calls. For example, if your application starts a distributed transaction using Oracle Rdb and Oracle CODASYL DBMS, your application must explicitly call SYS$START_TRANS and SYS$END_TRANS. In addition, you must use the full DISTRIBUTED_TRANSACTION clause in the START_TRANSACTION statement. The implementation of RDML with distributed transactions is very similar to using RDBPRE with distributed transactions. See the "Oracle Rdb Guide to Distributed Transactions" for a complete description of using RDBPRE with distributed transactions and the "RDML Reference Manual" and the "Guide to Using RDO, RDBPRE, and RDML" for more information. 2 Preprocessor The RDML preprocessor can operate on an OpenVMS system that has Oracle Rdb installed. To invoke the RDML preprocessor, you must first define a symbol. For example: $ RDML :== $RDML $ RDML/PASCAL SALARY_PLAN.RPA You must preprocess any programs that contain RDML statements before processing them with the C or Pascal compiler. 3 Language_Selection The RDML preprocessor will preprocess C and Pascal files that contain RDML statements. There are two methods that allow you to specify the language type of the source file: 1. Use a language qualifier when invoking the RDML preprocessor 2. If you do not specify a language qualifier, the RDML preprocessor will attempt to determine what language the source file contains by looking at the file extension supplied in the command line. If the file extension is ".RC", RDML will assume the file is a C file. If the file extension is ".RPA", the RDML preprocessor will assume the file is a Pascal file. An error message will be produced if the RDML preprocessor can not determine what language is to be associated with a specified input file. 3 /LISTING Specifies a file for the error listing file. The default file types are ".LC" for VAX C and DEC C, and ".LPA" for VAX Pascal and DEC Pascal. If /LISTING is not specified, no listing file will be created. Format: /LISTING[=file-spec] /NOLISTING (Default) Example: $ RDML/LISTING EMPUPDATE.REP 3 /OUTPUT Specifies a file for the host language output. The default file types are ".C" for VAX C and DEC C and ".PAS" for VAX Pascal, and DEC Pascal. The RDML preprocessor will create this file only following a successful precompilation. Format: /OUTPUT[=file-spec] (Default) /NOOUTPUT Example: $ RDML/OUTPUT=EMPUPDATE.PAS EMPUP.REP 3 /C Specifies that the input file is a VAX C or DEC C source file. If the /C qualifier is used on the command line, the RDML preprocessor will assume a file extension of ".RC" unless you specify otherwise in the file specification. Format: /C Example: $ RDML/C EMPUP 4 More The following restrictions affect the use of C host variables: o Host variables in C of the form *host_variable that appear directly after a host variable in an RSE are not detected correctly. For instance, *year_ptr is interpreted incorrectly as part of the host variable emp_id in the following example: FOR D IN DEGREES WITH D.EMPLOYEE_ID = emp_id *year_ptr = D.YEAR_GIVEN; END_FOR; The workaround for this restriction is to use braces around the host language statements, or parentheses around the WITH clause. For example, either of the following RSEs will preprocess correctly: FOR D IN DEGREES WITH D.EMPLOYEE_ID = emp_id { *year_ptr = D.YEAR_GIVEN; } END_FOR; FOR D IN DEGREES WITH (D.EMPLOYEE_ID = emp_id) *year_ptr = D.YEAR_GIVEN; END_FOR; o Although host variables with parentheses are permitted in non-RDML statements, they cannot be used as host variables in RDML statements. For example, the following syntax is not permitted: FOR E IN EMPLOYEES WITH E.LAST_NAME = (name)[offset].element . . . END_FOR; However, the following is permitted: FOR E IN EMPLOYEES WITH E.LAST_NAME = name[offset].element . . . END_FOR; The C string continuation character (a backslash, \) in string constants followed immediately by a new line is not recognized by RDML. Do not use this method of continuation with this version of RDML. RDML generates an error if it finds a string constant that does not begin and end on the same line within quotation marks. For example, the following C lines cause a syntax error: printf ("abcdefg\ hijklmnopqrstuvwxyz"); 3 /PASCAL Specifies that the input file is a DEC Pascal or VAX Pascal source file. If the /PASCAL qualifier is used on the command line, the RDML preprocessor will assume a file extension of ".RPA" unless you specify otherwise in the file specification. Format: /PASCAL Example: $ RDML/PASCAL SALARY_PLAN On Oracle Rdb for OpenVMS Alpha, Pascal users will need to compile /ALIGN=VAX because RDML/PASCAL generates code that is byte aligned. This is a permanent restriction. 4 More o RDML/Pascal does not generate the correct length for a character string value expression in the form: FOR E IN EMPLOYEES WITH E.LAST_NAME = ('T' | host_variable) ... some statements ... END_FOR; This statement generates an error from VAX Pascal, such as: 00470 0 0 RDB$PORT_FIELD_0 : VARYING[0] OF CHAR; 1 %PASCAL-E-MAXLENRNG, (1) Max-length must be in range 1..65535 %PASCAL-E-ENDDIAGS, Pascal completed with 1 diagnostic To avoid this problem, construct the value needed before issuing the query, using a method such as the following: host_variable1 := 'T' + host_variable2; FOR E IN EMPLOYEES WITH E.LAST_NAME = host_variable1 ... some statements ... END_FOR; This method is recommended for all RDML statements when possible because it generally improves the performance of the query. o RDML/Pascal does not accept all possible valid Pascal host language variables, and it issues the following error if one it does not accept is encountered: %RDML-W-HOST_VARIABLE, error detected in host variable syntax The set of possible values is limited to the syntax described in the RDML Reference Manual, with the restriction that the expression allowed in an array index must be a simple name or expression. The following illustrates an unacceptable statement: FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID = emparray[empstruct.index] ... some statements... END_FOR; In the preceding example, empstruct.index is a reference to a structure member. To avoid this problem, you would assign empstruct.index to an intermediate variable and use that variable in the FOR statement WITH clause. 3 /LINKAGE This qualifier allows you to determine whether RDML will communicate between separate modules with program sections or global symbols by specifying either program section or global symbol linkage methods. Format: /LINKAGE=PROGRAM_SECTIONS (Default) /LINKAGE=GLOBAL_SYMBOLS 4 PROGRAM_SECTIONS When you use this qualifier, RDML communicates between separate modules with program sections. Using this qualifier will allow you to link RDML modules with SQLPRE and RDBPRE modules. 4 GLOBAL_SYMBOLS When you use this qualifier, RDML communicates between separate modules with global symbols. Rdb recommends that you only use this qualifier if you have problems linking with program sections. 3 /DEFAULT_TRANSACTIONS The /[NO]DEFAULT_TRANSACTIONS qualifier controls whether or not the RDML preprocessor will generate code to support automatic database attachment and automatic transactions by default. Format: /DEFAULT_TRANSACTIONS (Default) /NODEFAULT_TRANSACTIONS (Recommended) When you use the /DEFAULT_TRANSACTIONS qualifier, RDML allows you to issue a query, without explicitly issuing a READY statement or START_TRANSACTION statement first. If you do not issue these statements explicitly, RDML will attach to the database and start a READ_ONLY transaction for you when it encounters the first DML statement. Furthermore, RDML allows you to detach from a database without first completing (committing or rolling back) any transaction that is attached to the database you are finishing. RDML will commit any outstanding transaction for you. The /DEFAULT_TRANSCTIONS qualifier causes the RDML preprocessor to generate additional (often unnecessary) code that may incur a significant amount of overhead because RDML must check the state of the database and transactions as each DML statement is processed. The /NODEFAULT_TRANSACTIONS qualifier eliminates this overhead by requiring you to explicitly READY a database and begin and end transactions. When you use the /NODEFAULT_TRANSACTIONS qualifier RDML does not check the state of the database and transactions as each RDML statement is processed. In fact, if you do not close a transaction prior to issuing a FINISH statement for the database with which the transaction is associated, RDML will issue the error message: %RDB-F-OPEN_TRANS. Rdb recommends that you always use the /NODEFAULT_TRANSACTIONS qualifier to reduce overhead, maximize performance and enforce good programming practices. However, the default for this qualifier is /DEFAULT_TRANSACTIONS, as this causes the behavior that RDML has exhibited prior to Oracle Rdb V3.0. 3 /INITIALIZE_HANDLES The /[NO]INITIALIZE_HANDLES qualifier lets you control whether RDML will initialize RDML-supplied database, transaction, and request handles. Use of the /NOINITIALIZE_HANDLES qualifier allows you to link a main image against a shareable image and share handles between the two. Format: /INITIALIZE_HANDLES (Default) /NOINITIALIZE_HANDLES These qualifiers have no effect on whether or when handles are cleared in the generated code; they only control initialization of handles in declarations. Furthermore, they only affect database, transaction and request handles that RDML declares. User-specified transaction and request handles will not be initialized when you use the /INITIALIZE_HANDLES qualifier. RDML will initialize database handles when their scope is GLOBAL or LOCAL. EXTERNAL scope database handles are never initialized. 3 /DISTRIBUTED_TRANSACTION For application programs that were written under Oracle Rdb V3.1 or earlier, use the two-phase commit protocol simply by recompiling your programs. You must use the /DISTRIBUTED_TRANSACTION qualifier in the precompiler command line. When you do this, Oracle Rdb invokes the DECdtm system service calls for your application. For example, to recompile the C program SAMPLE.RC with the RDML preprocessor, use the following command: $ RDML :== $RDML/C $ RDML SOURCE FILE> SAMPLE /DISTRIBUTED_TRANSACTION 3 /QUADWORD The /QUADWORD qualifier allows RDML to translate the quadword data type into a double or an unformatted buffer (RDML$QUAD_ TYPE). Format: /QUADWORD = DOUBLE (Default) /QUADWORD = UNFORMATTED The default is DOUBLE, which is the same as in previous versions. 3 /NEGSCALED_NUMERIC The /NEGSCALED_NUMERIC qualifier enables RDML to convert the negative scaled numeric to floating (REAL or DOUBLE), depending upon the scale and data type, or to the original data type ignoring negative scale. Format: /NEGSCALED_NUMERIC = FLOATING (Default) /NEGSCALED_NUMERIC = UNSCALED The default is FLOATING, which is the same as in previous versions. 3 /DATE_TYPE The /DATE_TYPE qualifier determines whether RDML generates the DATE data type as an empty record or not as an empty record, which is a pair of LONGWORD. This qualifier is used only when preprocessing a Pascal program. Format: /DATE_TYPE = EMPTY_RECORDS (Default) /DATE_TYPE = NOEMPTY_RECORDS The default is EMPTY_RECORDS. 2 Compiling You can compile RDML/PASCAL and RDML/C preprocessed programs like any other Pascal or C program. However, Rdb recommends that you always use the /G_FLOATING qualifier or an equivalent mechanism when you invoke the compiler to process RDML-generated source files. For example: DEC Pascal or VAX Pascal $ PASCAL/G_FLOATING myprogram.pas DEC C or VAX C $ CC/G_FLOATING myprogram.c 2 Linking All RDML-generated applications must be linked with the RDML Run- Time Library (SYS$LIBRARY:RDMLRTL.OLB). This library contains code for various functions and procedures needed for the code generated by RDML. To link RDML applications, use the following line in an options file or command file. SYS$LIBRARY:RDMLRTL.OLB/LIBRARY For example, if you include the preceding line in an options file named RDMLOPT.OPT, you can link a preprocessed and compiled RDML /PASCAL or RDML/C program by entering: $ LINK myprogram, rdmlopt/OPTIONS 2 Release_Notes To see the current Release Notes for Oracle Rdb (which includes information about RDML), type or print the following file: SYS$HELP:RDBvvu.RELEASE_NOTES where vv = version u = update For example: SYS$HELP:RDB071.RELEASE_NOTES 2 Statements 3 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. 4 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. 5 C_Example #include 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; } 5 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. 4 Format (B)0any-clause = qqqwqqq>qqqqqqqqwqq> ANY qqqwqq>qqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqk mqqq> NOT qqqj mqq> handle-options qj x x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x mqqqqqqqqqqqqqqqqqqqqqq> rse qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq> (B)0handle-options = q> ( qwqqqqqq> REQUEST_HANDLE qqqqq> var qqqqqqqqwq> ) q> tqqqqqq> TRANSACTION_HANDLE qqq> var qqqqqqqqu mqqqqqq> REQUEST_HANDLE q> var q> , qqqqqqqk x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x mqqqq> TRANSACTION_HANDLE q> var qqqqqqqqqqqj 5 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. 3 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. 4 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. 5 C_Example #include 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; } 5 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. 4 Format (B)0arith-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 5 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. 4 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 5 C_Example #include 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; } 5 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. 4 Format (B)0 qq> AVERAGE qqqqwqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqk mqq> handle-options qqj x x lqqqqqqqqqqqqqqqqqqqqqqqqqqq OF qqqqqqqqq> rse qqqqqqqqqqq> (B)0handle-options = q> ( qwqqqqqq> REQUEST_HANDLE qqqqq> var qqqqqqqqwq> ) q> tqqqqqq> TRANSACTION_HANDLE qqq> var qqqqqqqqu mqqqqqq> REQUEST_HANDLE q> var q> , qqqqqqqk x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x mqqqq> TRANSACTION_HANDLE q> var qqqqqqqqqqqj 5 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. 3 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 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. 5 C_Example #include 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")); } 5 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 Format (B)0BASED ON qqqqqwqq>qqqqqqqqqqqqqqqqqqqqwqqqqqqqqk mqq> db-handle qq> . qqqj x x lqqqqqqqqqqqqqqqqqqqqqq relation-name qqqq> . qqqq> field-name 5 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. 3 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. 4 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 C_Example #include 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 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. 4 Format (B)0between-clause = qqqq> value-expr qqqqqwqq>qqqqqqqwqqq> BETWEEN qqqqk mqq> NOT qqj x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq value-expr qqq> AND qqq> value-expr qqqqqqq> 5 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. 3 COMMIT Ends a transaction and makes permanent all changes you made during that transaction. 4 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. 5 C_Example #include 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; } 5 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. 4 Format (B)0COMMIT qqwqq>qqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqwk mqq> ( qq> TRANSACTION_HANDLE qq> var qq>) qjx x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x mqqqqqqqqqqqwqq>qqqqqqqqqqqqqqqwqqqqqqqqqqqqqq> mqq> on-error qqqj 5 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. 3 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. 4 Format (B)0qqqwq> conditional-expr qqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqwqqqqqq> tqqqqqqqqqqqqqqqqqqqqq> NOT qq> conditional-expr qqqqu tq> conditional-expr q> AND qq> conditional-expr qqqqu mq> conditional-expr q> OR qq> conditional-expr qqqqj (B)0conditional-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)0rel-operator = qqqqwqq> EQ qqqwqqqqq> tqq> = qqqu tqq> NE qqqu tqq> <> qqqu tqq> GT qqqu tqq> > qqqu tqq> GE qqqu tqq> >= qqqu tqq> LT qqqu tqq> < qqqu tqq> LE qqqu mqq> <= qqqj 3 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. 4 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. 5 C_Example #include 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; } 5 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. 4 Format (B)0containing-clause = qqq> value-expr qqqwqq>qqqqqqqwqq> CONTAINING qqq> value-expr qqq> mqq> NOT qqj 5 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. 3 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. 4 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 5 C_Example #include 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; } 5 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. 4 Format (B)0context-variable = qqqqqqq> identifier qqqq> 5 Format_arguments identifier A valid alphanumeric host language identifier. 3 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. 4 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 5 C_Example #include 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; } 5 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. 4 Format (B)0 qqq> COUNT qqqqqqqqq>qqqqqqqwqq>qqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqk mqq> handle-options qqqj x x lqqqqqqqqqqqqqqqqqqqqqqqqqqq OF qqqqqqq> rse qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq> (B)0handle-options = q> ( qwqqqqqq> REQUEST_HANDLE qqqqq> var qqqqqqqqwq> ) q> tqqqqqq> TRANSACTION_HANDLE qqq> var qqqqqqqqu mqqqqqq> REQUEST_HANDLE q> var q> , qqqqqqqk x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x mqqqq> TRANSACTION_HANDLE q> var qqqqqqqqqqqj 5 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. 3 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. 4 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. 5 C_Example #include 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; } 5 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. 4 Format (B)0cross-clause = qqwqq> CROSS qqq> relation-clause qqwqq>qqqqqqqqqqqqqqqqqqqqqqqqqqqwqq> x mqq> OVER qwq> field-name qqwqwj x mqqqqq , DATABASE PERS = FILENAME "PERSONNEL"; main () { READY PERS; START_TRANSACTION READ_ONLY; /* perform some action on the database */ COMMIT; FINISH; } 5 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. 4 Format (B)0INVOKE DATABASE qqqqqqqqwqqqqqqqqqqqqq>qqqqqqqqqqqwqqqqqqqqqqqqqqqqk mqq> db-handle-options qqqj x x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqwqq> PATHNAME qqq> path-name qqwqqk mqq> COMPILETIME qqqj mqq> FILENAME qqq> file-spec qqj x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqk mqq> RUNTIME FILENAME qqqqwqqqq> file-spec qqqqqqqqwqqj x mqqqq> host-variable qqqqj x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj mqqqqwqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqk mq> DBKEY SCOPE IS qqwq> COMMIT qqqqqqqu x mq> FINISH qqqqqqqj x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj mqwqqqqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqwqqq> mq> REQUEST_HANDLE SCOPE IS qwq> DEFAULT qqwqqj mq> FINISH qqqj (B)0db-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)0handle-scope = qqwqqqqqwqqwqqq> GLOBAL qqqqqqqqwqqwqqqqqwq> m> [ qj x x m> ] qj tqqq> EXTERNAL qqqqqqu x x mqqq> LOCAL qqqqqqqqqj 5 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. 3 DB_Field Use the database field value expression to reference database fields in record selection expressions and in other value expressions. 4 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. 5 C_Example #include 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; } 5 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. 4 Format (B)0db-field-expr qqq> context-var qqq> . qqq> field-name qqq> 5 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. 3 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. 4 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 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; } } 4 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. 5 C_Example #include DATABASE PERS = FILENAME "PERSONNEL"; main () { READY PERS; START_TRANSACTION READ_ONLY; /* perform some database actions */ COMMIT; FINISH PERS; } 5 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. 4 Format (B)0db-handle = qqqqq> host-variable qqqqq> 5 Format_arguments host-variable A valid alphanumeric host language variable. 3 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. 3 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. 4 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. 5 C_Example #include #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; } 5 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. 4 Format (B)0 DECLARE_STREAM qqqqqwqq>qqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqk mqq> handle-options qj x x lqqqqqqqqqqqqqqqqqqqqqqqq declared-stream-name qqqqq> USING qqqq> rse qq> (B)0handle-options = q> ( qwqqqqqq> REQUEST_HANDLE qqqqq> var qqqqqqqqwq> ) q> tqqqqqq> TRANSACTION_HANDLE qqq> var qqqqqqqqu mqqqqqq> REQUEST_HANDLE q> var q> , qqqqqqqk x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x mqqqq> TRANSACTION_HANDLE q> var qqqqqqqqqqqj 5 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. 3 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. 4 Examples The following programs demonstrate the use of the declared END_STREAM statement. 5 C_Example #include #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; } 5 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. 4 Format (B)0 END_STREAM qqqqqq> stream-name qqqqqqqqqk lqqqqqqqqq mq> on-error qqj 5 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. 3 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. 4 Examples The following programs demonstrate the use of the declared START_STREAM statement to open a stream declared with the DECLARE_STREAM statement. 5 C_Example #include #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; } 5 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. 4 Format (B)0 START_STREAM qqqqqqqq> declared-stream-name qqqqqqk lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj mqqqqqqwqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqq> mq> on-error qqj 5 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. 3 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. 4 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". 5 C_Example #include 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; } 5 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. 4 Format (B)0DECLARE_VARIABLE  qqqqqqqqqqqqqqqqqq> host-variable qqqk x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq> AS qqwqq>qqqqqqqqqqqqqqqqqqqwqqk mqq> SAME qj mqq> db-handle qq> . qqj x x lqqqqqqqqqqqqqqqqqqqq relation-name qqq> . qqq> field-name qqq> 5 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. 3 ERASE Deletes a record from a relation or open stream. 4 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 5 C_Example #include 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; } 5 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. 4 Format (B)0ERASE qqqq> context-var qqqwqq>qqqqqqqqqqqqqwqq> mqq> on-error qqqj 5 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. 3 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. 4 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 5 C_Example #include #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; } 5 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. 4 Format (B)0FETCH qq> stream-name qqqqqqqwqq>qqqqqqqqqqqqqwqqqqqk mqq> on-error qqqj x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqq> tqq> AT END qqwqq> statement qqwq> END_FETCH qqu x mqqqqqqq END_FETCH qqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqj 5 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. 3 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. 4 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 5 C_Example #include DATABASE PERS = FILENAME "PERSONNEL"; main() { FOR E IN EMPLOYEES printf ("%s\n", E.LAST_NAME); END_FOR; COMMIT; FINISH; } 5 Pascal_Example program empupdate (input,output); DATABASE PERS = FILENAME 'PERSONNEL'; begin FOR E IN EMPLOYEES writeln (E.LAST_NAME); END_FOR; COMMIT; FINISH; end. 4 Format (B)0FINISH qqqwqqq>qqqqqqqqqqqqqqqwqwqq>qqqqqqqqqqqqqwqq> mqwq> db-handle qwqqj mqq> on-error qqqj mqqqqq , 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; } 5 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. 4 Format (B)0first-clause = qqqqqq> FIRST qqqq> value-expr qqqq> 5 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. 3 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. 4 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. 5 C_Example #include 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; } 5 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. 4 Format (B)0first-from-expr = qqq> FIRST qwqqqqqqqqq>qqqqqqqqqqwq> value-expr qqq> FROM qqqk mq> handle-options qqj x x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x mqqqqqq> rse qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq> (B)0handle-options = q> ( qwqqqqqq> REQUEST_HANDLE qqqqq> var qqqqqqqqwq> ) q> tqqqqqq> TRANSACTION_HANDLE qqq> var qqqqqqqqu mqqqqqq> REQUEST_HANDLE q> var q> , qqqqqqqk x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x mqqqq> TRANSACTION_HANDLE q> var qqqqqqqqqqqj 5 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. 3 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. 4 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". 5 C_Example #include 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; } 5 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. 4 Format (B)0FOR qqqqqqqqqwqq>qqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqk mqq> handle-options qqqj x x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqq rse qqwq>qqqqqqqqqqqwqqqwqq> statement qqwqq> END_FOR mq>on-error qqj mqq ( qwqqqqqq> REQUEST_HANDLE qqqqq> var qqqqqqqqwq> ) q> tqqqqqq> TRANSACTION_HANDLE qqq> var qqqqqqqqu mqqqqqq> REQUEST_HANDLE q> var q> , qqqqqqqk x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x mqqqq> TRANSACTION_HANDLE q> var qqqqqqqqqqqj 5 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. 3 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. 4 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. 5 C_Example #include 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; } 5 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. 4 Format (B)0FOR qq> ss-handle qqq> IN qqqq> ss-field qqqqqwq>qqqqqqqqqqqqwqk mq> on-error qqj x lqqqqqqqqqqqqqqqqqqqqqqqqqqqq assignment qqqqqqqqq> END_FOR 5 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. 3 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. 4 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. 5 C_Example #include 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; } 5 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. 4 Format (B)0get-statement = GET qqwqqqq>qqqqqqqqwqqwq> get-item qqwqqqqq> END_GET qqq> mq> on-error qj mqqqqq ; host-var qqqqqqqq> = qqqqq> value-expr qqqqqqqqqqwqqqq> tqq> record-descr qqqq> = qqqqq> context-var.* qqqqqqqu mqq> host-var qqqqqqqq> = qqqqq> statistical-expr qqqqj 5 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 ".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. 3 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. 4 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". 5 C_Example #include 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; } 5 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. 4 Format (B)0C-host-variable = VMS-name qwqqqqqqwwwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwwq> mq> * qjxtqqq> . qqq> field-identifier qqqqqux xx xx xtqqq> [ qwq> expression qwqq> ] qqqux xx mqqqqqqq , "->" qqqq> field-identifier qqqqjx mqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqwqqq> x tqqq> . qqq> field-identifier qqqqqqqqu x x x x x x tqqq> [ qwq> expression qqqwqq> ] qqu x x x mqqqqqqqq , ^ qwqqqqqqqqqqqqq>qqqqqqqqqwqqqqqqj x x mq> field-identifier qqj x x x mqqqqqqqqqq" 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. 3 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. 4 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. 5 C_Example #include 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; } 5 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. 4 Format (B)0matching-clause = qqq> value-expr qqqwqq>qqqqqqqwq> MATCHING qqq> match-expr qqqq> mqq> NOT qqj 5 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 3 MAX Returns the highest value for a value expression for all records specified by a record selection expression. 4 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 5 C_Example #include 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; } 5 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. 4 Format (B)0 q> MAX qqqqqqqqqwq>qqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqk mq> handle-options qqj x x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqq value-expr qqqq> OF qqqq> rse qqqqqqqqqqqqq> (B)0handle-options = q> ( qwqqqqqq> REQUEST_HANDLE qqqqq> var qqqqqqqqwq> ) q> tqqqqqq> TRANSACTION_HANDLE qqq> var qqqqqqqqu mqqqqqq> REQUEST_HANDLE q> var q> , qqqqqqqk x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x mqqqq> TRANSACTION_HANDLE q> var qqqqqqqqqqqj 5 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. 3 MIN Returns the lowest value for a value expression for all records specified by a record selection expression. 4 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". 5 C_Example #include 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; } 5 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. 4 Format (B)0 qq> MIN qqqqqqqqqqqqqwqq>qqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqk mqq> handle-options qj x x lqqqqqqqqqqqqqqqqqqqqqqqqqqqq value-expr qqqqqq> OF qqqqqq> rse qqqqqqqqqq> (B)0handle-options = q> ( qwqqqqqq> REQUEST_HANDLE qqqqq> var qqqqqqqqwq> ) q> tqqqqqq> TRANSACTION_HANDLE qqq> var qqqqqqqqu mqqqqqq> REQUEST_HANDLE q> var q> , qqqqqqqk x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x mqqqq> TRANSACTION_HANDLE q> var qqqqqqqqqqqj 5 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. 3 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. 4 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. 5 C_Example #include 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; } 5 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. 4 Format (B)0missing-cond-expr qqq> db-field-expr qqq>qqqqwqq>qqqqqqqwqq> MISSING qqq> mqq> NOT qqj 5 Format_arguments db-field-expr A database field value expression. A field name qualified with a context variable. 3 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. 4 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". 5 C_Example #include 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; } 5 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. 4 Format (B)0MODIFY qqq> context-var qqqqq> USING qqqqwqqqqqqqqqqqqqqwqqqqk mqq> on-error qj x x lqqqqqqqqqqqqqqqqqqqqqqqqqqqq statement qqqqqqqqqqqqqqqqqqqqqqqqqqqwqwq> END_MODIFY x x x x x mqq> context-var.* qq> = qq> record-descr qqj x x x mqqqqqqqqqqqqqqqqqqqqqqqqqq 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(); } 5 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. 4 Format (B)0on-error = ON ERROR qqwqqq> statement qqqqwqqq> END_ERROR mqqqqqqqqq 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; } 5 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. 4 Format (B)0db-key = qqq> context-var qqq> . qqq> RDB$DB_KEY qq> 5 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. 3 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. 4 Format (B)0missing-value = qqq> RDB$MISSING qqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqk lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq ( qwq> context-var . field-name qqqqqqqqqqqqqqqwqq> ) qqq> tq> relation-name . field-name qqqqqqqqqqqqqu mq> db-handle . relation-name . field-name qj 5 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. 4 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". 5 C_Example #include 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; } 5 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. 3 READY Explicitly declares your intention to access one or more databases and causes an attach to the database. 4 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 5 C_Example #include DATABASE PERS = FILENAME "PERSONNEL"; . . . main () { READY PERS; . . . FINISH PERS; } 5 Pascal_Example program empupdate; DATABASE PERS = FILENAME 'PERSONNEL'; . . . begin READY PERS; . . . FINISH PERS; end. 4 Format (B)0READY qqqwqqqq>qqqqqqqqqqqqqqwqqqwqqqq>qqqqqqqqqqqqqwqqqqq> mqwqq> db-handle qwqj mqqqq> on-error qqqj mqqqqqq , 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; } 5 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. 4 Format (B)0reduce-clause = qqqqq> REDUCED TO qqqwqqqqqq> db-field qqqwqqq> mqqqqqq 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; } 5 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. 4 Format (B)0relation-clause = qq> context-var qqq> IN qqwqq>qqqqqqqqqqqqqqqqqqwqq> relation-name qq> mqq> db-handle qq> . qj 5 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. 3 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. 4 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. 4 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. 5 C_Example #include 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; } 5 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. 3 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) 4 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 4 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. 5 C_Example #include 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; } 5 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. 4 Format (B)0request-handle = qq>( q> REQUEST_HANDLE qqqqqqq> host-variable qqqqq> ) qq> 5 Format_arguments host-variable A valid host language variable. 3 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. 4 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". 5 C_Example #include 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; } 5 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. 4 Format (B)0ROLLBACK qqwqq>qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqk mqq> ( qq> TRANSACTION_HANDLE qq> var qq> )qj x x lqqqqqqqqqqqqqqqqqqqq mq> on-error qqqqj 5 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. 3 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. 4 Format (B)0rse = qqwqqqqqqqqqqqqqqqqqqqwqqq> relation-clause qqwqqqqqqqqqqqqqqqqqqwqk mqq> first-clause qqj mq> cross-clause qqj x x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq mqq> with-clause qqj mqq> reduce-clause qqj mqq> sort-clause qqj 3 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. 4 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 5 C_Example #include 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; } 5 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. 4 Format (B)0sort-clause = qqq> SORTED BY qqqwqwqqqqqqqqqqqqqqqqwqqqq> db-field qqqqqqwqq> x tqq> ASCENDING qqu x x mqq> DESCENDING qj x mqqqqqqqqqqqqqq , qqqqqqqqqqqqqqqqqqwqk mq> ( qq> TRANSACTION_HANDLE var qq> ) qj x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwqqqqqqq>qqqqqqqqwqk mq> distributed-transaction-flag qj tq> tx-options qqu x mq> on-clause qqqj x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj mqqwqqqqqqqq>qqqqqqqwqq> mqq> on-error qqqj (B)0distributed-transaction-flag = q> DISTRIBUTED_TRANSACTION qwqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqwq> m> DISTRIBUTED_TID distributed-tid qj (B)0tx-options = qqwq> BATCH_UPDATE qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq> x x mqqwqqqqqqqqqqqqqqqqqwqqwqqqqqqqqqqqwqqwqqqqqqqqqqqqqqqqqwqqqqqk x tq> READ_ONLY qqqu tq> WAIT qqqu tq> CONCURRENCY qqu x x mq> READ_WRITE qqqj mq> NOWAIT qj mq> CONSISTENCY qqj x x x x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqq>qqqqqk x mqq> EVALUATING qqwq> evaluating-clause qqqwj x x mqqqqqqqq , qqqqqqqj mqq> RESERVING qqqwq> reserving-clause qqwqqj mqqqqqqqqqqq , ON qwq> db-handle qwq> USING qqq> ( qwq> tx-options qwq> )qwq> x x x mq> DEFAULTS qqqqqj x x mqqqq , qqqqqqqqwqq constraint qqk mq> db-handle qq> . qq> qj x x lqqqqqqqqqqqqqqq AT qqqwqq> VERB_TIME qqqwqqqq> mqq> COMMIT_TIME qj (B)0reserving-clause = qqqwqqwqqqqqqqq>qqqqqqqqqqqqwqqq> relation-name qqqqqqqqqqqwqqk x mq> db-handle qq> . FOR qqwq>qqqqqqqqqqqqqqqwqqqwqq> READ qqqwqqqqqqqqqqqqqqk tq> EXCLUSIVE qqqqu mqq> WRITE qqj x tq> PROTECTED qqqqu x mq> SHARED qqqqqqqj x x lqqqqqqqqqqqqqqqqqqqqqq tq> WITH AUTO_LOCKING qqqu mq> WITH NOAUTO_LOCKING qj 5 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. 3 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. 4 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. 5 C_Example #include 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; } 5 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. 4 Format (B)0starting-with-clause = qqq> value-expr qqwqq>qqqqqqqqwqq> STARTING WITH qqq> value-expr qq> mqq> NOT qqqj 5 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. 3 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. 4 Format (B)0statistical-expr = qqqwqq> MAX qqqqqwqq> value-expr qwqqqqqqqqqqqqqqqqqqqqqk tqq> MIN qqqqqu x x tqq> TOTAL qqqu x x tqq> AVERAGE qj x x mqq> COUNT qqqqqqqqqqqqqqqqqqqqj x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj mqqqqqqqqqqqqqqqqqq>qqqqqqqwqq>qqqqqqqqqqqqqqqqqqqqwqqqk mqq> handle-options qqqj x x lqqqqqqqqqqqqqqqqqqqqqqqqqqq OF qqqqqqq> rse qqqqqqqqqqqqqqqqqqqqqqqqqqqqq> 5 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. 3 TOTAL Returns the sum of the values specified by a record selection expression. The value expression must be a numeric data type. 4 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 5 C_Example #include 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; } 5 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. 4 Format (B)0 q> TOTAL qqqqqwq>qqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqk mq> handle-options qqj x x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq value-expr qqqqqq> OF qqqqqq> rse qqqqqqqqqqqq> (B)0handle-options = q> ( qwqqqqqq> REQUEST_HANDLE qqqqq> var qqqqqqqqwq> ) q> tqqqqqq> TRANSACTION_HANDLE qqq> var qqqqqqqqu mqqqqqq> REQUEST_HANDLE q> var q> , qqqqqqqk x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x mqqqq> TRANSACTION_HANDLE q> var qqqqqqqqqqqj 5 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. 3 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. 4 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.) 5 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; } 5 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. 4 Format (B)0STORE qqqqwqq>qqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqk mqq> handle-options qj x x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq context-var qqq> IN qqqqqqqqqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqk lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq> relation-name qqqqq> USING qqqqqqqk mq> db-handle qq> . qj x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqwq> statement qqqqqqqqqqqqqqqqqqqqqqqqqqqqwk mq> on-error qj tq> context-var.* qq> = qq> record-descr qux mqqqqqqqqqqqqqqqqqq END_STORE mq> get-statement qj (B)0handle-options = q> ( qwqqqqqq> REQUEST_HANDLE qqqqq> var qqqqqqqqwq> ) q> tqqqqqq> TRANSACTION_HANDLE qqq> var qqqqqqqqu mqqqqqq> REQUEST_HANDLE q> var q> , qqqqqqqk x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x mqqqq> TRANSACTION_HANDLE q> var qqqqqqqqqqqj (B)0get-statement = GET qqwqqqq>qqqqqqqqwqqwq> get-item qqwqqqqq> END_GET qqq> mq> on-error qj mqqqqq ; host-var qqqqqqqq> = qqqqq> value-expr qqqqqqqqqqwqqqq> tqq> record-descr qqqq> = qqqqq> context-var.* qqqqqqqu mqq> host-var qqqqqqqq> = qqqqq> statistical-expr qqqqj 5 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 ".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. 3 STORE_Seg_Strings Stores a segmented string into a segmented string field. Must be nested within a regular STORE statement. 4 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. 5 C_Example #include 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; } 5 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. 4 Format (B)0STORE qqqqqwq>qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq>qqwqqqqqqqk mq> ( qq> TRANSACTION_HANDLE qq var qq> )qj x lqqqqqq ss-handle qqq> IN qqqq> ss-field qqqqqqqqqqqqqqqqqqk lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq USING qqqwq>qqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqk mq> on-error qqj x lqqqqqqqqqqqqqqqqqqqqqk x lqqqqqqqqqqqqqqqqqqqqqqqqqqqq END_STORE qqqqq> 5 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. 3 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. 4 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". 5 C_Example #include 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; } 5 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. 4 Format (B)0transaction-handle = qq> ( qq> TRANSACTION_HANDLE qqq> host-var q> ) qqq> 5 Format_arguments host-var A valid host language variable. 3 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. 4 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 5 C_Example #include 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; } 5 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. 4 Format (B)0START_STREAM qqqwqq>qqqqqqqqqqqqqqqqqqwqqq> stream-name qqqqqqk mqq> handle-options qqj x x lqqqqqqqqqqqqqqqqqqqqqqqqqqqq USING qqqq> rse qqqqwqqqqqqqqqqqqqqqwqqqqqqqqqq> mqq> on-error qqj (B)0on-error = ON ERROR qqwqqq> statement qqqqwqqq> END_ERROR mqqqqqqqqq ( qwqqqqqq> REQUEST_HANDLE qqqqq> var qqqqqqqqwq> ) q> tqqqqqq> TRANSACTION_HANDLE qqq> var qqqqqqqqu mqqqqqq> REQUEST_HANDLE q> var q> , qqqqqqqk x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x mqqqq> TRANSACTION_HANDLE q> var qqqqqqqqqqqj 5 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. 3 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. 4 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. 5 C_Example #include 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; } 5 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. 4 Format (B)0unique-clause = qqqwqq>qqqqqqqwqqqqqqqqqq> UNIQUE qqqqqqwq>qqqqqqqqqqqqqqqqqqwqqqqk mqq> NOT qqj mq> handle-options qqj x x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq rse qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq> (B)0handle-options = q> ( qwqqqqqq> REQUEST_HANDLE qqqqq> var qqqqqqqqwq> ) q> tqqqqqq> TRANSACTION_HANDLE qqq> var qqqqqqqqu mqqqqqq> REQUEST_HANDLE q> var q> , qqqqqqqk x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x mqqqq> TRANSACTION_HANDLE q> var qqqqqqqqqqqj 5 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. 3 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. 4 Format (B)0value-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. 3 WITH Contains a conditional expression that lets you specify conditions that must be true for a record to be included in a record stream. 4 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 5 C_Example #include 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; } 5 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. 4 Format (B)0with-clause = qqqqqq> WITH qqq> conditional-expr qqq> 5 Format_arguments conditional-expr Conditional expression. An expression that evaluates to true or false. For more information see the entry on Conditional expressions. 2 Query_governor Using the Oracle Rdb query governor feature, you can specify the following for RDML queries: o The maximum elapsed time that the query optimizer can spend compiling a query o The maximum number of rows delivered during query processing There is no RDML interface for the query governor feature, but this feature can be enabled for RDML queries by defining the RDMS$BIND_QC_REC_LIMIT and RDMS$BIND_QC_TIMEOUT logical names. See the top-level topic "Logical_Names" in the RDO interface help file for information on how to define and set values for the RDMS$BIND_QC_REC_LIMIT and RDMS$BIND_QC_TIMEOUT logical names. 2 Errors Explanations and suggested user actions about errors returned by the RDML preprocessor are available online in three forms: o Under the topic RDML_ERRORS o In the file SYS$HELP:RDML_MSG.DOC that contains message information specific to the RDML preprocessor. You can TYPE, PRINT, or SEARCH this file for detailed information. o In the file SYS$HELP:RDB_MSG.DOC that contains message information for errors that may be encountered during application execution. 2 RDML_ERRORS message_file RDML message file 3 AMBIG_HANDLE Database handle '', qualifying constraint '', found multiple times in ON list Explanation: The database handle used to qualify the constraint exists more than once in the ON list of the START_TRANSACTION statement. User Action: Correct the database handle name or correct the list of handles in the ON list. 3 AMBIG_REL_NAME Relation '' exists in multiple databases; the name must be qualified Explanation: The named relation exists in more than one database. User Action: Use a database context variable to qualify the relation. 3 ATLINE at line in the file Explanation: The previous error was detected at the stated line in the stated file. User Action: Use this information to help find an error in source file. 3 AUTO_LOCK_ONLY SQL does not support [NO]AUTO_LOCKING syntax; always uses AUTOLOCKING Explanation: A WITH AUTO_LOCKING or WITH NOAUTO_LOCKING clause was seen in a START_TRANSACTION statement. SQL does not support the syntax for either clause. SQL behaves as though WITH AUTO_LOCKING was specified as its default behavior. There is no way to mimic WITH NOAUTO_LOCKING. User Action: Eliminate WITH NOAUTO_LOCKING from your source code. The warning is generated for WITH AUTO_LOCKING also to make you aware that an explicit clause has been removed. 3 BADDMLPARSE Error parsing file specification '' Explanation: The file specification provided cannot be used. User Action: Correct the file specification. 3 BAD_PROTOCOL CDD record description is malformed Explanation: The CDD record is malformed. User Action: It may be necessary to integrate the database metadata in the CDD using the Rdb INTEGRATE command. 3 BETWEENORD BETWEEN operands must be in order in SQL Explanation: RDML interprets the BETWEEN operator differently from SQL. In RDML, "A BETWEEN B OR C" is equivalent to "A BETWEEN C OR B". Although the converter could have generated a functionally equivalent disjunction, this would have been sloppy. If the converter can evaluate the expressions, it does and places the expressions in the correct order. If not, it produces a warning. The diagnostic file can be used to easily locate and modify this code. User Action: Make sure that the second and third operands of a BETWEEN expression are always properly ordered; or alternatively, OR the BETWEEN clause generated with another with the second and third operands in reverse order. 3 BLR_GENERATION Error occurred during BLR generation Explanation: An error was detected during the BLR generation phase of precompilation. Additional messages identify the specific problem more precisely. User Action: Follow the actions suggested for the messages that identify the specific problem. 3 CDD_ERROR error accessing the CDD Explanation: RDML detected an unexpected error while loading metadata. User Action: insure that the CDD metadata is correct. 3 CODE_GENERATION Error occurred during code generation Explanation: An error was detected during the code generation phase of precompilation. Additional messages identify the specific problem more precisely. User Action: Follow the actions suggested for the messages that identify the specific problem. 3 CVINUSE Context variable '' has already been defined Explanation: The named symbol has been previously defined and cannot be redefined in a new context. User Action: Use a unique name for the conflicting symbol. 3 DATE_CONVERT date/time string can not be converted Explanation: The date/time string literal can not be converted to VMS binary date format. The string literal format does not match the format defined by the logical name LIB$DT_INPUT_FORMAT, or the default system format which is DD-MMM-YYYY HH:MM:SS.CC. User Action: Check the definition of this logical name and the string literal in the RDML source code. 3 DATE_EMPTY /DATE_TYPE=EMPTY_RECORDS specified; use /DATE_TYPE=NOEMPTY_RECORDS Explanation: You explicitly specified the obsolete value EMPTY_RECORDS using the /DATE_TYPE qualifier. This causes RDML to generate a date type for Pascal using an empty record. The Pascal compiler may not handle assignments for dates appropriately in this case. User Action: Either specify /DATE_TYPE=NOEMPTY_RECORDS or omit the /DATE_TYPE qualifier from the RDML command line. 3 DDTMHANDLES DDTM transaction handles generated for this module; use /CONTEXT=ALL qualifier when compiling SQL module Explanation: The converter generated extra arguments to SQL-Module Language procedures to pass transaction information to the Digital Database Transaction Manager. User Action: Be sure to COMPILE and LINK your SQL Module Language files with the correct compilation switches and libraries. 3 DIC_DB_CHG A dictionary definition used by CDD/Plus database has changed Explanation: A dictionary definition used by the database has changed. The actual information in the provided CDD pathname may not be consistent and you may receive unexpected results. User Action: Use the INTEGRATE statement to resolve any differences between the dictionary and the database, insure that the CDD metadata is correct. 3 DMLSYNTAX Syntax error: found '' when expecting '' Explanation: A syntax error has been detected; the symbol found does not match the expected symbol. User Action: Corrent the syntax error. 3 DUP_CDD_NAME Record type has conflicting CDD definitions Explanation: Two relations from different databases have the same name. Normally, the preprocessor disambiguates these relations by prefixing their names with the database handle. However, when the databases come from CDD and the converter is generating references to the data dictionary, the dictionary chooses the names which will be used to refer to a relation structured type. CDD will not choose unique names, hence your program will not compile. User Action: If the relation referred to by the message is identical to the relation which was declared (perhaps because it came from a different link to the same database), then nothing needs to be done. If the relation is different you will have to take steps to disambiguate the Orelation names yourself or re-translate using the /NOCDD_NAMES switch. 3 DUP_REDUCE_CLSE Duplicate REDUCED Clause found in this RSE. It was ignored Explanation: A REDUCED clause has been specified twice in a record selection expression. User Action: Remove the incorrect or duplicate entry from the record selection expression. 3 DUP_REQ_OPTION Duplicate Request Option found and ignored Explanation: A request or transaction handle has been specified twice in a RDML statement. User Action: Remove the incorrect or duplicate entry from the statement. 3 DUP_SORT_CLAUSE Duplicate SORT Clause Found in this RSE. It was ignored Explanation: A SORT clause has been specified twice in a record selection expression. User Action: Remove the incorrect or duplicate entry from the record selection expression. 3 DUP_WITH_CLAUSE Duplicate WITH Clause found in this RSE. It was ignored Explanation: A WITH clause has been specified twice in a record selection expression. User Action: Remove the incorrect or duplicate entry from the record selection expression. 3 EOFERR Unexpected end-of-file encountered while processing '' Explanation: A premature end of file was encountered during the processing of a statement. User Action: Correct the incomplete statement. 3 ERROR_CREATING Cannot create file '' Explanation: Cannot create the named file. User Action: Make sure the file can be created as specified. 3 ERROR_OPENING Cannot open file '' Explanation: Cannot open the named file. User Action: Make sure the file can be accessed as specified. 3 FINISH FINISH behaves differently in SQL Explanation: FINISH closes and detaches from all databases in SQL. User Action: Check code to see if your usage will be affected. 3 FLD_IN_MUL_REL Field '' (used in an OVER clause) is defined in more than two relations Explanation: The OVER clause is ambiguous because the named field appears in more than two relations in the current query. User Action: Use a WITH clause to express an unambiguous query. 3 FLD_NOT_DEFINED Field '' is not defined in relation '' Explanation: The named field is not defined in the named relation. User Action: Correct the field reference. 3 FLD_ONLY_IN_ONE Field '' (used in an OVER clause) is only defined in relation '' Explanation: The OVER clause is ambiguous because the named field does not appear in two relations in the current query. User Action: Use a WITH clause to express an unambiguous query. 3 GFLOATING G_Floating datatypes detected in this module; use /G_FLOATING qualifier when compiling Explanation: A double precision floating point number was detected in this module during processing. User Action: Be sure to COMPILE and LINK all modules in the application with the correct compilation switches and libraries. 3 HOST_VARIABLE Error detected in host variable syntax Explanation: The current token, which appears to be a host variable, does not fit the recognized syntax for host variables. User Action: Correct the host variable syntax. 3 ILLMODIFY Record modification must be done in a record stream Explanation: An attempt is being made to modify a record outside an established record stream. User Action: Place the modify statement in an established record using a FOR or START_STREAM statement. 3 ILLPREFIX Illegal characters in global prefix Explanation: The value specified for the /GLOBAL_PREFIX qualifier, or the file name if /GLOBAL_PREFIX was not specified, contains a character which is neither alphanumeric nor the underscore character. The converter will still operate, but the resulting code may not compile or link properly. User Action: Specify a value for /GLOBAL_PREFIX which contains only the upper or lowercase letters, the digits 0-9, or underscore ('_'). 3 ILLRETRIEVE Segmented string retrieval is not permitted from within a STORE Explanation: An attempt is being made to retrieve a segmented string from within a STORE statement. User Action: Remove the incorrect statement. 3 INVINGET Invalid within GET statement Explanation: The GET statement contains an invalid value expression. User Action: Correct the value expression. 3 INVPRESTRHDL handle '' specified in START_STREAM for declared stream -- handle ignored Explanation: A START_STREAM statement for a declared stream specified an explicit handle. The handle was ignored by RDML. Any explicit handle for a declared stream must be specified on the DECLARE_STREAM declaration, rather than on the START_STREAM statement. User Action: Remove the handle specification, or move it to the DECLARE_STREAM statement. 3 INVWILD Assigning values to database fields using wildcards is only valid once within STORE and MODIFY statements Explanation: An invalid wildcard assignment was recognized. Either it was not inside a STORE or MODIFY statement, or it was specified more than once in such a statement. User Action: Remove the wildcard assignment, or enclose it inside a STORE or a MODIFY statement. 3 INV_HANDLE Database handle '', qualifying constraint '', not found in ON list Explanation: The database handle used to qualify the constraint does not exist in the ON list of the START_TRANSACTION statement. User Action: Correct the database handle name or correct the list of handles in the ON list. 3 JOIN_ATTRIBUTE Relation '' is part of join cannot be updated Explanation: You tried to modify or erase records from a relation that is joined (using the CROSS clause) with other relations. User Action: Redefine the query so that the relation you are trying to update is not directly joined to other relations. i.e. do not use a CROSS clause, but instead represent the other relation references as subqueries. 3 LIKECONFL Letters and underscore detected in a LIKE string Explanation: The converter converts MATCHING string constants to use the LIKE wildcard characters. There is no way to have both letters and an underscore in a LIKE target string that will still have the same semantics as the original, because SQL only permits either IGNORE CASE or an ESCAPE character, but not both. The converter will generate IGNORE CASE andthe underscore will act as a wild card, matching ANY single character (as '%' does in MATCHING. User Action: Change the target pattern. 3 LIKETOOBIG Conversion to LIKE string overflows buffer Explanation: When converting a MATCHING string to a LIKE string, additional escape characters were added which overflow the character string buffer. User Action: Break the comparison up into smaller segments. 3 LIKE_TEXT LIKE strings have different wildcards in SQL Explanation: The RDML wild card pattern matching characters '*' and '%' are written as '%' and '_', respectively, in SQL. User Action: When constructing the character string to be passed to the SQL module language matching function, use '%' and '_'. 3 LINETOOLONG Line number is longer than characters Explanation: The line mentioned is longer than the maximum allowed length. User Action: Shorten the line or split the line into smaller lines. 3 MISSCLSQUOTE Missing close quote; inserted at end-of-line Explanation: A quoted string is missing a closing quote. User Action: Place a closing quote at the end of the quoted string. 3 MOD2RELS SQL does not allow on streams with multiple relations Explanation: You have used MODIFY or ERASE on a stream whose record selection expression references more than one relation (i.e, contains a CROSS clause). SQL only permits its equivalent of MODIFY (UPDATE) and ERASE (DELETE) to operate on streams which reference no more than one relation as these are considered read only. User Action: Rewrite your code so it does not attempt to modify or erase from these relations. 3 MODREDUCE SQL does not allow on reduced streams Explanation: You have used MODIFY or ERASE on a stream whose record selection expression contains a REDUCED TO clause. SQL does not permit its equivalent of MODIFY (UPDATE) and ERASE (DELETE) to operate on these streams as these are considered read only. User Action: Rewrite your code so it does not attempt to modify or erase from these relations. 3 MODSORT SQL does not allow on sorted streams Explanation: You have used MODIFY or ERASE on a stream whose record selection expression contains a SORTED BY clause. SQL does not permit its equivalent of MODIFY (UPDATE) and ERASE (DELETE) to operate on ordered streams as these are considered read only. User Action: Rewrite your code so it does not attempt to modify or erase from these relations. 3 MULTI_DB_ACCESS Multiple database access is not supported in a single request Explanation: The query as stated requires more than one database to be referenced in the same generated request. User Action: Correct the query. 3 NESTEDCOMMENT Nested comment encountered -- ignored Explanation: A nested comment was encountered. Nesting of comments is not supported. User Action: Remove the nested comment. 3 NESTED_MODIFY Nested modifies on the same context variable are not allowed Explanation: The MODIFY statement references a context variable already being modified. User Action: Correct the MODIFY statement. 3 NODMLOUTPUT No output file generated due to errors Explanation: No host language output file was generated due to errors detected during processing. User Action: Correct problems associated with any previous errors. 3 NOENDERROR No END_ERROR was found in this block Explanation: An END_ERROR was expected before this point. User Action: Add the missing END_ERROR. 3 NOENDSTMT Statement was not terminated by a matching end statement Explanation: A FETCH, FOR, GET, MODIFY, STORE, or ON ERROR was encountered but no matching END_FETCH, END_FOR, END_GET, END_MODIFY, END_STORE, or END_ERROR was found. User Action: Supply a matching end statement. 3 NOMISSVAL No missing value has been defined for field '' Explanation: A missing value was not found for the named field. User Action: Define a missing value in the database field's definition. 3 NOREQHANDLE Request handles are not supported by SQL Explanation: Currently SQL maintains the information contained in request handles on its own. There is no equivalent to RDML request handles in SQL. User Action: Programs which have multiple transactions must be rewritten without request handles. 3 NOSUCHREL Relation '' does not exist Explanation: The named relation does not exist in the currently referenced databases. User Action: Correct the relation reference. 3 NOSUCHSTREAM Stream name '' was not declared Explanation: The stream name specified in the START_STREAM statement was expected to be a declared stream name but had not previously been declared using a DECLARE_STREAM statement. User Action: Correct the stream name, or add the appropriate DECLARE_STREAM statement. 3 NOTATRANSHANDLE Transaction handle '' has not been defined Explanation: A transaction handle has not been defined correctly. User Action: Correct the transaction handle. 3 NOTIMPLEMENTED '' is not implemented Explanation: The named feature is not currently implemented. User Action: Contact your Oracle support representative for assistance. 3 NOTINREDUCE . does not appear in the REDUCED TO clause. Explanation: When a REDUCED TO clause appears in a record selection expression, the results of referencing any of the fields not contained in the REDUCED TO clause are not defined. Although the preprocessor does not disallow this, the results are not guaranteed. SQL does not permit such usage. User Action: Either add the missing fields to the REDUCED TO clause, or eliminate the reference to these fields. 3 NOTLOCAL Field referenced '' in SORTED BY or REDUCED TO is not in the context of query Explanation: The database field referenced with a SORTED BY or REDUCED TO clause is not defined within this query. This will have the effect of sorting, or projecting to a constant value which is of little value. User Action: Examine the query and ensure that you have used the correct context variable, and field name. 3 NOTRHANDLE Transaction handles are not supported by SQL Explanation: Currently SQL does not support multiple transactions. User Action: Programs which have multiple transactions must be rewritten so that all concurrent database requests occur in the same transaction. 3 NOTRHANDLES No user transaction handles detected in this module; consider retranslation with /NOTRANS_HANDLES Explanation: You have converted an RDML Module with no user-specified transaction handles. This message only appears if the /TRANS_HANDLES qualifier is in effect by default. User Action: You may want to re-convert your code using the /NOTRANS_HANDLES qualifier. This will eliminate some of the overhead of using DDTM with your database code. If you do want the extra DDTM arguments and procedure calls, this message can be suppressed by specifying /TRANS_HANDLES explicitly in the command line. 3 NOTSSFIELD '' is not a segmented string field name Explanation: The preprocessor expected a field of type segmented string and the specified field was not of that type. User Action: Change the field name to one of type segmented string, or change the syntax so that a segmented string is not expected. 3 NOTSTORECV '' is not a STORE or MODIFY context variable Explanation: The context handle must be one specified on an enclosing STORE or MODIFY statement. User Action: Change the context handle to the one specified in the enclosing STORE or MODIFY statement. 3 NOWORKINBLK No items stored or modified in STORE or MODIFY block Explanation: No fields are stored or modified by the current STORE or MODIFY statement. User Action: Add fields to be modified or stored if appropriate. 3 NO_CDD_META_DATA unable to load metadata from the CDD Explanation: An unexpected error was detected while accessing the CDD and attempting to load metadata. The provided CDD pathname may be incorrect or the actual information in the CDD may be inconsistent with the expected protocol. User Action: use a correct CDD pathname, insure that the CDD metadata is correct. 3 NO_DATABASE No DATABASE statement has been processed prior to this point Explanation: A DATABASE statement must be the first statement processed. User Action: Place the DATABASE statement in the appropriate position in the source code. 3 NO_LANGUAGE No Language has been specified on the command line or input file Explanation: A language must be specified as a command line qualifier or by using the default file extensions. User Action: Use a command qualifier or file extension to specify what language is to be processed. 3 NO_META_DATA Cannot access compile-time metadata in file '' Explanation: An unexpected error was detected while accessing the database and attempting to load metadata. The provided filename may be incorrect or the actual information in the database may be inconsistent with the expected format. User Action: Use a correct database filename, insure that the database metadata is correct. 3 ON_ERROR ON_ERROR should be ON ERROR Explanation: An ON_ERROR was found when the correct syntax is ON ERROR, with no underscore. RDML issues the warning message, but assumes that an ON ERROR clause was encountered. User Action: Change the ON_ERROR to ON ERROR, removing the extraneous underscore, and preprocess the source again. 3 PARSING Error occurred during parsing Explanation: An error was detected during the parsing phase of precompilation. Additional messages identify the specific problem more precisely. User Action: Follow the actions suggested for the messages that identify the specific problem. 3 PREPARE PREPARE statement not translated to VAX SQL Explanation: The PREPARE statement has no effect in RDML, and is not converted to SQL. User Action: Although not necessary, the statement may be removed from the source code along with ON ERROR clauses. 3 PSECTNOEFF Linkage type PROGRAM_SECTIONS is not supported with EPASCAL -- using linkage type GLOBAL_SYMBOLS Explanation: The /LINKAGE=PROGRAM_SECTIONS qualifier only applies to VAX Pascal and VAX C. With RDML/EPASCAL, the GLOBAL_SYMBOLS linkage type is always used, regardless of what is specified on the command line. User Action: This is an informational message to remind the EPASCAL user that only linkage type GLOBAL_SYMBOLS is available with EPASCAL. To suppress the message, simply specify the /LINKAGE=GLOBAL_SYMBOLS qualifier. 3 RDML_ABORT Fatal Preprocessor Utility Error Aborted because: Explanation: A fatal internal error has occurred. User Action: Contact your Oracle support representative for assistance. You will need to provide adequate information to reproduce the problem, including, but not limited to: a complete description of the problem, a complete description of the database, the source code of the module that is causing the problem. and any related output files such as listings that may provide further information. 3 READY READY statement has no VAX SQL equivalent Explanation: The READY statement has no SQL equivalent, and is not converted to SQL. User Action: Remove the statement from the source. 3 READ_ONLY Invalid attempt to update a read only field '' Explanation: A COMPUTED BY field can only be read and can not be updated in STORE, or MODIFY statement. User Action: Do not reference the read only field in the STORE, or MODIFY statements. 3 REL_NOT_IN_DB Relation '' does not exist in database '' Explanation: The named relation does not exist in the named database. User Action: Correct the relation name or add the relation to the database. 3 SEGSTRACCESS Segmented Strings Should be accessed through FOR or STORE Explanation: Detected access to a segmented string field outside a normal segmented string FOR or STORE statement. User Action: Check to make sure that segmented string fields are being correctly accessed. 3 SIGARGS Signal arguments: PC = , PSL = Explanation: This message contains information related to the RDML_ABORT message, when an unexpected preprocessor exception is encountered. It displays the signal argument vector contents for the exception. User Action: See the RDML_ABORT message code. 3 SORTSTAT SORTED BY found in a boolean or statistical expression Explanation: A SORTED BY clause was found in a record selection expression associated with any of the operators ANY, AVERAGE, COUNT, MAX, MIN, TOTAL, and UNIQUE. Although strictly not incorrect, the order of records in a stream does not affect the value of these operations. User Action: In most cases, this message results from the SORTED BY clause being incorrectly associated with a nested subexpression. Placing parentheses around the subexpression will help RDML properly group the clause with the primary expression. Because of SQL syntax restrictions, the side effects of sort clauses on statistical expressions in RDML cannot be reproduced. 3 SOURCE_REQUIRED A Source file specification is required on the command line Explanation: A source file is required for further processing. User Action: Provide a source file specification on the command line. 3 SUMMARY Completed with Error(s), warning(s), and informational message(s) Explanation: A brief summary of messages generated during processing. User Action: None. 3 SYNTAX Syntax Error Explanation: A syntax error has been detected. User Action: Correct the syntax error. 3 TRUNCPREFIX Prefix too long; truncated to Explanation: The value specified for the /GLOBAL_PREFIX qualifier, or the file name if /GLOBAL_PREFIX was not specified, is longer than the maximum allowable size. If the prefix is not truncated, global symbols are likely to share their first 31 characters and the linker will see them as the same symbols. User Action: Specify a shorter value for /GLOBAL_PREFIX. 3 TYPE_CONFLICT There is a type conflict between elements of this RSE Explanation: A potential type conflict has been detected, but the database system may be able to perform the conversion. User Action: Make sure that the statement causing this message is performing the function that is intended. 3 UNBALSTRM Undeclared stream '' has no END_STREAM statement Explanation: A START_STREAM statement was seen for an undeclared stream, but a matching END_STREAM statement was not seen. User Action: Use a declared stream, or balance the START_STREAM statement for the undeclared stream with an END_STREAM statement. 3 UNTERMPASNUMLIT Missing closing apostrophe in Pascal binary, octal or hexadecimal numeric literal -- inserted Explanation: A Pascal binary (%b'010101'), octal (%o'1234'), or hexadecimal (%x'12AB') numeric literal was found not to have been terminated properly. User Action: Properly terminate the numeric literal with an apostrophe. 3 WILDHASSEGSTR This wild-card assignment includes a segmented string field Explanation: When a wild-card assignment occurs in RDML, all fields in the relation are copied. In SQL, it is not possible to retrieve segmented string handles from the database, therefore these fields will not have valid values. The converter generates an assignment for the field, but it will have no effect. User Action: It is recommended that the program be rewritten without segmented string identifiers directly accessed.