1 STORE Inserts a record into an existing relation. Within a single STORE statement, you can refer to only one relation. You cannot store records into a view that was defined using the WITH, CROSS, or REDUCED clause. Example: RDO> STORE D IN DEPARTMENTS USING cont> D.DEPARTMENT_CODE = "RECR"; cont> D.DEPARTMENT_NAME = "Recreation"; cont> D.MANAGER_ID = "00175"; cont> D.BUDGET_PROJECTED = 240000; cont> D.BUDGET_ACTUAL = 127098 cont> END_STORE RDO> 2 Format (B)0STORE qqwqqqqqqqqqqq>qqqqqqqqqqqqwqqqqqqk mqqqq> handle-options qqqj x lqqqqqqqqqqqqqqqqqqq context-var qq> IN qwqqqqqqqqq>qqqqqqqqqqwqq> relation-name qqk mq> db-handle qq> . qj x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq USING qwqqqqqqq>qqqqqqwq> store-items qqq> END_STORE qq> mqq> on-error qj (The optional GET...RDB$DB_KEY is available only in programs preprocessed by the RDBPRE preprocessor. The optional PRINT...RDB$DB_KEY can be used in RDO only.) 3 handle-options (B)0handle-options = qq> ( qwq> REQUEST_HANDLE qqq> var qqqqqqqqqqqqqqqqqqqqqqqqqqqwq> ) qq> tq> TRANSACTION_HANDLE qqq> var qqqqqqqqqqqqqqqqqqqqqqqu mq> REQUEST_HANDLE q> var , TRANSACTION_HANDLE q> var qj 4 REQUEST_HANDLE A keyword followed by a host language variable. A request handle points to the location of a compiled Oracle Rdb request. If you do not supply a request handle explicitly, Oracle Rdb associates a default request handle with the compiled request. You must use a request handle to make an identical query to two different databases. In Callable RDO, use !VAL as a marker for host language variables. You can put parentheses around the host language variable name. 4 TRANSACTION_HANDLE A keyword followed by a host language variable. A transaction handle identifies each instance of a transaction. If you do not declare the transaction handle explicitly, Oracle Rdb attaches an internal identifier to the transaction. In Callable RDO, use !VAL as a marker for host language variables. You can put parentheses around the host language variable name. Normally, you do not need to use this argument. The ability to declare a transaction handle is provided for compatibility with other database products and future releases of Oracle Rdb. 3 context-var A valid context variable. 3 db-handle A host language variable used to refer to the database. 3 relation-name The name of the relation into which the value is stored. 3 store-items (B)0store-items = qwqwq> context-var . field-name qqq> = qqq> value-expr qqwqqqwqqqk x mqqqqqqqqqqqqqqqqqqqqqqqq ; context-var . * qqqqqqqqqq> = qq> record-descr qqqqqqqj x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq> tq> GET qq> host-var q> = qq> context-var qq> . qq> RDB$DB_KEY qu mq> PRINT qqqqqqqqqqqqqqqqqqq> context-var qq> . qq> RDB$DB_KEY qj 4 field-name The name of the field in the relation where the value is stored. 4 value-expr A valid Oracle Rdb value expression that specifies the value to be stored. For more information, request HELP on Value_expr. 4 record-descr A valid data dictionary record descriptor matching all the fields of the relation. 4 GET...RDB$DB_KEY In an RDBPRE program (BASIC, COBOL, FORTRAN), you can use GET...RDB$DB_KEY to retrieve the database key of the record about to be stored by the STORE statement into a host language variable. Example: &RDB& STORE E IN EMPLOYEES USING E.EMPLOYEE_ID = 15231; &RDB& E.LAST_NAME = "Smith"; &RDB& GET MY_DB_KEY = E.RDB$DB_KEY; &RDB& END_GET &RDB& END_STORE (MY_DB_KEY is a user-defined host language variable.) (The optional GET...RDB$DB_KEY is available only in programs preprocessed by the RDBPRE preprocessor. The optional PRINT...RDB$DB_KEY can be used in RDO only.) 4 PRINT...RDB$DB_KEY In RDO only, you can use PRINT...RDB$DB_KEY to display the database key of the record about to be stored by the STORE statement. Example: RDO> STORE E IN EMPLOYEES USING cont> E.EMPLOYEE_ID = "15231"; cont> E.LAST_NAME = "Smith"; cont> PRINT E.RDB$DB_KEY cont> END_STORE RDB$DB_KEY 21:339:0 3 on-error The ON ERROR clause, which specifies a host language statement or Oracle Rdb data manipulation statement to be performed if an Oracle Rdb error occurs. For more information, request HELP on ON_ERROR. 2 Examples Example 1 The following example shows how to store a record in RDO: RDO> START_TRANSACTION READ_WRITE RDO> ! RDO> STORE D IN DEPARTMENTS USING cont> D.DEPARTMENT_CODE = "RECR"; cont> D.DEPARTMENT_NAME = "Recreation"; cont> D.MANAGER_ID = "00175"; cont> D.BUDGET_PROJECTED = 240000; cont> D.BUDGET_ACTUAL = 128776; cont> END_STORE RDO> ! RDO> COMMIT This RDO statement explicitly assigns a literal value to each field in the DEPARTMENTS relation. Example 2 The following example shows how to store a record in COBOL: ACCEPT JOB-CODE. DISPLAY "Enter starting date: " WITH NO ADVANCING. ACCEPT START-DATE. DISPLAY "Enter ending date: " WITH NO ADVANCING. ACCEPT END-DATE. DISPLAY "Enter department code: " WITH NO ADVANCING. ACCEPT DEPT-CODE. DISPLAY "Enter supervisor's ID: " WITH NO ADVANCING. ACCEPT SUPER. &RDB& START_TRANSACTION READ_WRITE &RDB& RESERVING JOB_HISTORY, &RDB& FOR PROTECTED WRITE, &RDB& JOBS, EMPLOYEES &RDB& FOR SHARED READ &RDB& STORE J IN JOB_HISTORY USING &RDB& ON ERROR &RDB& ROLLBACK DISPLAY "An error has occurred. Try again." GO TO STORE-JOB-HISTORY &RDB& END_ERROR &RDB& J.EMPLOYEE_ID = EMPL-ID; &RDB& J.JOB_CODE = JOB-CODE; &RDB& J.JOB_START = START-DATE; &RDB& J.JOB_END = END-DATE &RDB& J.DEPARTMENT_CODE = DEPT-CODE; &RDB& J.SUPERVISOR_ID = SUPER; &RDB& END_STORE &RDB& COMMIT This sequence stores a new record in the JOB_HISTORY relation. The COBOL program does the following: o Prompts for the field values. o Starts a read/write transaction. Because you are updating JOB_ HISTORY, you do not want to conflict with other users who may be reading data from this relation. Therefore, you use the PROTECTED WRITE reserving option. There are also constraints on the database to ensure that the employee and the job code being stored actually exist in other relations. Because the constraints check these other relations, you must reserve those relations also. o Stores the record by assigning the host language variables to database field values. o Includes an ON ERROR clause to check for errors and reprompt if necessary. o Uses COMMIT to make the update permanent. A more extensive example appears under COMMIT. Example 3: The following RDBPRE program segment uses GET...RDB$DB_KEY to retrieve the database key of the record about to be stored by the STORE statement into a host language variable. &RDB& STORE E IN EMPLOYEES USING E.EMPLOYEE_ID = 15231; &RDB& E.LAST_NAME = "Smith"; &RDB& GET MY_DB_KEY = E.RDB$DB_KEY; &RDB& END_GET &RDB& END_STORE (MY_DB_KEY is a user-defined host language variable.) Example 4: The following program reads a file and loads it into the specified employee's RESUMES record in the PERSONNEL database. program STORE_RESUME ! ! STORE RESUME ! This program reads a file and loads it into the specified ! employee's RESUMES record in the PERSONNEL database ! option type = EXPLICIT declare long constant TRUE = -1%, FALSE = 0% declare & string & employee_id, resume_file, text_line, & last_name, first_name, & long & found, line_count &RDB& INVOKE DATABASE FILENAME "DB$:PERSONNEL31" print "** Personnel RESUME Load **" when error in input "Enter EMPLOYEE_ID"; employee_id use print "Program terminated" continue END_PROGRAM end when &RDB& START_TRANSACTION READ_WRITE &RDB& FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID = employee_id &RDB& GET &RDB& last_name = E.LAST_NAME; &RDB& first_name = E.FIRST_NAME; &RDB& END_GET found = TRUE &RDB& END_FOR if not found then print "Error - employee " + employee_id + " not found" exit program else ! ! Display the employees name ! print "Loading RESUME for employee " + & TRM$(first_name) + ", " + TRM$(last_name) ! ! Read the name of the resume source file ! GET_NAME: when error in input "Enter the resume file name"; resume_file open resume_file for input as file #1, & organization sequential, recordtype ANY use if err = 11% then print "Program terminated" continue END_PROGRAM else print "Error - " + RIGHT(ERT$(err),2%) continue GET_NAME end if end when &RDB& CREATE_SEGMENTED_STRING RES ! ! Loop and read each line from the resume, and store ! it in the segmented string ! line_count = 0% while TRUE ! indefinite loop when error in linput #1, text_line use continue EOF end when text_line = TRM$(text_line) line_count = line_count + 1% &RDB& STORE R IN RES USING &RDB& R.RDB$VALUE = text_line; &RDB& R.RDB$LENGTH = LEN(text_line) &RDB& END_STORE next EOF: close #1 print line_count; "lines stored in resume." &RDB& STORE RS IN RESUMES USING &RDB& RS.EMPLOYEE_ID = employee_id; &RDB& RS.RESUME = RES &RDB& END_STORE &RDB& END_SEGMENTED_STRING RES end if &RDB& commit &RDB& finish END_PROGRAM: end program Example 5: The following RDO example uses the PRINT statement to display the database key of the record about to be stored by the STORE statement: RDO> STORE E IN EMPLOYEES USING cont> E.EMPLOYEE_ID = "15231"; cont> E.LAST_NAME = "Smith"; cont> PRINT E.RDB$DB_KEY cont> END_STORE RDB$DB_KEY 21:339:0 2 segmented_string_STORE A special form of the STORE statement inserts a segment into a segmented string. Storing a segmented string requires four steps: 1. Create a segmented string with the CREATE_SEGMENTED_STRING statement. See HELP on CREATE_SEGMENT. 2. Store each segment using the syntax shown in the following Format section. You must use the special Oracle Rdb value expression RDB$VALUE or RDB$LENGTH as the segment name in the USING clause. 3. Store the entire segmented string using the usual syntax for storing records in relations. Use the segmented string handle, declared in the CREATE_SEGMENTED_STRING statement, as the value expression in this USING clause. 4. Close the segmented string. 3 Format (B)0STORE qqqk lqqqqqj mqq> context-var qqq> IN qqqq> ss-handle qqqqk lqqqqqqqqqqqqqqqqqqqqq USING qqqwqqqqqqqqqqqqqqwqqqqqqqk mq> on-error qqj x lqqqqqqqqqqqqqqqqq context-var . RDB$VALUE qqwq> = qqq> value-expr qqk mq> context-var . RDB$LENGTH qj x lqqqqqqqqqqqqqqqqqqqqqqqqqqqq END_STORE 4 Arguments context-var A valid context variable. ss-handle A host language variable or name used to refer to the segmented string. This handle must match the one declared in the CREATE_SEGMENTED_STRING statement. on-error The ON ERROR clause, which specifies host language or Oracle Rdb data manipulation statements to be performed if an Oracle Rdb error occurs. value-expr A valid Oracle Rdb value expression that specifies the value to be stored. 3 More Oracle Rdb defines a special name to refer to the segments of a segmented string. This value expression is equivalent to a field name; it names the fields or segments of the string. Furthermore, because segments can vary in length, Oracle Rdb also defines a name for the length of a segment. These names are: o RDB$VALUE The value stored in a segment of a segmented string o RDB$LENGTH The length in bytes of a segment When using the RDML and RDBPRE precompilers, be sure to define a sufficiently large value for the RDMS$BIND_SEGMENTED_STRING_ BUFFER logical name. An adequate buffer size is needed to store large segmented strings (using segmented string storage maps) in storage areas other than the default RDB$SYSTEM storage area. The minimum acceptable value for the RDMS$BIND_SEGMENTED_STRING_ BUFFER logical name must be equal to the sum of the length of the segments of the segmented string. For example, if you know that the sum of the length of the segments is one megabyte, then 1,048,576 bytes is an acceptable value for this logical name. You must specify the logical name value because when RDML and RDBPRE precompilers store segmented strings, Oracle Rdb does not know which table contains the string until after the entire string is stored. Oracle Rdb buffers the entire segmented string, if possible, and does not store it until the STORE statement executes. If the segmented string remains buffered, it is stored in the appropriate storage area. If the string is not buffered (because it is larger than the defined value for the logical name or the default value of 10,000 bytes), it is not stored in the default storage area and the following exception message is displayed: %RDB-F-IMP_EXC, facility-specific limit exceeded -RDMS-E-SEGSTR_AREA_INC, segmented string was stored incorrectly To avoid this error, set the value of the RDMS$BIND_SEGMENTED_ STRING_BUFFER logical name to a sufficiently large value. Note that a value of up to 500 MB can be specified for this logical name. 3 Example For a complete example, ask for HELP on CREATE_SEGMENT.