CusorAdapter Class

CusorAdapter Class

5.  CursorAdapter Class

The CursorAdapter class is new for Visual FoxPro 8.0 and provides an object-oriented base class for creating cursors that control access to data.

CursorAdapters work well with various different data sources:

·                  Data stored in native FoxPro format on your local disk drive or local area network servers.

·                  Client-server data stored in a central database management system accessed using ODBC drivers.

·                  ADO recordsets created on the workstation or by a middle tier business component server.

·                  XML documents.

CursorAdapters can be created programmatically or using the CursorAdapter builder that forms part of the visual design tools for the DataEnvironment of a Form. Third party tools such as the cabuilder from www.mctweedle.com build CursorAdapter class libraries directly from existing local or remote databases.

There are several advantages of using CursorAdapter classes:

·                  Object oriented inheritance allows a base class to be defined with application specific properties and methods.

·                  CursorAdapters are suitable for implementing systems that need to operate with either local or remote data according to the installation.

·                  CursorAdapter definitions can be created and stored in a visual class library and can be added into a DataEnvironment for a Form in a similar manner to local tables or views defined in a database container.

Some disadvantages include:

·                  CursorAdapters are objects and the object variable needs to remain in scope for the data to be available.

·                  Visual class libraries have a limit fo 255 characters for properties and these properties often need to be defined in a method of the class.

·                  Views in the database container have additional properties for the individual fields defined for the cursor.

·                  A cursor created by a CursorAdapter class is instatiated in program code instead of with the USE command.

CursorAdapters combine many of the best qualities of views defined in a database container with the flexibility of programmatic control and object oriented inheritance. Storing CursorAdapter definitions within a Visual Class Library in combinations with the builder tool allows visual design and persistance of the definitions (although this needs some improvement). The main benefit however is a single object oriented technique for accessing data from a variety of data sources including local tables, client-server tables, ADO recordset objects and XML documents.

CursorAdapter

A CursorAdapter uses a connection to communicate with a client server database when accessing data through with ODBC. A SQLCONNECT or SQLSTRINGCONNECT command is issued to get a connection handle and the DATASOURCETYPE and DATASOURCE properties defined as shown below.

The SELECTCMD property is given a command that is executed on the server and a local cursor is created from the server data when the CURSORFILL method is called.

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

loPubs = CREATEOBJECT('cursoradapter')

loPubs.DATASOURCETYPE ='ODBC'

loPubs.DATASOURCE = lnHandle

lopubs.alias = 'caAuthors'

loPubs.SELECTCMD=[select * from authors]

IF loPubs.CURSORFILL()

   BROWSE

   ELSE

   ? 'Error'

ENDIF

This cursor will be closed as the program ends and the variable holding a reference to the CursorAdapter goes out of scope.

 An identical procedure is followed for local FoxPro data where the DATASOURCE is blank and the DATASOURCE type is set to 'NATIVE'.

Examples of CursorAdapter use with ADO and XML are shown later in this document.

Using parameters to filter data

Accessing client server data without specifying a filter is expensive as all the data must be retrieved from the server into a cursor on the local machine. A paramerised query may be specified in the SELECTCMD property to specify a selection of data.

The following example shows a CursorAdapter object retrieving an empty cursor by specifying the NODATA property when filling the cursor with the CURSORFILL command. This adds a WHERE 1=2 clause onto the SELECT command and returns an empty cursor with zero records.

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

loPubs = CREATEOBJECT('cursoradapter')

loPubs.DATASOURCETYPE ='ODBC'

loPubs.DATASOURCE = lnHandle

loPubs.ALIAS = 'caAuthors'

loPubs.SELECTCMD=[select * from authors where state=?lcState]

IF loPubs.CURSORFILL(.F.,.T.)

   BROWSE TITLE 'NODATA'

ELSE

   ? 'Error'

ENDIF

An empty cursor might typically be created when initially running a form until the user can be prompted for some selection criteria. At this stage the parameters can be set and the CURSORFILL command reissued to populate the cursor as shown below. This process can be repeated as often as required.

lcState = 'TX'

