Server Fetch statement - MapBasic - 2023

Purpose

Retrieves result set rows from a remote data server. You can issue this statement from the MapBasic window in MapInfo Pro.

Syntax

Server StatementNumber Fetch [ NEXT | PREV | FIRST | LAST | [ REC ] recno ] 

or

Server StatementNumber Fetch INTO Table [ FILE path ]

StatementNumber is an integer value that identifies information about an SQL statement.

recno is an integer representing the record to fetch.

path is the path to an existing table.

Description

The Server Fetch statement retrieves result set data (specified by the StatementNumber) from the database server. For fetching the data one row at a time, it is placed in local storage and can be bound to variables with the Server Bind Column statement, or retrieved one column at a time with the Server_ColumnInfo(SRV_COL_INFO_VALUE) function. The other option is to fetch an entire result set into a MapInfo table at once, using the Into Table clause.

The Server Fetch and Server Fetch Into statements halt and set the error code ERR() = ERR_SRV_ESC if the user presses Esc. This allows your MapBasic application using the Server Fetch statements to handle the escape.

Following a Server Fetch Into statement, the MapInfo table is committed and there are no outstanding transactions on the table. All character fields greater than 254 bytes are truncated. All binary fields are downloaded as double length hexadecimal character strings. The column names for the downloaded table will use the column alias name if a column alias is specified in the query.

Null Handling

When you execute a Select statement and fetch a row containing a table column that contains a null, the following behavior occurs. There is no concept of null values in a MapInfo table or variable, so the default value is used within the domain of the data type. This is the value of a MapBasic variable that is DIMed but not set. However, an Indicator is provided that the value returned was null.

For Bound variables (see Server Bind Column statement), a status variable can be specified and its value will indicate if the value was null following the fetch. For unbound columns, SRV_COL_INFO with the Attr type SRV_COL_INFO_STATUS will return the status which can indicate null.

Refer to the MapBasic User Guide for information on how MapInfo Pro interprets data types.

Error Conditions

The command Server n Fetch Into table generates an error condition if any attempts to insert records into the local MapInfo table fail. The commands Server n Fetch [Next|Prev|recno] generate errors if the desired record is not available.

Example 1

' An example of Server Fetch downloading into a MapInfo table
Dim hdbc, hstmt As Integer
hdbc = Server_Connect("ODBC", "DLG=1")
hstmt = Server_Execute(hdbc, "Select * from emp")
Server hstmt Fetch Into "MyEmp"
Server hstmt Close

Example 2

' An example of Server Fetch using bound variables
Dim hdbc, hstmt As Integer
dim NameVar, AddrVar as String
dim NameStatus, AddrStatus as Integer
hdbc = Server_Connect("ODBC", "DLG=1")
hstmt = Server_Execute(hdbc, "Select Name, Addr from emp")
Server hstmt Bind Column 1 to NameVar, NameStatus
Server hstmt Bind Column 2 to AddrVar, AddrStatus
Server hstmt Fetch Next
While Not Server_Eot(hstmt)
	Print "Name = " + NameVar + "; Address = " + AddrVar
	Server hstmt Fetch Next
Wend

See Also:

Server_ColumnInfo() function