Integrating SQL Server with Visual FoxPro
Updated : July 19, 2001
By Robert Green
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.
This session will explore 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 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 1.
.gif "Cc966498.sqlfox1(en-us,TechNet.10).gif")
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 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.
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.
.gif "Cc966498.sqlfox2(en-us,TechNet.10).gif")
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.
.gif "Cc966498.sqlfox3(en-us,TechNet.10).gif")
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.
.gif "Cc966498.sqlfox4(en-us,TechNet.10).gif")
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.
.gif "Cc966498.sqlfox5(en-us,TechNet.10).gif")
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?
This is 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.
You have 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.