Accessing Remote Data with Views

  • Article

When you want to use data located on a remote server, you create a remote view. To create a remote view, you must first be able to connect to a data source.

A remote data source is typically a remote server for which you've installed an ODBC driver and set up an ODBC data source name. To have a valid data source, you must ensure that ODBC is installed. From within Visual FoxPro, you can define a data source and connections.

For more information about setting up an ODBC data source, see Setting Up an ODBC Data Source.

Defining a Connection

In Visual FoxPro, you can create and store a named connection definition in a database, which you can then refer to by name when you create a remote view. You can also set properties of the named connection to optimize the communication between Visual FoxPro and the remote data source. When you activate a remote view, the view's connection becomes the pipeline to the remote data source.

Note   Before you can create a connection, you must have a database open.

To create a new connection

  1. In the Project Manager, select a database.

  2. Select Connections and choose New.

  3. In the Connection Designer, enter the options that correspond to your server requirements.

  4. From the File menu, choose Save.

  5. In the Save dialog box, enter a name for the connection in the Connection Name box.

  6. Choose OK. You can also create a connection by choosing New from the File menu and selecting the Connection option.

    -or-

For example, to create a connection in the testdata database that stores the information needed to connect to the ODBC data source sqlremote, you can enter the following code:

OPEN DATABASE testdata
CREATE CONNECTION remote_01 DATASOURCE sqlremote userid password

Visual FoxPro displays remote_01 as the name of the connection in the Project Manager.

Creating a named connection in your database does not use any network or remote resources, because Visual FoxPro doesn't activate the connection until you use the view. Until you activate the connection, the named connection merely exists as a connection definition stored as a row in the database's .dbc file. When you use a remote view, Visual FoxPro uses the named connection referenced in the view to create a live connection to the remote data source, and then sends the request for data to the remote source using the active connection as the pipeline.

You can optionally create a view that specifies only the name of the data source, rather than a connection name. When you use the view, Visual FoxPro uses the ODBC information about the data source to create and activate a connection to the data source. When you close the view, the connection is closed.

Naming Precedence for Connections and Data Sources

When you use the CREATE SQL VIEW command with the CONNECTION clause, you specify a name that represents either a connection or a data source. Visual FoxPro first searches the current database for a connection with the name you specified. If no connection with the specified name exists in the database, then Visual FoxPro looks for an established ODBC data source with the specified name. If your current database contains a named connection with the same name as an ODBC data source on your system, Visual FoxPro will find and use the named connection.

Displaying ODBC Login Prompts

When you use a view whose connection login information is not fully specified, Visual FoxPro might display a data source -specific box that prompts you for the missing information.

You can control whether Visual FoxPro prompts you for information that was left unspecified at connection time.

To control the display of ODBC login prompts

  1. In the Project Manager, select the name of the connection, and then choose Modify to open the Connection Designer.

  2. In the Display ODBC login prompts area, choose an option.

    -or-

Using an Existing Connection

You can use an existing named connection to create a remote view. You can see a list of the connections available in a database by using the Project Manager or the DISPLAY CONNECTIONS command.

To determine existing connections

For example, the following code displays the connections in the testdata database:

OPEN DATABASE testdata
DISPLAY CONNECTIONS

Creating a Remote View

Once you have a valid data source or named connection, you can create a remote view using the Project Manager or the language. A remote view is similar to a local view, but you add a connection or data source name when you define the view. The remote view's SQL statement uses the native server dialect.

To create a remote view

  1. In the Project Manager, select a database, then select Remote Views and choose New to open the View Designer.

  2. Choose New View.

  3. In the Select Connection or Data Source dialog box, select the Available data sources option.

    -or-

  • Use the CREATE SQL VIEW command with the REMOTE and/or the CONNECTION clause.

If you use the CONNECTION clause with the CREATE SQL VIEW command, you don't need to include the REMOTE keyword. Visual FoxPro identifies the view as a remote view by the presence of the CONNECTION keyword. For example, if you have the products table from the Testdata database on a remote server, the following code creates a remote view of the products table:

OPEN DATABASE testdata 
CREATE SQL VIEW product_remote_view ; 
   CONNECTION remote_01 ;
   AS SELECT * FROM products

You can use a data source name rather than a connection name when you create a remote view. You can also choose to omit a connection or data source name when you use the CREATE SQL VIEW command with the REMOTE clause. Visual FoxPro then displays the Selection Connection or Data Source dialog box, from which you can choose a valid connection or data source.

Creating a New Remote View

To gain access to your remote data in a view, you can use an existing connection or create a connection to use with your new view.

To create a new remote view

  1. Select a data source or connection and choose OK.

  2. If required, in the Microsoft SQL Server or other login dialog box, enter your login ID and password.

    -or-

    If you have previously defined and saved a connection, select the Connections option.

You can also choose New from the File menu and select the Remote View option.

Once the connection is established, the Open dialog box appears so you can select a table on the remote server.

After you select a table, the View Designer appears.

To continue creating your remote view, you can select output fields and set filters just as you would a local view.

Using a View Wizard

You can also create a remote view with a view wizard. To set up an updateable view using tables on an ODBC data source, use the Remote View Wizard.

To create a remote view with a wizard

  1. In the Project Manager, select a database.
  2. Select Remote Views, and choose New.
  3. Choose View Wizard.
  4. Follow the instructions on the wizard screens.

After you create a view, you can open the Database Designer and see that the view is in the schema displayed in the same manner as a table, with the view name and icon in place of a table name and icon.

If you join two or more tables in the Remote View Designer, the Designer uses inner joins (or equi-joins) and places the join condition in the WHERE clause. If you want to use an outer join, the Remote View Designer provides only left outer joins, the syntax supported by ODBC. If you need right or full outer joins or just want to use a native syntax for a left outer join, create the view programmatically.

See Also

Creating a Multitable View | Displaying Data with Views | Creating Queries | Project Manager | Connection Designer | DBSETPROP( ) | SQLSETPROP( ) | Remote View Wizard | Customizing Views