Visual FoxPro Database Publishing on the Internet

1. ™ Visual FoxProDatabase Publishing on the Internet • by Rick Strahl • West Wind Technologies • http://www.west-wind.com/

2. Internet Development • Businesses are expanding their operations onto the Internet • Internet Development is exploding • Active, Database Applications are in high demand

3. Why build Web Applications?Issues that make the Web hot • Distribute widely • Administer centrally • Universal Client Interface • Application Platform of the future

4. Limitations of Web ApplicationsOr: Two steps forward, one step back... • Configuration issues • Interface limitations of HTML • Indirect data accesss through server • Mostly non-visual development • Server based programming model

5. Browser provides the Active interface • Web Server provides data / application connectivity • The Internet Server API (ISAPI) is the building block for server side extensions

6. How the Active Web Works Client side Server side Active Documents Webserver Static HTML Pages Web Browser Displays HTML HTTP Server Extensions ISAPI/CGI Browser Scripting: VBScript JavaScript ActiveX Controls/ Java Dynamic Data and Database ‘TheWall’

7. Common Gateway InterfaceTraditional Web Interface CGI.exe (1) (EXE, CMD, BAT) CGI.exe (2) (EXE, CMD, BAT) CGI.exe (n) (EXE, CMD, BAT) New system process for each instance of script. (relatively slow, resource intensive) Web Server HREF=“/cgi-bin/cgi.exe?Parms”

8. Internet Server API (ISAPI)Extending the Architecture within the Server Web Server HREF=“/scripts/isapi.dll?parms” MyISAPI (1,2..n) (Multithreaded, In-Process DLL) OtherISAPI (Multithreaded, In-Process DLL) Loads DLL once after which it stays resident, processing multiple requests simultaneously.

9. Internet Server API (ISAPI) The extension interface for IIS Static content (e.g., HTML) Microsoft Exchange Databases (FoxPro, SQL, Access etc.) Custom scripts, Interfaces to other applications etc. ODBC MAPI Internet Database Connector Exchange Web Connector Active Server Pages (Denali) Custom ISAPI Scripting ISAPI Microsoft Internet Information Server

10. Getting StartedWhat you need for building Web apps • Fast Pentium box (133Mhz/32-64megs) • WindowsNT (recommended) • Web Server • Connector Interface/Application • Web browser • Basic HTML skills • Everything can run on 1 box!

11. Connecting Visual FoxPro DataSome of the tools available for IIS • Active Server Pages (IIS 3.0) • FoxISAPI connector (OLE) • West Wind Web Connection (ISAPI/OLE)

12. Active Server Pages (IIS 3.0)Server side scripting for IIS • Object Based Architecture • Tight integration with IIS • Database Connectivity with Active Data Objects (ADO) • Supports external object creation • Several sophisticated objects are built-in

13. Active Server ArchitectureComponents Galore Response/Request Objects (Input and Output) Server Object (System Services) ASP.DLL (ISAPI Extension) Scripting Engine (VBScript/JavaScript) Active Data Objects (ODBC) Session/Application Objects (Keeping State)

14. Active Data Objects • Lightweight ODBC Connector • Implements OLE DB (ODBC 3.5) • Based on Visual Basic’s Remote Data Object • It’s fast especially when tied to a persistent connection object! • Implemented as Automation Object.

15. Automation Server Access OLE EXE Server (OutOf Process) TClass::Tmethod() ASP Scripting Engine HTML containing VBScript code Creates Object Reference: <% oServer=Server.CREATEOBJ(“Tserver.TClass“) cVar=oServer.Tmethod(“Parm1“,1) %> WebServer ADO Data Object (ODBC Data Access) OLE DLL Server (InProcess) HREF=“MyPage.ASP”

16. Active Server Summary Pros: • Tight Integration with IIS • No hassle configuration • Very easy for simple active content Cons: • Code Management • Automation Server Scalability • Scripting Language Limitations

17. FoxISAPIConnecting VFP Automation servers • Direct link from Web pages • ISAPI DLL creates persistent Automation object • DLL does equivalent of: • Passes form vars in parameter • Passes server vars in INI file oServer=CREATEOBJECT(“TOleServer.TOleClass”) oServer.YourMethod(“UserId=1”,”c:\temp\fox2.ini”)

18. How FoxISAPI works Visual FoxPro OLE Server (loaded once then stays in memory) Method1 Method2 Methodn returns HTML Document passes HTML Form Data WebServer FOXISAPI.DLL (HREF=“foxisapi.dll/Server.Class.Method”) multithreaded/running InProcess

19. Hello World with FoxISAPI HREF=“/scripts/foxisapi.dll/TDevCon.TFoxIsapi.Helloworld?” DEFINE CLASS TFoxISAPI AS Custom OLEPUBLIC FUNCTION Helloworld LPARAMETER lcFormVars, lcIniFile, lnReleaseFlag LOCAL lcOutput #DEFINE CR CHR(13)+CHR(10) *** HTTP header - REQUIRED on each request! *** System Defines lcOutput="HTTP/1.0 200 OK"+CR+; "Content-type: text/html"+CR+CR lcOutput=lcOutput+; "<HTML><BODY>"+CR+; "<H1>Hello World from Visual FoxPro</H1><HR>"+CR+; "This page was generated by Visual FoxPro...<HR>"+CR+; "</HTML></BODY>" RETURN lcOutput ENDDEFINE

20. FoxISAPI Method Rules • Must take 3 parameters • lcFormVar - HTML Form vars or ‘parameters’ passed on the URL • lcIniFile - filename containing server var • lnReleaseFlag - Set to keep or release server reference. • Must return HTTP compliant output • HTML document including HTTP header • Use custom HTTP headers for things like authentication, redirection, Cookies etc.

