Chapter 21: Implementing a Client/Server 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

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

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.

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

resultCode = SQLExec(connHand,
   "CREATE PROCEDURE sp_test;
      @mult1 int, @mult2 int, @result int;
   OUTPUT AS SELECT
      @result = @mult1 * @mult2")

outParam = 0

resultCode = SQLExec(connHand, ;
"{CALL sp_test (2, 4, ?@outParam)}")

? "outParam =", outParam  && the value is 8

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

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.

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

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:

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:

Engine Properties

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

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

* 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

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.

CURSORSETPROP('buffering', 5, 'sqltitles')

* Update batched changes;
* ignore changes made by others

TABLEUPDATE(.T., .T., 'titles')

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.

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.

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.

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.