Building Three-Tier Client/Server Applications with Visual FoxPro

Robert Green
Microsoft Corporation

January 1997

Introduction

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 Fat Client Problem

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.

Three-Tier Architecture

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.

Creating a Visual FoxPro OLE Server

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 Sample Data

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

Table Structures

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.

Declarative Referential Integrity

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

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

Using the 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.

Loading the Form

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')

Connecting to SQL Server

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.)

Locating a Member

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.

The OLE Server's GetMember Method

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

Adding an Adult

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.

The OLE Server's AddMember Method

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

Saving Changes

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>

The OLE Server's UpdateMember Method

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

Deleting a Member

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

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

Storing SQL Server Errors

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)

Black Box Programming with OLE Servers

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.

Where Is the Fat?

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.

When Do You Need This?

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.

Conclusion

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.