SQLData Systems, Inc.

Template Token User Guide

 

 

Template Token User Guide

Publishing live database information

Introduction

Template Tokens are commands embedded in HTML files. The SQLData Server parses and executes template tokens and sends the result HTML file to web browsers. Users normally would not see these template tokens even in source format.

A template token is enclosed by a beginning marker and ending marker, which indicate the start and end point of the token. The markers can be any valid strings that do not cause any confusion. For instance, you can choose

<!--- and --!>

as the beginning marker and ending marker. The default markers are ‘[‘ and ‘]’. The markers can be set in the server configuration file.

This document describes how to use template tokens to generate dynamic HTML files that contain live data from ODBC data sources.

Connect to Databases

A database connection must be established before accessing data. The SQLData Servers make a database connection under the following situations:

    1. The HTTP request contains database connection information: The server makes a database connection when the request contains DataSource and SQLStatement.
    2. A Query template token is used in your HTML file , which contains an ODBC connection string.
    3. The request refers to a report or procedure defined in the system.

The easiest way to make a database connection is to use parameters in the HTTP request, for example, the following URL

http://www.myhost.com/myQuery.html? DataSource=mySQL&SQLStatement=select * from customer

The server will try to connect to database mySQL using the current user name and password (when authentication is enabled.) This is one way to create an HTML page that can display records from different databases. The hyperlinks can be set up in other HTML pages so that it displays myQuery.html when clicked. The URL can be drag and dropped on the desktop for quick execution later.

The second approach is to include a complete connection string inside the HTML pages when use a Query token. For instance, the following token can be put at the beginning of the myQuery.html to trigger a database connection and to process a SQL query.

[Query("DSN=mySQL;UID=scott;PWD=tiger", "select * from customer")]

The third approach is to used reports or procedures (See user guide for details) where the details of database connection and SQL statement are hidden from users. The report name or procedure name is passed as a parameter in the URL:

http://www.myhost.com/myQuery.html? ReportName=CustomerInfo

Processing SQL Statements

Processing a SQL statement other than query is simple, using the SQL token and pass the SQL statement as the parameter. For instance, if the following token is embedded in an HTML file, say SQLPage.html

[SQL delete from table employee where employee_name=’joe’]

The command will be executed when user browses the page.

Processing a query requires display of result set. Here is an example how to display the customer table:

[Query ("select * from customer", "DSN=mySQL;USR=scott;PWD=tiger")]

[ResultSet(0, -1, "Detail.html")]

The first token tells the server to process the query statement along with the ODBC connection string. The second token displays the result set in a table. There are three parameters passed to the ResultSet token:

    1. The first parameter is an integer that specifies the starting row to be displayed.
    2. The second parameter is an integer that specifies the last row to be displayed in the result set..
    3. The third parameter is an HTML file that will be displayed when user clicks on the record. The server creates a hyperlink using the file name.

The following is a screen shot of what is being displayed by the ResultSet token. Note that each record is hyper-linked to another page that will display the record details when user clicks on it.

 

 

 

To avoid displaying too many records in a single page, you can set the maximum number of rows in each result set in the server configuration file. The limit can also be specified through the HTTP request as one of the parameters. For instance,

http://www.myhost.com/myQuery.html? DataSource=mySQL&SQLStatement=select * from customer&maxrows=200

displays maximum 200 rows on the HTML page. The maximum row parameter passed through HTTP requests overrides the maximum row value in the server configuration file.

Creating record browser and editor

There are many template tokens that can be used to create HTML forms for data manipulations. The following shows a form that use Fields for data update:

<form method="POST" action="/update"> <pre> [Fields(0, -1, 1)]</pre> <p><input type="submit" value="Update" name="B1"><input type="Reset" value="Reset" name="B2"></p>

</form>

It produces a form dynamically based on the contents of your database table similar to :

 

The HTML forms uses one template token: Fields , which displays the values of a record and provides all necessary elements for record updates.

Display Record In Your Own Way

There are many template tokens that can used for retrieving data from databases. The following is a list of common template tokens:

Template tokens can be placed anywhere in a HTML page and formatted using HTML editors.

For further information about these template tokens, pleases refer to the Reference section of this document.

Creating Navigation Links

It is often unpractical to display thousands of records in a single HTML page, this is why we have a maximum row limit. However, user must have a way to navigate all records in the result set. Navigation support such as Previous Set, Next Set, First Set and Last Set are needed.

The SQLData server has a template token, ButtonLink, for generating such dynamic links. The syntax of the token is:

ButtonLink(ButtonType, TargetFile)

Where ButtonType is one of the following string:

TargetFile is the template file to be used for the result when user clicks the button. The ButtonLink token creates a hyperlink to the TargetFile with all the necessary parameters. For example,

<a href="[ButtonLink __FirstSet record_browser.html]">First Set</a> | <a href="[ButtonLink __PrevSet record_browser.html]">Previous Set</a> | <a href="[ButtonLink __NextSet record_browser.html]">Next Set</a> | <a href="[ButtonLink __LastSet record_browser.html]">Last Set</a>

