TalonGen® Templates

Building templates is the simple and powerful way you control the TalonGen Generator. Templates are simply a model of the HTML or text file that Talongen will use to construct your pages. These templates can be created using any text editor or by using custom tags in most popular web authoring tools.

Template Elements:

Detail Start, SQL statement, and Detail End

	<!-- DETAIL START -->
	<!-- SQL your SQL statement -->
	<!-- DETAIL END -->

Detail Start and end tags as shown above are used to indicate an area of the file that should be repeated for each occurance of a row of data in the SQL statement found within these marks. The SQL statement is always immediately following the Detail Start. These marks must be used exactly as shown here. This means that they must be upper case and spaced as shown. The SQL statement may be more than one line long. If this is done the lines should be bounded as shown in this example:

	<!-- DETAIL START -->
	<!-- SQL select somejunk  -->
	<!-- SQL from mytable where  -->
	<!-- SQL where junkid = 1 -->
	**)somejunk(** <br>
	<!-- DETAIL END -->

Notice that the SQL statement lines each begin with ''. This allows you to spread your SQL over several lines keeping it very readable. You also may notice the characters '**)somejunk(**' in the middle of the statement. This is the symbol used to indicate where to put the result column 'somejunk' during generation. Keep in mind that during generation the other lines of this example would be stripped away and not shown in the output. For example if there were 3 lines of data returned they would appear something like:

	firstjunk <br>
	secondjunk <br>
	thirdjunk <br>

The following table is a summary of the elements that may be employed in a template.

Element Syntax Description

DETAIL START  Indicates the start of a block of repeatable script. The text after this mark and before the DETAIL END will be repeated for each row returned from the SQL Statement following it. These blocks may be nested and repeated. For example:

    **)somejunk(**     **)junktext(**  <br> 
In this example we have select all the rows from the 'outer select' and opened a result set for each row depending on that rows 'rowid'. Whenever you use a subtitution variable in a select, the result set from the next layer 'out' will be used to replace it. This technique can be used to create very sophisticated presentation simply by using multiple and nested results.

DETAIL END  Indicates the end of a block of text to repeat. If the blocks are nested the DETAIL ENDS will be matched to the last unclosed DETAIL START.

SQL Statement  The SQL statement should immediately follow the DETAIL START for the block. This line and any additional lines will be passed to your database to create a result set for processing occurances of the block in question. Multiple lines may be used, but all lines should be adjacent and in the same format. For example:

  

Result Set Variables (Columns) **)n(**
or
**)column name(** After your results are returned from your SQL statement the columns of data must be formated into your result. This is accomplished by substitution of result set variables. These varables may be placed anywhere within a block, as well as inside any immediately nested SQL statement.
When using the number syntax (**)n(**) the number should be the column number of the result set formed by the SQL statement. For example if you had a select statement like: select foo, bar, foey from foobar your result set column numbers would be:
1.)foo shown **)1(**
2.)bar shown **)2(**
3.)foey shown **)3(**

When using the name syntax (**)n(**) the name should be the column name of the result set formed by the SQL statement. Be careful to exactly match the case of these variables as they are case sensitive. In case independent databases be sure to use the case used in the select statement exactly. For example if you had a select statement like: select foo, BAR, foey from foobar your result set column variables would be:
foo shown **)foo(**
BAR shown **)BAR(**
foey shown **)foey(**

Which syntax do you use? The answer to that is up to you. Generally is you have a computed column it is easier to use the column number. The name syntax is easier to read. The number syntax is slightly faster. You pick.

Result Set Variables Formatted **)n(**[xxxx]
or
**)column name(**[xxxx] Numeric Data can be formatted during substitution. To select a format simply add the bracketted format number immediately after the variable field. The format numbers are actually 4 numbers defined as follows:
1.)Data type (only numbers(1) supported now) [1xxx]
2.)Length past the decimal (0-9) for example [12xx] would have 2 decimal places past the decimal
3.)Negative Display Values:

0 -> Parenthesis on negative for example (99.99)

1 -> Leading dash negative for example: -99.99

2 -> Trailing dash negative for example: 99.99-

3 -> Leading dash and space negative for example: - 99.99

4 -> Trailing space and dash negative for example: 99.99 -

4.)Show comma as thousand seperator for example [1xx1] would have commas and [1xx0] would not

Example Formatting:

Format Data Result

[1101] 22222.223 22,222.2

[1101] -22222.223 (22,222.2)

[1001] 22222.223 22,222

[1002] 22222.223 22222

[1121] -22222.223 22,222.2-

[1041] -22222.223 22,222 -

User Passed Variables **)USER[n](** User Variables are passed into the process from the command line using the /V:n syntax where n is a variable number from 0 to 9. These values are taken from the command line and substituted accross the entire template before any other processing is completed. This means you can pass parts of URLs, Dates, Ranges of Data, and other useful externally defined information. User Passed Varables can be used multiple times and in any part of your template. You can see User Passed Variables used in the full examples at the end of this section.

Output file Includes  After database updates have been processed text files may be included as part of the resulting file. These output includes will be inserted at the time the output is written at the location of the include tag. The 'PARSE' keyword may be included or left out. If this keyword is used the included file will be scanned for special characters which will be substituted for the equivelant html tag.

A Simple Template Example: This example shows a simple sheet with two User Variables and a single level of detail. This example returns two columns in a very simple detail section.

	<HTML>
	<HEAD>
	<TITLE>**)USER[1](**'s Page</TITLE>
	</HEAD>
	<BODY>
	<CENTER>
	<I><B><FONT SIZE=5>**)USER[1](**'s Page </FONT></B></I>
	<BR>
	</CENTER>
	<HR>
	<TABLE WIDTH=100% BORDER>
	<TR>
		<TH>Name</TH>
		<TH>ID</TH>
	</TR>

	<!-- DETAIL START -->
	<!-- SQL select name, ID from sysobjects where name like '**)USER[2](**%' -->
	<TR>
		<TD>**)name(**</TD>
		<TD>**)ID(**</TD>
	</TR>
	<!-- DETAIL END -->

	</TABLE>
	</BODY>
	</HTML>

A Batch File Template Example: This example shows a template for a batch file that calls Talongen for each record in a table. This execution would have been called with the /T parameter, indicating text.

	<!-- DETAIL START -->
	<!-- SQL select itemid from catalog-->
	talongen /Dmydata /Umyid /Pmypass /Icat.tmp /O**)itemid(**.html /V1:**)itemid(**
	<!-- DETAIL END -->

A Nested SQL Template Example with formatted number: This example shows a template for an html file that contains data from two nested select statements. For each row returned from the outer select the inner select would be called based on the value of the 'ID' column.

	<HTML>
	<HEAD>
	<TITLE>Test Page</TITLE>
	</HEAD>
	<BODY>
	<CENTER>
	<I><B><FONT SIZE=5>My Test Page (**)USER[1](**)</FONT></B></I>
	<BR>
	</CENTER>
	<HR>
	<TABLE WIDTH=100% BORDER>
	<TR>
		<TH>Name</TH>
		<TH>ID</TH>
		<TH>Type</TH>
		<TH>Play</TH>
		<TH>Columns</TH>
	</TR>

	<!-- DETAIL START -->
	<!-- SQL select name, ID,type, ID - 100.0576 from sysobjects where name like '**)USER[2](**%' -->
	<TR>
		<TD>**)name(**</TD>
		<TD>**)ID(**</TD>
		<TD>**)type(**</TD>
		<TD>**)4(**[1210]</TD>
		<TD>
	<!-- DETAIL START -->
	<!-- SQL select name  from syscolumns where ID = **)2(** --> **)name(** <!-- DETAIL END -->
	</TD>
	</TR>
	<!-- DETAIL END -->

	</TABLE>
	</BODY>
	</HTML>