Integrating SQL Server 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 Microsoft 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.

This article explores two approaches to building client-server applications: remote views and SQL pass-through. You will see some of the pros and cons of each approach. Of course, the best solution will vary from application to application but after you see the examples here you will have a better feel for the strengths of these two methods and how you would use them to build client-server applications.

The Sample Data

The data used in this article’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 1.

Figure 1. 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 eighteenth 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.

Using Views—Attempt 1

The first pass at building the Library application uses remote views. Views are easy to set up, can be parameterized so they only return one or a few records at a time, support row and table buffering, and support transaction processing. With all that power built in you would be remiss not to consider building client-server applications based on remote views.

The Views

In the Library database (the Visual FoxPro version) you will find the remote views vAdultMember and vJuvenileMember. The following shows the SQL statements that define the two views:

SELECT Member.member_no, Member.lastname, Member.firstname, ;

       Member.middleinitial, Adult.street, Adult.city, ;

       Adult.state, Adult.zip, Adult.phone_no, Adult.expr_date ;

FROM dbo.adult Adult, dbo.member Member ;

WHERE Adult.member_no = Member.member_no ;

  AND Member.member_no = ?nMemberID


SELECT Member.member_no, Member.lastname, Member.firstname, ;

       Member.middleinitial, Juvenile.adult_member_no, ;

       Juvenile.birth_date, Adult.street, Adult.city, Adult.state, ;

       Adult.zip, Adult.phone_no, Adult.expr_date ;

FROM dbo.adult Adult, dbo.juvenile Juvenile, ;

     dbo.member Member ;

WHERE Adult.member_no = Juvenile.adult_member_no ;

  AND Juvenile.member_no = Member.member_no ;

  AND Member.member_no = ?nMemberID

The two views are very straightforward. The member's name is in the Member table and the address is in Adult. A juvenile's birth date and sponsoring adult can be found in the Juvenile table. Both views are based on the same connection and are made modifiable by identifying the primary key in each table and by marking the other fields as updatable. This occurs in the Update Criteria tab in the View Designer.

Loading the Form

The form MEMBVIEW.SCX uses these two views. The following code is the form's Load method. The form comes up with no data loaded because the two views are opened with the NoData option. Optimistic row buffering is then set for the view cursors.

Open Database library

Use vAdultMember In 0 NoData

= CursorSetProp("Buffering", DB_BUFOPTRECORD, ;

                "vAdultMember")

Use vJuvenileMember In 0 NoData

= CursorSetProp("Buffering", DB_BUFOPTRECORD, ;

                "vJuvenileMember")

Locating a Member

The user can enter a member's ID and press the Locate button. This supplies a value for the nMemberID parameter and the view is requeried. The following code is in the Click event of the Locate button:

nMemberID = Val(ThisForm.txtMemberID.Value)


Select vAdultMember

= ReQuery()

If RecCount("vAdultMember") = 0

  Select vJuvenileMember

  = ReQuery()

  If RecCount("vJuvenileMember") = 0

    lcMessage = "There is no member with this ID."

    = MessageBox(lcMessage , MB_ICONINFORMATION)

<code intentionally left out>

The code first checks to see if the user had made changes. It then stores the entered ID to the nMemberID variable. The Adult view is requeried first. If no record is found with that ID the Juvenile view is requeried. If no record is found with that ID then the ID is not valid. If the member is found the information is displayed on the form.

Adding a Member

When the user presses the Add button he or she is presented with an empty form. The record is not actually saved until the Save button is pressed. The Click event code of the Add button first checks to see if the user has made changes to the current member record. TableRevert() is issued on the view that is not in use to make sure Visual FoxPro does not think changes are pending in that view. A blank record is then added to the view in use and the form is refreshed. The user enters the new member's information in the blank record. When the user presses Save, Visual FoxPro will send the new record to SQL Server.

Saving Changes

One of the nice features of views is that dealing with the back-end is handled for you. The TableUpdate() function is used to save changes to the view's underlying table(s). Visual FoxPro handles this automatically. The following code is in the Click event of the Save button:

If TableUpdate()

  = MessageBox(lcMessage, MB_ICONINFORMATION)

Else

  ThisForm.ShowError

Endif

The vAdultMember or vJuvenileMember view is selected, depending on whether the current member is an adult or a juvenile. A TableUpdate() is then issued. If it worked, the data was saved. If it didn't, the user is shown what the problem is.

Notice that because this code uses TableUpdate(), it handles both adding new members and making changes to existing members.

Deleting a Member

To delete a row on the back-end it merely needs to be deleted in the view. The following code is in the Click event of the Delete button:

Select (ThisForm.cViewInUse)

Delete

If TableUpdate()

  = MessageBox("This member has been deleted.", ;

               MB_ICONINFORMATION)

  Append Blank

<code intentionally left out>

Else

  ThisForm.ShowError

  =TableRevert()

Endif

After the user is asked to confirm the deletion the appropriate view is selected, the record is deleted from the view and TableUpdate() is issued. If it works, the record is gone on the back-end. If it doesn't, the user is shown what went wrong.

Issues

Isn't Visual FoxPro wonderful? How easy can client-server be, you ask? This form is elegantly simple and easy to use. There are only three problems with it.

You can't add new members

To test this, press the Add button, add a new member and press Save. After a small pause you will see the message shown in Figure 2.

Figure 2. Error message after adding a member