Generates the four leading buttons in the following picture.

 

Template Token Reference

This section contains detailed description of template tokens offered by the SQLData server. Almost all template tokens return a string as a result. The result replaces the token string in the final HTML file sent to users.

FieldValue(RowId, FieldId)

Get the field value.

Parameters

RowId : The row number in the current result set. The first row number is 0.

FieldId: The field number, starting from 0.

 

Return

The value of the field. An empty string is returned if there is no value.

Example

[FieldValue(0, 1)]

retrieves the second field value in the first record selected by a SQL query.

Remarks

The token is normally used in an HTML page that displays the details of a record. The cursor is positioned to the record specified by the RowId parameter in the HTTP request (not to be confused with the parameter to the token.]. The RowId parameter to the token should be 0 in such a situation.


FieldName(FieldId)

Get the field name.

Parameters

FieldId: The field number, starting from 0.

 

Return

The name of the field.

Example

[FieldValue(1)]

retrieves the second field name in a SQL query.

Remarks

 


ResultSet(StartingRow, EndingRow, TargetFile)

 

Parameters

 

Return

The token returns a formatted result set from StartingRow to the EndingRow with hyper-links. If the total number of rows in the specified is greater than the MaxRows limit, the MaxRows limit will be applied.

Example

[ResultSet(0, -1, "http://www.myhost.com/record_details.html")]

 

Remarks

The token is used to show a limited number of rows in the result set.


Query(ConnectString, QueryStatement)

Parameters

 

Return

The token doesn't return anything.

Example

Query("DSN=myDbase;UID=scott;PWD=lion", "select * from customer where country='USA'")

 

Remarks

The token instructs server to establish a database connection and prepare the query statement for data retrieval.

The position of the token in the HTML file is important. All subsequent tokens that return result from database will use the result selected by the query.

The following example displays customer information from database myOra and order information from myDbase:

[Query("DSN=myOra;UID=scott;PWD=lion", "select * from customer")]

[ResultSet[0, -1, "")]

[Query("DSN=myDBase;UID=;PWD= ", "select * from orders")]

[ResultSet[0, -1, "")]

Note that the HTML page shows two results pulling from different databases.


 

Header

Parameters

None.

Return

The token returns the formatted record header in the current result set. A record header consist of all selected field names from database.

Example

<pre>

[Query("DSN=myDBase;UID=;PWD= ", "select * from orders")]

[Header]

[ResultSet[0, -1, "")]

</pre>

Remarks

The record header is aligned properly with the result set if a fixed pitch font is used. It is a good idea to use the HTML tag <pre> and </pre> to enclose both header and result set as in the example. The server formats the header based on the length of the columns and the length of the column names.


 

Fields(StartField, EndField, ActionType, UseTable)

Parameters

 

Return

The token returns a list of field formatted for the specified operation.

Example

The following demonstrate how to create a record editor using HTML form.

<form method="POST" action="/update">

<pre> [Fields(0, -1, 1)]</pre>

<p><input type="submit" value="Update"> <input

type="Reset" value="Reset"></p>

</form>

 

Remarks

The token is useful for display individual records in an HTML page Note that the token doesn't generate the <form> tag. So, it should normally be used inside a form definition as shown in the example.


 

GetFieldValue(FieldName)

Parameters

 

Return

The token returns the value of the field in the current result set.

Example

The following example returns the field value of a customer id in the result set.

[FieldValue("CustomerId")]

Remarks

Note that the returned value is the value of the first record in the result set.


BOOL ButtonLink(ButtonType, TargetFile);

Parameters

ButtonType: Navigation type for the result set. It can be one of the following string:

    1. __FisrtSet: Display the first result set when user clicks.
    2. __PrevSet: Display the previous result set when user clicks.
    3. __NextSet: Display the next result set when user clicks.
    4. __LastSet: Display the last result set when user clicks.
    5. __First: Display the first record when user clicks. This is for record navigation.
    6. __Previous: Display the previous record when user clicks. This is for record navigation.
    7. __Next: Display the next record when user clicks. This is for record navigation.
    8. __Last: Display the last record when user clicks. This is for record navigation.

TargetFile: The URL pointed to the template file to be processed. The current file is used if the parameter is empty.

 

Return

The token creates hyperlink strings for record navigation and record set navigation.

Example

The following example generates navigation buttons for record sets.

<a href="[ButtonLink __FirstSet record_browser.html]">First Set</a> | <a href="[ButtonLink __PrevSet record_browser.html]">Previous Set</a> | <a href="[ButtonLink __NextSet record_browser.html]">Next Set</a> | <a href="[ButtonLink __LastSet record_browser.html]">Last Set</a>

 

Remarks

There are two different navigation links generated by the ButtonLink token:

Record Navigation: Movement from one record to another. This is used for HTML pages that display the details of a record. A page that uses the Fields token would be a good example.

