Introducing the Server

 <  Day Day Up  >  

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.

Listing 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:rstrahl@west-wind.com">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 SELECT command

  • The 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.

Listing 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.

Listing 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 SEND2DBF and SEND2SQL Functions

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.

Listing 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.

Database Design Still Matters

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.

 <  Day Day Up  >  


Visual Fox Pro to Visual Basic.NET
Visual FoxPro to Visual Basic .NET
ISBN: 0672326493
EAN: 2147483647
Year: 2004
Pages: 130
Authors: Les Pinter

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net