When the Add button was pressed, a blank record was appended to the vAdultMember view cursor. This view is made up of two tables, Adult and Member. When the Save button is pressed, Visual FoxPro sends the name information to the Member table. SQL Server automatically supplies the member ID. So far so good. Visual FoxPro also sends the address information to the Adult table. But it doesn't send anything for the member's ID. That column is left empty, which is invalid and causes the TableUpdate() to fail.

For this to work, the new row needs to be added to the Member table first and the resulting ID needs to be put into the Adult table along with the address information. There is no reason to think that Visual FoxPro view knows this. How could it? Visual FoxPro doesn't know what the ID is and wouldn't know to send it in the Adult table anyway.

You can't delete members

In the Library database (the SQL Server version) there is referential integrity defined between the Adult and Juvenile tables and between the Member and Loan tables. You can not delete a member if he or she is an adult responsible for juvenile members or if he or she has outstanding loans. You would expect SQL Server to reject the deletion and send back an error.

However, you should be able to delete a member if he or she has no associated juveniles and no outstanding loans. Try this and you will see the error message shown in Figure 3.

Figure 3. Error message after deleting a member

This error, once you decipher the SQL Server language, tells you that you have violated referential integrity between the Adult and Member tables. This will happen if you try to delete the Member row before you delete the Adult row. This obviously is what the view is doing. For the delete to work the row has to be deleted from Adult first and then from Member. But again, how is Visual FoxPro supposed to know that?

The error messages are incomprehensible

If you try to delete a member with juveniles you will be stopped. If you try to delete a member with loans you will be stopped. However, SQL Server will send back to Visual FoxPro an error message with language similar to the one in Figure 3. This is totally incomprehensible to the user.

Consider another example. The Adult table has the following Check constraint defined:

ALTER TABLE adult

  WITH NOCHECK

  ADD CONSTRAINT phone_no_rule CHECK (phone_no LIKE 

    '(206)[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')

This constraint forces phone numbers to begin with the 206 area code. If a row is inserted or updated and the phone number violates this rule the data will be rejected. However, the user will be presented with the monstrosity shown in Figure 4.

Figure 4. Check constraint violated error message

Using Views—Attempt 2

So are views useless in this situation? Should you give up on them? Not without at least one more attempt. The problem above is that the views are made up of two tables and you have no control over how Visual FoxPro tries to insert or update rows. There are specific rules you have to follow based on the Library database schema but you can't make the views behave.

As an alternative approach, the form MEMBVEW2.SCX uses three views instead of two. The views vMemberTable, vAdultTable and vJuvenileTable are all based on a single SQL Server table.

Loading the Form

The form still comes up with no data loaded. All three views are opened with the NoData option and optimistic row buffering is then set for them.

The three views are all based on the same shared connection. The ConnectHandle property of any of the views stores the connection handle used by the views. This handle will be used shortly.

* What is the connection handle used by the views?

ThisForm.nHandle = CursorGetProp("ConnectHandle", ;

  "vMemberTable")

Locating a Member

When the user wants to see a new member's information he or she will still provide a member ID and press Locate. The code in this form differs slightly from the previous version in that the Member view is requeried first. If no record is found then you immediately know the ID is not valid. The Adult view is requeried next. If no record is found then the member must be a juvenile.

Saving Changes

In the previous version of the form a simple TableUpdate() was issued when the user pressed Save. As you saw, that is insufficient given the table structures. This version of the form takes a more complicated but ultimately much more successful approach. If the user is currently adding a record the first half of Save button's Click event code will be run.

If ThisForm.lAddMode = .T.

  Wait Window "Saving new member information..." NoWait

The code adds the new member to the Member table first. A row will then be added to the Adult table. Both rows should be added or neither should be added. Therefore a transaction has to be started. The Transactions property of a connection is used to control transaction processing. In the form's Load method the view's connection handle was stored to the form property nHandle. SQLSetProp() is then used to start a transaction.

  * Begin a transaction

  = SQLSetProp(ThisForm.nHandle, "Transactions", 2)

The vMemberTable view is then selected and a TableUpdate() is issued. If it works the new member's name information will have been added to the Member table. In addition the member will have a member ID. If the TableUpdate() failed the user should see what happened and the transaction is rolled back using the SQLRollback() function.

* Add the new member to the member table

  Select vMemberTable

  If Not TableUpdate()

    ThisForm.ShowError

    * Rollback the transaction

    = SQLRollback(ThisForm.nHandle)

    Return

  Endif

If the TableUpdate() worked then the new row made it into the Member table. The next step is to find out what member ID was assigned. SQLExec() is used to send a Select statement to SQL Server. This Select retrieves the value of @@Identity, which stores the last Identity value inserted into a table. In this case that will be the member ID. SQLExec() uses the same connection handle the views use. This is efficient because it doesn't require another connection to SQL Server.

  * Find out the member_no of the new member

  If SQLExec(ThisForm.nHandle, "Select @@Identity")< 0

<code intentionally left out>

The member_no in the vAdultTable view is replaced with the value of @@Identity. This enforces the relationship between Member and Adult. Note that this involves changing the primary key of the Adult table. There is no other choice. The expiration date for this member is set to one year from today and TableUpdate() is issued to save this information to the Adult table. If it doesn't work the transaction is rolled back. This would remove the row from the Member table.

  * Add the new member to the adult table

  Select vAdultTable

  * The member_no is the primary key but it is

  * updatable in the view for just this very reason.

  * The expiration date is one year from today.

  Replace member_no With sqlresult.exp, ;

          expr_date With DToT(GoMonth(Date(),12))

  If Not TableUpdate()

<code intentionally left out>

If everything worked to this point the transaction is committed with the SQLCommit() function. If that fails then everything is rolled back.

  * Everything worked so commit the transaction.

  If SQLCommit(ThisForm.nHandle) < 0

    ThisForm.ShowError

    * Rollback the transaction

    = SQLRollback(ThisForm.nHandle)

  Else

<code intentionally left out>

If the user is not adding a new member the code is somewhat simpler. A transaction is still started. The information in the Member table is saved first and then the information in the Adult and Juvenile tables is saved. If everything worked the transaction is committed. If not it is all rolled back.

Else

  Wait Window "Saving member information..." NoWait


  * Begin a transaction

  = SQLSetProp(ThisForm.nHandle, "Transactions", 2)


  * Save the information to the member table

  Select vMemberTable

  If Not TableUpdate()

<code intentionally left out>


  * Save the information to the adult table

  Select vAdultTable

  If Not TableUpdate()

<code intentionally left out>


* Everything worked so commit the transaction.

  If SQLCommit(ThisForm.nHandle) < 0

    ThisForm.ShowError

    * Rollback the transaction

    = SQLRollback(ThisForm.nHandle)

  Else

    = MessageBox("This member's information has been " + ;

                 "saved.", MB_ICONINFORMATION)

  Endif

Endif

Deleting a Member

Using one view per table not only allows you to solve the problem of adding a new member, it also allows you to solve the problem of not being able to delete members. When the user presses the Delete button a transaction is started. The member's row from the Adult or Juvenile table is deleted first. The Delete is applied to the view and the TableUpdate() sends it to SQL Server. If the TableUpdate() doesn't work the transaction is rolled back.

* Start a transaction on the view connection

= SQLSetProp(ThisForm.nHandle, "Transactions", 2)


If RecCount("vJuvenileTable") = 0

  Select vAdultTable

  Delete

Else

  Select vJuvenileTable

  Delete

Endif  

If Not TableUpdate()

  ThisForm.ShowError

  * Rollback the transaction

  = SQLRollBack(ThisForm.nHandle)

<code intentionally left out>

If the corresponding Adult or Juvenile record has already been removed you can remove the Member table record. The Delete may still fail, for instance if this member has outstanding loans. In that case the transaction is rolled back and the Adult or Juvenile record is put back into the relevant table.

Select vMemberTable

Delete

If Not TableUpdate()

  ThisForm.ShowError

  * Rollback the transaction

  = SQLRollBack(ThisForm.nHandle)

<code intentionally left out>

If both of the TableUpdate() functions worked then the transaction is committed with the SQLCommit() function. If that doesn't work the transaction is rolled back.

Issues

Two of the three problems with the first version of the form are solved here. New members can be added and members can be deleted. If referential integrity is violated the deletion will fail. For example, suppose you try to delete a member who has no associated juveniles but does have outstanding loans. You will see the error shown in Figure 5.

Figure 5. Error message when member with loans is deleted

So the problem of the incoherent error messages still remains. You could write code to parse the error messages and look for certain phrases. You could then translate the SQL Server messages into something a user would want to see. This is a lot of work and requires a thorough familiarity with SQL Server error messages. It also makes it quite difficult to port your application to other back-end databases, for example Oracle.

Using SQL Pass-Through

The alternative to using views is to rely solely on SQL pass-through. This means that you send SQL statements to the back-end and explicitly tell it what to do. If you want to add a record you send an Insert. To save a record you send an Update. This obviously involves more work than using views. It does however allow you to be in complete control over what happens and when it happens.

Loading the Form

The form MEMBEXEC.SCX is the same form used above, only it uses SQL pass-through instead of views. The following code is in the form's Load method.

Open Database library

ThisForm.nHandle = SQLConnect('cnLibrary')

If ThisForm.nHandle < 0

  ThisForm.ShowError

  ThisForm.lConnected = .F.

  Else

  lcSQL = "Select member.member_no, lastname, firstname, " + ;

          "  middleinitial, street, city, state, zip, " + ;

          "  phone_no, expr_date, birth_date = null, " + ;

          "  adult_member_no = null " + ;

          "From member, adult " + ;

          "Where member.member_no = -99 "

  If SQLExec(ThisForm.nHandle, lcSQL, "c_member") < 0

    ThisForm.ShowError

    ThisForm.lConnected = .F.

  Endif

    = CursorSetProp("Buffering", DB_BUFOPTRECORD, 'c_member')

Endif

When the form is loaded SQLConnect() is used to establish a connection to SQL Server using the cnLibrary connection, which is stored in the Visual FoxPro Library database. If the SQLConnect() fails there is not much to do except quit and go home.

If the connection works, an empty cursor is created by sending to SQL Server a Select statement looking for member number -99. Visual FoxPro creates the cursor even though no rows are returned. Optimistic buffering is then set on this cursor. The reason for doing this is to be able to use a buffered cursor on this form. This gives back some of the ease of use of views.

Locating a Member

When views are used on the form, locating a member is as simple as plugging in a value for the view's parameter and requerying. When SQL pass-through is used it is a bit more complex. To retrieve member information a Select statement is constructed and sent to the server. You can see in the code below that this Select is a Union. The member, if he or she exists, is in the Member table and in either the Adult or Juvenile table.

