Server Link Table statement - MapBasic - 2023

Purpose

Creates a linked MapInfo table (TAB file). This statement establishes a connection to a database server and linked table. A linked table identifies the remote data to be updated and stores metadata in a TAB file. You can issue this statement from the MapBasic window in MapInfo Pro.

Syntax 1

Server Link Table 
	SQLQuery 
	Using ConnectionString 
	[ Symbol... ] [ Linestyle Pen(...) ]
	[ Regionstyle Pen(...) Brush(...) ]
	Into TableName 
	Toolkit Toolkitname
	[ File filespec ] 
	[ Charset char_set ]
	[ Type { NATIVE | NATIVEX } ]
	[ ReadOnly ]
	[ Autokey { Off | On } ]

Syntax 2

Server ConnectionNumber Link Table 
	SQLQuery 
	Toolkit toolkitname
	[ Symbol... ] [ Linestyle Pen(...) ]
	[ Regionstyle Pen(...) Brush(...) ]
	Into TableName 
	[ File filespec ] 
	[ Charset char_set ]
	[ Type { NATIVE | NATIVEX } ]
	[ ReadOnly ]
	[ Autokey { Off | On } ]

ConnectionNumber is an integer value that identifies an existing connection.

SQLQuery is a SQL query statement (in native SQL dialect plus object keywords) that generates a result set. The MapInfo linked table is linked to this result set.

ConnectionString is a string used to connect to a database server. See Server_Connect( ) function.

TableName is the alias of the MapInfo table to create.

filespec is an optional tab filename. If the parameter is not present, the tab filename is created based on the alias and current directory. If a filespec is given and a tab file with this name already exists, an error occurs.

char_set is the name of a character set; see CharSet clause.

Toolkitname is a string indicating the type of connection, ODBC or ORAINET.

Description

This statement creates a linked MapInfo table on disk. The table is opened and enqueued. This table is considered a MapInfo base table under most circumstances, except the following: The MapBasic Alter Table statement will fail with linked tables. Linked tables cannot be packed. The Pack Table dialog box will not list linked tables. Use the Server Link Table syntax to establish a connection to a database server and to link a table. Use the Server ConnectionNumber Link Table to link a table using an existing connection. Linked tables contain information to reestablish connections and identify the remote data to be updated. This information is stored as metadata in the tab file.

The absence of the ReadOnly keyword does not indicate that the table is editable. The linked table can be read-only under any of the following circumstances: the result set is not editable; the result set does not contain a primary key; there are no editable columns in the result set; and, the ReadOnly keyword is present. If the server is Oracle, Autokey indicates if the key auto-increment is used or not.

If Autokey is set On, the table will be opened with key auto-increment option. If Autokey is set Off or this option is ignored, the table will be opened without key auto-increment.

The Symbol clause specifies a symbol style for point objects.

The Brush clause specifies a fill style for graphic objects.

The Linestyle clause specifies a line style for line object types.

The Regionstyle clause specifies the line style and fill style for region object types.

The optional Charset clause saves to a specific character set. If not provided, the character set of the source table is assumed. Using an incompatible character set may cause the character data in the destination table to convert into underscores (such as WindowsCyrillic to WindowsLatin1). The preference is to convert to or from UTF-8 or UTF-16 character sets. Use a Charset clause with the Type clause.

The optional Type clause indicates the format of the local table, as either MapInfo (NATIVE) or MapInfo Extended (NATIVEX). The NATIVEX format supports tables larger than 2GB in size and with UTF-8 and UTF-16 character sets. When the The Type clause is not specified, then the system_charset is used (its default is NATIVE). When NATIVEX is specified, the .tab file written out uses the type LINKEDX.

ReadOnly indicates that the table should not be edited.

SQL Query Syntax

The MapInfo keyword OBJECT may be used to reference the spatial column(s) within the SQL Query. MapInfo Pro translates the keyword OBJECT into the appropriate spatial column(s). A SELECT*FROM tablename will always pick up the spatial columns, but if you want to specify a subset of columns, use the keywords OBJECT. For example:

SELECT col1, col2, OBJECT
FROM tablename

will download the two columns plus the spatial object. This syntax will work for any database that MapInfo Pro supports.

MapInfo Pro Spatial Query

MapInfo Pro supports the keyword WITHIN which is used for spatial queries. It is used for selecting spatial objects in a table that exists within an area identified by a spatial object. The following two keywords may be used along with the WITHIN keyword:

• CURRENT MAPPER: entire rectangular area shown in the current Map window.
• SELECTION: area within the selection n the current Map window.

The syntax to find all of the rows in a table with a spatial object that exists within the current Map window would be as follows:

SELECT col1, col2, OBJECT FROM tablename
WHERE OBJECT WITHIN CURRENT_MAPPER

This syntax will work for any database that MapInfo Pro supports. MapInfo Pro will also execute spatial SQL queries that are created using the native SQL syntax for the spatial database. Valid values for toolkitname can be found in Server_DriverInfo( ) function.

Examples

Declare Sub Main
Sub Main
	Open table "C:\mapinfo\data\states.tab"
	Server Link Table "Select * from Statecap" Using 
		"DSN=MS Access;DBQ=C:\MSOFFICE\ACCESS\DB1.mdb"
		Into test File "C:\tmp\test"
	Map From Test,States
End Sub 'Main
Declare Sub Main
Sub Main
	Dim ConnNum As Integer 
	ConnNum = Server_Connect("ODBC","DSN=SQS;PWD=sysmal;SRVR=seneca")
	Server ConnNum Link Table									
		"Select * from CITY_1"
	Into temp
	Map From temp 

	Server ConnNum Disconnect 
End Sub 

The following example creates a linked table.

Dim hdbc As Integer
hdbc = Server_Connect("ORAINET", "SRVR=ONTARIO;UID=MIPRO;PWD=MIPRO")
Server hdbc link table 
	"Select *  From ""MIPRO"".""SMALLINTEGER""" 
	Toolkit "ORAINET"
	Into SMALLINTEGER
	Autokey ON
Map From SMALLINTEGER

See Also:

Close Table statement, Commit Table statement, Drop Table statement, Rollback statement, Save File statement, Server Refresh statement, Unlink statement