How to: Use SQL Pass-Through Functions

Your client/server application can access server data by using:

• Remote views

• SQL pass-through

Remote views provide the most common and easiest method for accessing and updating remote data. The upsizing wizards can automatically create remote views in your database as part of upsizing, or you can use Visual FoxPro to create remote views after upsizing. For more information on remote views, see How to: Create Remote Views.

SQL pass-through technology enables you to send SQL statements directly to a server. SQL pass-through statements, because they execute on the back-end server, are powerful ways to enhance the performance of your client/server applications. The following table compares remote views with SQL pass-through.

Comparison of Remote View and SQL Pass-Through Technologies

Remote View	SQL Pass-Through
Based on a SQL SELECT statement.	Based on any native server SQL statement, enabling data definition statements or execution of stored procedures on a remote server.
Can be used as data source for controls at design time.	Can't be used as a data source for controls.
Provides no ability to execute DDL commands on data source.	Provides method for using DDL commands on data source.
Fetches one result set.	Fetches one or multiple result sets.
Provides built-in connection management.	Requires explicit connection management.
Provides built-in default update information for updates, inserts, and deletes.	Provides no default update information.
Provides implicit SQL execution and data fetching.	Provides explicit SQL execution and result fetching control.
Provides no transaction handling.	Provides explicit transaction handling.
Stores properties persistently in database.	Provides temporary properties for SQL pass-through cursor, based on session properties.
Employs asynchronous progressive fetching while executing SQL.	Fully supports programmatic asynchronous fetching.

SQL pass-through technology offers the following advantages over remote views:

• You can use server-specific functionality, such as stored procedures and server-based intrinsic functions.

• You can use extensions to SQL supported by the server, as well as data-definition, server administration, and security commands.

• You have more control over SQL pass-through Update, Delete, and Insert statements.

• You have more control over remote transactions.

  Tip

  Visual FoxPro can handle SQL pass-through queries that return more than a single result set.

SQL pass-through queries also have disadvantages:

• By default, a SQL pass-through query always returns a non-updatable snapshot of remote data, which is stored in an active view cursor. You can make the cursor updatable by setting properties with the CURSORSETPROP( ) Function. An updatable remote view, in contrast, usually doesn't require that you set properties before you can update remote data, because property settings are stored in the database with the view definition.

• You must enter SQL commands directly into the Command window or into a program, rather than using the graphical View Designer.

• You create and manage the connection to the data source.

Whether you use remote views or SQL pass-through, you can query and update remote data. In many applications, you'll use both remote views and SQL pass-through.

Using SQL Pass-Through Functions

To use SQL pass-through to connect to a remote ODBC data source, you first call the Visual FoxPro SQLCONNECT( ) Function to create a connection. You then use the Visual FoxPro SQL pass-through functions to send commands to the remote data source for execution.

To use Visual FoxPro SQL pass-through functions

1. Confirm your system's ability to connect your computer to your data source. Use a utility such as ODBC Test for ODBC.

2. Establish a connection to your data source with the SQLCONNECT( ) Function or the SQLSTRINGCONNECT( ) Function.

   For example, if you're connecting Visual FoxPro to the SQL Server data source sqlremote, you might log on with the following command:

   nConnectionHandle = SQLCONNECT('sqlremote','<userid>','<password>')
   

   Note

   You can also use the SQLCONNECT() function to connect to a named connection.

3. Use Visual FoxPro SQL pass-through functions to retrieve data into Visual FoxPro cursors and process the retrieved data with standard Visual FoxPro commands and functions.

   For example, you might query the authors table and browse the resulting cursor using this command:

   ? SQLEXEC(nConnectionHandle,"select * from authors","mycursorname")
   BROWSE
   

4. Disconnect from the data source with the SQLDISCONNECT( ) function.

See Also

Tasks

How to: Access Stored Procedures on Remote Servers with SQL Pass-Through Functions

How to: Return Multiple Result Sets

Concepts

Using Result Sets

Processing Multiple Result Sets

Reference

Visual FoxPro SQL Pass-Through Functions

Other Resources

Using SQL Pass-Through Technology

Enhancing Applications Using SQL Pass-Through Technology

Planning Client/Server Applications

Upsizing Visual FoxPro Databases

Creating Views