lcSQL = "Select member.member_no, lastname, firstname, " + ;

        "  middleinitial, 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(ThisForm.txtMemberID.Value) + " " + ;

        "Union " + ;

        "Select member.member_no, lastname, firstname, " + ;

        "  middleinitial, 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(ThisForm.txtMemberID.Value)

  

If SQLExec(ThisForm.nHandle, lcSQL, "c_member") < 0

<code intentionally left out>

If the c_member cursor is empty there is no member with the entered ID. Otherwise all the member information is in the cursor. Row buffering is set for the cursor and the form controls are then populated with the member information in the cursor.

The Union allows you to send one Select to the server and get all the information for the member. In the previous examples either two or three views were requeried whereas here one SQLExec() is all that is required. Note that you cannot create a remote view with a Union if you use the View Designer. You could however create the view by hand using the Create SQL View command.

Adding an Adult

When the user presses the Add button a blank record is added to the c_member cursor. This is no different than in the previous view examples.

Select c_member

= TableRevert()

Append Blank

Solely for reasons of readability, the code to add a new member is located in the Form method AddMember. Because adding a member involves adding rows to two tables a transaction is started. Just as in the forms with views, the SQLSetProp() function is used to start the transaction.

= SQLSetProp(ThisForm.nHandle, "Transactions", 2)

When using views to access remote data you can rely on Visual FoxPro to do most of the behind the scenes work for you. For example, in the previous forms you saw that to send an Insert or Update to the server all you had to do was issue a TableUpdate(). The syntax of the Insert or Update was created for you by Visual FoxPro.

The form here uses the SQLExec() function to send SQL statements to the server. That means that you have to construct the SQL statements yourself. After the transaction is started an Insert statement is constructed to add the new row to the Member table.

* Add the new member to the member table

lcSQL = "Insert member (lastname, firstname, " + ;

                       "middleinitial, photograph) " + ;

        "Values ('" + ;

             AllTrim(ThisForm.txtFirstName.Value) + ;

             "', '" + ;

             AllTrim(ThisForm.txtLastName.Value) + ;

             "', '" + ;

             AllTrim(ThisForm.txtMiddleInitial.Value) + ;

             "', " + "NULL)"

If SQLExec(ThisForm.nHandle, lcSQL) < 0

  ThisForm.ShowError

  * Rollback the transaction

  = SQLRollback(ThisForm.nHandle)

  Return

Endif

You now need to know the member_no SQL Server assigned the new member. The code to do this is the same as in the previous form.

* Find out the member_no of the new member

If SQLExec(ThisForm.nHandle, "Select @@Identity") < 0

  <code intentionally left out>


nNewMemberID = sqlresult.exp

An Insert is then constructed to add the new row to the Adult table. The @@Identity value from the server is used in this Insert to correctly link the Adult row to the Member row.

* Add the new member to the adult table

lcSQL = "Insert adult (member_no, street, city, state, " + ;

                      "zip, phone_no, expr_date) " + ;

        "Values (" + AllTrim(Str(nNewMemberID)) + ", '" +;

             AllTrim(ThisForm.txtStreet.Value) + ;

             "', '" + ;

             AllTrim(ThisForm.txtCity.Value) + ;

             "', '" + ;

             AllTrim(ThisForm.txtState.Value) + ;

             "', '" + ;

             AllTrim(ThisForm.txtZip.Value) + ;

             "', '" + ;

             AllTrim(ThisForm.txtPhoneNumber.Value) + ;

             "', "'" + ;

             TToC(DToT(GoMonth(Date(),12))) + "' )"

If SQLExec(ThisForm.nHandle, lcSQL) < 0

<code intentionally left out>

As before, if everything worked the transaction is committed. If not it is all rolled back.

Saving Changes

The code to save an existing member's information is in the Form method UpdateMember. To save information an Update statement is sent to the server. The form of the Update statement is as follows:

Update <table> Set <column1> = <value1>,

                   <column2> = <value2>,

                   etc

It is pretty straightforward, although a bit bulky, to construct an Update statement to send to the server. You know the names of the table's columns and the values are sitting in controls on the form. You can just build the Set part of the Update one column after another. However, you want to be a bit more clever than that. You don't want to waste SQL Server's time updating a column that has not changed. The code here uses the OldVal() function, made possible by the use of the buffered cursor, to check the value of each field in the cursor to its original value. Only if it has changed does it become part of the Update statement. By the way, remote views do this automatically.

lcSQL = ""

* Update this member in the member table

If c_member.firstname <> OldVal("c_member.firstname")

  lcSQL = lcSQL + "firstname = '" + ;

          AllTrim(ThisForm.txtFirstName.Value) + "', "

Endif

If c_member.lastname <> OldVal("c_member.lastname")

  lcSQL = lcSQL + "lastname = '" +;

          AllTrim(ThisForm.txtLastName.Value) + "', "

Endif

<code intentionally left out>

If none of the columns in the Member table have changed, the lcSQL variable will be empty and there is nothing to save for that table. If there is data to save, the rest of the Update statement is constructed and is then sent to the server.

If Len(lcSQL) > 0

  * Add the Update, strip off the last comma, 

  * add a Where clause

  lcSQL = "Update member Set " + ;

          Left(lcSQL, Len(lcSQL) - 2) + ;

          "Where member_no = " + ;

          AllTrim(ThisForm.txtMemberID.Value)

  If SQLExec(ThisForm.nHandle, lcSQL) < 0

