Advanced Client\Server in Visual FoxPro


This document is reprinted from the Microsoft DevCon 95 Speaker materials and is provided "as-is." This document and any associated demo files were created using Visual FoxPro 3.0. Some features discussed may have changed in Visual FoxPro 5.0.

Melissa W. Dunn, M.A

Micro Endeavors, Inc.

SQL Pass-Through

SQL pass-through allows for connection, and communication, with back end servers. Unlike remote views, connection information is not stored in the database but has to be re-established each time. Once the connection has been established, you can use other pass-through commands to execute stored procedures, back end triggers and rules, initiate remote transaction processing. Further, SQL pass-through commands can be used with Visual FoxPro parameters (with some limitations). The processing itself may occur in batch or non batch mode and in synchronous or asynchronous mode.

This session is devoted to fostering an understanding of the fundamentals involved in making wise decisions when using SQL pass-through.

Connections

In order to communicate with a back end, such as SQL Server, Visual FoxPro requires a handle which references the connection to the server, or data source. If the connection request is successful, Visual FoxPro receives a non-zero numeric handle.

Each connection is a separate transaction space which can only process a single statement stream. If you want to be able to process several different transactions against the same data source you can request multiple connections. In other words, let’s say that the customer information is stored in a database devoted solely to customer information and the order entry information in another database solely used for orders. Even though both databases were stored within the MEIServer data source, you would need one handle for each connection.

On the other hand, you may request connections to multiple servers. In a server environment, this would not be all that atypical. After all, you may have sales information for rep commissions stored in an entirely different server from the order entry. In this case, you would require a connection handle to both data sources.

Client-Server Environment

The Visual FoxPro native environment, the environment which is established when a VF session is started, contains the following defaults for any client-server connections:

SQLSetProp()

The SQLSETPROP() function is used to control the default property settings for both the client-server environment and specific connections, data sources or attached tables. The settings and their return values appear in the table below:

Setting Description Value Read/Write
Asynchronous Result set returned synchronously or synchronously 0 Synchronous (default)1 Asynchronous Yes
BatchMode SQLEXEC() function returns results sets that all at once (Batch) or individually with SQLMORERESULTS() (NonBatch) 0 NonBatch 1 Batch (default) Yes
ConnectName The connection name The connection name No
ConnectString The login connection string The login connection string Yes
ConnectTimeOut Specifies the time (in seconds) to wait before returning a connection time-out error. Range is 0 to 600 0 no time out (default)> 0 number of seconds to wait *only available at the global environment level Yes
DataSource The name of the data source as defined in the ODBC.INI file The name of the data source Yes
DispLogin Determines when the ODBC Login dialog is displayed 1 (DB_COMPLETE) Login displays only when information is incomplete (default)2 (DB_PROMPT) Login always displayed3 (DB_NOPROMPT) Login never displayed. If information is missing an error is generated Yes
DispWarnings Determines if error messages are displayed 0 Not displayed (default)1 Displayed Yes
ODBChdbc Internal ODBC connection handle which may be used by external library (FLL) files to call ODBC Connection handle No
ODBChstmt Internal ODBC statement handle which may be used by external library (FLL) files to call ODBC Statement handle No
Password The connection password The connection password No
QueryTimeOut Time to wait (in seconds) before returning a general time-out error 0 indefinite (no error returned)15 (Default)600 Max number Yes
Transactions Determines how the connection manages transactions on the remote table(s) 1 (DB_TRANSAUTO) Transaction processing is automatically handled2 (DB_TRANSMANUAL) Transaction processing handled manually through SQLCOMMIT() and SQLROLLBACK() functions Yes
UserID The user identification The user identification Yes
IdleTimeout Determines the idle timeout interval (in seconds) before an active connection is deactivated 0 Wait indefinitely (default)> 0 Number of actual seconds Yes
WaitTime Amount of time (in milliseconds) that Visual FoxPro waits before checking if the SQL statement has finished executing 100 (default) Yes

Note You can use logical variables (0 = .F., 1 = .T.) or string expressions (0 = “On”, 1 = “Off”) for any value that takes one of two values.

Generally, before you set the properties for an active connection, you would want to know, if not save, the current setting values.

SQLGetProp()

The SQLGetProp() function will return the current value for the requested property setting. For instance, to find the environment (or new connection default) for transactions you would run this line of code:


?SQLGetProp(0, “Transactions”)

Note A connection handle of zero (0) indicates that you are requesting information about, or want to change the global client-server environment rather than a specific active connection. If you change the value of a global setting, new connections will be opened with the changed value. Established connections will retain their old values, however.

You can use the following to reset all of the global environment settings back to their defaults:

?SQLSetProp(0, “*”)

The same general rules apply to using the SQLGetProp() and SQLSetProp() functions with specific connections. The only difference is that you must supply the specific connection handle. The following code would therefore work for an established connection whose reference is stored in the variable handel:

If SQLGetProp(handel, "trans") <> 2
	m.oldtrans = SQLGetProp(handel, "trans")
