Chapter 43 SQL command FETCH

Reposition the cursor and retrieve data from it.

outline

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

parameter

• Cursor name - the name of the currently open cursor.
  The cursor name is specified in the DECLARE command.
  Cursor names are case sensitive.
• Into host variable list - optional - places the data in the fetch operation column into a local variable.
  Host variable list specifies a host variable or a comma separated list of host variables that are targets that contain the data associated with the cursor.
  The INTO sentence is optional.
  If not specified, the FETCH statement locates only the cursor.

describe

In embedded SQL applications, FETCH statements retrieve data from cursors.
The required operation sequence is: DECLARE, OPEN, FETCH, CLOSE.
Attempting to FETCH on an unopened cursor results in an SQLCODE -102 error.

As an SQL statement, this is only supported in embedded SQL.
Using ODBC API through ODBC supports equivalent operations.

The INTO clause can be specified as a clause of a DECLARE statement, a clause of a FETCH statement, or both.
The INTO clause allows the data in the fetch column to be placed in the local host variable.
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 match or support 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 selection list.

When the cursor advances to the end of the data, the FETCH operation is completed.
This sets sqlcode = 100 (no more data).
It also sets the% ROWCOUNT variable to the number of rows obtained.

Note: the value returned by the host variable of the INTO clause is reliable only when SQLCODE=0.
Host variable values should not be used if sqlcode = 100 (no more data).

Cursor names are not namespace specific.
Changing the current namespace has no effect on the use of declaration cursors.
The only namespace to consider is that FETCH must appear in the namespace containing the table to query.

%ROWID

When FETCH retrieves the row of the updatable cursor, it sets% ROWID to the ROWID value of the obtained row.
Updatable cursors are cursors whose top FROM clause contains only one element (table name or updatable view name).

Setting% ROWID for each retrieved row is limited by the following conditions:

• Define cursorname cursor and OPEN cursorname statements do not initialize% ROWID;
  %The ROWID value remains the same as the previous value.
  The first successful FETCH set% ROWID.
  Each subsequent FETCH retrieval row resets% ROWID to the current ROWID.
  FETCH sets% ROWID if retrieving rows of updatable cursors.
  If the cursor is not updatable,% ROWID will remain unchanged.
  If there are no rows matching the query selection criteria, FETCH will not change the previous% ROWID value.
  When SQLCODE 100 (No Data, or No More Data) is issued by CLOSE or FETCH,% ROWID contains the ROWID of the last row retrieved.
• Cursor based SELECT with DISTINCT keyword or GROUP BY clause will not set% ROWID.
  %The ROWID value remains the same as the previous value (if any).
• Cursor based SELECT only performs aggregation and does not set% ROWID.
  %The ROWID value remains the same as the previous value (if any).

Embedded SQL SELECT that does not declare cursors will not set% ROWID.
The% ROWID value remains unchanged after a simple SELECT statement is completed.

FETCH for UPDATE or DELETE

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

ClassMethod FETCH()
{
	s $NAMESPACE="Samples"
	&sql(
		DECLARE MyCursor CURSOR FOR SELECT %ID,Status
		FROM Sample.Quality WHERE Status='Bad' FOR UPDATE
	)
	&sql(
		OPEN MyCursor
	)
	if SQLCODE<0 {
		w "SQL Open Cursor error:",SQLCODE," ",%msg  
		q
	}
	n %ROWCOUNT,%ROWID
	for {
		&sql(
			FETCH MyCursor
		)  
		q:SQLCODE'=0
		&sql(
			DELETE FROM Sample.Quality WHERE CURRENT OF MyCursor
		) 
	}
	w !,"Number of rows updated=",%ROWCOUNT
	&sql(
		CLOSE MyCursor
	)
	if SQLCODE<0 {
		w "SQL Close cursor error:",SQLCODE," ",%msg  
		q
	}
}

Example

The following embedded SQL example shows a nonparametric FOR loop calling FETCH to retrieve data from a cursor named EmpCursor.
The INTO clause specifies in the DECLARE statement:

ClassMethod FETCH1()
{
	&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 {
		w "SQL Open Cursor error:",SQLCODE," ",%msg  
		q
	}
	n %ROWCOUNT,%ROWID
	for { 
		&sql(
			FETCH EmpCursor
		)
		q:SQLCODE'=0  
		w "count: ",%ROWCOUNT," RowID: ",%ROWID,!
		w "  Name=",name," State=",state,! 
	}
	w !,"Final Fetch SQLCODE: ",SQLCODE
	&sql(
		CLOSE EmpCursor
	)
	if SQLCODE<0 {
		w "SQL Close cursor error:",SQLCODE," ",%msg  
		q
	}
}

The following embedded SQL example shows a nonparametric FOR loop calling FETCH to retrieve data from a cursor named EmpCursor.
The INTO clause is specified as part of the FETCH statement:

ClassMethod FETCH2()
{
	&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 {
		w "SQL Open Cursor error:",SQLCODE," ",%msg  
		q
	}
	&sql(FETCH C1)
	while (SQLCODE = 0) {
		w "count: ",%ROWCOUNT," RowID: ",%ROWID,!
		w "  Name=",name," State=",state,!
		&sql(FETCH C1) 
	}
	w !,"Final Fetch SQLCODE: ",SQLCODE
	&sql(CLOSE C1)
	if SQLCODE<0 {
		w "SQL Close cursor error:",SQLCODE," ",%msg  
		q
	}
}

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

ClassMethod FETCH3()
{
	&sql(
		DECLARE PersonCursor CURSOR FOR 
		SELECT COUNT(*),AVG(Age) FROM Sample.Person 
	)
	&sql(OPEN PersonCursor)
	if SQLCODE<0 {
		w "SQL Open Cursor error:",SQLCODE," ",%msg  
		q
	}
	n %ROWCOUNT
	for { 
		&sql(
			FETCH PersonCursor INTO :cnt,:avg
		)
		q:SQLCODE'=0  
		w %ROWCOUNT," Num People=",cnt," Average Age=",avg,! 
	}
	w !,"Final Fetch SQLCODE: ",SQLCODE
	&sql(CLOSE PersonCursor)
	if SQLCODE<0 {
		w "SQL Close cursor error:",SQLCODE," ",%msg  
		q
	}
}

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

ClassMethod FETCH4()
{
	&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 {
		w "SQL Open Cursor error:",SQLCODE," ",%msg  
		q
	}
	n %ROWCOUNT
	for { 
		&sql(
			FETCH EmpCursor INTO :state
		)
		q:SQLCODE'=0  
		w %ROWCOUNT," State=",state,! 
	}
	w !,"Final Fetch SQLCODE: ",SQLCODE
	&sql(CLOSE EmpCursor)
	if SQLCODE<0 {
		w "SQL Close cursor error:",SQLCODE," ",%msg  
		q
	}
}

The following embedded SQL example shows cursor persistence across namespaces.
The cursor is declared in% SYS, opened and retrieved in USER, and closed in SAMPLES.
Note that OPEN must be executed in the namespace containing the table to be queried, and FETCH must be able to access the output host variables, which are namespace specific:

ClassMethod FETCH5()
{
	&sql(USE DATABASE %SYS)
	w $ZNSPACE,!
	&sql(DECLARE NSCursor CURSOR FOR SELECT Name INTO :name FROM Sample.Employee)
	&sql(USE DATABASE "USER")
	w $ZNSPACE,!
	&sql(OPEN NSCursor)
	if SQLCODE<0 {
		w "SQL Open Cursor error:",SQLCODE," ",%msg  
		q
	}
	n SQLCODE,%ROWCOUNT,%ROWID
	for { 
		&sql(FETCH NSCursor)
		q:SQLCODE  
		w "Name=",name,! 
	}
	&sql(USE DATABASE SAMPLES)
	w $ZNSPACE,!
	&sql(CLOSE NSCursor)
	if SQLCODE<0 {
		w "SQL Close cursor error:",SQLCODE," ",%msg  
		q
	}
}