Presented by: Robert Green
Robert Green is a product manager for desktop databases in the Tools Marketing division at Microsoft Corporation.
Phone: (425) 882-8080
Fax: (425) 936-7329
Email: rgreen@microsoft.com
The Microsoft® Visual FoxPro™ database is a robust and powerful interface for client/server applications. Remote views and Structured Query Language (SQL) pass-through provide powerful tools to take advantage of SQL servers such as ORACLE® and Microsoft SQL Server™ client/server database management system using ODBC (Open Database Connectivity).
One of the great truisms of application development is that there are many ways to do something. 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 as the interface and a very powerful SQL database engine on the server.
In this session a Microsoft ActiveX™ Automation server framework 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 is knowing how to split up the work between the applications and servers. 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.
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 server but it can also lead to problems.
The first problem with loading up the front end is that it requires a more powerful computer. If you are using Visual FoxPro as the front end you will want at least a 486 with 12 MB of RAM. This issue spawned the phrase fat client. When choosing a front end, girth is a consideration .. You need to weigh the strengths and weaknesses and resource requirements of Visual FoxPro, the Visual Basic® programming system, the Microsoft Access database, Delphi, PowerBuilder®, Java™, and others. Java promiss to be 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 write the validation and business rule code, you will presumably write it once, either in Visual FoxPro, as SQL Server–stored procedures, or 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 will be the location of the code?
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 select, insert, update, and delete procedures. The keepers of the back end may limit you to using stored procedures only . This approach guarantees that the front end does things right. If your only access to the data is through the stored procedures, you will be forced to do things in the correct order and not leave out any steps. For instance, if you are deleting a member, you will not forget to remove the member's reservations. If you are adding a new member, you will not forget to put the new ID into the Adult table.
If your front end is one of several front ends all accessing the same back-end data, you will 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 development is two-tier, consisting of a client and a server. As previously discussed, this can 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 for both the client and the server.
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 another 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. Because the problem with fat clients is that the client machine is overburdened. In a two-tier architecture you may have to provide each of the clients with at least a 16 MB Pentium® machine. In a three-tier architecture you can locate the middle layer on a 32 MB dual microprocessor Pentium machine but use a 12 MB 486 as the client.
There are several ways to set up the middle layer. One approach is to use a SQL Server machine as both the middle layer and server, such as using an Automation server created in Visual FoxPro. The Automation server is called from Visual FoxPro or any Automation controller using the CreateObject() function, in the same manner as you call a Microsoft Excel spreadsheet or Microsoft Word word-processor as Automation servers.
To create an Automation server in Visual FoxPro, read Custom OLE Servers on the Visual FoxPro Web site (www.microsoft.com), or see Chapter 16 of the Visual FoxPro Developers Guide.
The Automation server can be created as an InProc or OutOfProc server. In the world of the Win32® application programming interface (using Windows® 95 and Windows NT® operating systems) 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 dynamic-link library (DLL) and runs in the same address space as the process that called it. An OutOfProc server is compiled as an executable and runs as a separate process. An InProc server offers better performance because interprocess communication is slower than intraprocess communication. On the other hand, since an OutOfProc server runs in a separate process you can take advantage of a multiprocessor environment.
The Automation server can be run locally or remotely. Typically, a remote Automation 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 for remote automation.
The data used in this session's examples is from a SQL Server–based application for a library. The library wants to keep track of its members, books and 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. The 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 adults and juveniles. This saves disk space because 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. Furthermore, you don't care about the adult's birth date although you do care about a juvenile's birth date because on their 18th birthday he or she becomes an adult.
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 generated automatically 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 version 6.0 added declarative referential integrity (RI), so you can 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 these examples 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 want those you can use triggers instead of constraints.
The Library Automation 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 Automation Server project is the file LibraryServer.pjx. The project contains a custom class called Members. Click the appropriate check box in the Class Info dialog box to identify the OLE Public class.
The methods and properties of the Members class, and hence the Automation Server, are listed in Table 1 and Table 2, respectively.
Table 1: Library Automation 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 Automation 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 Info 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 Automation server is registered automatically on the machine that built it. If you move it to another machine you can register it by running Regsvr32.exe, found in the Windows/System directory in Windows 95 and in the Windows/System32 directory in Windows NT.
Figure 4. Windows registry showing automation server
To use the Automation server, a client application uses a similar line of code:
oLibrary = CreateObject("VFPLibraryOLEServer.Members")
Visual FoxPro first checks this code to see if the object name is a Visual FoxPro class. If it isn't, 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 shown in Figure 5, the Registry stores the executable file associated with each CLSID. That is how Windows knows to start the Automation Server when Visual FoxPro executes its CreateObject command.
Figure 5. Registry CLSID entries for Library Automation Server
The form Membolefp.scx uses the Visual FoxPro Library Automation 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, etc.
To call the Automation server, the form uses the same CreateObject() function that is used to call Microsoft Excel or Word. The official registered name of the Automation 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 tries to create a reference to the Automation server. If it works, the form property oLibrary will contain a reference to the Automation server. If the reference can't be created, there is nothing left to do. If the object can be created, the form will invoke the InitConnection method of the Automation server in an attempt to log on 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 Automation 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 Automation server is used to retrieve information for a member. In the SQL pass-through examples from Session BKO02, 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.
Because the Automation server is specifically designed to be used with any client, including Microsoft Access and Visual Basic, it cannot simply return results in a Visual FoxPro cursor.
The Automation server's GetMember method takes three parameters: a string that holds 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 Automation 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 Automation 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.
As mentioned above, the GetMember method of the Automation 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 Automation 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 Automation 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 column 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 Automation server displays the error message if the addition was unsuccessful.
As mentioned above, the AddMember method of the Automation 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 go 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 Automation 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 Automation server. The form passes to the Automation 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 is added, the array adds a row for each field. In this case, however, it should contain a row for only those fields where the value has changed. SQL Server does not 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 Automation server. The array, as well as the member's ID, is passed. 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 Automation 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 Automation 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 if otherwise. If the deletion fails, the Automation 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 Automation server's RemoveMember method takes one parameter, the member ID of the member the user is attempted to delete.
LParameters cSearchID
There are several reasons a delete attempt may fail. The two most frequent reasons for this example are because the member either 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 will 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 will also be deleted at this point. Once 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 Automation 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)
A two-tier approach to client/server development is to use SQL Server stored procedures to add, update, and delete members. A benefit to this technique is that processing occurs on the server and not the client. This not only avoids the fat client problem but also makes it unlikely that developers will jumble 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 Automation 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 Automation server methods become black boxes.
Someone who is in charge of the front end only doesn't need to know what is in the middle layer as opposed to the back end. He or she may just want to know how to call the methods and how to tell whether they succeeded.
In a two-tier architecture, SQLExec() can be used from the client form to handle all communications with SQL Server. The select, insert, update, and delete procedures are sent and the results are manipulated as necessary. Most of the work other than the actual physical managing of data is done by the client. The risk here is winding up with a fat client.
Another two-tier approach is to use SQLExec()to call stored procedures sitting on SQL Server. No work is done on the client, other than managing a local cursor filled with one row at a time. Both data manipulation and validation are performed on the server. The risk here is winding up with a fat server.
In the example used in this session, the front end does nothing but invoke methods of the Automation server. In fact, in this example, the middle layer looks strikingly like the first SQLExec() example. In fact, much of the code for the Automation 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 is possible to overload the Automation server to the point where it can't perform its role satisfactorily. However, since it runs on one machine it will be easier to solve the fat issue by improving the hardware than if the problem were fat clients. For a relatively small amount of money you could stick one or two more microprocessors 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 Automation server, to call an Automation server's methods from a form or other code, or to set up remote automation. 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? You need to move up to a three-tier architecture when 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 decreased with version 5.0, you still want at least a 486 personal computer with 12 MB of RAM. Consider a company who is moving an application off the mainframe and rewriting it using Visual FoxPro and SQL Server. The company buys 100 brand new machines and has a fairly fast network. It may or may not realize a large performance gain by using a three-tier as opposed to two-tier architecture.
A more compelling reason to consider three-tier architecture is 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 display 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 costly. You may have designed the system so that business rules are stored in a metatable somewhere. If not, there is a lot of work to do for a simple change.
However, if the business rules were in an Automation server, the change is fairly simple. You can change the code and recompile the one .exe. None of the client machines will have to change. The rule will go into effect as soon as the executable was done and the maintenance burden is a fraction of what it could have been.
Even if a remote Automation server decreased overall performance slightly you can still use it to take advantage of the decreased maintenance burden.
The Automation server middle-layer approach provides several enticing benefits. Because it runs on a separate machine you can run your validations and business rules on a powerful machine without going over your budget. You can purchase one very powerful machine instead of having to upgrade every client.
When you need to change the validations or business rules you will only need to change the middle layer. You will change the Visual FoxPro code and remake the Automation server. Every client will automatically use the new rules because they are located in a single location. This dramatically simplifies the problem of distributing changes and also guarantees that all clients are using the same, updated, rules.
© 1997 Microsoft Corporation. All rights reserved.
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.
Microsoft, Visual Basic, Win32, Windows, and Windows NT are registered trademarks and ActiveX and Visual FoxPro are trademarks of Microsoft Corporation.
Java is a trademark of Sun Microsystems, Inc.
Other product or company names mentioned herein may by the trademarks of their respective owners.