|
Visual FoxPro is a cross-platform, visual tool for database development that includes a number of wizards. Here, George uses the WWW Search Page Wizard to run Visual FoxPro programs across the Internet.
December 01, 1996
URL:http://www.drdobbs.com/web-development/web-programming-with-visual-foxpro/184410020
as a template
George is the founder of Micro Endeavors, a training and development firm. He is also the author of Creating FoxPro Applications (Que Corp., 1993). George can be contacted at [email protected].
Visual FoxPro (VFP) is a database development tool that lets you create classes either visually or by command-line coding. Visual FoxPro comes with a number of wizards--one of which is the WWW Search Page Wizard that builds HTML documents instead of VFP objects. You can then link that HTML search page to a VFP table or view. In this article, I'll provide a detailed look at how you can leverage the technology in the WWW Search Page Wizard to run VFP programs across the Internet. In the process, I'll show how you can use it as a template for building your own Internet database-programming tools.
The WWW Search Page Wizard consists of the WWWPAGE.APP wizard, VFPCGI.EXE CGI script, and SERVER.APP query-server application. For the purpose of this article, the "user" of the WWW Search Page Wizard will load a WWWPAGE.APP-generated HTML document into a web browser, provide a single value to use in a simple query, and view the result. The "developer" will use WWWPAGE.APP to generate the HTML query document and install the SERVER.APP and VFPCGI.EXE files on a Windows NT-based Internet server. None of the WWW Search Page Wizard components reside on the user's machine. The HTML document includes instructions for the Internet server's HTTP service to run a copy of the VFPCGI.EXE script (also on the Internet server) when the user elects to search. VFPCGI.EXE writes files to a directory on the Internet server. SERVER.APP constantly searches the Internet server, looking for files written by the VFPCGI.EXE script.
In short, the WWWPAGE.APP wizard is used to create a search page, a result-page template, and a query template. VFPCGI.EXE is used to convert search requests received from the web browser into text files. The SERVER.APP reads the text files, runs the query, and places the results in an HTML document. The resultant document is picked up by VFPCGI.EXE and returned to users through the HTTP service.
The WWWPAGE.APP wizard employs the standard Microsoft Wizard interface to prompt the developer for the following:
Using this developer-supplied information, WWWPAGE.APP creates the following:
When users load DrDobbs.htm, they are presented with a text box and command button. After filling in the text box and electing to search, the line <FORM ACTION="vfpcgi.exe?IDCFile=DRDOBBS.IDC" METHOD="POST"> in DrDobbs.htm is executed on the Internet server.
The HTTP server runs a copy of the VFPCGI.EXE script, passing the user-entered contents from DrDobbs.htm as an argument. The CGI script creates a file name, writes the passed information to a .DAT file of that name, writes a .ATN file of that name, and spends up to 60 seconds waiting for a .ACK file with the same name as the .ATN and .DAT files to appear.
The HTTP server starts a new copy of the VFPCGI.EXE script whenever a user utilizes the DrDobbs.htm. As a result, there may be many VFPCGI.EXE scripts running simultaneously. Luckily, these scripts are small--less than 45 KB in size.
While there may be many VFPCGI.EXE scripts running at the same time, there is probably only one copy of Visual FoxPro running on the server. Theoretically, you could start a new copy of VFP for each query request. However, since VFP takes several seconds to load, and consumes in excess of 4 MB of RAM, it's not advisable.
Since VFP 3.0b does not support multiple threads, SERVER.APP must process each query sequentially. To support a high volume of simultaneous query requests, WWWPAGE.APP forces the creation of simple, filtered, Rushmore-optimized queries. This results in the creation of queries that take less than two seconds to complete. Further, the WWWPAGE.APP and SERVER.APP combine to limit the number of rows returned to a maximum of 99. As a result, a single instance of SERVER.APP can process dozens of queries per minute.
SERVER.APP uses a form that contains a timer object. Once every second, the object checks for the existence of *.ATN files in a developer-defined directory. When a .ATN file is discovered, that file is erased and the .DAT file of the same name is opened with VFP's low-level file I/O functions. User input is read from the .DAT file and stuffed into the query defined in DrDobbs.idc. From there, a macro expansion executes the query. SERVER.APP writes the results to the .DAT file and then creates a .ACK file. At this point, the VFPCGI.EXE script that wrote the original .DAT and .ATN files sees the .ACK file and returns the .DAT file to the HTTP service for delivery to the waiting user.
While SERVER.APP is easy to explain at a high level, it requires almost 2000 lines of code to implement (mostly error trapping, bullet proofing, configuration management, and so on). In comparison, SERVER2.APP, the program I'll present here, is about 100 lines of VFP code (along with the existing VFPCGI.EXE script and one hand-coded HTML document). With SERVER2.APP, you can type Visual FoxPro code into a browser, and cause a copy of SERVER2.APP running on the server to execute that code.
The HTML code in Listing One presents users with a simple text area (VFP developers call this an "Editbox") in which to enter lines of code. Figure 1 is the display generated by Listing One. Since the VFPCGI.EXE script was provided as part of the original WWW Search Page Wizard, all that remained was to write the new SERVER2.APP.
My plan was simple: Use a DO/WHILE loop to wait for a .ATN file to appear, read the .DAT file containing the user's input, write that user code to a .PRG file, compile and run the .PRG file, and write a .ACK file for the VFPCGI.EXE script to read. To provide feedback to the user, I create the beginning of an HTML document using VFP's native FOPEN() function, pass the handle of the HTML document to the user's program, compile and do the user's program, and then complete the document.
For example, to create a "Hello World" program using only a web browser, the user enters Example 1(a) in the text area of the DrDobbs.htm page. Upon selecting the Search button, the user receives a new document containing the words "Hello World." The user then enters Example 1(b) to retrieve a web page with the structure of the mestates table. Finally, the user can find all of the cities in New Jersey, ordered by county, by entering Example 1(c) into the text area of DrDobbs.htm; see Figure 2.
SERVER2.APP can run on any number of machines (or instances of VFP on a single machine) that have read/write privileges on the HTTP server directory where the .DAT, .ACK, and .ATN files are written. To test the application, I ran a single instance of VFP on the actual HTTP server to reduce network traffic. For the sake of clarity, I put all code for the SERVER2.APP in a single SERVER2.PRG file.
When the HTTP server receives a request from a browser to run the VFPCGI.EXE CGI script, VFPCGI.EXE writes its .DAT and .ATN files to the \temp directory. Consequently, SERVER2.PRG first looks for .ATN files on the \temp directory of the HTTP server. This is done with a DO WHILE .T. loop and the SYS(2000) get file function. Figure 3 shows the VFP development environment during the creation of the SERVER2.APP program.
Once you have the name of the .ATN file, you can erase it with the ERASE command. Because you know that the .DAT file will have the same name as the .ATN file, you can use VFP's STRTRAN() function to determine the name of the .DAT file.
Next, use VFP's FOPEN(), FSEEK(), and FREAD() low-level file I/O functions to put the content of the .DAT file into the l_ccontent memory variable. Modify the l_ccontent string to ignore extra information provided by the CGI script and to undo string translations performed by the HTTP server.
The CGI script writes the user's input at the beginning of the .DAT file. Rather than use a delimiter, however, the CGI script adds the value of the CONTENT_ LENGTH environment variable to the .DAT file. The CONTENT_LENGTH identifier is prefaced with an ampersand, so a simple ATC() string search, coupled with a VAL() function to translate the string into a numeric value, determines the number of characters at the beginning of l_ccontent that comprise the user's input. The LEFT() function is then used to truncate the l_ccontent variable appropriately.
The HTTP service converts all spaces (CHR(32)) to plus signs (CHR(43)). This is easily corrected with the CHRTRAN() function. The HTTP service also converts characters outside the range of a-z, A-Z, and 0-9 to hex preceded by a percent sign (CHR(37)). This requires a bit more work, including the use of the simple hextochr user-defined function found at the end of Listing Two.
After skipping over the "Searchparam=" string at the beginning of the l_ccontent string, use FCREATE() and FWRITE() to create a .PRG file. The COMPILE command compiles the .PRG into an .FXP file.
After FCLOSE() closes the l_nfhdat file handle, FCREATE() overwrites the .DAT file in preparation for the return document. Write the HTML headers to the .DAT file, and you are almost ready to run the user's compiled code.
To prevent users from crashing the server, prepare a simple error trap using the ON ERROR v_nErr=1 command. If an error occurs during the user's program, the error v_nErr variable is set to 1, and the rest of the program continues execution.
With the error trap in place, use the DO (l_cPrgfile) WITH (l_nFhdat) command to run the user's code and pass the file handle of the return document. Any output written to that file handle by the user's code will appear in the body of the HTML document.
Next, check the state of our v_nErr flag. If v_nErr has been set to 1, use the AERROR() function to obtain information about the last error that occurred, then write that information to the return page.
Error or not, finish the return page by writing the HTML footers, closing the file handle, and writing the .ACK file. When the CGI script sees the .ACK file, it returns the newly written .DAT file to the HTTP service, which returns the page to the user's browser.
SERVER2.PRG raises several issues worthy of discussion. First, users could include commands such as ERASE mestates.dbf in their programs. This kind of vandalism can be prevented by limiting read and write privileges on the server.
Second, this entire technique can be easily duplicated on a LAN or Intranet environment.
Third, once a program has been compiled, there is no reason to throw it away. User-generated programs could be saved as named .PRGs or stored procedures for subsequent reuse. In this way, VFP could simply rerun the program code without the need to recompile.
Fourth, this VFP solution need not be limited to accessing local VFP tables. VFP databases can store views of remote data, allowing fast, flexible access to SQL Server and other client/server databases.
Visual FoxPro is an inexpensive, flexible, and fast database-server solution for Internet database access. The growing ubiquity of web browsers offers VFP developers the opportunity to offer the benefits of Visual FoxPro's incredibly fast database access, business rules, data storage, OOP language, and procedural-language constructs to users who don't have VFP, and who can't directly connect to VFP databases. While many applications will still be created entirely in VFP, it's good news for VFP developers that the power of .PRGs and .DBCs is still valuable in a world full of Internet users.
(a) Parameters p_nfh
=fputs(p_nfh,"Hello World")
(b) Parameters p_nfh
local l_n,l_nqty
use mestates share
l_nqty=afields(l_aflds)
for l_n=1 to l_nqty
=fputs(p_nfh,l_aflds[l_n,1])
endfor
use in mestates
return
(c) parameters p_nfh
use mestates share
select * from mestates where st_state="NJ"
and st_county<>" " ;
order by st_county,st_city into cursor drdobbs
scan
=fputs(p_nfh,drdobbs.st_city+drdobbs.st_county+;
drdobbs.st_state)
endscan
use in DrDobbs
use in mestates
return
Terms of Service | Privacy Statement | Copyright © 2024 UBM Tech, All rights reserved.
Listing One
<HTML>
<HEAD>
<TITLE>Micro Endeavors FoxPro Web Compiler</TITLE>
</HEAD>
<CENTER><h1>Enter your code and press Search to execute </h1></CENTER>
<P>
</BODY>
<P>
<CENTER>
<P></P>
<FORM ACTION="vfpcgi.exe?IDCFile=MEST02M.IDC" METHOD="POST">
<P><TEXTAREA NAME="searchparam" ROWS="10" COLS="65">Parameters p_nfh</TEXTAREA>
<P>
<INPUT TYPE="SUBMIT" VALUE="Search">
<br>
<hr>
</FORM>
</BODY>
</HTML>
Listing Two
* Server2.prg
* Control loop for
CLEAR
DO WHILE .T.
@ 0,70 SAY TIME()
IF INKEY(1)<>0 && 1 second pause to reduce excessive disk access
EXIT
ENDIF
DO myfiler
ENDDO
PROCEDURE myfiler
LOCAL l_cFilename,l_nFhprg,l_nFhack,l_nTries,l_nContentlength,l_cContent,;
l_nFhprg,l_cFilename,l_cDatfile,l_nSize,l_nSpot,l_cChar,;
l_cPrgfile,l_aErrs,l_cFullstring,l_nRet
PRIVATE v_nErr
** look for ATN file
l_cFilename=SYS(2000,"c:\temp\*.atn")
IF EMPTY(l_cFilename)
RETURN
ENDIF
l_cFilename="c:\temp\"+l_cFilename
ERASE (ALLTRIM(l_cFilename))
? l_cFilename
** Translate ATN file name to obtain .DAT file name
l_cDatfile=STRTRAN(l_cFilename,".ATN",".DAT")
** Try to open the .DAT file 10 times before quitting
FOR l_nTries=1 TO 10
l_nFhdat=FOPEN(l_cDatfile) IF l_nFhdat>=0
EXIT
ENDIF
ENDFOR
IF l_nTries>10
RETURN
ENDIF
l_nSize=FSEEK(l_nFhdat,0,2) && get size of file
=FSEEK(l_nFhdat,0,0) && reposition file pointer
l_cContent=FREAD(l_nFhdat,l_nSize) && read file into l_ccontent var
** Use &CONTENT_LENGTH environment string to determine length
** of "content" sent by the user.
l_nContentlength=VAL(SUBSTR(l_cContent,ATC("&CONTENT_LENGTH=",l_cContent)+16))
** truncate l_ccontent to only contain user input
l_cContent=LEFT(l_cContent,l_nContentlength)
? l_nSize,l_nFhdat
? l_cContent
** translate all "+" to " ".
l_cContent=CHRTRAN(l_cContent,"+"," ")
** Convert hex to ascii.
DO WHILE "%"$l_cContent
l_nSpot=ATC("%",l_cContent)
l_cChar=hextochr(SUBSTR(l_cContent,l_nSpot+1,2))
l_cContent=STUFF(l_cContent,l_nSpot,3,l_cChar)
ENDDO
** Content is preceded by "searchparam=", so strip first 12
** characters from l_ccontent
l_cContent=SUBSTR(l_cContent,13)
? l_cContent
** Get a filename with PRG extension.
l_cPrgfile=STRTRAN(l_cDatfile,".DAT",".PRG")
** create the PRG file
l_nFhprg=FCREATE(l_cPrgfile)
** Copy translated user input to the PRG file
=FWRITE(l_nFhprg,l_cContent)
=FCLOSE(l_nFhprg)
** Use VFP's runtime compiler to compile the program
COMPILE (l_cPrgfile)
=FCLOSE(l_nFhdat) && give up the .DAT file handle
** Prepare to return data to the user by overwriting the
** original .DAT file.
l_nFhdat=FCREATE(l_cDatfile)
** Put HTML header code into .DAT file now:
=FPUTS(l_nFhdat,"Content-Type: text/html"+CHR(13)+CHR(10))
=FPUTS(l_nFhdat,"<HTML>")
=FPUTS(l_nFhdat,"<HEAD>")
=FPUTS(l_nFhdat,"<TITLE>Micro Endeavors Remote Compiler Return Page</TITLE>")
=FPUTS(l_nFhdat,"</HEAD>")
=FPUTS(l_nFhdat,"<HEAD><TITLE>Micro Endeavors VFP Compiler Server"+;
"</TITLE></HEAD>")
=FPUTS(l_nFhdat,"<BODY>")
=FPUTS(l_nFhdat,"<CENTER><H1>Program Results</H1><H2>Micro Endeavors' WWW"+;
"Program Server</H2><HR></CENTER>")
=FPUTS(l_nFhdat,"<PRE>")
** establish error trap
v_nErr = 0 ON ERROR v_nErr = 1
** Run the user's program, passing the file handle of the return page.
DO (l_cPrgfile) WITH (l_nFhdat)
** See if he crashed.
IF v_nErr = 1
** gather info about the last error, and send it back on return page.
=AERROR(l_aErrs)
l_cFullstring = "ERROR MESSAGE: "+ TRANSFORM(l_aErrs[1,2],"")
=FPUTS(l_nFhdat,l_cFullstring)
ENDIF
** Finish the HTML return page
=FPUTS(l_nFhdat,"</PRE>")
=FPUTS(l_nFhdat,"</BODY>")
=FPUTS(l_nFhdat,"</HTML>")
=FCLOSE(l_nFhdat)
** create the ACK file
l_nFhack=FCREATE(STRTRAN(l_cDatfile,".DAT",".ACK"))
=FCLOSE(l_nFhack)
** cleanup before processing next request
CLEAR ALL
CLOSE ALL
CLEAR PROGRAM
CLEAR
RETURN
PROCEDURE hextochr
PARAMETERS p_c
LOCAL l_nRet
l_nRet=CHR((ATC(LEFT(p_c,1),"0123456789ABCDEF")-1)*16+;
ATC(RIGHT(p_c,1),"0123456789ABCDEF")-1)
RETURN l_nRet