Chapter 5 Dynamo Tags Tutorial

Tags tutorial

These tutorials teach you how to use the most commonly used Dynamo tags.

SQL
FORMATTING
The error checking tags
INCLUDE
SQL_INSERT

Using the SQL tag

Use the SQL Tag to embed SQL queries within HTML. You can then display the output in a variety of formats by using additional Dynamo tags.

This example shows how to embed a SQL query in HTML. This query requests the ID, first name, last name, and phone number of the customers in the customer table. The SQL block of information between the start SQL tag () is processed by the Dynamo application server. The document is then passed on to the browser to be processed; the results display in HTML format.

<HTML>
<TITLE>SQL_tag.stm</TITLE>
<BODY>
<H1>Customer Information</H1>
<!--SQL
SELECT  customer.id, customer.lname, customer.fname, customer.phone
FROM DBA.customer customer

 -->

There are two ways to create a document with an embedded SQL query:

Use the Add Template wizard in Sybase Central. This automatically generates the SQL template for you.
Use the Sybase Central code editor to enter the SQL query.

Using the Add Template wizard:

Use Sybase Central to connect to the sample Web site.
Open the root folder of the Web site.
In the right pane of Sybase Central, double-click Add Template.
Enter a name for the document you are creating, and click Next.
You may enter a description of the document. The description you enter is displayed as an HTML H1 element. Click Next.
If you have more than one connection, select the connection to use for this document. Select <inherited> unless you want to connect to a database other than the one where your Web site is stored. Click Next.
Click Select. The SQL Query Editor opens.
From the Tables tab, double-click on the customer table. The customer table appears under the selected table panel.
Select the Columns tab. Double-click on the following columns in the order given.
- Id
- Fname
- Lname
- phone
Click OK once you have completed the SQL query. This closes the editor and returns you to the wizard. Click Next.
Select HTML as the type of document you wish to create. Click Next.
Select "Table with headings" as the format to display your SQL query. Click Finish.

You can also create a document with an embedded SQL query by using the Add a Script wizard to create a script and then using the Sybase Central code editor to add the SQL tags and query by hand.

Using the Sybase Central code editor

Use Sybase Central to connect to the sample Web site.
Double-click the document to edit.
The document opens in the code editor ready for editing.

The document you just created contains a simple embedded SQL query.

Next, we'll add a FORMATTING tag to display the results of the query.

Using the FORMATTING tag

Use the FORMATTING tag to display rows in the form of a table or a list. When you perform a query, you usually do not know how many rows will be returned.

To display the output as a simple table with no borders, headings or titles, enter:

<!--SQL
SELECT  customer.id, customer.lname, customer.fname, customer.phone
FROM DBA.customer customer
 -->
<!--formatting--><TR>

<!--/formatting-->

The template you created with the Add Template wizard automatically inserted FORMATTING tags. Your template should look something like this:

<HTML>
<TITLE>SQL_tag.stm</TITLE>
<BODY>
<H1>Customer Information</H1>
<!--SQL
SELECT  customer.id, customer.lname, customer.fname, customer.phone
FROM DBA.customer customer

 -->
<TABLE BORDER>
<TR>
<TH>id</TH>
<TH>lname</TH>
<TH>fname</TH>
<TH>phone</TH>
</TR>
<!--formatting--><TR>
<TD><!--data--></TD>
<TD><!--data--></TD>
<TD><!--data--></TD>
<TD><!--data--></TD>
</TR><!--/formatting-->
</TABLE>
</BODY>
</HTML>

The  tag indicates that the tags following it provide information on the formatting of the query. The  tags represent data in a column. This example has four  tags; one for each column being returned.

To assign column headings of the database table to the output, enter:

<TR>
<TH>id</TH>
<TH>lname</TH>
<TH>fname</TH>
<TH>phone</TH>
</TR>

You can use the Sybase Central code editor to edit formatting tags.

To alter a table heading with the Sybase Central code editor:

From Sybase Central, double-click on the template you created. The template opens in the code editor.
Change the line:
```
<TH>id</TH>
```
to
```
<TH>Customer ID</TH>
```
Change the line:
```
<TH>lname</TH>
```
to
```
<TH>Last Name</TH>
```
Change the line:
```
<TH>fname</TH>
```
to
```
<TH>First Name</TH>
```
Select File-> Save to Database or click the Save to Database icon to save the changes you have made to the file.

You have now altered the table headings of your query. To view the template with these changes, right-click on the template within Sybase Central, and select Browse Output.

The LABEL tag

Yet another way to alter the formatting of your output is by using the LABEL tag. LABEL allows you to return the column name along with the column data. For example, you could set up a query to return output in this format:

id: 101
lname: Devlin
fname: Michael
phone: 2015558966

id: 102
lname: Reiser
fname: Beth
phone: 2125558725

id: 103
lname: Niedringhaus
fname: Erin
phone: 2155556513

To use the LABEL tag to format the output of a SQL query:

Use the code editor to open the template you created previously.

Delete the text between the  and  tags so that your template contains only the following lines:

