The process of retrieving rows from the result set and returning them to the application is called fetching.
An application fetches data with a cursor. A cursor is different from a result set. A result set is the set of rows that matches a particular criteria. A cursor is the software that returns those rows to the application. The name "cursor", as it applies to databases, probably originated from the blinking cursor on a computer terminal. Just as that cursor indicates the current position on the screen and where the typed words will appear next, a cursor on a result set indicates the current position in the result set and what row will be returned next.
Different cursors have different characteristics. The most common type of cursor, which is called a forward-only cursor, can only move forward through the result set. To return to a previous row, the application must close and reopen the cursor, then read rows from the beginning of the result set until it reaches the required row. Forward-only cursors provide a fast mechanism for making a single pass through a result set.
Forward-only cursors are less useful for screen-based applications, in which the user scrolls backward and forward through the data. Such applications can use a forward-only cursor by reading the result set once, caching the data locally, and performing scrolling themselves. However, this works well only with small amounts of data. A better solution is to use a scrollable cursor, which provides random access to the result set. A further way such applications can increase performance is by fetching more than one row of data at a time, using what is called a block cursor.
The forward-only cursor is the default cursor type in ODBC and is discussed in the following section.