How do you connect to remote data sources via ODBC?

Creating Connections

Before you can retrieve or update data on a database server or other ODBC source such as Access, you must establish a connection. This applies whether you are using SQL pass-through (SPT) or a remote view. When you [tt]USE[/tt] a remote view, a connection using the name connected you previously defined is automatically established. You must explicitly establish a connection before using one or more SQL pass-through statements.

A connection usually involves identifying a server, a database, a username and a password. Connections can be named and stored in a database container so that you can reconnect later by specifying the connection name, or they can be created on-the-fly through a command.

A named connection can be created by issuing the command [tt]CREATE CONNECTION[/tt] while you have a database container open. You will be presented with a dialog allowing you to pick the ODBC data source, username, and so forth. When you save it, you'll be asked to enter a name; you can use that name later to establish a connection, or it can be used as a connection by any remote views you create.

Once you have created a named connection, it can be used for both remote views and [tt]SQLCONNECT()[/tt].


Establishing Connections

You will only need to manually establish a connection if you are using SQL pass-through. Opening a remote view will automatically establish a connection, so there is no need to use these methods described below with remote views.

To establish a connection using an existing named connection, open the database container (you can issue the command [tt]OPEN DATABASE [/tt]MyDatabase to do that) and issue the command:

[tt]nCONNECT=SQLCONNECT("MYCONNECTION")[/tt]

where "myconnection" is the name of the connection you've created. Note that we've stored the connection handle returned to us by SqlConnect() in the memvar nConnect for use later.

Connections can also be created that do not need to be stored in a database container. These will need to contain all the information necessary to establish the connection each time.

One way to establish a connection dynamically is with [tt]SQLSTRINGCONNECT()[/tt]. For example, issue the command:

[tt]nCONNECT=SQLSTRINGCONNECT("driver={SYBASE ODBC DRIVER};UID=me;PWD=mypwd;DATABASE=maindata")[/tt]

An alternative to using the "driver=" method is to use the "dsn=" parameter, where the DSN (data source name) is one found in your Control Panel/32-bit ODBC User DSN section.

Another way to establish a connection without having a named connection in a DBC is by using [tt]SQLCONNECT()[/tt] and specifying a DSN defined in your ODBC.INI, such as:

[tt]nCONNECT=SQLCONNECT("MYDSN", "MYUSER", "MYPASSWORD")[/tt]

The user name and password are optional if you've already defined them in the ODBC DSN setup.