Having created some records we now need to maintain them. We need to be able to display a table of customers, selected and sorted according to preference. First let's create a screen that simply lists all records.
In the main menu, clicking on "List customer records" results in a call to the servlet with a ScreenID of "CList". So we need to add another line to the dispatcher in Library.ls:
case "CList" send "list" to Customer
and another line to the message handler in Customer.ls:
else if the message is "list" gosub to ListCustomers
Next we need a set of new variables in Customer.ls and some initialization code for them. Here's the additional script:
buffer Pattern element Table element Row element Cell element Text element EditIcon element DeleteIcon set the type of Table to table set the type of Row to row set the type of Cell to cell set the type of EditIcon to image set the type of DeleteIcon to image set the type of Text to text set the width of EditIcon to 20 set the height of EditIcon to 20 set the width of DeleteIcon to 20 set the height of DeleteIcon to 20 set the name of EditIcon to "Edit" set the name of DeleteIcon to "Delete" set the icon of EditIcon to "public/system/edit.gif" set the icon of DeleteIcon to "public/system/cut.gif" set the border of EditIcon to 0 set the border of DeleteIcon to 0
Pattern will be used to hold an SQL query string; the rest are all HTML elements. Linguist takes an object-oriented view of an HTML page, especially where tables are concerned. Every item on an HTML page can be regarded as posessing attributes. For text, these include color, size, style and alignment. A table has other attributes, but it is also a container, comprising one or more rows of items. Each row is a container for cells, and each cell is a container for practically any other HTML object, including another table.
By treating each element as a distinct item we can ignore the effect it has on other elements and avoid the problem with HTML where a single mistake screws up the whole of the rest of the page. In this scripting language it is rarely necessary to use explicit HTML strings; instead, everything is made from elements.
Before an element can be used we must define its type. There are a fair number of different types, which are covered by the Linguist reference documentation (start at index.html in the linguist folder). Here we use five different types. After that it's useful to do some initialization, assigning to each element any value that will be constant thoughout its life. This makes for less dense code elsewhere. We think the attributes above are self-explanatory; about the only thing I feel compelled to explain is why the two icons are put in the public folder hierarchy and not in servlets. The answer is that when the browser receives the page it will request the icons; if they are not in public they are inaccessible outside the server.
Having declared and initialized our variables it's time for the ListCustomers subroutine:
ListCustomers: prompt "List Customers" set the title to "Customer List" set the alignment of Text to centre put "Return to main menu" into Text set the target of Text to "library" add Text add break add break clear Table set the border of Table to 1 clear Row clear Cell set the alignment of Cell to centre set the style of Cell to bold set the text of Cell to "Name" add Cell to Row set the text of Cell to "Address" add Cell to Row set the width of Cell to 24 put empty into Cell add Cell to Row add Cell to Row add Row to Table !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! ! Now do the data area of the table set the style of Cell to plain gosub to GetPattern get Customer using Pattern while more Customer begin get next Customer put field "id" of Customer into ID clear Row set the width of Cell to 0 set the alignment of Cell to left set the text of Cell to field "name" of Customer add Cell to Row set the text of Cell to field "street" of Customer add Cell to Row set the width of Cell to 24 set the alignment of Cell to center set the target of EditIcon to "library?ScreenID=CEdit" cat "&id=" cat ID put EditIcon into Cell add Cell to Row set the target of DeleteIcon to "library?ScreenID=CDelete" cat "&id=" cat ID put DeleteIcon into Cell add Cell to Row add Row to Table end add Table add break add Text return
This is a fairly long subroutine, but once again there's really not much to it. It starts by putting a hot link back to the main menu at the top of the screen. Next it clears the table, row and cell elements (in case they have previously been used) then constructs the headers for the table, one by one. As each element (e.g. a cell) is added to another element (e.g. a row) a copy is made of it, so you can then reuse the inner (cell) element without affecting anything else. This saves the need to have separate element variables for every single item. Once all the cells have been added, including two blank ones, the row is added to the table. I've only displayed the name and street address fields; you can easily show more by following the same style.
Now we get to the customer data. The script calls a subroutine to get a value in Pattern (I'll come to that in a short while) and does a search on the database using it as the SQL query string. It then iterates through the returned records, copying some of the fields into the cells of the table. The two icons for Edit and Delete are finally placed in the table, having first had a URL attached to each that includes the appropriate ScreenID.
Once all the records have been processed, another hot link is placed at the bottom to return to the main menu.
This subroutine has done an immense amount of work in a very concise manner. I haven't tried to explain everything in detail as it should all be fairly obvious simply by reading the script. Most people would find it hard to say the same for ASP!
The GetPattern subroutine looks like this:
GetPattern: put session value "cpattern" into Pattern if Pattern is empty put "ORDER BY name" into Pattern return
This uses a set of values associated with the session (which roughly corresponds to the current user). If the session value "cpattern" is present its value is loaded into the buffer Pattern, otherwise a new value is used that sorts the displayed entries by name.
This version of the Customer script is called Customer-4.ls.
6.2 Defining an SQL search pattern
Once the number of customer records grows to more than will comfortably fit on a single screen we will need to provide a means to look at just part of the database. We can do this by setting up a SQL search pattern. In the previous section the search pattern is set by default to ORDER BY name, which is SQL for "show the records alphabetically ordered by name". (Pretty obvious, huh?) For anyone who is completely unfamiliar with SQL a brief look at an SQL primer would be handy, but don't get too enthusiastic; there's far more to SQL than we'll ever need in a servlet.
In GetPattern we put something called session value "pattern" into the buffer Pattern. A session value is something stored in the context of the current session. How the server maintains this across HTTP requests is a matter of some wizardry involving URL rewriting and cookies, but the means is unimportant. What matters is we can save things as session values and get them back again.
To allow session values to be used we need a single command to be executed when the request is first received by Library.ls:
get session
This 'primes' the system and makes any existing session values available. Now our GetPattern subroutine will retrieve a value for "pattern" if one has been put there. So we'll add three lines to ListCustomers:
put parameter "pattern" into Pattern if Pattern is empty gosub to GetPattern set session value "cpattern" to Pattern
These take the parameter received as part of the request and assign it to the local variable. If it's empty (no parameter was passed or it had an empty value) we go to GetPattern to pick up the default string. Either way we then write the pattern back to the session. Next time it will be there, ready for use.
To give the user a chance to enter a pattern, we'll arrange for the main menu to call a new screen when the user clicks on List Customer Records. This screen is called pattern.html and it comprises a single text field with a submit button. The text field will be preloaded with the current SQL search pattern. When the submit button is clicked we go to our customer listing screen, taking with us the search pattern. The ScreenID for this is CListBy, so we need a new dispatcher entry:
case "CListBy" send "listby" to Customer
and a corresponding change to the message handler:
else if the message is "list" gosub to SetPattern else if the message is "listby" gosub to ListCustomers
which retargets the first message and adds another for the original function. We have a new subroutine SetPattern that looks like this:
SetPattern: prompt "Set pattern" gosub to GetPattern add template the directory cat "servlets/templates/pattern.html" where "*PATTERN*" is Pattern and "*SCREENID*" is "CListBy" return
Nothing very difficult here; take a look at the HTML to see the full story.
Now you can test the effect of different SQL query strings such as
ORDER BY id
or
WHERE city LIKE 'New%' ORDER BY name
For those unfamiliar with SQL, the second one looks for any city starting with the word "New" and lists all customers in alphabetical name order.
The scripts for this stage of development are Library-5.ls and Customer-5.ls.
The screens for editing and deleting records are next. Both are called up by clicking on one of the icons against a customer record in the listing screen. The editing script is very similar to New Customer; in fact, we can use the same HTML template, giving it a different value for ScreenID. When the user has made any wanted changes, the form sends us to another new routine that updates the record in the database. All the code is very similar to what you've already seen before.
EditCustomer is the subroutine that generates the editing screen. It receives the customer ID from the edit button (see the script for ListCustomers), then looks up that customer and fills in the form from the record fields.
EditCustomer: prompt "Edit Customer" set the title to "Edit Customer" put parameter "id" into ID get Customer id ID add template Website cat "servlets/templates/cnew.html" where "*MESSAGE*" is "" and "*ID*" is ID and "*NAME*" is field "name" of Customer and "*NAME*" is field "name" of Customer and "*STREET*" is field "street" of Customer and "*CITY*" is field "city" of Customer and "*TELEPHONE*" is field "telephone" of Customer and "*SCREENID*" is "CUpdate" and "*FUNCTION*" is "Update" return
You might prefer to use a separate template rather than sharing the one for NewCustomer, allowing more freedom to customize the appearance of the screen, but don't forget it means two files to edit if any major changes are to be made.
When the client submits the form it's handled by another new subroutine:
UpdateCustomer: prompt "Update Customer" put parameter "id" into ID put parameter "name" into Name put parameter "street" into Street put parameter "city" into City put parameter "telephone" into Telephone ! Check that all required fields are present if Name is empty or Street is empty or City is empty begin add template Website cat "servlets/templates/cnew.html" where "*MESSAGE*" is "Please fill in all fields marked with a *." and "*ID*" is ID and "*NAME*" is Name and "*STREET*" is Street and "*CITY*" is City and "*TELEPHONE*" is Telephone and "*SCREENID*" is "CUpdate" and "*FUNCTION*" is "Update" end else ! Update the record begin ! Get the customer record new transaction get Customer id ID set field "name" of Customer to Name set field "street" of Customer to Street set field "city" of Customer to City set field "telephone" of Customer to Telephone commit add "Customer " cat Name cat " updated." gosub to ListCustomers end return
After a check that no important fields have been left blank, the script updates the fields of the record. This is simply a matter of creating a new transaction, getting the old record, updating its fields and committing the changes.
The scripts for this stage of development are Library-6.ls and Customer-6.ls. You'll see that I've added a dispatcher to the Customer script, since the number of different messages is getting quite large. It's good practice in any case.
The final maintenance item is to delete a record. Here's the delete form: