|
Remote Data Editing |
In this Session we will work on editing data with a web application. We begin with a discussion of the issues related to editing data with the WebClass library.
Remote data editing generally requires two HTML forms. The first form gives the user a way to find or lookup the record to edit. When this request to edit data is submitted to the server, the CGI program finds the requested record and returns the data in a second HTML page.
The second HTML page is often the same as the remote data entry page, but in this case it is populated with data. The page also has a HIDDEN field which contains a record identification code. This is necessary because after the user modifies and submits the data, the CGI program must find the record in the table before it can update the fields.
In addition to editing data with a CGI application, we will work with a custom component that is designed to read HTML pages into a dBASE Plus CGI program. The method is named displayHTML() and you will find it in the WebClassEx library.
In Session Four we saw how you can provide your user with a means to enter data into a database. Now we will discuss how to let them edit that data. The following example is an application that enables a user to edit the data entered in the Session Four example. There are two steps to the process of editing data. First you must find the record that the user wishes to modify and put that data into a web form. The web form must include a hidden field that uniquely identifies the record being edited. This is because when the modified data is returned to the web server, your application terminates. Therefore you need a means to find the record being modified when the data is returned your application.
Try // Error trap entire CGI applet.
////// Create new instance of the Web Class
Set proc to WebClass.cc additive
oCGI = new CGISession ()
////// Connect to the Web Server (StdIn/StdOut)
oCGI.Connect()
////// Open Query
cTable = "USERS.DBF"
q = new query()
q.sql = [Select * from "] + cTable + ["]
q.active = true
q.rowset.indexName = "userID"
if oCGI.isKey('userID ')
cUserID = oCGI['userID ']
endif
do case
case oCGI.isKey("finduser")
/// locate the record to be modified
if q.rowset.findKey( cUserID )
if oCGI["password"] == q.rowset.fields["password"].value
oCGI.query = q
oCGI.StreamHeader()
oCGI.StreamUserInfo()
else
cMsg = "<h2>The password you entered is not valid. Please reenter.</h2>"
oCGI.StreamHeader()
oCGI.StreamBody( cMsg )
oCGI.StreamFooter()
endif
else
cMsg = "<h2>" + cUserID + " is not on file. Please open a new account.</h2>"
oCGI.StreamHeader()
oCGI.StreamBody( cMsg )
oCGI.StreamFooter()
endif
case oCGI.isKey("modifyuser")
/// Update the user information in the table
q.rowset.indexname := "tableid"
nPlace = val(oCGI["place"])
q.rowset.findKey( nPlace )
oCGI.loadFieldsFromArray(q.rowset.fields, false)
/// Print a response page
cMsg = "<h2>Your information has been modified.</h2>"
oCGI.StreamHeader()
oCGI.StreamBody( cMsg )
oCGI.StreamFooter()
otherwise
cMsg = "<h2>Click here to start this application.</h2>"
oCGI.StreamHeader()
oCGI.StreamBody( cMsg )
oCGI.StreamFooter()
endcase
catch (exception e)
oCGI.errorPage(e)
endtry
/// cleanup our mess
oCGI = null
////// all done
Quit
////// End //////
There are two main procedures in this application. First the application must look up the record that is going to be edited. In this example, the web user is requesting to make a modification to account information. On the HTML page this user must enter a user ID and a password. When the form is submitted, the information entered in the two text fields and the name/value pair of the submit button, are passes to the application. The name of this submit button is finduser. That name (and it's value) are added to the CGI array when oCGI.Connect() is called. Thus the first case statement in the above code will return true and the subsequent code will execute. In this code the user's Id is looked up, and, if it is found, the user's password is validated. After the user is validated, oCGI.StreamUserInfo() is called. The rowset's record pointer has already been moved to the correct record. So this function will produce a web page that contains the user's record.
As we are here walking through the sequence of step the web user will follow, it is important to note that when the user gets a web page full of data, the web application has terminated, and the data tables are closed. This means that the web user does not lock the record. It also means that we can not simply save() the rowset after the data is modified. Consequently, you need a means to find the modified record when the user returns the data to the application. For this reason, I include a hidden field in the response page. This field is not visible and cannot be changed. I use the value of an AutoIncremented field as my record marker. So when the browser re-submits the web form, my web application seeks this value in the data table, and thereby returns to the correct record for updating. The important point here is that there must be at least one field in the table that users cannot modify. Otherwise there will be no way to know which record needs to be updated.
Your web user can now edit the information that is presented in the web page and resubmit the data back to your application. The web server, then, executes the CGI program a second time. But now, the HTML NAME of the submit button is modifyuser so the second case statement in the above code is true. The rowset's index order is changed to the table's key field (i.e., the autoincremented field) and the record being updated is sought in the rowset. After moving the record pointer to the desired record, the example calls loadFieldsFromArray(q.rowset.fields, false).
This is the same function that was called when a new record was added (see Session Four). Note, however, that the second parameter passed to the function is set to false. The second parameter is named bAppend, and controls whether the data is appended to the rowset ( bAppend=true) or the data is updated ( bAppend=false). When bAppend is true, WebClass.cc calls rowset.beginAppend() and the data is added to the bottom the data table. When bAppend is false, however, WebClass.cc updates the current record. Consequently in order to update the correct record it is important that the rowset's record point be moved to the record before loadFieldsFromArray() is called.
Using DisplayHTML
The next example is an alternative method for creating an edit data web form. This Example uses a method called DisplayHTML().
Set talk OFF // turn off interactive mode
Set century ON // Y2K
Try // Error trap entire CGI applet.
Set proc to WebClassEx.cc additive
oCGI = new CGISessionEx()
////// Connect to the Web Server (StdIn/StdOut)
oCGI.Connect()
////// Open Query
cTable = "Users.DBF"
q = new query()
q.sql = [Select * from "] + cTable + ["]
q.active = true
q.rowset.indexName = "userID"
if oCGI.isKey('userID ')
cUserID = oCGI['userID ']
endif
/// locate the record to be modified
if q.rowset.findKey( cUserID )
if oCGI["password"] == q.rowset.fields["password"].value
cHTMLFile = oCGI['pagename']
////////////////////////////////////////////////////////////
// At this point the record pointer of the rowset should
// be at the record you want to edit. You can now call
// DisplayHTML()
////////////////////////////////////////////////////////////
oCGI.DisplayHTML(q.rowset.fields,cHTMLFile)
else
cMsg = "<h2>The password you entered is not valid. Please reenter.</h2>"
oCGI.StreamHeader()
oCGI.StreamBody( cMsg )
oCGI.StreamFooter()
endif
else
cMsg = "<h2>" + cUserID + " is not on file. Please open a new account.</h2>"
oCGI.StreamHeader()
oCGI.StreamBody( cMsg )
oCGI.StreamFooter()
endif
catch (exception e)
oCGI.errorPage(e)
endtry
oCGI = null
Quit
DisplayHTML() is designed to display a Web page that contains the EntryField your user wants to edit. It replaces streamUserInfo() in the first example. You may find that using DisplayHTML() simplify the process of creating and editing HTML pages particularly if you use a GUI HTML editor. With this method, the HTML codes are not embedded in your program file as is the case with the PrintUserInfo() function. Rather, the HTML page remains a separate file and is accessible to your editor.
To use DisplayHTML(), start by creating the HTML page that will contain the data your user needs to edit. This page is often identical to the HTML page you use for Remote Data Entry. You must make one modification to this file — you must use a special field code as the default value for each text field. Normally the INPUT tag used in an HTML form looks similar to this:
<INPUT TYPE="TEXT" NAME="FIRSTNAME">
To use DisplayHTML() , add the VALUE property and enter a field code as the default value. The INPUT tag should now look like this:
<INPUT TYPE="TEXT" NAME="FIRSTNAME" VALUE="<#FIRSTNAME>">
When you call DisplayHTML(), it opens your HTML document and reads the file line-by-line. If the line includes a field code, that code is replaced with a value from your rowset. The INPUT tag in your response page will then look something like this:
<INPUT TYPE="TEXT" NAME="FIRSTNAME" VALUE="Michael">
DisplayHTML() was designed to work very much like LoadFieldsFromArray(). That is, to display values from a rowset, you must create your query and move the record pointer to the record that you want edited. This should be done before DisplayHTML() is called. Moreover, replacement of field codes is based on name matching. So be sure that <#FieldName> is identical to the rowset's field name. If the field code is not matched with a rowset field, DisplayHTML() will look for a matching value in the oCGI array. This way you can pass additional values like session IDs or page names or names for button tags. Finally notice that DisplayHTML() requires the filename of the HTML disk file.
This Exercise is an extension of Exercise 4.1. In Session Four, you created a simple data entry application. For the current exercise you will create an application that edits the data entered by the program created in Exercise 4.1.
In Exercise 4.1 we used three fields from the Customer table. Assume that we entered Michael Nuwer of Potsdam with this application, and, further assume that I have now moved to Hanawa Falls. We need a way to edit my record. This is the goal of Exercise 6.1.
The application will need to do two things. First we must look up the record that needs to be edited and return a response page that is populated with the data. Second, after the data is edited and submitted back to the server, our application must find the correct record and update the row.
The Customer lookup page
There are various ways that we can design a page for Customer lookups. We can create a report that lists customer names and that contain a HTML hyperlink. We make a report like this in Exercise 2.3. Another method is to use an entry field whereby the user can enter the data to be found. This might be a customer number, a subscriber ID, or a member's email address.
For the current Exercise I have created a dBASE report that you can use to select a customer. You will find Exercise0601rep.rep in the "Source" folder. Use the Live Report Web wizard to create an executable program.
The report will call an application named Exercise0601a.exe. This program must open a query of the customer table, locate the CustomerID requested by the user, and print an HTML response page that contains the data to be edited.
The Customer lookup program
You can us the web program template to get started with this code. After the line that makes the web server connection, create the database and query objects for Customer.dbf. We must also set an index order so that we can find the requested row. Use "CustomerID" as the indexName.
The report will pass a value for the "CustomerID," so our program must check for this element in the oCGI array and then find the row.
if oCgi.isKey('customerID')
nCustID = val(oCGI['customerID'])
q.rowset.findkey(nCustID)
oCGI.streamHeader('Exercise 6.1')
oCGI.StreamBody()
oCGI.streamFooter()
else
oCGI.sorryPage("Customer Not Found")
endif
Assuming the row is found, the row pointer is at the record we want to edit. Therefore we can stream an HTML page and the data back to the browser. However, we still need the response page.
We can use the HTML form that was used to enter data in Exercise 4.1. Use HTMLtoPRG.wfm to process Exercise0401.htm. Add the output as the streamBody method of child class.
Class MyCGISession of CGISessionEx from "WebClassEx.cc"
function streamBody( oQuery )
local f
f = oQuery
with (this.fOut)
puts('<BODY BGCOLOR="#FFFFFF">')
puts('<FORM METHOD=POST ACTION="/app/Exercise0601b.exe">')
puts('<P>Remote Data Editing Exercise')
// ETC.
endwith
return true
endClass
Remember to change the ACTION property of this response page so that it points to Execise0601b.exe.
In order for this response page to contain data we need to embed it in the form. You will need code similar to the following for each of the three field that are on the form -- Last Name, First Name, and City. Note that I have used an object reference shortcut -- "f" is equal to the query object which you must pass when the streamBody() method is called.
cValue = this.GetFieldValue( f, "firstname", false )
cValue = [ VALUE="] + cValue + ["]
puts('<P>First Name<INPUT TYPE="text" NAME="FirstName" ' + cValue+ '>')
One final addition to the response page is essential. We must add a HIDDEN field that contains the unique record identifier. For this table that field is "CustomerID", so the following lines must be added somewhere inside the HTML FORM container.
cValue = ltrim(str(f['Customer ID'].value))
puts('<INPUT TYPE="hidden" NAME="customerID" VALUE=' + cValue + '>')
That should be it. Save this program file as Exercise0601a.prg. Compile and build executable.
A complete example of the program file is available in "\Source\Exercise0601a.prg".
Task 2 Exercise0601b.prg must open a query for the Customer table, locate the record that was modified (this is known from the hidden field), and then call loadFieldsFromArray(). If the second parameter of loadFieldsFromArray is false, then the rowset will be placed in beginEdit() mode and the current row will be updated with the information from the oCGI array. Send a simple response page, and we're done.
db = new DATABASE()
db.databaseName = "WebTutorial"
db.active = true
q = new QUERY()
q.database = db
q.sql = "select * from customer.dbf"
q.active = true
q.rowset.indexName = "CustomerID"
if oCgi.isKey('customerID')
nCustID = val(oCGI['customerID'])
q.rowset.findkey(nCustID)
/// update the data to the table.
oCGI.loadFieldsFromArray(q.rowset.fields, false)
oCGI.streamHeader('Exercise 6.1')
oCGI.StreamBody("Your records have been updated!")
oCGI.streamFooter()
else
oCGI.sorryPage("Customer Not Found")
endif
It is not necessary to subclass this program, so the oCGI object is created from the CGISession class in WebClass.cc. However, if you want the response page to be more complex, subclassing is the way to go.
You may have noticed that the application built in Execsise 6.1 is designed a bit differently than the example discussed at the beginning of this Session. In that example, we put both tasks (the lookup and the update) in one program file and use a case statement to control the execution. In this Exercise we used two different program files. But the concept is the same in both cases.
For many purposes the displayHTML method is the easiest way to process a response page. However, this custom class is limited to data that is taken directly from a DBF or the oCGI array. Moreover, I have not found a good way to modify the method so that it can handle conditional values (for example a check box or select list) or loops (as might be used in a report that walks down a table.) At this time displayHTML() uses a single row of a rowset. A developer can of course, modify the code to handle conditionals and loops, but then it is no longer generic.
The next Exercise will produce a response page with displayHTML(). With this method you will create an HTML page. The CGI program will read this file line-by-line and stream the page back to the web browser.
If you don't have your own html page, here is one that you can use. (Remember to change the eMail address in the action property.)
<HTML>
<HEAD>
<TITLE> Bugs Bee Wee </TITLE>
</HEAD>
<BODY>
<H1>The Good Bug List </H1>
<#Greeting>
<p>The editors of <SPAN style="font-family: sans-serif; color: #0000FF">
Bugs Bee Wee </SPAN> journal are looking for the most
popular butterfly, (or moth), according to our readers.
Please take a moment to select your favorite from the
list shown below.
<FORM
action="/app/Exercise0402.exe"
method="POST"
>
Which of the following <SPAN STYLE="font-style: italic; font-weight:
bold;">Lepidoptera</SPAN> is your favorite?<BR>
Choose only one.
<BR>
<INPUT TYPE="radio" name="fav" value="mb">Monarch<BR>
<INPUT TYPE="radio" name="fav" value="lm">Luna Moth<BR>
<INPUT TYPE="radio" name="fav" value="st">Black Swallow Tail<BR>
<INPUT TYPE="radio" name="fav" value="cs">Common Sulphur<BR>
<INPUT TYPE="radio" name="fav" value="mk">Milkweed Butterfly<BR>
<INPUT TYPE="radio" name="fav" value="tm">Tiger Moth<BR>
<INPUT
type="SUBMIT"
value="Send Selection"><BR>
</FORM>
</BODY>
</HTML>
Open a new CGI template file. We need to make two modifications to this program code. First, displyHTML is contained in the WebclassEx library, so we need to modify the way the oCGI object is created. The following lines are used in place of lines 5 and 6 in the template code.
////// Create new instance of the Web Class
Set proc to WebClassEx.cc additive
oCGI = new CGISessionEx()
Second, we need to call the method that reads the HTML file. Remember, with displayHTML the HTML file is a stand-alone file. It is not embedded in our program file. The lines below will determine what greeting should be inserted into the response page and then stream the file to the client.
oCGI.Connect()
dNow = new date()
if dNow.getHours() < 12
oCGI['Greeting'] ="Good morning!"
elseif dNow.getHours() < 18
oCGI['Greeting'] ="Good afternoon"
else
oCGI['Greeting'] ="Good evening"
endif
cHTMLFile = 'exercise0602.htm'
oCGI.DisplayHTML(NULL,cHTMLFile)
Build this program and deploy it to the "app" folder.
compile exercise0602.prg
build exercise0602.pro to ..\app\exercise0602.exe WEB