XLiRAD Tutorial part 2

XLiRAD Tutorial

Part 2
Building on the Basics

Requirements

Be sure you have completed part 1 of this tutorial.

In the first tutorial we covered simple template flow which collects user input on one page and then is directed to a second page that processes the data. (Usally an INSERT into the database). As we all know, whatever we add to a database we might also want to change or delete it. Thats what we're going to cover in this tutorial.

Search Criteria Page

Open up Template Maker and click on the New Template icon. What we want to accomplish is to create a form in which to enter search criteria for our customers table. For now we'll just focus on three types of searches -- by name, by city or by email. Type in the following code below (without the line numbers) and this will give you a head start in this project. We'll examine certain code blocks for your further understanding.

HTML Code for CustomerSearch Template

Examination

The first green block in the code above is similar to what we did in the first tutorial. The action clause is the server alias you have assigned when you initially set up DBServer. In this case we are using "/servlet/engine". Line number 09 shows the destination template that will process the form data ( CustomerSearchExecute ).

The next green block is a standard drop down select list. Line number 22 shows the the value of 220 if you select "By Name". This value represents the command number we will use to search by name. It can be any number you want as long as it matches the SQL statement command number you write in SQL Editor. In this case we will use command number 220 to search by name, 221 to search by city, and 222 to search by email.

The last green block just shows what should be a standard template writing technique. If you encounter an error on the execute page you would be directed back to the collection page with all the name-value pairs in their respective text boxes. The <REP2> tags serve as placement holders for whichever name-value pair you want to place. Here whatever value you typed in for "criteria" will be present if your page was directed back.

CustomerSearchExecute

Now we'll make the page that executes the search. Create a new template and put in your prefered formatting code such as the <BODY> tags and <CENTER> etc.

This page will have only one command for now and we want the display in TABLE format. Click on the CMD icon and select the TABLE tab on the left. For now just enter the number 9999 for the Command ID as we are going to edit that part manually. Type in something descriptive for the CommandName. In the Error Template text box type in the name of the previous template, ie. CustomerSearch. Check the Use Auto Header box. Press submit.

Remember that you must sandwich a format table command between html <TABLE> tags otherwise it wont look like much on your display. It was decided against having the Command Wizard add the <TABLE> tags for you because you might want to insert your own custom column headers instead.

We want to edit this last command so as to serve for multiple types of searches. Delete the '9999' part and replace it with this--- <REP>search_type</REP>. The name-value pair search_type is from the drop down select list. The <REP> tags simply replace whatever value you picked from that select list into the argument portion of the command tag. Because of the order of operations, you must use <REP> tags. <REP2> tags simply will not work inside command <CMD> tags. This one line can therefore serve the purpose of three command tags. Your page should look something like this:

<HTML>
<HEAD>
<TITLE>Customer Search Execute</TITLE>
</HEAD>

<BODY>
<CENTER>
<H2>Customer Search Results</H2>

<TABLE>
<CMD>{get search results}COMMAND=<REP>search_type</REP>&FORMAT=TABLE&AUTOHEADER=TRUE
&SHOWROWCOUNT=FALSE&NULLMASK=FALSE&FAILURE=NONE&ERRORTEMPLATE=CustomerSearch.groupname</CMD> </TABLE>

</CENTER>
</BODY>
</HTML>

The SQL Statements.

At this point you may want to go back to the templates you worked on in part one. The main reason being that you need to have test data to work with if you are going to successfully test your templates. So run the AddCustomer templates in your browser and add ten ficticious customers if you haven't done so in the first place.

And now for the three statements we have been refering to, 220, 221 and 222. Start up your SQL Editor and click on the add new query icon. Enter 220 in the Command ID text box. In the SQL Statement text box typ in the following line:

select customer_number, name, city, email from customers where name like '%{criteria:7}%'

Click the submit button. For Command 221 enter the following:

select customer_number, name, city, email from customers where city like '%{criteria:7}%'

Click submit. Command 222 looks like this:

select customer_number, name, city, email from customers where email like '%{criteria:7}%'

Click submit and your ready to test. You may want to add some ERROR tags to the first page before you test. Go back to CustomerSearch and add these lines between the <BODY> tags:

Now you should be ready to test.

The Detail Page

Lets make a few changes on the CustomerSearchExecute page that will allow a user to click on a customer's number and be redirected to a page with the full customer data with address, phone etc. Edit the CMD tag to include the code as seen in bold type:

<CMD>{get search results}COMMAND=<REP2>search_type</REP2>&FORMAT=TABLE&AUTOHEADER=TRUE&
SHOWROWCOUNT=FALSE&NULLMASK=FALSE&FAILURE=NONE&ERRORTEMPLATE=CustomerSearch.groupname& LINKTEMPLATE=CustomerDetail.groupname&LINKCOLUMN=customer_number</CMD>

The LINKTEMPLATE arguement tells of the specific destination we want when we click on a link for this command. The LINKCOLUMN is the name of the column that becomes a hyperlink. In this case the customer_number will appear as a blue underlined link on the page. Type this in and save the template. Test it out. When your mouse is over the link you will see the URL at the bottom border of your browser. If you click on it nothing will happen but a Template Not Found error message.

Create a new template with your favorite formatting preferences. Place the cursor between the <body> tags and click the CMD icon. Click on the EDIT tab on the left. Give the Command ID a number of 223. Give the CommandName something descriptive like get customer info. In the Static Fields text box type in customer_number.

Format Edit returns data in an editable form. When you describe a field as static you are not able to edit that field. This is important for preserving the integrity of primary fields or any data with which you want to display but not change.

Click submit. Now sandwich the CMD code you just inserted between some <TABLE> </TABLE> tags. Format Edit is similar to Format Table in that it allows you to add your own table properties.

Save this template as CustomerDetail. Look at the right hand side of the flow chart at the beginning of this tutorial. According to that chart we have just completed the first three boxes, the Search Criteria, the Search Results and the Detail.

Now create command 223 in SQL Editor by entering this code:

select * from customers where customer_number = {customer_number:8}

Go ahead and test what you have done so far. Enter some search criteria and hit submit. If you have entered some dummy test data you may have a search result page with one or more rows returned. The customer number column should be hot-linkable by clicking on which you go to the Detail page. The Detail page shows all the fields in an edit form.

The Update Page

If you have tested as you go along and are satisfied so far, we can move on to the CustomerUpdate template. First we have to modify some code on the CustomerDetail.groupname page that will accomodate an update page.

First of all we want to include a standard error block at the top of the CustomerDetail.groupname page:

Next we want to sandwich the CMD line and the <TABLE> tags between some <FORM> code like this:

<FORM ACTION="/servlet/engine" METHOD="Post">
<INPUT type="Hidden" name="TEMPLATE" value="CustomerUpdate.groupname">

<TABLE>
<CMD>{get customer info}COMMAND=223&FORMAT=EDIT&STATIC=customer_number&
NULLMASK=FALSE&FAILURE=NONE</CMD>
</TABLE>

<INPUT type="submit" value="Update">
</FORM>

Click save and hit the refresh button icon (the one with the two circling green arrows). The editable form will now appear with an UPDATE button below it which will take you to the CustomerUpdate.groupname page. Lets now create the CustomerUpdate page.

Click on the New Template icon. We're going to erase all the pre-generated HTML code on that page and start off with a completely blank page. Yes, that's right. DBServer and Form Processor do not need HTML in order to process <CMD> tags. Since we are not going to view anything on this page we don't need any HTML formating.

Click on the CMD icon. Select the tab NONE from the tabs on the left. Type in the number 224 in the Command ID text box. Type in update customer in the Command Name text box.Type in CustomerDetail in the Error Template text box. ( Alternately you can click on the down arrow next to the Error Template text box and a drop down list will appear with all the available templates. If the template you're looking for doesn't appear try hitting the refresh icon to update the template list.) Leave the Error Message text box empty for the moment, we will explain why it should be empty later on. Now click the Submit button. Your template should have only one line on it now:

<CMD>{update customer}COMMAND=224&FORMAT=NONE&FAILURE=NONE&
ERRORTEMPLATE=CustomerDetail.groupname</CMD>