<code intentionally left out>

The same process as above then occurs for the Adult table. The next part should by now be very familiar. Commit if it all worked and rollback if it didn't.

Deleting a Member

One of the benefits of using SQL pass-through rather than remote views is that you gain control over what happens and when it happens. The code run when the user clicks the Delete button is a good example of this.

There are various reasons you might not be able to delete a member. If the member has associated juveniles or if the member has outstanding loans any Delete will fail. You can easily determine if either of these is true by sending a Select statement to the server. The code here uses SQLExec() to check for both of these conditions. If either is true a user-friendly message is displayed and nothing further happens.

* First check to see if this is an adult with 

* active juveniles

lcSQL = "Select member_no From juvenile " + ;

        "Where adult_member_no = " + ;

        ThisForm.txtMemberID.Value

If SQLExec(ThisForm.nHandle, lcSQL) < 0

  ThisForm.ShowError

  Return

Else

  If RecCount("sqlresult") <> 0  

    lcMessage = "This member can not be deleted. " + ;

                "He/she is an adult with active juveniles."

    = MessageBox(lcMessage, MB_ICONINFORMATION)

    Return

  Endif  

Endif


* Now check to see if this member has active loans

lcSQL = "Select member_no From loan " + ;

        "Where member_no = " + ;

        ThisForm.txtMemberID.Value

If SQLExec(ThisForm.nHandle, lcSQL) < 0

  ThisForm.ShowError

  Return

Else

  If RecCount("sqlresult") <> 0  

    lcMessage = "This member can not be deleted. " + ;

                "He/she has active loans."

    = MessageBox(lcMessage, MB_ICONINFORMATION)

    Return

  Endif  

Endif

If there are additional checks that need to be performed that code can be placed after the code above. You have complete control over what you check and what order the checks are run. If all the checks succeed and the member can be deleted a transaction is started.

There are relationships defined between the Member table and the Loanhist and Reservation tables. Loanhist contains a row for every book borrowed and returned by the member. The Reservation table contains a row for every book the member has on reserve. If the member is deleted the associated information in those two tables needs to be removed. It needs to deleted first or else there will be a referential integrity violation.

* Delete the loan history records for this member

lcSQL = "Delete loanhist Where member_no = " + ;

        AllTrim(ThisForm.txtMemberID.Value)

If SQLExec(ThisForm.nHandle, lcSQL) < 0

<code intentionally left out>


* Delete the loan reservation records for this member

lcSQL = "Delete reservation Where member_no = " + ;

        AllTrim(ThisForm.txtMemberID.Value)

If SQLExec(ThisForm.nHandle, lcSQL) < 0

<code intentionally left out>

To delete an adult member you have to delete the row in the Adult table first and then you can delete the row in the Member table. This is still part of the transaction so if anything goes wrong everything is rolled back.

* Delete the member

lcSQL = "Delete adult Where member_no = " + ;

        AllTrim(ThisForm.txtMemberID.Value)

If SQLExec(ThisForm.nHandle, lcSQL) < 0

<code intentionally left out>


lcSQL = "Delete member Where member_no = " + ;

        AllTrim(ThisForm.txtMemberID.Value)

If SQLExec(ThisForm.nHandle, lcSQL) < 0

<code intentionally left out>

If all of the Deletes worked then the entire transaction is committed and the member is removed. The user should then be looking at an empty screen so a blank record is added to c_member and the form is refreshed.

Issues

What are the tradeoffs between using remote views and using SQL pass-through?

More work

Clearly, using SQL pass-through is more work than using remote views. Views do a lot of the work for you, taking care of communicating with the server and passing Inserts, Updates, and Deletes. Views are easy to set up and easy to use.

More control

You saw in the two view examples that one of the problems with using remote views is that you have little control over the communication between Visual FoxPro and the back-end. With many database schemas this might not be a problem. However, with the schema used here an issue arose. The problem was mitigated by using one view per table but the point remains. There may be times when views do not give you the power you need for data entry.

When you use SQL pass-through you have complete control over how Visual FoxPro talks to the back-end. You construct the SQL statements and then use SQLExec() to send them to the server. If validations need to be performed or business rules need to checked you decide when and how they occur.

Error messages can be made user-friendly

Because you are in control of what happens and you are validating by hand you have control over the error messages. You will essentially intercept the SQL Server errors and give the users an understandable message when something predictable happens. You still have the issue of a SQLExec() failing due to unpredictable occurrences, such as a network failure. For those you can decide if you want to parse the messages or display them in their raw form.

On the one hand this provides less interoperability

A downside to this approach is that it sacrifices interoperability to a certain degree. The SQL statements sent to the back-end via the SQLExec() are written in the back-end's language. The examples here were designed to work with SQL Server. How much of a rewrite would be involved to port this application to Oracle?

It is true that the basic form of a Select, Insert, Update, or Delete doesn't vary much from back-end to back-end. So the example here might be easily ported. However, the point is an important one and depending on the complexity of the SQL statements you use you might limit your ability to swap back-ends. Of course, if the application is written for one and only one back-end this will not be a big issue.

On the other hand this provides more interoperability

Consider what happens if you are using remote views and you try to delete a member who has outstanding loans. Referential integrity defined on the server will prevent the deletion. However, the error message sent back by Oracle will differ from the message sent back by SQL Server. You could parse the messages and translate them into a user-friendly form but you will have to parse differently for each back-end. This limits your interoperability because you will have to create parsing routines for each server.

