The FETCH data manipulation statement retrieves values from the result table associated with a cursor. You can use this statement only in SQL that is embedded in a program.
None required.
►►─── FETCH cursor-name ──────────────────────────────────────────────────────► ┌─────── , ────────────────┐ ►─┬─ INTO ─┬─▼┬─ host-variable ────────┬┴────────────────────────┬─┬─────────►◄ │ │ ├─ local-variable ───────┤ │ │ │ │ └─ routine-parameter ────┘ │ │ │ └─ :dyn-buffer USING DESCRIPTOR descriptor-area-name ─┘ │ └─ BULK ─┬─ :bulk-buffer bulk-options ───────────────┬───────────┘ └─ :dyn-buffer dynamic-bulk-options2 ───────┘
Expansion of bulk-options
►►──┬──────────────────────────────┬──────────────────────────────────────────► └─ START :start-variable-name ─┘ ►──┬─────────────────────────────────┬───────────────────────────────────────►◄ └─ ROWS :row-count-variable-name ─┘
Expansion of dynamic-bulk-options2
►►──┬──────────────────────────────┬──────────────────────────────────────────► └─ START :start-variable-name ─┘ ►── ROWS :row-count-variable-name ───────────────────────────────────────────► ►── using sql DESCRIPTOR descriptor-area-name ───────────────────────────────►◄
Specifies the cursor to be used for retrieving values. Cursor-name must identify an open cursor previously defined by a DECLARE CURSOR statement within the application program or by an ALLOCATE CURSOR statement executed within the same SQL transaction.
Directs CA IDMS to retrieve a single row from the result table associated with the named cursor and to return the column values into the specified locations.
Note: An INTO clause is required for SQL that is imbedded in host programs.
Identifies the host variables to which CA IDMS is to assign values retrieved from a result table defined by a query expression. CA IDMS assigns the value in the first result column to the first host variable, the value in the second result column to the second host variable, and so on.
Host-variable must be a host variable declared previously in the host-language application program.
Note: In COBOL, host-variable can be a non-bulk structure. For more information, see the CA IDMS SQL Programming Guide.
You must specify the same number of host variables in the INTO parameter as the number of columns in the result table. Multiple host variables must be separated by commas. For expanded host-variable syntax, see Host Variables.
Identifies the local variable or routine parameter which CA IDMS is to assign values retrieved from a result table defined by a query expression. CA IDMS assigns the value in the first result column to the first local variable or routine parameter, the value in the second result column to the second local variable or routine parameter, and so on. You must specify the same number of local variables and routine parameters in the INTO parameter as the number of columns in the result table.
Identifies a variable or a bulk buffer into which CA IDMS is to return all values retrieved from one or more rows of the result table associated with the named cursor.
Dyn-buffer must identify a variable previously declared in the host language application program or SQL routine.
The size of dyn-buffer must be sufficient to hold one row of the result table if specified as part of the INTO parameter or row-count-variable rows if specified as part of the BULK parameter. The format of the data returned into dyn-buffer is determined by the column descriptions in the SQL descriptor area specified in the USING DESCRIPTOR parameter.
Specifies the SQL descriptor area that describes the format in which the columns of the result table are to be returned to the host-language application program or SQL routine.
The specification of a descriptor area is a CA IDMS extension of the SQL standard.
Directs CA IDMS to use the named area as the descriptor area. Descriptor-area-name must identify an SQL descriptor area.
For the layout of an SQL descriptor area, see SQL Descriptor Area.
Directs CA IDMS to retrieve one or more rows from the result table associated with the cursor and to return the column values into a contiguous storage area. The specification of BULK is a CA IDMS extension of the SQL standard.
Identifies a variable to which CA IDMS is to assign values retrieved from one or more rows of the result table associated with the named cursor. Bulk-buffer must identify a variable previously declared in the host-language application program or SQL routine.
Bulk-buffer must be defined as a multiply-occurring structure having the same number of sub-elements in one occurrence as the number of columns in the result table.
Optionally specify the location in bulk-buffer for the first row fetched and/or the number of rows to be fetched from the result table associated with the cursor. Expanded syntax for bulk-options immediately follows the statement syntax.
Provides specifications for dynamically retrieving one or more rows from the result table associated with the named cursor.
Expanded syntax for dynamic-bulk-options2 appears immediately following the expanded syntax for bulk-options. Descriptions of dynamic-bulk-options2 parameters appear above.
Note: Dyn-buff, bulk-buffer, start-variable-name, and row-count-variable-name are variables that can be host variables or when the statement is used in an SQL routine, local variables or routine parameters. In this case, their names must not be preceded with a colon.
Parameters for Expansion of bulk-options
Identifies a variable containing the relative position within the bulk buffer to which CA IDMS is to assign the values in the first row retrieved from the result table. Values in subsequent rows of the result table are assigned sequentially to subsequent positions in the bulk buffer.
Start-variable-name must be a variable previously declared in the host-language application program. The value in the variable must be an integer in the range 1 through the number of rows that fit in the bulk buffer.
For languages whose subscript values are relative to 0, the value for start-variable-name must be in the range 0 through one less than the number of entries which fit in the bulk buffer.
If you do not specify the START parameter, CA IDMS assigns the values in the first row of the result table to the first row of the array.
Identifies a variable that specifies the maximum number of rows in the result table CA IDMS is to assign to the bulk buffer.
Row-count-variable-name must be a variable previously declared in the host-language application program. The value in the variable must be an integer in the range 1 through the number of rows that fit in the bulk buffer.
The ROWS parameter must be specified if a USING DESCRIPTOR clause is specified in a BULK parameter.
If you do not specify the ROWS parameter, CA IDMS assigns the rows in the result table to the buffer sequentially until no more rows exist in the result table or the buffer has been filled.
Parameters for Expansion of dynamic-bulk-options2
The following additional parameter is used with bulk-options to create dynamic-bulk-options2:
Directs CA IDMS to use the named area as the descriptor area. Descriptor-area-name must identify an SQL descriptor area.
Compatible Data Types
The data types of the values retrieved by the FETCH statement and the data types of the variables named in the INTO parameter must be compatible for assignment. If the values are assigned to a buffer defined as an array, the data types of the array elements must be compatible with the data types of the values.
FETCH Execution
When executing a FETCH statement, CA IDMS:
No More Rows
CA IDMS returns an SQLCODE value of 100 when any of the following is true:
In each case, CA IDMS leaves the cursor positioned after the last row.
Use of the Descriptor Area
When you use dynamic SQL to return data to a host-language application program in a form different from that in which it is stored in the database, you can modify the data characteristics in the SQL descriptor area named in the FETCH statement. You must make any changes to the descriptor area before the first fetch operation. You should not change the contents of the descriptor area after the first fetch operation and before the closing of the cursor.
Static and Dynamic Cursors
The format of the output of a static cursor is known at compile time. The format of the output of a dynamic cursor is often not known at compile time. Typically, you specify dyn-buffer when the cursor is dynamic, such as when the SELECT statement associated with the cursor is not known at compile time.
Fetching Multiple Rows
The following FETCH statement retrieves values from a result table defined by PROJ_CURSOR. Descriptions of the data in the output buffer are in a descriptor area named BUFF-1-SQLDA. The retrieved values are assigned to the CURSOR-BUFF-1 buffer, starting at the position in the buffer indicated by the value in BUFF-1-START. The value in BUFF-1-ROWS determines the number of rows retrieved.
EXEC SQL
FETCH PROJ_CURSOR
BULK :CURSOR-BUFF-1
START :BUFF-1-START
ROWS :BUFF-1-ROWS
USING DESCRIPTOR BUFF-1-SQLDA
END-EXEC
Fetching a Single Row
The following FETCH statement retrieves values from one row of the BONUS_CURSOR cursor. The values are assigned to the host variables EMP-ID and BONUS-AMT which have an associated indicator variable.
EXEC SQL
FETCH BONUS_CURSOR
INTO :EMP-ID, :BONUS-AMT :BONUS-IND
END-EXEC
|
Copyright © 2014 CA.
All rights reserved.
|
|