Back Previous Next


5 The customer record

5.1 The record structure

Before we can use an SQL database we have to define the structure of its records. The Linguist SQL package hides most of the detail of how this is done behind some very easy-to-use scripting syntax. Here's the code we need to define our customer record:

	specification CSpec
	record Customer
	
!	Set up the specification for a customer record
	set the table name of CSpec to "customer"
	set the fields of CSpec to
		id type long
		and name type varchar size 40
		and street type varchar size 40
		and city type varchar size 40
		and telephone type varchar size 40
	set the specification of Customer to CSpec

Here we have two new variable types. A specification describes the structure of a database record, and a record represents a single record on its way to or from the database.

Most of the script fragment here describes the record structure. Each field is named and its type is specified. There is one special field, id, that must - if used - contain a unique value; this helps to speed up database searches. This field can be of any type available; here it's a long (64-bit) integer and I'll describe later how it's used. The remaining fields are all strings of up to 40 characters, one for each of the fields in our new customer form.

We also define the name of the table that will contain records of this type. Once the definition is complete we can use it for real records, so the last line tells the system that the variable Customer will always be used for this kind of record.

Next we'll declare some variables to match each of the fields of the record. These will be used to hold field values while they are being used:

	variable ID
	buffer Name
	buffer Street
	buffer City
	buffer Telephone

5.2 Creating a table

Before we can use these records we must create a table to put them in. This is a one-off operation that destroys any existing table of that name, so needs to be done carefully. Rather than add a menu item that could be used by accident we'll require the system administrator to type a special URL that requests the table to be constructed. We'll use a ScreenID of "CReset" but also require a second parameter Confirm having the value "Yes I really mean this". So the URL you need to type will be

http://myURL/library?ScreenID=CReset&Confirm="Yes+I+really+mean+this

which is difficult to do by mistake. OK, now for the scripts. First thing is to add the new dispatch code in Library.ls:

		case "CReset" send "reset" to Customer

Then add the message handler in Customer.ls:

		if the message is "reset" gosub to ResetTable

and finally the ResetTable subroutine:

ResetTable:
	prompt "Reset Table"
	if parameter "Confirm" is "Yes I really mean this"
	begin
		create table for Customer
		add "New Customer table created."
	end
	else add "Incorrect confirmation; table not added."
	add template the directory cat "servlets/templates/mainmenu.html"
	return

The subroutine checks that the correct parameter has been provided; if not it outputs a warning message and does nothing. If the confirmation is correct the table is created. In either case the routine then reloads the main menu screen.

The script so far for the customer module is in servlets/scripts/Customer-2.ls. and the main program is servlets/scripts/Library-4.ls. Copy these to servlets/Customer.ls and servlets/Library.ls, recompile and re-initialize the servlet. Now you can go to your browser and type the URL above. Make a mistake somewhere in the confirmation string (or leave it out completely) and you'll see it refuses to play. Get it right and the new table will be created. You'll also see messages from InstantDB appearing on the console screen.

5.3 Adding the new customer

Having created a table it's time to add our first customer. We already have an AddCustomer subroutine but there's nothing in it yet. So take a deep breath and off we go:

AddCustomer:
	prompt "Add Customer"
	set the title to "Add Customer"
	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 "*NAME*" is Name
			and "*STREET*" is Street
			and "*CITY*" is City
			and "*TELEPHONE*" is Telephone
	end
	else
	! Create the new record
	begin
		! See if this is an existing customer
		get Customer using "WHERE name='" cat Name
			cat "' AND street='" cat Street cat "'"
		if more Customer
		begin
			add "Customer " cat Name cat " already exists."
		end
		else
		begin
			put the millisecond into ID
			new transaction
			create Customer where
				field "id" is ID
				field "name" is Name
				field "street" is Street
				field "city" is City
				field "telephone" is Telephone
			if the status is "OK"
			begin
				commit
				add "New customer " cat Name cat " added."
			end
			else
			begin
				set the title to "Internal error"
				add "Can't create record: " cat return
				add the status
			end
		end
		add template the directory cat "servlets/templates/mainmenu.html"
	end
	return

There's quite a lot of script here but it's all rather simple really. The first thing to do is to take the field parameters and put their contents into the buffers we set up earlier. Now there are a number of situations that might pertain at this point:

  1. An empty form may have been submitted, or some fields may be empty.
  2. The name and address may match that of an existing customer.
  3. No errors can be detected.

So there are at least three paths through the logic. We start by examining the fields to see if the name and two address fields have something in them. If one or more is empty we send the same form back again, this time putting a message into the substitution string *MESSAGE*. (So that's what it's for!) And to save the user retyping fields he/she filled in the first time round, the previous values are put back.

Assuming all the required fields have values it's time to add the record to the database. In some cases the logic may be more complex; here we make no attempt to deal with (for example) slight mis-typing, or examine for nonsense strings. The logic here says 'If the fields all have values it's a valid record'.

However, it's possible we already have a customer with the same name. Is this the same customer? If the name is 'John Smith' and this is an Anglo-Saxon country it may well be a different person sharing the same name. So let's look in the database for any customer having the name and street address of this person. The get instruction passes a chunk of SQL query code through to the database. If there are any records that match, the more Customer test will pass, so we merely output a warning message, skip most of the rest of the subroutine and send the main menu screen back to the browser.

If more Customer fails there is no record matching our new customer. So now, at last, is the time to write to the database. To get a unique ID for this record we ask for the current time in milliseconds (it's unlikely you have a server fast enough to process two records in the same millisecond). Next we create a new transaction. Rather than go into lots of tedious detail about database transactions I'll simply say that although you can read a database without using a transaction you can't write to it. Once the transaction has been created, every record you create or modify is held by the transaction. When a commit instruction is given the whole lot is written back to the database in one go. If you forget to commit, no changes will be made. Oh, and if you go away for more than an hour the transaction will remove itself.

The database write is done in two stages. First we create a Customer record using the values we have for its fields. This record is cached locally; the SQL server is not yet involved. The status that results will normally be "OK" so there should be no need to check. However, I've provided you with the code that will detect and report errors, just to be on the safe side.

Once the record exists we can commit it to the SQL server. This too results in a status code but the script doesn't check it. Finally the main menu is reloaded, and will report at the top whether the record had been added to the database.

This version of the script is called Customer-3.ls. Copy it as before and recompile. You should now be able to add records, though you can't yet see where they've gone. InstantDB will report the SQL commands to the console as it deals with them and you can try re-entering an existing customer to check you get an error message.


Back Previous Next