Back Previous Next


6 Record maintenance

6.1 Listing records

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.

6.3 Editing records

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.

6.4 Deleting records

The final maintenance item is to delete a record. Here's the delete form:

Customer Database



Are you sure you want to delete customer
*NAME*?


And here's the script that sends the form to the browser:

DeleteCustomer:
	prompt "Delete Customer"
	set the title to "Delete Customer"
	put parameter "id" into ID
	get Customer id ID
	add template Website cat "servlets/templates/cdelete.html"
		where "*ID*" is ID
		and "*NAME*" is field "name" of Customer
	return

The template file used for deletion contains two forms; one to confirm the delete and theother to allow the user to back out without making any changes. Looking inside the form you'll see three hidden fields. In one we place the customer ID so we'll get it back again when the form is submitted. The other two contain the names of the functions to perform for each of the submit buttons. Then we have the usual substitution strings.

When the form is submitted as a request to delete, we call this subroutine:

RemoveCustomer:
	prompt "Remove Customer"
	new transaction
	delete Customer id parameter "id"
	commit
	go to ListCustomers

which illustrates the final aspect of dealing with the SQL database, that of deleting a record.

That completes the customer module, which is in the scripts folder as Customer-7.ls, along with its corresponding Library-7.ls.


Back Previous Next