Tutorial

Tutorial

2.            Tutorial

This tutorial section introduces client-server database programming with Visual FoxPro with a minimum of fuss and bother. We explore the major concepts and get to know our way around the PUBS database that ships with SQL Server.

First let us get to grips with some concepts:

·                  FoxPro uses the Windows ODBC drivers to communicate with the client-server database. You will need to install the appropriate driver on each workstation.

·                  Remote Views can be created in a database container to reference a client server database query. The retrieved data is represented as a FoxPro cursor.

·                  Cursors can now (since Visual FoxPro 8.0) be created with the object oriented CursorAdapter base class.

·                  Cursors employ table buffering to control the timing of updates sent to the database server.

·                  Direct communication with the database server is possible using SQL pass through commands.

Create a Connection

A connection can be created with reference to an ODBC datasource defined on the workstation or directly using a connection string. The following example connects to the PUBS database on a locally installed copy of SQL Server using the system administrator username and password.

lnHandle = SQLSTRINGCONNECT(;

    'DRIVER=SQL Server;SERVER=(local);UID=sa;PWD=;DATABASE=pubs')

 

The SQLSTRINGCONNECT function will return a positive integer referring to the connection handle if successful. Incorrect passwords will usually result in a user prompt while other errors will be trapped by the AERROR function.

Alternatively, if you have defined an ODBC driver for the workstation you may access the datasource definition directly with the SQLCONNECT command:

lnHandle = SQLCONNECT('dsnPubs','sa','')

You can test a connection by using SQLTABLES to get a list of the defined tables returned from the database server into a cursor. The PUBS database should contains several tables including AUTHORS and PUBLISHERS.

? SQLTABLES(lnHandle,'table')

BROWSE

We can also execute database commands directly against the database server. The following command will return a read-only copy of the AUTHORS table into a local cursor called fred:

? SQLEXEC(lnHandle, 'select * from authors', 'fred' )

SQLEXEC returns a 1 if completed successfully, 0 if still processing asynchronously, and –1 if there is an error.

Create a Remote View

A remote view can be created programmatically but requires a database container to be open for update. Most valid SQL SELECT clauses can be used to define a remote view.

CREATE DATABASE dbctutorial

CREATE CONNECTION conpubs ;

    CONNSTRING 'DRIVER=SQL Server;SERVER=(local);UID=sa;PWD=;DATABASE=pubs'

CREATE SQL VIEW vueauthor REMOTE ;

    CONNECTION conpubs SHARED AS select * from authors

SELECT 0

USE dbctutorial!vueauthor

BROWSE

Updating Data

The default definition for a remote view is to create read only data. We must change the view definition by setting the SENDUPDATES parameter to true either in code as shown below or by using the database designer:

? DBSETPROP( 'vueauthor',"VIEW","SENDUPDATES",.t.)

Now we can browse and alter data. Notice the error messages from the server if you attempt to set an illegal value (for example by entering letters into the ZIP field).

USE dbctutorial!vueauthor

BROWSE

Table Buffering

Table buffering can be useful if you want to update several records in a cursor and control the moment they are updated onto the database. Table buffering is set on an already open cursor using the CURSORSETPROP function below:

SELECT vueauthor

? CURSORSETPROP("Buffering",5)

You can now make any changes you require without sending anything to the server. You can cancel all the changes as shown below:

REPLACE ALL au_fname WITH 'fred'

? TABLEREVERT(.t.)

Or you use the TABLEUPDATE function to update onto the database server:

? TABLEUPDATE(.t., .t.)

Using Parameterised Remote Views

One of the primary design criteria for a scalable client-server application is that only small amounts of data are sent to the workstation at any one time. Parameterised Views allow FoxPro variables to be defined as part of the selection clause so that only the required records are retrieved.

In the example below,  a parameterised view is created on the authors table that retrieves only the authors that live in a single state:

MODIFY DATABASE dbctutorial

CREATE SQL VIEW vuestateauthors REMOTE CONNECTION conpubs SHARE as select * from authors where state = ?lcstate

? DBSETPROP('vuestateauthors','view','sendupdates',.t.)

