FETCH (SQL)

Repositions a cursor, and retrieves data from it.

Synopsis

FETCH cursor-name [INTO host-variable-list ]

Description

Within an embedded SQL application, a FETCH statement retrieves data from a cursor. The required sequence of actions is: DECLARE, OPEN, FETCH, CLOSE. Attempting a FETCH on a cursor that is not open results in an SQLCODE -102 error.

As an SQL statement, this is supported only from within embedded SQL. Equivalent operations are supported through ODBC using the ODBC API. For further details, refer to Embedded SQL.

An INTO clause can be specified as a clause of the DECLARE statement, as a clause of the FETCH statement, or both. The INTO clause allows data from the columns of a fetch to be placed into local host variables. Each host variable in the list, from left to right, is associated with the corresponding column in the cursor result set. The data type of each variable must either match or be a supported implicit conversion of the data type of the corresponding result set column. The number of variables must match the number of columns in the cursor select list.

The FETCH operation completes when the cursor advances to the end of the data. This sets SQLCODE=100 (No more data). It also sets the %ROWCOUNT variable to the number of fetched rows.

Note:

The values returned by INTO clause host variables are only reliable while SQLCODE=0. If SQLCODE=100 (No more data) the host variable values should not be used.

The cursor-name is not namespace-specific. Changing the current namespace has no effect on use of a declared cursor. The only namespace consideration is that FETCH must occur in the namespace that contains the table(s) being queried.

%ROWID

When a FETCH retrieves a row of an updateable cursor, it sets %ROWID to the RowID value of the fetched row. An updateable cursor is one in which the top FROM clause contains exactly one element, either a table name or an updateable view name.

This setting of %ROWID for each row retrieved is subject to the following conditions:

The DECLARE cursorname CURSOR and OPEN cursorname statements do not initialize %ROWID; the %ROWID value is unchanged from its prior value. The first successful FETCH sets %ROWID. Each subsequent FETCH that retrieves a row resets %ROWID to the current RowID. FETCH sets %ROWID if it retrieves a row of an updateable cursor. If the cursor is not updateable, %ROWID remains unchanged. If no rows matched the query selection criteria, FETCH does not change the prior the %ROWID value. Upon CLOSE or when FETCH issues an SQLCODE 100 (No Data, or No More Data), %ROWID contains the RowID of the last row retrieved.
A cursor-based SELECT with a DISTINCT keyword or a GROUP BY clause does not set %ROWID. The %ROWID value is unchanged from its previous value (if any).
A cursor-based SELECT that performs only aggregate operations does not set %ROWID. The %ROWID value is unchanged from its previous value (if any).

An Embedded SQL SELECT with no declared cursor does not set %ROWID. The %ROWID value is unchanged upon the completion of a simple SELECT statement.

FETCH for UPDATE or DELETE

You can use FETCH to retrieve a row for update or delete. The UPDATE or DELETE must specify the WHERE CURRENT OF clause. The DECLARE should specify the FOR UPDATE clause. The following example shows a cursor-based delete that deletes all selected rows:

  SET $NAMESPACE="Samples"
  &sql(DECLARE MyCursor CURSOR FOR SELECT %ID,Status
       FROM Sample.Quality WHERE Status='Bad' FOR UPDATE)
  &sql(OPEN MyCursor)
    IF SQLCODE<0 {WRITE "SQL Open Cursor Error:",SQLCODE," ",%msg  QUIT}
  NEW %ROWCOUNT,%ROWID
  FOR {&sql(FETCH MyCursor)  QUIT:SQLCODE'=0
       &sql(DELETE FROM Sample.Quality WHERE CURRENT OF MyCursor) }
  WRITE !,"Number of rows updated=",%ROWCOUNT
  &sql(CLOSE MyCursor)
      IF SQLCODE<0 {WRITE "SQL Close Cursor Error:",SQLCODE," ",%msg  QUIT}

Arguments

cursor-name

The name of a currently open cursor. The cursor name was specified in the DECLARE command. Cursor names are case-sensitive.

INTO host-variable-list

An optional argument that places data from the columns of a fetch into local variables. The host-variable-list specifies a host variable, or a comma-separated list of host variables, that are targets to contain data associated with the cursor. The INTO clause is optional. If it is not specified, the FETCH statement positions the cursor only.

Examples

The following Embedded SQL example shows FETCH invoked by an argumentless FOR loop retrieving data from a cursor named EmpCursor. The INTO clause is specified in the DECLARE statement:

    &sql(DECLARE EmpCursor CURSOR FOR 
        SELECT Name, Home_State
        INTO :name,:state FROM Sample.Employee
        WHERE Home_State %STARTSWITH 'M')
   &sql(OPEN EmpCursor)
        IF SQLCODE<0 {WRITE "SQL Open Cursor Error:",SQLCODE," ",%msg  QUIT}
   NEW %ROWCOUNT,%ROWID
   FOR { &sql(FETCH EmpCursor)
        QUIT:SQLCODE'=0  
        WRITE "count: ",%ROWCOUNT," RowID: ",%ROWID,!
        WRITE "  Name=",name," State=",state,! }
   WRITE !,"Final Fetch SQLCODE: ",SQLCODE
   &sql(CLOSE EmpCursor)
     IF SQLCODE<0 {WRITE "SQL Close Cursor Error:",SQLCODE," ",%msg  QUIT}

