Informix Guide to SQL: Syntax SQL Statements

Use the FETCH statement to move a cursor to a new row in the active set and to retrieve the row values from memory.

Use this statement with ESQL/C.

The way the database server creates and stores members of the active set and then fetches rows from the active set differs depending on whether the cursor is a sequential cursor or a scroll cursor.

In X/Open mode, if a cursor-direction value (such as NEXT or RELATIVE) is specified, a warning message is issued, indicating that the statement does not conform to X/Open standards.

A sequential cursor can fetch only the next row in sequence from the active set. The sole cursor-position option that is available to a sequential cursor is the default value, NEXT. A sequential cursor can read through a table only once each time it is opened. The following ESQL/C example illustrates the FETCH statement with a sequential cursor:

EXEC SQL FETCH seq_curs into :fname, :lname;
EXEC SQL FETCH NEXT seq_curs into ;fname, :lname;

When the program opens a sequential cursor, the database server processes the query to the point of locating or constructing the first row of data. The goal of the database server is to tie up as few resources as possible.

Because the sequential cursor can retrieve only the next row, the database server can frequently create the active set one row at a time. On each FETCH operation, the database server returns the contents of the current row and locates the next row. This one-row-at-a-time strategy is not possible if the database server must create the entire active set to determine which row is the first row (as would be the case if the SELECT statement included an ORDER BY clause).

A scroll cursor can fetch any row in the active set, either by specifying an absolute row position or a relative offset. Use the following cursor-position options to specify a particular row that you want to retrieve.

The following ESQL/C examples illustrate the FETCH statement with a scroll cursor:

EXEC SQL fetch  previous  q_curs  into  :orders;



EXEC SQL fetch  last  q_curs  into  :orders;



EXEC SQL fetch  relative  -10  q_curs  into  :orders;



printf("Which  row? ");

scanf("%d",row_num); 

EXEC SQL fetch  absolute  :row_num  q_curs  into  :orders;


Tip: Do not confuse row-position values with rowid values. A rowid value is based on the position of a row in its table and remains valid until the table is rebuilt. A row-position value (a value introduced by the ABSOLUTE keyword) is based on the position of the row in the active set of the cursor; the next time the cursor is opened, different rows might be selected.

The database server must retain all the rows in the active set for a scroll cursor until the cursor closes, because it cannot be sure which row the program asks for next. When a scroll cursor opens, the database server implements the active set as a temporary table although it might not fill this table immediately.

The first time a row is fetched, the database server copies it into the temporary table as well as returning it to the program. When a row is fetched for the second time, it can be taken from the temporary table. This scheme uses the fewest resources in case the program abandons the query before it fetches all the rows. Rows that are never fetched are usually not created or are saved in a temporary table.

Each value from the select list of the query or the output of the executed user-defined function must be returned into a memory location. You can specify these destinations in one of the following ways:

• Use the INTO clause of a SELECT statement.
• Use the INTO clause of an EXECUTE Function (or EXECUTE PROCEDURE) statement.
• Use the INTO clause of a FETCH statement.
• Use a system-descriptor area.
• Use an sqlda structure.

When you associate a SELECT, or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement with the cursor (a function cursor), the statement can contain an INTO clause to specify the program variables that are to receive the return values.

You can use this method only when you write the SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement as part of the cursor declaration (see DECLARE).

In this case, the FETCH statement cannot contain an INTO clause.

The following example uses the INTO clause of the SELECT statement to specify program variables in ESQL/C:

EXEC SQL  declare ord_date  cursor for

	select  order_num,  order_date,  po_num

		into  :o_num,  :o_date,  :o_po;

EXEC SQL open  ord_date;

EXEC SQL fetch  next  ord_date;

Use an indicator variable if the returned data might be null.

If you prepare a SELECT statement, the SELECT cannot include the INTO clause so you must use the INTO clause of the FETCH statement.

When you create a SELECT statement dynamically, you cannot use an INTO clause because you cannot name host variables in a prepared statement. If you are certain of the number and data type of values in the select list, you can use an INTO clause in the FETCH statement. However, if user input generated the query, you might not be certain of the number and data type of values that are being selected. In this case, you must use a system descriptor or sqlda pointer structure

When the SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement omits the INTO clause, you must specify the destination of the data whenever a row is fetched. For example, to dynamically execute a SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement, the SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) cannot include its INTO clause in the PREPARE statement. Therefore, the FETCH statement must include an INTO clause to retrieve data into a set of variables. This method lets you store different rows in different memory locations.

