SAS/IntrNet 9.2: htmSQL

A Step-by-Step Guide to Creating an htmSQL Web Page

The following steps guide you through the process of creating and displaying a Web page with htmSQL:

Construct your SQL statement. Decide whether you want to vary any part of it (that is, use variable references). For example
```
select name, address, city, zip
          from def.mailresp
          where incr="{&incr}" and ager="{&ager}"
          order by city, zip
```
where incr and ager are variables that you define on the URL when you invoke htmSQL.
Decide how you want to organize the results--perhaps in a table or in a preformatted section.
Decide how you want your Web page to look and where you want to place the various elements of your page (be sure to include titles, headings, images, and any query results).
If necessary, ask your Webmaster to add data sources to your data source definition file. To define a data source, the Webmaster uses the dsdef program that is provided with htmSQL.
If you want to update data, use an update section in your input file. In the update section, include
- an SQL section that contains the SQL you wrote in step 1. The SQL keywords that are allowed in an update section are ALTER, CREATE, DELETE, DROP, INSERT, and UPDATE.
- a success section that contains the steps to take if the SQL is processed with a return code of zero.
- an error section that contains the steps to take if the SQL is processed with a nonzero return code.
To perform a query and display data from the results set, use a query section at the location where you want your query results to be displayed. In the query section, include
- an SQL section that contains the SQL you wrote in step 1. Use variable references for the parts you want to vary, such as column values in a WHERE clause and column names in an ORDER BY clause.
- an eachrow section that contains the text and HTML that is to accompany each row of the results set (depending on what you decided in step 2). Use variable references in the appropriate places for the columns you want to display.
  - If you want the results shown as preformatted text, the eachrow section should be contained within an HTML PRE element.
  - If you want the results shown in an HTML table, the eachrow section should be contained within an HTML TABLE element. Between {eachrow} and {/eachrow}, the row details should be contained within an HTML TR element.
Continuing with the example:
```
{query datasrc="demos"}

{sql}
        select name, address, city, zip
                from def.mailresp
                where incr="{&incr}" and ager="{&ager}"
                order by city, zip
{/sql}

{eachrow} 
{&name}<br>
{&address}<br>
{&city}, TX  {&zip}<p>
{/eachrow}

{/query}
```
You can test your file by invoking htmSQL from a command line prompt and passing the file and any required variables on the command line:
```
htmSQL mailres3.hsql "incr=20K to 39K&ager=30 to 39" > file1.out.html
```
where mailres3.hsql is the name of an input file.

The string that is enclosed within the quotation marks (") specifies values for the variables that are used by the input file. See Invoking htmSQL for more information about htmSQL command line options.

You can then display file1.out.html in a Web browser to ensure that what htmSQL produced is what you want.
After you test your input file, you can link to your new Web page
- from an HTML form, or
- by specifying the URL for your Web page either on an existing Web page or on your Web browser command line.

You can see the complete example input file that is described on this page by visiting the following URL: www2.sas.com/htmSQL/mailres3.txt.

Top of Page