The parameterised view is first opened with the NODATA clause so that no data is retrieved from the server. The required value for the State is specified in the appropriate variable and selecting the empty cursor and issuing a REQUERY() command will interrogate the server and retrieve only the required records.

USE vuestateauthors NODATA

lcstate ='CA'

REQUERY()

lcstate ='TX'

REQUERY()

Executing a Stored Procedure

Another great advantage of a client-server database is that programs, known as stored procedures, can be defined to run on the server minimising the traffic passing to and fro between server and workstation.

The following example shows how to call the BYROYALTY stored procedure which returns a cursor of author identifiers who receive a particular percentage royalty:

OPEN DATABASE dbctutorial

lnhandle = SQLCONNECT( 'conpubs')

? SQLEXEC(lnhandle,'exec byroyalty 40','fred')

Creating a client server Form

FoxPro forms behave in a similar fashion with client-server remote views as they do with local views and local tables. Care is required to manage the retrieval of the data and updating using table buffering but, otherwise, the behaviours of the cursor is the same in all three environments.

We shall create a form that prompts the user to enter the state required and then retrieves the corresponding authors into a grid for updating. The VUESTATEAUTHORS parameterised view is employed for this form, which will operate with record level buffering on the cursor.

First create the form and right click to add the VUESTATEAUTHORS remote view into the data environment. Select the properties for the cursor and set the NODATAONLOAD property to true so that no data is retrieved from the server when the form is opened. Also specify the BUFFERMODEOVERRIDE property to 3 for record level buffering.

Now drag the image of the cursor from the data environment onto the form to create a grid control.

Finally, create a textbox control that allows for a two character string to be entered for the user to specify the state required and add the following code the refresh the parameterised view and the grid:

lcstate = THIS.Value

SELECT vuestateauthors

=REQUERY()

THIS.Parent.GRdVuestateauthors.Refresh

Run the form and enter CA into the textbox. The grid should refresh and allow you to update the author records. Try entering an invalid zip code to check that a response is returned from the server as you move to the next record in the grid.

Create a Cursor with the CursorAdapter Object

CursorAdapter classes are the new object oriented way to access both local and remote client server databases as well as access data using ADO RecordSets and XML.

An easy way to build a CursorAdapter object directly into a form is to create a new form and richt-click to view the data environment. Ignore the initial prompt for a table and right-click to add a CursorAdapter. Right-click on the newly added CursorAdapter and select the builder.

Name the alias and select the ODBC datasource type and define the connection characteristics. An existing workstation datasource is used below and the password and username specified.

Click the Data Access tab and enter the following SELECT command:

SELECT * FROM authors

Now press the BUILD button just above the select command to select the fields required and automatically update the SCHEMA values. Select all the fields in the AUTHORS table:

Now select the AUTO-UPDATE tab to spacify the SENDUPDATES and AUTOUPDATE check boxes and the updatable fields. Specify the AU_ID field as the primary key.

The CursorAdapter object is now complete and will create a cursor that updates the table on the client server database. Close the builder down and drag the fields onto the form to create a form in the usual way.

Summary

We have covered most of the concepts involved in creating client-server applications with Visual FoxPro in this short tutorial:

·                  A connection that uses the Windows ODBC/OLEDB middleware to communicate with the client-server database must be defined before FoxPro can communicate with the server.

·                  Once the connection has been made, commands can be executed directly against the database server to retrieve data or to execute stored procedures.

·                  A Remote View can be defined in a Database Container to coordinate the retrieval of data from the server into a local cursor. The SENDUPDATES property must be set to allow updates back onto the server controlled by the table buffering properties of the cursor.

·                  CursorAdapter objects offer an object oriented way of accessing data stored locally or remotely or via ADO or XML with a single programmable object. CursorAdapters can be defined with a builder tool and properties stored persistantly in a Visual Class Library.

·                  Parameterised views are used to control the retrieval of small sets of data as required by the application. The art of client-server application design is to break up access to the data into small queries that will allow the application to scale easily to hundreds of users.

·                  Forms are created in a similar fashion to standard FoxPro data access with attention required for the specification of parameterised views and to the control of the table buffering.

The remainder of the handbook describes each of these areas in detail and offers instruction in the fine tuning and optimisation of FoxPro access to database server.

22/07/2017 09:45:16