Fetch statement - MapBasic - 2023

Purpose

Sets a table's cursor position (for example, which row is the current row). You can issue this statement from the MapBasic window in MapInfo Pro.

Syntax

Fetch { First | Last | Next | Prev | Rec n } From table 

n is the number of the record to read.

table is the name of an open table.

Description

Use the Fetch statement to retrieve records from an open table. By issuing a Fetch statement, your program places the table cursor at a certain row position in the table; this dictates which of the records in the table is the "current" record.

After you issue a Fetch statement, you can retrieve data from the current row by using one of the following expression types:

World.Country

World.col1

World.col( 1 )

A Fetch First statement positions the cursor at the first un-deleted row in the table.

A Fetch Last statement positions the cursor at the last un-deleted row in the table.

A Fetch Next statement moves the cursor forward to the next un-deleted row.

A Fetch Prev statement moves the cursor backward to the previous un-deleted row.

A Fetch Rec n statement positions the cursor on a specific row, even if that row is deleted.

Various MapInfo Pro and MapBasic operations (for example, Select, Update, and screen redraws) automatically reset the current row. Accordingly, Fetch statements should be issued just before any statements that make assumptions about which row is current.

Reading Past the End of the Table

After you issue a Fetch statement, you may need to call the EOT() function to determine whether you fetched an actual row.

If the Fetch statement placed the cursor on an actual row, the EOT() function returns FALSE (meaning, there is not an end-of-table condition).

If the Fetch statement attempted to place the cursor past the last row, the EOT() function returns TRUE (meaning, there is an end-of-table condition; therefore there is no "current row").

The following example shows how to use a Fetch Next statement to loop through all rows in a table. As soon as a Fetch Next statement attempts to read past the final row, the EOT() function returns TRUE, causing the loop to halt.

Dim i As Integer

	i = 0 
	Fetch First From world 
	Do While Not EOT(world) 
		i = i + 1 
		Fetch Next From world
	Loop 

	Print "Number of undeleted records: " + i 

Fetching Data from GeoPackage Tables with Nonsequential Rows

MapInfo tables always assume rows one to n are sequential. However, GeoPackage tables do not always have sequential data; there may be empty rows, rows that start after one (1), and gaps in row IDs. This must be taken into consideration when using MapBasic operations to fetch data from GeoPackage tables.

For GeoPackage tables, the MapBasic RowId value is a table's primary key value and not the row enumeration value. A Fetch Rec 3 command returns the row with primary_key value of three (3) if it exists. It does not return the third record in the table. Do not fetch using a specific row ID. Instead, use Fetch First, Fetch Next, Fetch Last instead of Fetch Rec #.

The following example loops through a nonsequential table:

Dim iCount as integer 
fetch first from USA_NonSeq 
For iCount = 1 to TableInfo(USA_NonSeq, TAB_INFO_NROWS) 
	'COL5 is an integer column in USA_NonSeq table 
	Print USA_NonSeq.COL5 
	fetch next from USA_NonSeq 
next

Examples

The following example shows how to fetch the 3rd record from the table States:

Open Table "states" 
Fetch Rec 3 From states 'position at 3rd record
Note states.state_name 'display name of state 

As illustrated in the example below, the Fetch statement can operate on a temporary table (for example, Selection).

Select * From states Where pop_1990 < pop_1980
Fetch First From Selection
Note Selection.col1 + " has negative net migration"

See Also: