FETCH

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.
idmscu19
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.
This article describes the following information:
2
2
Authorization
None required.
Syntax
  ►►─── 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
 ───────────────────────────────►◄  
Parameters
  • cursor-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.
  • INTO
    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.
    An INTO clause is required for SQL that is imbedded in host programs.
  • host-variable
    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.
    In COBOL,
    host-variable
    can be a non-bulk structure. For more information, see Host Variables.
    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.
  • local-variable
  • routine-parameter
    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.
  • :
    dyn-buffer
    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.
  • USING DESCRIPTOR
    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.
  • descriptor-area-name
    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.
  • BULK
    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.
  • :
    bulk-buffer
    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.
  • bulk-options
    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.
  • dynamic-bulk-options2
    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.
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
  • START :
    start-variable-name
    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.
  • ROWS :
    row-count-variable-name
    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
:
  • descriptor-area-name
    Directs CA IDMS to use the named area as the descriptor area.
    Descriptor-area-name
    must identify an SQL descriptor area.
Usage
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:
  1. Positions a cursor on the next row following the current row
  2. Retrieves one or more rows of values from the result table beginning with the new current row
  3. Assigns the retrieved values to the specified variables
  4. Leaves the cursor positioned on the last row retrieved
No More Rows
CA IDMS returns an SQLCODE value of 100 when any of the following is true:
  • The result table associated with the cursor named in the FETCH statement is empty
  • The result table associated with the cursor named in the FETCH statement is not empty, and the cursor is positioned after the last row before the statement is executed
  • A bulk fetch operation retrieves fewer rows than are requested in the FETCH statement
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.
Examples
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
More Information