With the SQL pass-through approach you would send a Select statement to the back-end looking for this member in the Loan table. If the Select finds a row the member can't be deleted. The message shown to the user is the same regardless of what was sent in the SQLExec(). This serves to increase your interoperability, assuming that the form of the Selects, Inserts, Updates and Deletes doesn't vary greatly among the various back-ends, which is a reasonable assumption.

Using Stored Procedures

In the approach just reviewed, the work is split between the client and the server. The server does the actual data manipulation, which involves searching for records and adding, updating and deleting records. However, the client controls the logic, telling the server what to do and what order to do it in.

An alternative approach is to let the server not only do the work but also control the logic. This is accomplished by relying on stored procedures. A stored procedure is a precompiled set of SQL statements. Because they are precompiled there is a performance gain to using them. In addition, they are essentially black box routines.

The SQLExec() function can be used to send not only SQL statements such as Insert and Update, but also can be used to tell SQL Server to run a stored procedure. Visual FoxPro tells the server what stored procedure to run but it doesn't know what is in the various procedures. Visual FoxPro only knows, and in fact only needs to know, whether or not the stored procedures worked.

Loading the Form

The form MEMBPROC.SCX uses stored procedures to talk to SQL Server. SQLExec() is still used so a connection to the server needs to be established in the Load method of the form. This also occurred in the previous example and in fact the Load method of both forms is exactly the same.

Locating a Member

A SQL Server stored procedure called GetMember is used to retrieve information on a member. This stored procedure takes as a parameter the ID of the member whose information you want. This stored procedure is called when the user presses the Locate button.

If SQLExec(ThisForm.nHandle, "Execute GetMember " + ;

           ThisForm.txtMemberID.Value, "c_member") < 0

  ThisForm.ShowError

  Return

Endif

The stored procedure runs a Select and therefore returns a result set to the cursor c_member. If the cursor is empty then no member with that ID was found. Otherwise the member's information is in the fields of the cursor and can then be displayed on the form.

The SQL Server code that created the GetMember stored procedure is as follows. You can see that it takes one parameter and then runs a Union Select to find the member's information.

CREATE PROCEDURE getmember 

  @member_no member_no

AS

  SELECT member.member_no, firstname, lastname,

         middleinitial, 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 = @member_no

  UNION

  SELECT member.member_no, firstname, lastname,

         middleinitial, 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 juvenile.adult_member_no = adult.member_no

    AND member.member_no = @member_no

  RETURN (@@ROWCOUNT)

Adding an Adult

The code to add an adult member is in the form method AddMember. The code calls the stored procedure AddAdult, which takes as parameters the name, address, and phone information for the new member.

lcSQL = "Execute addadult " '" + ;

        AllTrim(ThisForm.txtLastName.Value) + ;

        "', '" + ;

        AllTrim(ThisForm.txtFirstName.Value) + ;

        "', '" + ;

        AllTrim(ThisForm.txtMiddleInitial.Value) + "', " + ;

        "'" + AllTrim(ThisForm.txtStreet.Value) + "', " + ;

        "'" + AllTrim(ThisForm.txtCity.Value) + "', " + ;

        "'" + AllTrim(ThisForm.txtState.Value) + "', " + ;

        "'" + AllTrim(ThisForm.txtZip.Value) + "', " + ;

        "'" + AllTrim(ThisForm.txtPhoneNumber.Value) + "'"

If SQLExec(ThisForm.nHandle, lcSQL) < 0

  ThisForm.ShowError

Else

  = MessageBox("This member has been added.", ;

               MB_ICONINFORMATION)

  * Find out the member_no of the new member

  ThisForm.txtMemberID.Value = AllTrim(Str(sqlresult.exp1))

<code intentionally left out>

The SQL Server code used to create the AddAdult stored procedure is as follows:

CREATE PROCEDURE addadult

  @lastname         shortstring,

  @firstname        shortstring,

  @middleinitial    letter = NULL,

  @street           shortstring = NULL,

  @city             shortstring = NULL,

  @state            statecode = NULL,

  @zip              zipcode = NULL,

  @phone_no         phonenumber = NULL