In the following ESQL/C example, a series of complete rows is fetched into a program array. The INTO clause of each FETCH statement specifies an array element as well as the array name.

EXEC SQL BEGIN DECLARE SECTION;

	char wanted_state[2];

	short int row_count = 0;

	struct customer_t{

	{

		int    c_no;

		char   fname[15];

		char   lname[15];

	} cust_rec[100];

EXEC SQL END DECLARE SECTION;



main()

{

	EXEC SQL connect to'stores_demo';

	printf("Enter 2-letter state code: ");

	scanf ("%s", wanted_state);



	EXEC SQL declare cust cursor for 

		select * from customer where state = :wanted_state;



	EXEC SQL open cust;



	EXEC SQL fetch cust into :cust_rec[row_count];

	while (SQLCODE == 0)

	{

		printf("\n%s %s", cust_rec[row_count].fname,

			cust_rec[row_count].lname);



		row_count++;

		EXEC SQL fetch cust into :cust_rec[row_count];

	}

	printf ("\n");

	EXEC SQL close cust;

	EXEC SQL free cust;

}

You can fetch into a program-array element only by using an INTO clause in the FETCH statement. When you are declaring a cursor, do not refer to an array element within the SQL statement.

Tip: If you are certain of the number and data type of values in the select list, you can use an INTO clause in the FETCH statement.

Using a System-Descriptor Area

You can use a system-descriptor area to store output values when you do not know the number of return values or their data types that a SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement returns at runtime. A system-descriptor area describes the data type and memory location of one or more return values.

The keywords USING SQL DESCRIPTOR introduce the name of the system-descriptor area into which you fetch the contents of a row or the return values of a user-defined function. You can then use the GET DESCRIPTOR statement to transfer the values that the FETCH statement returns from the system-descriptor area into host variables.

The following example shows a valid FETCHUSING SQL DESCRIPTOR statement:

EXEC SQL allocate descriptor 'desc';

...

EXEC SQL declare selcurs cursor for 

	select * from customer where state = 'CA';

EXEC SQL describe selcurs using sql descriptor 'desc';

EXEC SQL open selcurs;

while (1)

	{

	EXEC SQL fetch selcurs using sql descriptor 'desc';

	...

You can also use an sqlda structure to dynamically supply parameters. However, a system-descriptor area conforms to the X/Open standards.

You can use a pointer to an sqlda structure that stores the output values when you do not know the number of return values or their data types that a SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement returns at runtime. This structure contains data descriptors that specify the data type and memory location for one selected value. The keywords USING DESCRIPTOR introduce the name of the sqlda pointer structure.

Tip: If you are certain of the number and data type of values in the select list, you can use an INTO clause in the FETCH statement. For more information, see Using the INTO Clause of FETCH.

To specify an sqlda structure as the location of parameters, follow these steps:

1. Declare an sqlda pointer variable.
2. Use the DESCRIBE statement to fill in the sqlda structure.
3. Allocate memory to hold the data values.
4. Use the USING DESCRIPTOR clause of the FETCH statement to name the sqlda structure as the location into which you fetch the return values.

The following example shows a FETCH USING DESCRIPTOR statement:

struct sqlda *sqlda_ptr;

...

EXEC SQL declare selcurs2 cursor for 

	select * from customer where state = 'CA';

EXEC SQL describe selcurs2 into sqlda_ptr;

...

EXEC SQL open selcurs2;

while (1)

	{

	EXEC SQL fetch selcurs2 using descriptor sqlda_ptr;

	...

The sqld value specifies the number of output values that are described in occurrences of the sqlvar structures of the sqlda structure. This number must correspond to the number of return values from the prepared statement.

The FETCH statement does not ordinarily lock a row that is fetched. Thus, another process can modify (update or delete) the fetched row immediately after your program receives it. A fetched row is locked in the following cases:

• When you set the isolation level to Repeatable Read, each row you fetch is locked with a read lock to keep it from changing until the cursor closes or the current transaction ends. Other programs can also read the locked rows.
• When you set the isolation level to Cursor Stability, the current row is locked.
• In an ANSI-compliant database, an isolation level of Repeatable Read is the default; you can set it to something else.
• When you are fetching through an update cursor (one that is declared FOR UPDATE), each row you fetch is locked with a promotable lock. Other programs can read the locked row, but no other program can place a promotable or write lock; therefore, the row is unchanged if another user tries to modify it using the WHERE CURRENT OF clause of an UPDATE or DELETE statement.

When you modify a row, the lock is upgraded to a write lock and remains until the cursor is closed or the transaction ends. If you do not modify the row, the behavior of the database server depends on the isolation level you have set. The database server releases the lock on an unchanged row as soon as another row is fetched, unless you are using Repeatable Read isolation (see SET ISOLATION).

Important: You can hold locks on additional rows even when Repeatable Read isolation is not in use or is unavailable. Update the row with unchanged data to hold it locked while your program is reading other rows. You must evaluate the effect of this technique on performance in the context of your application, and you must be aware of the increased potential for deadlock.

When you use explicit transactions, be sure that a row is both fetched and modified within a single transaction; that is, both the FETCH statement and the subsequent UPDATE or DELETE statement must fall between a BEGIN WORK statement and the next COMMIT WORK statement.

A collection cursor allows you to access the individual elements of an ESQL/C collection variable. To declare a collection cursor, use the DECLARE statement and include the Collection Derived Table segment in the SELECT statement that you associate with the cursor. Once you open the collection cursor with the OPEN statement, the cursor allows you to access the elements of the collection variable.

To fetch elements, one at a time, from a collection cursor, use the FETCH statement and the INTO clause. The FETCH statement identifies the collection cursor that is associated with the collection variable. The INTO clause identifies the host variable that holds the element value that is fetched from the collection cursor. The data type of the host variable in the INTO clause must match the element type of the collection.

Suppose you have a table called children with the following structure:

CREATE TABLE children

(
	age			SMALLINT,
	name			VARCHAR(30),
	fav_colors			SET(VARCHAR(20) NOT NULL),
)

The following ESQL/C code fragment shows how to fetch elements from the child_colors collection variable:

EXEC SQL BEGIN DECLARE SECTION;

	client collection child_colors;

	varchar one_favorite[21];

	char child_name[31] = "marybeth";

EXEC SQL END DECLARE SECTION;

EXEC SQL allocate collection :child_colors;

/* Get structure of fav_colors column for untyped 

  * child_colors collection variable */

EXEC SQL select fav_colors into :child_colors

	from children

	where name = :child_name;
/* Declare select cursor for child_colors collection

  * variable */

EXEC SQL declare colors_curs cursor for 

	select * from table(:child_colors);

EXEC SQL open colors_curs;

do

{

	EXEC SQL fetch colors_curs into :one_favorite;

	...

} while (SQLCODE == 0)

EXEC SQL close colors_curs;

EXEC SQL free colors_curs;

EXEC SQL deallocate collection :child_colors;

Once you have fetched a collection element, you can modify the element with the UPDATE or DELETE statements. For more information, see the UPDATE and DELETE statements in this manual. You can also insert new elements into the collection variable with an INSERT statement. For more information, see the INSERT statement.

You can use the SQLSTATE variable to check the result of each FETCH statement. The database server sets the SQLSTATE variable after each SQL statement. If a row is returned successfully, the SQLSTATE variable contains the value 00000. If no row is found, the database server sets the SQLSTATE code to 02000, which indicates no data found, and the current row is unchanged. The following conditions set the SQLSTATE code to 02000, indicating no data found:

• The active set contains no rows.
• You issue a FETCH NEXT statement when the cursor points to the last row in the active set or points past it.
• You issue a FETCH PRIOR or FETCH PREVIOUS statement when the cursor points to the first row in the active set.
• You issue a FETCH RELATIVE n statement when no nth row exists in the active set.
• You issue a FETCH ABSOLUTE n statement when no nth row exists in the active set.

The database server copies the SQLSTATE code from the RETURNED_SQLSTATE field of the system-diagnostics area. You can use the GET DIAGNOSTICS statement to examine the RETURNED_SQLSTATE field directly. The system-diagnostics area can also contain additional error information.

You can also use SQLCODE of sqlca to determine the same results.

Related statements: ALLOCATE DESCRIPTOR, CLOSE, DEALLOCATE DESCRIPTOR, DECLARE, DESCRIBE, GET DESCRIPTOR, OPEN, PREPARE, SET DEFERRED_PREPARE, and SET DESCRIPTOR

For a task-oriented discussion of the FETCH statement, see the Informix Guide to SQL: Tutorial.

For more information about concepts relating to the FETCH statement, see the Informix ESQL/C Programmer's Manual.