<HTML>
<TITLE>SQL_tag1.stm</TITLE>
<BODY>
<H1>Customer Information</H1>
<!--SQL
SELECT  customer.id, customer.lname, customer.fname, customer.phone
FROM DBA.customer customer

 -->

<!--formatting-->

<!--/formatting-->

</BODY>
</HTML>

To assign a label to each unit of data that is returned by the query, enter this line four times between the formatting tags:
```
: <BR>
```
Using HTML tags with Dynamo tags
You can use normal HTML tags with the Dynamo tag to format the output. In this example we are using the break (<BR>) tag to format the output in a single column.

Save the changes you just made. Your template should now look like this:

<HTML>
<TITLE>SQL_tag.stm</TITLE>
<BODY>
<H1>Customer Information</H1>
<!--SQL
SELECT  customer.id, customer.lname, customer.fname, customer.phone
FROM DBA.customer customer

 -->

<!--formatting-->
<!--LABEL-->: <!--data--><BR>
<!--LABEL-->: <!--data--><BR>
<!--LABEL-->: <!--data--><BR>
<!--LABEL-->: <!--data--><BR>
<BR>
<!--/formatting-->

</BODY>
</HTML>

To view these changes, right-click on the template within Sybase Central, and select Browse Output.

Using the error checking tags

Queries and manipulations against a database may sometimes generate errors. When errors occur, it is more useful to display a message to the user rather than an empty table of results. Dynamo includes several error-checking tags.

The next example first queries the product table from the Sample database, then adds error checking to ensure that no errors occur while the output is generated.

To use error checking tags in your template:

Create a Dynamo template called error_check.stm that contains the following code:

<HTML>
<TITLE>error_check.stm</TITLE>
<BODY>
<H1>Error Checking Example</H1>
<!--SQL
select name, quantity, unit_price, (quantity * unit_price) as total  from product
 -->
 
<TABLE BORDER>
<TR>
<TH>Product Name</TH>
<TH>Quantity</TH>
<TH>Unit_price</TH>
<TH>Total</TH>
</TR>
<!--formatting--><TR>
<TD><!--data--></TD>
<TD><!--data--></TD>
<TD><!--data--></TD>
<TD><!--data--></TD>
</TR><!--/formatting-->
</TABLE>
</BODY>
</HTML>

The output from this query list all inventory items, and the quantity, price, and total value of each item.

Use the SQL_ON_NO_ERROR tag to ensure that the output is error-free.

Add the SQL_ON_NO_ERROR tags at the beginning and end of the portion of the file you want error-checked. Your template should look like this:

<HTML>
<TITLE>error_check.stm</TITLE>
<BODY>
<H1>Error Checking example</H1>
<!--SQL
select name, quantity, unit_price, (quantity * unit_price) as total  from product
 -->
<!--SQL_ON_NO_ERROR--> 
<TABLE BORDER>
<TR>
<TH>Product Name</TH>
<TH>Quantity</TH>
<TH>Unit_price</TH>
<TH>Total</TH>
</TR>
<!--formatting--><TR>
<TD><!--data--></TD>
<TD><!--data--></TD>
<TD><!--data--></TD>
<TD><!--data--></TD>
</TR><!--/formatting-->
</TABLE>
<!--/SQL_ON_NO_ERROR--> 
</BODY>
</HTML>

You can use the SQL_ON_ERROR tag to add a comment that displays only if an error is encountered.

The default behavior of the SQL tag in the event of an error is to abort processing. For this reason, you must add the NO_SQL_ERROR to the SQL tag. This will allow the template to continue executing even if an error is encountered. For example:

<HTML>
<TITLE>error_check.stm</TITLE>
<BODY>
<H1>Error Checking example</H1>
<!--SQL NO_SQL_ERROR
select name, quantity, unit_price,  (quantity * unit_price) as total  from product
 -->
<!--SQL_ON_NO_ERROR--> 
<TABLE BORDER>
<TR>
<TH>Product Name</TH>
<TH>Quantity</TH>
<TH>Unit_price</TH>
<TH>Total</TH>
</TR>
<!--formatting--><TR>
<TD><!--data--></TD>
<TD><!--data--></TD>
<TD><!--data--></TD>
<TD><!--data--></TD>
</TR><!--/formatting-->
</TABLE>
<!--/SQL_ON_NO_ERROR--> 
<!--SQL_ON_ERROR-->
<P>An error has occurred. Contact your system administrator.</P>
<!--/SQL_ON_ERROR-->
</BODY>
</HTML>

View the output of your template. You should see the output of your query in table format as expected.
Change the SQL query so that it has an error in it. Add a spelling mistake to the table that is being queried, for example:
```
select name, quantity, unit_price,  (quantity * unit_price) as total  from prodduct
```
Save your changes.
View the output of your template. Once the error in the query is encountered, the formatting instructions surrounded by the SQL_ON_NO_ERROR tags stop and the SQL_ON_ERROR tags take effect. The text indicating that an error has been encountered is displayed.