The following Embedded SQL example shows FETCH invoked by an argumentless FOR loop retrieving data from a cursor named EmpCursor. The INTO clause is specified as part of the FETCH statement:

   &sql(DECLARE EmpCursor CURSOR FOR 
        SELECT Name,Home_State FROM Sample.Employee
        WHERE Home_State %STARTSWITH 'M')
   &sql(OPEN EmpCursor)
        IF SQLCODE<0 {WRITE "SQL Open Cursor Error:",SQLCODE," ",%msg  QUIT}
   FOR { &sql(FETCH EmpCursor INTO :name,:state)
        QUIT:SQLCODE'=0  
        WRITE "count: ",%ROWCOUNT," RowID: ",%ROWID,!
        WRITE "  Name=",name," State=",state,! }
   WRITE !,"Final Fetch SQLCODE: ",SQLCODE
   &sql(CLOSE EmpCursor)
     IF SQLCODE<0 {WRITE "SQL Close Cursor Error:",SQLCODE," ",%msg  QUIT}

The following Embedded SQL example shows FETCH invoked using a WHILE loop:

  &sql(DECLARE C1 CURSOR FOR 
        SELECT Name,Home_State INTO :name,:state FROM Sample.Person
        WHERE Home_State %STARTSWITH 'M')
   &sql(OPEN C1)
        IF SQLCODE<0 {WRITE "SQL Open Cursor Error:",SQLCODE," ",%msg  QUIT}
   &sql(FETCH C1)
   WHILE (SQLCODE = 0) {
        WRITE "count: ",%ROWCOUNT," RowID: ",%ROWID,!
        WRITE "  Name=",name," State=",state,!
        &sql(FETCH C1) }
   WRITE !,"Final Fetch SQLCODE: ",SQLCODE
   &sql(CLOSE C1)
     IF SQLCODE<0 {WRITE "SQL Close Cursor Error:",SQLCODE," ",%msg  QUIT}

The following Embedded SQL example shows FETCH retrieving aggregate function values. %ROWID is not set:

    &sql(DECLARE PersonCursor CURSOR FOR 
        SELECT COUNT(*),AVG(Age) FROM Sample.Person )
   &sql(OPEN PersonCursor)
        IF SQLCODE<0 {WRITE "SQL Open Cursor Error:",SQLCODE," ",%msg  QUIT}
   NEW %ROWCOUNT
   FOR { &sql(FETCH PersonCursor INTO :cnt,:avg)
        QUIT:SQLCODE'=0  
        WRITE %ROWCOUNT," Num People=",cnt," Average Age=",avg,! }
   WRITE !,"Final Fetch SQLCODE: ",SQLCODE
   &sql(CLOSE PersonCursor)
     IF SQLCODE<0 {WRITE "SQL Close Cursor Error:",SQLCODE," ",%msg  QUIT}

The following Embedded SQL example shows FETCH retrieving DISTINCT values. %ROWID is not set:

    &sql(DECLARE EmpCursor CURSOR FOR 
        SELECT DISTINCT Home_State FROM Sample.Employee
        WHERE Home_State %STARTSWITH 'M'
        ORDER BY Home_State )
   &sql(OPEN EmpCursor)
        IF SQLCODE<0 {WRITE "SQL Open Cursor Error:",SQLCODE," ",%msg  QUIT}
   NEW %ROWCOUNT
   FOR { &sql(FETCH EmpCursor INTO :state)
        QUIT:SQLCODE'=0  
        WRITE %ROWCOUNT," State=",state,! }
   WRITE !,"Final Fetch SQLCODE: ",SQLCODE
   &sql(CLOSE EmpCursor)
     IF SQLCODE<0 {WRITE "SQL Close Cursor Error:",SQLCODE," ",%msg  QUIT}

The following Embedded SQL example shows that a cursor persists across namespaces. This cursor is declared in %SYS, opened and fetched in USER, and closed in SAMPLES. Note that the OPEN must be executed in the namespace that contains the table(s) being queried, and the FETCH must able to access the output host variables, which are namespace-specific:

    &sql(USE DATABASE %SYS)
    WRITE $ZNSPACE,!
  &sql(DECLARE NSCursor CURSOR FOR SELECT Name INTO :name FROM Sample.Employee)
    &sql(USE DATABASE "USER")
    WRITE $ZNSPACE,!
  &sql(OPEN NSCursor)
        IF SQLCODE<0 {WRITE "SQL Open Cursor Error:",SQLCODE," ",%msg  QUIT}
       NEW SQLCODE,%ROWCOUNT,%ROWID
  FOR { &sql(FETCH NSCursor)
       QUIT:SQLCODE  
       WRITE "Name=",name,! }
    &sql(USE DATABASE SAMPLES)
    WRITE $ZNSPACE,!
  &sql(CLOSE NSCursor)
     IF SQLCODE<0 {WRITE "SQL Close Cursor Error:",SQLCODE," ",%msg  QUIT}