IN THIS CHAPTER
In the preceding two chapters, we looked at how to build a simple Windows Forms application in Visual FoxPro and in Visual Basic .NET. You learned how to use either local tables or SQL Server in both environments. You saw how FoxPro requires additional coding to use SQL Server, whereas Visual Basic .NET treats local tables and SQL Server tables exactly the same way. Until a few years ago, those were the two technologies we had to offer our clients.
That has changed. Without a doubt, the Internet is the biggest thing that's ever happened to database application development. Using Internet-enabled applications, users can access their data from anywhere that has a telephone connection. With broadband access, the user experience is almost identical to that of a local area network user.
For several years, developers of Windows Forms database applications were at a disadvantage when it came to building Internet-enabled database apps. Active Server Pages (ASP) was easy to learn, easy to use, and free. That's a hard combination to beat.
ASP is a Microsoft technology that generates Web pages containing embedded data. Input fields within forms are used to send data back to the server. Inside a Web page that collects data, a statement like <form target="xxx.asp"> directs the contents of the page to an ASP program (named xxx.asp here) that extracts the contents of any input fields and uses them to update tables in a database.
But ASP and even its successor ASP.NET are not the only answer. In fact, for most applications, they're not even a good answer. Browsers like Internet Explorer and Netscape
Navigator have remarkable capabilities, but they're best for distributing a simple interface to a large number of unknown users. When it comes to complex applications like billing and purchasing systems, sophisticated inventory or payroll applications, and other programs that might accomplish a task in a few seconds or in an hour depending on the capabilities of the user interface, browser clients are a poor choice.
Applications having only a few users who need precise combinations of information and more complex controls such as combo boxes, tree views, and filtered lists are difficult to build for a browser environment. A smart client application (the latest name for a database application that connects to a database over the Internet) can allow users to do the same task in seconds instead of minutes. Repeat that task hundreds of times a day, and a smart client application can soon achieve savings that will pay for the cost of development in short order.
Development costs are another saving. Paradoxically, although browser applications tend to be simpler in the sense that a single screen may only do one or two things rather than the entire set of operations that a user needs to do, they also take longer to design and code. And smart clients are cheaper to maintain as the inevitable requests for changes are made.
Finally, besides being slower than their rich client cousins and costing more to develop and maintain, browser applications send their contents as plain text. That's no way to distribute the company's sensitive information; somewhere, some hacker will intercept and read their contents.
For all three of these reasons, many developers feel that thin client application development has been hugely oversold. There are several reasons that come to mind.
IT managers who are tired of the problems inherent in deploying traditional Windows applications to operating systems that may range from Windows 95 through Windows XP and beyond are partly to blame.
In addition, it can be very difficult to diagnose problems in your own Windows software when someone else's installation overwrites a DLL that your application depends on, and that certainly makes a technology that doesn't need to be deployed appear even more attractive.
Finally, people who bought a book on ASP and learned it in a week and who are excited by the salaries that database developers earn are also guilty of overestimating the applicability of ASP to database development.
You can't draw a picture of a car and drive it away, but building a Web page that sort of works is pretty easy. However, it will seldom duplicate the performance of an executable. User complaints don't begin until a poorly designed system is deployed, and by then it's too late. Windows Forms applications with well-designed interfaces based on well-designed databases provide the best combination of cost, performance, and features.
Browser applications have their place, but in time users will come to realize that it's a pretty small place. For everything else, there's rich client.
Still, getting access to data has never been easy. Traditional Visual Basic applications used recordsets internally, but recordsets aren't files, but rather in-memory structures that can't easily be sent across the Internet.
XML solves that problem. XML files are text files that express all data fields as strings delimited by tags that reflect the name of the field that the data came from. An inline schema included at the beginning of the file (or named internally and provided in another file) can describe how to reconstruct the table, if it's needed. However, when sending a known record structure to a known client, the table structures are usually known in advance. So XML is a simple way to send data from a server to a client and back again.
However, as you'll see in the examples shown in this chapter, you can send DBFs and FPTs across the Internet, and they work great. When zipped and encrypted, they provide data security in about one-seventh the space required for the equivalent XML. I wonder why Microsoft didn't decide to use them as the basis for Internet data interchange as a complement to XML. You'll have to ask them.
Microsoft and others developed Simple Object Access Protocol (SOAP) to enable remote access to a data server. SOAP provides descriptions of the functions and procedures available on a server in such a way that the client can discover what is available from a Web service and exchange data with it. A Web Services Description Language (WSDL) file is available at the server and can be queried by the client application to discover available services and their required parameters.
The implications for database applications are tremendous. You can open up an empty table structure and go to a Web server for data. After modifying a record, or after entering a new record, you can send it back to the Web server to update the appropriate database table. Deleting records is even simpler; you can just send the table name, a key field name and a key value, and the Web service can create and execute a DELETE command on the data table.
With the inclusion of support for XML Web Services in Visual FoxPro 8, FoxPro applications have begun to look like database applications written in .NET languages. Data acquisition involves creating a proxy for the remote Web Service, data translation to and from XML strings, and learning about diffgrams. It's a little different, but the results are spectacular.
In this chapter we'll add Internet access to the data tier developed in our FoxPro application in Chapter 2, “Building Sample Applications in Visual FoxPro and Visual Basic .NET,”and we'll add XML Web Services to our Visual Basic .NET application developed in Chapter 3, “Building a Visual FoxPro Application for SQL Server.” I'm going to describe two methods of building Internet access for FoxPro, one for use with Visual FoxPro 7 and another for use with Visual FoxPro 8. Visual FoxPro 7 has less Web Services support than subsequent versions, and not everyone has upgraded.
I can't talk about Internet-enabled FoxPro applications without mentioning the amazing Web Connection, a product developed by Rick Strahl of West Wind Technologies on Maui. Rick is the pioneer who led the way in showing how to incorporate Web access into FoxPro applications. His shareware product is still the easiest and fastest way to build Internet applications in FoxPro, and in Visual FoxPro 7, it's by far the best way. So the first part of this chapter will describe how to use Web Connection to build an Internet application. In Visual FoxPro 8, Web Services are fully supported, so the Visual FoxPro 8 Web server will be built using native Visual FoxPro 8 capabilities. However, you might still end up concluding that Web Connection is the best way to build Internet support in any version of FoxPro. You be the judge.
Building a Web-enabled smart client in Visual FoxPro is a lot like building a SQL Server application. You use local cursors, get as little data as possible, and pass it around using HTTP.
Visual FoxPro 8 has great support for Internet access. However, many Visual FoxPro developers haven't upgraded, so I'm going to start by showing you how to use Web Connection to build Web-enabled database applications in Visual FoxPro 7. After that, we'll see how Visual FoxPro 8 has added new features that permit development of the same type of applications without using a third-party tool (although you may decide to stick with Web Connection anyway). Finally, we'll see how completely XML Web Services are integrated into Visual Basic .NET, so that very little needs to be done to Web-enable your application.
The first time I read the sentence “The Internet Is The Network,” I thought, with my typical Hungarian cynicism: Right. More marketing BS to sell something. Then I spent a few months with Rick Strahl's Web Connection.
Let me make one thing perfectly clear: The Internet Is The Network.
If you haven't yet built your first Web-enabled database application, please, please take the time to implement the code in this chapter. It will change your life—professionally, at least.
For this example, I'll use the shareware version of Web Connection. Go to www.west-wind.com and download it. When you've finished this chapter, you can either send Rick $129 for your shareware, or $399 for the whole enchilada. I'd point out some of the additional features included in the complete package, any one of which would be worth the whole price of Web Connection, but I would just start sounding like a salesman, which I'm not; I'm just a very, very satisfied customer. Go ahead, download it. I'll wait right here.
Good, you're back. Let's get started.
First, install Web Connection. I say this as one might say “slice a tomato,” but installing Web Connection and configuring your Web server is undoubtedly the most challenging part of this entire exercise. There are four distinct pieces of software involved here, and the database application we're going to write is only one of them. There's Microsoft Internet Information Server (IIS), which comes with Windows but must be installed separately; there is SQL Server, which is a career in itself; and there are the two programs that you're going to write—one for the workstation and the other for the server.
One odd thing you'll soon notice is that the application doesn't have any data files on the client side. That's right, no DBFs. In fact, the entire application when installed, regardless of the number of screens, usually consists of one executable. It's kind of eerie to fire it up and watch data come over the wire. But that's how it works.
It does so by asking a Web server to go to an application server (a FoxPro program that you'll write) that will ask SQL Server (or FoxPro DBFs) for data. The Web server, application server, and SQL Server can be running either on your development machine, on another computer on your LAN, or anywhere on the Internet. During the development phase you'll run both the client and the server on the same computer and then move the server software to a server that can be accessed from anywhere on the Internet.
You must have installed a Web server on the computer before installing Web Connection. The Web server is either Personal Web Server, Internet Information Server 5 (Win2K/XP), or perhaps another such server.
To install Web Connection, extract it to a directory called wconnect on your C: or D: drive, and then run the SETUP.EXE program in the wconnect directory. (When you install it, it will try to run SETUP itself.) It will install the software and prepare two special directories that are used by Web Connection in addition to the wconnect directory where the server program is installed.
Three conditions must be met during the setup process: IIS has to be correctly installed and running; you will have to set it up to point to a virtual directory that contains part of your server software; and the computer's administrative account must have execute rights to the virtual directory where a little program called wc.dll is going to be installed. If any of these three conditions isn't met, you will have zero, I repeat, zero success. And, these three conditions have nothing to do with the FoxPro programs you're going to write.
I've spent days, maybe weeks, verifying these conditions. They are the heartbreak of Internet programming. However, if you follow Rick's instructions carefully, it will eventually work—unless you're trying to run under Windows 98, in which it may or may not work. So I strongly urge you to do all of this on a computer that's using either Win2K or XP for an operating system. That's the bad news.
All of the rest is good news.
The truly peculiar part of writing Internet applications is that part of your program is running on one computer, and part of it is running on another one. Here's how it works.
Let's say you want to populate a grid with customers from the state of California. In a traditional FoxPro LAN application, you would write a SQL query like this:
SELECT Cust_ID FROM CUSTOMER WHERE STATE = 'CA' INTO CURSOR C1
Execute it and bind the resulting cursor to a grid on a form and voilà, you're looking at California customers.
In an Internet application, you construct something like an email that contains the SELECT statement and send it to a URL on another computer. The URL must call a program called wc.dll, which is the Web Connection component. It's one of only two programs in Web Connection that are written in C++; the rest are all written in FoxPro.
ASP supports (among other things) a Request object to read the URL you send to a server, and a Response object to send output back to the requestor. IIS takes care of intercepting the URL, running the program named in it, and returning the output (usually HTML) to the computer that sent the URL.
Wc.dll does the same thing. A Web Connection URL must include the wconnect directory, a reference to wc.dll (the connection that knows what IIS is expecting), a class name, a function name, and any parameters needed by the function. When the URL arrives, wc.dll activates the named class and passes the parameters to the named function in that class. The function first extracts the parameters passed to it using a wwRequest object. (You can't use the traditional PARAMETERS statement used in FoxPro.) The function constructs and runs the query, and then uses a wwResponse object to send back the data in XML format. Your program, meanwhile, is filing its nails and waiting for the data to come back. It sees it, drops the nail file, grabs the data and slaps it into the grid, and voilà redux.
For our example, we'll use the same form that we built in Chapter 2. The only change, it turns out, is in the Data Access Layer.
There are just a few changes to the MAIN program we saw in Chapter 2. They're highlighted in Listing 5.1.
Example 5.1. The Main Program
* Program-ID..: MAIN.PRG
* Purpose.....: MAIN program for application
CLEAR ALL
CLOSE ALL
CLEAR
CLOSE ALL
SET TALK OFF
SET CONFIRM ON
SET MULTILOCKS ON
SET CENTURY ON
SET EXCLUSIVE ON
SET SAFETY OFF
SET DELETED ON
SET STRICTDATE TO 0
WITH _Screen
.AddObject ( [Title1], [Title], 0, 0 )
.AddObject ( [Title2], [Title], 3, 3 )
.Title2.ForeColor = RGB ( 255, 0, 0 )
ENDWITH
Global error handler (called if no TRY...CATCH block
* handles an error, or if THROWn from top TRY..CATCH block.
ON ERROR DO ErrTrap WITH LINENO(), PROGRAM(), MESSAGE(), MESSAGE(1)
DO MENU.MPR
SET PROCEDURE TO DataTier.PRG ADDITIVE
oDataTier = CREATEOBJECT ( [DataTier] )
oDataTier.AccessMethod = [DBF]
* Added for WebConnection support
SET PATH TO WWIPSTUFF
* The next 5 lines refer to Web Connection files
SET CLASSLIB TO wwIPStuff ADDITIVE
SET PROCEDURE TO wwHTTP ADDITIVE
SET PROCEDURE TO wwUtils ADDITIVE
SET PROCEDURE TO WWPOP3 ADDITIVE
SET CLASSLIB TO wwXML ADDITIVE
* The following string was needed because my laptop has a different server name.
* Ordinarily the default "(local)" will be adequate.
*!* oDataTier.ConnectionString = [Driver={SQL Server};]
+ [Server=VAIOVAIO;Database=Northwind;UID=sa;PWD=;]
*!* oDataTier.AccessMethod = [SQL Server] && Last one will be used
IF NOT EMPTY ( oDataTier.AccessMethod )
READ EVENTS
ENDIF
ON ERROR
SET PROCEDURE TO
SET CLASSLIB TO
SET SYSMENU TO DEFAULT
WITH _Screen
.RemoveObject ( [Title1] )
.RemoveObject ( [Title2] )
ENDWITH
DEFINE CLASS Title AS Label
Visible = .T.
BackStyle = 0
FontName = [Times New Roman]
FontSize = 48
Height = 100
Width = 800
Left = 25
Caption = [My application]
ForeColor = RGB ( 192, 192, 192 )
PROCEDURE Init
LPARAMETERS nTop, nLeft
THIS.Top = _Screen.Height - 100 - nTop
THIS.Left= 25 - nLeft
ENDPROC
ENDDEFINE
PROCEDURE ErrTrap
LPARAMETERS nLine, cProg, cMessage, cMessage1
OnError = ON("Error")
ON ERROR
IF NOT FILE ( [ERRORS.DBF] )
CREATE TABLE ERRORS (;
Date Date, ;
Time Char(5), ;
LineNum Integer, ;
ProgName Char(30), ;
Msg Char(240), ;
CodeLine Char(240) )
ENDIF
IF NOT USED ( [Errors] )
USE ERRORS IN 0
ENDIF
SELECT Errors
INSERT INTO Errors VALUES ( ;
DATE(), LEFT(TIME(),5), nLine, cProg, cMessage, cMessage1 )
USE IN Errors
cStr = [Error at line ] + TRANSFORM(nLine) + [ of ] + cprog + [:] + CHR(13) ;
+ cMessage + CHR(13) + [Code that caused the error:] + CHR(13) + cMessage1
IF MESSAGEBOX( cStr, 292, [Continue] ) <> 6
SET SYSMENU TO DEFAULT
IF TYPE ( [_Screen.Title1] ) <> [U]
_Screen.RemoveObject ( [Title2] )
_Screen.RemoveObject ( [Title1] )
ENDIF
CLOSE ALL
RELEASE ALL
CANCEL
ELSE
ON ERROR &OnError
ENDIF
The five lines of code about 45 lines down in Listing 5.1 that are preceded by the comment “Web Connection source code” refer to the Web Connection shareware libraries, which in turn call the functions in WWIPSTUFF.DLL, the heart of the product. I've copied these lines from a startup routine that normally would urge you to send Rick your $129.00 for the shareware each time the program starts. The message has been removed, but please remember that this is shareware. If at the end of this exercise you don't think it's worth the price, I would be very surprised.
The StandardForm class template is shown in Figure 5.1.
I've included in the following snippet the code changes you'll need to make in order to use Internet support:
-End-
That's right. There aren't any code changes.
When we implemented SQL in Chapter 2, we created a cursor to hold the records that we bring back from SQL Server, or the records we add before sending them to SQL Server. These cursors are created in the forms' Load events, before going for data.
An Internet server works exactly the same way, except that now the program goes to the Internet server instead of to SQL Server for the data. It also goes to the Internet server for table structures because that's how cursors are built.
First let's start building the Internet server. Web Connection installs in a directory structure named wconnect, typically on the C: drive. If you installed it on another drive, substitute the correct drive letter wherever you see C:. Beneath wconnect, there are six or seven directories depending on the version. I'm using Web Connection version 4.25 for this book, and at this time the directories are these:
Classes Console FoxCentral HTML Scripts SoapSamples Templates Tools Wwdemo wwDevRegistry wwIPStuff_Samples wwReader wwThreads
The shareware version doesn't install all of these; however, the important ones are Classes and wwDemo. The main directory contains about 15 files, but the one you'll want to look at is called WCDEMOMAIN.PRG. Open it up and take a look. About 225 lines down, you'll find a section that looks like Listing 5.2. (I added the last two lines.)
Example 5.2. Modifying WCDEMOMAIN to Look for Our Web Server Process Class
CASE lcParameter == "WWTHREADS"
DO wwThreads with THIS
CASE lcParameter == "WWDEMO"
DO WWDEMO WITH THIS
CASE lcParameter == "MYDATASERVER"
DO MYDATASERVER WITH THIS
Web Connection organizes tasks into class libraries written as PRGs. There's a wwdemo.prg, a wwthreads.prg, and so forth. Each one of them has pretty much the same structure—a DEFINE CLASS statement, then some housekeeping, and finally a bunch of FUNCTIONs and PROCEDUREs.
You're required to write a process class that contains your own functions. wwDemo.PRG is a sample process class that you can use as a pattern.
When a program needs data, it sends an HTTP call to the server using a syntax like this:
http://www.lespinter.com/wconnect/wc.dll?wwdemo~Function1~Param1
which means “>go to www.lespinter.com, look in the wconnect virtual directory for a program called wc.dll, and use it to go to the wwdemo class library and run function1 using the value Param1 as a parameter.”
In a client application, you use an instance of a class called WWIPSTUFF to make this call. Within that class, a function named HTTPGETEX with three parameters will send the URL and wait for the result. The first parameter is the URL string, the second parameter is the returned string, and the third parameter is a numeric value containing the number of characters in the returned string. It looks like Listing 5.3.
Example 5.3. Attempting to Connect to the Server
lnReturnCode = oIP.HTTPGetEx ( lcURL, lcReturnedString,lnStrLen) IF lnReturnCode <> 0 MessageBox ( [Error connecting to server], 16 ) RETURN ENDIF
lcURL contains the URL, typically an IP address or Domain Name System (DNS) name (for example, 64.68.235.102 or www.lespinter.com), the wconnect virtual directory name, the wc.dll reference ending with a question mark, the process class library name (a .prg file), a function name, and zero or more parameters. The class library, function name, and parameter names are separated by tildes, and are referred to positionally within the server program. That is, QueryString(1) is the class name, QueryString(2) is the function name, and QueryString(3) is the first parameter. The “http://” prefix is not included. Every single call you make to your Web Connection server will look like this.
The process class library is derived from Rick's wwProcess class. You start with a small skeleton and then add your own functions and procedures. wwDemo.PRG is a sample that ships with Web Connection to show you what a process class is.
How do you set up one of these class libraries? One way is to copy wwdemo.prg to another name, erase most of its contents, and start writing your own using the original wwdemo as a guide. But a utility program that comes with WebConnection will build your empty shell class library for you. Run console.exe and follow the instructions. I named mine MyDataServer, so pick a similarly descriptive name.
After you've added your class library, add two lines to wcDemoMain that direct URLs that reference your process class to the corresponding object by adding the two lines of code shown in Listing 5.2, and you're ready to start writing functions.
I've found two peculiarities in using Web Connection that might also give you a few anxious moments: First, before typing DO WCDEMOMAIN, you need to type the following in the command window:
Set path to wwdemo;classes;tools
Second, all of Web Connection is quite dependent on wconnect.h, an include file with named constants representing enums (lists of integer codes) used by various Windows components like the WinSock DLL. It has a specific location, and SET PATH doesn't help FoxPro find it. So if you move anything and then start getting messages that HTTP_WHATEVER is undefined, try copying WCONNECT.H to wherever you're working. That usually does the trick.
Web Connection server functions typically look in either the QueryString or the post buffer for parameters; then they construct a data command (SELECT, INSERT, UPDATE, or DELETE) and execute it; finally, if a SELECT was issued, the results are sent back.
There are five objects in the ASP model. Web Connection has classes that mimic each of them. One mimics the Request object, and another the Response object. I like to instantiate these two objects in my Web Connection code with the names Request and Response, so that I forget which language I'm in and start writing in Visual BasicScript.
There are two ways you can send data to a Web page. One is called GET and the other is called POST. GET means you can see the parameters, whereas POST means you can't.
In an ASP program, the following statement
lcName = request("Name")
will return the value of Name in a URL that looks like this:
http://www.lespinter.com/subscriptions/login.asp?name=JoeBob
Web Connection has a Request object, which works a lot like Internet Explorer's Request object. However, Web Connection uses the slightly more formal syntax
lcEverything = Request.QueryString()
for the entire string, or
lcName = request.QueryString("Name")
for the Name value. However, because these are programs talking to programs, we generally simply separate the parameters with tildes (“~”) and count them, like this:
lcName = request.QueryString(3)
That's slightly confusing because the first parameter is the class library name and the function name is the second one, making your first parameter the third one as far as Web Connection is concerned. You'll get used to it.
Here's an example: The following URL sends the name of a table, a key field, and a value from a user who has just picked a name from a drop-down list of customers in Alabama and wants the customer record:
http://www.lespinter.com/wconnect/wc.dll?MyDataServer~GetOneRecord~Customers~CustID~3012214
The server code to respond to this might be as shown in Listing 5.4.
Example 5.4. Web Connection Function to Return a Single Record
FUNCTION GetOnerecord pTable = Request.QueryString(3) pKeyField = Request.QueryString(4) pKeyValue = Request.QueryString(5) cmd = [SELECT * FROM ] + pTable + [ WHERE ] + pKeyField + [=] + pKeyValue cmd = cmd + [ INTO CURSOR C1] &Cmd CursorToXML ( "C1", "lcXML" ) USE IN C1 USE IN ( pTable ) Response.Write ( lcXML ) ENDFUNC
However, there are limits to the size of the GET string, which I think is about 128 characters. It's irrelevant, though, because we generally don't want to send our data across the Internet in clear text that absolutely anyone can read, so we'll rarely use GET. Instead, we'll use POST, which stores the variables in a bag called the post buffer. They can also be encrypted, which is what you'll eventually want to do with sensitive information.
If you instead stuff values into the post buffer, they're hidden. To add a variable and its corresponding value to the post buffer, in your client program you might have this:
oIP.AddPostKey ( Value, "key" )
The entire URL is then simply
http://www.lespinter.com/wconnect/wc.dll?MyDataServer~ShowMeDaData
Any parameters, records being sent to be stored, or whatever, are no one's business but your own. There's an AddPostKey function for just that purpose, as you'll see shortly.
GET and POST are only important when sending data or requests to a server. Servers send back whatever you want. It can be an HTML page, of course; but it can also be XML, or a DBF, or an encrypted string.
The next few lines are calls to the WWIPSTUFF class library that's included in Web Connection. It essentially sends a request to the server, waiting for a string containing the results to be returned in the variable lcBuffer.
I've chosen to demonstrate two ways to pass data back: a DBF and an XML string. If a DBF is returned, you just use StrToFile() to turn it back into a DBF and APPEND FROM to read it into the CARRIER cursor. If an XML string is created on the server and returned, it's converted back to a cursor when it's received. In either event it takes about a second (see Listing 5.5).
Example 5.5. Using the Post Buffer to Send Parameters to the Web Server
lnConnect = o.HTTPConnect ( Server )
IF lnConnect <> 0
MessageBox ( ;
[Couldn't connect to Internet Server], ;
64, _Screen.Caption )
.cmdCancel.Click
ENDIF
lcBuffer = []
lnBufLen = 0
o.AddPostKey ( [RESET] )
o.AddPostKey ( [ParmC], ParmC )
o.HTTPGetEx ( FuncC, @lcBuffer, @lnBufLen )
SELECT Carrier
ZAP
DO CASE
CASE gcMethod = [DBF]
StrToFile ( lcBuffer, [Carrier.DBF] )
APPEND FROM Carrier.DBF
DELETE FILE Carrier.DBF
OTHERWISE && must be XML
lcXML = ALLTRIM( lcBuffer )
oXML.XMLToCursor ( lcXML,[Carrier] )
ENDCASE
ENDIF
ENDWITH
WITH THISFORM.List1
IF _Tally > 0
SCAN
.AddListItem ( Company )
Row = .NewItemID
.AddListItem ( City, Row, 2)
.AddListItem ( Region, Row, 3)
.AddListItem ( Cust_ID, Row, 4)
ENDSCAN
.ListIndex = 1
.Selected[1] = .T.
THISFORM.cmdSelect.Enabled = .T.
ELSE
THISFORM.cmdSelect.Enabled = .F.
ENDIF
ENDWITH
ENDPROC
The AddPostKey method stuffs strings into the HTTP transport and sends them over. It's the cleanest way to send strings that contain blanks; otherwise you get all of those funny tildes and pluses.
Show All Customers is even easier; however, it's not advisable in most cases. I'm only doing it here because I know there are just a few records in the table (see Listing 5.6).
Example 5.6. Code to Return All Customers
PROCEDURE cmdall.Click
SELECT Carrier
ZAP
THISFORM.List1.Clear
FuncC = Prefix +( gcAllCustomers )
lnConnect = o.HTTPConnect ( Server )
IF lnConnect <> 0
MessageBox ( ;
[Couldn't connect to Internet Server], ;
64, _Screen.Caption )
cmdCancel.Click
ENDIF
lcBuffer = []
lnBufLen = 0
o.HTTPGetEx ( FuncC, @lcBuffer, @lnBufLen )
DO CASE
CASE gcMethod = [DBF]
StrToFile ( lcBuffer, [Carrier.DBF] )
SELECT Carrier
APPEND FROM Carrier.DBF
DELETE FILE Carrier.DBF
OTHERWISE && must be XML
lcXML = ALLTRIM( lcBuffer )
oXML.XMLToCursor ( lcXML, [Carrier] )
ENDCASE
WITH THISFORM.List1
SCAN
.AddListItem ( Company )
Row = .NewItemID
.AddListItem ( City, Row, 2)
.AddListItem ( Region, Row, 3)
.AddListItem ( Cust_ID, Row, 4)
ENDSCAN
.ListIndex = 1
.Selected[1] = .T.
ENDWITH
ENDPROC
Think through the code for a few minutes and you'll see what's going on. You send off a query in the first parameter of HttpGetEx and then wait for the answer to be returned in lcBuffer as a string. You'll then convert it from either a DBF or from XML.
XML is simplicity itself. (We'll talk about it in detail in Chapter 7, “XML.”) If you have a CUSTOMER record with fields NAME, PHONE, and BALANCE, the XML of that record will look like this:
<name>Joe Blow</name> <phone>555-1212</phone> <balance>123.45</balance>
All data is converted to strings for XML transport. The XMLTOCURSOR() method of WWIPSTUFF class converts it back to the correct data types, using the corresponding field names in an open cursor to type the data.
The method call
lcXML = oXML.CursorToXML()
reads the currently open cursor and stores its XML representation in string lcXML. Its inverse function is
oXML.XMLtoCursor ( lcXML )
which takes the string and dumps it into the cursor that's open in the current work area, using field names alone to determine what goes where and what data types to use. (It doesn't have to be the current work area, but I'm trying to make this as simple as possible.)
You can also use something called a DTD. There are thick books about XML, and you don't need to know any of it for what we're doing here. If you're one of those anal-retentive types who delights in theoretical purity, you're reading the wrong book.
The Customer form is based on the StandardForm template. I included some basic capabilities in StandardForm, but a few additional features need to be added for each form that we build. The customer form appears in Figure 5.2.
The Load event creates a cursor that matches the data source (see Listing 5.7). If you're using SQL Server, the FoxPro data types are slightly different: SQL's Text is our Memo, Money is our Y field, and TinyInt and Smallint are just Int. For SQL VarChar, you can use either Memo or Char.
Example 5.7. Load Event for the Form
PROCEDURE Load CREATE CURSOR Customer (; CUST_ID Char( 6), ; COMPANY Char(40), ; CONTACT Char(30), ; TITLE Char(30), ; ADDRESS Char(60), ; CITY Char(15), ; REGION Char(15), ; POSTALCODE Char(10), ; COUNTRY Char(15), ; PHONE Char(24), ; FAX Char(24), ; MAXORDAMT Y ) ENDPROC
The users have to go to the server to pick a record. That's why, unlike what we're used to in the FoxPro world, the screen in most SQL apps is initially empty. The Find function described in Listing 5.8 lets them find a record to display on the form.
Example 5.8. Click Event Code for the Find Button
PROCEDURE cmdFind.Click
WITH THISFORM
DO FORM GetCust TO CustKey && GetCust is a MODAL form
IF NOT EMPTY ( CustKey )
o.HTTPConnect ( Server )
lcBuffer = []
lnBufLen = 0
o.HTTPGetEx ( Prefix + gcSelectedCustomer + [~] ;
+ CustKey, @lcBuffer, @lnBufLen )
IF NOT EMPTY ( lcBuffer )
SELECT CUSTOMER
ZAP
DO CASE
CASE gcMethod = [DBF]
StrToFile ( lcBuffer, [Carrier.DBF] )
APPEND FROM Carrier.DBF
DELETE FILE Carrier.DBF
OTHERWISE && must be XML
lcXML = ALLTRIM( lcBuffer )
oXML.XMLToCursor ( lcXML, [Customer])
ENDCASE
ENDIF
.Refresh
.cmdEdit.Enabled = .T.
.cmdDelete.Enabled = .T.
ELSE
.cmdEdit.Enabled = .F.
.cmdDelete.Enabled = .F.
ENDIF
.cmdNext.Enabled = .T.
.cmdPrev.Enabled = .T.
ENDWITH
ENDPROC
They might just want to add a record. See the template cmdAdd.click code shown in Listing 5.9 to see what happens here. Except for our code to ensure that the cursor is zapped before the default cmdADD.Click method code runs, there's little difference.
Example 5.9. Click Event Code for the Add Button
PROCEDURE cmdAdd.Click SELECT Customer ZAP DODEFAULT() ENDPROC
There's no cmdEdit.Click code because it's exactly the same as the template code—enable the input fields, then disable all buttons except Save and Cancel. Note that in the template code a form property called Adding is set to True during an add, because it's very, very important when it comes time to save changes or additions.
Depending on what the user's application reads from CONTROL.TXT, it's going to ask the app server to run one of three different functions for each of the main tasks—Save, Return a record or records, Delete a record, or skip Forward or Back. To do so, it passes the name of the function to run.
I've included functions to return data in three different ways:
As a DBF—I don't even zip the file, although it's easy and advisable to do so
As an XML string built from a DBF
As an XML string built from a cursor created from data selected from SQL Server
Because of the way I've written this application, it can use either a DBF or SQL Server tables on the server side with no changes in the user's application software. The middle tier—your function in the MyCode class library in the wwdemo subdirectory of the wconnect directory of your app server—stores and retrieves data based on which functions are called. So you can write FoxPro applications that use either DBFs or SQL Server as the data repository, and the Internet as the network. All you have to do is build an app server that has one function for each thing you want to do. As you will see in Listing 5.10, the average Web Connection function is about 8 or 10 lines long.
When you buy Web Connection, it gives you a Console routine to run, which builds a complete project, including a new shell class library where you'll add your own functions. If your class library is called ZCls, your call to a function called F1 on your server would look like this:
http://www.MySite.com/wconnect/wc.dll?Zcls~F1
However, because you're building this using the shareware version, let's keep it simple. The shareware install creates a subdirectory called wwdemo, and it already contains a little class file called Mycode.prg that will do just fine. So you can open it up and add your functions, and then call it using
http://www.MySite.com/wconnect/wc.dll?Mycode~F1
That's why line 70 or so of MAIN.PRG assigned a similar string to the global variable Prefix. Listing 5.10 shows a typical Web Connection class library to take care of your remote data access needs.
Example 5.10. My App Server Code
*PROCEDURE MyDataServer
LPARAMETER loServer
LOCAL loProcess
#INCLUDE WCONNECT.H
loProcess=CREATE("wwDemo",loServer)
loProcess.lShowRequestData = loServer.lShowRequestData
IF VARTYPE(loProcess)#"O"
WAIT WINDOW NOWAIT "Unable to create Process object..."
RETURN .F.
ENDIF
loProcess.Process()
RETURN
#DEFINE HOMEPATH "/wconnect/"
#DEFINE HOMEPAGE HOMEPATH+"default.htm"
#DEFINE BACKIMG ""
#DEFINE PAGEFOOT [<p><HR>]+CRLF;
+ [<A HREF="http://www.west-wind.com/webconnection/">] ;
+ [<IMG SRC="/wconnect/wcpower.gif" BORDER=0 HSPACE=5 ] ;
+ [ALIGN=LEFT ALT="Powered by Web Connection"></a>];
+ [<FONT SIZE=-1>Query created by ] ;
+ [<A HREF="mailto:[email protected]">Rick Strahl</A><BR>];
+ '[<A HREF="'+HOMEPATH+ 'default.htm">] ;
+ [Web Connection demo page</A>] ;
+ [<A HREF="/wconnect/wc.dll?wwdemo] ;
+ [~ShowCode~'+THIS.oRequest.QueryString(2)+'">] ;
+ [Show Code</a>]'
********************************
DEFINE CLASS wwDemo AS wwProcess
********************************
cHTMLPagePath = ""
cDataPath = ""
*******************
* wwDemo :: Process
*******************
FUNCTION Process
LOCAL lcParameter, lcOutFile, lcIniFile, lcOldError
THIS.cHTMLPAGEPATH = THIS.oServer.oConfig.owwDemo.cHTMLPagePath
THIS.cDATAPATH = THIS.oServer.oConfig.owwDemo.cDataPath
THIS.oResponse.cStyleSheet = "westwind.css"
Config = THIS.oServer.oConfig.owwDemo
DODEFAULT()
RETURN .T.
ENDFUNC
FUNCTION GetRecords
loXML = CREATE("wwXML")
* loXML.nCreateDataStructure = 1 && Schema
loXML.cDocRootName = "GetRecords"
lcAccess = UPPER(Request.Form("DataAccess"))
lcCmd = Request.Form("Cmd")
IF lcAccess = "DBF"
lcCmd = lcCmd + " INTO CURSOR C1"
&lcCmd
lcXML = loXML.CursorToXml ( "C1s", "C1" )
ELSE
Handle = SQLSTRINGCONNECT(THIS.Connstring)
IF Handle > 0
lr = SQLEXEC( Handle, Cmd, [C1] )
IF lr <= 0 Then
lcXML = [Error: SQL SELECT error]
ELSE
lcXML = loXML.CursorToXml ( "C1s", "C1" )
ENDIF
ELSE
lcXML = [Error: SQL connection error]
ENDIF
ENDIF
SQLDISCONNECT(0)
Response.ContentType="text/xml"
lcXML = loXML.CursorToXML("Records","Record")
IF USED ( "C1" )
USE IN C1
ENDIF
Response.Write( loXML.EncodeXML(lcXML) )
RELEASE loXML
ENDFUNC
FUNCTION GetStructure
lcAccess = UPPER(Request.QueryString(3))
lcTable = UPPER(Request.QueryString(4))
lcDBF = UPPER(Request.QueryString(5)) && if nonblank, return a dbf
DO CASE
CASE lcAccess = [DBF]
IF lcDBF == ""
SELECT 0
USE ( lcTable )
COPY STRUCTURE EXTENDED TO C1
USE C1
COPY TO x.TXT DELIM
USE
lcStru = FILETOSTR( "X.txt" )
ERASE x.TXT
ELSE
SELECT 0
USE ( lcTable )
COPY STRUCTURE EXTENDED TO C1
USE
SELECT 0
CREATE TABLE Carrier ( ;
field_name char(20), field_type char(1), ;
field_len numeric(3), field_dec numeric(3) )
APPEND FROM C1
ERASE C1.*
LOCAL oIP as WWIPSTUFF
oIP = CREA ( "WWIPSTUFF")
USE IN CARRIER
lcStru = oIP.EncodeDBF ( "CARRIER.DBF", .F. )
ERASE Carrier.dbf
ENDIF
Response.Write( lcStru )
CASE lcAccess = [SQL]
Handle = SQLSTRINGCONNECT(THIS.Connstring)
IF Handle > 0
lr = SQLEXEC( Handle, Cmd, [C1] )
IF lr <= 0 Then
lcXML = [Error: SQL SELECT error]
ELSE
lcXML = loXML.CursorToXml ( "C1s", "C1" )
ENDIF
SQLDISCONNECT(0)
ELSE
lcXML = [Error: SQL connection error]
ENDIF
ENDCASE
RELEASE loXML
ENDFUNC
FUNCTION GetMatchingRecords
lcAccess = UPPER(Request.QueryString(3))
lcCmd = Request.QueryString(4)
lcTable = Request.QueryString(5)
lcEncode = Request.QueryString(6)
DO CASE
CASE lcAccess = [DBF]
lcCmd = lcCmd + [ INTO CURSOR C1]
&lcCmd && Results are now in cursor C1
IF lcEncode == "" && return a comma-delimited list of fields
COPY TO x.TXT DELIM
USE
lcStru = FILETOSTR( "X.txt" )
ERASE x.TXT
ELSE
COPY TO CARRIER
HasMemo = THIS.DoesTableHaveAMemoField()
USE IN C1
LOCAL oIP AS WWIPSTUFF
oIP = CREA ( "WWIPSTUFF")
lcStru = oIP.EncodeDBF ( "CARRIER.DBF", HasMemo )
ERASE Carrier.DBF
ENDIF
IF USED ( lcTable )
USE IN ( lcTable )
ENDIF
Response.Write( lcStru )
ENDCASE
ENDFUNC
FUNCTION GetOneRecord
lcAccess = UPPER(Request.QueryString(3))
lcTable = Request.QueryString(4)
lcKeyField = Request.QueryString(5)
lcKeyValue = Request.QueryString(6)
lcEncode = Request.QueryString(7)
DO CASE
CASE lcAccess = [DBF]
SELECT 0
USE ( lcTable )
Dlm = IIF ( TYPE(lcKeyField ) $ [CM], ['], [] )
* Either string or numeric
lcCmd = [SELECT * FROM ] + lcTable + [ WHERE ] ;
+ lcKeyField + [=] + Dlm + lcKeyValue + Dlm
lcCmd = lcCmd + [ INTO CURSOR C1]
&lcCmd && Results are now in cursor C1
IF lcEncode == "" && return a comma-delimited list of fields
COPY TO x.TXT DELIM
USE
lcStru = FILETOSTR( "X.txt" )
ERASE x.TXT
ELSE
COPY TO CARRIER
HasMemo = THIS.DoesTableHaveAMemoField()
USE IN C1
LOCAL oIP AS WWIPSTUFF
oIP = CREA ( "WWIPSTUFF")
lcStru = oIP.EncodeDBF ( "CARRIER.DBF", HasMemo )
ERASE Carrier.DBF
ENDIF
IF USED ( lcTable )
USE IN ( lcTable )
ENDIF
Response.Write( lcStru )
ENDCASE
ENDFUNC
FUNCTION DoesTableHaveAMemoField
HasMemo = .F.
FOR I = 1 TO FCOUNT()
IF TYPE ( FIELD(I)) = [M]
HasMemo = .T.
Exit && Only need to find one
ENDIF
ENDFOR
RETURN HasMemo
PROCEDURE InsertOrUpdateRecord
Use POST buffer variables, since the command string may be quite
* long and sensitive data may be included
lcAccess = UPPER(Request.Form("Access"))
lcCmd = Request.Form("Cmd" )
lcTable = Request.Form("Table" )
*!* _Screen.Print ( CHR(13) + lcCmd )
DO CASE
CASE lcAccess = [DBF]
&lcCmd
IF USED ( lcTable )
USE IN ( lcTable )
ENDIF
CASE lcAccess = [SQL]
Handle = SQLSTRINGCONNECT(THIS.Connstring)
IF Handle > 0
lr = SQLEXEC( Handle, lcCmd )
IF lr <= 0 Then
lcResult = [Error: SQL UPDATE/INSERT error]
ELSE
lcResult = [Ok]
ENDIF
ELSE
lcResult = [Error: SQL connection error]
ENDIF
Response.Write ( lcResult ) && Either "Ok" or "Error"
ENDCASE
ENDPROC
PROCEDURE GetNextKeyValue
lcAccess = UPPER(Request.QueryString(3))
lcTable = UPPER(Request.QueryString(4))
EXTERNAL ARRAY laVal
DO CASE
CASE lcAccess = [DBF]
IF NOT FILE ( [Keys.DBF] )
CREATE TABLE Keys ( TableName Char(20), LastKeyVal Integer )
ENDIF
IF NOT USED ( [Keys] )
USE Keys IN 0
ENDIF
SELECT Keys
LOCATE FOR TableName = lcTable
IF NOT FOUND()
INSERT INTO Keys VALUES ( lcTable, 0 )
ENDIF
Cmd = [UPDATE Keys SET LastKeyVal=LastKeyVal + 1 ] ;
+ [ WHERE TableName='] + lcTable + [']
&Cmd
Cmd = [SELECT LastKeyVal FROM Keys WHERE TableName = '] ;
+ lcTable + [' INTO ARRAY laVal]
&Cmd
USE IN Keys
lcResult = TRANSFORM(laVal(1))
Response.Write ( lcResult )
CASE lcAccess = [SQL]
Cmd = [SELECT Name FROM SysObjects WHERE Name='KEYS' AND Type='U']
lr = SQLEXEC( THIS.Handle, Cmd )
IF lr < 0
lcResult = [Error: ] + CHR(13) + Cmd
IF lcResult = [Error]
Response.Write ( lcResult )
RETURN
ENDIF
ENDIF
IF RECCOUNT([SQLResult]) = 0
Cmd = [CREATE TABLE Keys ( TableName Char(20), LastKeyVal Integer )]
lr = SQLEXEC( THIS.Handle, Cmd )
IF lr < 0
lcResult = [Error: ] + CHR(13) + Cmd
ENDIF
IF lcResult = [Error]
Response.Write ( lcResult )
RETURN
ENDIF
ENDIF
Cmd = [SELECT LastKeyVal FROM Keys WHERE TableName='] + lcTable + [']
lr = SQLEXEC( THIS.Handle, Cmd )
IF lr < 0
IF lr < 0
lcResult = [Error: ] + CHR(13) + Cmd
ENDIF
IF lcResult = [Error]
Response.Write ( lcResult )
RETURN
ENDIF
ENDIF
IF RECCOUNT([SQLResult]) = 0
Cmd = [INSERT INTO Keys VALUES ('] + lcTable + [', 0 )]
lr = SQLEXEC( THIS.Handle, Cmd )
IF lr < 0
IF lr < 0
lcResult = [Error: ] + CHR(13) + Cmd
ENDIF
IF lcResult = [Error]
Response.Write ( lcResult )
RETURN
ENDIF
ENDIF
ENDIF
Cmd = [UPDATE Keys SET LastKeyVal=LastKeyVal + 1] ;
+ [ WHERE TableName='] + lcTable + [']
lr = SQLEXEC( THIS.Handle, Cmd )
IF lr < 0
IF lr < 0
lcResult = [Error: ] + CHR(13) + Cmd
ENDIF
IF lcResult = [Error]
Response.Write ( lcResult )
RETURN
ENDIF
ENDIF
Cmd = [SELECT LastKeyVal FROM Keys WHERE TableName='] + lcTable + [']
lr = SQLEXEC( THIS.Handle, Cmd )
IF lr < 0
IF lr < 0
lcResult = [Error: ] + CHR(13) + Cmd
ENDIF
IF lcResult = [Error]
Response.Write ( lcResult )
RETURN
ENDIF
ENDIF
lcResult = TRANSFORM(SQLResult.LastKeyVal)
USE IN SQLResult
Response.Write ( lcResult )
ENDCASE
ENDPROC
PROCEDURE DeleteRecord
lcAccess = UPPER(Request.QueryString(3))
lcTable = UPPER(Request.QueryString(4))
lcKeyField = UPPER(Request.QueryString(5))
lcKeyValue = UPPER(Request.QueryString(6))
lcDelim = UPPER(Request.QueryString(7))
lcCmd = [DELETE FROM ] + lcTable ;
+ [ WHERE ] + lcKeyField + [=] ;
+ lcDelim + lcKeyValue + lcDelim
DO CASE
CASE lcAccess = [DBF]
&lcCmd
IF USED ( lcTable )
USE IN ( lcTable )
ENDIF
lcResult = [Ok]
CASE lcAccess = [SQL]
Handle = SQLSTRINGCONNECT(THIS.Connstring)
IF Handle > 0
lr = SQLEXEC( Handle, lcCmd )
IF lr <= 0 Then
lcResult = [Error: SQL DELETE error]
ELSE
lcResult = [Ok]
ENDIF
ELSE
lcResult = [Error: SQL connection error]
ENDIF
ENDCASE
Response.Write ( lcResult ) && Either "Ok" or "Error"
ENDPROC
FUNCTION GetTable
lcTable = UPPER(Request.QueryString(3))
Cmd = [SELECT * FROM ] + lcTable + [ INTO CURSOR C1]
loXML = CREATE("wwXML")
*!* loXML.cDocRootName = "GetTable"
&Cmd
lcXML = loXML.CursorToXml ( "C1s", "C1" )
Response.Write ( lcXML )
USE IN C1
USE IN ( lcTable )
ENDFUNC
ENDDEFINE
*EOC MyDataServer.PRG
I've written one function to handle each type of data transport. So if CONTROL.TXT contains
Server = P400 Method = DBF
the program will look around the LAN for a computer named P400, and then send a request to the app server to perform the requested operation by calling a function that returns a DBF. If I write “Method SQL” in Control.txt, the call to HttpGetEx will send a function call to the equivalent SQL function in my app server class library.
In my class library I have a SQLNextCustomer function and a DBFNextCustomer function. The differences are
The syntax of the
SELECTcommandThe way the data string is returned
Change the Method assignment in Control.txt to XML, and it still uses a DBF, but uses XML to send it back and forth. Use SQL+XML, and it looks for data in a SQL Server table.
In wcDemoMain.prg I added a few lines of code to open a connection to SQL Server (see Listing 5.11). I used a DSN-less connection so that you don't have to screw with ODBC.
Example 5.11. Opening a Connection to SQL Server or MSDE
lstr = [uid=sa;pwd=;server=P400;] ;
+ [ driver={SQL Server};database=Customers;]
lh = SQLStringConnect ( lstr )
IF lh < 0
MessageBox ( ;
[Couldn't connect to SQL Server ] + pServerName,;
64, [UGripe] )
RETURN
ENDIF
P400 is my server. You can also use (local) or an IP address. You may need to include UID and PWD parameters if you don't use a DSN. SQL Server can be on any machine on the same LAN as the app server program. You'll also have to add two lines of code to wcDemoMain's PROCESS class to tell it to look at your library of functions, as shown in Listing 5.12.
Example 5.12. Redirecting the Request to the New Process Class
CASE lcParameter == "WWDEMO"
DO WWDEMO WITH THIS
CASE lcParameter == "MYCODE" <<- New
DO MYCODE WITH THIS <<- New
...
The user application can connect to any computer on the Internet that's running your server. If your server is connected to the Internet, open up the command window and type IPCONFIG, and then write down the IP address that has been assigned to your computer. Then go across the street with your laptop, edit Control.txt and enter the IP address as the Server, and restart the eCustomer application. Ta-da!
The two big labor-savers in this application are a pair of generic functions that I wrote a year ago to build SQL UPDATE and INSERT statements on the fly. They can be called with any open cursor selected. The DBF version is shown in Listing 5.13.
Example 5.13. Building INSERT and UPDATE Statements for SQL
* Program-Id....: Send2DBF.PRG
PARAMETERS ;
CommandOrProc, CursorName, WhereClause, IdentityField
IF EMPTY ( CommandOrProc )
RETURN [No command string was sent.]
ENDIF
IF NOT CommandOrProc $ [ADD/EDIT]
RETURN [Valid commands are ADD and EDIT.]
ENDIF
IF NOT EMPTY ( WhereClause ) AND WhereClause <> [WHERE]
RETURN [Where clause doesn't start with WHERE.]
ENDIF
Adding = .F.
DO CASE
CASE CommandOrProc = [ADD]
Adding = .T.
BuildInsertString()
CASE CommandOrProc = [EDIT]
BuildUpdateString()
OTHERWISE
* Stored Procedure - just execute it
ENDCASE
&CommandOrProc
RETURN [Ok]
PROCEDURE BuildInsertString
CommandOrProc = [INSERT INTO ]+CursorName+[ VALUES ( ]
* Assumes that a temporary cursor is open
FOR I = 1 TO FCOUNT()
Fld = FIELD(I)
IF NOT EMPTY ( IdentityField)
IF Fld = UPPER(IdentityField)
LOOP
ENDIF
ENDIF
DO CASE
CASE TYPE ( Fld ) $ [IN]
Str = ALLTRIM(STR(&fld))
CASE TYPE ( Fld ) $ [CM]
Str = [']+STRTR(ALLT(&fld),['],['']) + [']
CASE TYPE ( Fld ) = [Y]
Str = ALLTRIM(STR(MTON(&fld),10,2))
CASE TYPE ( Fld ) = [TD]
Str=IIF(EMPTY(&Fld),[''],['] + TTOC(&fld) + ['])
ENDCASE
CommandOrProc=CommandOrProc+Str+IIF(I=FCOUNT(),[],[,])
ENDFOR
CommandOrProc = CommandOrProc + [ )]
ENDPROC
PROCEDURE BuildUpdateString
CommandOrProc = [UPDATE ] + CursorName + [ SET ]
FOR I = 1 TO FCOUNT() && Assumes temporary cursor is open
Fld = FIELD(I)
IF NOT EMPTY ( WhereClause )
IF Fld $ UPPER(WhereClause)
LOOP
ENDIF
ENDIF
DO CASE
CASE TYPE ( Fld ) $ [IN]
Str = ALLTRIM(STR(&fld))
CASE TYPE ( Fld ) $ [CM]
Str = ['] + STRTRAN(ALLTRIM(&fld),['],[''])+[']
CASE TYPE ( Fld ) = [Y]
Str = ALLTRIM(STR(MTON(&fld),10,2))
CASE TYPE ( Fld ) = [TD]
Str = IIF(EMPTY(&Fld),[''],[']+TTOC(&fld)+['])
ENDCASE
Str = Fld + [=] + Str
CommandOrProc=CommandOrProc+Str+IIF(I=FCOUNT(),[],[,])
ENDFOR
CommandOrProc = CommandOrProc + [ ] + WhereClause
ENDPROC
The UPDATE statement requires a WHERE clause, which I pass to the function. If you have a key field that has its own method for generating the next unique value, it's automatic in SQL, but in Visual FoxPro you have to deal with it differently. In SQL you leave it out of the VALUES list, but in a FoxPro DBF you call a function like my AutoIncrement function (see my article #221 at www.LesPinter.com) and include it in the list. So you need two different functions: The source code download has both the DBF and the SQL version.
The sample file uses a very, very bad idea—a key field (CUST_ID) that's a user-specified string. These originated back in the days when users thought they should be able to construct little mnemonics to use as search keys, so that they would know, for example, that SMITH01 was Joe Smith and SMITH02 was Fred Smith. The way to search for records is the way we do it in our GetCust.SCX; the user never even has to see the key.
Table design matters even more in the Internet environment. Keys should be consecutive integers, period. If users can type in their own keys, you have to make an extra trip to the server to make sure they're not trying to add a key that's already in use. What's the point? Users don't even have to know the primary key. SQL Server will generate the next integer key if you simply define the key like this:
Cust_ID Integer IDENTITY(1,1)
If you're using a DBF, you can use a modification of our AutoIncrement function in the DBFSaveCustomer function. You can make the necessary modifications yourself. It only takes a few minutes. Email me if you need help in doing so.
The use of a user-supplied character string as a primary key in this table has led to countless clumsy imitations by Microsoft customers who assumed that the samples demonstrated good database techniques. And this poor example, which originated with the Access Northwind database, crept into the FoxPro samples without being strangled by someone on the Fox team who should have known better. Hard to imagine.
Listing 5.14 shows the Data Tier program, modified to include calls to Web Connection (DataAccess = [WC]). This will work with both Visual FoxPro 7 and later versions, although in Listing 5.14 we'll include the code to support Visual FoxPro 8 specifically (DataAccess = [XML]).
Example 5.14. Adding Internet Access to the Data Tier
DEFINE CLASS DataTier AS Custom
AccessMethod = []
* Any attempt to assign a value to this property will be trapped
* by the "setter" method AccessMethod_Assign.
ConnectionString = ;
[Driver={SQL Server};Server=(Local);Database=Northwind;UID=sa;PWD=;]
Handle = 0
* Added for WebConnection:
MyServerURL = [localhost/]
Prefix = [wconnect/wc.dll?MydataServer~]
SvrDataAccess = [DBF] && Either DBF or SQL (on the Server)
PROCEDURE AccessMethod_Assign
PARAMETERS AM
DO CASE
CASE AM = [DBF]
THIS.AccessMethod = [DBF] && FoxPro tables
CASE AM = [SQL]
THIS.AccessMethod = [SQL] && MS Sql Server
THIS.GetHandle
CASE AM = [XML]
THIS.AccessMethod = [XML] && FoxPro XMLAdapter
CASE AM = [WC]
THIS.AccessMethod = [WC] && WebConnection server
OTHERWISE
MESSAGEBOX( [Incorrect access method ] + AM, 16, [Setter error] )
THIS.AccessMethod = []
ENDCASE
_VFP.Caption = [Data access method: ] + THIS.AccessMethod
ENDPROC
PROCEDURE GetHandle
IF THIS.AccessMethod = [SQL]
IF THIS.Handle > 0
RETURN
ENDIF
THIS.Handle = SQLSTRINGCONNECT( THIS.ConnectionString )
IF THIS.Handle < 1
MESSAGEBOX( [Unable to connect], 16, [SQL Connection error], 2000 )
ENDIF
ELSE
Msg = [A SQL connection was requested, but access method is ] ;
+ THIS.AccessMethod
MESSAGEBOX( Msg, 16, [SQL Connection error], 2000 )
THIS.AccessMethod = []
ENDIF
RETURN
PROCEDURE CreateCursor
LPARAMETERS pTable, pKeyField
DO CASE
CASE THIS.AccessMethod = [DBF]
IF NOT USED ( pTable )
SELECT 0
USE ( pTable ) ALIAS ( pTable )
ENDIF
SELECT ( pTable )
IF NOT EMPTY ( pKeyField )
SET ORDER TO TAG ( pKeyField )
ENDIF
RETURN
CASE THIS.AccessMethod = [SQL]
Cmd = [SELECT * FROM ] + pTable + [ WHERE 1=2]
SQLEXEC( THIS.Handle, Cmd )
AFIELDS ( laFlds )
USE
CREATE CURSOR ( pTable ) FROM ARRAY laFlds
CASE THIS.AccessMethod = [WC]
LOCAL oIP AS WWIPSTUFF
oIP = CREATEOBJECT( "WWIPSTUFF" )
oIP.HTTPConnect(THIS.MyServerURL)
lcBuffer = []
lnBufLen = 0
Cmd = THIS.Prefix + [GetStructure] + [~] ;
+ THIS.SvrDataAccess + [~] + pTable + [~] + [EncodeDBF]
oIP.HTTPGetEx ( Cmd, @lcBuffer, @lnBufLen )
lcTxt = ALLTRIM( lcBuffer )
oIP.DecodeDBF ( lcTxt, "Carrier.DBF" )
SELECT 0
USE Carrier
COPY TO ARRAY aStru
USE
ERASE Carrier.DBF
CREATE CURSOR ( pTable ) FROM ARRAY aStru
CASE THIS.AccessMethod = [XML]
ENDCASE
PROCEDURE GetMatchingRecords
LPARAMETERS pTable, pFields, pExpr
pFields = IIF ( EMPTY ( pFields ), [*], pFields )
pExpr = IIF ( EMPTY ( pExpr ), [], ;
[ WHERE ] + STRTRAN ( UPPER ( ALLTRIM ( pExpr ) ), [WHERE ], [] ) )
cExpr = [SELECT ] + pFields + [ FROM ] + pTable + pExpr
pFields = IIF ( EMPTY ( pFields ), [*], pFields )
pExpr = IIF ( EMPTY ( pExpr ), [], ;
[ WHERE ] + STRTRAN ( UPPER ( ALLTRIM ( pExpr ) ), [WHERE ], [] ) )
cExpr = [SELECT ] + pFields + [ FROM ] + pTable + pExpr
DO CASE
CASE THIS.AccessMethod = [DBF]
SET FILTER TO &pExpr
GO TOP
CASE THIS.AccessMethod = [SQL]
lr = SQLExec ( THIS.Handle, cExpr )
IF lr >= 0
THIS.FillCursor(pTable)
ELSE
Msg = [Unable to return records] + CHR(13) + cExpr
MESSAGEBOX( Msg, 16, [SQL error] )
ENDIF
SELECT ( pTable )
ZAP
APPEND FROM DBF( [SQLResult] )
USE IN SQLResult
CASE THIS.AccessMethod = [WC]
LOCAL oIP AS WWIPSTUFF
oIP = CREATEOBJECT( "WWIPSTUFF" )
oIP.HTTPConnect(THIS.MyServerURL)
lcBuffer = []
lnBufLen = 0
Cmd = THIS.Prefix + [GetMatchingRecords] + [~] ;
+ THIS.SvrDataAccess + [~] ;
+ cExpr + [~] ;
+ pTable + [~] ;
+ [EncodeDBF]
oIP.HTTPGetEx ( Cmd, @lcBuffer, @lnBufLen )
lcTxt = ALLTRIM( lcBuffer )
oIP.DecodeDBF ( lcTxt, "SQLResult.DBF" )
SELECT ( pTable )
APPEND FROM SQLResult
ERASE SQLResult.DBF
ENDCASE
ENDPROC
PROCEDURE GetMatchingRecordsForView
LPARAMETERS pTable, pFields, pExpr
ViewName = [View] + pTable
IF NOT USED ( ViewName )
CreateView ( pTable )
ENDIF
pFields = IIF ( EMPTY ( pFields ), [*], pFields )
pExpr = IIF ( EMPTY ( pExpr ), [], ;
[ WHERE ] + STRTRAN ( UPPER ( ALLTRIM ( pExpr ) ), [WHERE ], [] ) )
cExpr = [SELECT ] + pFields + [ FROM ] + pTable + pExpr
DO CASE
CASE THIS.AccessMethod = [DBF]
cExpr = cExpr + [ INTO CURSOR SQLResult]
&cExpr
SELECT ( ViewName )
APPEND FROM DBF( [SQLResult] )
USE IN SQLResult
CASE THIS.AccessMethod = [SQL]
lr = SQLExec ( THIS.Handle, cExpr )
IF lr >= 0
THIS.FillCursor(ViewName)
ELSE
Msg = [Unable to return records] + CHR(13) + cExpr
MESSAGEBOX( Msg, 16, [SQL error] )
ENDIF
CASE THIS.AccessMethod = [WC]
LOCAL oIP AS WWIPSTUFF
oIP = CREATEOBJECT( "WWIPSTUFF" )
oIP.HTTPConnect(THIS.MyServerURL)
lcBuffer = []
lnBufLen = 0
Cmd = THIS.Prefix + [GetMatchingRecords] + [~] ;
+ THIS.SvrDataAccess + [~] ;
+ cExpr + [~] ;
+ pTable + [~] ;
+ [EncodeDBF]
oIP.HTTPGetEx ( Cmd, @lcBuffer, @lnBufLen )
lcTxt = ALLTRIM( lcBuffer )
IF LEN(lcTxt) = 0
MESSAGEBOX( "Empty string returned", 16 )
SET STEP ON
ENDIF
oIP.DecodeDBF ( lcTxt, "SQLResult.DBF" )
SELECT ( ViewName )
APPEND FROM SQLResult
ERASE SQLResult.DBF
ENDCASE
ENDPROC
PROCEDURE CreateView
LPARAMETERS pTable
IF NOT USED( pTable )
MESSAGEBOX( [Table ] + pTable + [ isn't open -] ;
+ [ probable programmer error], 16, [Error creating view], 2000 )
RETURN
ENDIF
SELECT ( pTable )
AFIELDS( laFlds )
SELECT 0
CREATE CURSOR ( [View] + pTable ) FROM ARRAY laFlds
ENDFUNC
PROCEDURE GetOneRecord
LPARAMETERS pTable, pKeyField, pKeyValue
SELECT ( pTable )
Dlm = IIF ( TYPE ( pKeyField ) = [C], ['], [] )
IF THIS.AccessMethod = [DBF]
cExpr = [LOCATE FOR ] + pKeyField + [=] + Dlm + TRANSFORM ( pKeyValue ) + Dlm
ELSE
cExpr = [SELECT * FROM ] + pTable + [ WHERE ] + pKeyField ;
+ [=] + Dlm + TRANSFORM ( pKeyValue ) + Dlm
ENDIF
DO CASE
CASE THIS.AccessMethod = [DBF]
&cExpr
CASE THIS.AccessMethod = [SQL]
lr = SQLExec ( THIS.Handle, cExpr )
IF lr >= 0
THIS.FillCursor( pTable )
ELSE
Msg = [Unable to return record] + CHR(13) + cExpr
MESSAGEBOX( Msg, 16, [SQL error] )
ENDIF
CASE THIS.AccessMethod = [WC]
LOCAL oIP AS WWIPSTUFF
oIP = CREATEOBJECT( "WWIPSTUFF" )
oIP.HTTPConnect(THIS.MyServerURL)
lcBuffer = []
lnBufLen = 0
Cmd = THIS.Prefix + [GetOneRecord] + [~] ;
+ THIS.SvrDataAccess + [~] ;
+ pTable + [~] ;
+ pKeyField + [~] ;
+ TRANSFORM(pKeyValue) + [~] ;
+ [DBFEncode]
oIP.HTTPGetEx ( Cmd, @lcBuffer, @lnBufLen )
lcTxt = ALLTRIM( lcBuffer )
oIP.DecodeDBF ( lcTxt, "SQLResult.DBF" )
SELECT ( pTable )
ZAP
APPEND FROM SQLResult
ERASE SQLResult.DBF
GO TOP
CASE THIS.AccessMethod = [XML]
ENDCASE
ENDFUNC
PROCEDURE FillCursor
LPARAMETERS pTable
IF THIS.AccessMethod = [DBF]
RETURN
ENDIF
SELECT ( pTable )
ZAP
APPEND FROM DBF ( [SQLResult] )
USE IN SQLResult
GO TOP
ENDPROC
PROCEDURE DeleteRecord
LPARAMETERS pTable, pKeyField
ForExpr = IIF ( THIS.AccessMethod = [DBF], [ FOR ], [ WHERE ] )
KeyValue = EVALUATE ( pTable + [.] + pKeyField )
Dlm = IIF ( TYPE ( pKeyField ) = [C], ['], [] )
DO CASE
CASE THIS.AccessMethod = [DBF]
cExpr = [DELETE ] + pTable + [ WHERE ] + pKeyField ;
+ [=] + Dlm + TRANSFORM ( m.KeyValue ) + Dlm
&cExpr
SET DELETED ON
GO TOP
CASE THIS.AccessMethod = [SQL]
cExpr = [DELETE ] + pTable + [ WHERE ] + pKeyField ;
+ [=] + Dlm + TRANSFORM ( m.KeyValue ) + Dlm
lr = SQLExec ( THIS.Handle, cExpr )
IF lr < 0
Msg = [Unable to delete record] + CHR(13) + cExpr
MESSAGEBOX( Msg, 16, [SQL error] )
ENDIF
CASE THIS.AccessMethod = [WC]
LOCAL oIP AS WWIPSTUFF
oIP = CREATEOBJECT( "WWIPSTUFF" )
oIP.HTTPConnect(THIS.MyServerURL)
lcBuffer = []
lnBufLen = 0
Cmd = THIS.Prefix + [DeleteRecord] + [~] ;
+ THIS.SvrDataAccess + [~] ;
+ pTable + [~] ;
+ pKeyField + [~] ;
+ TRANSFORM(KeyValue) + [~] ;
+ Dlm
oIP.HTTPGetEx ( Cmd, @lcBuffer, @lnBufLen )
IF lcBuffer <> [Ok]
MESSAGEBOX( lcBuffer )
ENDIF
CASE THIS.AccessMethod = [XML]
ENDCASE
ENDFUNC
PROCEDURE SaveRecord
PARAMETERS pTable, pKeyField, pAdding
IF THIS.AccessMethod = [DBF]
RETURN
ENDIF
IF pAdding
THIS.InsertRecord ( pTable, pKeyField )
ELSE
THIS.UpdateRecord ( pTable, pKeyField )
ENDIF
ENDPROC
PROCEDURE InsertRecord
LPARAMETERS pTable, pKeyField
lcCmd = THIS.BuildInsertCommand ( pTable, pKeyField )
_ClipText = lcCmd && Userful for debugging
DO CASE
CASE THIS.AccessMethod = [SQL]
lr = SQLExec ( THIS.Handle, lcCmd )
IF lr < 0
msg = [Unable to insert record; command follows:] + CHR(13) + lcCmd
MESSAGEBOX( Msg, 16, [SQL error] )
ENDIF
CASE THIS.AccessMethod = [WC]
LOCAL oIP AS WWIPSTUFF
oIP = CREATEOBJECT( "WWIPSTUFF" )
oIP.HTTPConnect(THIS.MyServerURL)
lcBuffer = []
lnBufLen = 0
Use POST buffer variables, since the command string may be quite long
* and sensitive data may be included
oIP.AddPostKey( [Access], THIS.SvrDataAccess )
oIP.AddPostKey( [Cmd], lcCmd )
oIP.AddPostKey( [Table], pTable )
oIP.HTTPGetEx ( THIS.Prefix + [InsertOrUpdateRecord],;
@lcBuffer, @lnBufLen )
IF lcBuffer = [Error]
MESSAGEBOX( lcBuffer, 16, _VFP.Caption )
ENDIF
CASE THIS.AccessMethod = [XML]
ENDCASE
ENDFUNC
PROCEDURE UpdateRecord
LPARAMETERS pTable, pKeyField
lcCmd = THIS.BuildUpdateCommand ( pTable, pKeyField )
_ClipText = lcCmd && Useful for debugging
DO CASE
CASE THIS.AccessMethod = [SQL]
lr = SQLExec ( THIS.Handle, lcCmd )
IF lr < 0
msg = [Unable to update record; command follows:] + CHR(13) + cExpr
MESSAGEBOX( Msg, 16, [SQL error] )
ENDIF
CASE THIS.AccessMethod = [WC]
LOCAL oIP AS WWIPSTUFF
oIP = CREATEOBJECT( "WWIPSTUFF" )
oIP.HTTPConnect(THIS.MyServerURL)
lcBuffer = []
lnBufLen = 0
oIP.AddPostKey( [Access], THIS.SvrDataAccess )
oIP.AddPostKey( [Cmd], lcCmd )
oIP.AddPostKey( [Table], pTable )
oIP.HTTPGetEx ( THIS.Prefix + [InsertOrUpdateRecord], ;
@lcBuffer, @lnBufLen )
IF lcBuffer = [Error]
MESSAGEBOX( lcBuffer, 16, _VFP.Caption )
ENDIF
CASE THIS.AccessMethod = [XML]
ENDCASE
ENDFUNC
FUNCTION BuildInsertCommand
PARAMETERS pTable, pKeyField
Cmd = [INSERT INTO ] + pTable + [ ( ]
FOR I = 1 TO FCOUNT()
Fld = UPPER(FIELD(I))
IF TYPE ( Fld ) = [G]
LOOP
ENDIF
Cmd = Cmd + Fld + [, ]
ENDFOR
Cmd = LEFT(Cmd,LEN(Cmd)-2) + [ ) VALUES ( ]
FOR I = 1 TO FCOUNT()
Fld = FIELD(I)
IF TYPE ( Fld ) = [G]
LOOP
ENDIF
Dta = ALLTRIM(TRANSFORM ( &Fld ))
Dta = CHRTRAN ( Dta, CHR(39), CHR(146) )
* get rid of single quotes in the data
Dta = IIF ( Dta = [/ /], [], Dta )
Dta = IIF ( Dta = [.F.], [0], Dta )
Dta = IIF ( Dta = [.T.], [1], Dta )
Dlm = IIF ( TYPE ( Fld ) $ [CM],['],;
IIF ( TYPE ( Fld ) $ [DT],['],;
IIF ( TYPE ( Fld ) $ [IN],[], [])))
IF ( THIS.AccessMethod = [DBF] ) ;
OR ( THIS.AccessMethod = [WC] AND THIS.SvrDataAccess = [DBF] )
LDM = IIF ( TYPE ( Fld ) $ [DT], [{], Dlm )
RDM = IIF ( TYPE ( Fld ) $ [DT], [}], Dlm )
ELSE
LDM = Dlm
RDM = Dlm
ENDIF
Cmd = Cmd + LDM + Dta + RDM + [, ]
ENDFOR
Cmd = LEFT ( Cmd, LEN(Cmd) -2) + [ )] && Remove ", " add " )"
RETURN Cmd
ENDFUNC
FUNCTION BuildUpdateCommand
PARAMETERS pTable, pKeyField
Cmd = [UPDATE ] + pTable + [ SET ]
FOR I = 1 TO FCOUNT()
Fld = UPPER(FIELD(I))
IF Fld = UPPER(pKeyField)
LOOP
ENDIF
IF TYPE ( Fld ) = [G]
LOOP
ENDIF
Dta = ALLTRIM(TRANSFORM ( &Fld ))
IF Dta = [.NULL.]
DO CASE
CASE TYPE ( Fld ) $ [CMDT]
Dta = []
CASE TYPE ( Fld ) $ [INL]
Dta = [0]
ENDCASE
ENDIF
Dta = CHRTRAN ( Dta, CHR(39), CHR(146) )
* get rid of single quotes in the data
Dta = IIF ( Dta = [/ /], [], Dta )
Dta = IIF ( Dta = [.F.], [0], Dta )
Dta = IIF ( Dta = [.T.], [1], Dta )
Dlm = IIF ( TYPE ( Fld ) $ [CM],['],;
IIF ( TYPE ( Fld ) $ [DT],['],;
IIF ( TYPE ( Fld ) $ [IN],[], [])))
IF ( THIS.AccessMethod = [DBF] ) ;
OR ( THIS.AccessMethod = [WC] AND THIS.SvrDataAccess = [DBF] )
LDM = IIF ( TYPE ( Fld ) $ [DT], [{], Dlm )
RDM = IIF ( TYPE ( Fld ) $ [DT], [}], Dlm )
ELSE
LDM = Dlm
RDM = Dlm
ENDIF
Cmd = Cmd + Fld + [=] + LDM + Dta + RDM + [, ]
ENDFOR
Dlm = IIF ( TYPE ( pKeyField ) = [C], ['], [] )
Cmd = LEFT ( Cmd, LEN(Cmd) -2 ) ;
+ [ WHERE ] + pKeyField + [=] ;
+ + Dlm + TRANSFORM(EVALUATE(pKeyField)) + Dlm
RETURN Cmd
ENDFUNC
PROCEDURE SelectCmdToSQLResult
LPARAMETERS pExpr
DO CASE
CASE THIS.AccessMethod = [DBF]
pExpr = pExpr + [ INTO CURSOR SQLResult]
&pExpr
CASE THIS.AccessMethod = [SQL]
lr = SQLExec ( THIS.Handle, pExpr )
IF lr < 0
Msg = [Unable to return records] + CHR(13) + cExpr
MESSAGEBOX( Msg, 16, [SQL error] )
ENDIF
CASE THIS.AccessMethod = [WC]
LOCAL oIP AS WWIPSTUFF
oIP = CREATEOBJECT( "WWIPSTUFF" )
oIP.HTTPConnect(THIS.MyServerURL)
lcBuffer = []
lnBufLen = 0
Cmd = THIS.Prefix + [GetMatchingRecords]
oIP.AddPostKey ( "ServerAccess", THIS.SvrDataAccess )
oIP.AddPostKey ( "Expr", pExpr )
oIP.HTTPGetEx ( Cmd, @lcBuffer, @lnBufLen )
lcXML = ALLTRIM( lcBuffer )
XMLTOCURSOR ( lcXML, "SQLResult" )
GO TOP
BROWSE
CASE THIS.AccessMethod = [XML]
ENDCASE
ENDFUNC
FUNCTION GetNextKeyValue
LPARAMETERS pTable
EXTERNAL ARRAY laVal
pTable = UPPER ( pTable )
DO CASE
CASE THIS.AccessMethod = [DBF]
IF NOT FILE ( [Keys.DBF] )
CREATE TABLE Keys ( TableName Char(20), LastKeyVal Integer )
ENDIF
IF NOT USED ( [Keys] )
USE Keys IN 0
ENDIF
SELECT Keys
LOCATE FOR TableName = pTable
IF NOT FOUND()
INSERT INTO Keys VALUES ( pTable, 0 )
ENDIF
Cmd = [UPDATE Keys SET LastKeyVal=LastKeyVal + 1 ] ;
+ [ WHERE TableName='] + pTable + [']
&Cmd
Cmd = [SELECT LastKeyVal FROM Keys WHERE TableName = '] ;
+ pTable + [' INTO ARRAY laVal]
&Cmd
USE IN Keys
RETURN TRANSFORM(laVal(1))
CASE THIS.AccessMethod = [SQL]
Cmd = [SELECT Name FROM SysObjects WHERE Name='KEYS' AND Type='U']
lr = SQLEXEC( THIS.Handle, Cmd )
IF lr < 0
MESSAGEBOX( "SQL Error:"+ CHR(13) + Cmd, 16 )
ENDIF
IF RECCOUNT([SQLResult]) = 0
Cmd = [CREATE TABLE Keys ( TableName Char(20), LastKeyVal Integer )]
SQLEXEC( THIS.Handle, Cmd )
ENDIF
Cmd = [SELECT LastKeyVal FROM Keys WHERE TableName='] + pTable + [']
lr = SQLEXEC( THIS.Handle, Cmd )
IF lr < 0
MESSAGEBOX( "SQL Error:"+ CHR(13) + Cmd, 16 )
ENDIF
IF RECCOUNT([SQLResult]) = 0
Cmd = [INSERT INTO Keys VALUES ('] + pTable + [', 0 )]
lr = SQLEXEC( THIS.Handle, Cmd )
IF lr < 0
MESSAGEBOX( "SQL Error:"+ CHR(13) + Cmd, 16 )
ENDIF
ENDIF
Cmd = [UPDATE Keys SET LastKeyVal=LastKeyVal + 1;
+ [ WHERE TableName='] + pTable + [']
lr = SQLEXEC( THIS.Handle, Cmd )
IF lr < 0
MESSAGEBOX( "SQL Error:"+ CHR(13) + Cmd, 16 )
ENDIF
Cmd = [SELECT LastKeyVal FROM Keys WHERE TableName='] + pTable + [']
lr = SQLEXEC( THIS.Handle, Cmd )
IF lr < 0
MESSAGEBOX( "SQL Error:"+ CHR(13) + Cmd, 16 )
ENDIF
nLastKeyVal = TRANSFORM(SQLResult.LastKeyVal)
USE IN SQLResult
RETURN TRANSFORM(nLastKeyVal)
CASE THIS.AccessMethod = [WC]
LOCAL oIP AS WWIPSTUFF
oIP = CREATEOBJECT( "WWIPSTUFF" )
oIP.HTTPConnect(THIS.MyServerURL)
lcBuffer = []
lnBufLen = 0
Cmd = THIS.Prefix + [GetNextKeyValue] + [~] + [DBF] + [~] + pTable
oIP.HTTPGetEx ( Cmd, @lcBuffer, @lnBufLen )
IF LEN(lcBuffer) > 10
MESSAGEBOX( lcBuffer )
RETURN []
ELSE
RETURN ALLTRIM(lcBuffer)
ENDIF
CASE THIS.AccessMethod = [XML]
ENDCASE
ENDDEFINE
With Visual FoxPro 8, it's quite a bit simpler. You start with a Web service. A Web service is a project with a single .PRG, compiled to a multithreaded DLL. It must start with a DEFINE CLASS <name> AS SESSION OLEPUBLIC declaration. It must declare the type of data to be returned—String in this case because we want to return XML—and it must return the XML string.
Getting the XML string is a snap, thanks to the new XMLAdapter class. We create a connection to SQL Server, execute the SELECT statement, and then instantiate an XMLAdapter and use its ToXML method to create the XML. Listing 5.15 shows the code to return a list of company names and customer IDs for the list box.
Example 5.15. An XML Web Service (FoxPro 8)
* FoxClass
DEFINE CLASS FoxClass AS Session OLEPUBLIC
ConnectionString = ;
[Driver={SQL Server};Server=(local);Database=Northwind;UID=sa;PWD=;]
FUNCTION AllCustomers AS String
Cmd = "SELECT CompanyName, CustomerID FROM Customers"
Handle = SQLSTRINGCONNECT( THIS.ConnectionString )
lr = SQLEXEC( Handle, Cmd, "Customers" )
LOCAL xa AS XMLAdapter
xa = CREA ( "XMLAdapter" )
WITH xa
.AddTableSchema("Customers")
.ToXML("lcXML")
ENDWITH
xa = NULL
RELEASE xa
USE IN Customers
RETURN lcXML
ENDFUNC
ENDDEFINE
The project is called FoxServer, and the class is stored in FoxClass.PRG. I pick the Build option and select the radio button labeled Multithreaded COM Server (dll) in the Build Options dialog, as shown in Figure 5.3.
TIP
You can type BUILD MTDLL FoxServer FROM FoxServer in the command window instead of opening up the project and clicking on the Build button.
To test it, use the following code:
o = CREATEOBJECT ( "FoxServer.FoxClass" ) ? o.AllCustomers()
The first page of the result appears in Listing 5.16.
Example 5.16. XML Output from the FoxPro XML Web Service
<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>
<VFPDataSet>
<xsd:schema id="VFPDataSet" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xsd:element name="VFPDataSet" msdata:IsDataSet="true">
<xsd:complexType>
<xsd:choice maxOccurs="unbounded">
<xsd:element name="Customers" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="companyname">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="40"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="customerid">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="5"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:choice>
<xsd:anyAttribute namespace="http://www.w3.org/XML/1998/namespace" processContents="lax"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<Customers>
<companyname>Ana Trujillo Emparedados y helados</companyname>
<customerid>ANATR</customerid>
</Customers>
<Customers>
<companyname>Antonio Moreno Taquería Terrible</companyname>
<customerid>ANTON</customerid>
</Customers>
However, it's not a Web service unless it can be called from the Internet. To begin with, the service needs to be located in an IIS virtual directory. If you create a directory under InetpubWWWRoot, it's automatically accessible. Otherwise, you can open up the IIS Management Console and add the directory where you created this code, and it will also work.
You must also register the DLL. You can do this with a single line of code. The following will open a DOS command window and register your XML Web Service DLL:
! Regsvr32 FoxServer..dll
Finally, you need to register your Web service and publish a Web Service Description Language (WSDL, pronounced “wizdil”) file so that potential users can see what's available. Open up the Task Pane Manager in Visual FoxPro 8 and click on the XML Web Services tab. Select Publish Your XML Web Service, and follow the dialog to the creation of a WSDL file. If that worked, pick Register an XML Web Service and enter the name of the WSDL file you just created. Yeah, you should have written it down.
You're now ready to consume your Web service in your client application. The code's already written for you. Open the Task Pane in Visual FoxPro and select the Web Services tab. Go down to the next section on the Task Pane, Explore and XML Web Service, and pull down the combo box. Your new Web service will be in the list. Select it, and the edit box below fills with about 15 lines of code. You can copy and paste this code into your consuming application, right where you would otherwise have executed a SQLEXEC() to get your company and customerid cursor. You can also open the toolbox and drag and drop the code from the Web Services tab. You only need to add six lines of code, as shown in Listing 5.17.
Example 5.17. Web Service Code Generated by FoxPro
LOCAL loFoxClass AS "XML Web Service"
* Do not remove or alter following line. It is used to support IntelliSense
*__VFPWSDef__: loFoxClass = http://localhost/foxws/foxclass.wsdl , FoxClass , FoxClassSoapPort
LOCAL loException, lcErrorMsg, loWSHandler
TRY
loWSHandler = NEWOBJECT("WSHandler",IIF(VERSION(2)=0,"",;
HOME()+"FFC")+"_ws3client.vcx")
loFoxClass = loWSHandler.SetupClient(;
"http://localhost/foxws/foxclass.wsdl", "FoxClass", "FoxClassSoapPort")
* I inserted the following 6 lines of code:
lcXML = loFoxClass.AllCustomers()
XMLTOCURSOR( lcXML, [DataFromWebService] )
SELECT ( .MainTable )
ZAP
APPEND FROM DBF ( [DataFromWebService] )
USE IN DataFromWebService
CATCH TO loException
lcErrorMsg="Error: " + TRANSFORM(loException.Errorno) ;
+ " - "+loException.Message
DO CASE
CASE VARTYPE(loFoxClass)#"O"
CASE !EMPTY(loFoxClass.FaultCode)
lcErrorMsg=lcErrorMsg+CHR(13)+loFoxClass.Detail
OTHERWISE
ENDCASE
MESSAGEBOX(lcErrorMsg)
FINALLY
ENDTRY
Writing a Web Service function to accept a parameter is just as easy. Simply declare the function with parameters—not the PARAMETERS statement used in FoxPro, however. You have to include them in parentheses, with data type declarations, just as you would do in Visual Basic. Add the function shown in Listing 5.18 to the FoxClass.PRG and rebuild the DLL.
Example 5.18. Additional Web Service to Return a Single Customer
FUNCTION OneCustomer ( CustID as String ) as String
Cmd = "SELECT * FROM Customers WHERE CustomerID ='" + CustID + "'"
Handle = SQLSTRINGCONNECT( THIS.ConnectionString )
lr = SQLEXEC( Handle, Cmd, "Customers" )
LOCAL xa AS XMLAdapter
xa = CREA ( "XMLAdapter" )
WITH xa
.AddTableSchema("Customers")
.ToXML("lcXML")
ENDWITH
xa = NULL
RELEASE xa
USE IN Customers
RETURN lcXML
ENDFUNC
Finally, you'll need a Web service to send UPDATE, INSERT, and DELETE statements back to the server.
You can also write a Web service to accept a diffgram and apply the changes back to SQL. However, be aware that FoxPro diffgrams are not identical to SQL Server diffgrams, so you will need to do some additional work. For now, sending SQL pass-through statements works fine, and you already know how to write them. The function shown in Listing 5.19 shows how to handle them.
Example 5.19. Additional Web Service to Accept a Diffgram
FUNCTION SPT ( Cmd as String ) AS String Handle = SQLSTRINGCONNECT( THIS.ConnectionString ) lr = SQLEXEC( Handle, Cmd, "Customers" ) Return IIF ( lr < 0, [Error], [Ok] ) ENDFUNC
Now that you've made those changes, your FoxPro SQL application needs only to change from calling SQLExec to calling the appropriate WebService function and passing the SQL command built using the SendInsert, SendUpdate, or SendDelete function.
To demonstrate how you can add, edit, and delete records that are located on a computer on the other side of the Internet, I'm going to start with a form containing a grid that displays all of the records in a table. It's a simple design and eliminates the need to add a search form. However, you'll be able to see exactly how such a form could be built, using the techniques required for this example. Add, Edit, Delete and Close buttons at the bottom of the screen will provide the necessary functionality.
My example form is shown in Figure 5.4.
To build it, create a new Windows Application project called UseWS. Both the Solution and the Windows Application Project will be created wherever your default Visual Studio Projects directory is located, based on the default setting in your Tools, Options, Environment, Projects and Solutions default directory. Rename the form frmAllCustomers.vb. Add a DataGrid and four command buttons named cmdAdd, cmdEdit, cmdDelete, and cmdClose, with text captions to match, as shown in Figure 5.4. Double-click on the Close button and enter a single command: End. We'll code the other three buttons shortly.
When you use a local data source, you can add a data adapter and create a dataset from it. But when you're using Web Services, you need to create the Web service first, add a Web reference, and then base the dataset definition on the Web reference. So before going any further, we'll create the Web service.
It's as easy to build a Web service in Visual Basic .NET as it is in Visual FoxPro 8. Add a new project of type Visual Basic ASP.NET Web Service to the solution. Name it Chapter5WebService. This will create a project file named Chapter5WebService.vbproj and a Web service file named Chapter5WebService.asmx. Asmx files aren't Web pages, so there are no visible controls to drop on them. However, data controls are used with XML Web Services, and they make data access very easy. Drag a SQLDataAdapter to the design surface. When the Data Adapter Wizard opens, select the Northwind connection that you created earlier. When asked to type a SELECT command, enter
SELECT * FROM CUSTOMERS
to return the entire Customer file. The wizard will generate a DataAdapter, which you can name daAllCustomers. Right-click on the DataAdapter and select Generate Dataset to create a typed dataset. Change the default name of Dataset1 to dsAllCustomers.
Drag a second DataAdapter to the Web Service design surface to provide a dataset for a single record. For the SQL statement, enter this:
SELECT * FROM CUSTOMERS WHERE (CustomerID = @CustomerID)
This creates a parameterized query, which will return one record. You can name the new DataAdapter daOneCustomer and the dataset dsOneCustomer. You'll be surprised how easy it is to send a parameter to a Web service and retrieve the selected record.
You'll need to open the Config.Web file in the project and add the following line immediately after the <system.web> tag:
<identity impersonate="true" >
This provides data requests access to the Web server without requiring a user login. While you're at it, enter the following immediately before the <system.web> tag:
<appSettings> <add key="ConnectionString" value="user id=sa;data source=localhost;persist security info=False;initial catalog=Northwind" /> </appSettings>
This will allow us to read the connection string from a text file that can easily be changed when we deploy the application. We'll add that feature near the end of this topic.
Now you're ready to build our Web service. We'll need a function to populate the grid in the main application screen. That's what the dsAllCustomers dataset was for. When the user selects a record to edit, the second dataset, dsOneCustomer, will be used to return it. The Delete command button will just pass the key of the record to be deleted, to demonstrate the flexibility of Web services. They're called XML Web Services, but you don't have to pass XML back and forth. XML is just the transport mechanism for the parameters and results. You can send rows and columns back as XML, but scalars (strings, integers, dates, and the like) are just scalars.
Double-click on the design surface to open the code window, and enter the code shown in Listing 5.20.
Example 5.20. Web Service Functions
<WebMethod()> Public Function GetAllCustomers() _
As dsAllCustomers
Dim AllCustomers As New dsAllCustomers
SqlDataAdapter1.Fill(AllCustomers)
Return AllCustomers
End Function
<WebMethod()> Public Function GetOneCustomer( _
ByVal RecordKey As String) _
As dsOneCustomer
Dim OneCustomer As New dsOneCustomer
SqlDataAdapter2.SelectCommand.Parameters(0).Value = RecordKey
SqlDataAdapter2.Fill(OneCustomer)
Return OneCustomer
End Function
<WebMethod()> Public Function UpdateCustomers( _
ByVal customerChanges As dsOneCustomer) _
As dsOneCustomer
If Not (customerChanges Is Nothing) Then
SqlDataAdapter2.Update(customerChanges)
Return customerChanges
Else
Return Nothing
End If
End Function
<WebMethod()> Public Sub DeleteOneCustomer(ByVal RecordKey As String)
Dim cn As New SqlClient.SqlConnection
Cn.ConnectionString = ConfigurationSettings.AppSettings("ConnectionString")
cn.Open()
Dim sc As New SqlClient.SqlCommand("DELETE CUSTOMERS WHERE CustomerID='" + RecordKey + "'", cn)
sc.ExecuteNonQuery()
cn.Close()
End Sub
AllCustomers returns all of the records in the Customers table of the Northwind database. For a few hundred records, performance will be adequate, as you'll see when you run this application.
The second line of the DeleteOneCustomer method shows you why I added the string to Web.Config a few paragraphs earlier. The program can read the Web.Config file and find and return the connection string stored in the file. That means you can change the server name, or even the database name, user ID or password, simply by editing a text file on the server.
The function declaration for GetOneCustomer says that it has a string parameter named RecordKey, which is passed to it. Its return value (the As clause at the end of the function declaration) is a dataset—specifically, a dsOneCustomer dataset. So this function returns xml. So does AllCustomers because its return type is also a dataset.
UpdateCustomer accepts a single parameter of type dsOneCustomer—a dataset—and returns a dataset. It uses the dataset that is sent to it to call the data adapter's Update method, which applies the changes in the dataset (which is actually a DiffGram) to the source table.
Finally, DeleteOneCustomer takes a string containing a CustomerID value, deletes one record, and returns nothing.
Right-click on the UseWS project's References section and select Add Web Reference. You'll see the Start Browsing for Web Services screen shown in Figure 5.5.
This lets you pick a Web service from your development machine or from any computer on the Internet. Select the first link, Web Services on the Local Machine, and you'll be able to pick your new Web service from the Web services that you've built on your development computer, as shown in Figure 5.6. This screen is a welcome addition to Visual Studio 2003. The first release of Visual Studio required you to remember the name of your WSDL file, and it's easy to forget when you're building half a dozen of them a day.
Select Chapter5WebService and you'll see the screen shown in Figure 5.7, which confirms that the functions and subs that we added are indeed available.
Finally, you'll need to add a dataset to hold the customer records returned from the Web service. Drag a Dataset from the Data tab of the toolbox and drop it on the form. The dialog that appears will offer to base the dataset, not on a DataAdapter as in our previous examples, but rather on the Web Reference that we added. Select it and name it dsAllCustomers. This creates a typed dataset that's added to the project. You'll still need to fill it, but the code will be a little different.
The code to perform the functions represented by the buttons on the main application form is shown in Listing 5.21.
Example 5.21. Code to Interact with the Web Service Functions
Private Sub frmAllCustomers_Load( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles MyBase.Load
LoadData()
End Sub
Public Sub LoadData()
SuspendLayout()
Dim ws As New UseWS.localhost.Chapter5WebService
ws.Credentials = System.Net.CredentialCache.DefaultCredentials
DsAllCustomers1.Clear()
DsAllCustomers1.Merge(ws.GetAllCustomers())
DataGrid1.DataSource = DsAllCustomers1
DataGrid1.DataMember = "Customers"
ResumeLayout(False)
End Sub
Private Sub cmdAdd_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles cmdAdd.Click
Dim frm As New frmEditOneCustomer
frm.RecordKey = "Add"
frm.ShowDialog()
LoadData()
End Sub
Private Sub cmdEdit_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles cmdEdit.Click
Dim Recordkey As String = _
DsAllCustomers1.Tables(0).Rows(DataGrid1.CurrentRowIndex)(0)
Dim frm As New frmEditOneCustomer
frm.RecordKey = Recordkey
frm.ShowDialog()
LoadData()
End Sub
Private Sub cmdDelete_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles cmdDelete.Click
Dim Recordkey As String = _
DsAllCustomers1.Tables(0).Rows(DataGrid1.CurrentRowIndex)(0)
Dim ws As New UseWS.localhost.Chapter5WebService
ws.Credentials = System.Net.CredentialCache.DefaultCredentials
ws.DeleteOneCustomer(Recordkey)
LoadData()
End Sub
Private Sub cmdClose_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles cmdClose.Click
End
End Sub
The form's Load event calls the LoadData method to get the entire Customers table, which is returned as an XML dataset. I call this routine after any sort of change (add, edit, or delete) to the source table, so the routine clears the dataset, creates a proxy to host the Web service's methods, authenticates the proxy (that's what that modification to config.web was for), calls the GetAllCustomers method, and uses the Merge method of the proxy to dump the records into the dataset.
Because an XML dataset is the native data source for a DataGrid, we set the data grid's DataSource to the dataset. We also have to set the DataMember to the precise table name; if we don't, a little “+” appears in the grid for us to expand and pick a table—never mind that there's only one. You can also use DataMember = dsAllCustomers.Tables(0), but any time you can spell out what's happening in .NET you're better off. It's arcane enough as it is.
When the user clicks on either Add or Edit, we create an instance of the frmEditCustomer form class, which you'll see in the next section. In the case of an Edit, we pass the value of the key for the currently selected record in the grid, which is returned by the expression
DsAllCustomers1.Tables(0).Rows(DataGrid1.CurrentRowIndex)(0)
That (0) out at the end means “column 1”, for reasons that were the subject of at least one rant in an earlier chapter. This is perhaps the oddest syntax that I've come across in .NET. But it returns the key of the record to edit. Passing the string “Add” instead will be handled by code in the Customer Edit form, which you'll see at the end of this section. In both cases, we use the instance's ShowDialog() method, which is like setting a FoxPro form's WindowType = 1 – Modal and activating the form. The next line, LoadData(), executes when control returns from the modal form.
Delete is accomplished simply by using the value of the key field from the current record to call the Web service proxy's DeleteOneRecord() method. I did it a little differently just to demonstrate how Web service functions act almost exactly like methods on local classes. They can't operate on global variables, but they can pass parameters back and forth; and the parameters can be tables, in the form of datasets.
Figure 5.8 shows the EditCustomer form.
The form consists of labels and text boxes for each of the fields in the Customer Record. I also included a text box for the record key at the upper-right corner of the screen so that you can see what's happening, although it wouldn't be necessary or desirable in a production environment. In each text box, I set the Databinding for the Text property to the corresponding field in the dsOneCustomer dataset.
The code shown in Listing 5.22 either finds the record to edit and displays it on the screen, or adds a blank record and inserts the first five characters of a GUID (pronounced as two syllables, GOO-id) converted to uppercase.
The approach to passing a parameter is quite different in Visual Basic than in FoxPro. In FoxPro, you include a PARAMETERS statement as the first line in the INIT event code, and you've got parms. In Visual Basic, the easiest way is to create a property procedure and do the record lookup in its Set method, which is just like a FoxPro property's Assign method. The code that triggers this event is the statement frm.RecordKey = RecordKey in cmdEdit_Click method in Listing 5.21, and the statement frm.RecordKey="Add" in the cmdAdd_Click method in the same listing.
Example 5.22. Code for the frmEditCustomer Form
Public Property RecordKey() As String
Get
Return _RecordKey
End Get
Set(ByVal Value As String)
_RecordKey = Value
If _RecordKey = "Add" Then
DsOneCustomer1.Clear()
Dim dr As DataRow
dr = DsOneCustomer1.Tables(0).NewRow
Dim I As Integer
For I = 0 To dr.ItemArray.Length - 1
dr(I) = ""
Next
dr(0) = Guid.NewGuid.ToString.Substring(0, 5).ToUpper
DsOneCustomer1.Tables(0).Rows.Add(dr)
Else
Dim ws As New UseWS.localhost.Chapter5WebService
ws.Credentials = System.Net.CredentialCache.DefaultCredentials
DsOneCustomer1.Merge(ws.GetOneCustomer(_RecordKey))
End If
End Set
End Property
Private Sub cmdSave_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles cmdSave.Click
BindingContext(DsOneCustomer1, "Customers").EndCurrentEdit()
If DsOneCustomer1.HasChanges Then
Dim ws As New UseWS.localhost.Chapter5WebService
ws.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim diffCustomers As New UseWS.localhost.dsOneCustomer
diffCustomers.Merge(DsOneCustomer1.GetChanges())
diffCustomers = ws.UpdateCustomers(diffCustomers)
DsOneCustomer1.Merge(diffCustomers)
DsOneCustomer1.AcceptChanges()
Close()
End If
End Sub
Private Sub cmdCancel_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles cmdCancel.Click
Close()
End Sub
If the record key is Add, it means that the user clicked on the Add button. What follows looks like a lot of code, and compared to FoxPro, it is. But you can't just APPEND BLANK and REFRESH in Visual Basic .NET. You have to Clear the dataset, create a DataRow object based on one of the dataset's rows using the NewRow() method, assign a blank string to all of the fields (that was easy because all of the fields in this table are of type character) so that they won't be DBNulls, and finally add the data row to the empty dataset. If they passed a valid record key, we instead call the Web server proxy's GetOneCustomer method and use the resulting returned XML string to fill the dataset. Databinding takes care of the rest.
The RecordKey property procedure is invoked back in the AllCustomers form in the frm.RecordKey = String statement. The Set method for a property procedure is just like a FoxPro property's Assign method. When you assign the value, the procedure executes. It looks complicated, but it works.
The cmdSave_Click code is also a little more complicated than FoxPro's TableUpdate() function, but not much more involved than the equivalent FoxPro Web Service proxy code generated by dragging your Web service name from the FoxPro toolbox to your code window. You have to end the current edit using the EndCurrentEdit() method, and then call the proxy Web service UpdateCustomers method passing it the changes in the record in the form of a DiffGram created using the GetChanges() method of the dataset. Finally, you have to call the dataset's AcceptChanges() method and close the form. The cmdCancel Click event just closes the form without doing anything.
You should be aware that there is another technology for using remote data services with smart client applications. It's called remoting. Remoting sends data in a more compact format, and has many more options, than Web services. It's more complicated than XML Web Services, although I've seen examples that are only 30 or 40 lines of code.
Nonetheless, even the simplest implementation of remoting borders on a level of complexity that stretches the limits of the typical FoxPro developer's skill. .NET is simply a lot harder than FoxPro, and remoting is a pretty good example of that inherently greater complexity. For that reason, it's beyond the scope of this book. However, if you're intent on building a major smart client application and suspect that minimizing data traffic might become important, you should take a look at remoting.
This chapter has covered a topic that is large, but hopefully straightforward. FoxPro has simple ways to do simple things, but as the task becomes more complicated it takes more code to get the job done. With Visual Basic .NET, the complexity is about the same for all three types of database operations.
If you're using DBFs, you probably figured out that you don't want to send DBFs using Web Connection's Compression methods, because they don't work with long field names. They require a DBC, and you can't send a DBF that's part of a DBC without sending the DBC, DCX, and DTC files as well. And if you think you can just keep all of your field names to 10 characters or less, go look at the field names your users have been using. I've seen field names 30 characters long in SQL databases.
That's why our examples all have short field names. But in the real world they don't, so you may have problems demonstrating the difference between DBF and SQL access if your SQL tables have long field names.
It may look complicated, but it's really pretty easy to do. If you download the source code, you can have it up and running in 30 minutes, and you can probably adapt one of your own forms to use my library of functions (with a few modifications) in an hour or so. And I should point out that the full version of Web Connection contains hundreds of functions, a number of which would make some of the code that I wrote here unnecessary. In fact, there's a function to package a DBF and its associated FPT for transmission in a single command. And there are many, many more.
I don't write purely LAN-based database applications any more, unless users absolutely insist that they don't want Internet access to their data. This kind of app will run just as well on a local area network as it does over the Internet, with no modifications. And if you happen to be on the road and want to hook up with your database, it's just a phone call away.
Visual FoxPro 8 has great new features for building XML Web Services. I hope this doesn't sound like an advertisement. I don't work for West Wind and don't have any interest in the company. However, you may conclude that even though you could save a few hours' pay by building Web Services in Visual FoxPro 8, there's no better combination than FoxPro, Web Connection, and XML for building database applications for the Internet.