Now we are going to redirect back to the CustomerDetail page with a message for a successul update. Click on the CMD icon again and select the tab that reads EXTERNAL. Look at the scrolling list on the right. Select number 2 Redirection from the list. In the Error Template text box type in CustomerDetail. In the Error Message text box type in something like Customer Update Successful! ( Even though this is not truly an error, we are using Form Processor's exception handling abilities to redirect template flow in the way we want.) Hit the Submit button and the new line of code should look like this:

<CMD>{Redirection}COMMAND=ext2&ERRORTEMPLATE=CustomerDetail.groupname
[Customer Update Successful!]</CMD>

These are the only two commands for this template. Go ahead and save it as CustomerUpdate. Even though both of these lines handle exceptions, the first line handles exceptions when something goes wrong in the update statement. If an error occured, you would be sent back to CustomerDetail with an error message explaining the problem. In this case, since we have left the Error Message text box empty when we crafted this command, the message we see on the screen will be the one automatically generated by the database at the time of execution. This brings up and important point. During the development cycle it is best to leave some Error Message text box empty so that you can debug your templates when they are not working the way you expected them to. After you are satisfied with your testing you can go back and manually insert the message you want the viewer to see when using your templates. Most database generated error messages are disturbing to the non-technical user.

Now look at the second command. After the ERRORTEMPLATE= you will see the actual Error Message nested within square brackets. If you were satisfied with your testing you could come back and change the first command by adding your error message right after the error template name nested within square brackets. You may add something like:

[Customer Update Failed.]

Customer Update SQL Statement

In SQL Editor click the New SQL Statement icon. When the Command Editor window opens, click on the SQL Wizard button. Click Next. Make sure the Active Database is the one with your customers tables in it. Click Next. On the Query Type choose UPDATE. Click Next. On the Available Tables list box choose the customers table. Click Next. You should now be looking at a select list of all available columns for that table. Select all of the columns EXCEPT the customer_number column. (The customer_number column is a primary field and shouldn't be messed with.) Click Next. Now you'll be looking at Step 5 which allows you to set the proper variable type for each column. We'll let this stay at the default values. Click Next.This is where you add your clause. Click the button that reads Add Clause. Set the Column Name to customers.customer_number. Leave the operator at =. Set the Variable Type to Form Variable. The Variable Name should read customer_number. If not then type that in. Set the Variable Type to Integer. Click OK then click Add Clause. Click Finish. Look at your complete SQL Statement in the window. If you need to make any corrections, just type them in this window. Now add the Command ID number ( 224 ) and click Submit. Your SQL Statement should look like this:

UPDATE customers SET name='{name:0}', phone='{phone:0}', address1='{address1:0}', address2='{address2:0}', city='{city:0}', state='{state:0}', zip='{zip:0}', country='{country:0}', email='{email:0}' WHERE customer_number={customer_number:8}

When you analyze the code generated above notice that the name-values being piped in are bracketed in curly braces and consist in the name, a colon and a numeral. The numeral is just a token that represents what kind of data type the variable is. Also, string variables must have single quotes around them. Integers and other numeric variable do not have any quotes around them.

I want to bring another thing to your attention here. It is not manditory that the name-values from your templates match the names of the columns in your database table. You could just as well have a portion of the SQL statement above read country='{place:0}'. However it is good practice to match your template name-values with the table column names whenever possible as it is much easier to keep track of the flow of data between templates and database. This is especially true when developing and debugging complex templates with dozens of different name-values from many different tables.

Test your work so far. Change some items on a customer record and update them. Make sure everything is working.

Customer Delete

The final step in this template flow is the CustomerDelete page. We will create it exactly like the CustomerUpdate page in that it will have only two commands on it with no HTML code. In fact, we can save some time by opening up the CustomerUpdate template and then SAVE AS CustomerDelete. Now lets edit the first command to read:

<CMD>{delete customer}COMMAND=225&FORMAT=NONE&FAILURE=NONE&
ERRORTEMPLATE=CustomerDetail.groupname</CMD>

Lets edit the second command to read:

<CMD>{Redirection}COMMAND=ext2&ERRORTEMPLATE=CustomerDetail.groupname[Customer Deleted!]</CMD>

The differences appear in red. Hit save and we're done with that template. Craft your SQL Statement #225 to look like this:

delete from customers where customer_number={customer_number:8}

All thats left to do is provide a link from CustomerDetail to CustomerDelete. Here's how its done.

Open up the CustomerDetail template. Add the following lines below the </FORM> tag:

<br>
<a href="/servlet/engine?TEMPLATE=CustomerDelete.groupname
&customer_number=<REP2>customer_number</REP2>">Delete This Customer</a>

The crucial part is highlighted in red. This is one way to set a name-value in a standard href tag. Now when you run the sequence of templates you will see the CustomerDetail.groupname page with an UPDATE button and a hyperlink below it going to a delete page. Test your work.

Summary

You have learned a lot in this lesson. The good thing is that these two tutorials have covered the basics. Everthing else you learn about the client tools in XLiRAD will just be an elaboration on these basic themes. It really is that simple to use.

Send feedback regarding these
tutorials to gsmith@hhpn.com

< Go to Previous Tutorial

Go to Next Tutorial >