See the SQL_ERROR_CODE, SQL_ERROR_INFO, and SQL_STATE tags in "Dynamo Tags" in PowerDynamo Reference for information on how to display errors returned by the database.

Using the INCLUDE tag

Use the INCLUDE tag to incorporate generated output from a document into the current document. This can be useful when an identical SQL query is used by several different documents. Instead of maintaining and updating the query in each document, you can create one template containing the common query.

Let's say that you have an internal Web site that requires a the same list of customer contacts on several different Web pages. The easiest way to handle this is to create one template that queries the customer table for a list of contacts and then to include that template within all the Web pages that required that data.

To include the output of a document within another document:

Create a template that performs the required query. For example, the following template customer.stm queries the customer table for a list of all customer contacts and displays the results in table format:

<!--SQL
SELECT  customer.company_name, customer.lname, customer.fname, customer.phone
FROM DBA.customer customer

 -->
<TABLE BORDER>
<TR>
<TH>company_name</TH>
<TH>lname</TH>
<TH>fname</TH>
<TH>phone</TH>
</TR>
<!--formatting--><TR>
<TD><!--data--></TD>
<TD><!--data--></TD>
<TD><!--data--></TD>
<TD><!--data--></TD>
</TR><!--/formatting-->
</TABLE>

Create a second template that lists expectations for Sales reps. For example:

<HTML>
<H1>Customer List</H1>
<H2>Expectations</H2>
<P>Each sales rep is expected to contact the customers on their contact list a 
minimum of once every two weeks. For a list of the customers that you are 
responsible for please talk to you supervisor.</P>
</HTML>

This example would benefit from displaying a list of customer contacts as well as the expectations. To do this, you need only add one line to the example, which includes the generated output from the customer list. After the "Customer List" heading, add this line:

<!--INCLUDE name="customer.stm"-->

Your example should look like this:

<HTML>
<BODY>
<H1>Customer List</H1>
<!--INCLUDE name="customer.stm"-->
<H2>Expectations</H2>
<P>Each sales rep is expected to contact the customers on their contact list a 
minimum of once every two weeks. For a list of the customers that you are 
responsible for please talk to you supervisor.</P>
</BODY>
</HTML>

Save the template and view the output.

Using the SQL_INSERT tag

Use SQL_INSERT to create a Web page that updates data in a database.

For example, let's say you want to create an internal Web page for the sales group that allows the sales staff to add new customers to the contact table.

To create a Web page that updates the database:

Create a template called NewCustomer.stm that looks like this:

 <HTML>
<TITLE>newCustomer.stm</TITLE>
<BODY>
<H1>New Customer</H1>
<P>Enter the following information</P>
<FORM METHOD=POST ACTION="insertCust.ssc">
<OL>
<LI><INPUT TYPE="text" NAME="id" >ID of the customer<BR>
<LI><INPUT TYPE="text" NAME="last_name" >Last name of the customer<BR>
<LI><INPUT TYPE="text" NAME="first_name" >First name of the customer<BR>
<LI><INPUT TYPE="text" NAME="title" >Customer title<BR>
<LI><INPUT TYPE="text" NAME="street" >Street<BR>
<LI><INPUT TYPE="text" NAME="city" >City<BR>
<LI><INPUT TYPE="text" NAME="state" >State<BR>
<LI><INPUT TYPE="text" NAME="zip" >Zip Code<BR>
<LI><INPUT TYPE="text" NAME="phone" >Phone number<BR>
<LI><INPUT TYPE="text" NAME="fax" >Fax number<BR>
</OL>
<P><INPUT TYPE="submit"></p>
<P><INPUT TYPE="RESET" VALUE="Clear Form"></P>
</FORM>
</BODY>
</HTML>

In a browser, this template would look like Figure 5-1:

Figure 5-1: NewCustomer.stm template

The NAME tags must match the appropriate column in the table that the information is being inserted into. For example, the field that requests the customer's first name has a NAME tag of first_name. This matches the first_name column in the contact table.
Save the template.

Create a template called insertCust.ssc. To take the data that is entered in the NewCustomer.ssc template and add it to the database. It should look like this:

<HTML>
<TITLE>insertCust.ssc</TITLE>
<BODY>
<H1>New Customer</H1>
<!--SQL_INSERT TABLE="contact" -->
<P>Click here <a HREF=NewCustomer.ssc>here </a> if you would like to add another customer</P>
<P>Click here <a HREF=Customer.stm>here </a> if you would like a listing of all customers</P>
</BODY>
</HTML>

Save the template.
Display the NewCustomer.ssc page in your browser and add a new customer as requested (make sure you enter a unique customer ID).
You have now added data to the contact table of the database.

The SCRIPT tag

Use the SCRIPT tag to embed Dynamo script language within documents. The format:

<!--SCRIPT
DynaScript
-->

lets Web editing tools know that they should ignore all text between the start tag () while also letting the Dynamo application server know that there is DynaScript present that requires processing.

For more information on the SCRIPT tag and DynaScript, see "The DynaScript Language" in PowerDynamo Reference.