EndIf

?SQLSetProp(handel, “trans”, 2)

As with global settings, you can reset a specific connection’s properties back to the defaults by using the wildcard:

?SQLGetProp(handel, “*”)

Before you can work with the specific connections, you need to have connection handles established. Establishing a connection is accomplished using one of two functions: SQLConnect() or SQLStringConnect().

SQLConnect()

The SQLConnect() function establishes a connection to a data source in one of two ways. First, you can specify the data source name, userID and password:

m.handel = SQLConnect(“meiserver”, “sa”,””)

Alternatively, you can specify a connection name that you have created using CREATE CONNECTION:

m.handel = SQLCONNECT(“meibench”)

SQLConnect( ) will return a positive non-zero numeric handle if you successfully connect to the data source, or a - 2 if the connection cannot be made.

If SQLCONNECT( ) is issued without any arguments, the SQL Data Sources dialog box will be displayed so long as the current SQLSetProp() DispLogin has been set to 1 or 2. When the SQL Data Sources dialog is displayed, you can choose a data source from the list of data sources and stored connections.

Typically, the return value from the SQLConnect() function, or the assigned connection handle, is assigned to a memory variable (e.g. m.handel) that is used as the connection reference for subsequent SQL pass-through functions.

SQLStringConnect()

If you need to connect to a server directly through the ODBC, you can use the SQLStringConnect() function. The SQLStringConnect() function is used to specify the data source connection string required by some ODBC drivers. Visual FoxPro passes the connection string to the ODBC driver. For example, I can connect to meibench using the following:

m.handel =
SQLStringConnect(“ODBC;DSN=meibench;SERVER=meisql;UID=sa;PWD=“)

Like the SQLConnect() function, SQLStringConnect( ) returns a positive non-zero numeric handle if you successfully connect to the data source.

SQLDisconnect()

Once you have finished with a connection you should close it. This limits the total number of connections in use on the server to only active connections. Remember that server connections are a finite resource which must be efficiently maintained.

Disconnecting a connection is accomplished via the SQLDisconnect() function:

?SQLDisconnect(handel)

The SQLDisconnect( ) returns 1 if the connection is successfully terminated, - 1 if there is a connection level error, and - 2 if there is an environment level error.

Note If you execute SQLDisconnect( ) within an asynchronous function sequence or during a transaction, SQLDisconnect( ) generates an error.

Executing Code

Having established a connection, you can now pass SQL commands back to the server. The commands used can be any statement acceptable to your specific back end (for this session, we will be referencing Microsoft SQL Server syntax.) Passing the commands through ODBC is accomplished through the use of the SQLExec() function

SQLExec()

The SQLExec() function requires the use of the connection handle, a SQL command string and optionally, the name of the FoxPro cursor to which you want the result set sent. If you do not name the resulting cursor in the SQLEXEC() function, FoxPro will default the cursor name to SQLResult.

The return value from SQLExec() indicates whether the command is still executing (0) or not (1). If an error is encountered at the connection level, a -1 is returned. If the error encountered is at the environmental level a -2 is returned.

So, what can you pass-through to the server? Just about anything from creating and populating tables to building and using stored procedures, rules and triggers.

Parameters

When we use Visual FoxPro to communicate with a back end server, we often need to reference the contents of a local variable, column, or an object property. Sometimes we might need to send a complex expression which includes one or more of these objects.

If we are dealing with the simple passing of a local variable, column or object property, we can reference the value within the SQL statement by preceding the name of the object with a colon (:). For instance, if we need to get all of the information about a user-specified sales rep, we might use the following code:


m.mvar = "BURPS"
?SQLEXEC(handel,"SELECT * FROM bkreps WHERE sr_repid = :m.mvar")

There are some limitations as to where you can use parameters within the SQL statement. You cannot use a parameter in any of the following situations:


On the other hand, I can use memory variable as an expression itself, as in the following example used to create and populate a new table on the server:

handel = SQLConnect("meibench","sa","")
=SQLSetProp(handel,"DispWarnings",1)
*! create a line items table on server from fox
mvar = "CREATE TABLE bkordl(ol_orderno char(12),ol_item char(6),"+;
	"ol_cost smallmoney null,ol_price smallmoney,ol_qty int,"+;
	"ol_taxable bit,ol_exte money null,timestamp timestamp)"
retcode = SQLExec(handel,mvar)

if retcode < 0
 return