21. Set up for FoxISAPI • OLE Server must be registered • Copy FoxISAPI.dll into script dir • Directory must have Web Server Execute rights set! • Run DCOMCnfg on NT 4.0 • Add IUSR_ account to default rights • Set user to Interactive user on the specific server • Need to re-run whenever server is rebuilt

22. FoxISAPI OLE Instancing • InProcess DLL • Very fast • Only 1 VFP server can be InProcess • MultiUse (Out of Process EXE) • Slightly slower • Multiple different servers • Single Use • Use for multiple pooled servers • Same server can be instanced more than once

23. Starter FoxISAPI classProvided on the CD • Send/SendLn() Send text to output • StandardPage() Generates a full HTML page • ContentTypeHeader() Adds HTTP header • StartRequest() Called to set up a request. Decodes input vars and clears the output property. • GetFormVar() Retrieves a form variable passed in with the first parameter. • GetCGIVar() Retrieves a server/browser variable from the INI file. • ReleaseServer() Standard method that releases the OLE server.

24. Method example with FoxISAPI class HREF=“/scripts/foxisapi.dll/TDevCon.TFoxIsapi.TestMethod?” • * TFoxISAPI :: TestMethod • FUNCTION TestMethod • LPARAMETER lcFormVars, lcIniFile, lnReleaseFlag • LOCAL lcOutput, lcUserId, lcName • *** Decode the Form Vars and assign INI file to class property • THIS.StartRequest(lcFormVars,lcIniFile) • *** Must always add a content Type Header to output first • THIS.HTMLContentTypeHeader() • lcUserId=THIS.GetFormVar("UserId") • lcName=THIS.GetFormVar("UserName") • THIS.SendLn("<HTML><BODY>") • THIS.SendLn("<H1>Hello World from Visual FoxPro</H1><HR>") • THIS.SendLn("The current time is: "+time()+"<p>") • THIS.SendLn("<b>Encoded Form/URL variables:</b> "+lcFormVars+"<BR>") • THIS.SendLn("<b>Decoded UserId:</b> "+ lcUserId+"<p>") • THIS.SendLn([To retrieve the Browser use ]+; • [THIS.GetCGIVar("HTTP_USER_AGENT","ALL_HTTP"): ]+; • THIS.GetCGIVar("HTTP_USER_AGENT","ALL_HTTP") ) • THIS.SendLn("<HR></HTML></BODY>") • RETURN THIS.cOutput

25. FoxISAPI Summary Pros: Full support for Visual FoxPro Real Development Environment Excellent performance Cons: Difficult First Time Configuration No Web specific code support Doesn’t run on non-ISAPI servers or Windows ‘95

26. West Wind Web Connection • Extensive Visual FoxPro framework for Web development • Support for multiple sessions • Works with Automation and File based messaging interchangeably • Scalable across multiple machines • Real-time, live debugging • Server Management

27. Web Connection Data Server WebServer wc.dll (ISAPI) Visual FoxPro Data Server (already loaded) Server and Form Data returns HTML Doc HTML Link FoxPro User Code Scripted HTML HTML Document Web Browser Database

28. How your code gets called wwServer Visual FoxPro form class handles request routing on incoming requests. wwServer::Process() Routes request to your PRG file invokes MyPRG creates new Process object and calls Process method To process this URL: wc.dll?MyPRG~MyMethod CGIProcess Class Contains MyMethod() that creates HTML output. Class can contain multiple methods. Returns HTML object

29. How your code gets called DEFINE CLASS MyProcess... Procedure Process loCGI=THIS.oCGI lcParam=loCGI.GetParam(1) *** Any ‘global processing’ here *** Check for Cookies, User Ids etc. *** Route to appropriate method CASE PEMSTATUS(THIS,lcParam,5) =EVAL("THIS."+lcParam+"()") RETURN PROCEDURE CUSTLIST <Your processing goes here> <Create HTML document file> RETURN wwServer Visual FoxPro form that’s an OLEPUBLIC Automation Object or uses a timer to poll for requests on disk. Creates Process Object returns HTML object

30. Web Connection FrameworkSome of the features available • Class framework for easy access to CGI/HTML functionality • Solid error handling scheme • Hit Logging, Mulitple Session Management and Maintainence Routines • HTML scripting from files or memos • Single method output of tables to HTML • Built-in support for many advanced HTML/HTTP features

31. Sample Processing Code *** wwCGIProcess :: CustList FUNCTION CustList loCGI=THIS.oCGI loHTML=THIS.oHTML lcClient=TRIM(UPPER(loCGI.GetFormVar(“Client“)) SELECT tt_cust.company FROM TT_CUST ; WHERE UPPER(tt_cust.company)=lcClient INTO CURSOR TQUERY ORDER BY company,Datein IF _TALLY<1 THIS.ErrorMsg("No Matching Records found...") RETURN ENDIF *** HTML output creation follows loHTML.HTMLHeader("NWDS Customer List",; "Customer List Sample",”#FFFFFF”) loHTML.EnclosedText("H3","Time review for: "+lcClient) loHTML.SendLn(“<p>”) *** Now show the table loHTML.ShowCursor() loHTML.HTMLFooter() RETURN

32. Tools summary • Check out Active Server for sophisticated server scripting and connectivity to VFP via Auto servers • For more control use Visual FoxPro as a Web data server • FoxISAPI provides powerful OLE connectivity with an easy interface • For a complete Fox based Web environment check out Web Connection

33. ™