the Basics
Requirements
For this tutorial make sure that you have XLiRAD installed as well as a web server, servlet manager and approved database. A list of the acceptable combinations appear below:
|
Requirement |
Description |
|
Web service |
Apache HTTP Server version 1.2.x or later Microsoft Internet Information Server version 4.0 or later Netscape Enterprise Server version 3.6.3 or later |
|
Servlet management software |
Allaire JRun version 2.3 Jserv |
|
Database application compliant with Structured Query Language (SQL) |
IBM Universal Database (UDB) version 6.0 or later Microsoft Access 97 or later Microsoft SQL Server version 7.0 or later MySQL version 3.22 or later Oracle version 7.0 or later Sybase Adaptive Server Enterprise (ASE) version 11.0 or later |
Setup:
First, create a databases and call it tutorial. Configure DBServer to have at least one connection to this DB. Within this database create a table called customers using a statement similar to the one below:
CREATE TABLE customers
(customer_number smallint(5) NOT NULL auto_increment,
name varchar(50),
phone varchar(12),
address1 varchar(25),
address2 varchar(25),
city varchar(25),
state char(2),
zip varchar(10),
country varchar(25),
email varchar(20),
PRIMARY KEY (customer_number));
The above shows the create statement as it would appear using MySQL which support auto increment fields. For SQL Server 7.0 you would just make customer_number an identity field setting the increment value to 1. For Oracle and DB2 we will just leave customer_number as a PRIMARY KEY and take care of the incrementing ourselves by invoking a SELECT MAX(customer_number) statement prior to inserting a new record.
Add Customer
The first set of templates follow a basic "collect and execute" flow. Open up the Template Maker and click on the new template icon. When the new template opens up, maximize the window and place the cursor below the <BODY> tag.
Click on the CMD tag icon and a dialog box opens up. Along the left side you'll see a bunch of tabs placed vertically. Select the tab that says Insert. In the text box that says Command ID type the number 201. In the text box that says CommandName type something like get table column names. Enter "customer_number" in the text box that says Ignored Columns. Leave the rest of the form alone and click the submit button on the lower right. Your template should look like this:
<HTML>
<HEAD>
<TITLE>Untitled</TITLE>
</HEAD>
<BODY>
<CMD>{get the column names}COMMAND=201&FORMAT=INSERT&IGNORE=customer_number&FAILURE=NONE</CMD>
</BODY>
</HTML>
Save this template on the server as AddCustomer. Save it in Public Domain but don't exit Template Maker yet.
The .groupname suffix
Look at the title bar of your template. You will notice a word has been appended to it. That word is the name of the group you belong to. All members of that group will be able to read and edit the template. Members of another group may not read or edit the template. This is simple enough to understand but please bear in mind the following point. All references and links to a template must include the groupname suffix otherwise the reference or link won't work. Here at HHPN the developers belong to a group designated as software. So if I were to create a template for this tutorial named AddCustomer, my template would be referenced as AddCustomer.software. If you belonged to a group called devteam your template would be called AddCustomer.devteam. For the sake of this tutorial please keep in mind that you MUST append the group name to all of the templates you create when referencing them or linking to them.
SQL Statement
The reference to COMMAND=201 above would not do anything yet because we haven't created the SQL statement for 201. Leave Template Maker running and open SQL Editor now.
Click on the New SQL icon. Make sure the database drop down list reads tutorial. Enter the number 201 into the text box that says Command ID. Now, in the large text area that says SQL Statement, enter the line:
select * from customers
Click submit in the lower right corner and you're done.
The result of all this is that when the template AddCustomer is run by DBServer, the command tag specifies that command id 201 be executed. What you see as a result of that execution depends on the FORMAT clause, in this case FORMAT=INSERT. The FORMAT=INSERT does nothing more than return the column names of a table with text input fields. And command id 201 specifies that the table in question is customers.
If you were to run this template now, however, you would see nothing but garbage. The reason is that FORMAT=INSERT requires that you embed the <CMD> tags between <TABLE> tags. This is so that you have more control over the appearance of the page.
Go back to Template Maker and add <TABLE> tags so that your code looks like this:
.........
<TABLE
BORDER>
<CMD>{get
the column names}COMMAND=201&FORMAT=INSERT&IGNORE=customer_number&FAILURE=NONE</CMD>
</TABLE>
.........
One more thing before you're done. You must place the above code within a block of <FORM> tags for FORMAT=INSERT to be of any practical use. So follow along with these simple lines:
<FORM
ACTION="ServerAlias" METHOD="Post">
<INPUT TYPE="Hidden" NAME="TEMPLATE"
VALUE="AddCustomerExecute.groupname">
<TABLE BORDER>
<CMD>{get
the column names}COMMAND=201&FORMAT=INSERT&IGNORE=customer_number&FAILURE=NONE</CMD>
</TABLE>
<INPUT TYPE="Submit"
VALUE="Add This Customer">
</FORM>
The ACTION in the first line refers to the server alias name that you chose when you set up DBServer. It could be whatever you want -- it is just called ServerAlias here only for our example.
The second line tells DBServer where to send the information of this form when the user presses the submit button. In this case it would go to a template named AddCustomerExecute which doesn't exist yet, we'll make it next.
Next to the last line is the submit button.
Go ahead and save now.
Check it out
Now open your browser and go to the site hosted by your web server by entering the following line:
http://www.yourwebsite.com/servlet/ServerAlias?TEMPLATE=AddCustomer.groupname
This is the standard way to reference templates. Your web site address followed by the word "servlet" the server alias and a question mark, the word TEMPLATE, an equals sign, and then the template name. The .group will be the group name that you configured in DBServer.
The page should look something like this:
| Name | |
| Phone | |
| Address1 | |
| Address2 | |
| City | |
| State | |
| Zip | |
| Country | |
Now if you typed in anything and pressed the submit button you would get a Template Not Found error. We have to now create the execute page for this form.
Add Customer Execute
With Template Maker still open, click on the New Template icon. Place the cursor below the <BODY> tag and then click on the CMD icon. Click on the tab that says None. Format None is used when you don't need to display a result and is used when inserting or updating a record. In the Command ID text box type the number 202. In the CommandName text box type something like insert customer record. In the Error Template text box type AddCustomer. If any error is encountered during the insert operation, DBServer will direct you to whatever template you specify here. In this case we want the previous template named AddCustomer. The relevant code should look like this:
<CMD>{insert customer record}COMMAND=202&FORMAT=NONE&FAILURE=NONE&ERRORTEMPLATE=AddCustomer.groupname</CMD>
Now go back to SQL Editor and click on the New SQL icon. This time we'll use the SQL Wizard so click on that button. Click Next. Now make sure that the active database is the tutorial DB. Click Next. Under the Query Type choose Insert. Cick Next. From the available tables list choose customers. Click Next. Under the Columns list choose all EXCEPT customer.customer_number. This is if you are using MySQL or SQL Server 7.0 which takes care of auto incrementing for you. If you are using DB2 or Oracle, go ahead and select customer.customer_number also. Click Next. Now examine each column name and be sure that the data types match the database schema.
| name |
STRING
|
| phone |
STRING
|
| address1 |
STRING
|
| address2 |
STRING ALLOWS NULL
|
| city |
STRING
|
| state |
STRING
|
| zip |
STRING
|
| country |
STRING
|
|
STRING
|
Click Next. Now you will see the entire statement written out in the text area box. Type the number 202 into the text box that says Command ID and click submit. The SQL statement generated will look something like this:
INSERT INTO customers(address2, zip, country, address1, city, state, name, phone, email) VALUES ('{address2:0}', '{zip:7}', '{country:7}', '{address1:7}', '{city:7}', '{state:7}', '{name:7}', '{phone:7}', '{email:7}')
Now when you run the template AddCustomers, fill out the form and hit submit, the template AddCustomerExecute.groupname will insert the data into the customers table.
Oracle and DB2 only
If you are running Oracle or DB2 you will have to add one
more SQL statement. In SQL Editor click the
New SQL icon. Type in the number 203 in the Command ID text box. Enter
the following line in the large text area:
select max(customer_number + 1) as customer_number from customers
Submit this. Now update the command 202 and make sure the statement includes the customer_number column. In the VALUES clause make sure it includes the code {customer_number:8}. The numeral 8 means that the data type is an integer and will NOT allow nulls.
Open up AddCustomerExecute again in Template Maker. Place the cursor above/before the line with command 202 and then click the CMD icon. Select Pairs from the vertical tabs. Enter 203 for the Command ID. Enter something like get next customer number in the CommandName text box. In the Error Template text box you can type AddCustomer. Hit submit. Here's what the execute page looks like now.
<HTML>
<HEAD>
<TITLE>Add
Customer Execute</TITLE>
</HEAD>
<BODY>
<!-- for Oracle or DB2 only -->
<CMD>{get next customer number}COMMAND=203&FORMAT=PAIRS&NULLMASK=FALSE&FAILURE=NONE</CMD>
<!-- insert the customer record -->
<CMD>{insert customer record}COMMAND=202&FORMAT=NONE&FAILURE=NONE&ERRORTEMPLATE=AddCustomer.groupname</CMD>
</BODY>
</HTML>
Format Pairs simply gets information and makes it available to other commands on the page. In this case we got the maximum value from customer_number, incremented it by one and made it available on the page for the next command to insert.
So now we have to modify command 202 in order to accept the value of customer_number in the insert statement. In SQL Editor, highlight command 202 and press the UPDATE BUTTON. (Its the one that looks like a Q withand upward pointing green arrow.) Change the statement to include customer_number like this:
INSERT INTO customers(customer_number, address2, zip, country, address1, city, state, name, phone, email) VALUES ({customer_number:8}, '{address2:0}', '{zip:7}', '{country:7}', '{address1:7}', '{city:7}', '{state:7}', '{name:7}', '{phone:7}', '{email:7}')
Error Handling
Open up the AddCustomer template in Template Maker. We have to add some lines that will display errors when they occur. Place the cursor right under the first BODY tag and press the !E button. Choose Previous template and then press Submit. Error tags can be placed anywhere on a page.
What this example does is provide a place to display error messages that may be encountered on the AddCustomerExecute page. The AddCustomer page will now look something like this:
<HTML>
<HEAD>
<TITLE>Add Customer</TITLE>
</HEAD>
<BODY>
<CENTER>
<H3><FONT>
<ERR><ERROR=Previous Template></ERR>
</FONT></H3>
<FORM ACTION="ServerAlias"
METHOD="Post">
<INPUT type="Hidden"
name="TEMPLATE" value="AddCustomerExecute.groupname">
<TABLE border>
<CMD>{get the column names}COMMAND=201&FORMAT=INSERT&IGNORE=customer_number&FAILURE=NONE</CMD>
</TABLE>
<INPUT type="Submit"
value="Add This Customer">
</FORM>
</CENTER>
</BODY>
</HTML>
We have sandwiched the error tags between some
code that will make the display bold and red. That's all just a matter of
personal style.
Summary
In this introduction we have covered the most basic of template flow design, that of data collection and execute. More complex flow designs will be covered in subsequent tutorials.
tutorials to gsmith@hhpn.com