endif
*! now send the fox data to the server
use \bench\bkitms in 0 again
select bkitms
go top
scan
scatter memvar memo
mvar1 = ["]+m.ol_orderno+[","]+m.ol_invntry+;
	[",CONVERT(smallmoney,]+STR(m.ol_cost,9,2)+;
	[),CONVERT(smallmoney,]+;
	STR(m.ol_price,9,2)+[),CONVERT(int,]+STR(m.ol_qty,7,0)+;
	[),CONVERT(bit,]+IIF(m.ol_taxable,"1","0")+[),CONVERT(money,]+;
	STR(m.ol_exte,10,2)+[)]

mvar = "INSERT INTO bkordl (ol_orderno,ol_item,ol_cost,"+;
	"ol_price,ol_qty,ol_taxable,ol_exte) VALUES("+mvar1+")"	
retcode = SQLExec(handel,mvar)
IF retcode < 0
  wait window "Unable to insert record"
  return
ENDIF  
endscan

Obviously, the CREATE TABLE could have been passed directly through to the server without having been placed into a memory variable first. Populating the new server table with Visual FoxPro data is another story, however. Given that we are combining SQL Server functions, such as CONVERT, with values stored in Visual FoxPro variables, we would have a rather complex expression. Remembering that complex expressions containing FoxPro parameters cannot be passed through, we establish a variable which represents the entire expression (i.e. mvar) and reference the variable itself (e.g. SQLExec(handel,mvar)).

Rules

As you probably already know from Visual FoxPro itself, rules are typically used for integrity constraints. While most often applied to a specific column, a rule can be used for several columns.

Rules are evaluated whenever the user attempts to INSERT or UPDATE a value. If the rule is violated, an error is generated. If you were doing an UPDATE, the column value is “rolled back”.

Creating a rule is fairly straightforward:


retcode = SQLEXEC(handel,”CREATE RULE startdate AS @cu_stdate BETWEEN ‘09/01/90’ AND GETDATE()”)

retcode = SQLEXEC(handel,”SP_BINDRULE startdate, ‘bkcust.cu_stdate’”)


Rules accept a parameter (e.g. @cu_stdate) which references the column value to be affected by the INSERT or UPDATE. While the parameter may be named anything so long as it begins with @, it is not a bad idea to use the name of the column as a reminder (e.g. cu_stdate).

The rule’s definition can contain any expression valid for a WHERE clause and can include arithmetic and comparison operators, and the special comparisons of LIKE, IN and BETWEEN.

The rule startdate, for instance, was created for the column cu_stdate and is meant to ensure that no date less than September 01, 1990 or greater than today (GETDATE()) can be entered. Notice that rules can reference built-in functions which do not reference an object, such as GETDATE(). Direct references to columns (e.g. oh_ordate) or database objects is not allowed.


Note Rules must be bound to the column(s) before they can be applied. Hence the second bit of TRANSACT-SQL code which calls the catalog stored procedure SP_BINDRULE.

Stored procedures

A stored procedure is a pre-compiled set of Transact-SQL statements. This set of statements is stored as a database object within the system catalog (data dictionary). All of the parsing, normalization and optimization which occurs when a SQL statement is executed is completed when the procedure is defined. Furthermore, the time required to generate an execution plan only occurs the first time the procedure is run. Once executed, the plan is stored in the database along with the procedure itself.

Stored procedures can accept parameters, send back return values and call other stored procedures, including recursive calls to the current procedure. Related procedures may also be grouped together into a single procedure. This procedure may then be run as a group or individually.

The following stored procedure will actually create a remote view containing a list of company names, total number of orders and sales rep commissions by customer for a user (or program) specified sales rep. Such a stored procedure might be used to limit what a sales rep can see of the orders database.


CREATE PROCEDURE repview @repid char(5)
As SELECT cu_company,cu_custid,count(DISTINCT oh_orderno) orders,
SUM(oh_commsn) tcomm
FROM bkcust,bkordh,bkordl
WHERE cu_custid = oh_custid
AND ol_orderno = oh_orderno
AND oh_repid = @repid
GROUP BY cu_custid

To actually run the stored procedure, you simply need to issue the EXECUTE command:

retcode = SQLExec(handel,”EXECUTE repview ‘SWILL’”)

Given that the sales rep is not going to be allowed to change the underlying tables, but will only be able to query their information, the stored procedure becomes more economical. Besides, since sales reps may come and go, I would continually have to adjust the number and types of views. With the stored procedure, I just change the parameter value.

There are several other advantages to stored procedures: first, stored procedures can reduce the amount of network traffic since I am only sending the short Execute command rather than all of the possible SQL statements that the procedure may contain.

Second, because stored procedures are precompiled and cached, they execute faster.

Finally, because the stored procedure resides on the server, rather than the client, the procedure can be shared by multiple front-end applications and users.

Triggers

Triggers are specialized stored procedures that are executed automatically whenever the associated data modification occurs in the specified table. Triggers are most often used to maintain system consistency or enforce referential integrity.

Triggers are “bound” to one of the data modification statements (INSERT, UPDATE, DELETE) and are executed immediately after the modification statements are complete. Since triggers are considered to be part of the same transaction as the modification statement, transaction rollbacks can occur if required.

Triggers can be used to provide:


The SQL statements used may be simple or complex.

In fact, multiple conditions and actions may be evaluated and carried out within a single trigger. Controlling the flow of these statements is accomplished by using IF ... BEGIN ... END control-of-flow language.

Finally, you may specify which column updates are of interest by using the IF UPDATE clause.

I have provided the following triggers in order to demonstrate how powerful and useful triggers can be in any application. The first trigger, nextcustid, ensures that new customer rows will be assigned a sequential id number from a back-end system table.


CREATE TRIGGER nextcustid ON bkcust FOR INSERT AS
UPDATE bkcust 
SET cu_custid = RIGHT('000000'+ CONVERT(char(6),sy_number),6) 
FROM bksystem WHERE cu_custid IS NULL
AND sy_tag = 'CUSTOMER'
UPDATE bksystem SET sy_number = sy_number + 1
WHERE sy_tag = 'CUSTOMER'

Notice that this trigger actually contains two different procedures. The first set of SQL statements assigns the next sequential id number from the system table after converting the value to a string and then padding it with zeros.

CREATE TRIGGER nextcustid ON bkcust FOR INSERT AS
UPDATE bkcust 
SET cu_custid = RIGHT('000000'+ CONVERT(char(6),sy_number),6) 
FROM bksystem WHERE cu_custid IS NULL
AND sy_tag = 'CUSTOMER'

After having assigned the current system number, I update the nextid value in the system table.

UPDATE bksystem SET sy_number = sy_number + 1
WHERE sy_tag = ‘CUSTOMER’

This last trigger demonstrates a restricted delete rule. The trigger is activated when the user attempts to delete a customer row:

CREATE TRIGGER delcust ON bkcust FOR DELETE AS
IF (SELECT COUNT(*) FROM deleted,bkordh
   WHERE bkordh.oh_custid = deleted.cu_custid) > 0
   BEGIN
      ROLLBACK TRANSACTION
   END
ELSE
  INSERT bkhist (hs_custid,hs_company,hs_deldate)
  SELECT cu_custid, cu_company,GETDATE()
  FROM deleted

Before explaining how this trigger works, please remember the following: first, the trigger is executed after the delete has taken place. Second, deleted rows are stored in a special system table, deleted. Finally, triggers are considered part of the modification transaction.

All of these being true, here’s what happens:


Not all SQL statements are allowed in a trigger. Here is a list of the prohibited statements:
Note If an error occurs at the ODBC level while attempting to connect or execute SQL pass through functions, AERROR() can be used to trap and evaluate the error. The following table indicates what each of the five array elements will contain:

Element number Return Value Description
1 Numeric Contains the remote number of the error.
2 Character The text of the remote error message.
3 Numeric Contains the connection handle number if a SQL call fails.
4 .NULL.  
5 .NULL.  

Result Sets

With Visual FoxPro, we no longer have to update remote data by issuing SQL commands directly to the server through SQL pass-through. In fact, you can update the remote data directly from within the returned cursor. The properties associated with a cursor determine whether or not columns in the remote table(s) can be updated.

As with the connection properties, there are two built-in function to assist with the setting of cursor properties: CursorSetProp() and CursorGetProp().

CursorSetProp()/CursorGetProp()

You use the CursorSetProp() function to set the properties for a Visual FoxPro cursor (or table). The following table lists each of the function’s properties.

Property Value Description
Buffering 23 (default)45 Sets row and table buffering off. Record locking and data writing are identical to earlier versions of FoxProSets pessimistic row buffering onSets optimistic row buffering onSets pessimistic table buffering onSets optimistic table buffering on
FetchMemo .T..F. Memo fields are fetched with the view resultsMemo fields are not fetched with the view results
FetchSize 100 (default) Number of rows progressively fetched from the remote table result set
KeyFieldList   Comma delimited list of primary fields for the cursor.
MaxRecords -1 (default)0>0 All rows are returnedView is executed but no results are fetchedThe maximum number of rows fetched when result sets are returned
SendUpdates .T. (default) Specifies that a SQL update query is sent to remote tables
.F. Specifies that a SQL update query is not sent to update remote tables
Tables   Comma delimited list of the names of remote tables.
UpdatableFieldList   Comma delimited list of remote field names and the local field names assigned to the cursor.
Use this option to specify valid Visual FoxPro names for fields in the cursor that have invalid Visual FoxPro field names
UpdateType 1

2 (default)
Specifies that updates occur by deleting the old data and inserting the new data (standard procedure for ANSI/ISO UPDATE command)Specifies that old data is updated with the new data
UseMemoSize 1 to 255 (default) Specifies the minimum size (in bytes) for which result columns are returned in memo fields. If the width of a column result is greater than the value of the UseMemoSize value, the column result is stored in a memo field.
WhereType 1 The WHERE clause used to update the remote tables consists of only the primary fields specified with the KeyFieldList property
3 (default) The WHERE clause used to update remote tables consists of the primary fields specified with the KeyFieldList property and any other fields that are updated

Before you can update directly within a cursor based upon a remote table, you need to set at least the KeyFieldsList, UpdatableFieldList and Tables properties:

handel = sqlconnect("meiserver","sa","")
=sqlexec(handel,"use bench")
=sqlexec(handel,"SELECT * FROM bkreps","temp")
=cursorsetprop("keyfieldlist","sr_repid","temp")
=cursorsetprop("tables","bkreps","temp")
=cursorsetprop("updatablefieldlist","sr_first,sr_last,sr_commsn","temp")

In the above example, the result set will be sent to a cursor named “temp” rather than the default “sqlresult”. The properties for the cursor “temp” are then set so that Visual FoxPro can update the specified table bkreps using a WHERE clause which reads as WHERE sr_repid = <current row value>. The WHERE clause values are designated by the keyfieldlist property (e.g. sr_repid). Finally, only the columns specified with the updatablefieldlist property may actually be updated on the server: sr_first, sr_last and sr_commsn.

The first table in the Tables list is used as the default qualifier for all columns retrieved in to the cursor. The only exception occurs when a cursor is created from two remote tables which contain duplicate column names (i.e. repid). If this condition exists, you must use the UpdatableFieldList property to specify a remote table name for each column.

For example, if the column state exists in both the sales rep table (bkrep) and the customer table (bkcust), I would use the following property setting to ensure that only the value in the customer’s state column is updated:


=CursorSetProp(“UpdatableFieldList”,”state bkcust.state”)

Note To avoid creating duplicate column names in the result set, you should alias the result set columns in the initial SQL statement (e.g. SELECT bkcust.state, bkreps.sr_first ...) You can then use the Visual FoxPro UpdatableFieldList property of the result set cursor to map each aliased column to the correct remote table and column name.

Further, if the sales rep table(bkreps) is excluded from the list provided with the Tables property setting, column values from that table cannot be updated.


To find out the current setting of a cursor’s properties, you use the CursorGetProp() function.

Processing

Both retrieving and updating remote data sources involves processing the SQL requests and transmitting the data between the Visual FoxPro front end and the server back end. Visual FoxPro provides two methods for processing data using SQL pass through: synchronous and asynchronous.

Synchronous vs Asynchronous

When you process a request synchronously Visual FoxPro does not return control to the application until the function call has been completed. In other words, program execution is halted until the entire result set, as defined by the BatchMode property of SQLSetProp(), is returned from the remote server. Given that Visual FoxPro assumes that most work is done interactively, synchronous processing is the default.

Asynchronous processing provides greater flexibility by continuing to execute code while the SQL command is executed in the background. Asynchronous processing is required if you want to do such things as build a thermometer indicating the progress of the query or provide the user with a means of interrupting the process if it seems to be taking too much time.

Since asynchronous processing is not the default, you will have to reset the connection’s processing method using SQLSetProp():


=SQLSetProp(handel,”Asynchronous”,1)

There is slightly more work involved in running SQL pass through synchronously. First of all, there are only four functions which will operate synchronously: SQLExec(), SQLMoreResults(), SQLTables() and SQLColumns(). You are already familiar with the SQLexec() function, so I will briefly go through the remaining three.

SQLTables()

The SQLTables() function store the names of the tables in the remote source into a Visual FoxPro cursor. The arguments required for this function include: the variable referencing the connection handle to the data source named in SQLConnect( ); the type of tables you want listed in the cursor; and the name of the Visual FoxPro cursor, if you don’t want the default of SQLResult.

The valid table types are ‘TABLE,’ ‘VIEW,’ ‘SYSTEM TABLE,’ or any valid data source-specific table type identifier. The TableTypes must be in upper-case. If you include a list of table types, separate the table types with commas. Note that all table names in the data source are selected if you omit TableTypes argument or if the TableTypes argument is an empty string. Finally, the table type(s) must be delimited with single quotation marks.


? SQLTABLES(handel, “‘VIEW’, ‘SYSTEM TABLE’”, “myresult”)

The following table shows the columns in the cursor.

Column name Description
TABLE_QUALIFIER Table qualifier identifier
TABLE_OWNER Table owner identifier
TABLE_NAME The table name as it appears in the data dictionary
TABLE_TYPE The table type as it appears in the data dictionary
REMARKS A description of the table

SQLTABLES( ) returns a 1 if the cursor is successfully created, a 0 if SQLTABLES( ) is still executing, -1 if a connection level error occurs, and - 2 if an environment level error occurs.

SQLColumns()

The SQLColumns() function stores a list of column names and information about each column for the specified data source table to a Visual FoxPro cursor.

The arguments required for the SQLColumns() function include the connection handle, the name of the remote source table whose column information is being requested, the format for the column information in the result set and the name of the resulting cursor if SQLResult is not desired.

New here, is the format for the column information in the result set. There are two options: NATIVE and FOXPRO. The NATIVE format option stores column information for tables in the same format as the data source. The FOXPRO format option stores the column information in the same format as that used for the Visual FoxPro table or cursor that would be created if you imported the data source table into Visual FoxPro.


Notes First, if you omit FOXPRO or NATIVE, the format option defaults to FOXPRO. Second, be sure to enclose FOXPRO or NATIVE in quotation marks.
The following table shows the columns in the result set for the FOXPRO format.

Column name Description
Field_name Column name
Field_type Column data type
Field_len Column length
Field_dec Number of decimal places

The following table shows the columns in the result set for the NATIVE format. In NATIVE format, depending on the data source, additional columns not listed in the following table may be included in the result set.

Column name Description
Table_qualifier Table qualifier identifier
Table_owner Table owner identifier
Table_name Table identifier
Column_name Column identifier
Data_type Column data type
Type_name Column data type name
Precision Precision of the column
Length Transfer size of the data
Scale Scale of the column
Radix Base for Numeric type
Nullable Supports null values
Remarks Description of the column

There are a variety of return values for the SQLColumns() function, as you can see from the following table:

Return Value Meaning Action
True (.T.) TableName specified doesn’t exist and format is set to NATIVE Creates an empty table or cursor
False (.F.) TableName specified doesn’t exist and format is set to FOXPRO  
1 Cursor successfully created  
0 SQLColumns() is still executing In asynchronous mode, keep calling SQLColumns() until a 1 is returned
-1 Connection level error  
-2 Environment level error  

?SQLColumns(handel,”bkcust”)

SQLMoreResults()

The last of the new SQL pass through functions is SQLMoreResults(). The SQLMoreResults() function copies another result set to a Visual FoxPro cursor if more result sets are available.

SQLMoreResults( ) determines if more result sets are available from a SQL statement executed with SQLExec( ) in non-batch mode (we’ll discuss batch and non-batch modes in a moment). If more result sets are available, they are copied to a Visual FoxPro cursor, one set at a time.

SQLMoreResults( ) returns 0 if the SQL statement is still executing, 1 if it is finished executing, and returns 2 if no more data is found. In non-batch mode, SQLMoreResults( ) should be called after each successful SQLExec( ) call until SQLMoreResults( ) returns 2 (no more data found). As with all SQL pass through functions, SQLMoreResults( ) returns - 1 if a connection level error occurs, and - 2 if an environment level error occurs.


=SQLSetProp(handel,"Asynchronous",1)
=SQLExec(handel,"Select cu_custid,oh_orderno,oh_ordate,ol_item,"+;
		"ol_exte FROM bkcust,bkordh,bkordl "+;
		"WHERE oh_custid = cu_custid "+;
		"AND ol_orderno = oh_orderno "+;
		"AND oh_ordate BETWEEN '01/01/94' AND :m.lastdate")

=CursorSetProp("FetchSize",50,"SQLResult")
m.moreresults = 0
DO WHILE m.moreresults = 0
	m.moreresults = SQLMoreResults(handel)
ENDDO

A common theme you may have noticed is that when you use asynchronous processing, you need to call each function repeatedly until the return value is something other than zero (which indicates that the process is still executing in the background). Until the function has completed, you can only use that specific connection handle with the SQLCancel() function or the asynchronous function originally associated with the connection handle (i.e. SQLExec()) or SQLMoreResults()(depending upon the processing mode).

When you issue a SQLExec() function which issues more than one SELECT statement, or you execute a stored procedure that issues multiple SELECT statements. The results of each SQL SELECT statement are returned in separate Visual FoxPro cursors.

If you recall, the default name for the first cursor is SQLResult. All subsequent cursors are assigned unique names by indexing the default name (i.e. SQLResult, SQLResult1, SQLResult2, etc.) When you change the default name by using the Cursorname argument in the SQLExec() function you need to make sure that the name isn’t already in use. If the name you specify for a result set has already been used, the new result set will overwrite the information in the existing cursor.

When the application retrieves multiple result sets you will have to choose between batch and non-batch modes, as well as synchronous and asynchronous.

Batch vs Non Batch

The SQLSetProp() function BatchMode property controls how SQLExec() returns multiple result sets.

Batch

If you recall from the previous table, the default value for the BatchMode property is 1, or batch mode. When using batch mode processing, Visual FoxPro doesn’t return any results from a SQLExec() call until all of the individual result sets have been retrieved on the back end.

Further, when in batch mode multiple result set cursors are given unique names by using post-fixed indexes. The number of result sets is limited to 10 characters:


=SQLSetProp(handel,”BatchMode”,1)
?SQLEXEC(handel,”SELECT * FROM bkcust;SELECT * FROM bkordh”,”mt”)

This SQLExec() function will produce two Visual FoxPro cursors mt1 and mt2

Non-Batch

If the BatchMode property is set to 0 (non-batch), then each result set is returned individually. The first result set is returned by the SQLExec() function call. From that point, the application must then call SQLMoreResults() repeatedly until a value of 2 is returned, indicating that no more results are available.

While in non-batch mode, the cursor name can be changed in each subsequent SQLMoreResults() call. Thus, if we use the above example, the first cursor name in the SQLEXEC() sequence would be MT. Now, if we change the cursor name in the subsequent SQLMoreResults()


?SQLMoreResults(handel,”newmt”)

the next cursor would be named NEWMT.

How you get result sets returned is a function of the combination of both BatchMode and Synchronous processing.

The table below shows the behavior of each type of processing.

Action Processing Type Sequence Description
  Synchronous   Control isn’t returned until all result sets have been retrievedYou specify the name of the first cursor by using the CursorName parameter in the original function. If the cursor name is already in use, the result set is overwritten. If multiple result sets are requested, Visual FoxPro creates the names of additional cursors by uniquely indexing the name of the first cursor.
Prepare, Progressively Fetch and send result set #1 Synchronous Batch SQLExec() Control remains with the SQLExec() until all result sets are returned (SQLExec() returns a 3)
Prepare, Progressively Fetch and send result set #1 Synchronous Non Batch SQLExec() The first statement retrieves the first results set and returns a 1 (no longer executing). When SqlMoreResults() returns a 2, there are no more results available.
Prepare, progressively fetch and send result set #2   SQLMoreResults() Call SQLMoreResults() repeatedly and optionally specify a new name for the cursor. If a new name is not specified for subsequent cursors, Visual FoxPro will create multiple cursors by uniquely indexing the base name.
etc     When SqlMoreResults() returns a 2, there are no more results available.
  Asynchronous   The application must continue calling the same SQL pass through function until the server returns a value other than 0 (still executing).The default result set name, SQLResult can be explicitly changed with the CursorName parameter the first time you call the function. If the cursor name specified for a result set has already been used, the new result set overwrites the information in the existing cursor.
Prepare, Progressively Fetch and send result set #1. Asynchronous Batch SQLExec() 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 cursor(s)
Prepare, progressively fetch and send result set #2   SQLExec() When all results have been retrieved, the return value is either the number of cursors, or a negative number, indicating an error
etc     Returns 0
Prepare, progressively fetch and send result set #1 Asynchronous NonBatch SQLExec() Repeat calls until a return of 1 indicates the retrieval of the first result set.
Prepare, progressively fetch and send result set #2   SQLMoreResults() Repeated calls to SQLMoreResults() until a value of 2 is returned, indicating no more result sets are available
etc.     (the USED() function can be called to determine whether the cursor is complete)

Updating Remote Tables

We have already spoken briefly about what you need to do to make the result set from a server updatable. We didn’t talk about the updating itself, however. Updating remote data sources is the final topic for this session.

Buffering

Updates to remote data sources are controlled by the Visual FoxPro cursor’s Buffering property. If will recall from the table on the arguments for CursorSetProp() above, there are 5 possible buffer settings. Only two of these are valid for remote servers:


If the cursor’s buffering property is set to the default (3), then changes made would be committed on a row by row basis. In other words, when the user moves through the cursor via a SKIP or GO BOTTOM command or by using the mouse or keyboard to move through a grid control, changes made to the row being exited would be sent to the server.

Alternatively, if you want changes to be committed a batch at a time via a control such as a SAVE or OK command button, you would set the Buffering property to 5


=CursorSetProp(“Buffering”,5,”mycursor”)

and then commit the changes as part of the CLICK event code for the appropriate command button

=TABLEUPDATE(.T.,.F.,”remotetable”)

Note Bear in mind that in either type of update, you are responsible via either code on the front or back end for checking for changes made by other users.

TABLEUPDATE()

If you are unfamiliar with the TABLEUPDATE() function, which commits changes made to a buffered row or a buffered table or cursor, the table below shows you the arguments and their options.

Argument Options Description
AllRows .T. If table buffering is enabled, changes made to all records are committed to the table or cursorIf row buffering is enabled, only changes made to the current row in the table or cursor are committed
F. (default) Only changes made to the current row in the table or cursor are committed, regardless as to whether row or table buffering is enabled
Force .T. Any changes made to the table or cursor by another user on a network are overwritten
.F. (default) Changes are committed to the table or cursor, starting with the first row and continuing towards the end of the table or cursor. If a row modified by another user on the network is encountered, an error is generated.
TableAlias   Specifies the table or cursor in which the changes are committed

Note TABLEUPDATE() will update the server without moving the record pointer in the local cursor.
When working on a network, 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 upon the setting of the CursorSetProp() WHERETYPE property. Errors generated by changes made by another can be detected through the use of the AERROR() function. AERROR() creates an array containing five elements which are described in the table below

Element number Return Value Description
1 Numeric Contains the number of the error. Identical to the value returned by ERROR( ).
2 Character The text of the error message. Identical to the value returned by MESSAGE( ).
3 .NULL. However, if the error has an additional error parameter, contains the text of the error parameter. Identical to the value returned by SYS(2018).
4 .NULL. However, as appropriate, will contain the number of the work area in which the error occurred.
5 .NULL. However, if a trigger failed (error 1539), contains one of the following numeric values: 1–Insert trigger failed. 2–Update trigger failed. 3–Delete trigger failed.

TABLEREVERT()

If you want to revert, or reverse, changes made to the cursor, you can use the TABLEREVERT() function at either the row or table level. For example, if the user chooses a CANCEL command button to “undo” changes made to the current row, the associated CLICK event procedure would contain code similar to this:


=TABLEREVERT(.F.,”remotetable”)

If the user presses ESC or a QUIT command button, you could use the following code to revert changes made to all rows:

=TABLEREVERT(.T.,”remotetable”)

Remember that in order to revert changes made to the entire cursor, the buffering method has to be table level buffering rather than row level. Otherwise, changes made to prior rows will have already been committed when the user moved to another row.

If you are unfamiliar with the TABLEREVERT() function, which returns the number of rows for which changes were discarded, it is intended to discard changes made to a buffered row or a buffered table or cursor and restores the OLDVAL( ) data (the original value of a column which has been modified but not yet updated) for remote cursors and the current disk values for local tables and cursors. The table below summarizes the arguments for the TABLEREVERT() function:

Argument Value Description
AllRows .T. If table buffering is enabled, changes made to all rows are discarded in the table or cursor
.F. (default) If table buffering is enabled, only changes made to the current row in the table or cursor are discardedNote if row buffering is enabled, the value is ignored and changes made to the current row in the table or cursor are discarded
TableAlias Character Specifies the alias of the table or cursor in which the changes are discarded
WorkArea Numeric Specifies the work area of the table or cursor in which the changes are discarded

The TABLEREVERT( ) function cannot discard changes made to a table or cursor that does not have row or table buffering enabled. If TABLEREVERT( ) is issued and row or table buffering is not enabled, Visual FoxPro generates a “Command is illegal for this buffering mode” error message. Finally, changes are discarded in the table or cursor open in the currently selected work area if TABLEREVERT( ) is issued without the optional TableAlias or WorkArea arguments.
Note On a network, the data currently on disk may differ from the data on disk when the table was opened or the cursor was created. Other users on the network may have changed the data after the table was opened or the cursor was created.
Transactions

Having set up the buffering and enabling the local cursor(s) for updates, you may still need to wrap the changes in a transaction in order to revert changes to rows. The standard Visual FoxPro BEGIN/END TRANSACTION commands can only create transactions for local cursors, they do not prevent loss of data on the remote server.

Transactions are handled according to the Transactions property setting on the connection (refer to the table on SQLSetProp()). If the default setting (1) has been used, then Visual FoxPro will automatically wrap every transactionable command sent to the remote server (i.e. UPDATE, implicit buffering update, etc.)

If you have chosen to control transactions manually (Transactions property set to 2), then while FoxPro will automatically begin the transaction for you, you will have to use either SQLCommit() or SQLRollback() functions to end the transaction. A new transaction is automatically begun when the next transactionable command is issued after the prior transaction has either been committed or rolled back.

SQLCommit()

The SQLCommit( ) function commits a transaction. The only argument specifies the connection handle to the data source returned by SQLConnect( ).


?SQLCOMMIT(handel)

The SQLCommit( ) function returns true (.T.) if the transaction is successfully committed. Otherwise, it returns false (.F.). If SQLCommit( ) returns .F., you can use AERROR( ) to determine why the transaction could not be committed. When manual transactions are in effect (the SQLSetProp( ) Transactions property is set to manual), you can send multiple updates to remote tables and commit all the updates with SQLCommit( ).

SQLRollback()

The SQLRollBack( ) function cancels any changes made during the current transaction. As with SQLCommit(), the only argument specifies the connection handle to the data source returned by SQLConnect( ):


?SQLROLLBACK(handel)

The SQLRollBack( ) function returns true (.T.) if the transaction is successfully rolled back. Otherwise, the function returns false (.F.). If SQLRollBack( ) returns .F., you can use AERROR( ) to determine why the transaction could not be rolled back. When manual transactions are in effect (the SQLSetProp( ) transaction property is set to manual), you can send multiple updates to remote tables. The updates can all be rolled back with SQLRollBack( ).

Common Errors

As a reward for sitting through the entire session (or reading through all these pages of notes), here is a brief listing of some of the Visual FoxPro or ODBC error messages that apply specifically to remote updates.

Error Message Description Source of Error Possible Resolution
No remote table(s) specified in the Tables property of this cursor The cursor’s Tables property contains no remote table names. None of the tables referenced in the query are updatable Visual FoxPro Set the Tables property of the CursorSetProp() function to specify at least one updatable table for the cursor
No key column(s) specified for the remote table tablename. Use KeyFields cursor property The primary key for the remote result set specified in the error message is not included in the KeyFields property for the cursor. A primary key for each table being updated is required Visual FoxPro Set the KeyFields property of the CursorSetProp() function to specify the primary key(s) for the remote table(s)
No default remote table for synonym column columnname. Use UpdatableFieldList property for this column The local column specified in the error message represents a duplicate, or ambiguous, remote column name Visual FoxPro Alias the column names in the original function (i.e. SQLExec()). Then set the UpdatableFieldList property of the CursorSetProp() function to specify the remote table and column to which the local column should be mapped upon update
ODBC invalid object Wrong name for the remote object (i.e. table, column, etc.) ODBC Check accuracy of object name. The ODBC driver cannot find the target object because it doesn’t exist as named

Note Local Visual FoxPro column names are validated by Visual FoxPro. Remote table and column names are validated by the remote server