Now that we are connected to the back end, let us look at how to access those back end tables. Visual FoxPro allows the developer to move selected data from the server to a local FoxPro cursor. Looking back to our first figure we see that with the client-server solution the client, which is our FoxPro front end, sends a command to the server. The server then processes that command and sends the results back to the client. Usually the command that is sent is a Transact SQL Select statement and the result set is a FoxPro cursor.
As we look at these different query options, let's remember that the trick to optimizing remote queries is to make sure the result set is relatively small. This is somewhat mitigated by the new fetch capabilities of FoxPro, which we will discuss later.
There are two methods of querying the back end tables. One way involves creating a Remote View that is part of the database container. The other way makes use of SPT by employing the SQLEXEC() function. Each of these methods require a SQL Select statement and return a local FoxPro cursor as a result. The methods of implementation, however, are quite a bit different.
Remote views allow the developer to attach remote tables to the database. These views can then be accessed with a standard FoxPro USE command and will accept all standard FoxPro commands such as SKIP, GOTO, REPLACE, LOCATE, etc. The developer should be aware that when a view is used, all of the records associated with the view are transferred from the server to the client in the form of a cursor. Therefore, with large tables it doesn't make sense to select all of the records into a view. This would simply be copying all of the records from the server each time you used the view. Remember that optimization of remote queries, relates to the number of records returned from a query.
A Remote View can be created visually using the View Designer. This tool can be accessed visually through the New File dialog, Project Manager or Database Designer toolbar. Programmatically it can be accessed by issuing a CREATE SQL VIEW REMOTE command.
The same remote connection can be performed programmatically. There are multiple options available when creating the view programmatically, as can be seen with the following code.
*** Open the Database first to hold the connection OPEN DATABASE cstest *** Create remote view and use the table CREATE SQL VIEW jfh REMOTE AS select * from authors USE jfh
This code will ask you to select a connection or data source. If you select a connection, that connection will be saved with the view. Selecting a data source will cause the SQL Server Login dialog to be displayed every time you use the table.
*** Create view using a named connection with shared option CREATE SQL VIEW jfh CONNECTION jfh SHARED AS select * from authors
This view demonstrates the use of a shared named connection. When this view is used, it will use the named connection properties and attempt to use an existing connection. If there is no existing connection, a new connection will be created that can then be shared with other views. This shared option is important since each connection takes up resources on the server and there are a finite number of connections available on the server.
As stated several times, you need to limit the number of rows returned by your view in order to optimize your application. This is accomplished by adding a filter criteria to your SQL Select statement when you create the view. Visual FoxPro has added a powerful new capability to aid in this effort with the "?" operand. When you start a reference for a criteria with a "?", FoxPro will automatically enter the correct delimiters if required. This greatly simplifies using memory variables or fields in select statements. In this case a picture is really worth a thousand words, so follow the code sample stated below.
*** Create view with parameter criteria
cLname = 'Green%'
CREATE SQL VIEW jfh CONNECTION jfh SHARED AS select * from authors;
where authors.au_lname like ?cLname
USE jfh
The result of the above code is to have a local cursor with rows related to authors with a last name starting with Green. By changing the parameter and refreshing the view, different authors are returned to the local FoxPro cursor.
*** Change cLname and refresh view
cLname = 'R%'
=REQUERY(CURSORGETPROP('ConnectHandle'))
If cLname is not defined at the time that you use a view, a parameter dialog will appear requesting an end user input for the value.
SPT queries are created using the SQLEXEC() function. Just like a Remote View, these queries return a local FoxPro cursor based upon a Transact-SQL select statement. To demonstrate this, I will use the above parameterized query that created a view.
*** Parameterized SPT query
cLname = 'Green%'
nCSHandle = SQLCONNECT('jfh')
nSPT = SQLEXEC(nCSHandle, 'select * from authors;
where authors.au_lname like ?cLname', 'jfh')
Note that the nCSHandle value is used in the SQLEXEC() function. The end result of this code is to have a local cursor aliased as 'jfh'. This is exactly what happens when the above view example is used. There is no appreciable speed difference in using a view as opposed to executing SPT. The SPT simply gives a lower level of control to the developer. The most popular use for SPT is to execute stored procedures on the server as opposed to executing select statements.