Getting Started Exercises

About the Exercises

The following exercises illustrate the steps that your organization must follow to install htmSQL and run htmSQL applications. The first exercise requires no data source definition file and does not require that you modify the TCP/IP SERVICES file on your Web server machine.

Note:

Our exercises assign each step to either the Webmaster or the programmer. However, depending on the way your organization is set up, you might have other people performing the tasks.
For more htmSQL samples, see your htmSQL administrator for the URL of the samples that are installed with the htmSQL software. The default URL is http://yourserver/sasweb/IntrNet9/htmSQL/samples.html where yourserver is your Web server host name.

Exercise 1

Basic Example

In the samples directory, we provide an example htmSQL input file (retail1.hsql) that you can modify and use. This exercise shows how to customize and run the example file.

Note: You do not need to save the example htmSQL input file that is shown here. All of the example files in these exercises are supplied in the samples directory.

Webmaster: Install htmSQL by following the instructions in the README file that is in the download package.
Webmaster: Define your SAS/SHARE server in the TCP/IP SERVICES file on the SAS/SHARE server machine.
Webmaster: Ensure that a SAS/SHARE server is running. The following SAS commands can be used to start a SAS/SHARE server. Our exercise uses an example value of shr10 for the server ID; replace shr10 with the service name that you defined in step 2.
```
options comamid=tcp;
proc server id=shr10;
run;
```
Programmer: Edit the {query} directive in the example input file that is in the samples directory:
- Change sampnode.pc.sas.com to the IP name of the SAS/SHARE server machine.
- Change 5000 to the port number that is assigned to the service name that you defined in step 2.
In our exercise, the SAS/SHARE server, shr10, is running on sampnode.pc.sas.com. Service shr10 is assigned to port 5000.
Programmer: Invoke htmSQL to process the example input file. To run our example from the command line, change to the samples directory and issue the following command:
```
htmSQL retail1.hsql param=1992
```

retail1.hsql Sample Input File

{*--------------------------------------------------------------------*}
{* Note: You do not need to save this file from your Web browser. *}
{* This file is available in the htmSQL "samples" subdirectory. *}
{* *}
{* This input file produces a Web page that lists sales information *}
{* that is stored in the 'retail' SAS data set of the SASHELP library.*}
{*--------------------------------------------------------------------*}

<HEAD><TITLE>htmSQL: Retail Data</TITLE></HEAD>
<BODY BGCOLOR="FFFFFF">

{*--------------------------------------------------------------------*}
{* The following section queries the SAS server that is identified by *}
{* the server= parameter and dynamically generates the sales *}
{* information. *}
{*--------------------------------------------------------------------*}

{query server="sampnode.pc.sas.com:5000"}
{sql}
select sales,date,year,month,day
from sashelp.retail where
year = {&PARAM}
{/sql}

{*--------------------------------------------------------------------*}
{* We use a table to show the sales information. *}
{*--------------------------------------------------------------------*}

<TABLE BORDER=1 ALIGN=LEFT>
<TR>
<TH ALIGN=CENTER VALIGN=MIDDLE BGCOLOR="FF0000" NOWRAP>Sales</TH>
<TH ALIGN=CENTER VALIGN=MIDDLE BGCOLOR="FF0000" NOWRAP>Date</TH>
<TH ALIGN=CENTER VALIGN=MIDDLE BGCOLOR="FF0000" NOWRAP>Year</TH>
<TH ALIGN=CENTER VALIGN=MIDDLE BGCOLOR="FF0000" NOWRAP>Month</TH>
<TH ALIGN=CENTER VALIGN=MIDDLE BGCOLOR="FF0000" NOWRAP>Day</TH>
</TR>

{*--------------------------------------------------------------------*}
{* Each row of our results set corresponds to a row in the table *}
{*--------------------------------------------------------------------*}

{eachrow}
<TR>
<TD ALIGN=LEFT VALIGN=MIDDLE NOWRAP>{&sales}</TD>
<TD ALIGN=LEFT VALIGN=MIDDLE NOWRAP>{&date}</TD>
<TD ALIGN=LEFT VALIGN=MIDDLE NOWRAP>{&year}</TD>
<TD ALIGN=LEFT VALIGN=MIDDLE NOWRAP>{&month}</TD>
<TD ALIGN=LEFT VALIGN=MIDDLE NOWRAP>{&day}</TD>
</TR>
{/eachrow}
</TABLE>

{*--------------------------------------------------------------------*}
{* End the query section. *}
{*--------------------------------------------------------------------*}

{/query}
</BODY>

Exercise 2

Advanced Example

In the samples directory, we also provide two files that demonstrate how to use a data source definition with htmSQL. This example htmSQL input file (retail2.hsql) and example data source definition file (retail.datasrc for UNIX and z/OS and retail.dsf for Windows) perform the same task as the input file in exercise 1 but give you the ability to centralize the definition of your data.

Note: You do not need to save the example htmSQL input file and data source definition file that are shown here. All of the example files in these exercises are supplied in the samples directory.

Webmaster: Install htmSQL by following the instructions in the README file that is in the download package.
Webmaster: Define your SAS/SHARE server in the TCP/IP SERVICES file on both the SAS/SHARE server machine and on the Web server machine.
Webmaster: Ensure that a SAS/SHARE server is running. The following SAS commands can be used to start a SAS/SHARE server. Our exercise uses an example value of shr10 for the server ID; replace shr10 with the service name that you defined in step 2.
```
options comamid=tcp;
proc server id=shr10;
run;
```
Webmaster: Modify the example data source definition file that is in the samples directory (you can use a text editor to make these changes):
- Change sampnode.pc.sas.com to the IP name of the SAS/SHARE server machine.
- Change all occurrences of sampnode.shr10 to node.service where
  
  node is the node name of the server machine
  
  service is the service name that is specified in the TCP/IP SERVICES file in step 2 (which is also the value for the ID= option of the PROC SERVER statement in step 3).
  
  In our exercise, the SAS/SHARE server, shr10, is running on sampnode.pc.sas.com.