IF loPubs.CURSORFILL(.F.,.F.)

   BROWSE TITLE [state=TX]

ELSE

   ? 'Error'

ENDIF

The SELECTCMD property of the CursorAdapter can be updated as required. This removes a major limitation of database container views in that a complex where clause can be specified for a cursor at run time.

lcWhere = [WHERE state='CA' AND contract=1]

lcSQL = [SELECT * FROM authors ] & lcWhere

loPubs.SELECTCMD = lcSQL

IF loPubs.CURSORFILL(.F.,.F.)

   BROWSE TITLE lcWhere

ELSE

   ? 'Error'

ENDIF

It seems that the SELECTCMD property for a CursorAdapter can contain any command that can be executed against the server. The following example executes a paramerised stored procedure on the server and returns the results as a cursor:

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

loPubs = CREATEOBJECT('cursoradapter')

loPubs.DATASOURCETYPE ='ODBC'

loPubs.DATASOURCE = lnHandle

lopubs.alias = 'caRoyalty'

lnPerCent = 40

loPubs.SELECTCMD=[exec byroyalty ?lnPerCent]

IF loPubs.CURSORFILL()

   BROWSE

   ELSE

   ? 'Error'

ENDIF

Explain cursorschema here….

Updating data

The process for making a cursor updatable involves specifying properties to allow the system to automatically generate commands to execute on the server to update the changed records in the cursor.

The required properties are the same properties used to make a cursor created with SQL passthrough updatable and are the same as those set against a cursor created by a remote view:

·                  TABLES contains a list of the tables on the server to be updated.

·                  KEYFIELDLIST is a list of the fields that form the primary key of the tables.

·                  UPDATEABLEFIELDLIST is a list of the cursor fields that are updatable. Changes to the remaining fields are ignored.

·                  UPDATENAMELIST is a translation from local cursor field names to the field names on the server with the correct table prefix.

The primary key fields must be specified in the UPDATENAMELIST to allow the update commands to be automatically created by the system. They do not need to be updatable to update records or if the key values are automatically generated on the server when adding a new record.

The following example allows updates to occurs only on the AU_LNAME and AU_FNAME fields of the AUTHORS table:

Example here…

 

The ALLOWUPDATE, ALLOWINSERT, ALLOWDELETE and SENDUPDATES properties are true as the default settings for each CursorAdapter object. These values can be changed to restrict the apporpriate data modification operation.

The WHERETYPE property can also be set to specify the type of update command that is applied when records are changed. A good default value to chose is to set Key and Modified fields so that the system checks any modifed fielda for changes by other users but allows two users to change the same record provided they change different fields.

UPDATETYPE by default notifies the system to automatically generate a single update command to update records on the server. Change this setting to generate separate DELETE and UPDATE commands if required by your database server.

CONVERSIONFUNC is an iteresting property allowing local field values to be converted with a FoxPro function immediately prior to updating the data on the server. This is particularly useful for data stored as variable length character strings on the server but recognised as fixed length character strings in the local cursor. Specifiyng the RTRIM conversion function for each required field ensures that spaces are added onto the end of the character values in the server.

Example here…

Adding spaces onto variable length character fields may cause unpredicatable results when selecting records from the server which may now expect the correct number of trailing spaces when comparing values.

FoxPro automatically generates the update commands for any changes in the local cursor and controls their execution against the server system. This functionality works perfectly for both local and remote client server tables using ODBC. Properties such as UPDATECOMMAND, UPDATEDATASOURCE, and UPDATEDATASOURCETYPE allow detailed specification of update, insert, or delete commands if required.

CursorAdapter Event Model

CursorAdapter cursors have an event model that allows methods to be defined before and after major events such as filling a cursor or modifying or inserting data.

These events can be particularly useful for definging the equivalent of database validation and triggers in an object oriented fashion. Although is it perhaps better to exncapsulate this functionality on the database server, this approach might be useful if defining data validation rules on the client where a variety of database servers are used some of which may not allow data constraints or triggers to be specified.

The following code specifies a an updatable CursorAdapter class programmetically and then saves the object into a Visual Class Library.

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

