Using SQL Pass-Through Technology

  • Article

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 Creating 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 server stored procedures.
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 updateable 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 function SQLCONNECT( ) 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( ) or the SQLSTRINGCONNECT( ) function.

    For example, if you're connecting Visual FoxPro to the SQL Server data source sqlremote, you might log on as system administrator (user id sa) using the password secret with the following command:

    nConnectionHandle = SQLCONNECT('sqlremote','sa','secret')

    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.

Visual FoxPro SQL Pass-Through Functions

The following table lists the Visual FoxPro SQL functions that support working with remote data sources, grouped according to task.

Task Function Purpose
Connection management SQLCONNECT( ) Connects to a data source for SQL pass-through operations.
  SQLSTRINGCONNECT( ) Connects to a data source using ODBC connection string syntax.
  SQLDISCONNECT( ) Breaks a connection to an ODBC data source, making the specified connection handle obsolete.
SQL statement execution and control SQLCANCEL( ) Cancels an asynchronously executing SQL query on an active connection.
  SQLEXEC( ) Executes a SQL pass-through query on an active connection; returns the number of result sets generated, or 0 if SQLEXEC( ) is still executing (asynchronous processing).
  SQLMORERESULTS( ) Puts another result set into a cursor. Returns 0 if the statement creating the result set is still executing.
  SQLPREPARE( ) Pre-compiles the SQL statement on the data source and binds the Visual FoxPro parameters, i.e. saves the actual parameter expressions for all the parameters in the SQL statement.
  SQLCOMMIT( ) Requests a transaction commitment.
  SQLROLLBACK( ) Requests a transaction rollback.
Data source information SQLCOLUMNS( ) Stores a list of column names and information about each to a cursor. Returns 1 if the function succeeds, or 0 if the function is still executing.
  SQLTABLES( ) Stores the names of tables in the source into a cursor. Returns 1 if the function succeeds, or 0 if the function is still executing.
Miscellaneous control SQLGETPROP( ) Gets a connection property from an active connection.
  SQLSETPROP( ) Sets a property of an active connection.

The SQLEXEC( ), SQLMORERESULTS( ), SQLTABLES( ), and SQLCOLUMNS( ) statements can be canceled in synchronous mode by pressing ESC if SET ESCAPE is set to ON. You can cancel these statements at any time in asynchronous mode by issuing SQLCANCEL( ). All other SQL pass-through statements function synchronously and are not interruptible.

Creating Result Sets

When you use the SQL pass-through functions SQLEXEC( ) or SQLMORERESULTS( ) to query data, Visual FoxPro returns the data to you in one or many result sets. Result sets originate from cursors in the server data source and become cursors in Visual FoxPro. The default name for a result set is SQLRESULT.

See Also

Implementing a Client/Server Application | Accessing Server Stored Procedures with SQL Pass-Through Functions | Returning Multiple Result Sets | Processing of Multiple Result Sets | Designing Client/Server Applications | Upsizing Visual FoxPro Databases | Creating Views