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.
Connecting to a Remote 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 Chapter 1, Installing Visual FoxPro, in the Installation Guide.
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.
To create a named connection
In the Project Manager, select Connections, and then choose New to open the Connection Designer.
-or-●
Open a database and use the CREATE CONNECTION command to open the Connection Designer.
-or-●
Use the CREATE CONNECTION command with a connection name.
●
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
In the Project Manager, select the name of the connection, and then choose Modify to open the Connection Designer.
●
In the Display ODBC login prompts area, choose an option.
-or-●
Use the DispLogin property of the DBSETPROP( ) or SQLSETPROP( ) functions.
●
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
In the Project Manager, select a database, and then select Connections.
-or-●
Use the DISPLAY CONNECTIONS command.
●
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
In the Project Manager, select a database, select Remote Views, and then choose New to open the View Designer.
-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.
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.