AS 

  DECLARE  @member_no  member_no


  IF  @lastname = NULL OR @firstname = NULL OR

      @street = NULL OR @zip = NULL 

    BEGIN

      PRINT "   You must provide Last name, First name,"

      PRINT "   Street, City, State, and Zip."

      RETURN

    END


  BEGIN TRANSACTION


  INSERT member (lastname,  firstname,  middleinitial,

                 photograph)

  VALUES (@lastname, @firstname, @middleinitial, NULL )

  IF @@error != 0

    BEGIN

      ROLLBACK TRAN

      RAISERROR('The new member was not added. Feel free 

                 to try again.',16,1)

      RETURN

    END


  SELECT @member_no = @@Identity

  INSERT adult ( member_no,  street,  city,  state,  zip,  

                 phone_no, expr_date)

  VALUES (@member_no, @street, @city, @state, @zip, 

          @phone_no, dateadd(year,1,getdate()) )

  IF @@error != 0

    BEGIN

      ROLLBACK TRAN

      RAISERROR('The new member was not added. Feel free 

                 to try again.',16,1)

      RETURN

    END


  PRINT "        *** Adult Member added ***"

  SELECT "This member is number ", @member_no


  COMMIT TRANSACTION

This stored procedure takes the name, address, and phone number information as parameters. If the name or address fields are not supplied then the stored procedure won't continue. They are defined in the Member and Adult tables as Not Null. The stored procedure begins a transaction and adds a new row to the Member table. It then issues a Select to store the value in @@Identity to the @member_no variable. That value is then used when the new row is added to the Adult table.

If anything goes wrong the transaction is rolled back and an error number and message is sent back. This is needed so that Visual FoxPro knows the stored procedure failed. This causes the SQLExec() function to return a -1, which is how you know it didn't work.

It turns out that the Select used to get the value of @@Identity creates a result set and that this is sent back to Visual FoxPro. This is a happy coincidence as this causes the SQLExec() to create a result set cursor called sqlresult. The value in the exp field in that cursor is the value of @@Identity and is easily retrieved.

This is good for another reason. If you waited until the stored procedure had run and then used SQLExec() to ask SQL Server for the value of @@Identity it would come back empty. This is because the second thing the stored procedure does is insert a row into the Adult table, which doesn't have an Identity column. This causes the value of @@Identity to be set to Null. So if the stored procedure didn't send back the @@Identity after the Insert into Member then the information would have been lost.

Saving Changes

The code to save a member's information is in the form method UpdateMember. The code calls the stored procedure UpdateAdult, which takes as parameters the member ID and the name and new value of any fields that need to be updated.

lcSQL = ""

If c_member.firstname <> OldVal("c_member.firstname")

  lcSQL = lcSQL + " @firstname = '" + ;

          AllTrim(ThisForm.txtFirstName.Value) + "', "

Endif

If c_member.lastname <> OldVal("c_member.lastname")

  lcSQL = lcSQL + " @lastname = '" + ;

          AllTrim(ThisForm.txtLastName.Value) + "', "

Endif

<code intentionally left out>


If Len(lcSQL) > 0

  * Strip off the last comma

  lcSQL = Left(lcSQL, Len(lcSQL) - 2)

  lcSQL = "Execute updateadult @member_no = " + ;

          AllTrim(ThisForm.txtMemberID.Value) + ;

          ", " + lcSQL

Else

  = MessageBox("There is nothing to save.", ;

               MB_ICONINFORMATION)

  Return

Endif          


If SQLExec(ThisForm.nHandle, lcSQL) < 0

  ThisForm.ShowError

<code intentionally left out>

The SQL Server code used to create the UpdateAdult stored procedure is as follows:

CREATE PROCEDURE updateadult

  @member_no        member_no,

  @lastname         shortstring = NULL,

  @firstname        shortstring = NULL,

  @middleinitial    letter = NULL,

  @street           shortstring = NULL,

  @city             shortstring = NULL,

  @state            statecode = NULL,

  @zip              zipcode = NULL,

  @phone_no         phonenumber = NULL

AS

  DECLARE @sqlstring1    varchar(255)

  DECLARE @sqlstring2    varchar(255)


  IF  @lastname      = NULL AND @firstname     = NULL AND

      @middleinitial = NULL AND @street        = NULL AND

      @city          = NULL AND @state         = NULL AND

      @zip           = NULL AND @phone_no      = NULL 

  BEGIN

    PRINT "Nothing to do."

    RETURN

  END


  SELECT @sqlstring1 = NULL

  SELECT @sqlstring2 = NULL


  IF  @lastname != NULL 

      SELECT @sqlstring1 = @sqlstring1  + "lastname = '" + 

             @lastname + "',"

  IF  @firstname != NULL

      SELECT @sqlstring1 = @sqlstring1  + "firstname = '" + 

             @firstname + "',"

  <code intentionally left out>


  BEGIN TRANSACTION


  IF @sqlstring1 != NULL

    BEGIN

      SELECT @sqlstring1 = "UPDATE member SET " + 

             SUBSTRING(@sqlstring1, 1, 

             DATALENGTH(@sqlstring1) - 1) + 

             " WHERE member_no = " + 

             CONVERT(char(6), @member_no)

      EXECUTE (@sqlstring1)

      IF @@error != 0

       BEGIN

         ROLLBACK TRAN

         RAISERROR('The member information was not saved. 

                    Feel free to try again.',16,1)

         RETURN

       END

    END

    

  IF @sqlstring2 != NULL

    BEGIN

      SELECT @sqlstring2 = "UPDATE adult SET " + 

             SUBSTRING(@sqlstring2, 1, 

             DATALENGTH(@sqlstring2) - 1) + 

             " WHERE member_no = " + 

             CONVERT(char(6), @member_no)

      EXECUTE (@sqlstring2)

      IF @@error != 0

       BEGIN

         ROLLBACK TRAN

         RAISERROR('The member information was not saved. 

                    Feel free to try again.',16,1)

         RETURN

       END

    END

    

  COMMIT TRANSACTION

The member ID is a required parameter for this stored procedure. It couldn't construct an Update statement without some way of identifying the member's record. The other parameters are optional and will default to Null if they are not passed. If they are all Null then there is nothing to do. Note that the Visual FoxPro code will not call the stored procedure if nothing has changed.

The stored procedure then constructs two Update statements, one for the Member table and one for the Adult table. For efficiency, only the columns that have changed are included. The Update statements are stored to the variables @sqlstring1 and @sqlstring2. The Execute statement parses the strings and then executes that code. This capability of the Execute statement is a very popular feature that first appeared in SQL Server 6.0.

Deleting a Member

When the user presses the Delete button SQLExec() is used to tell SQL Server to run the stored procedure RemoveMember, which takes the member's ID as a parameter.

If SQLExec(ThisForm.nHandle, "Execute RemoveMember " + ;

           ThisForm.txtMemberID.Value) < 0

  ThisForm.ShowError

Else

<code intentionally left out>

The SQL Server code to create the RemoveMember stored procedure is as follows:

CREATE PROCEDURE removemember

  @member_no        member_no

AS 

  IF NOT EXISTS

     (SELECT * FROM member WHERE member_no = @member_no) 

  BEGIN

      PRINT " Member number not found in Member table."

      RETURN

  END


  IF EXISTS (SELECT member_no FROM juvenile

             WHERE @member_no = adult_member_no)

  BEGIN

      RAISERROR('This member can not be deleted. He/she is

                 an adult with active juveniles.',16,1)

      RETURN

  END


  IF EXISTS (SELECT member_no FROM loan

             WHERE member_no = @member_no)

  BEGIN

      RAISERROR("This member can not be deleted. He/she has

                 active loans.",16,1)

      RETURN

  END


  BEGIN TRANSACTION


  IF EXISTS (SELECT member_no FROM loanhist

             WHERE member_no = @member_no)

  BEGIN

      PRINT 'Deleting Loan History information'

      DELETE loanhist WHERE member_no = @member_no 

      IF @@error != 0

        BEGIN 

          ROLLBACK TRAN

          RETURN

        END

  END


  IF EXISTS (SELECT member_no FROM reservation

             WHERE member_no = @member_no)

  BEGIN

      PRINT 'Deleting Loan Reservation information'

      DELETE reservation WHERE member_no = @member_no 

      IF @@error != 0

        BEGIN 

          ROLLBACK TRAN

          RETURN

        END

  END


  IF EXISTS (SELECT member_no FROM juvenile

             WHERE member_no = @member_no)

  BEGIN

      DELETE juvenile WHERE member_no = @member_no

      IF @@error != 0

        BEGIN 

          ROLLBACK TRAN

          RETURN

        END

  END

  ELSE IF EXISTS (SELECT member_no FROM adult

                  WHERE member_no = @member_no)

  BEGIN

      DELETE adult WHERE member_no = @member_no

      IF @@error != 0

        BEGIN 

          ROLLBACK TRAN

          RETURN

        END

  END


  DELETE member WHERE member_no = @member_no

  IF @@error != 0

    BEGIN 

      ROLLBACK TRAN

      RETURN

    END


  COMMIT TRANSACTION

This stored procedure first checks to see if the member exists. It then checks to see if this is an adult with active juveniles or a member with outstanding loans. If so the member can not be deleted and the stored procedure sends back an error and user friendly message. If the deletion can occur a transaction is begun and the member's information is removed from the Loanhist, Reservation, Juvenile, Adult and Member tables. As always, if anything goes wrong the whole thing is rolled back.

Issues

How does SQL pass-through with hand crafted SQL statements compare to SQL pass-through with stored procedures?

This is less work

Assuming the stored procedures already exist there is much less work for the Visual FoxPro programmer to do in this scenario. All you need is to know is which stored procedure to call, what parameters it takes and what it returns. You do not need to know how the stored procedure works or in what order it does things.

You have less control in Visual FoxPro

Because all of your access to SQL Server occurs through stored procedures you have very little control from Visual FoxPro over what happens on the server. Of course, assuming that the stored procedures do what they are supposed to do, you probably won't mind this.

You have more control in SQL Server

If you are responsible not only for the Visual FoxPro front-end but also the SQL Server back-end then you have control either way. However, what if you are just writing a Visual FoxPro front-end to SQL Server data and that data is controlled by someone else? Providing access to the data solely through stored procedures is a great way for the owners of the SQL Server data to exercise control over that data. They can rest assured that the front-end developers don't mess up the data. This would come in extremely handy if there is a Visual FoxPro, a Visual Basic and an Access front-end to the same data.

This provides more interoperability

Because all the work is done in the stored procedures it should be very easy to switch back-ends. At the worst you would have to change each line of code containing a SQLExec() to reflect a different syntax for calling stored procedures.

Conclusion

In this session you have seen examples of using both views and SQL pass-through for building client-server applications using Visual FoxPro. The obvious question is when should you use one or the other?

Using remote views has the benefit of being relatively simple because Visual FoxPro does a lot of the work for you. You can use standard Visual FoxPro functions such as TableUpdate() for data entry. FoxPro handles the communications with the back-end server. A downside to using views is that you give up a large degree of control.

A particularly good use of views is for reporting. The View Designer is a very easy way to construct many Select statements. It is also a great way to construct heterogenous queries, where some of the data is coming from the back-end server and some is coming from local Visual FoxPro tables, for instance lookup tables.

Using SQL pass-through and sending SQL statements directly to the back-end gives you total control. You create the SQL statements that get run. You can still use buffered cursors and get much of the convenience of views. The cost however is the work involved in constructing the SQL statements. Calling stored procedures on the back-end via SQL pass-through greatly reduces the burden of writing SQL statements but also takes away your control of what happens and when.

The purpose of this session is not to convince you to use either views or SQL pass-through. Rather, you should consider both of them to be powerful tools you can use to build applications. If your data structures and validation requirements are not complex then you should consider using views. If you need more control or views can't handle something you want to do you should consider SQL pass-through. You can also use both whenever that makes sense.