{query} Directive

Delimits the query section

Syntax

Details

Example

Syntax

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

...{/query}

Required Arguments

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.

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"}

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

Optional Argument

sapw="sapw"

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.

Details

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

Example

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}