Accessing data from different databases in MapInfo using Map Basic
Difference between Register and Server Link Table:
Register table will give you a table that is connected to the database via a live connection. This means that "no data" is stored locally. MapInfo will always read the data directly from the database. I have written "no data" because data will for a certain time be stored locally but in temporary files
Server Link Table will create a local table with a copy of the data from the database. This means that MapInfo doesn't really need the database when you are using this type of connection. The database will be used when you try to save changes back to the database and when you want to refresh the linked table (get the changes from the database copied down locally).
Let's take a look at how you would connect to a database from MapInfo and open tables from this database into MapInfo.
1. Open a connection to the database
This can be done using the Server_Connect function:
Dim nConn As Integer
Dim sMyDSN, sMyUsername, sMyPassword As String
sMyDSN = "MySQLServer"
sMyUsername = "PEM"
sMyPassword = "ABCDEF"
nConn = Server_Connect("ODBC", "DSN=" & sMyDSN & "; UID=" & sMyUsername & "; PWD=" & sMyPassword)
Where MySQLServer is a Data source on your computer.
2a. Open a live table
This can be done using the Register table statement and the Open Table statement.
Register table "POINTSOFINTEREST"
Type ODBC
Connection Handle nConn
Toolkit "ODBC"
Table "Select * From GISDB.POINTS_OF_INTEREST"
Into "C:\Data\PointsOfInterest.tab"
Open Table "C:\Data\PointsOfInterest.tab"
2b. Open a linked table
This can be done using the Server Link Table statement
Server nConn Link Table
"Select * From GISDB.POINTS_OF_INTEREST"
Into "POINTSOFINTEREST"
Toolkit "ODBC"
File "C:\Data\PointsOfInterest.tab"
Pitfalls
- When using the live options your table needs a primary unique column - an ID that uniquely identifies every row.
- The DNS is not the same as the name of your database on the server
- Do remember to add the owner/schema of the table (owner.tablename)
- The owner/schema is not the same as the name of the database
Description about the various ways to connect to databases:
Server_Connect() function
Purpose
Establishes communications with a remote data server. You can call this function from the MapBasic window in MapInfo Pro.
Syntax
Server_Connect( toolkit, connect_string )
toolkit is a string value identifying the remote interface, for example, "ODBC", "ORAINET". Valid values for toolkit can be obtained from the Server_DriverInfo() function.
connect_string is a string value with additional information necessary to obtain a connection to the database.
Return Value
Integer
Description
The Server_Connect() function establishes a connection to a data source. This function returns a connection number. A connection number is an identifier to the connection. This identifier must be passed to all server statements that you wish to operate on the connection.
The parameter toolkit identifies the MapInfo Pro remote interface toolkit through which the connection to a database server will be made. Information can be obtained about the possible values via calls to the Server_NumDrivers() function and the Server_DriverInfo() function.
The connect_string parameter supplies additional information to the toolkit necessary to obtain a connection to the database. The parameters depend on the requirements of the remote data source being accessed.
The connection string sent to Server_Connect() has the form:
attribute=value[;attribute=value...]
Note: There are no spaces allowed in the connection string.
Passing the DLG=1 connect option provides a connect dialog box with active help buttons.
Microsoft ACCESS Attributes
The attributes used by ACCESS are:
Attribute
Description
DSN
The name of the ODBC data source for Microsoft ACCESS.
UID
The user login ID.
PWD
The user-specified password.
SCROLL
The default value is NO. If SCROLL=YES the ODBC cursor library is used for this connection allowing the ability to fetch first, last, previous, or record n of the database.