Robert Green
Microsoft Corporation
January 1997
Your good friends on the Visual FoxPro™ team at Microsoft spent a great deal of time to make Visual FoxPro a robust and powerful front-end for client/server applications. Remote views and SQL pass-through provide powerful tools to take advantage of SQL back-ends such as Oracle and SQL Server via ODBC (Open Database Connectivity).
One of the great truisms of application development is that there are many ways to do everything. One of the hardest things to do when building an application is to decide on an approach and to know if it is better than the other approaches. In client/server development this is compounded by the fact that you are dealing with two database engines, Visual FoxPro on the front-end and a very powerful SQL database engine on the back-end.
A companion article to this one, "Integrating SQL Server with Visual FoxPro," explores building two-tier client/server applications using SQL pass-through. In this article, an OLE server built with Visual FoxPro is used to create three-tier client/server applications. Adding a middle layer gives you an additional place to put code and perform validations.
The client/server application developer's dilemma lies in knowing how to split up the work between the front and back-ends. Visual FoxPro and SQL Server both have very powerful database engines. Both have powerful programming languages and are capable of validating data and enforcing business rules.
Lately there has been much talk in the industry about fat clients and thin clients and the effect they have on your client/server development efforts. As the available front-end tools have grown in power there has been a tendency to rely on them more. This does ease the burden on the back-end, but it can also lead to problems.
The first problem with loading up the front-end is that it requires a more powerful computer. Certainly if you are using Visual FoxPro as the front-end you would want at least a 486 with 12 MB of RAM. This issue is what spawned the phrase "fat client." The girth of the front-end is certainly a consideration if you are trying to choose which front-end to use. You would weigh the strengths and weaknesses and resource requirements of Visual FoxPro, Visual Basic, Microsoft® Access, Delphi, PowerBuilder, Java™, and all of the others. Java is exciting because it holds out the promise of a truly thin client that has enough power to be a robust front-end. Visual FoxPro 5.0 is significantly less fat than version 3.0.
The second problem with having the front-end do a lot of the work is that if your validations and business rules change you have to change the code in the front-end. This may or may not be a problem. Suppose you are developing the Visual FoxPro front-end and you also happen to be in charge of the SQL Server back-end. If you are the one who writes the validation and business rule code, you will presumably write it once, either in Visual FoxPro or as SQL Server stored procedures, or perhaps as some combination of the two. If some logic has to be changed and you are the one who changes it, how much of an issue is it where the code is located?
On the other hand, you may be writing the front-end and not have any say on the back-end. You may not be allowed to write your own Selects, Inserts, Updates, and Deletes. The keepers of the back-end may limit you to using only stored procedures. The beauty of that approach is that they are guaranteed that the front-end does things right. If your only access to the data is through the stored procedures, you would be forced to do things in the correct order and not leave out any steps. For instance, if you were deleting a member you could not forget to remove the member's reservations. If you were adding a new member you would not forget to put the new ID into the Adult table.
What if your front-end is just one of several front-ends all accessing the same back-end data? In that case you might want all logic to be on the back-end. That way, if any of it needs to change, it only needs to be changed in one place.
A recent advance in client/server development is the three-tier architecture scheme. Traditional client/server is two-tier, consisting of a client and a server. As discussed above, this can potentially lead to problems if too much work is concentrated in either the client or the server. The three-tier architecture introduces a middle layer, which serves to ease the processing burden of the two ends.
In a classic three-tier architecture, shown in Figure 1, each tier is responsible for providing a service. The client provides user services, consisting mainly of the user interface. The server provides data services, consisting of the data and the means of accessing and maintaining it. The middle layer provides business services, consisting of data validations and enforcement of business rules.
Figure 1. A layered application architecture
To fully realize the potential of this architecture, you should make the middle layer easily accessible to multiple front-ends. If it is enforcing your business rules, then all clients need to be able to talk to it. This is true whether the clients are written in Visual FoxPro, Visual Basic® or some other language.
The middle tier should be running on a separate machine. This allows it to be accessible to multiple clients and it provides for the maximum performance. Remember, the problem with fat clients is that the client machine is overburdened. In a two-tier architecture you might have to provide each of the clients with at least a 16 MB (or more) Pentium machine. In a three-tier architecture you might locate the middle layer on a 32 MB dual processor Pentium machine and be happy with 12 MB 486s as the clients.
There are at least a couple of ways you could set up the middle layer. One approach would be to use a SQL Server machine as both the middle layer and server. The approach taken in this session uses an OLE server created in Visual FoxPro as the middle layer. The OLE server is called from Visual FoxPro, or for that matter any OLE controller, using the CreateObject() function, in the same manner as you would call Excel or Word as OLE Automation servers.
If you don't yet know how to create an OLE server in Visual FoxPro, check out the Visual FoxPro Online Documentation (Chapter 16 of the Visual FoxPro Developers Guide).
The OLE server can be created as an InProc or OutOfProc server. In the Win32 world (Windows® 95 and Windows NT®), a process is a running application and can address up to 4 GB of memory, half of which is used to store the processes code and data and half of which is used by the system. An InProc server is compiled as a DLL and runs in the same address space as the process that called it. An OutOfProc server is compiled as an EXE and runs as a separate process. An InProc server offers better performance because inter-process communication is slower than intra-process communication. On the other hand, since an OutOfProc server runs in a separate process, you can take advantage of a multiprocessor environment.
The OLE server can also be run locally or remotely. Typically a remote OLE server is created as an OutOfProc server and sits on a powerful computer. That allows it to be accessed by any number of client machines. The Remote Automation Connection Manager can be used to configure the server and client machines to allow for remote automation.
The data used in this session's examples is from a SQL Server 6.5 based library application. The library wants to keep track of its members, its books and its loans. The library database schema is shown in Figure 2.
Figure 2. library database schema
One of the central tables in the Library application is the Member table, which contains one row for every member of the library. An interesting twist here is that juveniles can only be members of the library if they have a sponsoring adult. Since a juvenile lives, presumably, in the same place as the adult, there are separate tables for Adult and Juvenile. This saves disk space because all of a juvenile's address information is redundant once you know who the adult is. Also, the juvenile's expiration date is the same as the adult's. Further, you don't care about the adult's birth date, although you do care about a juvenile's birth date, but only because on their 18th birthday he or she becomes an adult (at least as far as the tables are concerned!).
The following code shows the SQL Server statements used to create the Member, Adult, and Juvenile tables:
CREATE TABLE member
( member_no member_no NOT NULL IDENTITY(1,1),
lastname shortstring NOT NULL ,
firstname shortstring NOT NULL ,
middleinitial letter NULL ,
photograph image NULL )
CREATE TABLE adult
( member_no member_no NOT NULL ,
street shortstring NOT NULL ,
city shortstring NOT NULL ,
state statecode NOT NULL ,
zip zipcode NOT NULL ,
phone_no phonenumber NULL ,
expr_date datetime NOT NULL )
CREATE TABLE juvenile
( member_no member_no NOT NULL ,
adult_member_no member_no NOT NULL ,
birth_date datetime NOT NULL )
The member_no field in the Member table is automatically generated by SQL Server when a new row is added. This field is an Identity column. The seed value of 1 and the increment value of 1 specify that the first row entered into the table should have a member_no of 1. For every row inserted after that, the member_no is incremented by 1. When adding a row, the client does not specify the member_no. SQL Server takes care of it automatically and the client can ask what number was used.
The member_no field in the Adult and Juvenile tables is not an Identity column. The value in that column has to match the member_no of the corresponding row in the Member table. When a new member is added to the library, a row is first added to the Member table. The SQL Server global variable @@Identity contains the automatically generated member_no. A row is then added to the Adult or Juvenile table and the value for the member_no will be whatever is in @@Identity.
In prior versions of SQL Server, referential integrity was enforced through the use of triggers, which is the same way Visual FoxPro enforces referential integrity. SQL Server 6.0 added declarative referential integrity, which allows you to define your RI rules as part of the data structure. The first step is to create a Primary Key constraint in each table, as shown in the following code:
ALTER TABLE member
ADD CONSTRAINT member_ident PRIMARY KEY CLUSTERED
(member_no)
ALTER TABLE adult
ADD CONSTRAINT adult_ident PRIMARY KEY CLUSTERED
(member_no)
ALTER TABLE juvenile
ADD CONSTRAINT juvenile_ident PRIMARY KEY CLUSTERED
(member_no)
The Primary Key constraint creates a Unique index, which enforces the uniqueness of the member_no. In the examples here a clustered index, which physically sorts the data, is created.
The second step in defining declarative referential integrity is to create Foreign Key constraints between related tables, as shown in the following code:
ALTER TABLE adult
ADD CONSTRAINT adult_member_link FOREIGN KEY (member_no)
REFERENCES member (member_no)
ALTER TABLE juvenile
ADD CONSTRAINT juvenile_member_link FOREIGN KEY
(member_no) REFERENCES member (member_no)
ALTER TABLE juvenile
ADD CONSTRAINT juvenile_adult_link FOREIGN KEY
(adult_member_no) REFERENCES adult (member_no)
The first Alter Table defines a relationship between the Member and Adult tables. This is a one-to-one relationship, although there is nothing in the code to indicate or enforce that aspect of the relationship. The second Alter Table defines a relationship between the Member and Juvenile tables. The final Alter Table defines a relationship between the Adult and Juvenile tables. This is a one-to-many relationship.
Be aware that SQL Server currently does not support cascading updates or deletes. If you wanted those you would use triggers instead of constraints.
The Library OLE Server uses SQL pass-through to talk to the SQL Server Library database. The server will contain methods to take actions such as retrieving information for a member, adding a new member, changing a member's information, and deleting a member. The server exposes a few properties, including properties to determine what errors have occurred.
The OLE Server project is the file LIBRARYSERVER.PJX. The project contains a custom class called Members. The class has been identified as OLE Public by checking the appropriate box in the Class Info dialog.
The methods and properties of the Members class, and hence the OLE Server, are listed in Table 1 and Table 2, respectively.
Table 1. Library OLE Server Methods
| Method | Visibility | Description |
| InitConnection | Public | Initiate a connection to SQL Server |
| CloseConnection | Public | Close the connection to SQL Server |
| GetMember | Public | Retrieve information for a member |
| AddMember | Public | Add a new member |
| UpdateMember | Public | Update a member's information |
| RemoveMember | Public | Remove a member |
| SetError | Protected | Store the SQL Server error |
| Convert | Protected | Convert values to strings |
Table 2. Library OLE Server Properties
| Property | Visibility | Description |
| nHandle | Public | Connection handle |
| NewID | Public | ID of newly added member |
| LastErrDesc | Public | Description of the last error that occurred |
In the Project Information dialog box, shown in Figure 3, the project has been given the name VFPLibraryOLEServer. This, combined with the class name Members, yields the registered OLE name of VFPLibraryOLEServer.Members. This can be found in the HKEY_CLASSES_ROOT hive in the Registry, as shown in Figure 4.
Figure 3. Libraryserver Project Information dialog box
The OLE server is automatically registered on the machine that built it. If you move it to another machine you can register it by running REGSVR32.EXE, found in the System directory under Windows 95 and in the System32 directory under Windows NT.
Figure 4. Windows Registry showing OLE server
To use the OLE server a client application would use something like the following line of code:
oLibrary = CreateObject("VFPLibraryOLEServer.Members")
When Visual FoxPro comes across this code, it first checks to see if the object name is a Visual FoxPro class. It isn't, so the Registry is checked. The object name is found and the CLSID, the object's unique identifier, is noted.
Also in the Registry's HKEY_CLASSES_ROOT hive is a key named CLSID, which contains an entry for all objects that have CLSIDs. As you can see in Figure 5, the Registry stores the executable file associated with each CLSID. That is how Windows knows to start the OLE Server when Visual FoxPro executes its CreateObject command.
Figure 5. Registry CLSID entries for Library OLE server
The form MEMBOLEFP.SCX uses the Visual FoxPro Library OLE server as the middle layer to talk to the SQL Server Library database. After calling the server the form invokes methods to retrieve member information, delete and update members, and so on.
To call the OLE server, the form uses the same CreateObject() function that would be used to call Excel or Word. The official registered name of the OLE server as it appears in the Windows Registry is vfpLibraryOLEServer.Members. The following code is located in the Load event of the form.
This.oLibrary = CreateObject("vfpLibraryOLEServer.Members")
If Type("This.oLibrary") # "O" Or IsNull(This.oLibrary)
= MessageBox("Couldn't set up the OLE Server.", MB_ICONINFORMATION)
This.lConnected = .F.
Return
Else
If This.oLibrary.InitConnection("robertg","vfpguru")
= MessageBox("Welcome to the library", MB_ICONINFORMATION)
ThisForm.lConnected = .T.
Else
= MessageBox("Access to the library denied", MB_ICONINFORMATION)
This.lConnected = .F.
Return
Endif
Endif
The code here tries to create a reference to the OLE server. If it works, the form property oLibrary will contain a reference to the OLE server. If the reference can't be created, then there is nothing left to do. If the object can be created, the form will invoke the InitConnection method of the OLE server in an attempt to login to SQL Server. If that doesn't work, there is nothing to do.
If the connection is made successfully, the form creates a cursor and then sets optimistic row buffering. The cursor will be used for adding and editing and allows you to use familiar data entry functions such as GetFldState() and OldVal(), as you will see later.
Set Multilocks On
Create Cursor c_member ;
( member_no I, firstname C(50), middleinitial C(1), ;
lastname C(50), street C(50), city C(50), state C(10), ;
zip C(10), phone_no C(20), expr_date T, ;
birth_date T, adult_member_no I )
= CursorSetProp("Buffering", DB_BUFOPTRECORD, 'c_member')
The InitConnection method of the OLE server takes the user ID and password as parameters and then uses SQLConnect() to connect to SQL Server. The method returns true if the connection worked and false if it didn't.
lParameters cUserID, cPassword
This.nHandle = SQLConnect('Library', cUserID, cPassword)
Return Iif(This.nHandle < 0, .F., .T.)
The GetMember method of the OLE server is used to retrieve information for a member. In the SQL pass-through examples from "Integrating SQL Server with Visual FoxPro," the sample forms used SQLExec() to either run a Select statement or to execute a stored procedure. In either case the results were returned in a cursor, which was then used to populate the form.
Life is not so simple with the OLE server, which is specifically designed to be used with any client, including Microsoft Access and Visual Basic. So it cannot simply return results in a Visual FoxPro cursor.
The OLE server's GetMember method takes three parameters: a string that will hold a delimited list of the retrieved data, the character you want to use as the delimiter, and the ID of the member you want retrieved. The Click event of the form's Locate button calls the GetMember method of the OLE server.
The string is passed by reference because the GetMember method will fill it with the member's information. The delimiter character will be used to separate the columns of information. If GetMember returns 0, there is no member with the supplied ID. If it returns a negative number, there was an error, which will be stored in the OLE server's LastErrDesc property.
nRetVal = ThisForm.oLibrary.GetMember(@lcMemberInfo, "|", ;
ThisForm.txtMemberID.Value)
If nRetVal < 0
lcMessage = ThisForm.oLibrary.LastErrDesc
= MessageBox(Substr(lcMessage, RAt(']',lcMessage)+1), ;
MB_ICONINFORMATION)
<code intentionally left out>
If nRetVal = 0
= MessageBox("There is no member with this ID.", ;
MB_ICONINFORMATION)
<code intentionally left out>
If the member's information is found, the string needs to be parsed to read the individual columns of information. The cursor created in the form's Load method is populated with the information and the form is refreshed.
For i = 1 To 10
j = AllTrim(Str(i))
nPipe&j = At("|", lcMemberInfo, i)
Next
nPipe11 = Len(lcMemberInfo)
Select c_member
Append Blank
Replace firstname With Substr(lcMemberInfo, 1, nPipe1 - 1), ;
middleinitial With Substr(lcMemberInfo, nPipe1 + 1, ;
nPipe2 - nPipe1 - 1), ;
lastname With Substr(lcMemberInfo, nPipe2 + 1, ;
nPipe3 - nPipe2 - 1), ;
street With Substr(lcMemberInfo, nPipe3 + 1, ;
nPipe4 - nPipe3 - 1), ;
city With Substr(lcMemberInfo, nPipe4 + 1, ;
nPipe5 - nPipe4 - 1), ;
state With Substr(lcMemberInfo, nPipe5 + 1, ;
nPipe6 - nPipe5 - 1), ;
zip With Substr(lcMemberInfo, nPipe6 + 1, ;
nPipe7 - nPipe6 - 1), ;
phone_no With Substr(lcMemberInfo, nPipe7 + 1, ;
nPipe8 - nPipe7 - 1), ;
expr_date With Ctot(Substr(lcMemberInfo, nPipe8 + 1, ;
nPipe9 - nPipe8 - 1)), ;
birth_date With Ctot(Substr(lcMemberInfo, nPipe9 + 1, ;
nPipe10 - nPipe9 - 1)), ;
adult_member_no With Val(Substr(lcMemberInfo, nPipe10 + 1, ;
nPipe11 - nPipe10 - 1))
<code intentionally left out>
ThisForm.Refresh
<code intentionally left out>
The code in the For loop determines the location of each pipe delimiter. Everything up to the first delimiter is the member's first name. Everything between the first delimiter and the second is the member's middle initial. Everything between the second delimiter and the third is the member's last name. And so on.
As mentioned above, the GetMember method of the OLE server takes three parameters, a string that will contain a delimited list of the member's information, the delimiter, and the member ID of the desired member. A Select statement is constructed and sent to SQL Server to retrieve the information. If the member is found, the string is constructed. Since the string was passed by reference it will be sent back to the form. The GetMember method itself returns 0 if the member was not found and 1 if the member was found.
LParameters cMemberInfo, cDelimiter, cSearchID
lcSQL = "Select firstname, middleinitial, lastname, street, " + ;
" city, state, zip, phone_no, expr_date, " + ;
" birth_date = null, adult_member_no = null " + ;
"From member, adult " + ;
"Where member.member_no = adult.member_no " + ;
" And member.member_no = " + AllTrim(cSearchID) + " " + ;
"Union " + ;
"Select firstname, middleinitial, lastname, street, " + ;
" city, state, zip, phone_no, expr_date, " + ;
" birth_date, adult_member_no " + ;
"From member, adult, juvenile " + ;
"Where member.member_no = juvenile.member_no " + ;
" And adult.member_no = juvenile.adult_member_no " + ;
" And member.member_no = " + AllTrim(cSearchID)
<code intentionally left out>
If RecCount("c_member") = 0
Return 0
Else
cMemberInfo = AllTrim(This.Convert(c_member.firstname)) + ; +
cDelimiter + ;
AllTrim(This.Convert(c_member.middleinitial)) + ;
cDelimiter + ;
AllTrim(This.Convert(c_member.lastname)) + ;
cDelimiter + ;
AllTrim(This.Convert(c_member.street)) + ;
cDelimiter + ;
AllTrim(This.Convert(c_member.city)) + cDelimiter + ;
AllTrim(This.Convert(c_member.state)) + ;
cDelimiter + ;
AllTrim(This.Convert(c_member.zip)) + cDelimiter + ;
AllTrim(This.Convert(c_member.phone_no)) + ;
cDelimiter + ;
AllTrim(This.Convert(c_member.expr_date)) + ;
cDelimiter + ;
AllTrim(This.Convert(c_member.birth_date)) + ;
cDelimiter + ;
AllTrim(This.Convert(c_member.adult_member_no)) + ;
cDelimiter
Return RecCount("c_member")
Endif
The AddMember method of the OLE server is used to add a member to the library. For ease of use, the method takes as a parameter a two-dimensional array. The first column contains field names and the second column contains the associated information. The form's AddMember method is called from the Click event of the Save button and in turn calls the GetMember method of the OLE server.
Dimension laMember[11,2]
laMember[1,1] = "firstname"
laMember[2,1] = "middleinitial"
laMember[3,1] = "lastname"
laMember[4,1] = "street"
laMember[5,1] = "city"
laMember[6,1] = "state"
laMember[7,1] = "zip"
laMember[8,1] = "phone_no"
laMember[9,1] = "expr_date"
laMember[10,1] = "birth_date"
laMember[11,1] = "adult_member_no"
* Some of the data is either left empty or supplied by the server
laMember[1,2] = AllTrim(ThisForm.txtFirstName.Value)
laMember[2,2] = AllTrim(ThisForm.txtMiddleInitial.Value)
laMember[3,2] = AllTrim(ThisForm.txtLastName.Value)
laMember[4,2] = AllTrim(ThisForm.txtStreet.Value)
laMember[5,2] = AllTrim(ThisForm.txtCity.Value)
laMember[6,2] = AllTrim(ThisForm.txtState.Value)
laMember[7,2] = AllTrim(ThisForm.txtZip.Value)
laMember[8,2] = AllTrim(ThisForm.txtPhoneNumber.Value)
If ThisForm.oLibrary.AddMember(@laMember) < 0
lcMessage = ThisForm.oLibrary.LastErrDesc
= MessageBox(Substr(lcMessage, RAt(']',lcMessage)+1), ;
MB_ICONINFORMATION)
Else
= MessageBox("This member has been added.", MB_ICONINFORMATION)
* Find out the member_no of the new member
ThisForm.txtMemberID.Value = AllTrim(Str(ThisForm.oLibrary.NewID))
<code intentionally left out>
The first column of the array laMember contains the names of the fields. The second contains the actual data, which is taken from the controls on the form. The array is passed, by reference to ensure it all goes, to the AddMember method, which returns 1 if the member was added and -1 if he or she wasn't. The LastErrDesc property of the OLE server contains the error message if the addition was unsuccessful.
As mentioned above, the AddMember method of the OLE server takes one parameter, an array of field names, and values.
LParameters aMemberInfo
Adding a member to the Library is a two-step procedure. First, a new row is added to the Member table, which contains the member's name. The member_no field in that table has been assigned the Identity property and SQL Server automatically determines the next value to use. The second step is to add a corresponding row to the Adult table, which contains the member's address. The same member_no must be used in both tables to maintain the one-to-one relationship.
The two steps need to be part of a transaction so that either both rows go in correctly or neither goes in.
= SQLSetProp(This.nHandle, "Transactions", 2)
Next, an Insert statement for the Member table is constructed and sent to SQL Server. If the Insert fails, the transaction is rolled back and the AddMember method returns -1, alerting the client that the Insert failed.
cSQL = "Insert member (firstname, middleinitial, lastname, " + ;
"photograph) " + ;
"Values ('" + AllTrim(aMemberInfo[1,2]) + "', " + ;
"'" + AllTrim(aMemberInfo[2,2]) + "', " + ;
"'" + AllTrim(aMemberInfo[3,2]) + "', " + ;
"NULL)"
If SQLExec(This.nHandle, cSQL) < 0
This.SetError
* Rollback the transaction
= SQLRollback(This.nHandle)
Return -1
Endif
The SQL Server global variable @@Identity stores the most recent value inserted into an Identity column. In this case it contains the new user's member_no. That number is stored in the NewID property of the OLE server.
If SQLExec(This.nHandle, "Select @@identity") < 0
This.SetError
* Rollback the transaction
= SQLRollback(This.nHandle)
Return -1
Endif
This.NewID = sqlresult.exp
Next, the corresponding row in the Adult table is added. The value for member_no comes from the value of @@Identity. As before, if this Insert fails, the whole transaction is rolled back and AddMember returns -1.
cSQL = "Insert adult (member_no, street, city, state, zip, " + ;
"phone_no, expr_date) " + ;
"Values (" + AllTrim(Str(This.NewID)) + ", " + ;
"'" + AllTrim(aMemberInfo[4,2]) + "', " + ;
"'" + AllTrim(aMemberInfo[5,2]) + "', " + ;
"'" + AllTrim(aMemberInfo[6,2]) + "', " + ;
"'" + AllTrim(aMemberInfo[7,2]) + "', " + ;
"'" + AllTrim(aMemberInfo[8,2]) + "', " + ;
"'" + TToC(DToT(GoMonth(Date(),12))) + "' )"
If SQLExec(This.nHandle, cSQL) < 0
This.SetError
* Rollback the transaction
= SQLRollback(This.nHandle)
Return -1
Endif
The last step is to try to commit the transaction. If that fails, everything is rolled back. If the Commit works, the new member has been added and AddMember returns 1, indicating success.
If SQLCommit(This.nHandle) < 0
This.SetError
* Rollback the transaction
= SQLRollback(This.nHandle)
Return -1
Else
Return 1
Endif
The UpdateMember method of the form calls the UpdateMember method of the OLE server. The form passes to the OLE server a two dimensional array and the ID of the member. As with the AddMember method above, the first column of the array contains the names of fields to be updated, while the second column contains the new information for a particular member.
When a member was added, the array contained a row for each field. In this case however, it should contain a row for only those fields whose value has changed. There is no point in making SQL Server update information that hasn't changed.
The form looks at each control to see if its value has changed. If so, a row is added to the array. Notice the use of OldVal() to see if the field changed. The use of the buffered cursor makes this possible.
i = 0
If c_member.firstname <> OldVal("c_member.firstname")
i = i + 1
Dimension laMember[i, 2]
laMember[i,1] = "firstname"
laMember[i,2] = AllTrim(ThisForm.txtFirstName.Value)
Endif
If c_member.lastname <> OldVal("c_member.lastname")
i = i + 1
Dimension laMember[i, 2]
laMember[i,1] = "lastname"
laMember[i,2] = AllTrim(ThisForm.txtLastName.Value)
Endif
<code intentionally left out>
If no fields were changed, there is nothing to do and therefore no point in bothering SQL Server.
If i = 0
= MessageBox("There is nothing to save.", MB_ICONINFORMATION)
Return
Endif
If any fields have been changed, the form invokes the UpdateMember method of the OLE server. The array is passed, as well as the member's ID. If UpdateMember returns -1, the update failed and the reason is displayed to the user.
If ThisForm.oLibrary.UpdateMember(@laMember, ;
ThisForm.txtMemberID.Value) < 0
lcMessage = ThisForm.oLibrary.LastErrDesc
= MessageBox(Substr(lcMessage, RAt(']',lcMessage)+1), ;
MB_ICONINFORMATION)
Else
= MessageBox("This member's information has been saved.", ;
MB_ICONINFORMATION)
<code intentionally left out>
As mentioned above, the OLE server UpdateMember method takes two parameters, the array of field names and data, and the ID of the member to update.
LParameters aMemberInfo, cSearchID
Just as adding a member required two Inserts wrapped in a transaction, updating a member requires two Updates wrapped in a transaction. To build each Update statement, UpdateMember searches each array for each field. For instance, if the string "firstname" is found in the array and if it is in the first column, the firstname field should be updated in the Member table. Two strings, cSQL1 and cSQL2 are constructed, each containing part of an Update statement.
cSQL1 = ""
nPos = AScan(aMemberInfo, "firstname")
If Mod(nPos, 2) = 1
cSQL1 = cSQL1 + "firstname = '" + aMemberInfo[nPos + 1] + "', "
Endif
nPos = AScan(aMemberInfo, "lastname")
If Mod(nPos, 2) = 1
cSQL1 = cSQL1 + "lastname = '" + aMemberInfo[nPos + 1] + "', "
Endif
<code intentionally left out>
cSQL2 = ""
nPos = AScan(aMemberInfo, "street")
If Mod(nPos, 2) = 1
cSQL2 = cSQL2 + "street = '" + aMemberInfo[nPos + 1] + "', "
Endif
nPos = AScan(aMemberInfo, "city")
If Mod(nPos, 2) = 1
cSQL2 = cSQL2 + "city = '" + aMemberInfo[nPos + 1] + "', "
Endif
A transaction is then begun.
= SQLSetProp(This.nHandle, "Transactions", 2)
If anything has been stored to the cSQL1 variable, the rest of the Update statement for the Member table is constructed and passed to SQL Server. If it fails, the transaction is rolled back.
If Len(cSQL1) > 0
* Add the Update, strip off the last comma, add a Where clause
cSQL1 = "Update member Set " + Left(cSQL1, Len(cSQL1) - 2) + ;
"Where member_no = " + AllTrim(cSearchID)
If SQLExec(This.nHandle, cSQL1) < 0
This.SetError
* Rollback the transaction
= SQLRollback(This.nHandle)
Return -1
Endif
Endif
Similarly, if anything has been stored to the cSQL2 variable, the rest of the Update statement for the Adult table is constructed and passed to SQL Server. If it fails, the transaction is rolled back.
If Len(cSQL2) > 0
* Add the Update, strip off the last comma, add a Where clause
cSQL2 = "Update adult Set " + Left(cSQL2, Len(cSQL2) - 2) + ;
"Where member_no = " + AllTrim(cSearchID)
If SQLExec(This.nHandle, cSQL2) < 0
This.SetError
* Rollback the transaction
= SQLRollback(This.nHandle)
Return -1
Endif
Endif
The last step is to commit the transaction. If the Commit fails, the transaction is rolled back.
If SQLCommit(This.nHandle) < 0
This.SetError
* Rollback the transaction
= SQLRollback(This.nHandle)
Return -1
Else
Return 1
Endif
The OLE Server's RemoveMember method is used to delete a member. The method takes as a parameter the ID of the member to delete. RemoveMember returns 1 if the member was deleted and -1 otherwise. If the deletion fails, the OLE server property LastErrDesc contains the reason. The deletion could fail because referential integrity is violated; for instance, a member with outstanding loans or active juveniles cannot be deleted. It could also fail because a SQLExec() failed. Either way, the form code assumes the reason will be contained in oLibrary.LastErrDesc and can also be displayed to the user.
If ThisForm.oLibrary.RemoveMember(ThisForm.txtMemberID.Value) < 0
lcMessage = ThisForm.oLibrary.LastErrDesc
= MessageBox(Substr(lcMessage, RAt(']',lcMessage)+1), ;
MB_ICONINFORMATION)
Else
= MessageBox("This member has been deleted.", MB_ICONINFORMATION)
<code intentionally left out>
The OLE server's RemoveMember method takes one parameter, the member ID of the member that the user is attempted to delete.
LParameters cSearchID
There are several reasons a delete attempt might fail. With the data used here, the two most frequent are that the member has outstanding loans or is an adult sponsoring active juveniles. If either of those is true the Delete will fail.
The RemoveMember method code could have been written to try the deletion and then monitor failures to see if either of these is the reason. But this is inefficient, especially compared to the alternative of checking these two conditions first. If the member has active loans or is sponsoring juveniles, then the Delete won't even be tried. This makes it easier to trap for the error and also spares processing on the SQL Server.
* First check to see if this member has active loans
cSQL = "Select member_no From loan Where member_no = " + ;
AllTrim(cSearchID)
If SQLExec(This.nHandle, cSQL) < 0
This.SetError
Return -1
Else
If RecCount("sqlresult") <> 0
This.LastErrDesc = "This member can not be deleted. " + ;
"He/she has active loans."
Return -1
Endif
Endif
* Now check to see if this is an adult with active juveniles
cSQL = "Select member_no From juvenile Where adult_member_no = " + ;
AllTrim(cSearchID)
If SQLExec(This.nHandle, cSQL) < 0
This.SetError
Return -1
Else
If RecCount("sqlresult") <> 0
This.LastErrDesc = "This member can not be deleted. " + ;
"He/she is an adult with active juveniles."
Return -1
Endif
Endif
If there are other things to check, the code for them could follow the lines above. If none of the obvious problems is present, a transaction is started and the deletion proceeds.
= SQLSetProp(This.nHandle, "Transactions", 2)
A member may have related records in the Loanhist table, which contains a row for every borrowed book that was returned, or in the Reservation table, which contains a row for every book on reserve. These related rows are deleted first. If not, referential integrity would be violated when the member was deleted. If either of these Deletes fails the transaction is rolled back.
* Delete the loan history records for this member.
cSQL = "Delete loanhist Where member_no = " + AllTrim(cSearchID)
If SQLExec(This.nHandle, cSQL) < 0
This.SetError
* Rollback the transaction
= SQLRollback(This.nHandle)
Return -1
Endif
* Delete the loan reservation records for this member.
cSQL = "Delete reservation Where member_no = " + AllTrim(cSearchID)
If SQLExec(This.nHandle, cSQL) < 0
This.SetError
* Rollback the transaction
= SQLRollback(This.nHandle)
Return -1
Endif
Any other related records would also be deleted at this point. After all the related rows are gone, the member can be deleted, first from the Adult table and then from the Member table. The Deletes must be in this order to preserve referential integrity. Again, if either of these Deletes fails, the transaction is rolled back.
* Delete the member's row in the Adult table.
cSQL = "Delete adult Where member_no = " + AllTrim(cSearchID)
If SQLExec(This.nHandle, cSQL) < 0
This.SetError
* Rollback the transaction
= SQLRollback(This.nHandle)
Return -1
Endif
* Delete the member's row in the Member table.
cSQL = "Delete member Where member_no = " + AllTrim(cSearchID)
If SQLExec(This.nHandle, cSQL) < 0
This.SetError
* Rollback the transaction
= SQLRollback(This.nHandle)
Return -1
Endif
If the next part is not familiar, you either didn't read the preceding parts of these notes or you slept through them. Commit if go, rollback if no. The RemoveMember method returns -1 if the delete failed and 1 if it succeeded.
If SQLCommit(This.nHandle) < 0
This.SetError
* Rollback the transaction
= SQLRollback(This.nHandle)
Return -1
Else
Return 1
Endif
The SetError method of the OLE server stores the SQL Server error to the LastErrDesc property, which is then available to the client.
lnError = AError(laError)
This.LastErrDesc = Substr(laError[3], RAt(']',laError[3]) + 1)
See "Integrating SQL Server with Visual FoxPro" for an explanation of how to use views and SQL pass-through to create two-tier client/server applications. One of the examples used SQL Server stored procedures to add, update, and delete members. A benefit to that technique was that processing occurred on the server and not the client. This not only avoided the fat client problem but also made it unlikely that developers would mess up the data. For instance, you couldn't forget to remove the member's related loan history records if all you did was invoke a stored procedure.
Using a three-tier architecture is very similar to using stored procedures in terms of coding difficulty (low) and the level of control you retain (also low). For instance, the code to invoke the stored procedure to delete a member is
If SQLExec(ThisForm.nHandle, "Execute RemoveMember " + ;
ThisForm.txtMemberID.Value) < 0
while the code to invoke the equivalent OLE server method is
If ThisForm.oLibrary.RemoveMember(ThisForm.txtMemberID.Value) < 0
In both of these cases, all the developer needs to know is the name of the method or procedure, what parameters it takes, and what it returns. Both the SQL Server stored procedures and the OLE server methods become black boxes.
Someone who is in charge only of the front-end doesn't need to know or care what is in the middle layer as opposed to what is in the back-end. He or she may just want to know how to call the methods and how to tell whether they succeeded. This puts the rapid back in rapid development.
In one of the examples from "Integrating SQL Server with Visual FoxPro," SQLExec() was used from the client form to handle all communications with SQL Server. Selects, Inserts, Updates, and Deletes were sent and the results were manipulated as necessary. Most of the work other than the actual physical managing of data was done in the client. The risk there was that you might wind up with a fat client.
In another example from "Integrating SQL Server with Visual FoxPro," SQLExec() was used to call stored procedures sitting on SQL Server. No work was done in the client, other than managing a local cursor filled with one row at a time. Both data manipulation and validation were performed on the server. The risk there was that you might wind up with a fat server.
In the example used in this article, the front-end does nothing but invoke methods of the OLE server. In fact, in this example, which is admittedly rather simple, the middle layer looks strikingly like the first SQLExec() example. In fact, much of the code for the OLE server was taken from the form MEMBEXEC.SCX.
If all the data validations and business rule checking are put into the middle layer, does this lead to a problem of fat middles? Has the fat problem merely been moved up the hierarchy? To be sure, it would be possible to overload the OLE server to the point where it can't perform its role satisfactorily. However, since it runs on one machine it would be easier to solve the fat issue by throwing hardware at it than if the problem were fat clients. For a relatively small amount of money you could stick one or two more processors and 16 or 32 MB more RAM in the middle-tier machine and, conceivably, see any performance problems disappear. Of course, that works only if the processing power of the machine is the bottleneck.
The important point is that because Visual FoxPro and SQL Server, and for that matter Oracle and others, have both powerful data engines and powerful languages, you can fine tune the fat. You can decide where to place code and where to put the processing burden, and you can tweak it and adjust it until performance is acceptable.
It is not difficult to create a Visual FoxPro OLE server. It is also not difficult to call an OLE server's methods from a form or other code. Setting up remote automation is not difficult either. So, writing a client/server application using a three-tier architecture is only mildly more difficult than using a two-tier architecture.
So why would you do it? When do you need to move up to a three-tier architecture? Certainly performance is a reason. Do it if your client machines are underpowered and if, by moving large chunks of code to a more powerful middle-tier box, you can significantly improve performance.
How likely is this? While it is true that the resource requirements for Visual FoxPro have fallen with version 5.0, you still want at least a 486 with 12 MB of RAM. Pentiums with 32 MB cost $2,000 these days. Consider a company that is moving an application off the mainframe and rewriting it using Visual FoxPro and SQL Server. They buy 100 brand new machines and have a fairly speedy network. In that situation, they may not realize a large performance gain by using a three-tier, as opposed to two-tier, architecture. Note that might is the operative word here.
Perhaps a more compelling reason to consider three-tier is the fact that you can move your data validation and business rules code into one location. Suppose there is a rule that no new orders can be placed if a customer has more than $20,000 60 days past due. In a two-tier architecture you can easily code this into the Click event of the Add button and immediately put up a message telling the user he or she can't place the order. The users love it and everyone is happy.
Suppose the rule changes and the limit is raised to $30,000. How many client machines have to be upgraded? Do you have to regenerate the APP or EXE file and redistribute it? This can be quite a burden and a large cost. You may have designed the system so that business rules are stored in a metatable somewhere. If so, good for you. If not, there is a lot of work to do for what seems like a simple change.
If the business rule were in an OLE server, however, the change is fairly simple. You would change the code and recompile the one EXE. None of the client machines would have to change. The rule would go into effect as soon as the EXE was done and the maintenance burden would be a fraction of what it could have been.
Even if a remote OLE server decreased overall performance slightly, you might still use it to take advantage of the decreased maintenance burden.
The OLE server middle layer approach provides several enticing benefits. The fact that it runs on a separate machine allows you to run your validations and business rules on a powerful machine without breaking your budget. You can purchase one ultra-powerful machine instead of having to beef up every client.
When it comes time to change the validations or business rules, you would only need to change the middle layer. You would change the Visual FoxPro code and remake the OLE server. Every client would automatically use the new rules because they would be located in a single location. This dramatically simplifies the problem of distributing changes and also guarantees that all clients are using the same, and updated, rules.