Server_Connect() function - MapBasic - 2023

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, ORAINET and FDO. 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...]

Passing the DLG=1 connect option provides a connect dialog box with active help buttons.

See one of the next sections for the database type you are connecting to:

• Connecting to Microsoft ACCESS
• Connecting to SQLite
• Connecting to SQL SERVER
• Connecting to Oracle Spatial
• Connecting to POSTgreSQL

Connecting to Microsoft ACCESS

An example of a connection string for Microsoft ACCESS is:

"DSN=MI ACCESS;UID=ADMIN;PWD=SECRET"

The attributes used by ACCESS are:

This example connects to the MS ACCESS file named atstest.mdb:

Server_connect("ODBC", "DRIVER=Microsoft Access Driver (*.mdb, *.accdb);
MDB;DBQ=atstest.mdb;DefaultDir=C:\database\odbc\;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;SCROLL=No")

Connecting to SQLite

You must specify "FDO" as the toolkit type to connect to a SQLite database. For examples of how to connect to a SQLite database, see Accessing/Updating Remote Databases with Linked Tables in the MapBasic User Guide.

An example of a connection string for SQLite is:

"File=C:\Data\MIPRO71252.sqlite;PROVIDER=OSGeo.SQLite;SCROLL=NO"

The attributes used by SQLite are:

The following example opens a linked table from a SQLite (FDO) database. This example:

1. Connects to a SQLite database named MIPRO71252.sqlite.
2. Opens a table named EN_STATE, so that the open table is linked to the database, in NativeX (MapInfo Extended) format, and uses the UTF-8 character set. In this example, username is the Windows user account.
3. Opens a Map window to display the EN_STATE data.

dim i as integer 
	i = Server_Connect("FDO", "File=C:\Data\MIPRO71252.sqlite;PROVIDER=OSGeo.SQLite;SCROLL=NO")
	
Server i Link Table "select * from EN_STATE" ToolKit "FDO" INTO "EN_STATE" FILE "C:\Users\username\Documents\EN_STATE.tab" TYPE NATIVEX Charset "UTF-8"
	
map from EN_STATE

The following example connects to a SQLite database and opens a live table with cache turned on. This example:

1. Connects to a SQLite database named MIPRO71252.sqlite.
2. Registers a table named EN_STATE, indicates that it uses the UTF-8 character set, and opens it in NativeX (MapInfo Extended) format. In this example, username is the Windows user account.
3. Opens the EN_STATE table in interactive mode, so that changes are made live (directly to the table in the database).
4. Opens a Map window to display the EN_STATE data.

dim i as integer 
	i = Server_Connect("FDO", "File=C:\Data\MIPRO71252.sqlite;PROVIDER=OSGeo.SQLite;SCROLL=NO")

Register Table "EN_STATE" Type ODBC Charset "UTF-8" Table "select * from EN_STATE" Connection Handle i ToolKit "FDO" Cache ON TYPE NATIVEX Into "C:\Users\username\Documents\EN_STATE.tab"

Open Table "C:\Users\username\Documents\EN_STATE.tab" Interactive

Map From EN_STATE

Omitting the Charset and TYPE clauses when registering a table causes it to open in Native (MapInfo) format using the default system character set from the Windows operating system.

Replacing the Handle clause with the path to the SQLite database opens the table without prior connection to the database (so you do not need the dim . . . Server_Connect statement).

Register Table "EN_STATE" Type ODBC Charset "UTF-8" Table "select * from ""EN_STATE""" Connection "File=C:\Data\MIPRO71252.sqlite;PROVIDER=OSGeo.SQLite;SCROLL=NO" ToolKit "FDO" Cache ON TYPE NATIVEX Into "C:\Users\username\Documents\EN_STATE.tab"

Connecting to SQL SERVER

If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which section in the system information to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in system information. These values are not written to the system information.

The connection string has the form:

DSN=data_source_name[;attribute=value[;attribute=value]...]

An example of a connection string that uses JOHN for the user ID (UID) and XYZZY for the password (PWD) for SQL Server is:

DSN=Accounting;UID=JOHN;PWD=XYZZY

The paragraphs that follow give the long and short names, when applicable, for each attribute, as well as a description. The defaults listed are initial defaults that apply when no value is specified in either the connection string or in the data source definition in the system information. If you specified a value for the attribute when configuring the data source, that value is the default.

Address: The network address of the server running SQL Server. Used only if the Server keyword does not specify the network name of a server running SQL Server. Address is usually the network name of the server, but can be other names such as a pipe, or a TCP/IP port and socket address. For example, on TCP/IP: 199.199.199.5, 1433 or MYSVR, 1433.

AnsiNPW: AnsiNPW={yes | no}. Determines whether ANSI-defined behaviors are exposed. When set to yes, the driver uses ANSI-defined behaviors for handling NULL comparisons, character data padding, warnings, and NULL concatenation. When set to no, ANSI-defined behaviors are not exposed.

APP: The name of the application calling SQLDriverConnect (optional). If specified, this value is stored in the master.dbo.sysprocesses column program_name and is returned by sp_who and the Transact-SQL APP_NAME function.

DATABASE: The name of the default SQL Server database for the connection. If DATABASE is not specified, the default database defined for the login is used. The default database from the ODBC data source overrides the default database defined for the login. The database must be an existing database unless AttachDBFileName is also specified. If AttachDBFileName is specified, the primary file it points to is attached and given the database name specified by DATABASE.

LANGUAGE: The SQL Server language name (optional). SQL Server can store messages for multiple languages in sysmessages. If connecting to a SQL Server with multiple languages, this attribute specifies which set of messages are used for the connection.

Network: The name of a network library dynamic-link library. The name need not include the path and must not include the .dll file name extension, for example, Network=dbnmpntw.

PWD: The password for the SQL Server login account specified in the UID parameter. PWD need not be specified if the login has a NULL password or when using Windows NT authentication (Trusted_Connection=yes).

QueryLogFile: The full path and file name of a file to be used for logging data about long-running queries.

QueryLog_On: QueryLog_On={yes | no}. Determines whether long-running query data is logged. When set to yes, logging long-running query data is enabled on the connection. When set to no, long-running query data is not logged.

QueryLogTime: A digit character string specifying the threshold (in milliseconds) for logging long-running queries. Any query that does not receive a response in the time specified is written to the long-running query log file.

QuotedID: QuotedID={yes | no}. Determines whether QUOTED_IDENTIFIERS is set ON or OFF for the connection. When set to yes, QUOTED_IDENTIFIERS is set ON for the connection, and SQL Server uses the SQL-92 rules regarding the use of quotation marks in SQL statements. When set to no, QUOTED_IDENTIFIERS is set OFF for the connection, and SQL Server uses the legacy Transact-SQL rules regarding the use of quotation marks in SQL statements.

Regional: Regional={yes | no}. Determines how currency, date, and time data are converted. When set to yes, the SQL Server Wire Protocol driver uses client settings when converting currency, date, datetime, and time data to character data. The conversion is one way only; the driver does not recognize non-ODBC standard formats for date strings or currency values. When set to no, the driver uses ODBC standard strings to represent currency, date, and time data that is converted to string data.

SAVEFILE: The name of an ODBC data source file into which the attributes of the current connection are saved if the connection is successful.

SERVER: The name of a server running SQL Server on the network. The value must be either the name of a server on the network, or the name of a SQL Server Client Network Utility advanced server entry. You can enter "(local)" as the server name on Windows NT to connect to a copy of SQL Server running on the same computer.

StatsLogFile: The full path and file name of a file used to record SQL Server Wire Protocol driver performance statistics.

StatsLog_On: StatsLog_On={yes | no}. Determines whether SQL Server Wire Protocol driver performance data is available. When set to yes, SQL Server Wire Protocol driver performance data is captured. When set to no, SQL Server Wire Protocol driver performance data is not available on the connection.

Trusted_Connection: Trusted_Connection={yes | no}. Determines what information the SQL Server Wire Protocol driver will use for login validation. When set to yes, the SQL Server Wire Protocol driver uses Windows NT Authentication Mode for login validation. The UID and PWD keywords are optional. When set to no, the SQL Server Wire Protocol driver uses a SQL Server username and password for login validation. The UID and PWD keywords must be specified.

UID: A valid SQL Server login account. UID need not be specified when using Windows NT authentication.

WSID: The workstation ID. Typically, this is the network name of the computer on which the application resides (optional). If specified, this value is stored in the master.dbo.sysprocesses column hostname and is returned by sp_who and the Transact-SQL HOST_NAME function.

The following example connects to a SQL Server database named MIPRO-SQL2014:

Server_connect("ODBC", "DRIVER={SQL Server Native Client 11.0};
DATABASE=QADB;Server=MIPRO-SQL2014;UID=myusername;PWD=mypassword;QuotedID=YES;Trusted_Connection=No;
Network=DBMSSOCN;Address=MIPRO-SQL2014,1433")

Connecting to Oracle Spatial

Oracle Spatial is an implementation of a spatial database from Oracle Corporation. It has some similarities to the previous Oracle SDO implementation, but is significantly different. Oracle Spatial maintains the Oracle SDO implementation via a relational schema. However, MapInfo Pro does not support the Oracle SDO relational schema via OCI. MapInfo Pro does support simultaneous connections to Oracle through OCI and to other databases through ODBC. MapInfo Pro does not support downloading Oracle Spatial geometry tables via ODBC using the current ODBC driver from Intersolv. There is no DSN component.

An example of a connection string to access an Oracle Spatial server named OraMIPRO using TCP/IP is:

"SRVR=OraMIPRO;UID=myusername;PWD=mypassword"

The following Oracle Spatial example connects to a server called OraMIPRO:

server_connect ("ORAINET", "SRVR=OraMIPRO;UID=myusername;PWD=mypassword")

Connecting to POSTgreSQL

An example of a connection string for POSTgreSQL is:

DSN=AccountingDepartment;UID=myusername;PWD=mypassword

The following parameters can be used for a POSTgreSQL driver:

The following Postgres with PostGIS example connects to a server called MiProPostGIS21:

Server_connect("ODBC", "DRIVER={PostGreSQL UNICODE(x64)};DATABASE=QADB;
Server=MiProPostGIS21;UID=myusername;PWD=mypassword;Port=5432")

See Also:

Server Disconnect statement