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