Programmer: Invoke htmSQL to process the example input file. To run our example from the command line, change to the samples directory and issue one of the following commands:
For UNIX and z/OS:
```
htmSQL retail2.hsql param=1992 -dsf retail.datasrc
```
For Windows:
```
htmSQL retail2.hsql param=1992 -dsf retail.dsf
```

Sample Data Source File

{*--------------------------------------------------------------------*}
{* Note: You do not need to save this file from your Web browser. *}
{* This file is available in the htmSQL "samples" subdirectory. *}
{* *}
{* This input file produces a Web page that lists sales information *}
{* that is stored in the 'retail' SAS data set of the SASHELP library.*}
{*--------------------------------------------------------------------*}

<HEAD><TITLE>htmSQL: Retail Data</TITLE></HEAD>
<BODY BGCOLOR="FFFFFF">

{*--------------------------------------------------------------------*}
{* The following section queries the SAS server that is identified by *}
{* the data source and dynamically generates the sales information. *}
{*--------------------------------------------------------------------*}

{query datasrc="retail"}
{sql}
select sales,date,year,month,day
from sashelp.retail where
year = {&PARAM}
{/sql}

{*--------------------------------------------------------------------*}
{* We use a table to show the sales information. *}
{*--------------------------------------------------------------------*}

<TABLE BORDER=1 ALIGN=LEFT>
<TR>
<TH ALIGN=CENTER VALIGN=MIDDLE BGCOLOR="FF0000" NOWRAP>Sales</TH>
<TH ALIGN=CENTER VALIGN=MIDDLE BGCOLOR="FF0000" NOWRAP>Date</TH>
<TH ALIGN=CENTER VALIGN=MIDDLE BGCOLOR="FF0000" NOWRAP>Year</TH>
<TH ALIGN=CENTER VALIGN=MIDDLE BGCOLOR="FF0000" NOWRAP>Month</TH>
<TH ALIGN=CENTER VALIGN=MIDDLE BGCOLOR="FF0000" NOWRAP>Day</TH>
</TR>

{*--------------------------------------------------------------------*}
{* Each row of our results set corresponds to a row in the table *}
{*--------------------------------------------------------------------*}

{eachrow}
<TR>
<TD ALIGN=LEFT VALIGN=MIDDLE NOWRAP>{&sales}</TD>
<TD ALIGN=LEFT VALIGN=MIDDLE NOWRAP>{&date}</TD>
<TD ALIGN=LEFT VALIGN=MIDDLE NOWRAP>{&year}</TD>
<TD ALIGN=LEFT VALIGN=MIDDLE NOWRAP>{&month}</TD>
<TD ALIGN=LEFT VALIGN=MIDDLE NOWRAP>{&day}</TD>
</TR>
{/eachrow}
</TABLE>

{*--------------------------------------------------------------------*}
{* End the query section. *}
{*--------------------------------------------------------------------*}

{/query}
</BODY>

retail2.hsql Sample Input File

*---------------------------------------------------------------
*
* Notes about this sample htmSQL data source file.
*
* o You do not need to save this file from your Web browser.
* This file is available in the htmSQL "samples" subdirectory.
*
* o This data source definition file specifies one data source
* named 'retail'.
*
* o The retail data source specifies the SAS/SHARE server
* 'sampnode.shr10'.
*
* o The server name is derived as follows:
*
* The SAS/SHARE server is running on sampnode.pc.sas.com
* (machine name). The ID (which is also the TCP/IP service name)
* is shr10. The following job is running on
* sampnode.pc.sas.com:
*
* options comamid=tcp;
* proc server id=shr10;
* run;
*
* o A data source definition file can include one or more
* libraries. However, because the library we are using,
* SASHELP, is already predefined to the server, you do
* not need to define it in the data source file.
*
* o Note that the 'retail' data source is referenced in
* the associated 'retail.hsql' file. Similarly, the 'sashelp'
* library is also referenced in 'retail.hsql'.
*
*---------------------------------------------------------------

* --------------------------------------------------------------
* Identify Data Sources
*
* In this example, the data source is named 'retail'.
*
* --------------------------------------------------------------

Data Source Name:retail=sas

* --------------------------------------------------------------
* Define Method
* --------------------------------------------------------------

Method tcp:COMMEXE=wqetcp
Methods:tcp=

* --------------------------------------------------------------
* Define Server
*
* In this example, the server is named 'sampnode.shr10'.
*
* --------------------------------------------------------------

Server sampnode.shr10:AM=tcp
Server sampnode.shr10:SASSHARE=yes
Server sampnode.shr10:Server Version=6
Server sampnode.shr10:ServerAddress=sampnode.pc.sas.com
Server sampnode.shr10:TraceFlags=0
Servers:sampnode.shr10=

* --------------------------------------------------------------
* Define Data Sources
*
* In this example, the data source is named 'retail'.
* Note that this data source is identified under the
* 'Identify Data Sources' section of this file.
*
* --------------------------------------------------------------

retail:Description=Retail Data Source
retail:Server=sampnode.shr10
retail:UndoRequired=y