SQLData Systems, Inc.

Template Token User Guide

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.

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

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

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 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:

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:

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,

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.

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.

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.

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:

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,

Generates the four leading buttons in the following picture.

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.

Get the field value.

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

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

[FieldValue(0, 1)]

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.

Get the field name.

The name of the field.

[FieldValue(1)]

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.

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

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

The token doesn't return anything.

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

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:

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

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

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.

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

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

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.

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

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

[FieldValue("CustomerId")]

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

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

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

The following example generates navigation buttons for record sets.

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.

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

The total number of record selected is [TotalRows].

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

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

Record [RowId] out of [TotalRows].

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

The token is replaced by the content of the file.

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.

The token is replaced by the content of the file.

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.

The token returns a database/ODBC error string.

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.

The token returns a parameter value of an HTTP request.

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

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

None.

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.

The token returns the value of the given setting.

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

A string contains complete HTML SELECT element with option values.

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.

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).

None

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.

None

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

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

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