loPubs = CREATEOBJECT('cursoradapter')

loPubs.DATASOURCETYPE ='ODBC'

loPubs.DATASOURCE = lnHandle

loPubs.ALIAS = 'caAuthors'

loPubs.SELECTCMD=[select * from authors where state=?lcState]

lcState = 'TX'

IF NOT loPubs.CURSORFILL()

   ? 'Error'

ENDIF

 

loPubs.TABLES='authors'

loPubs.KEYFIELDLIST='au_id'

loPubs.UPDATABLEFIELDLIST='au_lname,au_fname'

* Primary key must be defined in updatenamelist

loPubs.UPDATENAMELIST = 'au_id authors.au_id, au_lname authors.au_lname, au_fname authors.au_fname'

 

loPubs.SAVEASCLASS('ca07','cauthors','authorsw - only fname and lname updatable')

The CursorAdapter object can subsequently be opened using the following code. Note the use of the AUTOPEN method instead of CURSORFILL. The method simply fills the cursor but is the method used by a Form DataEnvironment to open a CursorAdapter.

SET CLASSLIB TO ca07

lcState = 'TX'

loAuthors = CREATEOBJECT('cauthors')

loAuthors.AUTOOPEN

loAuthors.BUFFERMODEOVERRIDE= 3

BROWSE

? TABLEUPDATE(.t.,.t.)

A validation rule can easily be defined by modifying the BEFOREUPDATE  event method. The DODEFAULT command is issued to continue with the update or not if the current record transgresses the validation rule. The following example prevents BILL being entered as an author's first name:

* BEFOREUPDATE

LPARAMETERS cFldState, lForce, nUpdateType, cUpdateInsertCmd, cDeleteCmd

 

* Do not allow update if firstname is bill

LOCAL llFailUpdateRule

STORE .F. TO llFailUpdateRule

LOCAL lcFirstName

STORE SPACE(0) TO lcFirstName

LOCAL lcField

STORE SPACE(0) TO lcField

 

lcFirstName = EVALUATE(THIS.ALIAS+[.au_fname])

IF TYPE('lcFirstName')=='C'

   IF ALLTRIM(UPPER(lcFirstName))= 'BILL'

      llFailUpdateRule = .T.

      WAIT WINDOW 'Sorry. You cannot have BILL as the first name.'

   ENDIF

ENDIF

 

IF llFailUpdateRule

   RETURN .F.

ELSE

   RETURN DODEFAULT(cFldState, lForce, nUpdateType, cUpdateInsertCmd, cDeleteCmd)

ENDIF

The BREAKONERROR property defaults to allowing a CursorAdapter class to trap its own errors within an ERROR method. Set this property to FALSE to allow standard errors to be generated if there is a problem with your code.

Attaching an Existing Cursor

An existing cursor can be attached to a CursorAdapter object and the properties of the object manipulated as required. The following example shows a read-only cursor created with SQL pass-through attached to a CursorAdapter with the CURSORATTACH method and then specified as an updatable cursor.

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

lnExec = SQLEXEC( lnHandle, ;

  [SELECT * FROM authors],'sptAuthors')

 

loPubs = CREATEOBJECT('cursoradapter')

loPubs.CURSORATTACH('sptAuthors')

 

loPubs.DATASOURCETYPE ='ODBC'

loPubs.DATASOURCE = lnHandle

lopubs.Tables='authors'

lopubs.KeyFieldList='au_id'

lopubs.UpdatableFieldList='au_lname,au_fname'

* Primary key must be defined in updatenamelist

lopubs.UpdateNameList = 'au_id authors.au_id, au_lname authors.au_lname, au_fname authors.au_fname'

The CURSORDETACH method is used to detach a cursor from the CursorAdapter object. The object reference can then go out of scope but the cursor remains in the environment as a standard local cursor. The CursorAdapter properties will need to be specified again if the cursor is subsequently attached to a CursorAdapter object.

Using the CursorAdapter Builder

 

Building a CursorAdapter with cabuilder.prg

Using CursorAdapter Objects in a Form

 

27/05/2017 01:26:58