Chapter 21 -- Implementing a ClientServer Application

Whether you ve created and upsized a working local prototype or developed your application against remote data using remote views, you ve gained access to the large data stores typically available in a server database. You can also take advantage of the security and transaction processing capabilities of the remote server. While remote views handle the main data management tasks, you can enhance your application by using SQL pass-through (SPT) technology to create objects on the server, run server stored procedures, and execute commands using native server syntax.

This chapter discusses techniques for implementing client/server technology in a working application that uses remote views. If you want to know more about designing and creating a client/server application, see Chapter 19, Designing Client/Server Applications, and Chapter 20, Upsizing Visual FoxPro Databases. For more information on creating remote views, see Chapter 8, Creating Views.

This chapter discusses:

Using SQL Pass-Through Technology
Working with Remote Data Using SQL Pass-Through
Handling SQL Pass-Through Errors

Using SQL Pass-Through Technology

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 Chapter 8, 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. For more information, see Processing Multiple Result Sets later in this chapter.

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 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

Confirm your system s ability to connect your computer to your data source. Use a utility such as ODBC Test for ODBC.
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.
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 
```
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.

Accessing Server Stored Procedures with SQL
Pass-Through Functions

You can use Visual FoxPro SQL pass-through technology to create and execute stored procedures on a remote server. Stored procedures can greatly enhance the power, efficiency, and flexibility of SQL, and dramatically improve the performance of SQL statements and batches. Many servers provide stored procedures for defining and manipulating server database objects and for performing server system and user administration.

Note The examples in this chapter use Microsoft SQL Server syntax unless otherwise noted.

To call a server stored procedure

Use the SQLEXEC( ) function with the stored procedure name.

For example, the following code displays the results of calling a stored procedure named sp_who on SQL Server using an active connection to the data source sqlremote:

nConnectionHandle = SQLCONNECT('sqlremote') ? SQLEXEC(nConnectionHandle, 'use pubs') ? SQLEXEC(nConnectionHandle, 'sp_who') BROWSE

For more information on creating and executing server stored procedures, see your server documentation.

Returning Multiple Result Sets

If you execute a stored procedure that contains native server syntax SELECT statements, each result set is returned to a separate Visual FoxPro cursor. You can use these cursors to return values or parameters from a server stored procedure to the Visual FoxPro client.

To return multiple result sets

Use the SQLEXEC( ) function to select multiple results sets using your native server syntax.

For example, the following code creates and executes a SQL server stored procedure, my_procedure, that returns three Visual FoxPro cursors: sqlresult, sqlresult1, and sqlresult2:

=SQLEXEC(nConnectionHandle,'create procedure my_procedure as ;       select * from sales; select * from authors;        select * from titles') =SQLEXEC(nConnectionHandle,'execute my_procedure')

How the Server Processes Result Sets and Errors

Because the server compiles each stored procedure when you create it, you receive any server syntax errors at create time. When you execute the stored procedure, the server executes the compiled SQL statements sequentially (as in a Visual FoxPro program) and Visual FoxPro fetches each result set from each SQL statement within the stored procedure separately, in the order executed.

Result sets and errors are returned in the order received, and processing stops if an error is encountered. For example, if a run-time error occurs when the server executes the third statement in a four-statement stored procedure, you receive the first two result sets and then receive the error that occurred upon processing the third result set. Processing stops after the error is returned; the fourth result set is not retrieved. You can use the AERROR( ) function to obtain information about the most recent error.

Note You can execute server stored procedures from Visual FoxPro only by using Visual FoxPro SQL pass-through functions. Views don't support server stored procedures, because each view contains an explicit SQL SELECT statement in its SQL definition.

Passing a SQL Statement to the Data Source

The SQLEXEC( ) function enables you to send a SQL statement to the data source without interpretation. In the simplest case, any string you enclose in the second parameter of the SQLEXEC( ) function is passed to your data source without interpretation. This allows you to execute any statement using your data source s native SQL.

You can also use the SQLEXEC( ) function to create a parameterized query, or to pass ODBC extensions to SQL to the data source.

Creating a Parameterized Query

Just as you can create parameterized views using the View Designer or the language, you can create a parameterized SQL pass-through query.

To create a parameterized query with SQL pass-through

Precede a Visual FoxPro parameter with a question mark (?) symbol, and then include the parameter in a SQL string you send with SQLEXEC( ).
The parameter you supply is evaluated as a Visual FoxPro expression and the value is sent as part of the view s SQL statement. If the evaluation fails, Visual FoxPro prompts for the parameter value.
Tip If your parameter is an expression, enclose the parameter expression in parentheses. This ensures the entire expression is evaluated as part of the parameter.
For example, if you have the customer table from the Testdata database on a remote server, the following code creates a parameterized query that limits the view to those customers whose country matches the value supplied for the ?cCountry parameter:

? SQLEXEC(1,'SELECT * FROM customer WHERE customer.country = ?cCountry')

If you want to prompt the user for a parameter value, enclose the parameter expression in quotation marks. For more information on prompting for a parameter value, see Chapter 8, Creating Views.

Your ODBC data source doesn't accept parameters in the following locations:

In a SELECT fields or tables list.
As both expressions in a comparison predicate.
As both operands of a binary operator.

An ODBC data source will not accept parameters in the following locations in the WHERE or HAVING clause of a SELECT statement:

As both the first and second operands of a BETWEEN predicate.
As both the first and third operands of a BETWEEN predicate.
As both the expression and the first value of an IN predicate.
As the operand of a unary + or - operator.
As the argument of a SET function.

Using SQL Server Input/Output Parameters

You can use input/output parameters to pass values between Visual FoxPro and SQL Server. Input/output parameters are available only using SQL pass-through; they can t be used in views.

The following table provides an example using input/output parameters to pass values from Visual FoxPro to a SQL Server stored procedure, returning the result to a Visual FoxPro variable.

Using Input/Output Parameters with a SQL Server Stored Procedure

Code	Comments
`resultCode = SQLExec(connHand, "CREATE PROCEDURE sp_test; @mult1 int, @mult2 int, @result int; OUTPUT AS SELECT @result = @mult1 * @mult2")`	Create a stored procedure, sp_test, that multiplies two variables (`mult1` and `mult2`), then stores the resulting amount in the variable `result`.
`outParam = 0`	Create a Visual FoxPro variable to receive the output parameter value when it's passed from SQL Server to Visual FoxPro.
`resultCode = SQLExec(connHand, ; "{CALL sp_test (2, 4, ?@outParam)}")`	Execute the SQL Server stored procedure, passing the values 2 and 4 to be multiplied together in the stored procedure.
`? "outParam =", outParam && the value is 8`	Display the value of the output parameter.

Defining Parameters

The syntax for output parameters is:

?@parameter_name

When you implement input/output parameters, define the Visual FoxPro variables you want to include in your SQL pass-through command before you use the variables in the SQL statement. To successfully send and receive information with input/output parameters, you must define:

A stored procedure parameter, with an output type, that returns a value.
For example, if your stored procedure parameter is @result, you must assign an output type, such as int, to @result, and you must assign a value to @result.
An output parameter (@parameter_name) expression that evaluates to an existing Visual FoxPro variable.
For example, if your output parameter expression is ?@outParam, your application must have defined the Visual FoxPro variable outParam.
Note If you don't use an output parameter, either in Visual FoxPro or in the stored procedure, or you don't define a Visual FoxPro variable to receive the return value, the Visual FoxPro parameter value will not change.

Converting Data Types

Visual FoxPro converts returned variable values using the following rules:

Floating point data type (N, F, B) variables are converted to N.
The display size is set to 20.
The decimal setting is set to the current session setting. The decimal setting affects only the default display format, and doesn't affect the decimal precision.
Date and time variables (D, T) are converted to time (T) variables.

You can t use Memo, General, Picture, or NULL data types in input/output parameters.

If your application uses cursor fields as parameters, Visual FoxPro will attempt to convert the result back to the original field data type.

Returning Parameter Values

Input/output parameters are available only after the last result set of a statement has been fetched. This means that input/output values are returned to Visual FoxPro only after:

SQLEXEC( ) returns (1) in batch mode
-or-
SQLMORERESULTS( ) returns (2) in non-batch mode.

If your SQLEXEC( ) statement requests multiple result sets, the output parameters are only guaranteed to be available after the last result set has been fetched from the data source.

Creating Outer Joins with Remote Data

You can use SQL pass-through to perform outer joins on remote data using native server syntax, if your server supports outer joins. An outer join combines information from one or more tables regardless of whether matching rows are found.

To perform an outer join on a server

Use the SQLEXEC( ) function with the server s outer join syntax.

For example, the following code uses the Visual FoxPro SQL pass-through function SQLEXEC( ) to display the results of an outer join on SQL Server using the active named connection sqlremote:

? SQLEXEC(sqlremote, 'select au_fname, au_lname, pub_name ;                from authors, publishers ;                where authors.city *= publishers.city') BROWSE

For more information on outer join syntax and types of outer joins, see your server documentation. For information on creating a named connection, see Defining a Connection in Chapter 8, Creating Views.

Using ODBC Extensions to SQL

You can use SQLEXEC( ) to execute ODBC extensions to SQL by enclosing the SQL statement with SQL Access Group standard or extended escape syntax. For more information about ODBC extensions to SQL, refer to the SQL Grammar appendix in your ODBC documentation.

Creating Outer Joins Using the ODBC Escape Clause

You can use SQL pass-through to perform outer joins on remote data using the ODBC escape syntax, if your server supports outer joins. An outer join combines information from one or more tables regardless of whether matching rows are found.

The syntax for outer joins using the ODBC escape clause is:

{oj outer-join expression}

The following example creates a result set of the names and departments of employees working on project 544:

SELECT employee.name, dept.deptname;    FROM {oj employee LEFT OUTER JOIN dept;             ON employee.deptid = dept.deptid};    WHERE employee.projid = 544

Managing Connections with SQL Pass-Through

When you create a remote view, you choose an ODBC data source name or a connection name that is then used as a pipeline to the remote server upon activation of the view. To access remote data directly with SQL pass-through, you must have the handle for an active connection. A handle is a value that refers to an object; in this case, the handle refers to a data source connection. To obtain a handle, you request a connection to the data source using the SQLCONNECT( ) or SQLSTRINGCONNECT( ) function. If the connection is successful, your application receives a connection handle for use in subsequent Visual FoxPro calls.

Your application can request multiple connections for one data source. You can also work with multiple ODBC data sources by requesting a connection to each data source you want to access. If you want to reduce the number of connections used, you can configure remote views to share the same connection. You disconnect from a data source with the SQLDISCONNECT( ) function.

Tip Visual FoxPro relies on the definition of the ODBC data source that's stored in your Windows Odbc.ini file or Windows NT registry to connect to a data source. If you change the name or the login information for a data source, keep in mind that these changes might affect whether an application using that data source can connect to the desired remote server.

Controlling Environment and Connection Properties

The client/server environment is established each time you open Visual FoxPro. The environment exists for that session of Visual FoxPro and disappears when you close Visual FoxPro. The client/server environment contains:

Global properties that act as the prototypes for new connections.
Error values for errors that occur outside a specific connection.

You can use a handle of 0, the environment handle, to refer to global property settings. You use the SQLSETPROP( ) function to control default property settings in the connection environment and properties within individual connections. The methods you use for entering SQLSETPROP( ) values are consistent for both the environment and individual connections:

Properties specified with one of two values can use a logical value (.F. or .T.) for eExpression.
A property name can be abbreviated to its shortest unambiguous truncation. For example, you can use Asynchronous , Asynch , or A to specify the Asynchronous property. Property names aren't case-sensitive.

When you initiate a connection, the connection inherits default connection property values. You can use SQLSETPROP( ) to change these values.

Setting Connection Properties

To view the current property settings for a connection, use SQLGETPROP( ) with the respective connection handle. The following table lists the connection properties you can access with SQLGETPROP( ).

Visual FoxPro Connection Properties

To	Use this property	Purpose
Display the information used to create the active connection	ConnectString	The login connection string.
	DataSource	The name of the data source as defined by ODBC.
	Password	The connection password.
	UserID	The user identification.
Work with shared connections	ConnectBusy	True (.T.) if a shared connection is busy; false (.F.) otherwise.
Control interface display	DispLogin	Controls when the ODBC Login dialog box is displayed.
	DispWarnings	Controls whether non-fatal warning messages are displayed or not.
Control time intervals	ConnectTimeout	Specifies the time (in seconds) to wait before returning a connection time-out error.
	IdleTimeout	Specifies the idle time-out interval (in seconds). Qualifying active connections are deactivated after the specified time interval.¹
	WaitTime	Controls the amount of time in milliseconds that elapses before Visual FoxPro checks whether the SQL statement has completed executing.
	QueryTimeout	Controls the time (in seconds) to wait before returning a general time-out error.
Manage transactions	Transactions	Determines how the connection manages transactions on the remote table.
Control fetching of result sets into view cursors	Asynchronous	Specifies if result sets are returned synchronously (the default) or asynchronously.
	BatchMode	Specifies if SQLEXEC( ) returns result sets all at once (the default), or individually with SQLMORERESULTS( ).
	PacketSize	Specifies the size of the network packet used by the connection.
Display internal ODBC handles	ODBChdbc²	The internal ODBC connection handle that can be used by external library files (.fll files) to call the ODBC API functions.
	ODBChstmt²	The internal ODBC statement handle that can be used by external library files (.fll files) to call the ODBC API functions.

1. If in manual transaction mode, the connection is not deactivated.

2. If a connection is deactivated, the ODBChdbc and ODBChstmt values are no longer valid. Do not free or drop these values in a user library.

For more information on connection properties and their default settings, see SQLSETPROP( ).

Controlling Environment Property Settings

The values you set in the Visual FoxPro environment using handle 0 are used as prototypes or default values for each subsequent connection or attachment.

To view the current environment property settings

Use SQLGETPROP( ) with 0 as the value for the handle.

The following example displays the current environment s WaitTime property setting:

? SQLGETPROP(0, "WaitTime")

If you set the DispWarnings property to true (.T.), Visual FoxPro displays any environment errors from that point on, and also sets DispWarnings to true (.T.) for newly created connections.

Although the values you set for handle 0 are used as prototype values for each connection, you can also set custom properties for an individual connection by issuing SQLSETPROP( ) for that connection handle. The exceptions are the ConnectTimeout, PacketSize, and DispLogin properties, whose settings the connection inherits at connect time. If you change the setting of the ConnectTimeout, PacketSize, or DispLogin property, the new setting isn't used until you reconnect.

Controlling Connection and View Objects

You can control connections and views by setting properties on the connection or view object. Properties that control databases, tables, table fields, view definitions, view fields, named connections, active connections, or active view cursors are called engine properties. You can display or set engine properties with one of the following Visual FoxPro functions:

To display engine properties use	To set engine properties use
CURSORGETPROP( )	CURSORSETPROP( )
DBGETPROP( )	DBSETPROP( )
SQLGETPROP( )	SQLSETPROP( )

The function you use depends on whether you want to set properties on object 0 (connection 0 and cursor 0), the object definition in a database (named connection or view definition), or the active object (active connection or active view cursor). The following table lists objects and the functions you use to set properties on each object:

To set properties for	Connection	View
Object 0	SQLSETPROP( )	CURSORSETPROP( )
Object definition in a database	DBSETPROP( )	DBSETPROP( )
Active object	SQLSETPROP( )	CURSORSETPROP( )

Engine Properties

The following table lists engine properties alphabetically along with the objects that use each property.

Engine property	Applies to
Asynchronous	Connection definitions: see DBSETPROP( ). Active connections: see SQLSETPROP( ).
Batchmode	Connection definitions: see DBSETPROP( ). Active connections: see SQLSETPROP( ).
BatchUpdateCount¹	View definitions: see DBSETPROP( ). Active view cursors: see CURSORSETPROP( ).
Buffering	Active view cursors: see CURSORSETPROP( ).
Caption	Fields in tables, fields in view definitions: see DBSETPROP( ).
Comment	Databases, tables, fields in tables, view definitions, fields in view definitions, connection definitions: see DBSETPROP( ).
CompareMemo	View definitions: see DBSETPROP( ). Active view cursors: see CURSORSETPROP( ).
ConnectBusy	Active connections: see SQLGETPROP( ).
ConnectHandle	Active view cursors: see CURSORGETPROP( ).
ConnectName¹	View definitions: see DBSETPROP( ). Active connections: see SQLGETPROP( ). Active view cursors: see CURSORGETPROP( ).
ConnectString	Connection definitions: see DBSETPROP( ). Active connections: see SQLGETPROP( ).
ConnectTimeout	Connection definitions: see DBSETPROP( ). Active connections: see SQLSETPROP( ).
Database	Active view cursors: see CURSORGETPROP( ).
DataSource	Connection definitions: see DBSETPROP( ). Active connections: see SQLGETPROP( ).
DataType	Fields in view definitions: see DBSETPROP( ).
DefaultValue	Fields in tables, fields in view definitions: see DBSETPROP( ).
DeleteTrigger	Tables: see DBGETPROP( ).
DispLogin	Connection definitions: see DBSETPROP( ). Active connections: see SQLSETPROP( ).
DispWarnings	Connection definitions: see DBSETPROP( ). Active connections: see SQLSETPROP( ).
FetchAsNeeded	View definitions: see DBSETPROP( ). Active view cursors: see CURSORGETPROP( ).
FetchMemo¹	View definitions: see DBSETPROP( ). Active view cursors: see CURSORGETPROP( ).
FetchSize¹	View definitions: see DBSETPROP( ). Active view cursors: see CURSORSETPROP( ).
IdleTimeout	Connection definitions: see DBSETPROP( ). Active connections: see SQLSETPROP( ).
InsertTrigger	Tables: see DBGETPROP( ).
KeyField	Fields in view definitions: see DBSETPROP( ).
KeyFieldList²	Active view cursors: see CURSORSETPROP( ).
MaxRecords¹	View definitions: see DBSETPROP( ). Active view cursors: see CURSORSETPROP( ).
ODBCHdbc	Active connections: see SQLGETPROP( ).
ODBCHstmt	Active connections: see SQLGETPROP( ).
Offline	View definitions: see DBGETPROP( ).
PacketSize	Connection definitions: see DBSETPROP( ). Active connections: see SQLSETPROP( ).
ParameterList	View definitions: see DBSETPROP( ). Active view cursors: see CURSORSETPROP( ).
Password	Connection definitions: see DBSETPROP( ). Active connections: see SQLGETPROP( ).
Path	Tables: see DBGETPROP( ).
Prepared	View definitions: see DBSETPROP( ).
PrimaryKey	Tables: see DBGETPROP( ).
QueryTimeOut	Connection definitions: see DBSETPROP( ). Active connections: see SQLSETPROP( ).
RuleExpression	Tables, fields in tables, view definitions, fields in view definitions: see DBSETPROP( ).
RuleText	Tables, fields in tables, view definitions, fields in view definitions: see DBSETPROP( ).
SendUpdates²	View definitions: see DBSETPROP( ). Active view cursors: see CURSORSETPROP( ).
ShareConnection	View definitions: see DBSETPROP( ). Active view cursors: see CURSORGETPROP( ).
SourceName	Active view cursors: see CURSORGETPROP( ).
SourceType	View definitions: see DBGETPROP( ). Active view cursors: see CURSORGETPROP( ).
SQL	View definitions: see DBGETPROP( ). Active view cursors: see CURSORGETPROP( ).
Tables²	View definitions: see DBSETPROP( ). Active view cursors: see CURSORSETPROP( ).
Transactions	Connection definitions: see DBSETPROP( ). Active connections: see SQLSETPROP( ).
Updatable	Fields in view definitions: see DBSETPROP( ).
UpdatableFieldList²	Active view cursors: see CURSORSETPROP( ).
UpdateName	Fields in view definitions: see DBSETPROP( ).
UpdateNameList²	Active view cursors: see CURSORSETPROP( ).
UpdateTrigger	Tables: see DBGETPROP( ).
UpdateType	View definitions: see DBSETPROP( ). Active view cursors: see CURSORSETPROP( ).
UseMemoSize¹	View definitions: see DBSETPROP( ). Active view cursors: see CURSORGETPROP( ).
UserID	Connection definitions: see DBSETPROP( ). Active connections: see SQLGETPROP( ).
Version	Databases: see DBGETPROP( ).
WaitTime	Connection definitions: see DBSETPROP( ). Active connections: see SQLSETPROP( ).
WhereType	View definitions: see DBSETPROP( ). Active view cursors: see CURSORSETPROP( ).

1. Property primarily useful for remote views; setting has no effect on performance of local views. You can set this property on local views if you want to pre-set the property on the local view and then upsize later to create a remote view.

2. Property must be set for updates to be sent to remote data source.

Using Transactions with Remote Data

You can wrap transactions around updates, deletes, and inserts to remote data using one of two methods:

Automatic transaction mode
Manual transaction mode

The transaction mode you select determines how Visual FoxPro handles transactions on your local machine.

Using Automatic Transaction Mode

By default, Visual FoxPro automatically wraps every transactable command sent to the remote server in a transaction. This default automatic transaction handling is provided when the Transactions property is set to 1, or DB_TRANSAUTO.

To use automatic transaction mode

Use the DBSETPROP( ) function to set the Transactions property on the connection to 1 or DB_TRANSAUTO.
-or-
Use the SQLSETPROP( ) function to set the Transactions property on the active connection to 1 or DB_TRANSAUTO.

Transaction processing for the remote table is automatically handled.

Note The Visual FoxPro commands BEGIN TRANSACTION and END TRANSACTION create a transaction for the local Visual FoxPro cursor only. They don't extend the transaction to the remote server.

Controlling Transactions Manually

If you want to control transactions manually, you can set the Transactions property to 2, or DB_TRANSMANUAL. With manual transaction handling, Visual FoxPro automatically begins a transaction for you when you issue the first transactable SQL statement, but you must submit the Visual FoxPro SQLCOMMIT( ) or SQLROLLBACK( ) functions to end the transaction.

To use manual transaction mode

Use the DBSETPROP( ) command to set the Transactions property on the connection to 2 or DB_TRANSMANUAL.
-or-
Use the SQLSETPROP( ) command to set the Transactions property on the active connection to 2 or DB_TRANSMANUAL.

Transaction processing is handled manually through SQLCOMMIT( ) and SQLROLLBACK( ).

After committing or rolling back the prior transaction, Visual FoxPro automatically begins a new transaction when you issue the next transactable SQL statement. For more information about transactions, see Chapter 17, Programming for Shared Access.

Nested Transactions

Visual FoxPro supports transactions nested up to five levels for local data. A single level of transaction support is built into SQL pass-through.

If your server supports multiple levels of transactions, you can use SQL pass-through to manage transaction levels explicitly. Explicit transaction management is complex, however, because it can be difficult to control the interaction between the built-in transaction and the timing of remote server transactions. For more information on explicit transaction management, see your ODBC documentation.

Working with Remote Data Using SQL
Pass-Through

After you retrieve a result set using SQL pass-through, you can view and control the properties of your result set cursor using the Visual FoxPro functions CURSORGETPROP( ) and CURSORSETPROP( ). These are the same functions you use to set properties on an active view cursor.

Note Cursors aren't objects and aren't tied to the object model. However, you can view their properties, or attributes, with CURSORGETPROP( ) and set their properties with CURSORSETPROP( ).

Setting Cursor Properties for Remote Data

The following table lists the Visual FoxPro cursor properties that support working with views and connected result sets, grouped according to task categories.

Visual FoxPro Cursor Properties

Task	Property	Purpose
View cursor definition	SQL	Contains the SQL statement from which the cursor was created.
Control interactions between Visual FoxPro and ODBC	ConnectHandle	Handle to remote connection that's used by cursor.
	ConnectName	Name of connection used by the cursor.
	Prepare	Specifies whether the query for the view is prepared before it's executed.
	FetchAsNeeded	Specifies whether rows are fetched automatically during the idle loop or only on an as-needed basis.
	CompareMemo	Specifies whether Memo and General fields participate in the WHERE clause of an UPDATE statement, regardless of the setting of the UpdateType property
	FetchMemo	Specifies whether Memo and General fields are fetched automatically with result sets, or fetched later, on demand, as the Memo or General field is opened.
	UseMemoSize	Specifies the minimum column size (1 to 255) in result sets for which columns are returned in Memo fields.
	FetchSize	Specifies the number of rows that are fetched at one time from the remote result set.
	MaxRecords	Specifies the maximum number of rows fetched when result sets are returned.
Update data	SendUpdates^*	Specifies whether updates to the cursor are sent to the tables on which the cursor is based.
	BatchUpdateCount	Specifies the number of update statements sent to the back end for buffered tables.
	Tables^*	Comma-delimited list of table names on the data source; used to define scope for UpdateNameList and UpdatableFieldsList properties.
	KeyFieldList^*	Comma-delimited list of Visual FoxPro fields that represent the primary key of the result set used for updates.
	UpdateNameList^*	Comma-delimited list pairing Visual FoxPro fields in the cursor with the table and column names of fields to which you want to send updates.
	UpdatableFieldList^*	Comma-delimited list of the Visual FoxPro fields for which updates are sent.
	Buffering	Specifies the type of buffering being performed on the cursor.
	UpdateType	Specifies whether updates should occur using UPDATE, or DELETE and then INSERT commands.
	WhereType	Specifies what should be included in the WHERE clause for updates to table data.

* Properties that must be set before you can update data.

You use these properties to control the way your application interacts with remote data, such as establishing the number of rows retrieved during progressive fetching, and controlling buffering and updates to remote data.

Using the Remote Data Tab in the Options Dialog Box

Some cursor properties inherit their initial values from the environment; other properties only become available at the cursor level. Some properties are available to cursors representing remote views and ODBC or SQL pass-through connected tables.

You can control some cursor and connection property settings through the Remote Data tab of the Options dialog box. When you display the Remote Data tab, the values in the dialog box represent the cursor settings for the current session and the Visual FoxPro global default settings for the connection. When you change values in the Remote Data tab and choose OK, the new values are saved to the cursor s current session and the connection s global default settings. If you choose Set As Default, the values are written to the configurable system settings on your machine. The following diagram illustrates these interactions.

View and set global and session settings with the Options dialog box

Setting Properties with SQL Pass-Through

When you create a cursor, the cursor inherits property settings, such as UpdateType and UseMemoSize, from the environment cursor, or cursor 0 of the current session. You can change these default property settings by using the CURSORSETPROP( ) function with 0 as the cursor number.

After you create a view cursor with SQL pass-through, you can change the active cursor s property settings by using the CURSORSETPROP( ) function for the view cursor. Changes you make with CURSORSETPROP( ) are temporary: the temporary settings for the active view disappear when you close the view, and the temporary settings for cursor 0 go away when you close the Visual FoxPro session.

Connections inherit properties in a similar fashion. Default properties for connection 0 are inherited when you create and store a named connection in a database. You can change these default property settings for connection 0 with the SQLSETPROP( ) function. After the connection has been created and is stored in a database, you can change connection properties with the DBSETPROP( ) function. When you use a connection, the property settings stored for the connection in the database are inherited by the active connection. You can change these properties on the active connection using the SQLSETPROP( ) function for the connection handle.

Both SQL pass-through view cursors and named connections can use a named ODBC data source. If you use an ODBC data source in a SQL pass-through view cursor, the connection inherits properties from the session defaults.

The following diagram illustrates property inheritance for cursors and connections created with SQL pass-through. The gray lines represent the flow of property inheritance; the black lines represent Visual FoxPro commands.

SQL pass-through (SPT) connection and cursor property inheritance

Updating Remote Data with SQL Pass-Through

When you use SQL pass-through functions to update data on a remote server, you control whether data is updated, as well as specific details about the updates, by setting properties on the result set cursor. Visual FoxPro checks these properties when an update is requested before committing the update.

To update remote data you must set five properties: Tables, KeyFieldList, UpdateNameList, UpdatableFieldList, and SendUpdates. You can specify additional properties such as Buffering, UpdateType, and WhereType to best fit the requirements of your application.

To enable updates on an active view cursor

Use the CURSORSETPROP( ) function to specify the view cursor s update properties: Tables, KeyFieldList, UpdateNameList, UpdatableFieldList, and SendUpdates.
Tip SQL pass-through view cursors aren't updatable until you specify update properties for the view cursor. If you want to store update property settings persistently, create a view definition. Visual FoxPro supplies default values that prepare the view to be updatable when you create a view using the View Designer or the language. You can use the CURSORSETPROP( ) function to add additional information to supplement or customize the default values.

The update properties you set on the active view cursor have slightly different names than their DBSETPROP( ) counterparts. The following table lists the names used for both view definitions and active cursors.

View and Cursor Update Properties

Purpose	View definition properties¹	Active cursor properties²
Make remote table updatable.	Tables	Tables
Specify the remote names for view fields.	UpdateName (field-level property)	UpdateNameList
Specify view fields you want to use as keys.	KeyField (field-level property)	KeyFieldList
Specify the view fields that are updatable.	Updatable (field-level property)	UpdatableFieldList
Turn updates on.	SendUpdates	SendUpdates

1 Set with DBSETPROP( ).
2 Set with CURSORSETPROP( ).

For more information on setting update properties, see Chapter 8, Creating Views, or see DBSETPROP( ) or CURSORSETPROP( ).

Controlling the Timing of Remote Updates

You control how updates to remote data are buffered by setting the cursor s Buffering property. Of the five possible buffering property settings, two are valid for remote views:

3, or DB_BUFOPTROW, the default, which optimistically locks the row.
5, or DB_BUFOPTTABLE, which optimistically locks the table.

Visual FoxPro supports only optimistic locking on remote cursors.

Note The pessimistic row and table buffering settings, 2 and 4, don t apply to remote views, because Visual FoxPro doesn t take locks on the server data. Buffering property setting 1 doesn t apply to remote views because views are always buffered.

Using Optimistic Row Buffering

The default Buffering setting, DB_BUFOPTROW, optimistically locks remote data on a row-by-row basis. For example, if you want changes to the titles table to be committed on a row-by-row basis, such as when using the SKIP command, you could set the Buffering property to 3:

CURSORSETPROP('buffering', 3, 'titles')

When Buffering is set to row buffering, you have two methods of sending updates to the remote server. You can:

Call the TABLEUPDATE( ) function.
Use a command that moves the record pointer off the row, such as SKIP or GO BOTTOM.

The TABLEUPDATE( ) function updates the server without moving the record pointer. Commands that move the record pointer send updates to the remote server as a by-product of moving off the updated record.

If you use row buffering and want to be able to revert changes to rows, you must wrap the changes in manual transactions using SQL pass-through transaction functions.

Using Optimistic Table Buffering

If you want changes to a table to be committed a batch at a time, such as when the user clicks a Save or OK button in a form, you can set the Buffering property to 5, or DB_BUFOPTTABLE. You must call the TABLEUPDATE( ) function to send the update to the server.

In the following example, you set the Buffering property in your form s initialization code and then commit the changes in the save code.

Code	Comments
`CURSORSETPROP('buffering', 5, 'sqltitles')`	Set in Init code
`* Update batched changes; * ignore changes made by others`
`TABLEUPDATE(.T., .T., 'titles')`	Set in Save code

To restore the original values to a table and prevent updates from being sent to the remote server, you call TABLEREVERT( ). You can control whether a single row or all rows are reverted by combining the setting of the cursor s Buffering property with the TABLEREVERT( ) command. The following example reverts only the current row. You might want to invoke this code when the user clicks on a Cancel button in a form:

= TABLEREVERT(.F., 'titles')      && Revert current row

If you wanted to revert all rows, such as when the user presses ESC to leave a form, you could use the same example, this time changing the settings of the Buffering property and TABLEREVERT( ) command to revert all rows, with the entire table buffered:

= TABLEREVERT(.T., 'titles')      && Revert all rows

For more information about buffering, see Chapter 17, Programming for Shared Access.

Detecting Changes By Other Users

In multi-user applications, conflicts with other users updates are detected by the SQL Update query, which is generated when a write is attempted locally. The level of detection depends on the setting of the WhereType property. For more information on setting the WhereType property, see Chapter 8, Creating Views.

Forcing Updates

You can use the TABLEUPDATE( ) function to control whether changes made to a table or cursor by another user on a network are overwritten when you send your updates. If you set the Force parameter of TABLEUPDATE( ) to true (.T.), and the CURSORSETPROP( ) UpdateType property is set to the default value, 1, old data is updated with the new data you send, as long as the value in the record s key field on the remote table hasn't been changed. If the value in the remote table s key field has changed, or if the UpdateType property is set to 2, Visual FoxPro sends a DELETE and then an INSERT statement to the remote table.

Troubleshooting Update Error Messages

The following table lists the Visual FoxPro and ODBC error messages that apply specifically to remote updates. The Action column contains the action you take to resolve the error condition.

Error Message	Meaning	Action
No update table(s) specified. Use the Tables cursor property.	The cursor property Tables contains no remote table names. At least one table is required to enable updates to the remote server.	Use the Tables property to specify at least one table for the cursor.
No key column(s) specified for the update table table_name. Use the KeyFieldList cursor property.	The primary key for the remote table specified in the error message isn't included in the KeyFieldList property for the cursor; a primary key is required for each table being updated.	Use the KeyFieldList property to specify the primary key for the remote table.
No valid update table specified for column column_name. Use the UpdateNameList and Tables cursor properties.	The UpdateName property for column column_name has an invalid table qualifier.	Set the table qualifier with the UpdateNameList property, or add the table qualifier to the Tables property setting, or both.
The KeyField List cursor property doesn t define a unique key.	More than one remote record has the same key.	Use the KeyField List property to define a unique key for the remote table.
From ODBC: ODBC invalid object.	ODBC cannot find the remote table or column because it doesn t exist as named. Visual FoxPro field names are validated by Visual FoxPro; remote table and column names are validated only by the remote server.	Check the object name.

For more information on error handling, see Handling SQL Pass-Through Errors later in this chapter.

Choosing an Efficient SQL Pass-Through Processing Mode

Visual FoxPro provides two processing modes for retrieving and updating remote data using SQL pass-through: synchronous and asynchronous. When you use SQL pass-through functions you can choose the method you prefer. You don t need to choose a method for remote views; Visual FoxPro automatically employs progressive fetching and manages the processing mode for you for remote views.

Benefits of Synchronous Mode

By default, Visual FoxPro SQL functions are processed synchronously: Visual FoxPro doesn t return control to an application until a function call is completed. Synchronous processing is useful when you're working with Visual FoxPro interactively.

Benefits of Asynchronous Mode

Asynchronous processing provides greater flexibility than synchronous processing. For example, when your application is processing a function asynchronously, your application can build a progress indicator to display the progress of the executing statement, display movement of the mouse pointer, create loops, and set timers to allow interruption of processing that is taking too long.

Using SQL Pass-Through Asynchronously

Your application can request asynchronous processing for the four functions that submit requests to a data source and retrieve data: SQLEXEC( ), SQLMORERESULTS( ), SQLTABLES( ), and SQLCOLUMNS( ). You enable asynchronous processing by setting the Asynchronous property of the connection with the SQLSETPROP( ) function. When asynchronous communication is established for the connection, all four of these functions operate asynchronously.

To check the setting of the Asynchronous property

Use the SQLGETPROP( ) function to view the Asynchronous property setting. In the following example, nConnectionHandle represents the handle number for your active connection:
```
? SQLGETPROP(nConnectionHandle,'Asynchronous') 
```

To enable asynchronous processing

Use the SQLSETPROP( ) function to specify the Asynchronous property:
```
? SQLSETPROP(nConnectionHandle,'Asynchronous', .T.) 
```

In Asynchronous mode, you must call each function repeatedly until it returns a value other than 0 (still executing). While the function is still executing, you can cancel processing of the function by pressing the ESC key if the SET ESCAPE property is set to true (.T.).

Until the function has finished processing, the application can use a connection handle only with SQLCANCEL( ) or with the asynchronous function SQLEXEC( ), SQLMORERESULTS( ), SQLTABLES( ), or SQLCOLUMNS( ) originally associated with the handle. You can t call any of the other three asynchronous functions or SQLDISCONNECT( ) with the same connection handle until the function has finished.

Processing Multiple Result Sets

Your application retrieves multiple result sets when you use the SQLEXEC( ) function to issue more than one SQL SELECT statement, or to execute a stored procedure that issues multiple SELECT statements. The results of each SQL SELECT statement are returned in a separate Visual FoxPro cursor.

The default name SQLRESULT is used for the first cursor; subsequent cursors are assigned unique names by indexing the default name. For example, the default names for the cursors returned by a SQLEXEC( ) statement requesting three result sets are Sqlresult, Sqlresult1, and Sqlresult2.

In batch mode, if a function returns multiple result sets, the respective cursor names in Visual FoxPro have unique suffixes and can have up to 255 characters. For example, the following example sets the BatchMode property to batch mode, and then issues a SQLEXEC( ) statement containing four SQL SELECT statements that build four result sets:

? SQLSETPROP(nConnectionHandle,'BatchMode', .T.)  ? SQLEXEC(nConnectionHandle,'select * from authors ;                       select * from titles ;                       select * from roysched ;                       select * from titleauthor','ITEM')

When the function above has completed processing, Visual FoxPro returns the four result sets as the Visual FoxPro cursors Item, Item1, Item2, and Item3.

You can change the default name by using the cCursorname parameter with the SQLEXEC( ) or SQLMORERESULTS( ) functions. If the name you specify for a result set has already been used, the new result set overwrites the existing cursor.

When your application retrieves multiple result sets, you can choose between asynchronous or synchronous processing and batch or non-batch modes.

Using Batch Mode Processing

The BatchMode property, set with SQLSETPROP( ), controls how SQLEXEC( ) returns multiple result sets. The default value is .T., for batch mode. Batch mode processing means that Visual FoxPro doesn t return any results from a still-executing SQLEXEC( ) call until all of the individual result sets have been retrieved.

Using Non-Batch Mode Processing

If you use SQLSETPROP( ) to set the BatchMode property to .F., for non-batch mode, each result set is returned individually. The first result set is returned by the SQLEXEC( ) function call. Your application must then call SQLMORERESULTS( ) repeatedly until a value of 2 is returned, indicating that no more results are available.

In non-batch mode, the cursor name can be changed in each subsequent SQLMORERESULTS( ) call. In the previous example, if the first cursor name in a SQLEXEC( ) sequence is Item, and the second SQLMORERESULTS( ) call changes the cCursorName parameter to Otheritem, the resulting cursors will be named Item, Item1, Otheritem, and Otheritem1.

The next section describes batch mode and non-batch mode processing with synchronous and asynchronous detail added. The following diagram provides a representation of the four possible processing combinations. The numbers 0, 1, and 2 represent the values returned when you call each function.

Visual FoxPro synchronous and asynchronous processing modes

The behavior of each type of processing is explained below: the labels A, B, C, and D reference the preceding diagram. Each explanation assumes the execution of a statement that will return three result sets, represented in the diagram by three horizontal bands.

Using Synchronous Processing

In synchronous mode, control doesn't return to your application until the execution of a function is complete.

A: Synchronous Batch Mode

When you execute a SQL pass-through statement synchronously in batch mode, control isn t returned until all result sets have been retrieved. You specify the name of the first cursor by using the cCursorname parameter in the original function. If the cursor you specify already exists, the result set overwrites the existing cursor. When you request multiple result sets in synchronous batch mode, Visual FoxPro creates the names of additional cursors by uniquely indexing the name of the first cursor.

B: Synchronous Non-Batch Mode

When you execute a SQL pass-through statement synchronously in non-batch mode, the first statement retrieves the first result set and returns a 1. You must then call the SQLMORERESULTS( ) function repeatedly, and optionally specify a new name for the cursor. If you don t specify a new name for the cursor, multiple names for multiple result sets are created by uniquely indexing the base name. When SQLMORERESULTS( ) returns a value of 2, there are no more results available.

Using Asynchronous Processing

In asynchronous mode, your application must continue calling the same SQL pass-through function until it returns a value other than 0 (still executing). The default result set name, Sqlresult, can be explicitly changed with the cCursorname parameter the first time you call the function. If the name you specify for a result set has already been used, the new result set overwrites the information in the existing cursor.

C: Asynchronous Batch Mode

When you execute asynchronously in batch mode, each repeat call of the original function returns a 0 (still executing) until all of the multiple result sets have been returned to the specified cursors. When all results have been retrieved, the return value is either the number of cursors, or a negative number indicating an error.

D: Asynchronous Non-Batch Mode

When processing asynchronously in non-batch mode, SQLEXEC( ) returns a value of 1 when it completes the retrieval of each result set. Your application must then call SQLMORERESULTS( ) repeatedly until a value of 2 is returned, indicating that no more results are available.

Tip Remote result sets are retrieved in two stages: first, the result set is prepared on the server; then the result set is fetched into a local Visual FoxPro cursor. In asynchronous mode, you can call the USED( ) function to see whether Visual FoxPro has started fetching the cursor you requested.

Controlling Data Type Conversion

When you move data between a remote server and Visual FoxPro, you may encounter differences in the richness of data types available either on your server or in Visual FoxPro, because there is rarely a one-to-one correlation between data types available on a remote data source and those available in Visual FoxPro. To handle these differences, Visual FoxPro uses ODBC data types to map remote data types to local Visual FoxPro data types. By understanding how data types are mapped between ODBC and Visual FoxPro, you can predict how your server s remote data will be handled in your Visual FoxPro application.

If you need to, you can also adjust the data types used on your server or in your application. The default Visual FoxPro field data type can be overridden by creating a view for the remote data set and then setting the DataType view field property in the database. The DataType property is a character property indicating the desired data type for each field of a remote view. For more information on the DataType property, see DBSETPROP( ).

Downloading and Uploading Remote View Data

When you retrieve data from a remote ODBC data source, Visual FoxPro converts the data type of each ODBC field into an equivalent Visual FoxPro data type in the result set cursor. The following table lists the data types available on ODBC data sources and their Visual FoxPro equivalents.

ODBC data type of remote field	Field data type in Visual FoxPro cursor
SQL_CHAR SQL_VARCHAR SQL_LONGVARCHAR	Character or Memo¹
SQL_BINARY SQL_VARBINARY SQL_LONGVARBINARY	Memo
SQL_DECIMAL SQL_NUMERIC	Numeric or Currency²
SQL_BIT	Logical
SQL_TINYINT SQL_SMALLINT SQL_INTEGER	Integer
SQL_BIGINT	Character
SQL_REAL SQL_FLOAT SQL_DOUBLE	Double; the number of decimal places is the value of SET DECIMAL in Visual FoxPro
SQL_DATE	Date
SQL_TIME	DateTime³
SQL_TIMESTAMP	DateTime⁴

1 If the ODBC field width is less than the value of the cursor property UseMemoSize, it becomes a Character field in the Visual FoxPro cursor; otherwise, it becomes a Memo field.
2 If the server field is a money data type, it becomes a Currency data type in Visual FoxPro.
3 The day defaults to 1/1/1900.
4 If the value in the SQL_TIMESTAMP field contains fractions of seconds, the fractions are truncated when the value is converted to a Visual FoxPro DateTime data type.

Note Null values in ODBC data source fields become null values in the Visual FoxPro cursor, regardless of the SET NULL setting in Visual FoxPro at the time your application retrieves remote data.

Converting Visual FoxPro Parameters to Remote View Data Types

If Visual FoxPro data exists in a cursor that originated from remote data, the data goes back to its original ODBC type when sent to the remote server. If you send data that originated in Visual FoxPro to the remote server via SQL pass-through, the following conversions apply.

Visual FoxPro data type	ODBC data type
Character	SQL_CHAR or SQL_LONGVARCHAR¹
Currency	SQL_DECIMAL
Date	SQL_DATE or SQL_TIMESTAMP²
DateTime	SQL_TIMESTAMP
Double	SQL_DOUBLE
Integer	SQL_INTEGER
General	SQL_LONGVARBINARY
Logical	SQL_BIT
Memo	SQL_LONGVARCHAR
Numeric	SQL_DOUBLE

1 If the Visual FoxPro variable that maps to a parameter creates an expression whose width is less than 255, it becomes a SQL_CHAR type in the ODBC data source; otherwise, it becomes a SQL_LONGVARCHAR type.
2 Visual FoxPro Date data is converted to SQL_DATE for all ODBC data sources except SQL Server, where it becomes SQL_TIMESTAMP.

Mapping a Visual FoxPro Parameter Into a Remote Data Type

You can map a Visual FoxPro parameter value to a particular remote data type by formatting the parameter as a character expression that uses the syntax for the desired remote data type. For example, if your server provides a DateTime data type, you can create your Visual FoxPro parameter as a character expression in the format used by your server to represent DateTime data. When your server receives the parameter value, it attempts to map the formatted data to the DateTime data type.

Note When you send a parameter to the remote server, be sure the data type in the WHERE clause matches the data type that's used for the parameter expression.

Handling SQL Pass-Through Errors

If a SQL pass-through function returns an error, Visual FoxPro stores the error message in an array. The AERROR( ) function provides information about errors that are detected in any of the component levels: Visual FoxPro, the ODBC data source, or the remote server. By examining the values returned by AERROR( ), you can determine the server error that occurred and its error message text.

Important You must call AERROR( ) immediately to obtain error information. If you generate any other error before you call AERROR( ), the error information is lost.