Setting up forms based upon Remote Views (often used for Client/Server) can be tricky. How do you handle relations; When and how do you do field-level validations; Record-level validations? Let Eldor Gemst show you what you need to successfully use remote views with your forms. This is an intermediate-level session.
Unlike my previous session, „Introduction to Remote Views“, even though here we will be discussing how to best use Remote Views with VFP Forms, we will also touch on the use of SQL Pass-Through techniques in order to gain the most power and to offer solutions to some common problems.
Please see the notes for my other session, E-VIEW – „Introduction to Remote Views“ for information about the sample data and how to use it. I have provided data that can be used to create these examples, for SQL Server users as well as for VFP. The sample code has a README.TXT file explaining how to set things up.
Please note that the focus of this session is to show some of the techniques of using Views, specifically Remote Views, in forms. In the interest of simplicity, to illustrate the points we are trying to convey as directly as possible, some of our examples have used VFP’s base classes directly. In the real world, we should be create our own classes and use those instead. Our use of custom classes for this section is very limited.
When using Views you must have Visual FoxPro’s Buffering modes turned on. And in order to use any buffering modes, you need to SET MULTILOCKS ON (which is not the VFP default). This should be done in your main setup program for your application.
Views will automatically have Optimistic Row Buffering set for them, and you cannot set Pessimistic Buffering even if you wanted to. Pessimistic Buffering, would of course, require that we lock the record while the edit is taking place and this just cannot be done when using Views. Our choice then is whether to use Optimistic Row (the default) or Optimistic Table. I prefer to always have Optimistic Table Buffering, mainly so that I don’t have to remember or guess what I used (guess I’m getting old) and then I just use GETNEXTMODIFIED() to find the next record with pending changes. It makes my life simpler to only have to deal with one type of buffering.
For these examples, we will use Row Buffering for Parent data and Table Buffering for Child data. We also use Private Datasessions.
Several approaches can be used for relations, depending on the needs of the form (or the application). One might be to create a Remote View of each table in the foreign system and then to relate them together in the form after having created needed indexes. Did you know that you can create indexes on Views? The trick here is that every time the view is opened, the index needs to be recreated, and every time the view is closed, it disappears. The best place to create those indexes is in the form’s LOAD event.
The second, and more common approach, is to use Parameterized Views for the child data. Here’s how to set this up…
This example (TEST1.SCX) will use „rv_Friends“ for the parent and „rv_CardsParm“ for the child. The child data will be presented in a grid. After creating the rv_CardsParm view (it’s included in the source code for this session) with a parameter on the foreign key (Friend_id) called liFriendID, we add both views to our form. In the cursor object representing the rv_CardsParm view, make sure that the NODATAONLOAD property is set to True, otherwise when everything initializes, the view will try to get data and since it won’t yet know which Friend we’re interested in, we’re going to get the ugly question asking us to enter the view parameter:
Normally we would not want that question to appear. Setting NODATAONLOAD to True makes the View open without it trying to get data. This corresponds to the NODATA keyword when opening the View by hand, as in „USE rv_CardsParm NODATA“.
Let’s now add a custom method to the form to requery the child view. Call it „ChildRequery“. The code should say:
LiFriendID = rv_friends.Friend_id
In the form’s INIT event, we’ll need to specifically REQUERY() our child view, and again whenever we change the record pointer in the parent (Friends) view. In the INIT all we need to do is call our new custom ChildRequery() method. When we move the record pointer, our navigation code already calls THISFORM.Refresh() so the easiest way to get the child data to refresh is to call the ChildRequery() method from the form’s Refresh method.
Notice that some of the data above is displaying nulls. You need to know if nulls are allowed in the C/S data and make a decision about how they should display if they are. This tip really has little to do with Remote Views, but it might save you some frustration. The textboxes in the grid columns have a NULLDISPLAY property (there is also a system-wide SET NULLDISPLAY TO command) and if you set the numeric field „GiftValue“ for example to display nulls as 0.00, you also need to set the column’s SPARSE property to False. Otherwise the null display formatting you apply will only show up when the user lands the cursor into that particular field.
The real problem with our Test1 form is that we are looking at this as we would with local VFP data. With Client/Server, there are several issues to address:
First, the above doesn’t follow the C/S paradigm in that sequential navigation really is frowned upon. Don’t forget that the number of records in the system might be enormous and you should never let your user view all the data. Insist on some sort of selection criteria from the user first. A common approach here might be to have a pageframe with a selection dialog on the first page, and editing details for the record on the next page. If the volume of data is not too great, you could even display a grid of the data in the first page of the pageframe and once your user navigated to the record he/she wanted to edit, the user would click on the next page and be presented with the full details of the selected record to edit.
Second, once you save an edit (or an add) with TABLEUPDATE(), your View will not show the new data until you REQUERY() it. And when you REQUERY(), you will no longer be on the newly added record, as it’s just like what happens when you open a table. You’ll be at the first record in the current order. If you are adding records and rely on something like the SQL Server IDENTITY column, I think you will have to live with this. However if you are manually obtaining the next available key value, then you could store it into a custom form property, and after the REQUERY() call, you could return to the newly added entry.
Doing all this with one view is just plain hard. It’s much better to use two views – one view which is read-only for the selection process (in a grid or selection-criteria page or form) and another updateable view for the editing. That second view should be parameterized and should only bring down the one record that is to be edited.
Create two views: rv_FriendsAll to get the complete listing of friends. This view will not be updateable. Rv_FriendOne will be a Parameterized View getting us just one Friend, and this view will be updateable. Use the Advanced Options from the Query menu to set Share Connection and turn off Fetch Memo for performance. Create a new form – frmTest2 – based on a custom form class called frmBase. (It has a couple of additional properties defined.) Add both those views to the data environment of the form. For the rv_FriendOne view, don’t forget to set the NODATAONLOAD property to True.
Create a PageFrame on the form and on the first page (the Selection page) drop a grid of the rv_FriendsAll view. On the second page (the Entry page) place the fields you want from the rv_FriendOne view. Note that for our example, we are using a custom textbox class called txtBase. It includes code in the InteractiveChange Event to set the form’s „isEditing“ property to True once an edit starts. It also calls a custom method of the form (ButtonRefresh()) to refresh any buttons once editing starts. Note that the InteractiveChange Event would fire over and over as editing continues, so we wrap the code with a check to see if the form is already in edit mode, and if so, we don’t do anything. In the Page2 Activate event, place the following code:
VpFriendID = rv_FriendsAll.Friend_id
The „vpFriendID“ variable is the parameter for the View. Instead of calling THISFORM.Refresh() when it’s not necessary, we simply refresh the page we are on. (With large PageFrames, often there can be quite a performance hit if all pages with all their controls are refreshed unnecessarily.)
Just for fun, check out the form’s LOAD Event, where we create two indexes on our read-only View.
Drop two commandbuttons onto Page 2 of the PageFrame. One will be the Cancel button and the other the Save button. With either button we want to move the user back to the Page 1. (Personal decision.) The code for the cmdCancel Click() event is:
.isEditing = .F.
.PageFrame1.Page1.Enabled = .T.
Our Save button code is more complicated, even though we’re taking a very simple approach to saving the changes. We call TABLEUPDATE() and if it returns a False, we were unable to save successfully. Depending on how we set up our Update Criteria for the View, this could mean that either someone else changed the same field we did while we were editing, or that someone changed something else. My normal settings for Remote Views would be the „SQL WHERE clause includes“ the „Key and Modified fields“ setting. Here is the code:
IF NOT TABLEUPDATE(.F.,.F.,“rv_FriendOne“)
WAIT WINDOW „Cannot save your changes!“ TIMEOUT 2
.isEditing = .F.
.PageFrame1.Page1.Enabled = .T.
.PageFrame1.ActivePage = 1
The Save button also has Refresh code which enables or disables it depending on whether or not we are in editing mode.
At this point the form should work, but when we save the edited record and REQUERY() the main read-only View, we will be positioned at the first entry in the index. We can make this a little bit more friendly by trying to return to the correct record after the edit. We will do this in the Page1 Activate Event. There is also a little bug in the GRID which causes the row highlight to get out of synch with the data under certain conditions and we have a little work-around there as well. (Thanks to Jim Booth for helping pin down the problem.) Here’s the code for Page1’s Activate Event:
LiFriend = rv_FriendOne.Friend_ID
IF liFriend > 0
we’ve got a valid friend to return to
SET ORDER TO TAG friend_id && lucky we’ve got the index
SET ORDER TO TAG lastname
THIS.Grid1.Column1.SetFocus() && workaround for grid highlight problem
Our form is rather simple, but it illustrates some important concepts. Here is what Page 1 looks like:
Page 2 (before starting to edit) looks like this:
Once editing starts, the Save button becomes enabled; the Quit button and Page 1 become disabled. Clicking the Cancel or the Save button will either cancel or save the edit and then move the user back to Page 1.
To handle field-level validations we could send the new data to the C/S system and have the validation handled there, returning an error code (or a False for a TABLEUPDATE() call). I personally don’t like this approach, although it is in common use today. I don’t like the idea of sending anything to the server until I know it should be accepted. Field-Level validations are often very easy to perform in VFP and the advantage of this (in my opinion) is that we minimize network traffic which often gives us considerably better performance.
Our example here will go out to the C/S system, get the list of acceptable States from the States table and use that list to validate the State entry in the Friends table. Of course this approach needs to consider the possibility that new entries might be made to the States table by another user, so in the scenario where the data being validated against is fairly static, this approach works well.
We will use this technique to validate the State against a State table on the C/S system, but rather than get the States data over and over for every record, we’ll create a cursor of the values when we launch our form. We will use SQL Pass-Through for this (to show how it’s done) although we could do it with a regular Remote View. Another idea would be to create a Stored Procedure on the C/S system to return only the list of valid states.
In the Test3 form, we will create a cursor of the valid states in the Init event of the form. The decision we need to make is about the connection we will use. Our existing connection (conFriends) was created as an Asynchronous connection, meaning that control is returned to the application immediately – perhaps even before the statement has finished running. Generally speaking we like Asynchronous mode, as it gives better performance. The problem is that with SQL Pass-Through, the data may not yet have been retrieved before we continue with the rest of the program, and then we’d have more housekeeping to do. We’d have to check periodically to see if the statement had finished processing or if something was going wrong. We need to look at the data involved, (how much data) and the number of users and the number of connections we’re using and are licensed to use. We have several options: 1) Share the existing Asynchronous connection and write some more code, 2) Establish the connection, change it to Synchronous, execute the Pass-Through code and change the connection back to Asynchronous, or 3) Establish a new connection just for this purpose, and leave it at the default of Synchronous.
My own vote in this case is #3. There may be other users also establishing connections, and I wouldn’t like to change the connection type for them. Also in this case, there is very little data to retrieve and it is done only one time as the form initializes and then the connection is immediately closed. So I’ve created a new connection called „conFriendsSynch“ which we’ll use just for this purpose.
Here is the code from the Test3 form’s INIT:
LOCAL lnHandle, lnExec
LnHandle = SQLCONNECT(‘conFriendsSynch’)
IF lnHandle < 0
WAIT WINDOW „Connection to conFriendsSynch could not be established!“
* we’ve got our connection, now let’s get the data…
lnExec = SQLEXEC(lnHandle, ‘SELECT state, name FROM tblStates’, ‘curStates’)
IF lnExec < 0
WAIT WINDOW „Problem executing SQL Pass-Through code!“
*we’re outta here!
Now that we’ve got a cursor of valid states, we can add the States field to Page2 of the form and use a combobox fed from this cursor. What we’ve gained is the ability to validate the data locally, before sending it to the back-end. When we do send it, we know just about for sure that it will be accepted. Depending on the load, this technique can result in nice performance gains. Here’s what it looks like:
The same SQL Pass-Through can be used to execute any stored procedures on the C/S system, even if VFP natively doesn’t understand the commands.
All we need to do to our Test2 form (naming it Test4 along the way) is to add two more command buttons to Page 2 of the PageFrame – one for Delete and the other for Add. Here’s the code for the Delete button:
IF MESSAGEBOX(„Are you sure you want to permanently delete this record?“, ;
4+32+256, „Delete Request“) = 6
IF NOT TABLEUPDATE(.F., .F., „rv_FriendOne“)
WAIT WINDOW „Cannot Delete this record!“ TIMEOUT 2
.PageFrame1.ActivePage = 1
And the code for our Add button is even simpler:
THISFORM.isEditing = .T.
The only remaining change is a small addition to the code in our form’s custom ButtonRefresh method. Right after the last ENDIF and right before the ENDWITH, add the following two lines of code (it’s all on the source code disk anyway):
Our new form’s Page 2 now looks like this:
Both the FoxPro CodeBook (either Visual FoxExpress from F1 Technologies, http://www.fltech.com, or CodeBook for Mere Mortals from Oak Leaf Enterprises Solution Design, Inc. http://www.oaksleafsd.com) and MaxFrame Professional from GE Capital Consulting http://www.gectms.ge.com, offer beautifully complete implementations of using Remote Views (or Local Views) in their frameworks. There is obviously a lot involved in producing an industrial-strength solution, and I highly recommend both these products to developers looking for the most return with the least effort.
These sessions are really a „work in progress“ and you might want to check on my web site periodically to get the latest version of this article and source code. There will be a hidden page (you need to know it’s there to get to it) from where you can download the latest versions. It will be at http://www.elgem.com/remote.