Record Set Navigation: Movement from one record set to another. This is used for HTML pages that display a set of record each time. A page that uses the RecordSet token is a good candidate for such navigation.

Note that the token doesn't generates the <a href=> tag so that you can name your button anything you like, or even use a button image file.


 

TotalRows

Parameters

 

Return

The token returns the total number of rows in the current result set.

Example

The total number of record selected is [TotalRows].

 

Remarks

The returned value is the total number of record selected by a query (not the total number of records displayed in the HTML page).


RowID

Parameters

 

Return

The token returns the total number of rows in the current result set.

 

Example

Record [RowId] out of [TotalRows].

 

Remarks

The RowId returned is the parameter passed from the HTTP request. The token should be used on HTML pages that display individual record.


 

Include(TemplateFile)

Parameters

TemplateFile: A file that contains HTML elements and template tokens.

 

Return

The token is replaced by the content of the file.

Remarks

This is a server side include. The server loads and processes the specified file, and inserts the contents into the location of the token.

The included file can be any text file. The token is very useful where a HTML block is shared by multiple HTML files. The token can also be used recursively in template files.

 


 

RInclude(TemplateFile)

Parameters

TemplateFile: An URL point to the file to be included.

 

Return

The token is replaced by the content of the file.

Remarks

This is a remote include. The server sends an HTTP request to get the file, and insert it into the current HTML before sending to the client.

The server on the other side should also be a SQLData server.

The token is very useful in a multi-tier database environment where database may be divided into multiple servers. The token is also very convenient where server collaboration and coordination is needed in a server group.


 

ErrorString

Parameters

None.

 

Return

The token returns a database/ODBC error string.

Remarks

The token can be used in the error pages, which are send to users when an error occurred during processing a request. The string contains complete description of the error. It is the ODBC error string if the error occurs in the ODBC layer.

 


 

GetValue(ParameterName)

Parameters

ParameterName: The name of the parameter.

 

Return

The token returns a parameter value of an HTTP request.

Remarks

The token can be used for retrieving information about HTTP request parameters. For example, if URL is

http://www.myhost.com/CoolQuery.html ?DataSource=mySQL

GetValue("DataSource") returns mySQL. The token also returns data posted from HTML forms.


 

SetValue(Name, Value)

Parameters

Name: The name of the parameter to be set.

Value: The string value of the parameter

 

Return

None.

Remarks

The token put a new name-value pair into the request object. The value can be retrieved using GetValue token later, It may also be used by other tokens in the HTML page.


 

GetEnvSetting(ItemName)

Parameters

ItemName: The name of the setting in the configuration file.

 

Return

The token returns the value of the given setting.

Remarks

Use the token to get environment settings in the server configuration file.

 


AddSelection(Type, FieldName, ValueType)

Parameters

Type: Type of the form element, use SELECT for this version.

FieldName: The name of the input field (for submitting).

ValueType: The type of values to be filled into the SELECT list, it can be any of the following values:
DataSources: Fill the SELECTION list with all available ODBC data sources.
Tables: Fill the SELECTION list with all tables in all ODBC data sources.
FieldName: If the parameter is not DataSoures or Tables, the server fills the list with all values of a database column specified by the parameter. The name of the database table is defined by the SQL statement associated with the current HTML page.

 

Return

A string contains complete HTML SELECT element with option values.

Remarks

The token build a list of ODBC data sources, a list of database tables or a list of values from a database field. If the third parameter is not DataSources or Tables, the server use it as the name of a database field to be filled in the list.

The token is used primarily in an HTML form where user can select a data source, a table name or a value in a dropdown list.

Example

The following example displays all ODBC data sources in a dropdown list box.

[AddSelection(SELECT, DSN, DataSources)]

To display all database tables, use

[AddSelection(SELECT, TableName, Tables)]

To show all customer names from a customer table, you may use,

[Query(MyAccess, "select CustomerName from Customers")]

[AddSelection(SELECT, CustomerName, CustomerName)]

where the Query token open the customer table and AddSelection fills the list with all customer names (Assume there is a CustomerName column in the table).

Redirect(NewURL)

Parameters

NewURL: The new URL .

 

Return

None

Remarks

The token send a REDIRECT command to user browser, to switch to a new page specified by the NewURL parameter. This is often used in a situation where the information is offered at a different file.

 

Log(Filename, Message)

Parameters

FileName: The name of your log file.

Message: The string to be put into the log file.

 

Return

None

Remarks

The toke puts the string message into the specified file. This is mainly for debugging purpose.

 

CallScript(ScriptFile, FunctionName)

Parameters

ScriptFile: The name of a file that contains the script.

FunctionName: The name of a function in the script.

 

Return

Depending on the script function, any output from the script function will be returned.

Remarks

This token calls a function written in SQLData Script Language. Check out the Scripting user guide for further information about how to use the tool to generate dynamic HTML pages.

The output strings from the function will be put at the location of the token.