SAS/IntrNet 9.2: htmSQL

Syntax for htmSQL Directives

htmSQL directives are commands that process SQL statements and results sets for your Web page. For more information on the structure of htmSQL input files, see htmSQL Input Files.

The following rules apply to all of the directives:

directives are delimited by braces {}
all htmSQL keywords (directives, parameters, parameter values) are not case sensitive
unless otherwise noted, parameter values can be delimited by either double or single quotation marks
white space within a directive is ignored
no line breaks are allowed in the middle of any string that is delimited by quotation marks (").

The following directives and syntax elements are available:

{query}

Syntax:

{query datasrc="htmSQL-ds" server="host:port" 
       userid="id" password="pw" sapw="sapw"} ... {/query}

datasrc="htmSQL-ds"

htmSQL-ds is a name or a variable reference that identifies an htmSQL data source.

Examples:

  {query datasrc="sales data"}
  {query datasrc="{&dsrc}"}

A data source specifies a SAS/SHARE server and the libraries that are available through the server. Data sources are defined in data source definition files. To define a data source, use the dsdef program that is provided with htmSQL.

server="host:port"

host:port specifies the SAS/SHARE server to connect to. You can use the server= parameter instead of the datasrc= parameter to specify the SAS/SHARE server. When used alone, the server= parameter must specify both the host name and the port for the SAS/SHARE server.

You can also use server= together with datasrc= to override the host and port that are specified in a data source definition. When used together with the datasrc= parameter, the server= parameter can specify the host name, the port, or both for the SAS/SHARE server. If you specify only one of these items, you must include a colon (:) to indicate which one you are specifying.

Note: If any libraries are defined in the data source definition that is specified by the datasrc= parameter, then htmSQL attempts to define those same libraries to the server that is specified by the server= parameter.

The host name can be specified as a fully qualified domain name or it can be specified in any shortened form that is sufficient to enable network services to identify it.

The port can be specified as a number or as a service name that is defined in the TCP/IP SERVICES file.

Users who are familiar with the SAS syntax for specifying a server name can use a period (.) instead of a colon (:) to separate the host name and port. All of the other syntax rules for the server= option still apply.

The following are examples of valid syntax:

  {query server="klondike.acme.com:5228"}

  {query server="penn.sylvania:6500"}

  {query server="yukon.sasshr1"}

  {query datasrc="finance" server="testsrv:"}

  {query datasrc="sales & Marketing" server=":5010"}

  {query datasrc="alaska" server="yukon:sasshr1"}

Tip: If you are just getting started with htmSQL and do not want to define a data source definition file, you can use the server= parameter instead of defining a data source. And, if you specify a port number instead of a service name for this parameter, you also do not need to add an entry to your TCP/IP SERVICES file.

userid="id" (conditionally optional)

id is a user ID for the system that the SAS/SHARE server runs on. If your server is running in secured mode, you must specify a user ID. This can be done by specifying a value for this parameter or by specifying a user ID in your data source definition. You do not have to specify user IDs in both places. If the data source definition contains a user ID, then the user ID that you specify for this parameter overrides the user ID that is stored in the data source definition.

password="pw" (conditionally optional)

pw is the password for the user ID that is specified in the userid= parameter. If your server is running in secured mode, you must specify a password. This can be done by specifying a value for this parameter or by specifying a password in your data source definition. You do not have to specify passwords in both places. If the data source definition contains a password, then the password that you specify for this parameter overrides the password that is stored in the data source definition.

sapw="sapw" (optional)

sapw is the SAS/SHARE server access password for users. This must be the same password that is specified in

the UAPW= option of the SERVER procedure that was used to define the SAS/SHARE server. You must specify a password if user access to the server is password protected and if this password is not already specified in your data source definition.

the SAPW= option of the LIBNAME statement and the SQL procedure's CONNECT TO statement.

The password that you specify for this parameter overrides the password that is stored in the data source definition.

Description: The {query} and {/query} directive pair delimits the query section. An input file can contain multiple query sections. Query sections can be nested within the eachrow and norows sections of a query section and within the success, error, and norows sections of an update section. Each query section must contain at least one SQL/eachrow section pair and can contain multiple pairs.

The SQL section is delimited by the {sql} and {/sql} directive pair and contains the query to be sent to the SAS/SHARE server.
The eachrow section is delimited by the {eachrow} and {/eachrow} directive pair. htmSQL applies the details in the eachrow section to the results set that is generated by the SQL section that immediately precedes that eachrow section. The eachrow section is processed once for each row in the results set.

A query section can also contain a norows section, a {library} directive, and other text, including HTML and variable references. The text can appear before sections, between sections, and after sections.

The following example illustrates a typical query section:

  {query datasrc="data_source_name"}

  {sql}
    [SQL query here]
  {/sql}

  <p>This text is always output.</p>

  {norows}
    [Things to do if no rows are returned]
  {/norows}

  <p>This text is output only when some rows are returned.</p>

  {eachrow}
   [HTML formatting here]
  {/eachrow}

  <p>More text that is output only when some rows are returned.</p>

  {/query}

{sql}

Syntax: {sql empty="success"|"error" error="noprint"} ... {/sql}

empty="success"|"error" (optional): The value for empty= specifies whether processing transfers to the success section (empty="success") or error section (empty="error") if the SQL section resolves to an empty section. The default value is error.
Note: This parameter is used only for SQL sections that are within update sections.
error="noprint" (optional): Specify error="noprint" if you want to suppress error messages that are produced by htmSQL during SQL processing.
Note: If you do not specify this option and your SQL statement contains a SAS data set password, then you risk exposing the password because htmSQL includes the SQL statement along with the SQL error message.

Description: The {sql} and {/sql} directive pair delimits the SQL section. The SQL section is a part of both the query and update sections and contains the SQL statements that are to be sent to the SAS/SHARE server. You can use any SQL statement that is supported by the SAS SQL processor. You can have only one SQL statement per SQL section, but you can have multiple SQL sections within both the query and update sections.

For more information about using SQL statements with SAS data, see the SAS Procedures Guide.

SQL for a Query Section

In a query section, the information between the beginning and ending {sql} directives must begin with the SELECT keyword and must contain one valid SQL query.

An SQL query can be either static or variable.

If you want each of your users to use the same query every time they access your Web page, write a static query. Static queries consist of expressions and clauses that contain constant values and no variable references.
With a variable query, the users of your Web page can customize the query by specifying their own values for search parameters. The query is written using variable references that are given values when users access the Web page.

For example, if your data contains a DATE column, and you want users to be able to specify their own dates to search on, you can place a variable reference in the query for DATE. The following example illustrates this query:
```
  {sql}
    select NAME, TITLE, DEPT from EMPDB.EMPLOYEE
         where START='{&DATE}'
  {/sql}
```
The values that users provide can be specified on the htmSQL URL or collected from an HTML form that you link them to. If you nest a query in the eachrow section of another query section, your nested query can refer to variables in the results set of the encompassing query.

SQL for an Update Section

In an update section, the information between the beginning and ending {sql} directives must begin with the ALTER, CREATE, DELETE, DROP, INSERT, or UPDATE keyword and must contain one valid SQL statement.

Note: The Webmaster can disable ALTER, CREATE, DELETE, DROP, INSERT, and UPDATE statements by specifying the READONLY option in the htmSQL configuration file.

The following example SQL sections are placed consecutively in an update section. The first section creates a data set named def.play, and the second section inserts values into it.

  {sql}
    create table def.play
      (a numeric, b numeric, c numeric, d char, e char);
  {/sql}

  {sql}
    insert into def.play
      set a=1, b=2, c=3, d='xx', e='yy';
  {/sql}

{eachrow}

Syntax:

{eachrow n="n1" first="n2" last="n3" 
         closequery="yes|no"} ... {/eachrow}

n="n1" (optional): n1 is the total number of rows that you want htmSQL to get. A value of max tells htmSQL to get all the rows in the results set. The default value is max.

Note: The n= and last= parameters are mutually exclusive. When you specify both of them, whichever parameter is specified last is the one that is used. For example, if you specify

  {eachrow last="15" n="10"}

then the last row that is retrieved is row 10.

first="n2" (optional)

n2 is the number of the first row that you want htmSQL to get. The default value is 1.

last="n3" (optional)

n3 is the number of the last row that you want htmSQL to get. A value of max specifies the last row in the results set. For example, if you specify the following:

  {eachrow first="20" last="max"}

you get all but the first nineteen rows in the results set.

Note: The n= and last= parameters are mutually exclusive. When you specify both of them, whichever parameter is specified last is the one that is used. For example, if you specify

  {eachrow n="10" last="15"}

then the last row that is retrieved is row 15.

closequery="yes|no" (optional)

closequery="yes" tells htmSQL to send the SAS/SHARE server a message that causes the server to terminate query processing when htmSQL finishes processing the eachrow section. When the SAS/SHARE server terminates query processing, it closes the input tables and frees the memory associated with this query. If you do not specify this parameter or if you specify closequery="no", then query processing is not terminated until htmSQL finishes processing the main input file.

Specify this parameter

if your htmSQL input file contains more than 64 queries to the same SAS/SHARE server
if you want to perform a DROP or ALTER TABLE on a table that the query refers to.

Note: If you specify this parameter, then no references to the columns of the results set are allowed after the eachrow section.

Description: The {eachrow} and {/eachrow} directive pair delimits the eachrow section. The eachrow section is a part of the query section and contains instructions on how to format the results that are generated by the SQL section that immediately precedes that eachrow section. The formatting information is applied to each row of the results set and can include any valid HTML tag and variable reference. The variable references contained in the eachrow section are resolved for each row of output as that row is formatted.

Note: Because htmSQL sends all HTML information to stdout exactly as it is encountered, eachrow sections that are enclosed within HTML PRE elements might not format the way that you expect them to. If your {eachrow} directive is followed by a line break, htmSQL sends that line break to stdout and causes the output to appear double-spaced. The following lines

  <pre>
  {eachrow}
  X: {&x}   Y: {&y}
  {/eachrow}
  </pre>

are sent to stdout (the Web browser) as

  <pre> 

  X: 1      Y: A

  X: 2      Y: B

  X: 3      Y: C

  </pre>

and the Web browser displays following double-spaced output on the Web page:

  X: 1      Y: A

  X: 2      Y: B

  X: 3      Y: C

You can avoid double-spacing by putting the {eachrow} directive on the same row as the variables:

  <pre>
  {eachrow}X: {&x}   Y: {&y}
  {/eachrow}
  </pre>

Nested Sections

If you want to submit more SQL statements from within your eachrow section, you can do one of the following:

nest one or more SQL sections (with accompanying eachrow and norows sections) in the eachrow section. All queries are sent to the data source that is specified by the encompassing {query} directive.

Note that you can also nest a {library} directive; however, we recommend that you nest them only if your {library} directive contains variable references that change as the eachrow section is processed. Otherwise, if the parameters in the {library} directive have static values, then place the {library} directive before the eachrow section to avoid unnecessary processing.
nest a complete query section in the eachrow section, and use the {query} directive to specify a different data source. The SQL, eachrow, and norows sections that are within the nested section will work with data from this new data source.
nest a complete update section in the eachrow section to update data from the same data source or from a different data source.

Note: htmSQL does not limit the number of times that you can nest query sections or SQL/eachrow section pairs within eachrow sections. However, beyond a certain point, you might experience poor performance or your system can run out of memory.

{norows}

Syntax: {norows} ... {/norows}

Description: The {norows} and {/norows} directive pair delimits the norows section. The norows section is a part of the query and success sections. In this section, you include the HTML elements and htmSQL directives that you want htmSQL to process when the previous SQL section does not return or update any rows. After processing a norows section in a query section, htmSQL skips to either the next SQL section or to the end of the query section, whichever comes first. After processing a norows section in a success section, htmSQL skips to the end of the success section.

When an SQL section returns or updates at least one row of data, htmSQL ignores the norows section and continues processing the input file starting with the first line after the norows section.

Nested Sections

If you want to submit more SQL statements from within your norows section, you can do one of the following:

nest one or more SQL sections (with accompanying {library} directive and eachrow, success, error, and norows sections when necessary) in the norows section. All SQL statements are sent to the data source that is specified by the encompassing {query} or {update} directives.
nest a complete query or update section in the norows section. The SQL, eachrow, success, error, and norows sections that are within the nested section will work with data from the data source that is specified on the {query} or {update}directive.

Note: htmSQL does not limit the number of times that you can nest sections within norows sections. However, beyond a certain point, you might experience poor performance or your system can run out of memory.

{update}

Syntax:

{update datasrc="htmSQL-ds" server="host:port"
        userid="id" password="pw" sapw="sapw"} ... {/update}

datasrc="htmSQL-ds"

htmSQL-ds is a name or a variable reference that identifies an htmSQL data source.

Examples:

  {update datasrc="employee_data"}
  {update datasrc="{&dsrc}"}

server="host:port"

The host name can be specified as a fully qualified domain name or it can be specified in any shortened form that is sufficient to enable network services to identify it.

The port can be specified as a number or as a service name that is defined in the TCP/IP SERVICES file.

The following are examples of valid syntax:

  {update server="klondike.acme.com:5228"}

  {update server="penn.sylvania:6500"}

  {update server="yukon.sasshr1"}

  {update datasrc="finance" server="testsrv:"}

  {update datasrc="sales & Marketing" server=":5010"}

  {update datasrc="alaska" server="yukon:sasshr1"}

userid="id" (conditionally optional)

password="pw" (conditionally optional)

sapw="sapw" (optional)

sapw is the SAS/SHARE server access password for users. This must be the same password that is specified in

the UAPW= option of the SERVER procedure that was used to define the SAS/SHARE server. You must specify a password if user access to the server is password protected and if this password is not already specified in your data source definition.

the SAPW= option of the LIBNAME statement and the SQL procedure's CONNECT TO statement.

The password that you specify for this parameter overrides the password that is stored in the data source definition.

Description: The {update} and {/update} directive pair delimits the update section. An input file can contain multiple update sections. Update sections can be nested within the success, norows, and error sections of an update section and within the eachrow and norows sections of a query section. Each update section must contain at least one SQL section and can contain multiple sections. The update section can also contain a success section and an error section.

The SQL section is delimited by the {sql} and {/sql} directive pair and contains the SQL statement that is to be sent to the SAS/SHARE server. The allowed SQL statements are ALTER, CREATE, DELETE, DROP, INSERT, and UPDATE.

Note: The Webmaster can disable these SQL statements by specifying the READONLY option in the htmSQL configuration file.
The success section is delimited by the {success} and {/success} directive pair and contains instructions on what to do if the SQL statement returns with a return code of zero. The success section can contain a norows section for instances where no rows are updated.
The error section is delimited by the {error} and {/error} directive pair and contains instructions on what to do if the SQL statement returns with a return code that is not equal to zero.

An update section can also contain a {library} directive and other text, including HTML and variable references. The text can appear before sections, between sections, and after the sections.

The following example illustrates a typical update section:

  {update datasrc="data_source_name"}

  {sql}
    [SQL statement here]
  {/sql}

  {success}
    [Things to do if the return code is 0]
    {norows}
      [Things to do if no rows are returned]
    {/norows}
  {/success}

  {error}
     [Things to do if the return code is not 0]
  {/error}

  {/update}

{success}

Syntax: {success} ... {/success}

Description: The {success} and {/success} directive pair delimits the success section. The success section is a part of the update section. In this section, you include the HTML elements and htmSQL directives that you want htmSQL to process when the previous SQL section completes with a return code of zero.

When an SQL section returns with a return code that is not equal to zero, htmSQL ignores the success section.

The success section can include a norows section for instances where no rows are updated.

Nested Sections

If you want to submit more SQL statements from within your success section, you can do one of the following:

nest one or more SQL sections (with accompanying {library} directive and success, error, and norows sections when necessary) in the success section. All SQL statements are sent to the data source that is specified by the encompassing {update} directive.
nest a complete update section in the success section, and use the {update} directive to specify a different data source. The SQL, success, error, and norows sections that are within the nested section will work with data from this new data source.
nest a complete query section in the success section to query data from the same data source or from a different data source.

Note: htmSQL does not limit the number of times that you can nest sections within success sections. However, beyond a certain point, you might experience poor performance or your system can run out of memory.

{error}

Syntax: {error} ... {/error}

Description: The {error} and {/error} directive pair delimits the error section. The error section is a part of the update section. In this section, you include the HTML elements and htmSQL directives that you want htmSQL to process when the previous SQL section completes with a nonzero return code.

When an SQL section returns with a return code of zero, htmSQL ignores the error section.

Nested Sections

If you want to submit more SQL statements from within your error section, you can do one of the following:

nest one or more SQL sections (with accompanying {library} directive and success, error, and norows sections when necessary) in the error section. All SQL statements are sent to the data source that is specified by the encompassing {update} directive.
nest a complete update section in the error section, and use the {update} directive to specify a different data source. The SQL, success, error, and norows sections that are within the nested section will work with data from this new data source.
nest a complete query section in the error section to query data from the same data source or from a different data source.

Note: htmSQL does not limit the number of times that you can nest sections within error sections. However, beyond a certain point, you might experience poor performance or your system can run out of memory.

Variable Reference

Syntax:

{&varname format=formats before="string1" 
          between="string2" after="string3"}

&varname: varname is the name of the variable. If the variable you want to reference was specified in the URL with more than one value, you can use the following syntax to reference a single specific value, a range of values, or all values:

`&varname[n]`	references the `n`th value that the variable contains where `n>=1`.
`&varname[m..n]`	references all values from the `m`th value to the `n`th value where `m<=1<n` and the variable contains two or more values.
*`&varname[]`**	references all values that the variable contains.

In the first two instances above, m and n can be references to numeric variables.

format=formats

formats is a single formatting option (format=value) or a comma-delimited list of options enclosed in parentheses (format=(value,...,valueN)). Do not use quotation marks to delimit the values. This is an optional parameter. See Formats for Variable Values and Labels for a list of formatting options.

before="string1" between="string2" after="string3"

These parameters enable you to output strings of characters before, between, and after variable values.

You can use an unlimited number of characters in your strings (note that the maximum number of characters that you can use depends on how much memory your system has). If you want to include double quotation marks within your string, then delimit the string with single quotation marks or use two double quotations marks within your string. For example, both of the following values will return the string "How are you?".

  between='"How are you?"'
  between="""How are you?"""

To include single quotation marks within your string, delimit the string with double quotation marks or use two single quotation marks within your string. For example, both of the following values will return the string What's up?.

  before="What's up?"
  before='What''s up?'

The default value for before= is a null string ("").
The default value for between= is a blank space (" ").
The default value for after= is a null string ("").

Description: A variable reference is a string that htmSQL replaces with the value of a variable. Variables are symbols that are defined on the URL, columns that are selected by a query, or symbols that htmSQL automatically defines and supplies values for.

When htmSQL encounters a variable reference, it replaces the reference with the current value of the variable. If the reference is to a column in a results set and

the variable reference occurs before the SQL section, then the variable is undefined and cannot be resolved. The variable reference is written to stdout unresolved.
the variable reference occurs between the SQL section and the eachrow section, then htmSQL replaces the variable reference with the variable's value from the first row of the results set.
the variable reference occurs within an eachrow section, then htmSQL replaces the variable reference with the current row's value for the variable.
the variable reference occurs after the eachrow section, then htmSQL replaces the variable reference with the variable's value from the last row of the results set.

Resolution of Nested Variable References

htmSQL also supports the resolution of nested variable references. That is, the value of a variable can itself be a variable that htmSQL can resolve. For example, if you have a variable named taxi, and the value of taxi is the variable name driver,

  {&taxi}    ----->   driver

and the value of driver is Bob,

  {&driver}  ----->   Bob

then, when you specify {&{&taxi}}, htmSQL resolves the nested references to a value of Bob.

  {&{&taxi}} ----->   Bob

htmSQL can resolve an infinite number of these nested variable references.

Examples

The following are examples of variable references:

  {&myname}
  {&weekdays[1..7]}
  {&theworld[*]}
  {&array1[{&counter}]}
  {&array2[{&begin}..{&end}]}
  {&months[1..12] before="(" between="," after=")"}
  {&{&varname}}
  {&{&sys.colname[*]}}

{library}

Syntax: {library sqlname="table-qualifier" path="library-path"}

Note: The keyword libname is a synonym for library.

sqlname="table-qualifier": table-qualifier is the high-level qualifier you use in your SQL for the names of tables and views that reside in this SAS data library. This qualifier corresponds to the libref in a SAS program.
You can use the keyword libref as a synonym for sqlname.
path="library-path": library-path is the pathname of the SAS data library.

Description: The {library} directive can be included in both the query and update sections and defines a high-level qualifier that you use in the names of tables and views in your SQL queries and statements. Use this directive when the SAS library that contains the tables and views that you want to access is

not predefined to the SAS/SHARE server through which the library is accessed and
not defined as part of a data source that specifies that server.

Notes:

When you specify a value for sqlname (or libref), you can use that high-level qualifier for any update or query section that specifies the same values for the datasrc= and server= parameters.
You can also specify the same high-level qualifier for two different libraries if they are on different servers. If you specify the same high-level qualifier for two libraries that are on the same server, then the second value overrides the first one.

{label}

Syntax:

{label var="varname1 varname2 ... varnameN" format=formats
       before="string1" between="string2" after="string3"}

var="varname1 varname2 ... varnameN"

varname1 varname2 ... varnameN are the names of variables whose labels you want to display. You can specify one or more variable names; separate the variable names with single spaces.

You can also use a variable reference for a value. For example,

  var="{&mylabel}"
  var="{&sys.colname[*]}"

format=formats

before="string1" between="string2" after="string3"

These parameters enable you to output strings of characters before, between, and after labels.

You can use an unlimited number of characters in your strings (note that the maximum number of characters that you can use depends on how much memory your system has). The only character that is not allowed is the double quotation mark ("). If you want to include double quotation marks within your string, then delimit the string with single quotation marks or use two double quotations marks within your string. For example, both of the following values will return the string "How are you?".

  between='"How are you?"'
  between="""How are you?"""

  before="What's up?"
  before='What''s up?'

The default value for before= is a null string ("").
The default value for between= is a blank space (" ").
The default value for after= is a null string ("").

Description: The {label} directive enables you to display the label for a variable that is in a results set. The label is either returned from the data set or set in the SQL statement. If you want to display the label using a particular format, you can specify the format= parameter.

{include}

Syntax:

{include file="web-server-host-pathname"
         vars="var1=value1&var2=value2&..."}

file="web-server-host-pathname"

web-server-host-pathname is the pathname (either absolute or relative) for a file that is to be processed as an htmSQL input file. If the pathname is relative, then it is relative to either the current working directory for htmSQL or to the path of the calling input file--see your setting for the RELATIVE run-time configuration option.

You can use a variable reference to specify the filename. For example:

  {include file="/dept/web/{&proj}.hsql"}

Note: The value for the file= parameter must be a physical pathname on the Web server machine. It is not a URL.

vars="var1=value1&var2=value2&..."

var1=value1&var2=value2&... is one or more variable name and value pairs that the included input file requires.

The variables that you specify exist in the scope of the included file. This scoping is done so that a variable that is set by both the input file and the included file can retain separate values for each file. When htmSQL finishes processing the included file and returns to the calling input file, the value of the variable is restored to the value that it had before the included file was called. If a variable is only defined for the included file, then you cannot access it after htmSQL returns to the calling file.

The following example uses an input file named emps.hsql that requires values for the variables name and status:

  {include file="emps.hsql" vars="name={&emp}&status=EXEMPT"}

Note from the example that you can use one or more variable references (such as {&emp}) in the value for the vars parameter.

Description: The {include} directive enables you to include other HTML files into the current file. The included file can be a simple HTML file or another htmSQL input file. If it is an htmSQL input file, then it must be complete; it cannot contain a partial query or update section.

The {include} directive cannot appear inside any other htmSQL directive section.

Comment

Syntax: {* your comments here}

Description: All text contained between {* and the closing brace } is considered an htmSQL comment and is not written to stdout. You can comment out single directives or entire sections with one pair of comment braces. The following example shows an entire query section that is commented out:

  {* 
  {query datasrc="employee"}
     {sql}
        select * from empdb.employee
     {/sql}
     {eachrow}
        lastname: {&lname}   firstname: {&fname}
     {/eachrow}
  {/query} 
  }

Note: HTML comments are considered text and are written to stdout along with the other text in the input file.

Top of Page